The Path to Insights: Data Models and Pipelines
The Path to Insights: Data Models and Pipelines tests how data moves from source systems into a model that answers BI questions. Expect schemas, keys, grain, ETL versus ELT, validation, and pipeline reliability.
Model layers
Conceptual models name business entities; logical models add attributes and relationships; physical models define tables, types, and indexes. Normalization reduces redundancy, usually to third normal form; denormalization trades redundancy for read speed. Dimensional models use a fact table of events, measures, and foreign keys plus dimension tables that provide context. Star schemas keep dimensions one join away; snowflake schemas normalize dimensions into hierarchies. Declare grain - what one row represents - before adding measures.
Pipeline anatomy
Extract full or incremental data; transform by cleaning, conforming, joining, and aggregating; load by overwrite, append, or upsert. ETL transforms before loading and suits sensitive or complex transformations; ELT loads raw data and transforms inside the warehouse, using its compute. Orchestration covers dependencies, schedules, retries, idempotency, alerting, logging, and lineage.
Data quality and validation
Test not-null, uniqueness, accepted values, referential integrity, row counts, freshness, and schema drift. Reconcile source and target totals. Idempotent loads prevent duplicates on rerun. Slowly changing dimensions: type 1 overwrites, type 2 preserves history with effective dates and current flags.
Worked example
An orders pipeline receives order_id, customer_id, order_ts, amount, and status. The goal is daily revenue by customer segment. Extract incrementally on updated_at; deduplicate by order_id keeping the latest record; validate amount ≥ 0 and status in an accepted set; join to the customer dimension as of the order date using SCD type 2; aggregate to customer-day grain; load partitions by order_date with MERGE. Reasoning: the grain is declared, keys are preserved, late-arriving records are upserted, and historical segment membership is not rewritten.
Traps that cost marks
- Fan-out from joining at the wrong grain.
- Counting duplicates after incremental loads.
- Using today's dimension row for historical facts.
- Ignoring NULL semantics.
- Treating ETL versus ELT as a tool choice rather than an architecture.
- Skipping validation and allowing non-idempotent reruns that double revenue.