Phase 3: Data Pipelines & ETL

Staging, intermediate & mart layer organization

Intermediate ~2 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you're helping a super chef prepare a huge dinner party with lots of different dishes! If you just threw all the ingredients—flour, tomatoes, raw chicken, spices—into one giant pile and started cooking everything at once, it would be a messy disaster. You'd never know what went where, and if something went wrong, it would be impossible to fix. It's much better to have a system, right? That's exactly what we do with data in a tool like dbt. We organize it into different "steps" or "layers" so everything stays neat, clear, and easy to manage.

The very first step is like setting up your "prep station." This is where you take all your raw ingredients—the unwashed vegetables, the whole chicken, the bag of flour—and get them ready. You'd wash the vegetables, chop them into neat pieces, trim the chicken, and maybe measure out the flour. You're not cooking yet, just cleaning and organizing. In dbt, we call this the Staging Layer. It's the first step for all the raw data we get. We clean it up, make sure names are consistent, fix any simple mistakes, and get it into a standard, easy-to-use form. Think of it as making sure every ingredient is clean, correctly named, and in its right little bowl, ready for the next cooking step. You don't mix anything together here, just prepare each item individually.

Once your ingredients are prepped, you move to the actual cooking! Some ingredients might go into a sauce (like an "intermediate" step) that will be used in multiple dishes. Other ingredients might go directly into a main dish. This is where you combine things, add flavors, and make delicious creations. In dbt, after the "prep station" (Staging), we have more layers. The Intermediate Layer is like making those sauces or doughs that are used in many places. And finally, the Mart Layer is like the finished dishes, perfectly cooked and ready to be served to your guests. Each layer builds on the last, getting closer and closer to the final delicious meal.

So, by organizing your data like this, using a "prep station" (Staging) and then separate cooking steps (Intermediate and Mart), you make sure that if a tomato is bad, you only have to check the prep station, not the entire finished meal. If you want to change a recipe, you know exactly which step to tweak. This means when you build data systems, you create really strong, reliable recipes for your data. You can easily find problems, change things, and add new dishes (or data reports!) without making a giant mess, ensuring your data is always delicious and ready to be enjoyed!

In dbt, organizing your data models into distinct layers—Staging, Intermediate, and Mart—is a fundamental best practice for building robust, maintainable, and scalable data pipelines. This layered architecture promotes separation of concerns, making your transformations easier to understand, debug, and reuse. Each layer serves a specific purpose, guiding raw data through progressive stages of cleaning, aggregation, and business logic application until it's ready for consumption.

Your journey begins with the Staging Layer (conventionally prefixed stg_). These models are the first transformation step from your raw source data. Their primary role is minimal cleaning and standardization: casting data types, renaming columns for consistency, basic filtering (e.g., removing soft-deleted records), and handling nulls. Staging models should typically represent a one-to-one mapping with your source tables, avoiding complex joins or aggregations. They act as a reliable, consistent foundation for all downstream transformations, abstracting away the idiosyncrasies of your source systems.

Next, the Intermediate Layer (often prefixed int_) is where reusable, complex business logic or transformations reside. These models typically join multiple staging models or perform aggregations that will be used by several final data marts. By centralizing these calculations, you avoid duplication and ensure consistency across your reports. Finally, the Mart Layer is your consumption-ready data. This layer (often prefixed fct_ for facts, dim_ for dimensions, or agg_ for aggregates) contains models tailored to specific business use cases, dashboards, or analytical tools. They leverage intermediate models and provide the final, user-friendly data structures required by analysts and business users.

Key Takeaways

  • Layered architecture (Staging, Intermediate, Mart) enhances dbt project clarity and maintainability.
  • Staging models (stg_): First step, minimal cleaning, 1:1 with source data.
  • Intermediate models (int_): Complex, reusable business logic, joins across staging models.
  • Mart models (fct_, dim_, agg_): Final, consumption-ready data for specific business needs.
  • Consistent naming conventions are crucial for navigation and understanding dependencies.

Code Example

sql
-- models/staging/stg_raw_products.sql
SELECT
    id AS product_id,
    name AS product_name,
    price AS unit_price_usd
FROM {{ source('ecommerce', 'products') }}
WHERE is_available = TRUE

-- models/intermediate/int_products_with_tax.sql
SELECT
    product_id,
    product_name,
    unit_price_usd,
    unit_price_usd * 1.05 AS price_with_tax_usd -- Example reusable logic
FROM {{ ref('stg_raw_products') }}

-- models/marts/fct_product_catalog.sql
SELECT
    product_id,
    product_name,
    price_with_tax_usd AS retail_price_usd,
    'Active' AS product_status_category
FROM {{ ref('int_products_with_tax') }}

How this code works

This code demonstrates dbt's layered approach to data transformation, organizing raw product data into progressively refined staging, intermediate, and mart models. The overall job is to transform raw product details into a clean, enriched, and analytics-ready format. The first model, models/staging/stg_raw_products.sql, serves as the staging layer. It connects directly to the raw ecommerce.products table using {{ source('ecommerce', 'products') }}. This layer focuses on initial cleaning and standardization, such as renaming columns like id AS product_id, and filtering out unavailable items using WHERE is_available = TRUE to provide a consistent base.

Moving into the intermediate layer, models/intermediate/int_products_with_tax.sql builds upon the staged data, referencing it with {{ ref('stg_raw_products') }}. This model introduces reusable business logic, such as calculating price_with_tax_usd by multiplying unit_price_usd by 1.05. Finally, models/marts/fct_product_catalog.sql creates a mart layer, consuming data from the intermediate model via {{ ref('int_products_with_tax') }}. Mart models are tailored for specific analytical use cases, here renaming price_with_tax_usd AS retail_price_usd and adding a fixed 'Active' AS product_status_category. A subtle but critical dbt feature is how {{ ref() }} automatically manages the build order: dbt understands that stg_raw_products must run before int_products_with_tax, and int_products_with_tax before fct_product_catalog, ensuring all transformations execute in the correct sequence.

Related Articles

Hand-picked reads to go deeper on data transformation (dbt).

Back to Data Engineer Roadmap