Project structure: staging, intermediate, marts

A well-structured dbt project organizes transformations into three distinct layers: staging, intermediate, and marts. This architecture enforces clean separation of concerns, guarantees that raw sources are cleaned once, and provides business users with dependable dimensional models.

The standard three-layer model

The data flow progresses through three sequential tiers:

• Staging sits directly on top of raw data sources. Each staging model has a 1-to-1 relationship with a source table, performing basic cleanup like column renaming, type casting, and timezone conversion. Staging models should always be materialized as views.
• Intermediate handles complex business logic and multi-table joins. If calculating user retention requires joining orders, subscriptions, and web events, that logic lives here. Intermediate models are typically materialized as views or ephemeral models.
• Marts deliver consumer-facing datasets structured for analytics and reporting. This layer contains dimensional star schemas composed of facts and dimensions, materialized as tables or incremental models.

Data flows strictly in one direction from staging to intermediate to marts.

Naming conventions and access rules

Consistent naming conventions make model discovery and debugging intuitive:

• Use stg_source__entity for staging, such as stg_stripe__charges.sql.
• Use int_topic__transformation for intermediate models, such as int_orders_joined.sql.
• Use fct_entity for fact tables and dim_entity for dimensions, such as fct_orders.sql and dim_customers.sql.

A strict rule of analytics engineering is that only staging models may query the source function. Intermediate and mart models must reference upstream data exclusively using ref. This rule ensures that if an upstream table changes, you only update a single staging file.

Folder-level configurations

Avoid configuring materializations and schemas inside individual SQL files. Instead, define folder-level defaults in dbt_project.yml:

models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

This configuration keeps project settings centralized and consistent across developers.