06 · Query Optimization: Making Data Warehouses Fast

-- Fact table (row-oriented OLTP)
CREATE TABLE fact_sales (
  sale_id INT,          -- 4 bytes
  customer_id INT,      -- 4 bytes
  product_id INT,       -- 4 bytes
  store_id INT,         -- 4 bytes
  amount DECIMAL,       -- 16 bytes
  quantity INT,         -- 4 bytes
  cost DECIMAL,         -- 16 bytes
  discount DECIMAL,     -- 16 bytes
  -- Total: ~64 bytes per row
);

-- ROW STORAGE: Like a spreadsheet row
| 1001 | 5432 | 789 | 2 | 99.99 | 1 | 50.00 | 5.00 |
| 1002 | 5433 | 790 | 2 | 149.99| 1 | 75.00 | 15.00|
| 1003 | 5434 | 791 | 2 | 199.99| 2 |100.00 | 20.00|

-- Query: What's total sales amount?
SELECT SUM(amount) FROM fact_sales;

-- Row-oriented: Must read ALL 8 columns (64 bytes) per row
-- 1,000,000 rows × 64 bytes = 64 MB to answer one question!

COLUMNAR (Column-oriented) storage:

sale_id:    | 1001 | 1002 | 1003 | ... |  (4M bytes)
customer_id:| 5432 | 5433 | 5434 | ... |  (4M bytes)
product_id: | 789  | 790  | 791  | ... |  (4M bytes)
store_id:   | 2    | 2    | 2    | ... |  (4M bytes)
amount:     | 99.99| 149.99| 199.99| ...|  (16M bytes) ← Only read this!
quantity:   | 1    | 1    | 2    | ... |  (4M bytes)
cost:       | 50   | 75   | 100  | ... |  (16M bytes)
discount:   | 5    | 15   | 20   | ... |  (16M bytes)

Query: SUM(amount)
Columnar: Only read amount column (16 MB) — 4x smaller!

Row-oriented: 100,000 × 64 bytes = 6.4 MB (can't compress much)
Columnar: 
  - amount (only floats): 100,000 × 8 bytes = 800 KB → 200KB (80% compressed)
  - store_id (integers 1-10): 100,000 integers → 1MB compressed (90% ratio)
  - Total: ~10x compression vs. uncompressed

Result: 6.4 MB → 0.64 MB (10x smaller!)

CREATE TABLE gold.fact_sales (
  sale_id INT,
  sale_date DATE,
  customer_id INT,
  amount DECIMAL,
  ...
);

-- Query: Sales for date range (common case)
SELECT SUM(amount) FROM gold.fact_sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Without partitioning: Must scan entire table (1 billion rows)
-- With partitioning, below:

-- BigQuery: Automatically partitions by date
CREATE TABLE gold.fact_sales
PARTITION BY DATE(sale_date)
AS SELECT ...;

-- Query: Scans only 3 months of data (not 5 years)
SELECT SUM(amount) FROM gold.fact_sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Partition pruning: Query engine skips unneeded partitions
-- 5 years total = 1,800 days × 1M rows/day = 1.8B rows
-- 3 months = 90 days × 1M rows/day = 90M rows
-- 20x reduction in data scanned!

-- Fact table and customer dimension (not clustered)
SELECT SUM(f.amount), c.segment
FROM gold.fact_sales f
JOIN gold.dim_customer c ON f.customer_key = c.customer_key
WHERE f.sale_date = '2024-12-25'
GROUP BY c.segment;

-- For each sale row, must find matching customer
-- Without clustering: Random disk seeks (expensive!)

-- BigQuery: Cluster fact by customer_key + date
CREATE TABLE gold.fact_sales
PARTITION BY DATE(sale_date)
CLUSTER BY customer_key, product_key
AS SELECT ...;

-- Similarly for dimension
CREATE TABLE gold.dim_customer
CLUSTER BY customer_key
AS SELECT ...;

-- Query benefit: Data locality
-- - All rows with customer_key = 5432 are stored together
-- - JOIN is much faster (sequential read vs. seeks)
-- - Second-level: product_key clustering helps GROUP BY

-- OLTP: B-tree index on frequently filtered column
CREATE INDEX idx_customer_id ON orders(customer_id);

-- Query planner: Use index to find 5 rows quickly
SELECT * FROM orders WHERE customer_id = 12345;

-- OLAP: Column-oriented storage is the index!
SELECT SUM(amount) FROM fact_sales WHERE customer_id = 12345;

-- Columnar: No index needed, column store itself is optimized

-- Query: Monthly revenue by product
SELECT 
  DATE_TRUNC('MONTH', sale_date) as month,
  product_id,
  SUM(amount) as revenue
FROM gold.fact_sales
GROUP BY 1, 2;

-- Runs full scan + aggregation every time (expensive!)

-- Pre-compute common aggregation
CREATE MATERIALIZED VIEW gold.mv_monthly_revenue AS
SELECT 
  DATE_TRUNC('MONTH', sale_date) as month,
  product_id,
  SUM(amount) as revenue,
  COUNT(*) as num_transactions
FROM gold.fact_sales
GROUP BY 1, 2;

-- Query: Uses pre-materialized result (instant!)
SELECT * FROM gold.mv_monthly_revenue
WHERE month = '2024-12-01';

-- Normalized: Multiple JOINs
SELECT 
  f.amount,
  c.customer_name,
  c.segment,
  p.product_name,
  p.category,
  d.month
FROM fact_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.sale_date > '2024-01-01';

-- Denormalized: Store dimension attributes in fact
CREATE TABLE gold.fact_sales_enriched AS
SELECT 
  f.amount,
  c.customer_name,     -- Copied from dim_customer
  c.segment,
  p.product_name,      -- Copied from dim_product
  p.category,
  d.month              -- Copied from dim_date
FROM fact_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key;

-- Query: No JOINs needed!
SELECT SUM(amount), segment 
FROM gold.fact_sales_enriched
GROUP BY segment;

-- BigQuery: Use EXPLAIN
EXPLAIN SELECT SUM(amount) FROM gold.fact_sales 
WHERE customer_id = 12345;

-- Shows: Data scanned, Shuffle operations, Bytes pushed down to I/O

-- Snowflake: Use EXPLAIN PLAN
EXPLAIN PLAN FOR SELECT ...;

-- Shows: Table scans, Join order, Filters pushed down