TL;DR
dbt data modeling means turning raw warehouse tables into a tested model of facts and dimensions, written as layered SQL SELECT statements. The tool builds that model, but people still decide its design. Start by writing the grain of each fact table as one plain sentence. Then give sources, staging, intermediate models and marts one job each, and join facts to dimensions on hashed surrogate keys. Keep dimension history with snapshots, and make large facts incremental only with a written plan for late data. Finally, define each metric once, in a mart or in MetricFlow, so every dashboard reports the same number.
Key Takeaways
Write the fact table’s grain as one sentence before you write any SQL. Give sources, staging, intermediate models and marts one job each. Use hashed surrogate keys, and keep the business key in the same row. Keep dimension history with snapshots and join each fact to the version valid at event time. Make big fact tables incremental only when you have a stated late-data policy. Define each metric once, in MetricFlow or in a mart, and never again in a dashboard. Watch on YouTube
Data Modeling Best Practices for AI: Dimensional, Data Vault & OBT
Kanerika compares dimensional models, Data Vault and one big table, and when each one holds up, the same choice every dbt project makes before its first mart.
A Confession About the First dbt Model Many Teams Write
Ask an analytics engineer about their first dbt project and you will often hear a confession. It usually involves one enormous model, something like orders_final.sql. That model read raw tables, joined order lines to orders, attached each customer’s current region and summed revenue.
It ran green every night for months. Yet every order with three lines counted its revenue three times.
The story above is a composite, but each mistake in it is common, and none of them is a dbt bug. The tool will happily build any SELECT statement you give it. So the quality of the model depends on decisions made before the SQL, about grain, keys, history and increments.
That is what dbt data modeling comes down to. If you need a refresher on what dbt is and how it runs , start there, then come back for the modeling. The rest of this guide walks one orders example from raw tables to a star schema, with the dbt feature that implements each decision.
What dbt Data Modeling Is and What dbt Does Not Decide for You
A dbt model is a SELECT statement saved as a .sql file. When you run it, dbt wraps the query in the right DDL and materializes the result as a view, a table or an incremental table.
Models call each other through ref() and read raw data through source(). From those calls, dbt draws the dependency graph and builds everything in the right order.
So dbt data modeling has two halves. The first half is design, meaning which business process you model, what one row of each table means and how history behaves.
That part is still a human decision, and no tool makes it for you. The second half is implementation, and that is where dbt earns its place.
dbt is also neutral about the method. You can build a Kimball star schema, a Data Vault or one wide table with it.
This guide uses Kimball dimensional modeling, because most BI tools and semantic models expect facts and dimensions. If you are still comparing products for the design half, our review of data modeling tools covers diagram-first options.
What dbt Replaces in a Classic Modeling Workflow
Classic modeling artifact What dbt gives you instead ER diagram drawn by hand A dependency graph generated from ref() and source() calls CREATE TABLE scripts A SELECT statement plus a materialization setting Separate data dictionary YAML descriptions and tests that sit next to the model Manual change scripts Version-controlled files reviewed in pull requests
One version note before the patterns. Since 16 September 2026, the name dbt refers to dbt v2 . The Apache 2.0 build of v2 is called dbt OSS, and as of November 2026 it is still an alpha release on PyPI .
As of November 2026, version 1 is still available as dbt Core, and PyPI lists 1.12.6 (uploaded 14 October 2026) as its latest stable release. The modeling patterns below apply to all three. Where a feature needs a minimum v1 release, such as YAML snapshots from v1.9, the section says so.
The Four Layers of a dbt Model: Sources, Staging, Intermediate and Marts
Layers keep a dbt project readable. Each one has a single job, so a reviewer can tell from the folder alone what a model is allowed to do. The pattern also mirrors the raw, cleaned and business-ready zones of a medallion architecture , although the mapping is loose rather than exact.
Layer Its one job Naming pattern Usual materialization Sources Declare raw tables loaded by your ingestion tool Declared in YAML, read with source() None (they already exist) Staging Rename, cast and clean one source table stg_[source]__[entity]s View Intermediate Reusable joins and reshaping for marts int_[entity]s_[verb]s Ephemeral or view Marts Facts and dimensions at a declared grain fct_ and dim_, or plain entity names Table or incremental
Sources and Staging: Clean Once, One Model per Table
dbt Labs’ staging guide sets three firm rules. Staging models map one to one with source tables, they are the only place source() appears, and they are built as views. The double underscore in stg_shop__orders separates the source system from the entity.
Staging is where you rename columns, cast types and convert units. It is not the place for joins or business rules. Once a business rule creeps into staging, every model downstream inherits it silently, and nobody thinks to look for it there.
Intermediate: Joins and Reshaping That Marts Reuse
Intermediate models hold the logic that more than one mart needs. They are named for what they do, such as int_order_items_summed_to_orders, and grouped into business-area folders. The intermediate guide notes that one popular option is to materialize them ephemerally, so they never appear as objects for end users to query.
Marts: Facts and Dimensions at a Declared Grain
Marts are the tables people query. The dbt guide suggests starting each one as a view, moving to a table when it gets too slow to query. Go incremental only when the table is too slow to build.
Snapshots sit beside these layers as stored history, so treat them as a source for marts rather than a fifth layer.
models/
staging/
shop/
_shop__sources.yml
stg_shop__orders.sql
stg_shop__order_items.sql
stg_shop__customers.sql
intermediate/
finance/
int_order_items_summed_to_orders.sql
marts/
finance/
fct_orders.sql
dim_customer.sql
dim_date.sql
_finance__models.yml
snapshots/
snp_shop__customers.ymlNaming: fct_ and dim_ or Entity Names?
Here dbt Labs disagrees with itself, which is useful to know. The marts guide names marts in plain English after the entity that forms the grain, such as customers or orders. It also recommends wide, denormalized marts, and it says so directly, as advice given unlike in a traditional Kimball star schema.
Meanwhile, dbt’s own Kimball dimensional model tutorial uses fct_ and dim_ prefixes throughout. Both choices work, so pick one and enforce it. This guide uses fct_ and dim_ because the topic here is star schema modeling.
The prefixes also tell a reader each table’s role at a glance. Our dbt best practices guide covers how to make naming rules checkable by a linter in CI.
Design the Star Schema Before You Write SQL
A star schema has one fact table in the middle and dimension tables around it. Facts hold the measurable events, such as orders, payments or shipments.
Dimensions hold the context you filter and group by, such as customer, product and date. The data modeling best practices that apply in any tool apply here too, and dbt adds no shortcut around them.
Pick the Business Process and Write the Grain as a Sentence
Start with one business process, then write the grain in plain words. “One row per order” is a grain. “One row per order line” is a different grain, and it answers different questions.
The choice decides which dimensions can attach. Product belongs on an order-line fact, because one order can hold many products.
At order grain, product has no single value, so it stays off the table. Measures follow the same logic, since order revenue adds up across any dimension while a stock balance only adds up across products, never across dates.
Write the sentence into the model’s YAML description. Then a reviewer can test every join against it, and the grain stops living only in one engineer’s head.
Business Keys, Surrogate Keys and Version Keys
A business key is the identifier the source system uses, such as customer_id. A surrogate key is the warehouse’s own identifier for a row.
The dbt Kimball tutorial builds surrogate keys with the dbt_utils package , whose generate_surrogate_key macro hashes the columns you pass it. It works the same way across databases.
Keep the business key next to the surrogate key in every dimension, because people debug with the ID they recognize. Also, be honest about what hashing does. It turns columns into a stable key.
However, it cannot decide that customer 42 in the shop and customer A-17 in the CRM are one person. That identity rule is a business decision you write in an intermediate model.
Once a dimension keeps history, you need one more key. The durable customer_id says who the customer is, while a version key says which version of that customer was true at a given time. Facts should join on the version key.
Conformed Dimensions and Which Dimensions Belong on the Fact
A conformed dimension is one shared table, such as dim_customer or dim_date, that every fact uses. Because orders, returns and support tickets then group customers the same way, reports built on different facts still agree.
If you are weighing a star against a normalized layout, our comparison of star schema vs snowflake schema covers the trade-offs. Most dbt marts land on the star side.
For the running example, fct_orders sits at order grain with two dimensions around it, dim_customer and dim_date. Product would attach only to a separate line-level fact. The overall data warehouse architecture may hold many more facts, yet each one starts with this same sentence about grain.
Worked Example: From Raw Orders to fct_orders and dim_customer
The example follows a web shop with three raw tables, orders, order_items and customers. Our goal is a fact table at order grain with a customer dimension and a date dimension. The finished graph reads sources, then stg_shop models, then int_order_items_summed_to_orders, then dim_customer, dim_date and fct_orders.
Declare Sources and Stage Them
First, declare the raw tables once in YAML, so every model reads them through source() and dbt can track them in lineage. Then build one staging view per table that only renames and casts.
# models/staging/shop/_shop__sources.yml
sources:
- name: shop
schema: raw_shop
tables:
- name: orders
- name: order_items
- name: customers
-- models/staging/shop/stg_shop__orders.sql
select
id as order_id,
customer_id,
cast(created_at as timestamp) as ordered_at,
lower(status) as order_status,
updated_at
from {{ source('shop', 'orders') }}Aggregate Order Items to Order Grain Before the Join
This step prevents the bug from the confession above. When you join order_items to orders, each order repeats once per line, and any order-level amount gets multiplied. So sum the lines to one row per order first, and only then join.
-- models/intermediate/finance/int_order_items_summed_to_orders.sql
select
order_id,
count(*) as line_count,
sum(quantity * unit_price) as gross_revenue,
sum(discount_amount) as discount_amount,
sum(quantity * unit_price)
- sum(discount_amount) as net_revenue
from {{ ref('stg_shop__order_items') }}
group by order_idPick one authoritative revenue measure while you are here. If the order header also carries a total, decide which one the business trusts, and document why. Two revenue columns with slightly different numbers will both end up on dashboards.
Build dim_customer and dim_date
The first version of dim_customer holds the current state of each customer, keyed by a hashed surrogate key. The section on snapshots later turns it into a versioned dimension. For dates, dbt_utils also ships a date_spine macro that generates one row per day, which is a quick start for dim_date.
-- models/marts/finance/dim_customer.sql
select
{{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_key,
customer_id,
customer_name,
region,
segment
from {{ ref('stg_shop__customers') }}
union all
select 'unknown', null, 'Unknown customer', 'Unknown', 'Unknown'The last row is a default “unknown” member. Orders whose customer cannot be matched point at it, so they stay in revenue totals instead of vanishing from an inner join.
Build fct_orders and Test Its Grain
Now the fact table joins the summed lines to the order header and looks up each dimension key. Because the lines were aggregated first, the join is one to one.
-- models/marts/finance/fct_orders.sql
select
{{ dbt_utils.generate_surrogate_key(['o.order_id']) }} as order_key,
o.order_id,
coalesce(c.customer_key, 'unknown') as customer_key,
cast(o.ordered_at as date) as order_date,
o.order_status,
i.line_count,
i.gross_revenue,
i.discount_amount,
i.net_revenue
from {{ ref('stg_shop__orders') }} as o
join {{ ref('int_order_items_summed_to_orders') }} as i
on o.order_id = i.order_id
left join {{ ref('dim_customer') }} as c
on o.customer_id = c.customer_id
# models/marts/finance/_finance__models.yml
models:
- name: fct_orders
description: One row per order.
columns:
- name: order_key
data_tests:
- unique
- not_null
- name: customer_key
data_tests:
- not_null
- relationships:
arguments:
to: ref('dim_customer')
field: customer_keyThose tests prove the grain. A unique, non-null order_key means one row per order, and the relationships test means every fact row finds its customer. Note that test inputs sit under arguments, a property available from v1.10.5 and the default from v1.10.8, as dbt’s data tests documentation shows.
Before you call the model done, run through a short acceptance list.
The grain sentence is in the YAML description. The primary key is unique and not null. Every foreign key passes a relationships test. Total net revenue matches the source system for a sample month. Unmatched rows land on the unknown member, not in the void. Deeper test design, such as freshness, volume and anomaly checks, belongs in a wider data quality framework . For modeling, the grain and key tests are the ones that tell you the shape is right.
Checklist
Data Engineering Checklist for Enterprise Teams
A practical list of pipeline, modeling, orchestration and monitoring checks to run alongside your dbt acceptance tests.
Get the Checklist → Slowly Changing Dimensions With dbt Snapshots
Customers move, change segment and switch account managers. If dim_customer only keeps today’s values, last year’s revenue gets reported under this year’s segments. A type 2 slowly changing dimension fixes that by keeping one row per version, and dbt implements it with snapshots.
From dbt v1.9, snapshots are configured in YAML . Each run compares the source with the stored history, closes changed rows and inserts new versions with dbt_valid_from and dbt_valid_to columns.
# snapshots/snp_shop__customers.yml
snapshots:
- name: snp_shop__customers
relation: source('shop', 'customers')
config:
unique_key: customer_id
strategy: timestamp
updated_at: updated_at
dbt_valid_to_current: "to_date('9999-12-31')"
hard_deletes: invalidateTimestamp or Check Strategy?
Strategy What it needs What it detects Use it when timestamp A reliable updated_at column Rows whose updated_at moved forward The source stamps every change check A list of check_cols Rows where a listed column changed The source has no trustworthy timestamp
Both strategies share one blind spot. They only see the state at each run, so a customer who changes segment twice between runs leaves one version, not two. If you need every change, capture it upstream with change data capture.
Snapshot the source, or a light staging model, rather than a mart. Marts get rebuilt and refactored, while snapshots hold history you cannot regenerate.
Also decide how deletions behave, since hard_deletes can ignore them, close the row with invalidate or add a new_record row flagged with dbt_is_deleted. That choice is a business policy, so write it down.
From Snapshot to a Versioned dim_customer
Next, build dim_customer from the snapshot instead of from staging. Each version gets its own surrogate key, hashed from customer_id and dbt_valid_from, plus valid_from and valid_to columns renamed from the snapshot’s meta columns. Setting dbt_valid_to_current to a far-future date keeps the current row’s valid_to non-null, which makes the next join simpler.
Join Each Order to the Customer Version Valid at Order Time
This join is where many SCD2 models go wrong. Joining on customer_id alone either duplicates orders, one per version, or picks today’s version for everything. So the fact must look up the version that was valid when the order happened.
left join {{ ref('dim_customer') }} as c
on o.customer_id = c.customer_id
and o.ordered_at >= c.valid_from
and o.ordered_at < c.valid_toWith that join, a customer who moved from the SMB segment to Enterprise in June keeps their spring orders under SMB. One more rule applies here, because model contracts are not supported on snapshots. Put contracts on the marts built from them instead.
Incremental Fact Tables Without Losing Late Data
A fact table that rebuilds in two minutes should stay a table. Once the full rebuild gets too slow, an incremental model processes only new or changed rows and merges them in. The speed is real, but so is the risk, because an incremental model only knows about the rows its filter lets through.
Choose a Strategy Your Warehouse Supports
dbt ships five built-in incremental strategies , and support differs by adapter. Check the table before you design around one.
Strategy How it writes Snowflake Databricks BigQuery Fabric append Inserts new rows only Yes Yes No Yes merge Updates matches on unique_key, inserts the rest Yes Yes Yes Yes delete+insert Deletes matching keys, then inserts Yes Yes No Yes insert_overwrite Replaces whole partitions Yes Yes Yes No microbatch Processes one bounded time period per batch Yes Yes Yes Yes
One warning applies to all of them. A unique_key setting tells dbt how to merge, but it does not test that the key is unique. Keep the unique test on the column.
Late-Arriving and Corrected Facts
Orders get refunded, statuses change and payment files land a day late. A filter that only reads rows newer than the latest one already loaded will miss all of them. So give the filter a lookback window, as this Snowflake-flavored example does.
{{ config(
materialized='incremental',
unique_key='order_key',
incremental_strategy='merge'
) }}
select ...
from {{ ref('stg_shop__orders') }}
{% if is_incremental() %}
where updated_at >= (
select dateadd(day, -3, max(updated_at)) from {{ this }}
)
{% endif %}For large event-time data, microbatch gives you a cleaner pattern. You set event_time on the model and its direct parents, plus a batch_size such as day.
After that, dbt processes one bounded period per batch. Its lookback setting reprocesses earlier batches to catch late records, and the default is one batch.
Even so, a lookback is a policy, not a guarantee. Data that arrives later than the window still slips through.
Schedule a periodic full refresh or a targeted backfill. Also re-resolve the customer version key for any corrected order, because its order date may now point at a different version.
Where Metrics Live: Marts, the dbt Semantic Layer and MetricFlow
A metric is a calculation, such as net revenue or average order value, applied over a model. If each dashboard writes its own version, the numbers drift apart within weeks. So the modeling question is where a metric gets defined once.
Normalized or Wide Marts? The Semantic Layer Decides
dbt’s marts guide gives two different answers, and both are right. Without the Semantic Layer, it recommends wide, denormalized marts that BI users can query directly. With it, the advice flips, and marts should stay as normalized as possible so MetricFlow has the most room to build joins.
MetricFlow is the engine behind the dbt Semantic Layer, and its source code is Apache 2.0 . You describe entities, which are join keys, along with dimensions and metrics. Then MetricFlow writes the SQL, including the joins.
The hosted dbt Semantic Layer is a separate thing, a dbt platform feature on the Starter, Enterprise and Enterprise+ plans. Keep the two apart when you plan, because the open engine and the hosted service have different costs.
One Metric, Defined Once
From dbt v1.12, the semantic model sits inside the model’s own YAML entry. Here is net revenue defined once on fct_orders.
models:
- name: fct_orders
semantic_model:
enabled: true
name: orders_semantic_model
agg_time_dimension: order_date
columns:
- name: order_key
entity:
type: primary
name: order
- name: customer_key
entity:
type: foreign
name: customer
- name: order_date
granularity: day
dimension:
type: time
metrics:
- name: net_revenue
label: "Net revenue"
type: simple
agg: sum
expr: net_revenueA metric layer cannot rescue a badly grained fact, though. If fct_orders double counts, net_revenue double counts everywhere, just consistently.
Governed definitions also give AI assistants something reliable to query, and the dbt MCP server is how those tools connect. Teams on Microsoft tools get the same benefit from Direct Lake semantic models in Power BI , which sit on the same kind of star schema.
Watch on YouTube
LLM Hallucination in Enterprise Data: The Semantic Layer Fix
Kanerika explains why AI assistants give wrong numbers without governed definitions, and how a semantic layer gives them one trusted version of each metric.
Contracts, Versions and Access: Make Marts Safe to Depend On
Once other teams build on your marts, a renamed column can break dashboards you have never seen. Fortunately, dbt has three interface tools for that, and each protects something different.
Mechanism What it protects What it does not do Model contract Column names and data types, checked at build time Check the values inside those columns Model version Consumers during a breaking change, since old and new versions run side by side Decide when to retire the old version Access modifier Who may ref() the model, as private, protected or public Control database permissions Data tests Grain, keys and accepted values in the data Stop a schema change before the build
With a model contract enforced, every column’s name and data type must be declared. Rename net_revenue to total_revenue in fct_orders and the enforced contract fails the build, instead of a dashboard breaking the next morning. Contracts work on table, view and incremental models, but not on ephemeral models, Python models or snapshots. Access modifiers then decide reach, since private models stay inside their group and public ones can be used by any project.
Contract the marts other people query, and leave internal models flexible. These controls also feed the data lineage view that auditors ask for.
Modeling Mistakes That Change the Numbers Without an Error
Most of these bugs pass every run. The job is green, the dashboard loads and the number is wrong. Here are the six that cause the most damage.
Mistake Symptom on the dashboard Fix Fan-out join of lines to orders Revenue too high for multi-line orders Aggregate to the fact’s grain before joining Mixed grain in one fact Totals change with the filter you apply One grain per fact, written in the YAML Fact joined to the current dimension row Last year’s sales move to this year’s segments Snapshot plus an effective-date join Non-unique dimension key Fact rows duplicate after a join Unique and not-null tests on every key One mart that does everything Every change breaks something unrelated Split into staging, intermediate and marts Business logic hidden in staging Two marts disagree and nobody knows why Keep staging to renames and casts
A wide presentation table is not a mistake by itself. It is fine when its grain is stated and it is built from the star schema, not instead of it. Teams that hit several of these at once usually have a data pipeline problem upstream too, so fix the ingestion contract at the same time.
Kanerika Service
Data Engineering Services
Pipeline Audit and Design, Build and Modernize, and Ongoing Support, with structured layers from raw to refined and data contracts enforced across workflows.
Explore Data Engineering Dimensional Model, Data Vault or One Big Table in dbt?
dbt builds any of these three, so the choice depends on who uses the data and how often the sources change. For BI-facing models, the dimensional model is the default answer. The other two work best as a step before it or a view on top of it.
Approach Best for Cost How it shows up in dbt Dimensional (Kimball) BI, self-service analytics and semantic models Upfront design of grain and conformed dimensions fct_ and dim_ marts on top of staging Data Vault Many changing sources and full auditability More tables and more joins before anyone can report Hubs, links and satellites in an integration layer that feeds marts One big table One bounded use case, such as a single dashboard or ML feature set Hard to reuse across grains A wide mart built from the star schema
Data Vault usually feeds dimensional marts rather than replacing them, because business users still want facts and dimensions at the end. Our guide to Data Vault modeling explains hubs, links and satellites in more depth. As for one big table, it works well for one consumer, yet it gets painful the moment two teams need different grains.
Platform Notes: Snowflake, Databricks, Fabric and BigQuery
The design in this guide is portable, since grain, keys and layers mean the same thing on every warehouse. What changes is the plumbing, such as constraint enforcement, data types and the incremental strategies listed earlier.
Adapter Modeling note dbt-snowflake Merge is the usual choice for facts with updates dbt-databricks Models land as tables in the lakehouse dbt-bigquery Partition-based overwrite suits large event facts dbt-fabric Maintained by Microsoft, targets Fabric Data Warehouse, and Lakehouse has its own setup
The dbt-fabric adapter is maintained by Microsoft and connects to Fabric Data Warehouse . On Databricks, our walkthrough of dbt on Databricks covers the Databricks side in more depth.
Where the models run is often part of a bigger platform decision, which our comparison of data lakehouse vs data warehouse works through. It also fits into the wider modern data stack , where dbt sits between ingestion and BI. If your raw files land in a lake first, these data lake examples show what eight teams stored there and what it fed.
Kanerika works across three of these platforms, with Microsoft Fabric , Databricks and Snowflake practices. Kanerika is a Microsoft Solutions Partner for Data and AI, a certified Databricks partner and a Snowflake Select Tier Partner.
Datasheet
Build a Modern Data and AI Foundation with Databricks
Kanerika’s datasheet on building a governed data and AI foundation on the Databricks lakehouse, where many dbt projects run.
View the Datasheet → How Kanerika Helps Teams Model Data in dbt
Good models start as design work, and most of the effort sits in agreeing grain, keys and ownership before anyone writes SQL. Kanerika’s data engineering services are organized around three engagement models.
Pipeline Audit and Design. Map existing pipelines and gaps, define the target architecture and tooling, and deliver a build-ready technical blueprint.Build and Modernize. Rebuild legacy ETL on a cloud-native stack, implement lakehouse and streaming patterns, and hand off tested, production-ready pipelines.Ongoing Support. Extend your team with dedicated engineers who monitor pipeline health and data quality.The same service page describes structured layers from raw to refined, transformation work that is version controlled and testable, and data contracts enforced across workflows. Those are the habits this guide applies to dbt. Teams moving logic off older tools can also compare dbt vs Informatica vs Azure Data Factory before they commit.
The closest proof is semantic-model work rather than a dbt project. For a leading enterprise, Kanerika migrated semantic models from SSAS to Microsoft Fabric with an automated process. That process moved relationships, calculated tables, columns and security settings, then enabled Direct Lake mode.
The starting point will sound familiar to many dbt teams. Manual updates and troubleshooting were eating significant resources, and large, complex models had grown slow. Kanerika automated the transfer instead of rebuilding each model by hand, and Direct Lake mode cut data duplication.
The result was 40% less manual maintenance, a 25% increase in real-time analytics capability, 20% better data integration efficiency and 35% lower operational costs. The lesson carries over to dbt, because one governed model definition beats many copies of the logic.
Case Study
40% Less Maintenance: SSAS to Fabric Migration
Kanerika moved an enterprise’s semantic models from SSAS to Microsoft Fabric with Direct Lake mode, cutting manual maintenance 40% and operational costs 35%.
Read the Case Study → dbt Data Modeling Comes Down to Grain First
Every decision in this guide follows from the one before it. Pick the business process, write the grain, then choose keys. Next, give each layer one job, keep history with snapshots, add increments with a late-data plan and define metrics once on top.
dbt makes each of those steps versioned, tested and repeatable. The design, though, is still yours.
So the answer to how you model data in dbt is easy to state and harder to do. Put grain first and everything else in order.
Frequently Asked Questions
What is dbt data modeling?
dbt data modeling is the practice of turning raw warehouse tables into analytics-ready models, usually facts and dimensions, using SQL SELECT statements that dbt builds in dependency order. You decide the design, such as grain, keys and history. dbt then materializes each model as a view, table or incremental table, and runs tests and documentation next to the code.
Is dbt a data modeling tool?
dbt is a transformation tool that implements data models rather than a diagramming tool that designs them. It does not choose your grain or draw an entity diagram for you. Once the design is decided, though, dbt builds the staging, intermediate and mart models, tracks their lineage, tests their keys and keeps every change in version control.
Can you build a star schema in dbt?
Yes. dbt Labs publishes its own tutorial for building a Kimball dimensional model, with fct_ and dim_ models and hashed surrogate keys from the dbt_utils package. You stage each source table, reshape data in intermediate models, then build fact and dimension marts at a declared grain and test that every foreign key finds its dimension row.
What is the difference between staging, intermediate and mart models?
Staging models clean one source table each, with renames and casts only, and are usually views. Intermediate models hold reusable joins and reshaping that several marts need, and are often ephemeral. Marts are the facts and dimensions people query, built as tables or incremental models at a grain that is written down and tested.
How do dbt snapshots implement SCD Type 2?
A snapshot compares a source table with the history it has stored and adds a new row whenever a tracked record changes. It records dbt_valid_from and dbt_valid_to for each version, using either an updated_at timestamp or a list of check columns. You then build a versioned dimension from the snapshot and join facts to the version valid at event time.
What is the difference between a snapshot and an incremental model?
A snapshot keeps history by storing a new version of a row each time it changes, so it suits slowly changing dimensions. An incremental model avoids full rebuilds by processing only new or changed rows, so it suits large fact tables. Snapshots answer what a record looked like back then, while incremental models answer how to build faster.
Should marts be wide or normalized when using MetricFlow?
dbt Labs advises keeping marts as normalized as possible when you use the Semantic Layer, because MetricFlow builds the joins itself and needs room to do it. Without the Semantic Layer, the same guide recommends wide, denormalized marts that BI users can query directly. Either way, the fact table still needs a clear, tested grain underneath.
Which dbt should I use for modeling: dbt v1, dbt or dbt OSS?
The modeling patterns are the same on all three. Since September 2026, dbt means dbt v2, which is free to use and adds optional paid features. As of November 2026, dbt OSS, the Apache 2.0 build of v2, is still an alpha release. dbt v1, known as dbt Core, remains available under Apache 2.0, with 1.12.6 as its latest stable release on PyPI.
How do you choose the grain of a dbt fact table?
Start from the business process and the questions people ask about it, then pick the most detailed level the source can support. Write the grain as one sentence, such as one row per order line, and put it in the model’s YAML description. Add unique and not-null tests on the key that expresses that grain.
