Storage Layout & Partitioning
Partition keys, clustering, and the scan the query planner is left able to skip.
5 to work through
-
beginner Multiple choice
A new engineer asks why the 8 TB events table in the lakehouse has no index on customer_id when the same column is indexed in the operational Postgres the data came from. What is the actual reason?
2 min answer -
intermediate Multiple choice
A 4 TB events table serves three query patterns: 80% ask for one tenant over the last 7 days, 15% look up a single event by id, and 5% scan one product across all history. The table currently has no partitioning. Which layout should the team adopt?
2 min answer -
intermediate
A 6 TB events table is partitioned by `customer_id`. Queries are slow and the storage layer complains. What is wrong?
2 min answer -
advanced
A platform's analytical queries scan far more data than they need. Which storage layout decisions fix this?
2 min answer -
advanced
An analytical query scans far more data than it needs. Before adding compute, what layout decisions should be examined?
2 min answer
3 terms in this topic
Clustering Decay
The gradual loss of data-skipping as newly written files overlap the sort order of existing ones - so query cost rises month after month with no chan…
conceptData Layout
How table data is physically organised into partitions and files, which determines how much can be skipped at query time - and is the dominant factor…
conceptPartition Pruning
The query engine skipping entire partitions whose values cannot match the filter, which is where most large-table query performance comes from.
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.
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.
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.