Cheat Sheets

Data Warehouse Cheat Sheet

Quick reference for data warehouse concepts — OLTP vs OLAP, dimensional modeling, star and snowflake schemas, data marts, SCD types, ETL/ELT patterns, OLAP operations, partitioning, data quality, and governance.

SQLPythonPower BIDAXM CodeMicrosoft FabricSnowflakeDatabricksGitVS CodeVimHomebrewHTMLCSS3JSONJavaJavaScriptAI PromptsMarkdownData Warehouse

Model. Load. Analyze. Govern.

Core Concepts
OLTP vs OLAPData MartFact TableDimension TableGrainSurrogate KeyMeasure Types
Schema Patterns
Star SchemaSnowflake SchemaGalaxy SchemaData VaultOne Big Table (OBT)
Slowly Changing Dimensions (SCD)
SCD Type 0 — FixedSCD Type 1 — OverwriteSCD Type 2 — History RowSCD Type 3 — Prior ColumnSCD Type 4 — History TableSCD Type 6 — Hybrid
ETL vs ELT
ETL PatternELT PatternMedallion ArchitectureIncremental LoadFull Refresh vs Incremental
OLAP Operations
Roll-UpDrill-DownSliceDiceGROUPING SETS
Partitioning & Clustering
Range PartitioningList PartitioningPartition PruningClustering / Sorting
Aggregation Patterns
Aggregate TableMaterialized ViewWindow AggregationsPeriod-over-Period
Data Quality
Null ChecksDuplicate DetectionReferential IntegrityRange & Sanity ChecksData Purging
Performance Patterns
Columnnar StoragePredicate PushdownJoin Order OptimisationAvoid Functions on Indexed Columns
Governance & Lineage
Audit ColumnsData Catalog MetadataRow-Level SecurityColumn Masking