Tag: snowflake

If you’ve ever inherited a dbt project, you know there are two kinds: the clean, logical, and easy-to-navigate project, and the other kind—a tangled mess of models that makes you question every life choice that led you to that moment. The difference between the two isn’t talent; it’s structure. For high-performing data teams, a well-defined structure for dbt projects in Snowflake isn’t just a nice-to-have, it’s the very foundation of a scalable, maintainable, and trustworthy analytics workflow.

While dbt and Snowflake are a technical match made in heaven, simply putting them together doesn’t guarantee success. Without a clear and consistent project structure, even the most powerful tools can lead to chaos. Dependencies become circular, model names become ambiguous, and new team members spend weeks just trying to understand the data flow.

This guide provides a battle-tested blueprint for structuring dbt projects in Snowflake. We’ll move beyond the basics and dive into a scalable, multi-layered framework that will save you and your team countless hours of rework and debugging.

Why dbt and Snowflake Are a Perfect Match

Before we dive into project structure, it’s crucial to understand why this combination has become the gold standard for the modern data stack. Their synergy comes from a shared philosophy of decoupling, scalability, and performance.

• Snowflake’s Decoupled Architecture: Its separation of storage and compute is revolutionary. This means you can run massive dbt transformations using a dedicated, powerful virtual warehouse without slowing down your BI tools.
• dbt’s Transformation Power: dbt focuses on the “T” in ELT—transformation. It allows you to build, test, and document your data models using simple SQL, which it then compiles and runs directly inside Snowflake’s powerful engine.
• Cost and Performance Synergy: Running dbt models in Snowflake is incredibly efficient. You can spin up a warehouse for a dbt run and spin it down the second it’s finished, meaning you only pay for the exact compute you use.
• Zero-Copy Cloning for Development: Instantly create a zero-copy clone of your entire production database for development. This allows you to test your dbt project against production-scale data without incurring storage costs or impacting the production environment.

In short, Snowflake provides the powerful, elastic engine, while dbt provides the organized, version-controlled, and testable framework to harness that engine.

The Layered Approach: From Raw Data to Actionable Insights

A scalable dbt project is like a well-organized factory. Raw materials come in one end, go through a series of refined production stages, and emerge as a finished product. We achieve this by structuring our models into distinct layers, each with a specific job.

Our structure will follow this flow: Sources -> Staging -> Intermediate -> Marts.

Layer 1: Declaring Your Sources (The Contract with Raw Data)

Before you write a single line of transformation SQL, you must tell dbt where your raw data lives in Snowflake. This is done in a .yml file. Think of this file as a formal contract that declares your raw tables, allows you to add data quality tests, and serves as a foundation for your data lineage graph.

Example: models/staging/sources.yml

Let’s assume we have a RAW_DATA database in Snowflake with schemas from a jaffle_shop and stripe.

YAML

version: 2

sources:
  - name: jaffle_shop
    database: raw_data 
    schema: jaffle_shop
    description: "Raw data from the primary application database."
    tables:
      - name: customers
        columns:
          - name: id
            tests:
              - unique
              - not_null
      - name: orders
        loaded_at_field: _etl_loaded_at
        freshness:
          warn_after: {count: 12, period: hour}

  - name: stripe
    database: raw_data
    schema: stripe
    tables:
      - name: payment
        columns:
          - name: orderid
            tests:
              - relationships:
                  to: source('jaffle_shop', 'orders')
                  field: id

Layer 2: Staging Models (Clean and Standardize)

Staging models are the first line of transformation. They should have a 1:1 relationship with your source tables. The goal here is strict and simple:

• DO: Rename columns, cast data types, and perform very light cleaning.
• DO NOT: Join to other tables.

This creates a clean, standardized version of each source table, forming a reliable foundation for the rest of your project.

Example: models/staging/stg_customers.sql

SQL

-- models/staging/stg_customers.sql
with source as (
    select * from {{ source('jaffle_shop', 'customers') }}
),

renamed as (
    select
        id as customer_id,
        first_name,
        last_name
    from source
)

select * from renamed

Layer 3: Intermediate Models (Build, Join, and Aggregate)

This is where the real business logic begins. Intermediate models are the “workhorses” of your dbt project. They take the clean data from your staging models and start combining them.

• DO: Join different staging models together.
• DO: Perform complex calculations, aggregations, and business-specific logic.
• Materialize them as tables if they are slow to run or used by many downstream models.

These models are not typically exposed to business users. They are building blocks for your final data marts.

Example: models/intermediate/int_orders_with_payments.sql

SQL

-- models/intermediate/int_orders_with_payments.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

order_payments as (
    select
        order_id,
        sum(case when payment_status = 'success' then amount else 0 end) as total_amount
    from payments
    group by 1
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        coalesce(order_payments.total_amount, 0) as amount
    from orders
    left join order_payments 
      on orders.order_id = order_payments.order_id
)

select * from final

Layer 4: Data Marts (Ready for Analysis)

Finally, we arrive at the data marts. These are the polished, final models that power your dashboards, reports, and analytics. They should be clean, easy to understand, and built for a specific business purpose (e.g., finance, marketing, product).

• DO: Join intermediate models.
• DO: Have clear, business-friendly column names.
• DO NOT: Contain complex, nested logic. All the heavy lifting should have been done in the intermediate layer.

These models are the “products” of your data factory, ready for consumption by BI tools like Tableau, Looker, or Power BI.

Example: models/marts/fct_customer_orders.sql

SQL

-- models/marts/fct_customer_orders.sql
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('int_orders_with_payments') }}
),

customer_orders as (
    select
        customers.customer_id,
        min(orders.order_date) as first_order_date,
        max(orders.order_date) as most_recent_order_date,
        count(orders.order_id) as number_of_orders,
        sum(orders.amount) as lifetime_value
    from customers
    left join orders 
      on customers.customer_id = orders.customer_id
    group by 1
)

select * from customer_orders

Conclusion: Structure is Freedom

By adopting a layered approach to your dbt projects in Snowflake, you move from a chaotic, hard-to-maintain process to a scalable, modular, and efficient analytics factory. This structure gives you:

• Maintainability: When logic needs to change, you know exactly which model to edit.
• Scalability: Onboarding new data sources or team members becomes a clear, repeatable process.
• Trust: With testing at every layer, you build confidence in your data and empower the entire organization to make better, faster decisions.

This framework isn’t just about writing cleaner code—it’s about building a foundation for a mature and reliable data culture.