Data Warehouse — Cheat Sheet
Tags: data-warehouse, olap, etl, elt, dimensional-modeling, snowflake, bigquery, dbt
Level: intermediate
TL;DR: Centralized analytical repository optimized for complex aggregation queries over historical, cross-domain business data via OLAP, ETL/ELT pipelines, and dimensional modeling.
Key concepts
OLTP optimizes for transactional write speed, row-level integrity, and normalized schemas (3NF).
OLAP optimizes for read-heavy aggregations across historical datasets using columnar storage.
Star schema centers around a de-normalized fact table connected directly to single-level dimension tables.
Snowflake schema normalizes dimension tables into hierarchies, saving storage at the expense of join overhead.
Facts contain additive, semi-additive, or non-additive business metrics and foreign keys.
Dimensions store descriptive context attributes (who, what, where, when) with surrogate primary keys.
Slowly Changing Dimensions (SCD Type 1) overwrite old values without history tracking.
SCD Type 2 preserves history by inserting new rows with validity intervals (valid_from, valid_to, is_current).
Modern ELT loads raw data directly into the warehouse before executing transformations via SQL engines like dbt.
Columnar storage compresses column data, eliminating disk I/O by scanning only referenced projection columns.
Typical questions and ready answers
Q: How do you decide between building a star schema versus a snowflake schema?R: I favor the star schema by default because modern columnar warehouses process denormalized tables faster, minimizing expensive joins for BI tools. I only introduce snowflake normalization when dimensions have distinct cardinality hierarchies that would otherwise cause extreme data redundancy or write synchronization issues.
Q: What is the architectural difference between ETL and ELT in modern cloud warehouses?R: In ETL, raw data is transformed by an intermediate compute cluster before storage, preventing warehouse bloat. In ELT, cloud engines like Snowflake or BigQuery leverage decoupled compute and object storage to ingest raw data first, enabling idempotent transformations directly inside the warehouse via SQL.
Q: How do you handle late-arriving dimensions in a streaming or batch pipeline?R: When a fact arrives referencing a missing dimension key, I generate a placeholder dimension row with a dummy business key, link the fact to this surrogate key, and update the dimension record when its payload arrives.
Q: What is the difference between additive, semi-additive, and non-additive facts?R: Additive facts like revenue can be summed across any dimension. Semi-additive facts like account balances can be summed across customers but not across time. Non-additive facts like profit margins or unit prices cannot be summed directly and require recalculation from raw components.
Pitfalls and common mistakes
Using source system operational primary keys directly as dimension foreign keys instead of surrogate keys.
Failing to define cluster keys or partition keys on date/timestamp columns, triggering costly full-table scans.
Pre-aggregating data prematurely in ELT transformations, destroying the analytical granularity required for root-cause queries.
Mixing multiple grain levels inside the same fact table rather than separating transaction, periodic snapshot, and accumulating snapshot facts.
Treating SCD Type 1 updates as harmless on audit-critical compliance dimensions.
Snippets and commandsSQL
```-- SCD Type 2 dimension lookup and interval query
SELECT
f.order_id,
f.amount,
d.customer_name,
d.tier
FROM fact_orders f
INNER JOIN dim_customer d
ON f.customer_id = d.customer_id
AND f.order_timestamp >= d.valid_from
AND f.order_timestamp < COALESCE(d.valid_to, '9999-12-31'::timestamp);
-- Surrogate key generation using window function in transformation step
SELECT
MD5(CONCAT(coalesce(source_id, ''), '_', updated_at)) AS customer_surrogate_key,
source_id,
email,
CURRENT_TIMESTAMP() AS valid_from,
NULL::TIMESTAMP AS valid_to,
TRUE AS is_current
FROM raw_staging.customers;
```
To review
[ ] Kimball dimensional modeling architecture (facts, conformed dimensions, enterprise bus matrix)
[ ] Inmon corporate information factory (3NF normalized data warehouse with dependent data marts)
[ ] Storage-compute separation mechanics in BigQuery, Snowflake, and Databricks SQL
[ ] Partitioning versus clustering strategies and pruning behaviors on petabyte-scale tables
[ ] Zero-copy cloning and time-travel querying techniques in cloud warehouses
