Data Warehousing Concepts Cheat Sheet

Covers dimensional modeling, star vs. snowflake schemas, ETL/ELT pipelines, OLAP operations, and slowly changing dimensions for building analytical data warehouses.

2 PagesIntermediateMar 10, 2026

Core Concepts

Fundamental building blocks of a data warehouse.

Fact table- Central table storing quantitative business metrics (sales amount, quantity) at a specific grain, with foreign keys to dimensions
Dimension table- Descriptive table (customer, product, date) that provides context for facts via lookups
Star schema- Fact table directly linked to denormalized dimension tables; fast for reads, simple joins
Snowflake schema- Dimensions normalized into sub-dimensions; saves space but requires more joins
Grain- The level of detail a single fact row represents (e.g., one row per order line item)
Surrogate key- System-generated integer key used instead of natural keys to join facts and dimensions

Star Schema Query

Typical analytical query joining a fact table to its dimensions.

sql

SELECT d.year, p.category, SUM(f.sales_amount) AS total_salesFROM fact_sales fJOIN dim_date d ON f.date_key = d.date_keyJOIN dim_product p ON f.product_key = p.product_keyWHERE d.year IN (2023, 2024)GROUP BY d.year, p.categoryORDER BY d.year, total_sales DESC;

Slowly Changing Dimension (Type 2)

Track historical changes by inserting a new row and expiring the old one.

sql

-- Expire the current rowUPDATE dim_customerSET end_date = CURRENT_DATE, is_current = FALSEWHERE customer_id = 42 AND is_current = TRUE;-- Insert the new versionINSERT INTO dim_customer (customer_id, name, city, start_date, end_date, is_current)VALUES (42, 'Jane Doe', 'Austin', CURRENT_DATE, NULL, TRUE);

OLAP Operations

Common ways analysts navigate a multidimensional cube.

Roll-up- Aggregate data by climbing up a hierarchy (e.g., day -> month -> year)
Drill-down- Move from summarized to more detailed data (year -> quarter -> day)
Slice- Filter the cube to a single value on one dimension (e.g., Year = 2024)
Dice- Filter using multiple dimensions to produce a smaller sub-cube
Pivot (rotate)- Reorient the cube's axes to view data from a different perspective

ETL vs. ELT

Two approaches to loading data into a warehouse.

ETL- Extract, Transform, Load: transform data in a staging area before loading into the warehouse
ELT- Extract, Load, Transform: load raw data first, transform inside the warehouse using its compute (common with Snowflake, BigQuery)
Idempotency- Pipelines should be safely re-runnable without duplicating data
CDC (Change Data Capture)- Technique for incrementally capturing only changed source rows instead of full reloads

Pro Tip

Design fact tables around a single, well-documented grain before adding dimensions -- changing the grain later forces a rebuild of every downstream report.

Was this cheat sheet helpful?

Google Sheets Formulas Cheat Sheet

Feature Selection Techniques Cheat Sheet

Explore Topics

#DataWarehousingConcepts#DataWarehousingConceptsCheatSheet#DataScience#Intermediate#CoreConcepts#StarSchemaQuery#Slowly#Changing#MachineLearning#DevOps#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services