See it as a diagram
Everything below, as a diagram you can edit. Describe yours and see it in seconds.
No account needed · Editable canvas, not a picture
Start from a business process, not from a source system
The most common opening move is to list the source systems and give each one a landing schema. It feels like progress and it produces a warehouse shaped like your operational estate: one model per application, joined by whoever needs an answer. The alternative, argued by Ralph Kimball in The Data Warehouse Toolkit since its first edition in 1996, is to start from a business process the organization measures. Order to cash. Subscription billing. Support ticket resolution. Shipment tracking.
A business process gives you three things a source system cannot. It has a natural event, which becomes the grain. It has stakeholders who can say whether a number is right, which gives you an acceptance test. And it usually spans several source systems, which forces the integration work early rather than leaving it to the person who eventually has to join billing to CRM under deadline.
Pick one process for the first delivery. Not the most valuable one, the one with the cleanest event and a stakeholder who will actually look at the result. The second process is where the design gets tested, because that is when conformed dimensions stop being a theory.
The eight decisions, in order
One, the business process. Two, the grain: one sentence stating what a single fact row represents, written before any SQL. Three, the dimensions that apply at that grain, and which of them are conformed across processes. Four, the facts, which must all be true at the declared grain. Five, the history policy per dimension attribute: overwritten, or versioned. Six, the layer topology: how many layers between landing and consumption, and what each one is allowed to do. Seven, the physical containers: catalogs, databases, schemas, and which axis carries the environment. Eight, naming and ownership.
The order is not arbitrary. Decisions one to five are about meaning and are expensive to change once anything is built on them. Decisions six to eight are about packaging and can be refactored with rename statements and a migration window. Teams that invert the order spend their refactoring budget on the expensive half.
Write the first five down in a single file that lives next to the code, not in a slide deck. A design record of ten lines that a reviewer can diff is worth more than a forty-page document that nobody opens after the kickoff.
# models/marts/finance/_design.yml process: order_to_cash grain: one row per order line, per invoice event conformed_dimensions: [dim_date, dim_customer, dim_product] local_dimensions: [dim_payment_method] facts: [gross_amount, discount_amount, tax_amount, net_amount] history: dim_customer: type 2 on segment and billing_country, type 1 on everything else dim_product: type 2 on list_price, type 1 on name owner: finance-data freshness: daily by 06:00 UTC
Where a year usually goes
Three failure patterns account for most of the lost time. The first is modeling everything before delivering anything, which produces a model with no user and no feedback. The second is skipping the conformed date dimension and letting each mart define its own fiscal calendar, which shows up as two dashboards disagreeing about the same quarter. The third is letting the BI tool hold the business logic, so the definition of active customer lives in six different report filters and cannot be tested.
A fourth, quieter one: no written grain. Without a grain sentence in the repository, the first person to add a column at a different level of detail does it silently, and every measure built on that table becomes a guess. A uniqueness test on the grain columns catches this in CI on the day it happens rather than in a board meeting six months later.
Two pages that follow this one
Draw it before you build it, then keep the drawing true
A warehouse design is easier to argue about as a picture than as a document. Describe the intended platform in plain language and Datadef generates it onto an editable canvas: zones for the layers, real vendor icons for the storage and compute, labelled edges for what each hop does, and column-level lineage where a specific field needs tracing. Moving a box is faster than rewriting a paragraph, which is what you want during a design review.
Two node fields carry the first five decisions. The description holds the grain sentence, so a fact node reads as one row per order line, per invoice event rather than as a column list. The layer accent is a named property with a fixed vocabulary, source, ingest, bronze, silver, gold, consume, dbt, and table nodes infer it from their names, so a naming convention that has drifted shows up as a wrong colour before anybody reads the label.
The part that usually decays is the picture, not the decision. Connect the repository that holds the dbt project, the DDL, or the Terraform that provisions the platform, pick a branch or tag, and the daily sync regenerates the diagram and an architecture.md from the current source. Commits that change nothing structural do not redraw, and nodes you positioned by hand keep their position, so the picture stays recognizable across syncs. See how repository sync works.
FAQ
How long should warehouse design take before loading any data?
Do you still need dimensional modeling on a lakehouse?
Should business logic live in the warehouse or in the BI tool?
What is the first table to build?
How do you keep the design documented once delivery starts?