Data warehouse design best practices for the AI era are mostly the established ones, applied with more discipline, because models and agents now read the same tables as dashboards. The decisions that carry the weight: declare the grain, keep a dimensional core under the quality layers, model history as Type 2 rows, define metrics as catalog objects, and give embeddings their own keyed tables. Kimball Group calls declaring the grain "the pivotal step in a dimensional design". Meaning matters too: on the Spider 2.0 benchmark (2024), 632 enterprise text-to-SQL problems, an agent based on o1-preview "successfully solves only 21.3% of the tasks." If a language model will read your warehouse, the design has to tell it what a row means and what a metric means.
What Data Warehouse Design Best Practices Mean in the AI Era
Data warehouse design best practices are the decisions about grain, keys, history, metric definitions and physical layout that make a warehouse answer the same question the same way for every consumer, whether that consumer is an analyst, a dashboard, a training pipeline or an AI agent writing SQL.
Most published lists of data warehouse design principles would read the same in any year. The principles are not new; the readers are. A training pipeline reads rows, not totals, and asks how things stood on a past date. An agent writing SQL works from metric definitions instead of asking an analyst.
| Decision | What it meant for BI | What AI adds | Where it lives |
|---|---|---|---|
| Grain | One row per business event at the level reports need | Models learn from rows; aggregates hide the signal | Fact table definition |
| Dimensional core | Star schema for filtering and summarizing | One definition of customer, product and time for every model | Gold or presentation layer |
| Conformed dimensions | Drill across business processes | Shared features across models and teams | Bus matrix, catalog |
| History | Type 2 for accurate historical reporting | Training sets rebuilt as of label time, without leakage | Dimension tables with effective dates |
| Metric definitions | Measures in a BI tool or report | Agents and dashboards read one governed definition | Semantic or metric view in the catalog |
| Vectors and documents | Not in scope | Embeddings keyed to entities, versioned by model | Satellite tables beside the model |
| Physical layout | Partition and cluster for dashboard filters | Full-range training scans isolated from BI | Partitions, clustering, compute pools |
Our list, not a standard. Each row is a decision someone owns.
These practices concern the data model: what a row means, how history is kept, where a metric is defined, and where an embedding lives. They apply whether the engine is a warehouse or a lakehouse, a question we treat separately in our comparison of when a warehouse or a lakehouse should hold the model. If you want this designed and built rather than explained, that is what our data warehouse design services cover.
Start With the Business Process and Declare the Grain
Kimball Group's four-step dimensional design process runs in a fixed order: "Select the business process," "Declare the grain," "Identify the dimensions" and "Identify the facts." Business representatives "must participate," so the first two steps happen in a meeting, not in a modeling tool.
Kimball writes that "The grain declaration becomes a binding contract on the design." A useful grain statement is one sentence a business owner can sign, such as "one row per order line, as it stood at shipment." Each fact table holds one grain; mixing order headers and order lines breaks the contract for every reader.
AI strengthens the case for atomic grain, "the lowest level at which data is captured by a given business process." In our experience it stops being optional once a model trains on the warehouse, because a model learns from rows, not sums. A month by product fact table suits a revenue dashboard and is useless for predicting a customer's next transaction. Keep the atomic table as the base and build summaries separately; Kimball treats summary grains as performance tuning.
Keep a Dimensional Core Under the Quality Layers
Databricks describes the medallion architecture as "a series of data layers that denote the quality of data stored in the lakehouse," from raw bronze to gold "aggregated data tailored for analytics and reporting." Databricks' own documentation then adds: "The gold layer is where you'll model your data for reporting and analytics using a dimensional model." Bronze, silver and gold say how clean data is, not what a row means. Without a dimensional model, gold becomes a pile of wide tables where every team computes revenue differently.
Microsoft's modeling guidance calls star schema "a mature modeling approach widely adopted by relational data warehouses": "Dimension tables enable filtering and grouping. Fact tables enable summarization."
Conformed dimensions pay off twice. Kimball's bus architecture describes them as "common, standardized, master dimensions" that "support the ability to drill across and integrate data from multiple business processes." For AI, a conformed customer dimension is also one customer definition for every model; without it, each ML team builds its own.
Denormalization is a physical decision on top of the logical model. Google's BigQuery page on nested and repeated fields says "The recommended way to denormalize data in BigQuery is to use nested and repeated fields," and also warns: "In some circumstances, denormalizing your data and using nested and repeated fields doesn't result in increased performance." Keep the logical star; flatten per engine where measurements justify it. Data Vault fits where auditability and changing sources dominate, and its presentation layer usually ends up dimensional.
Model History You Can Reproduce, Not Just Query Back
In Kimball's definition of slowly changing dimension type 2, changes "add a new row in the dimension with the updated attribute values," and "A minimum of three additional columns should be added": a row effective date, a row expiration date and a current row indicator.
Those columns are what point-in-time correctness needs. The Databricks page on time series feature tables describes training sets built from "feature values as of the time each label observation was recorded," joined AS OF that time to prevent data leakage. The same principle that feature stores enforce applies to the dimensional model: a Type 2 dimension lets you join each label to the customer as they were that day. A Type 1 warehouse, which overwrites attributes, cannot rebuild last year's training set.
Platform time travel does not replace this. In Databricks table history, "Table history retention is determined by the table setting logRetentionDuration, which is 30 days by default," while time travel reaches back about 7 days by default, the deleted file retention period. BigQuery time travel uses a window that "covers the past seven days by default," configurable "from a minimum of two days to a maximum of seven days." Both are an operational safety net, not a modeled history: time travel restores a table from last week, a Type 2 dimension restores the business from last year.
Put Metric Definitions Where People and Agents Both Read Them
In our view, the newest of the data warehouse design best practices is treating a metric definition as a database object, with an owner, a version and access rules. Benchmarks show why a raw schema is not enough. On the BIRD benchmark (NeurIPS 2023), 12,751 question and SQL pairs over 95 databases totaling 33.4 GB, "even the most effective text-to-SQL models, i.e. ChatGPT, only achieves 40.08% in execution accuracy, which is still far from the human result of 92.96%." Models have improved since. The remaining gap is meaning the schema does not carry, such as which customers count as active.
Snowflake semantic views hold "semantic business concepts" as logical tables, relationships, facts, dimensions and metrics. Snowflake states that they "improve accuracy by combining LLM reasoning with rule-based definitions." Databricks metric views work by "separating measure definitions from the fields (also called dimensions) used to group, filter, and aggregate them," unlike standard views, which "lock in aggregations and groupings at creation time." Databricks recommends synonyms "to improve agent accuracy."
For the design, a metric definition belongs to the model, not to a report, and each metric has one owner. Governance comes down to the catalog holding these definitions, so a dashboard and an agent compute revenue from one rule. That a semantic layer narrows the benchmark gap is our conclusion, backed by vendor statements, not by a published benchmark.
Place Vectors Beside the Model and Lay Out Storage for Two Readers
An embedding depends on the model that produced it. Our rule: store embeddings in a satellite table keyed to the entity, with the source document identifier, the embedding model version and a timestamp, never as a column of a fact table. Switching embedding models then adds a version to the satellite instead of forcing a rebuild of the core.
As of Snowflake's current documentation on vector data types, a VECTOR holds 32-bit integers or floats with "a maximum value of 4096" dimensions, and "Vectors aren't supported as clustering keys." Keys and dates organize the table, so business filters need ordinary columns. Google's page on BigQuery vector search adds that "Without a vector index, these functions use brute force search to measure distance for every record," and pre-filtering is optimized only for partitioned and stored columns. Partition choice is a modeling decision.
Classic physical rules still apply. AWS's Amazon Redshift table design guidance notes that "certain key table design decisions heavily influence overall query performance" and lists rules such as "Choose the best sort key" and "Use the smallest possible column size." AI adds a second reader: BI reads narrowly and often, training scans full date ranges. Our recommendations:
- Partition on the column both readers filter on, usually the event date.
- Cluster on the dimension keys used most in filters.
- Give training and BI separate compute pools, so a full scan does not slow the dashboards.
None of these data warehouse design best practices is new. What is new is that a model, not a person, now notices when they are missing.
At DS Stream we design the model with the client's business owners, starting from grain, conformed dimensions, history and metric definitions, then build the pipelines and platform work underneath the model in the client's own cloud. If you are about to start, bring us one business process and the grain you cannot agree on.
FAQ
What are the most important data warehouse design best practices?
Declare the grain first, keep a dimensional core (a star schema) under the quality layers, model history as Type 2 rows, define metrics as catalog objects, and keep vectors in keyed satellite tables. The test for each: every reader gets the same answer.
How should a data warehouse handle history for machine learning?
Model it as slowly changing dimension type 2, with effective and expiration dates, so training sets can be built with an as-of join on each label's timestamp. Platform time travel covers days by default (a 30-day log in Delta, two to seven days in BigQuery) and serves audit and rollback.
Why does a data warehouse need a semantic layer for AI agents?
Metric definitions and business synonyms are not in the schema. ChatGPT reached 40.08% execution accuracy against 92.96% for humans on BIRD (2023), and an o1-preview agent solved 21.3% of Spider 2.0 tasks (2024). Semantic views and metric views move definitions into the catalog, where dashboards and agents read the same rule.
Should embeddings be stored in the data warehouse?
Yes, in a separate table keyed to the entity, with the embedding model version and a timestamp. BigQuery runs brute force search without a vector index, so business filters belong in ordinary partitioned columns.
