Warehouses

Warehouses and analytical stores are where data goes to be queried, and the modern versions differ from traditional databases in ways that change how you should use them. They are column-oriented, they scale compute separately from storage, and they charge in ways that reward some patterns and punish others severely.

The pricing model is the thing to understand first, because it determines cost far more than data volume. Some charge for data scanned per query, some for compute time while a cluster is running, some a flat rate for provisioned capacity. Under scan-based pricing, a single badly written query against a large table can cost more than a month of storage, and a dashboard refreshing that query hourly is a genuine budget event. Under time-based pricing, the failure is a cluster left running overnight for no one.

The techniques that control cost are the same ones that make queries fast, which is convenient. Partitioning tables by date so a query for last week reads last week rather than five years. Clustering on the columns you filter by. Selecting the columns you need instead of everything, which matters enormously in a column store and not at all in a row store. Materialising expensive aggregations that many dashboards share rather than recomputing them per view.

On modelling, the durable advice is to keep raw data raw and build derived layers on top of it. A raw layer that is an untouched copy of the source, a cleaned layer with types and naming fixed, and a presentation layer with business definitions applied, gives you somewhere to fix a mistake without re-ingesting and somewhere to change a definition without touching the source.

Governance becomes a real requirement faster than expected. Who can query what, whether personal data is isolated and access-logged, and how long raw data is retained are all questions that arrive with the first customer who asks, or the first regulation that applies.

The lakehouse-style options, storing open table formats in object storage with multiple engines able to query them, are worth watching because they reduce the lock-in that has characterised this category. See [pipelines](/categories/pipelines) for getting data in and [dashboards](/categories/dashboards) for getting it out.