Data Warehouse Fundamentals for Data Analysts

OLTP vs OLAP, star and snowflake schemas, fact and dimension tables, slowly changing dimensions, partitioning, and how to query BigQuery, Redshift, and Snowflake efficiently.

-- Star schema DDL (BigQuery / PostgreSQL syntax)

-- Dimension: customers
CREATE TABLE dim_customers (
    customer_key    INT64   PRIMARY KEY,   -- surrogate key
    customer_id     STRING,               -- natural key from source
    customer_name   STRING,
    email           STRING,
    region          STRING,
    city            STRING,
    segment         STRING,               -- B2B / B2C / Enterprise
    first_order_date DATE,
    is_active       BOOL,
    -- SCD Type 2 fields (see next section)
    effective_from  DATE,
    effective_to    DATE,
    is_current      BOOL
);

-- Dimension: date (pre-generated, 1 row per calendar day)
CREATE TABLE dim_date (
    date_key        INT64   PRIMARY KEY,  -- YYYYMMDD integer
    calendar_date   DATE,
    year            INT64,
    quarter         INT64,
    month           INT64,
    month_name      STRING,
    week_of_year    INT64,
    day_of_week     STRING,
    is_weekend      BOOL,
    is_holiday      BOOL
);

-- Fact table: one row per order line item
CREATE TABLE fct_orders (
    order_line_key  INT64   PRIMARY KEY,
    order_id        STRING,
    customer_key    INT64   REFERENCES dim_customers(customer_key),
    product_key     INT64   REFERENCES dim_products(product_key),
    date_key        INT64   REFERENCES dim_date(date_key),
    quantity        INT64,
    unit_price      NUMERIC,
    discount_pct    NUMERIC,
    line_revenue    NUMERIC,   -- pre-calculated: quantity * unit_price * (1-discount)
    cost_price      NUMERIC,
    margin          NUMERIC    -- pre-calculated: line_revenue - cost_price
);

Simply update the dimension row. Old value is lost. Use when history doesn't matter (e.g. correcting a typo in an email address).

UPDATE dim_customers
SET    email = 'new@example.com'
WHERE  customer_id = 'C1042';

Expire the old row and insert a new one with new values. Preserves full history. The fact table's surrogate key always points to the version that was current at event time.

-- Expire the current row
UPDATE dim_customers
SET    effective_to = CURRENT_DATE - 1,
       is_current   = FALSE
WHERE  customer_id = 'C1042'
AND    is_current  = TRUE;

-- Insert new version
INSERT INTO dim_customers
  (customer_key, customer_id, region, city, ..., effective_from, effective_to, is_current)
VALUES
  (NEXT_KEY, 'C1042', 'South', 'Bengaluru', ..., CURRENT_DATE, '9999-12-31', TRUE);

Add a "previous_value" column. Simple but only tracks one change back — not full history. Use for cases where only the most recent change matters.

-- Add previous_region column to dim_customers
ALTER TABLE dim_customers ADD COLUMN previous_region STRING;

-- When region changes:
UPDATE dim_customers
SET    previous_region = region,
       region          = 'South'
WHERE  customer_id = 'C1042';

-- BigQuery: partition by date column (most common pattern)
CREATE TABLE fct_orders
PARTITION BY DATE(order_date)
CLUSTER BY region, product_category
AS SELECT * FROM fct_orders_staging;

-- Queries that filter on order_date only scan the relevant partitions
-- Queries that also filter on region/product_category scan fewer files within partitions

-- Good query: uses partition + cluster pruning
SELECT region, SUM(line_revenue)
FROM   fct_orders
WHERE  order_date BETWEEN '2026-01-01' AND '2026-03-31'   -- partition filter
  AND  region IN ('North', 'East')                         -- cluster filter
GROUP  BY region;

-- Bad query: full table scan — no partition or cluster filter
SELECT *
FROM   fct_orders
WHERE  customer_name = 'Acme Corp';   -- not a partition or cluster column
-- → scans EVERY partition across ALL time

-- Check how many bytes a query will scan before running it:
-- BigQuery: use "dry run" — click "More > Query settings > Dry run" in console
-- or via CLI: bq query --dry_run --use_legacy_sql=false 'SELECT ...'

-- 1. SELECT only what you need — never SELECT * in production warehouse queries
-- Bad:
SELECT * FROM fct_orders WHERE order_date = '2026-10-01';
-- Good:
SELECT order_id, customer_key, line_revenue FROM fct_orders WHERE order_date = '2026-10-01';

-- 2. Filter on partition/cluster columns FIRST in WHERE clause
-- 3. Avoid functions on partition columns — they defeat partition pruning
-- Bad: DATE_FORMAT(order_date, '%Y') = '2026'
-- Good: order_date BETWEEN '2026-01-01' AND '2026-12-31'

-- 4. Use CTEs for readability, not performance — they don't cache by default
-- In BigQuery, materialise expensive CTEs as temp tables if referenced multiple times:
CREATE TEMP TABLE tmp_active_orders AS
SELECT * FROM fct_orders WHERE order_date >= '2026-01-01' AND status = 'completed';

-- 5. JOIN dimension tables after filtering the fact table
SELECT c.region, SUM(o.line_revenue)
FROM (
    SELECT customer_key, line_revenue
    FROM   fct_orders
    WHERE  order_date BETWEEN '2026-01-01' AND '2026-06-30'  -- filter first
) o
JOIN dim_customers c ON o.customer_key = c.customer_key AND c.is_current = TRUE
GROUP BY c.region;

-- 6. APPROX functions for exploration (BigQuery)
SELECT APPROX_COUNT_DISTINCT(customer_key) AS approx_unique_customers
FROM   fct_orders
WHERE  order_date >= '2026-10-01';
-- ~2% error vs exact COUNT(DISTINCT) but scans far less data