Dimension Tables

• A dimension holds the descriptive, low-cardinality-per-row context that facts are grouped and filtered by. It is typically wide and short: many columns, comparatively few rows. Facts are narrow and long. That asymmetry is what makes the star schema efficient — the big table stays skinny and the descriptive text lives once, in a table small enough to broadcast (Broadcast Joins).
• Dimensions are joined many-to-one from the fact. That relationship is what preserves the fact's grain, and it holds only while the dimension key is genuinely unique. A dimension with a duplicate key silently turns every star join into a fan-out (Grain: What Does One Row Represent?).
• A date dimension is a table of one row per calendar day, pre-computed with every attribute anyone might group by: month, quarter, ISO week, fiscal period, day of week, holiday flag, business-day flag. It exists because date arithmetic in SQL is dialect-specific, because fiscal calendars do not follow the Gregorian one, and because a table of dates is the only way to get rows for days on which nothing happened (Snapshot Tables).
• A conformed dimension is one shared by several facts with identical keys and meaning, which is what makes cross-process questions possible — orders and support tickets and marketing touches, all sliced by the same customer. Conforming dimensions is mostly an organisational act, not a technical one (Data Ownership).
• Degenerate dimensions are identifiers with no attributes — order_id, invoice_number — that stay on the fact rather than getting a table of their own. Junk dimensions collect several low-cardinality flags into one small table to keep the fact narrow. Both exist to stop the fact table growing columns.

Without a date dimension, a sparse fact:

  SELECT order_date, SUM(revenue) FROM fct_orders GROUP BY 1

    2026-03-09   1200.00
    2026-03-10    980.00
    2026-03-13   1450.00      <- the 11th and 12th are simply ABSENT
    2026-03-14   1100.00         the chart draws a straight line through them

With a date dimension, LEFT JOINed from the calendar:

  SELECT d.date, COALESCE(SUM(f.revenue), 0)
  FROM   dim_date d
  LEFT   JOIN fct_orders f ON f.date_key = d.date_key
  WHERE  d.date BETWEEN '2026-03-09' AND '2026-03-14'
  GROUP  BY 1

    2026-03-09   1200.00
    2026-03-10    980.00
    2026-03-11      0.00      <- a real zero, visible as an outage or a holiday
    2026-03-12      0.00
    2026-03-13   1450.00
    2026-03-14   1100.00

Same data. One version hides a two-day gap; the other makes it the
most obvious thing on the chart.

Fiscal periods no built-in function can produce:

  date        year_month  fiscal_year  fiscal_quarter  is_business_day
  2026-02-01  2026-02     FY2027       Q1              TRUE
  2026-01-31  2026-01     FY2026       Q4              FALSE

• Give every dimension a surrogate key and join facts on that, never on the source id. It is what makes versioning possible and what absorbs source-key churn (Surrogate Keys).
• Decide history per attribute. Most columns can be overwritten; the small number that appear in historical reports need versioning, and deciding that per column rather than per table keeps the dimension from doubling in size for no reason (Slowly Changing Dimensions).
• Build a date dimension on day one, covering more years than you expect to need, and make every fact carry a date_key into it. It is the cheapest table in the warehouse and it removes an entire category of date-arithmetic bug (Window Functions).
• Load dimensions before facts within a run, and make the fact load fail rather than proceed if a key does not resolve. Silent key loss is the failure mode you least want to be quiet.
• Include an unknown member row — a reserved key such as -1 with descriptive values of Unknown — so facts with missing or late context can be loaded without being dropped by an inner join, and so the gap is visible as a category rather than as absence.
• Keep descriptive text in the dimension and out of the fact. A customer_country column on the fact is a value frozen at load time that you can never restate (Analytical Data Modeling).

• A dimension guarantees uniqueness on its key only if you test it. Analytical engines generally accept a primary-key declaration without enforcing it, so uniqueness is a property of your pipeline and not of your schema (Database Constraints).
• A conformed dimension guarantees that two facts joined to it mean the same thing by "customer". That guarantee is organisational — it survives only as long as both producers keep using the same key space.
• A date dimension guarantees a row exists for every day in range, which is what makes zero-activity periods expressible. Nothing else in the model provides that.
• A dimension without versioning explicitly guarantees only current-state answers. It will answer a historical question anyway, with today's attribute values and no indication that it did so.