Data Modelling
Part of Data warehouses and analytical storage
Choosing warehouse requirements from reporting workloads
Build a testable warehouse brief from report queries, refresh times, history, concurrent use and acceptance checks.
Base warehouse requirements on the reports and preparation jobs the system must serve. Record what they read, when they run, how current data must be, and how much work may overlap. In an evaluation, a representative query, a busy-period schedule and an agreed acceptance target are more useful than “fast and scalable”.
Make a workload register
Start with maintained reports and recurring preparation jobs. Then add likely ad hoc analysis.
| Field | What to record |
|---|---|
| Reader and decision | Who uses the result and when it must be ready. |
| Source and history | Source systems, required past periods, current volume and expected growth. |
| Query shape | Filters, joins, grouping, level of detail and approximate result size. |
| Schedule | Refresh times, interactive use and overlap with other jobs. |
| Freshness | Latest source event the report must include and how the cutoff appears. |
| Access | Who may query the data and which records they may see. |
| Acceptance check | Agreed figure, acceptable wait and measurement conditions. |
Use observed values where available; label forecasts as assumptions. Row count alone says little about query demand: a selective lookup and a broad historical aggregation can use the same table differently.
Turn patterns into testable needs
If most reports filter to recent periods, require a way to check whether the platform can limit the data read. In BigQuery, partition pruning depends on a qualifying filter on the partition column. Do not assume a partitioned table will reduce work for queries that cannot use that filter.
If a daily load overlaps with dashboard use, test that combination. If finance must reproduce a closed month while source records change, specify a history and correction rule. If teams need different levels of detail, define which data each may access. These are requirements; a service tier or named feature is one possible way to meet them.
Set trial conditions in advance
Choose a small set of representative work: a routine dashboard query, a wide historical query, a preparation job and an overlapping busy period. State the expected result, acceptable completion time, maximum source lag and any operational constraint. Record the data volume, configuration and concurrent work when measuring.
A trial result applies only to its sample and settings. A small extract, cached answer or quiet period does not establish performance at production scale. Record checks that could not be reproduced and the evidence still needed.
Keep the brief usable
Give each requirement a business reason, an owner and a verification method. Separate launch needs from possible later work. Revise the brief when new reports change history, concurrency or access needs. It should let the team compare storage approaches against the same reporting work.
