Dimensional Modeling Deep Dive

erDiagram
    DIM_DATE ||--o{ FACT_SALES : "order_date_key"
    DIM_CUSTOMER ||--o{ FACT_SALES : "customer_key"
    DIM_PRODUCT ||--o{ FACT_SALES : "product_key"
    DIM_STORE ||--o{ FACT_SALES : "store_key"
    DIM_PROMOTION ||--o{ FACT_SALES : "promotion_key"
    FACT_SALES {
        bigint order_date_key FK
        bigint customer_key FK
        bigint product_key FK
        bigint store_key FK
        bigint promotion_key FK
        string order_number "degenerate dimension"
        int quantity "additive"
        decimal net_amount "additive"
        decimal unit_price "non-additive"
    }
    DIM_CUSTOMER {
        bigint customer_key PK "surrogate"
        string customer_id "natural key"
        string segment
        string city
        date valid_from
        date valid_to
        boolean is_current
    }
    DIM_PRODUCT {
        bigint product_key PK
        string sku
        string category
        string brand
    }
    DIM_DATE {
        bigint date_key PK "20261003"
        date full_date
        int iso_week
        string month_name
        boolean is_holiday
    }
    DIM_STORE {
        bigint store_key PK
        string region
        string country
    }
    DIM_PROMOTION {
        bigint promotion_key PK
        string promo_name
        string channel
    }

1. The three (plus one) fact table types

Accumulating snapshot example

Great for pipeline/funnel analysis (where are orders stuck?). Multiple date keys = the date dimension plays several roles.

2. Additivity

Interview trap: “Total balance for March” = SUM of daily balances is wrong. Use the end-of-month balance (or average daily balance). And never store a precomputed conversion_rate in a fact to be averaged later; store conversions and visits.

3. Dimension patterns

Conformed dimensions

Same dimension (same keys, same attribute meanings) shared by multiple fact tables → you can drill across processes (“orders vs returns by product category”). The backbone of an integrated warehouse (the bus matrix columns).

Role-playing dimensions

One physical dimension used in several roles: dim_date as order date, ship date, delivery date; dim_airport as origin and destination; dim_user as sender and receiver. Implement with views or aliases (dim_date AS ship_date).

Degenerate dimensions

Identifiers with no attributes worth a table (order_number, invoice_id, ticket_id) stored directly on the fact. Useful for grouping and drill-back to the source.

Junk dimensions

Several low-cardinality flags/indicators (is_gift, payment_type, channel, is_express) combined into one small dimension of all observed combinations, instead of 6 columns or 6 tiny dimensions on a billion-row fact.

Mini-dimensions

Rapidly changing attributes of a huge dimension (customer age band, income band, loyalty score band) split into a separate small dimension referenced directly by the fact. Avoids an exploding SCD2 customer dimension.

Outriggers

A dimension referencing another dimension (customer → dim_geography). Acceptable sparingly; most of the time flatten.

Bridge tables (many-to-many)

A song has multiple artists; a patient has multiple diagnoses; a bank account has multiple owners.

erDiagram
    FACT_STREAMS }o--|| DIM_TRACK : "track_key"
    DIM_TRACK ||--o{ BRIDGE_TRACK_ARTIST : "track_key"
    DIM_ARTIST ||--o{ BRIDGE_TRACK_ARTIST : "artist_key"
    BRIDGE_TRACK_ARTIST {
        bigint track_key FK
        bigint artist_key FK
        decimal allocation_factor "sums to 1 per track"
        string role "primary, featured"
    }

Joining facts through a bridge multiplies rows, so revenue per artist double counts unless you either multiply by an allocation_factor (weights summing to 1 per track) or report counts explicitly as “streams involving the artist” (non-additive across artists). Always mention this.

Hierarchies

• Fixed-depth (country → region → city): flatten as columns in the dimension.
• Ragged / variable-depth (org charts, account trees): parent–child table + a closure/bridge table (ancestor, descendant, depth) for easy roll-ups; or recursive CTEs.

4. Factless fact tables

• Event tracking: fact_attendance(student_key, class_key, date_key): count attendance.
• Coverage: fact_promotion_coverage(product_key, store_key, date_key): which products were on promotion. Answer “promoted products that didn’t sell” by anti-joining coverage with sales. That question is impossible with the sales fact alone.

5. Late-arriving data

6. Star vs snowflake vs OBT

Modern practice: star schema as the governed core, OBTs (or materialised views) generated from it for specific high-performance consumers. Columnar storage makes the repeated attributes in OBTs cheap (dictionary encoding).

7. Physical design in a lakehouse

• Facts: partition by date (if large) or liquid-cluster by (date, frequently filtered key); incremental loads via MERGE / append.
• Dimensions: small → no partitioning; broadcast in joins; SCD2 via MERGE or dbt snapshot.
• Surrogate keys: hash keys (xxhash64/sha2 of natural key + source) are deterministic and parallel-friendly; identity columns are compact but serialize inserts.
• Enforce not-null FKs with unknown members; test referential integrity in CI (dbt relationships tests).

8. Interview questions