Data warehouse
A separate home for historical data from many systems, built for heavy reporting without slowing the production database.
See it
What it is
A data warehouse is a database kept apart from production systems and shaped for analytics across lots of historical data. Pipelines copy data from the application database, billing platform, support tools, and other sources into one queryable place. BigQuery, Snowflake, and Amazon Redshift are familiar warehouses, usually backed by columnar storage.
Reach for one when a report needs to join several systems, scan months or years, or run without competing with customer-facing transactions. Teams often keep raw source-shaped tables, then use dbt to build cleaned fact and dimension tables in the star-schema style Ralph Kimball defined, so business definitions stay stable.
Gotcha: the warehouse is a delayed copy, not the transactional source of truth. Late records, deleted source rows, timezone mistakes, and changing definitions can all make a polished dashboard wrong. Track freshness and lineage, reconcile important totals back to source systems, restrict sensitive columns, and put cost limits around queries that can scan the entire history.
Ask AI for it
Build a BigQuery warehouse model for orders. Land immutable source rows in a raw dataset, then use a dbt incremental model with unique_key='order_id' and is_incremental() to MERGE updates into a typed fact_orders table. Partition the table by DATE(created_at), cluster by tenant_id and status, and add dbt tests for unique, not_null, relationships, and accepted_values. Publish source-freshness checks, reconcile daily order count and revenue against PostgreSQL, and include a backfill command plus a policy for propagating source deletions.