Dimensional Modelling
Facts, dimensions, grain, and the star schema's continued relevance.
4 to work through
-
beginner Multiple choice
An order-lines fact table holds 40 million rows per day for three years across 18 columns and is partitioned by order date. In Parquet it averages roughly 36 bytes per row compressed and columns are of similar width. A dashboard query reads three columns over the last 90 days. Roughly how many bytes does it scan?
2 min answer -
intermediate Multiple choice
A BI team replaces a 9-table star schema with a single denormalised table of 240 columns because dashboards were slow and analysts kept joining at the wrong grain. Six months later the table is 4 TB across roughly 3 billion rows, the nightly build takes 5 hours, and correcting one product's category requires a full rebuild. Which property did the flattening actually trade away?
3 min answer -
intermediate
When is dimensional modelling still the right approach, and what does it cost?
2 min answer -
advanced
An interviewer says — design the data model behind a subscription business's revenue reporting. Finance restates prior periods when a contract is amended and the board pack must be reproducible months later. Where do you take this?
3 min answer
3 terms in this topic
Conformed Dimension
A dimension defined once and used identically by every fact table, so that measures from different processes can be filtered and grouped together wit…
patternDimensional Model
Organising analytical data as fact tables of measurements surrounded by dimension tables of descriptive context.
practiceGrain Declaration
Stating exactly what one row of a fact table represents, before any column is chosen, because every later decision depends on it.
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.
Data Vault Modelling
Hubs, links and satellites, and the auditability and load parallelism they buy.
Slowly Changing Dimensions
Overwriting, versioning or timestamping attribute history, and the reporting each enables.
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.