Slowly Changing Dimensions
Overwriting, versioning or timestamping attribute history, and the reporting each enables.
5 to work through
-
intermediate
A "customers by segment" dashboard tile reports 104,300 where the customer table holds 100,000 rows. Revenue on the same dashboard is also about 4% high. The join looks correct, the pipeline is green, and re-running changes nothing. What is happening?
3 min answer -
intermediate
A customer's attribute changes. Which slowly-changing-dimension approach should be used, and what decides it?
2 min answer -
intermediate
A retail platform's product attributes change over time, and historical reports must reflect the values as they were. What modelling approach handles this?
2 min answer -
intermediate Multiple choice
The business asks why the regional sales report changed for a period that closed six months ago. What happened, and what is the fix?
2 min answer -
advanced
A customer dimension has always been Type 1 - attributes overwritten in place. Compliance now requires every order to report the segment and region the customer had at the time of the order, three years back. 140 reports read the dimension and 2.1 billion fact rows join it on the natural customer key. Sequence the conversion under live reporting.
3 min answer
4 terms in this topic
Attribute History Strategy
The per-attribute decision about whether history is overwritten, versioned or kept alongside the current value, which is a business question rather t…
conceptFact Grain Multiplication
Silent duplication of fact rows when a fact is joined to a versioned dimension without an effective-date predicate, inflating every additive measure …
patternSlowly Changing Dimension
A modelling technique for handling attributes that change over time, where the choice determines whether historical reporting stays correct.
patternSurrogate Key Binding
Resolving which version of a dimension row a fact belongs to at load time and storing that version's key on the fact - which is what freezes a histor…
Neighbouring topics
Data Platform Architecture
General material on designing the analytical data estate end to end.
Medallion Architecture
Bronze, silver and gold layers, and what each layer is allowed to guarantee.
Open Table Formats
Iceberg, Delta and Hudi — transactions, snapshots and time travel over object storage.
Warehouse, Lake & Lakehouse
Three answers to where analytical data lives, and the workloads that separate them.
Storage Layout & Partitioning
Partition keys, clustering, and the scan the query planner is left able to skip.
File Formats & Compaction
Columnar formats, the small-file problem, and the maintenance nobody schedules.
Ingestion Patterns
Full load, incremental, append-only and merge, and the source system each one suits.
CDC Pipeline Design
Building on a change stream: snapshot plus delta, tombstones, and merge into the target.
Batch Orchestration
DAGs, dependencies, retries, and the difference between a schedule and an orchestration.
Workflow Schedulers
Airflow, Dagster and their kin — where the control plane sits and what it can recover.
Transformation Frameworks
Declarative SQL transformation with tests, lineage and versioned models.
Dimensional Modelling
Facts, dimensions, grain, and the star schema's continued relevance.
Data Vault Modelling
Hubs, links and satellites, and the auditability and load parallelism they buy.
Analytics Cost Control
Scanned bytes, idle warehouses, and the query nobody knew was running hourly.
Workload Isolation
Keeping an analyst's query off the pipeline's compute, and both off the dashboard's.
Data Platform Tenancy
Multiple domains on shared storage and compute, with separable access and cost.
Reverse ETL
Pushing modelled analytical data back into operational systems, and who owns it then.
Data Virtualisation
Querying across sources without moving data, and the performance ceiling that imposes.
Warehouse Migration
Moving off a legacy warehouse with thousands of reports pointed at it.