learn.fttgsolutions.com · Cheat Sheets
Model. Load. Analyze. Govern.
| OLTP vs OLAP | -- OLTP (Online Transaction Processing)
-- Purpose: run day-to-day business operations
-- Schema: highly normalised (3NF)
-- Query pattern: many short read/write transactions
-- Example systems: MySQL, PostgreSQL (transactional)
SELECT * FROM orders WHERE order_id = 12345; -- point lookup
-- OLAP (Online Analytical Processing)
-- Purpose: support analysis and reporting
-- Schema: denormalised (star/snowflake)
-- Query pattern: few large read-only aggregations
-- Example systems: Snowflake, BigQuery, Redshift
SELECT region, SUM(revenue)
FROM fact_sales
GROUP BY region; -- aggregate millions of rows | OLTP systems optimise for fast, concurrent read/write transactions on normalised schemas. OLAP systems optimise for large analytical queries on denormalised schemas. A data warehouse is an OLAP system — data is extracted from OLTP sources, transformed, and loaded for analysis. |
| Data Mart | -- A subject-specific subset of the full data warehouse
-- Full enterprise DW: all business domains
-- ├── Sales data mart → sales team
-- ├── Finance data mart → finance team
-- ├── Marketing data mart → marketing team
-- └── HR data mart → HR team
-- Dependent mart: sourced from the central DW
CREATE TABLE finance_mart.fact_gl_transactions AS
SELECT * FROM enterprise_dw.fact_transactions
WHERE source_system = 'ERP';
-- Independent mart: sourced directly from operational systems
-- (less preferred — creates data silos) | A data mart is a focused slice of a data warehouse scoped to a specific business function or department. Dependent data marts draw from a central enterprise DW (preferred). Independent data marts source directly from operational systems and risk inconsistency across the organisation. |
| Fact Table | -- Central table in a star/snowflake schema
-- Contains measurable, quantitative data
CREATE TABLE fact_sales (
sales_key BIGINT PRIMARY KEY,
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(12,2)
); | Stores measurable business events (facts). Contains foreign keys to dimension tables plus numeric measures. Fact tables are tall and narrow — many rows, few columns. |
| Dimension Table | -- Descriptive context for facts
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id VARCHAR(20), -- natural/business key
product_name VARCHAR(200),
category VARCHAR(100),
brand VARCHAR(100),
unit_cost DECIMAL(10,2),
is_active BOOLEAN,
effective_date DATE,
expiry_date DATE
); | Stores descriptive attributes that provide context to facts. Dimensions are wide (many columns) and relatively small. Always include a surrogate key separate from the business/natural key. |
| Grain | -- Define grain before designing any table
-- BAD: ambiguous grain
CREATE TABLE fact_sales (order_id INT, ...);
-- GOOD: explicitly stated grain
-- Grain: one row per order line item
CREATE TABLE fact_order_lines (
order_line_key BIGINT,
order_id INT,
line_number INT,
...
); | The grain defines exactly what one row in a fact table represents. Declare the grain before designing the table — mixing granularities in one fact table causes incorrect aggregations. |
| Surrogate Key | -- Use auto-incremented integers, not business keys
-- SQL Server / PostgreSQL
product_key INT IDENTITY(1,1) PRIMARY KEY
-- MySQL
product_key INT AUTO_INCREMENT PRIMARY KEY
-- Snowflake / BigQuery
product_key INT GENERATED ALWAYS AS IDENTITY | A system-generated integer key used as the primary key in dimension tables. Decouples warehouse keys from source system keys — allows source key changes without cascading updates. |
| Measure Types | -- Additive: sum across all dimensions
SUM(sales_amount) -- safe to sum by date, region, product
-- Semi-additive: sum by some dimensions only
SUM(account_balance) -- sum by account, NOT across dates
AVG(account_balance) -- use AVG across dates instead
-- Non-additive: never sum
unit_price -- use AVG, MIN, MAX instead
ratio_metric -- recalculate from components | Additive measures can be summed across all dimensions. Semi-additive measures (balances, inventory) can only be summed across some dimensions. Non-additive measures (ratios, prices) should never be summed. |
| Star Schema | -- One fact table, denormalised dimension tables
dim_date ─────┐
dim_product ──┤
├── fact_sales
dim_customer ─┤
dim_store ────┘
-- Pros: simple joins (2 hops max), fast queries
-- Cons: dimension data is duplicated (denormalised) | The most common DW schema. One fact table at the centre surrounded by flat dimension tables. Optimised for query performance — tools like Power BI and Tableau expect star schemas. |
| Snowflake Schema | -- Dimension tables are normalised into sub-dimensions
dim_date ─────┐
dim_product ──┼─── fact_sales
└── dim_category
dim_customer ─┤
└── dim_city ── dim_state ── dim_country
-- Pros: less storage, consistent updates
-- Cons: more joins, harder for BI tools | A star schema where dimension tables are further normalised. Reduces storage redundancy but adds query complexity. Less common than star schema for reporting layers. |
| Galaxy Schema | -- Multiple fact tables share dimension tables
-- (also called fact constellation)
dim_date ───┬── fact_sales
└── fact_inventory
dim_product─┬── fact_sales
└── fact_inventory
dim_store ──── fact_sales | Two or more fact tables share conformed dimensions. Enables cross-process analysis (e.g., compare sales vs. inventory by product). Shared dimensions are called conformed dimensions. |
| Data Vault | -- Hub: business key
CREATE TABLE hub_customer (
customer_hk CHAR(32) PRIMARY KEY, -- hash key
customer_bk VARCHAR(50), -- business key
load_date TIMESTAMP,
record_source VARCHAR(100)
);
-- Link: relationships between hubs
CREATE TABLE link_order_customer (
order_customer_hk CHAR(32) PRIMARY KEY,
order_hk CHAR(32),
customer_hk CHAR(32),
load_date TIMESTAMP
);
-- Satellite: descriptive attributes + history
CREATE TABLE sat_customer_details (
customer_hk CHAR(32),
load_date TIMESTAMP,
customer_name VARCHAR(200),
email VARCHAR(200)
); | A highly flexible modeling methodology using Hubs (business keys), Links (relationships), and Satellites (attributes + history). Ideal for agile, auditable data warehouses with many source systems. |
| One Big Table (OBT) | -- Pre-join all dims into a single wide table
CREATE TABLE obt_sales AS
SELECT
f.total_amount,
d.full_date,
p.product_name,
p.category,
c.customer_name,
c.country
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key; | Denormalise all joins into a single wide table. Popular in modern cloud DWs (BigQuery, Databricks) where columnar storage makes wide tables efficient. Trades storage for query simplicity. |
| SCD Type 0 — Fixed | -- Attribute never changes after initial load
-- Example: customer date of birth
UPDATE dim_customer
SET date_of_birth = '1990-01-01' -- set once, never updated
WHERE customer_key = 1; | Attributes that should never change after the initial load. Used for inherently static data like birth dates or original registration dates. |
| SCD Type 1 — Overwrite | -- Simply UPDATE the row — no history kept
UPDATE dim_customer
SET email = 'new@email.com',
phone = '555-9999'
WHERE customer_bk = 'CUST-001'; | Overwrite the old value with the new one. No historical record is kept. Use when history is not needed or corrections are being made. Simplest SCD type. |
| SCD Type 2 — History Row | -- Expire old row, insert new row
-- Step 1: close the current record
UPDATE dim_customer
SET expiry_date = CURRENT_DATE - 1,
is_current = FALSE
WHERE customer_bk = 'CUST-001'
AND is_current = TRUE;
-- Step 2: insert the new record
INSERT INTO dim_customer
(customer_bk, name, city, effective_date, expiry_date, is_current)
VALUES
('CUST-001', 'Alice', 'Dallas', CURRENT_DATE, '9999-12-31', TRUE); | The most common SCD type. Adds a new row for each change, preserving full history. Requires effective_date, expiry_date, and is_current columns. Fact rows point to the dimension row active at the time of the event. |
| SCD Type 3 — Prior Column | -- Add 'previous_value' columns
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_bk VARCHAR(50),
current_city VARCHAR(100),
previous_city VARCHAR(100), -- one version of history
city_changed_date DATE
);
UPDATE dim_customer
SET previous_city = current_city,
current_city = 'Austin',
city_changed_date = CURRENT_DATE
WHERE customer_bk = 'CUST-001'; | Adds extra columns to hold the previous value of a tracked attribute. Only one level of history is kept. Use when you need to compare current vs. prior state (e.g., current region vs. previous region for reporting). |
| SCD Type 4 — History Table | -- Current dimension table (latest values only)
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_bk VARCHAR(50),
name VARCHAR(200),
city VARCHAR(100)
);
-- Separate history table with all changes
CREATE TABLE dim_customer_history (
history_key INT PRIMARY KEY,
customer_bk VARCHAR(50),
name VARCHAR(200),
city VARCHAR(100),
changed_date TIMESTAMP
); | Keeps a separate history table alongside the current dimension. The main dimension always reflects the latest state; the history table captures all changes. Good when most queries only need current data. |
| SCD Type 6 — Hybrid | -- Combines Type 1 + Type 2 + Type 3
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_bk VARCHAR(50),
-- Type 2 columns
effective_date DATE,
expiry_date DATE,
is_current BOOLEAN,
-- Current value (Type 1 — always latest)
current_city VARCHAR(100),
-- Value at time of row creation (Type 3)
original_city VARCHAR(100)
); | Combines Type 1 (overwrite current value), Type 2 (add history row), and Type 3 (keep original value). Each row shows both the historical city at the time of the event and the customer's current city — useful for 'as-is' vs. 'as-was' reporting. |
| ETL Pattern | -- Extract → Transform → Load
-- Transformation happens BEFORE loading into DW
[Source DB]
│ Extract (SQL, API, file)
▼
[Staging Server / ETL Tool]
│ Transform (clean, join, aggregate)
▼
[Data Warehouse]
│ Load (bulk insert)
▼
[Presentation Layer] | Traditional approach: transform data outside the warehouse before loading. Common with on-prem DWs where compute was expensive. Tools: SSIS, Talend, Informatica, Apache Spark. |
| ELT Pattern | -- Extract → Load → Transform
-- Transformation happens INSIDE the DW using SQL
[Source DB]
│ Extract (raw data)
▼
[Data Warehouse — Raw Layer]
│ Transform (SQL / dbt)
▼
[Data Warehouse — Curated Layer]
│
▼
[Data Warehouse — Presentation Layer] | Modern cloud approach: load raw data first, then transform using the warehouse's own compute (SQL, Spark). Preferred with cloud DWs (Snowflake, BigQuery, Databricks). Tools: dbt, Dataform. |
| Medallion Architecture | -- Bronze → Silver → Gold layers
-- Bronze: raw, unmodified source data
CREATE TABLE bronze.orders_raw (
raw_payload VARIANT, -- JSON as-is from source
ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Silver: cleaned, conformed, deduplicated
CREATE TABLE silver.orders (
order_id VARCHAR(50),
customer_id VARCHAR(50),
order_date DATE,
total DECIMAL(12,2)
);
-- Gold: business-ready aggregates and models
CREATE TABLE gold.daily_sales_summary (
sale_date DATE,
total_orders INT,
total_revenue DECIMAL(14,2)
); | A layered data architecture popularised by Databricks. Bronze = raw landing zone. Silver = cleaned and conformed. Gold = aggregated, business-ready tables. Each layer adds quality and structure. |
| Incremental Load | -- Load only new or changed records
-- Pattern 1: watermark on timestamp
INSERT INTO silver.orders
SELECT *
FROM bronze.orders_raw
WHERE ingested_at > (SELECT MAX(load_timestamp) FROM silver.orders);
-- Pattern 2: MERGE (upsert)
MERGE INTO silver.orders AS target
USING bronze.orders_raw AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
UPDATE SET target.total = source.total
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, total)
VALUES (source.order_id, source.customer_id, source.total); | Process only new or modified records since the last load, not the full dataset. Use timestamp watermarks, change data capture (CDC), or MERGE statements. Far more efficient than full reloads for large tables. |
| Full Refresh vs Incremental | -- Full refresh: truncate and reload
TRUNCATE TABLE silver.orders;
INSERT INTO silver.orders SELECT * FROM bronze.orders_raw;
-- Incremental: append only new records
INSERT INTO silver.orders
SELECT * FROM bronze.orders_raw
WHERE source_updated_at > :last_run_time;
-- Incremental upsert: handle updates too
MERGE INTO silver.orders USING bronze.orders_raw
ON silver.orders.order_id = bronze.orders_raw.order_id
WHEN MATCHED AND source_updated_at > target.updated_at THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...; | Full refresh is simple but expensive for large tables. Incremental append works for immutable event data. Incremental upsert (MERGE) handles both inserts and updates — necessary for mutable source data. |
| Roll-Up | -- Aggregate from lower to higher grain
-- Day → Month → Quarter → Year
SELECT
YEAR(sale_date) AS year,
QUARTER(sale_date) AS quarter,
MONTH(sale_date) AS month,
SUM(total_amount) AS revenue
FROM fact_sales
GROUP BY ROLLUP (YEAR(sale_date), QUARTER(sale_date), MONTH(sale_date)); | Aggregating data from a detailed level to a higher level of granularity. ROLLUP generates subtotals and grand totals for hierarchical dimensions — use GROUP BY ROLLUP for SQL support. |
| Drill-Down | -- Navigate from summary to detail
-- Start: total by year
SELECT year, SUM(revenue) FROM fact_sales GROUP BY year;
-- Drill down to quarter
SELECT year, quarter, SUM(revenue) FROM fact_sales
WHERE year = 2024
GROUP BY year, quarter;
-- Drill down to month
SELECT year, quarter, month, SUM(revenue) FROM fact_sales
WHERE year = 2024 AND quarter = 1
GROUP BY year, quarter, month; | The inverse of roll-up — navigating from aggregated data to more detailed data. Each level reveals more granular breakdowns of the same metric. |
| Slice | -- Filter one dimension to a single value
-- Slice the cube: only Q1 2024
SELECT
product_name,
region,
SUM(total_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2024
AND d.quarter = 1 -- SLICE on time
GROUP BY product_name, region; | Fix one dimension to a single value, reducing the n-dimensional cube by one. Equivalent to a WHERE clause on one dimension member. |
| Dice | -- Filter multiple dimensions
-- Dice the cube: Q1 2024, Electronics, North region
SELECT SUM(total_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
WHERE d.year = 2024
AND d.quarter = 1 -- filter on time
AND p.category = 'Electronics' -- filter on product
AND s.region = 'North'; -- filter on store | Filter on multiple dimensions simultaneously to select a sub-cube. Similar to slice but across two or more dimension axes. |
| GROUPING SETS | -- Generate multiple groupings in one query
SELECT
region,
product_category,
year,
SUM(revenue) AS total
FROM fact_sales
GROUP BY GROUPING SETS (
(region, year), -- revenue by region and year
(product_category, year), -- revenue by category and year
(year), -- total by year
() -- grand total
); | Compute multiple GROUP BY combinations in a single query pass. More efficient than UNION ALL of separate queries. Use GROUPING() function to identify which groups are subtotals. |
| Range Partitioning | -- Partition by date range (most common)
-- PostgreSQL
CREATE TABLE fact_sales (
sale_date DATE,
total_amount DECIMAL(12,2)
) PARTITION BY RANGE (sale_date);
CREATE TABLE fact_sales_2024
PARTITION OF fact_sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE fact_sales_2025
PARTITION OF fact_sales
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'); | Divide a table into partitions based on a column range — typically a date column. Queries with date filters only scan relevant partitions (partition pruning), dramatically reducing I/O on large tables. |
| List Partitioning | -- Partition by discrete values
CREATE TABLE fact_sales (
region VARCHAR(50),
total_amount DECIMAL(12,2)
) PARTITION BY LIST (region);
CREATE TABLE fact_sales_north
PARTITION OF fact_sales
FOR VALUES IN ('North', 'Northwest', 'Northeast');
CREATE TABLE fact_sales_south
PARTITION OF fact_sales
FOR VALUES IN ('South', 'Southeast', 'Southwest'); | Partition by a discrete list of values such as region or country. Good when a dimension has low cardinality and queries frequently filter on it. |
| Partition Pruning | -- Query scans ONLY matching partitions
-- Good: filter on partition column
SELECT SUM(total_amount)
FROM fact_sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31';
-- → only scans Q1 2024 partition
-- Bad: no filter, full scan
SELECT SUM(total_amount) FROM fact_sales;
-- → scans ALL partitions | The query engine skips partitions that cannot match the filter. Always filter on the partition key to enable pruning. Avoid wrapping partition columns in functions — WHERE YEAR(sale_date) = 2024 prevents pruning; WHERE sale_date >= '2024-01-01' enables it. |
| Clustering / Sorting | -- Snowflake: define clustering key
ALTER TABLE fact_sales CLUSTER BY (sale_date, region);
-- BigQuery: define cluster columns
CREATE TABLE fact_sales
PARTITION BY DATE(sale_date)
CLUSTER BY region, product_id
AS SELECT ...;
-- Redshift: define sort key
CREATE TABLE fact_sales (
sale_date DATE,
region VARCHAR(50),
...
) SORTKEY (sale_date, region); | Physically co-locate related rows on disk. Column order matters — put the most-filtered column first. Works alongside partitioning: partition prunes whole files, clustering prunes micro-partitions within files. |
| Aggregate Table | -- Pre-compute and store summary results
CREATE TABLE agg_daily_sales AS
SELECT
d.full_date,
p.category,
s.region,
SUM(f.total_amount) AS daily_revenue,
COUNT(*) AS order_count
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
GROUP BY d.full_date, p.category, s.region;
-- Refresh daily
TRUNCATE TABLE agg_daily_sales;
INSERT INTO agg_daily_sales SELECT ...; | Pre-compute frequently queried aggregations and store them as summary tables. Reports query the aggregate table instead of the full fact table — orders-of-magnitude faster for dashboards. |
| Materialized View | -- Auto-maintained aggregate
-- PostgreSQL
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
region,
SUM(total_amount) AS revenue
FROM fact_sales
GROUP BY 1, 2;
-- Refresh (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;
-- Snowflake auto-refreshes on data change
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT ...; | A query result stored physically and refreshed periodically or automatically. Unlike aggregate tables, materialized views are maintained by the database engine. Use CONCURRENTLY in PostgreSQL to refresh without locking. |
| Window Aggregations | -- Running total
SELECT
sale_date,
daily_revenue,
SUM(daily_revenue) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM agg_daily_sales;
-- 7-day rolling average
SELECT
sale_date,
AVG(daily_revenue) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM agg_daily_sales; | Compute aggregations over a sliding or cumulative window of rows. Essential for running totals, moving averages, and period-over-period comparisons. No GROUP BY needed — detail rows are preserved. |
| Period-over-Period | -- Year-over-year comparison using LAG or self-join
-- Using LAG
SELECT
month,
revenue,
LAG(revenue, 12) OVER (ORDER BY month) AS revenue_prior_year,
revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change,
ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS yoy_pct
FROM agg_monthly_revenue; | Compare a metric to the same period in a previous time range. LAG() looks back N rows in an ordered window. Use NULLIF to avoid division by zero when the prior period is missing or zero. |
| Null Checks | -- Identify nulls in critical columns
SELECT
COUNT(*) AS total_rows,
COUNT(*) FILTER (WHERE order_id IS NULL) AS null_order_id,
COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_customer_id,
COUNT(*) FILTER (WHERE sale_date IS NULL) AS null_sale_date
FROM fact_sales;
-- Block nulls at load time
ALTER TABLE fact_sales
ALTER COLUMN order_id SET NOT NULL; | Null values in fact keys or critical measures corrupt aggregations. Validate nulls before and after loading. Enforce NOT NULL constraints on surrogate keys and foreign keys in the warehouse layer. |
| Duplicate Detection | -- Find duplicate natural keys
SELECT order_id, COUNT(*) AS cnt
FROM fact_sales
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Deduplicate using ROW_NUMBER
DELETE FROM fact_sales
WHERE sales_key NOT IN (
SELECT MIN(sales_key)
FROM fact_sales
GROUP BY order_id
);
-- Or deduplicate in SELECT
SELECT DISTINCT ON (order_id) *
FROM fact_sales
ORDER BY order_id, ingested_at DESC; | Duplicate rows cause double-counting in aggregations. Always validate uniqueness on natural/business keys after loading. Use ROW_NUMBER() or DISTINCT ON to deduplicate, keeping the most recent record. |
| Referential Integrity | -- Orphaned fact rows (no matching dimension)
SELECT f.order_id
FROM fact_sales f
LEFT JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE c.customer_key IS NULL;
-- Enforce at schema level
ALTER TABLE fact_sales
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_key)
REFERENCES dim_customer(customer_key);
-- Note: most cloud DWs (Snowflake, BigQuery)
-- support FK definitions but do NOT enforce them | Orphaned fact rows (fact keys with no matching dimension) cause rows to disappear from reports when inner joins are used. Validate FK integrity post-load. In cloud DWs, foreign keys are informational only — enforce in ETL logic. |
| Range & Sanity Checks | -- Check for out-of-range values
SELECT *
FROM fact_sales
WHERE total_amount < 0
OR total_amount > 1000000
OR quantity <= 0
OR sale_date > CURRENT_DATE
OR sale_date < '2000-01-01';
-- Row count reconciliation
SELECT
COUNT(*) AS dw_row_count,
SUM(total_amount) AS dw_total,
MAX(ingested_at) AS last_loaded
FROM fact_sales
WHERE sale_date = '2024-01-15';
-- Compare against source system totals | Validate that loaded values fall within expected ranges. Always reconcile row counts and sum totals between source and target after each load — discrepancies indicate pipeline failures or data truncation. |
| Data Purging | -- Delete obsolete records beyond the retention window
-- Pattern 1: delete by date threshold
DELETE FROM fact_sales
WHERE sale_date < DATEADD(year, -7, CURRENT_DATE);
-- Pattern 2: partition drop (much faster for large tables)
ALTER TABLE fact_sales
DROP PARTITION p_2017;
-- Pattern 3: archive before delete
INSERT INTO fact_sales_archive
SELECT * FROM fact_sales
WHERE sale_date < DATEADD(year, -7, CURRENT_DATE);
DELETE FROM fact_sales
WHERE sale_date < DATEADD(year, -7, CURRENT_DATE);
-- After purge: reclaim space
VACUUM ANALYZE fact_sales; -- PostgreSQL | Remove data that is no longer needed based on a retention policy. Reduces storage costs, improves query performance by shrinking table sizes, and ensures compliance with data regulations (GDPR right to erasure). Dropping a partition is orders-of-magnitude faster than row-by-row DELETE on large tables. |
| Columnnar Storage | -- Cloud DWs store data column-by-column
-- Query only retrieves columns referenced
SELECT SUM(total_amount) -- reads only 1 column
FROM fact_sales; -- ignores all other columns
-- Columnar compression ratios are high
-- for low-cardinality columns
-- e.g., region VARCHAR(50) with 5 distinct values
-- compresses 10x-100x better than row storage
-- Best practice: avoid SELECT *
-- SELECT * defeats columnar pruning | Columnar databases (Snowflake, BigQuery, Redshift, Databricks) store each column separately. Only queried columns are read from disk, making analytical queries orders of magnitude faster than row stores for aggregations. |
| Predicate Pushdown | -- Filter as early as possible in the pipeline
-- BAD: filter after join (reads all rows)
SELECT *
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.year = 2024; -- filter applied after join
-- BETTER: filter dim before join
WITH dates_2024 AS (
SELECT date_key FROM dim_date WHERE year = 2024
)
SELECT f.*
FROM fact_sales f
JOIN dates_2024 d ON f.date_key = d.date_key; | Push filter conditions as close to the data source as possible to minimise rows processed. Query optimisers in modern DWs do this automatically for simple cases, but complex CTEs or subqueries may need manual optimisation. |
| Join Order Optimisation | -- Join smallest tables first to reduce row counts early
-- Start with the most selective filter
-- LESS EFFICIENT: large fact joins large dims
SELECT ...
FROM fact_sales f -- 1B rows
JOIN dim_customer c ON ... -- 10M rows
JOIN dim_date d ON ...
WHERE d.year = 2024; -- filter late
-- MORE EFFICIENT: filter dim first
WITH q1_dates AS (
SELECT date_key FROM dim_date
WHERE year = 2024 AND quarter = 1 -- 91 rows
)
SELECT ...
FROM q1_dates d -- 91 rows first
JOIN fact_sales f ON f.date_key = d.date_key
JOIN dim_customer c ON ...; | Join order affects how many rows flow through each stage. Most optimisers handle this automatically, but with very large tables it pays to pre-filter dimensions and join the smallest intermediate result sets first. |
| Avoid Functions on Indexed Columns | -- BAD: function on filter column prevents index/pruning
WHERE YEAR(sale_date) = 2024
WHERE UPPER(region) = 'NORTH'
WHERE CAST(order_id AS VARCHAR) = '12345'
-- GOOD: filter directly on the column value
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
WHERE region = 'North'
WHERE order_id = 12345 | Wrapping a column in a function prevents the query engine from using indexes or micro-partition metadata. Always filter on the raw column value to enable partition pruning, bloom filters, and zone maps. |
| Audit Columns | -- Add to every table in the warehouse
ALTER TABLE dim_customer ADD COLUMN
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dim_customer ADD COLUMN
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dim_customer ADD COLUMN
record_source VARCHAR(100); -- 'CRM', 'ERP', 'CSV'
ALTER TABLE dim_customer ADD COLUMN
batch_id VARCHAR(50); -- pipeline run ID | Track when each row was created, last updated, and where it came from. record_source traces data origin; batch_id links rows to the specific pipeline run that loaded them — essential for debugging load failures. |
| Data Catalog Metadata | -- Document tables and columns with comments
-- PostgreSQL / standard SQL
COMMENT ON TABLE fact_sales IS
'One row per order line item. Grain: order_id + line_number.';
COMMENT ON COLUMN fact_sales.total_amount IS
'Unit price × quantity, in USD, before tax and discounts.';
-- Snowflake
ALTER TABLE fact_sales
SET COMMENT = 'One row per order line. Source: OMS.';
ALTER TABLE fact_sales
MODIFY COLUMN total_amount
COMMENT 'Revenue in USD before tax.'; | Document tables with grain statements and column definitions as close to the object as possible. These comments are surfaced in data catalog tools (Alation, Collibra, DataHub) and help analysts understand data without needing to ask. |
| Row-Level Security | -- Restrict which rows a role can see
-- Snowflake Row Access Policy
CREATE OR REPLACE ROW ACCESS POLICY region_policy AS
(region VARCHAR) RETURNS BOOLEAN ->
CASE
WHEN CURRENT_ROLE() = 'ADMIN' THEN TRUE
WHEN CURRENT_ROLE() = 'NORTH_ANALYST' THEN region = 'North'
WHEN CURRENT_ROLE() = 'SOUTH_ANALYST' THEN region = 'South'
ELSE FALSE
END;
ALTER TABLE fact_sales
ADD ROW ACCESS POLICY region_policy ON (region); | Enforce data access restrictions at the row level based on user role, attribute, or context. Applied transparently to all queries — analysts can't bypass it. Implement at the presentation layer, not the raw layer. |
| Column Masking | -- Mask sensitive columns based on role
-- Snowflake Dynamic Data Masking
CREATE OR REPLACE MASKING POLICY email_mask AS
(val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() = 'PII_ADMIN' THEN val
ELSE REGEXP_REPLACE(val, '.+@', '***@')
END;
ALTER TABLE dim_customer
MODIFY COLUMN email
SET MASKING POLICY email_mask; | Dynamically redact or mask sensitive columns (PII, PCI) based on the querying user's role. Analysts see masked values; privileged roles see real data. Applied at query time — no data duplication needed. |