Separate Measures, Dimensions, Descriptive Attributes, Degenerate Dimensions, and Operational Metadata

Learning outcomes

Once grain is fixed, fields need semantic roles. A column’s SQL type does not tell you whether it is a measure, a dimension key, a descriptive attribute, a degenerate dimension, or operational metadata. AtlasMart’s identifiers, quantities, prices, status labels, timestamps, and batch IDs provide several useful counterexamples.

1. Measures answer “how much/how many” only when they are true to grain

For the order-line grain, quantity and unit_price are measurements of the event, and line_amount = quantity × unit_price is a derived measure. For the inventory snapshot grain, on_hand is a measurement of state. But being numeric is not enough: product_id=300, postal code 02139, or a batch sequence 1042 are not quantities to sum.

2. Dimensions and descriptive attributes provide analytical context

A dimension supplies descriptive context for a measurement event—who, what, where, when, why, or how. Its attributes are the labels users filter and group by. For AtlasMart, customer segment/region, product category/name, channel, and calendar attributes are analytical context.

3. Degenerate dimensions preserve a transaction identifier without inventing a table

An order number can be analytically useful: users count distinct orders, drill from a line to its transaction, or investigate one order. Yet if the order number has no additional descriptive attributes that justify a separate dimension row, it can live on the fact as a degenerate dimension. The name “dimension” reflects analytical role, not the existence of a separate physical dimension table.

This is not permission to place arbitrary header facts on line rows. The degenerate order identifier can be present without repeating an order-level shipping measure.

4. Operational metadata is evidence about the pipeline, not the business event

Fields such as source_system, source_extract_id, batch_id, ingested_at, record_hash, and CDC position are critical for lineage, reruns, deduplication, and incident response. They describe how the row arrived, not what the customer bought.

Operational metadata may be queried during troubleshooting and may even be exposed to engineering users, but it should not silently become a business dimension such as “sales by batch ID.” Keeping the categories separate makes downstream semantic layers and governance clearer.

5. Hands-on lab — classify fields and reject “all numbers are measures”

Run the following script. The purpose is semantic classification, not type inference.

fields = {  "quantity": "measure",  "unit_price": "measure",  "customer_id": "dimension_key",  "segment": "dimension_attribute",  "product_id": "dimension_key",  "category": "dimension_attribute",  "order_id": "degenerate_dimension",  "line_no": "event_identity_component",  "source_batch_id": "operational_metadata",  "postal_code": "dimension_attribute"}numeric_looking = {"quantity", "unit_price", "product_id", "line_no", "source_batch_id", "postal_code"}additive_candidates = {k for k,v in fields.items() if v == "measure"}print("measure candidates:", sorted(additive_candidates))print("numeric-looking but not measures:", sorted(numeric_looking - additive_candidates))assert additive_candidates == {"quantity", "unit_price"}assert fields["order_id"] == "degenerate_dimension"assert fields["source_batch_id"] == "operational_metadata"assert fields["postal_code"] == "dimension_attribute"

Expected output shows only quantity and unit_price as measure candidates. Numeric-looking identifiers and postal code remain non-measures. Deliberately change postal_code to measure; add an assertion that every measure is safe to sum, and explain why that classification is nonsensical even if the database stores postal codes as integers.

Cleanup: delete only classify_field_roles.py.

6. Controlled failure: let data type decide semantics

A schema profiler may report that customer_id, postal_code, line_no, and batch_id are integers. Automatically classifying every numeric field as a fact invites meaningless sums and averages. Conversely, a currency amount stored as text because of a source-system quirk is still semantically a measure after correct parsing/validation.

The repair is to classify fields from the process and grain, then apply data types as implementation constraints. Semantics first; representation second.

7. Production judgment and bridge

A field catalog should record business role, definition, grain compatibility, source lineage, aggregation behavior, sensitivity, and owner—not merely SQL type. Operational metadata should remain available for audit/replay while certified analytical surfaces expose only what consumers need.

The next lesson combines the chapter’s decisions into a requirements-to-model traceability matrix so every KPI and slice can be followed back to source evidence and grain.

Knowledge check

Summary and next step

AtlasMart fields now have semantic roles grounded in process and grain. Measures are not “numbers,” and dimensions are not “strings.” Degenerate dimensions and operational metadata solve different problems.

Next: build a traceability matrix that makes these choices auditable from stakeholder KPI to source fields.

Authoritative references

• Kimball Group — Four-Step Dimensional Design Process — Select business process, declare grain, identify dimensions, then identify facts.
• Kimball Group — Business Processes — Business processes are measurement-generating operational activities and define a design target.
• Kimball Group — Grain — Grain is the binding statement of what one fact row represents and must precede dimensions/facts.
• Kimball Group — Dimensions for Descriptive Context — Defines dimensions as descriptive who/what/where/when/why/how context for facts.
• Kimball Group — Another Look at Degenerate Dimensions — Explains transaction identifiers retained in a fact without a corresponding dimension table.
• Kimball Group — Dimensional Modeling Techniques — Reference index for fact, dimension, and advanced dimensional techniques.