Modern Data Modeling

The modern stack (cheap columnar storage, elastic compute, dbt, lakehouses) changed where modeling effort pays off, but not why we model: correct, understandable, reusable data.

1. The big methodologies

Pragmatic modern answer: sources → bronze (raw) → silver (cleaned, conformed entities; sometimes Data Vault in regulated, multi-source enterprises) → gold (Kimball stars) → optional OBTs/semantic layer for consumption.

2. Data Vault 2.0

erDiagram
    HUB_CUSTOMER ||--o{ SAT_CUSTOMER_CRM : "history"
    HUB_CUSTOMER ||--o{ SAT_CUSTOMER_WEB : "history"
    HUB_CUSTOMER ||--o{ LINK_CUSTOMER_ORDER : ""
    HUB_ORDER ||--o{ LINK_CUSTOMER_ORDER : ""
    HUB_ORDER ||--o{ SAT_ORDER : "history"
    HUB_CUSTOMER {
        string customer_hk PK "hash of business key"
        string customer_bk "business key"
        timestamp load_dts
        string record_source
    }
    SAT_CUSTOMER_CRM {
        string customer_hk FK
        timestamp load_dts PK
        string hash_diff "change detection"
        string name
        string segment
        string record_source
    }
    SAT_CUSTOMER_WEB {
        string customer_hk FK
        timestamp load_dts PK
        string hash_diff
        string preferred_language
    }
    HUB_ORDER {
        string order_hk PK
        string order_bk
        timestamp load_dts
        string record_source
    }
    LINK_CUSTOMER_ORDER {
        string link_hk PK
        string customer_hk FK
        string order_hk FK
        timestamp load_dts
        string record_source
    }
    SAT_ORDER {
        string order_hk FK
        timestamp load_dts PK
        string hash_diff
        string status
        decimal amount
    }

Why people choose it: dozens of sources that change often, strict audit (“show me exactly what system X said on date Y”), parallel loading (hash keys, no lookups), insert-only (no updates → easy restatement). Why people avoid it: 3× more tables, needs a dimensional layer for consumption, PIT (point-in-time) tables to make joins tolerable, and a team that knows the method.

3. One Big Table (OBT) in the lakehouse

CREATE OR REPLACE TABLE gold.obt_orders AS
SELECT f.*, c.segment, c.city, c.country, p.category, p.brand, d.iso_week, d.month_name, s.region
FROM gold.fct_order_lines f
JOIN gold.dim_customer c ON f.customer_key = c.customer_key
JOIN gold.dim_product  p ON f.product_key  = p.product_key
JOIN gold.dim_date     d ON f.order_date_key = d.date_key
JOIN gold.dim_store    s ON f.store_key    = s.store_key;

• Columnar compression makes repeated attributes cheap; BI tools love no-join tables.
• Build OBTs from the star schema (not instead of it): the star keeps definitions consistent; OBTs are disposable, rebuildable serving artifacts.
• Watch out for: SCD2 semantics (which version did you join?), rebuild cost when dimensions change, and grain mixing when you bolt on aggregates.

4. Modeling event data

Event streams (clickstream, IoT, app logs) have many event types with different properties.

Always include: event_id (dedup), event_ts (client), received_ts (server), schema_version, source/app version.

5. Semi-structured & nested data

• Bronze: keep raw JSON (STRING/VARIANT) to never lose fields.
• Silver: parse into typed structs; explode arrays into child tables when items are analysed independently (order_items from order.items[]).
• Keep nested STRUCTs when they’re always read together (address, device info). Columnar formats store nested fields as separate columns, so you still get column pruning.
• Schema drift: _rescued_data, mergeSchema, contracts for promoted columns.

6. Modeling for streaming

• Facts are append-only streams → natural fit for transaction facts.
• Dimensions change → streaming joins need versioned (temporal) lookups: stream-to-static join against SCD2 with event-time predicates, or temporal table joins in Flink (FOR SYSTEM_TIME AS OF).
• Accumulating snapshots in streaming = upserts keyed by process id (order_id): MERGE into Delta, upsert tables in Pinot/Hudi.
• Pre-aggregated rollups (per minute/hour) are their own fact tables at coarser grain; state the grain clearly.

7. dbt project layering

flowchart LR
    SRC[(sources)] --> STG["staging<br/>stg_crm__customers<br/>1:1 with source, rename, cast, dedupe"]
    STG --> INT["intermediate<br/>int_orders_joined<br/>business logic, not exposed"]
    INT --> MART["marts<br/>dim_customer, fct_orders<br/>contracts + tests + docs"]
    MART --> SEM["semantic layer / metrics<br/>revenue, active_users"]
    SEM --> BI[BI / apps]

Conventions: stg_<source>__<entity>, int_<verb/description>, dim_/fct_; tests on every primary key (unique + not_null) and foreign key (relationships); model contracts on public marts; exposures for dashboards.

8. Semantic / metrics layer

Problem: “revenue” is defined differently in 12 dashboards. Solution: define metrics once (measure + aggregation + filters + dimensions allowed) in a semantic layer (dbt Semantic Layer/MetricFlow, Databricks metric views, Cube, LookML, AtScale), and let BI tools query metrics instead of raw tables.

9. Choosing: a decision guide

10. Interview questions