Data Warehouse Template for Engineering Teams

Build a warehouse when analytical queries slow down the production database, when you need to join data across multiple source systems, when analysts need historical snapshots that the production database overwrites, or when query complexity exceeds what the operational schema supports. For most SaaS products, the inflection point is around 10M rows of event data or 5+ analysts querying regularly. The [Product Analytics Handbook](/analytics-guide) covers the measurement strategy that drives warehouse requirements.

Star schema (facts and dimensions) is the standard for analytical warehouses. It is optimized for the aggregation queries that dashboards and reports run. Normalized schemas (3NF) are better for operational databases where data integrity and write performance matter. Some modern warehouses use a hybrid: normalized in the transformed layer, star schema in the presentation layer. Start with star schema for your marts and adjust if you encounter specific limitations.

Late-arriving data (events that arrive after the batch window) needs a reconciliation strategy. Options: (1) re-process the affected partition when late data arrives, (2) run a daily correction job that fills gaps from the previous 48-72 hours, or (3) use streaming ingestion to minimize the window. Document your late-data strategy in the source system inventory so consumers know the freshness guarantee.

For teams under 50 people with moderate data volumes (under 1 TB), BigQuery offers the best cost structure because you pay per query, not for always-on compute. Snowflake is better for teams that need fine-grained access control, multi-cloud support, or heavy concurrent query loads. Databricks is best if your primary use case is data science and ML, not BI dashboards. All three work for most B2B SaaS companies. Pick the one your data engineer knows best.

Three practices: (1) Require a documented business question for every new source ingestion. (2) Enforce naming conventions and testing requirements via dbt or your transformation tool. (3) Run quarterly audits to identify unused models and stale data. Delete aggressively. A lean warehouse that people trust is more valuable than a large one that nobody understands. ---