E-Commerce Analytics Pipeline with dbt

Build a medallion architecture pipeline in Snowflake with dbt and CI/CD.

Introduction

30 Second Summary

Every time you place an online order, that transaction flows through multiple systems before it becomes the "recommended for you" insight on your next visit. The data pipeline connecting raw transactions to business intelligence is what separates a data team that reacts from one that leads.

In this project, you will build a production-grade e-commerce analytics pipeline using Snowflake and dbt. You will transform raw order data through a medallion architecture into an analytics-ready star schema, complete with data quality automation and CI/CD.

What You'll Build

A complete dbt project that processes e-commerce data through Bronze, Silver, and Gold layers with incremental models, SCD Type 2 dimensions, a data quality framework using dbt-expectations and Elementary, and automated CI via GitHub Actions.

By the end of this project, you'll have:

  • A medallion architecture pipeline (Bronze/Silver/Gold) with incremental models and SCD Type 2 customer tracking in Snowflake.
  • A data quality framework with 15+ tests using dbt-expectations for regex validation and range checks, plus Elementary for automated anomaly detection.
  • A GitHub Actions CI workflow using dbt clone for fast, incremental model testing on every pull request.
  • Secret Mission: Add Snowflake clustering keys and implement a lookback window for late-arriving data to optimize production performance.

Are there any prerequisites?

You need a Snowflake account with an active warehouse (a free trial works perfectly), Python 3.10+ installed, and a Git/GitHub account. Basic familiarity with SQL and the command line is helpful.

You will install dbt Core and all other packages during Step 1.

Set Up dbt Core and Snowflake Connection

Every production data pipeline starts with a solid foundation. Before you can transform raw e-commerce data into analytics-ready tables, you need a local dbt environment connected to your Snowflake warehouse.

In this step, you will install dbt Core, initialize your project structure, and establish the connection between your local machine and Snowflake. You will also create the database schemas that form the medallion architecture (Bronze, Silver, Gold) your pipeline will use.

In this step, get ready to:
  • Install dbt Core with the Snowflake adapter in a Python virtual environment.
  • Initialize a dbt project and configure the Snowflake connection profile.
  • Create the Snowflake database with Bronze, Silver, and Gold schemas and verify connectivity.
Install dbt Core with the Snowflake adapter

dbt Core is a Python package that runs transformations against your data warehouse. The dbt-snowflake adapter installs dbt Core and all the dependencies needed to connect to Snowflake.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ecommerce-analytics
cd ecommerce-analytics
  • Create a Python virtual environment by running:
python -m venv venv
  • Activate the virtual environment:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

Linux

source venv/bin/activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms you are inside the virtual environment.

  • Upgrade pip and install dbt with the Snowflake adapter by running these commands:
python -m pip install --upgrade pip wheel setuptools
python -m pip install dbt-snowflake

What does this install?

The dbt-snowflake package installs dbt Core and all Snowflake-specific dependencies in one command. You do not need to install dbt Core separately.

  • Verify the installation by running:
dbt --version

You should see output showing dbt Core v1.11 (or higher) and the snowflake adapter listed.

Command not found?

Make sure your virtual environment is activated. You should see (venv) in your terminal prompt. If not, re-run the activation command above.

On some systems, you may need to use python3 instead of python in all commands.

Help me troubleshoot my dbt installation

Initialize your dbt project and configure the Snowflake connection

The dbt init command scaffolds a new project with the standard folder structure (models, seeds, macros, snapshots) and walks you through setting up a connection profile.

What will dbt init ask you?

The command is interactive. It will ask you to name your project, select a database adapter, and provide connection details like account, user, and password.

It creates the project folder and a connection profile at ~/.dbt/profiles.yml.

  • Initialize your dbt project by running:
dbt init
  • When prompted for a project name, enter ecommerce_analytics.
  • When prompted for which database, select snowflake.
  • Enter your Snowflake connection details when prompted (account, user, password, role, warehouse, database, schema). You can accept defaults for now as you will verify the full configuration next.
  • Move into the new project directory by running:
cd ecommerce_analytics
  • Confirm the project files exist by running:
ls

You should see dbt_project.yml, models/, macros/, and other folders in the output.

Now you need to verify your connection profile has the correct Snowflake credentials. The profile lives at ~/.dbt/profiles.yml.

  • Open ~/.dbt/profiles.yml in VS Code or your text editor and replace its contents with the following:
ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: [[SNOWFLAKE_ACCOUNT="your-account-identifier"]]
      user: [[SNOWFLAKE_USER="your-snowflake-username"]]
      password: your-snowflake-password
      role: [[SNOWFLAKE_ROLE="your-role"]]
      warehouse: [[SNOWFLAKE_WAREHOUSE="your-warehouse"]]
      database: ECOMMERCE_ANALYTICS
      schema: public
      threads: 4

Where do I find my Snowflake account identifier?

Your account identifier is the string before .snowflakecomputing.com in your Snowflake URL. For example, if your URL is abc12345.us-east-1.snowflakecomputing.com, your account is abc12345.us-east-1.

For the role, use your active role (often ACCOUNTADMIN for trial accounts). For the warehouse, use the name shown in your Snowflake console (often COMPUTE_WH).

  • Save profiles.yml.

Next, configure the dbt project file to define your medallion architecture layers. Each layer maps to a separate Snowflake schema.

  • Open dbt_project.yml in the ecommerce_analytics folder and replace its entire contents with the following:
name: 'ecommerce_analytics'
version: '1.0.0'
config-version: 2

profile: 'ecommerce_analytics'

model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

vars:
  'dbt_date:time_zone': 'UTC'

models:
  ecommerce_analytics:
    staging:
      +materialized: view
      +schema: silver
    intermediate:
      +materialized: table
      +schema: silver
    marts:
      +materialized: table
      +schema: gold

seeds:
  ecommerce_analytics:
    +schema: bronze

snapshots:
  ecommerce_analytics:
    +schema: gold

models:
  elementary:
    +schema: "elementary"

flags:
  require_explicit_package_overrides_for_builtin_materializations: false
  source_freshness_run_project_hooks: true

What does this configuration do?

This file maps your dbt project to the medallion architecture. Staging and intermediate models write to the silver schema. Mart models (your analytics-ready tables) write to the gold schema. Seeds (raw CSV data) load into the bronze schema.

The vars section sets the timezone for a package you will install later. The flags section enables compatibility with the Elementary observability package.

  • Save dbt_project.yml.

YAML indentation errors?

YAML is sensitive to indentation. Make sure you use spaces (not tabs) and that nested keys are indented consistently with two spaces.

Help me fix my dbt_project.yml YAML formatting

✔️ Awesome, I've got everything!

Great. Double check you have saved both profiles.yml and dbt_project.yml.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml file:

name: 'ecommerce_analytics'
version: '1.0.0'
config-version: 2

profile: 'ecommerce_analytics'

model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

vars:
  'dbt_date:time_zone': 'UTC'

models:
  ecommerce_analytics:
    staging:
      +materialized: view
      +schema: silver
    intermediate:
      +materialized: table
      +schema: silver
    marts:
      +materialized: table
      +schema: gold

seeds:
  ecommerce_analytics:
    +schema: bronze

snapshots:
  ecommerce_analytics:
    +schema: gold

models:
  elementary:
    +schema: "elementary"

flags:
  require_explicit_package_overrides_for_builtin_materializations: false
  source_freshness_run_project_hooks: true
Create the Snowflake database structure and verify connectivity

Your dbt project is configured to write to bronze, silver, and gold schemas. Now you need to create those schemas inside your Snowflake warehouse so dbt has somewhere to land the data.

  • Log in to your Snowflake account at app.snowflake.com.
  • In the navigation menu, select Projects, then Worksheets.
  • Click + and choose SQL Worksheet to create a new worksheet.
  • Paste the following SQL into the worksheet and run it by clicking the Run button (or pressing Cmd+Enter on macOS, Ctrl+Enter on Windows):
-- Create the database for our analytics pipeline
CREATE DATABASE IF NOT EXISTS ECOMMERCE_ANALYTICS;

-- Switch to the new database
USE DATABASE ECOMMERCE_ANALYTICS;

-- Create schemas for each medallion layer
CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;

What are the Bronze, Silver, and Gold schemas?

Bronze holds raw, unprocessed data. Silver holds cleaned and standardized data. Gold holds analytics-ready tables optimized for reporting.

This separation keeps your pipeline organized and each layer independently testable. dbt does not load the Bronze layer directly. You will use dbt seed (CSV loading) in the next step to populate Bronze.

  • Confirm the schemas were created. You should see a success message for each statement in the Snowflake worksheet results.

Now return to your terminal to verify that dbt can reach Snowflake using your configured profile.

  • Run the connection check by executing:
dbt debug

You should see all checks passing, including connection, dependencies, and profile configuration. The final line should confirm that dbt can connect to your Snowflake account.

dbt debug shows a connection error?

Double check your profiles.yml values. The most common causes are an incorrect account identifier, a typo in the warehouse name, or using the wrong role.

Make sure the database ECOMMERCE_ANALYTICS matches exactly (Snowflake identifiers are case-sensitive when quoted).

Help me debug my dbt Snowflake connection

Your dbt environment is fully connected to Snowflake and ready to transform data. Next up, you will load raw e-commerce data into the Bronze layer using seed files.

Load Raw Data into Bronze Layer

Your dbt project is connected to Snowflake and ready to transform data. But there's nothing to transform yet.

In the medallion architecture, the Bronze layer stores raw source data exactly as it arrives from external systems. In this step, you'll populate the Bronze layer with sample e-commerce data (orders, customers, and products), then configure source freshness monitoring so dbt knows when data goes stale.

In this step, get ready to:
  • Create CSV seed files for orders, customers, and products.
  • Load seed data into the Bronze schema with dbt seed.
  • Configure source freshness checks in sources.yml.
Create seed CSV files and load them into Snowflake

dbt seeds are CSV files that dbt loads directly into your warehouse as tables. They're ideal for small reference datasets and development data.

What are dbt seeds?

Seeds are CSV files stored in your seeds/ directory. When you run dbt seed, dbt creates tables in your warehouse and loads the CSV data into them.

They're best for small, static datasets like lookup tables or test data. Production data ingestion would use tools like Snowpipe or Fivetran, but seeds keep this project self-contained.

  • Create a file called raw_orders.csv inside the seeds/ folder of your dbt project.
  • Paste this data into the file:
order_id,customer_id,product_id,order_date,amount,status
1,101,1001,2024-01-15,59.99,completed
2,102,1002,2024-01-16,129.99,completed
3,101,1003,2024-01-17,24.99,pending
4,103,1001,2024-01-18,59.99,completed
5,104,1004,2024-01-19,199.99,shipped
6,102,1002,2024-01-20,129.99,returned
7,105,1005,2024-01-21,49.99,completed
8,103,1003,2024-01-22,24.99,completed
9,101,1004,2024-01-23,199.99,shipped
10,106,1001,2024-01-24,59.99,pending

What does this data represent?

This is a 10-row orders table spanning January 2024. Each row captures an order with its customer, product, date, dollar amount, and fulfillment status. These are the transactions your pipeline will transform into analytics-ready facts.

  • Create a second file called raw_customers.csv in the same seeds/ folder.
  • Paste this data into the file:
customer_id,first_name,last_name,email,country,segment,created_at
101,Alice,Johnson,alice.johnson@email.com,US,premium,2023-06-01
102,Bob,Smith,bob.smith@email.com,UK,standard,2023-07-15
103,Charlie,Brown,charlie.brown@email.com,US,premium,2023-08-20
104,Diana,Lee,diana.lee@email.com,CA,standard,2023-09-10
105,Eve,Wilson,eve.wilson@email.com,US,premium,2023-10-05
106,Frank,Garcia,frank.garcia@email.com,UK,standard,2023-11-12

What does this data represent?

This is your customer dimension data. Each customer has a segment (premium or standard) and a country. Later, you'll track historical changes to these fields using SCD Type 2 snapshots.

  • Create a third file called raw_products.csv in the same seeds/ folder.
  • Paste this data into the file:
product_id,product_name,category,unit_price,cost_price
1001,Wireless Headphones,Electronics,59.99,25.00
1002,Running Shoes,Footwear,129.99,55.00
1003,Coffee Mug,Kitchen,24.99,8.00
1004,Laptop Stand,Electronics,199.99,85.00
1005,Yoga Mat,Fitness,49.99,15.00

What does this data represent?

This is your product catalog with both the retail price (unit_price) and what it costs to procure (cost_price). The difference between these two values is your gross profit margin per item.

  • Save all three CSV files.
  • Confirm the files exist by running:
ls seeds/

You should see raw_orders.csv, raw_customers.csv, and raw_products.csv listed in the output.

  • Load all three seed files into Snowflake by running:
dbt seed

What does this command do?

dbt seed reads each CSV in the seeds/ directory, creates a corresponding table in Snowflake, and inserts the rows. Because your dbt_project.yml sets +schema: bronze under the seeds config, all three tables land in your Bronze schema.

You should see output indicating all three seeds completed successfully, with each seed showing an OK status.

Seeing a permission or database error?

Make sure your Snowflake warehouse is active (not suspended). You can resume it from the Snowflake UI under **Warehouses**.

If you see a schema error, confirm that the Bronze schema exists in your Snowflake database. Run SHOW SCHEMAS in the Snowflake worksheet to check.

help me debug a dbt seed error with Snowflake permissions

Configure source freshness checks

dbt needs to know where your raw data lives so that downstream models can reference it. Source definitions also let you monitor data freshness, so you're alerted when upstream data stops flowing.

  • Create a folder called staging inside your models/ directory.
  • Inside models/staging/, create a file called sources.yml.
  • Paste this configuration into the file:
version: 2

sources:
  - name: bronze
    schema: bronze
    freshness:
      warn_after: {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: raw_orders
      - name: raw_customers
      - name: raw_products

What does this configuration do?

  • name: bronze gives this source group a name that your dbt models will reference with {{ source('bronze', 'raw_orders') }}.
  • schema: bronze tells dbt which Snowflake schema these tables live in.
  • freshness configures monitoring thresholds. If data hasn't arrived in 24 hours, dbt warns. After 48 hours, it errors.
  • loaded_at_field is the timestamp column dbt checks to measure freshness. In production, your ingestion tool would populate this.
  • Save sources.yml.

✔️ Awesome, I've got everything!

Great. Make sure you've saved sources.yml in the correct location: models/staging/sources.yml.

ⓧ I'd like to double check the full code

version: 2

sources:
  - name: bronze
    schema: bronze
    freshness:
      warn_after: {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: raw_orders
      - name: raw_customers
      - name: raw_products
Verify sources are accessible

Now that sources are defined, you can verify that dbt can find and query the tables you just seeded.

  • Run the source freshness check by running:
dbt source freshness

What does this command do?

dbt source freshness queries each source table and checks the loaded_at_field timestamp against the freshness thresholds you configured. It validates that your source configuration is correct and that dbt can reach the tables in Snowflake.

Expected warnings about freshness

You will likely see warnings or errors about freshness for your source tables. This is expected because seed data doesn't include a _loaded_at timestamp column.

In production, your ingestion tool (like Snowpipe or Fivetran) would add this metadata column automatically. The important thing here is that dbt recognizes your source configuration and can query the underlying tables.

Seeing a compilation error?

Check that sources.yml is saved at models/staging/sources.yml, not in the project root or another subdirectory.

Verify that the schema: bronze value matches the actual schema name in your Snowflake database. Schema names are case-sensitive in Snowflake when quoted.

help me fix a dbt source freshness compilation error

Your Bronze layer now has raw e-commerce data ready for transformation. Next up, you'll build the Silver layer with staging models that clean, type, and standardize this data into a consistent format.

Build Silver Layer with Staging and Intermediate Models

Your bronze layer has raw data loaded into Snowflake, but it is messy. Column types are ambiguous, naming is inconsistent, and there is no validation.

The silver layer solves this by transforming raw data into clean, typed, and tested models. In this step, you will build staging models that standardize your bronze data, an intermediate model that joins everything together, and a test suite that catches problems before they reach your analytics layer.

In this step, get ready to:
  • Create staging models that cast and rename raw columns into standardized fields.
  • Build an intermediate model with a custom Jinja macro for reusable join logic.
  • Add data quality tests and validate the entire silver layer with a single command.
Create staging models for orders, customers, and products

Staging models sit between raw data and business logic. They handle type casting, column renaming, and nothing else. This keeps your transformations predictable and your downstream models clean.

  • Create a new file called stg_orders.sql inside models/staging/ (the same folder that already contains sources.yml).
  • Paste the following code into stg_orders.sql:
with source as (
    select * from {{ source('bronze', 'raw_orders') }}
),

renamed as (
    select
        order_id::int as order_id,
        customer_id::int as customer_id,
        product_id::int as product_id,
        order_date::date as order_date,
        amount::decimal(10,2) as order_amount,
        status::varchar as order_status
    from source
)

select * from renamed

What does this code do?

The source() function references the raw_orders table you defined in sources.yml. This creates a dependency that dbt tracks in its lineage graph.

The renamed CTE casts every column to an explicit type. Notice that amount becomes order_amount and status becomes order_status. Prefixing with the entity name avoids ambiguity when these columns appear in joins later.

  • Save stg_orders.sql.
  • You won't see a change yet. The next models follow the same pattern, and you will run them all together shortly.
  • Create a new file called stg_customers.sql inside models/staging/.
  • Paste the following code into stg_customers.sql:
with source as (
    select * from {{ source('bronze', 'raw_customers') }}
),

renamed as (
    select
        customer_id::int as customer_id,
        first_name::varchar as first_name,
        last_name::varchar as last_name,
        email::varchar as email,
        country::varchar as country,
        segment::varchar as customer_segment,
        created_at::date as customer_created_at
    from source
)

select * from renamed

What does this code do?

Same CTE pattern as orders. The raw segment column becomes customer_segment and created_at becomes customer_created_at. These prefixes make downstream queries self-documenting.

  • Save stg_customers.sql.
  • Create a new file called stg_products.sql inside models/staging/.
  • Paste the following code into stg_products.sql:
with source as (
    select * from {{ source('bronze', 'raw_products') }}
),

renamed as (
    select
        product_id::int as product_id,
        product_name::varchar as product_name,
        category::varchar as product_category,
        unit_price::decimal(10,2) as unit_price,
        cost_price::decimal(10,2) as cost_price
    from source
)

select * from renamed

What does this code do?

The products staging model renames category to product_category and casts prices to decimal(10,2) for consistent arithmetic later. Both unit_price and cost_price keep their original names since they are already clear.

  • Save stg_products.sql.

Not sure where to create these files?

All three staging models go in the models/staging/ folder inside your dbt project directory. This is the same folder where sources.yml already lives. If you do not see this folder, make sure you are in the root of your ecommerce_analytics project.

Help me find where to put my staging model files.

Why are staging models configured as views?

Your dbt_project.yml already configures staging models as views in the silver schema. Views do not store data. They re-run the query each time they are referenced. For lightweight transformations like type casting, views are fast and free of storage cost.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files.

ⓧ I'd like to double check the full code

with source as (
    select * from {{ source('bronze', 'raw_orders') }}
),

renamed as (
    select
        order_id::int as order_id,
        customer_id::int as customer_id,
        product_id::int as product_id,
        order_date::date as order_date,
        amount::decimal(10,2) as order_amount,
        status::varchar as order_status
    from source
)

select * from renamed
with source as (
    select * from {{ source('bronze', 'raw_customers') }}
),

renamed as (
    select
        customer_id::int as customer_id,
        first_name::varchar as first_name,
        last_name::varchar as last_name,
        email::varchar as email,
        country::varchar as country,
        segment::varchar as customer_segment,
        created_at::date as customer_created_at
    from source
)

select * from renamed
with source as (
    select * from {{ source('bronze', 'raw_products') }}
),

renamed as (
    select
        product_id::int as product_id,
        product_name::varchar as product_name,
        category::varchar as product_category,
        unit_price::decimal(10,2) as unit_price,
        cost_price::decimal(10,2) as cost_price
    from source
)

select * from renamed
Create a custom Jinja macro and the intermediate model

Your staging models clean individual tables. The intermediate layer is where you join them together and centralize reusable logic. Before building the intermediate model, you need a custom macro and the dbt_utils package it depends on.

  • Create a file called packages.yml in the root of your ecommerce_analytics project folder (the same level as dbt_project.yml).
  • Paste the following into packages.yml:
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  - package: metaplane/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

What does this file do?

The packages.yml file declares external dbt packages your project depends on. dbt_utils provides utility macros like surrogate key generation. dbt_expectations adds advanced data quality tests you will use later in this step.

  • Save packages.yml.
  • Install the packages by running:
dbt deps
  • You should see output confirming both packages were installed into the dbt_packages directory.

Seeing a package resolution error?

Check that your packages.yml is in the project root (same level as dbt_project.yml), not inside the models/ folder. Also verify the YAML indentation uses spaces, not tabs.

Help me debug my dbt deps error.

  • Create a new file called generate_surrogate_key.sql inside the macros/ folder.
  • Paste the following code into generate_surrogate_key.sql:
{% macro generate_surrogate_key(field_list) %}
    {{ dbt_utils.generate_surrogate_key(field_list) }}
{% endmacro %}

Why wrap dbt_utils in a custom macro?

Wrapping dbt_utils.generate_surrogate_key in your own macro creates an abstraction layer. If you ever switch to a different hashing strategy or package, you only update this one file instead of every model that generates a key.

  • Save generate_surrogate_key.sql.
  • Create a new folder called intermediate inside the models/ directory.
  • Create a file called int_orders_enriched.sql inside models/intermediate/.
  • Paste the following code into int_orders_enriched.sql:
with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

products as (
    select * from {{ ref('stg_products') }}
),

enriched as (
    select
        o.order_id,
        o.order_date,
        o.order_amount,
        o.order_status,
        {{ generate_surrogate_key(['o.order_id', 'o.customer_id']) }} as order_customer_key,
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        c.country,
        c.customer_segment,
        p.product_id,
        p.product_name,
        p.product_category,
        p.unit_price,
        p.cost_price,
        (o.order_amount - p.cost_price) as gross_profit
    from orders o
    left join customers c on o.customer_id = c.customer_id
    left join products p on o.product_id = p.product_id
)

select * from enriched

What does this model do?

  • The ref() function pulls from your staging models, creating a dependency chain dbt uses to determine build order.
  • The generate_surrogate_key macro hashes order_id and customer_id together to create a unique composite key called order_customer_key.
  • The gross_profit calculation centralizes business logic here so every downstream mart gets the same formula.
  • Left joins ensure orders are not dropped if a customer or product record is missing.
  • Save int_orders_enriched.sql.
  • You won't see a visible change yet. The next substep runs the full build to validate everything together.

Macro folder missing?

The macros/ folder should already exist in your project root (dbt init creates it). If it does not, create it at the same level as models/ and seeds/.

Help me find my macros folder or create it.

✔️ Awesome, I've got everything!

Great. Make sure you have saved both generate_surrogate_key.sql and int_orders_enriched.sql.

ⓧ I'd like to double check the full code

{% macro generate_surrogate_key(field_list) %}
    {{ dbt_utils.generate_surrogate_key(field_list) }}
{% endmacro %}
with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

products as (
    select * from {{ ref('stg_products') }}
),

enriched as (
    select
        o.order_id,
        o.order_date,
        o.order_amount,
        o.order_status,
        {{ generate_surrogate_key(['o.order_id', 'o.customer_id']) }} as order_customer_key,
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        c.country,
        c.customer_segment,
        p.product_id,
        p.product_name,
        p.product_category,
        p.unit_price,
        p.cost_price,
        (o.order_amount - p.cost_price) as gross_profit
    from orders o
    left join customers c on o.customer_id = c.customer_id
    left join products p on o.product_id = p.product_id
)

select * from enriched
Add data quality tests and run dbt build

Models without tests are just unverified assumptions. A schema.yml file declares data quality expectations that dbt runs automatically whenever you build your project.

  • Create a file called schema.yml inside models/staging/ (alongside your staging SQL files).
  • Paste the following into schema.yml:
version: 2

models:
  - name: stg_orders
    description: "Staged orders with typed columns and standardized naming"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_status
        data_tests:
          - accepted_values:
              values: ['completed', 'pending', 'shipped', 'returned']
      - name: order_amount
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 10000

  - name: stg_customers
    description: "Staged customers with typed columns"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: email
        data_tests:
          - unique
          - not_null
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"

  - name: stg_products
    description: "Staged products with typed columns"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null
      - name: unit_price
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000

What do these tests check?

  • The unique and not_null tests enforce primary key integrity on ID columns.
  • The relationships test on customer_id ensures every order references a valid customer (referential integrity).
  • The accepted_values test catches typos or unexpected values in order_status.
  • The dbt_expectations tests add range checks on order_amount and unit_price, and a regex validation on email to catch malformed addresses.

Notice the data_tests: key. In dbt v1.8+, the old tests: key was renamed to data_tests:.

  • Save schema.yml.
  • Build and test the entire silver layer by running:
dbt build

What does dbt build do?

The dbt build command runs models, tests, seeds, and snapshots in DAG order. It materializes your staging views and intermediate table in Snowflake, then executes every test defined in schema.yml. If a test fails, downstream models are skipped.

  • You should see output ending with a summary line like Done. PASS=X WARN=0 ERROR=0 SKIP=0 TOTAL=X where all counts show zero errors.
  • Each staging model should show OK created view model and the intermediate model should show OK created table model.
  • All tests should display PASS.

Seeing a compilation error?

If dbt reports a model depends on a node that was not found, check that your staging file names match exactly: stg_orders, stg_customers, stg_products. The ref() function uses the file name without the .sql extension.

If you see a macro not found error for generate_surrogate_key, confirm the file is in macros/generate_surrogate_key.sql and that you ran dbt deps earlier to install dbt_utils.

Help me debug my dbt build failure.

✔️ Awesome, I've got everything!

Great. Your silver layer is fully built and tested.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_orders
    description: "Staged orders with typed columns and standardized naming"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_status
        data_tests:
          - accepted_values:
              values: ['completed', 'pending', 'shipped', 'returned']
      - name: order_amount
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 10000

  - name: stg_customers
    description: "Staged customers with typed columns"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: email
        data_tests:
          - unique
          - not_null
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"

  - name: stg_products
    description: "Staged products with typed columns"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null
      - name: unit_price
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000

Your silver layer is complete. You have clean, typed staging models feeding into an enriched intermediate model, all validated by automated tests. Next up, you will build the gold layer with an incremental fact table and SCD Type 2 dimension tracking.

Build Gold Layer with Incremental Fact Table and SCD Type 2 Dimension

Your Silver layer cleanses and structures the raw data. Now you need a Gold layer that serves analytics-ready tables directly to dashboards and downstream consumers.

The Gold layer follows a star schema pattern: a central fact table surrounded by dimension tables. In this step, you'll build an incremental fact table that efficiently handles new data, dimension tables for customers and products, and an SCD Type 2 snapshot that tracks historical customer changes over time.

In this step, get ready to:
  • Create an incremental fact model with merge logic for efficient loading.
  • Create a dbt snapshot implementing SCD Type 2 on the customers dimension.
  • Create dimension models and validate the complete star schema with dbt build.
Create the incremental fact model

A full table rebuild works fine for small datasets, but production pipelines process millions of rows daily. Incremental models only process new or changed data on each run, saving both time and compute cost.

  • Create a new file called fct_orders.sql inside your models/marts/ folder.
  • Add the following code to define the incremental fact table:
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns'
    )
}}

with enriched_orders as (
    -- Pull from the intermediate model that already joins orders, customers, and products
    select * from {{ ref('int_orders_enriched') }}
)

select
    order_id,
    order_date,
    customer_id,
    product_id,
    order_customer_key,
    order_amount,
    gross_profit,
    order_status,
    current_timestamp() as loaded_at
from enriched_orders

{% if is_incremental() %}
-- On incremental runs, only process rows newer than the latest existing date
where order_date >= (select max(order_date) from {{ this }})
{% endif %}

What does this code do?

The config block at the top sets the materialization to incremental. This tells dbt to MERGE new rows into the existing table rather than rebuilding it from scratch.

The unique_key='order_id' setting tells Snowflake which column to match on during the merge. If a row with the same order_id already exists, it gets updated. New rows get inserted.

The is_incremental() block at the bottom only activates on subsequent runs (not the first). It filters for rows where order_date is at or after the latest date already in the table, so only new data gets processed.

Why sync_all_columns?

The on_schema_change='sync_all_columns' setting handles schema evolution. If your upstream data adds a new column, dbt automatically adds it to the target table AND removes columns that no longer appear in the model. This prevents silent schema drift in production.

  • Save models/marts/fct_orders.sql.

You won't see a change in Snowflake yet. We'll build all Gold layer models together after creating the dimensions and snapshot.

Create the SCD Type 2 snapshot

Customer data changes over time. Someone upgrades from standard to premium, or moves to a different country. A regular dimension table overwrites the old value, losing history. SCD Type 2 preserves every version of a record with valid_from and valid_to timestamps.

  • Create a new file called snp_customers.sql inside your snapshots/ folder.
  • Add the following code to define the SCD Type 2 snapshot:
{% snapshot snp_customers %}

{{
    config(
        target_schema='gold',
        unique_key='customer_id',
        strategy='check',
        check_cols=['first_name', 'last_name', 'email', 'country', 'customer_segment']
    )
}}

-- Track changes to customer attributes over time
select * from {{ ref('stg_customers') }}

{% endsnapshot %}

What does this snapshot do?

The strategy='check' setting tells dbt to compare the current values of the specified columns against the previously stored values. If ANY of the columns in check_cols change, dbt creates a new row and closes out the old one with a valid_to timestamp.

This means if Alice Johnson upgrades from standard to premium, the snapshot table keeps BOTH rows. The old row gets a valid_to date marking when the change happened. The new row has valid_to = NULL, indicating it is the current record.

  • Save snapshots/snp_customers.sql.

This snapshot will execute when we run dbt build in the next substep.

Create dimension models and validate the star schema

Dimension tables provide the descriptive context for your fact table. Each one is a simple, denormalized view of an entity that analysts can join to fct_orders for reporting.

  • Create a new file called dim_customers.sql inside your models/marts/ folder.
  • Add the following code for the customers dimension:
with customers as (
    select * from {{ ref('stg_customers') }}
)

select
    customer_id,
    first_name,
    last_name,
    email,
    country,
    customer_segment,
    customer_created_at
from customers

What does this model do?

This dimension pulls from the staging layer and presents all customer attributes in one clean table. Analysts query dim_customers directly for reporting without needing to know about the raw source format.

  • Save models/marts/dim_customers.sql.
  • Create another new file called dim_products.sql inside models/marts/.
  • Add the following code for the products dimension:
with products as (
    select * from {{ ref('stg_products') }}
)

select
    product_id,
    product_name,
    product_category,
    unit_price,
    cost_price,
    -- Pre-calculate margin so analysts don't repeat this logic
    (unit_price - cost_price) as profit_margin
from products

What does profit_margin add?

By computing profit_margin directly in the dimension, you prevent every downstream analyst from writing their own margin calculation. One definition, used everywhere. This is a core principle of dimensional modeling.

  • Save models/marts/dim_products.sql.
  • Create a new file called schema.yml inside your models/marts/ folder to define tests on the Gold layer models:
version: 2

models:
  - name: fct_orders
    description: "Fact table containing order transactions"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_amount
        data_tests:
          - not_null
    meta:
      elementary:
        timestamp_column: "order_date"
    data_tests:
      - elementary.volume_anomalies:
          timestamp_column: order_date
          time_bucket:
            period: day
            count: 1

  - name: dim_customers
    description: "Customer dimension"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: dim_products
    description: "Product dimension"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

What do these tests cover?

The unique and not_null tests on primary keys ensure your star schema maintains referential integrity. If a duplicate order_id sneaks in through the incremental merge, these tests catch it.

The elementary.volume_anomalies test monitors the daily row count of fct_orders. If a future run inserts significantly more or fewer rows than expected, it flags an anomaly. You will fully configure Elementary in a later step.

  • Save models/marts/schema.yml.
  • Build the entire project (seeds, models, snapshots, and tests) by running:
dbt build
  • Review the terminal output. You should see your Gold layer models materialize as tables in the gold schema: fct_orders, dim_customers, dim_products, and the snp_customers snapshot.
  • All tests should pass, confirming your star schema has valid primary keys and no null values in critical columns.

Seeing a compilation error?

If you see an error mentioning an undefined macro or missing package, confirm you ran dbt deps in a previous step. The elementary.volume_anomalies test requires the Elementary package to be installed.

If a test fails on unique for order_id, check that fct_orders.sql has unique_key='order_id' in the config block. Without it, dbt inserts duplicates instead of merging.

Help me debug my dbt build errors in the Gold layer.

✔️ Awesome, I've got everything!

Great. Double check you have saved all four files: fct_orders.sql, snp_customers.sql, dim_customers.sql, and dim_products.sql.

ⓧ I'd like to double check the full code

Here is the complete code for each file created in this step.

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns'
    )
}}

with enriched_orders as (
    select * from {{ ref('int_orders_enriched') }}
)

select
    order_id,
    order_date,
    customer_id,
    product_id,
    order_customer_key,
    order_amount,
    gross_profit,
    order_status,
    current_timestamp() as loaded_at
from enriched_orders

{% if is_incremental() %}
where order_date >= (select max(order_date) from {{ this }})
{% endif %}
{% snapshot snp_customers %}

{{
    config(
        target_schema='gold',
        unique_key='customer_id',
        strategy='check',
        check_cols=['first_name', 'last_name', 'email', 'country', 'customer_segment']
    )
}}

select * from {{ ref('stg_customers') }}

{% endsnapshot %}
with customers as (
    select * from {{ ref('stg_customers') }}
)

select
    customer_id,
    first_name,
    last_name,
    email,
    country,
    customer_segment,
    customer_created_at
from customers
with products as (
    select * from {{ ref('stg_products') }}
)

select
    product_id,
    product_name,
    product_category,
    unit_price,
    cost_price,
    (unit_price - cost_price) as profit_margin
from products
version: 2

models:
  - name: fct_orders
    description: "Fact table containing order transactions"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_amount
        data_tests:
          - not_null
    meta:
      elementary:
        timestamp_column: "order_date"
    data_tests:
      - elementary.volume_anomalies:
          timestamp_column: order_date
          time_bucket:
            period: day
            count: 1

  - name: dim_customers
    description: "Customer dimension"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: dim_products
    description: "Product dimension"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

Your Gold layer is live. You now have a complete star schema with an incremental fact table, SCD Type 2 history tracking, and dimension tables serving analytics. Next up, you'll add a robust data quality framework to make sure every row meets your standards.

Implement Data Quality Framework with dbt-expectations

Your pipeline has models, but no guardrails. Without automated tests, bad data (negative order amounts, malformed emails, unexpected nulls) flows silently into your gold layer and corrupts downstream dashboards.

In this step, you'll install the dbt-expectations package and run a full test suite that validates data quality across your staging and mart layers.

In this step, get ready to:
  • Add dbt packages (dbt-utils, dbt-expectations, Elementary) and install them.
  • Walk through the data quality tests defined on your staging models.
  • Run the full test suite and verify all tests pass.
Add packages and install dependencies

dbt packages extend dbt's built-in testing capabilities. You'll add three packages that together form a production data quality framework.

  • Create a new file called packages.yml in the root of your ecommerce_analytics project folder (at the same level as dbt_project.yml).
  • Paste the following into packages.yml:
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  - package: metaplane/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]
  - package: elementary-data/elementary
    version: "0.23.1"

What do these packages do?

  • dbt_utils provides utility macros like surrogate key generation (which your project already uses).
  • dbt_expectations adds 50+ test types inspired by Great Expectations: regex matching, range checks, statistical tests, and more.
  • elementary enables automated anomaly detection on volume, freshness, and schema drift. You will configure it fully in the next step.
  • Save packages.yml.
  • Install the packages by running:
dbt deps
  • You should see output confirming each package was installed into the dbt_packages/ directory.

Seeing a package resolution error?

Make sure your packages.yml is in the project root (same folder as dbt_project.yml), not inside the models/ folder.

If you see a version conflict, double-check that you copied the version ranges exactly as shown above.

Help me troubleshoot my dbt deps error

Next, confirm the timezone variable is configured. Your dbt_project.yml already has this under the vars section:

vars:
  'dbt_date:time_zone': 'UTC'

Why does dbt-expectations need a timezone?

The dbt-expectations package depends on dbt_date for time-based tests. Setting dbt_date:time_zone to UTC ensures consistent timestamp comparisons across all test evaluations.

Walk through your data quality tests

With the packages installed, the test definitions already in your schema files are now executable. Let's review what they do.

  • Open models/staging/schema.yml in VS Code.

What do the dbt-expectations tests do?

Your staging schema defines three advanced tests beyond the standard dbt generics:

  • expect_column_values_to_be_between on order_amount (min 0, max 10000): catches negative amounts and wildly inflated values that signal data corruption.
  • expect_column_values_to_match_regex on email: validates email format using a regex pattern, catching malformed entries before they reach the gold layer.
  • expect_column_values_to_be_between on unit_price (min 0, max 100000): ensures product pricing stays within a sane range.

These tests run as SQL queries against your Snowflake warehouse. Each one returns rows that fail the condition. Zero failing rows means the test passes.

  • Open models/marts/schema.yml to see the mart-layer tests.

What is the volume_anomalies test?

The elementary.volume_anomalies test on fct_orders uses statistical methods to detect unusual spikes or drops in row count. It groups data by order_date in daily buckets and flags any day that deviates significantly from the historical pattern.

You will configure Elementary fully in the next step. For now, this test definition may be skipped during dbt test because Elementary's internal models have not been materialized yet.

Run the full test suite

With packages installed and tests defined, it's time to execute the full data quality framework against your warehouse.

  • Run the test suite by executing:
dbt test
  • You should see each test listed with a PASS status. The generic tests (unique, not_null, accepted_values, relationships) and the dbt-expectations tests should all pass.

What should I see?

The output lists each test by name with a pass/fail indicator. Your staging tests check uniqueness, nullability, referential integrity, value ranges, and email format. Your mart tests check the same basics on the gold layer.

Any Elementary tests may show a warning or skip at this stage since the Elementary internal models have not been materialized. That is expected and will be resolved in the next step.

Seeing test failures?

If a dbt-expectations test fails with an error about an unrecognized macro, run dbt deps again to confirm the packages installed correctly.

If a test fails on actual data (e.g. a value outside the expected range), check that your seed data in seeds/ matches the original CSV files from Step 2.

Help me debug my failing dbt tests

✔️ Awesome, I've got everything!

Great. Double check you've saved packages.yml and confirmed all tests pass.

ⓧ I'd like to double check the full code

Here is the new file introduced in this step:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  - package: metaplane/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]
  - package: elementary-data/elementary
    version: "0.23.1"

Your models/staging/schema.yml should look like this:

version: 2

models:
  - name: stg_orders
    description: "Staged orders with typed columns and standardized naming"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_status
        data_tests:
          - accepted_values:
              values: ['completed', 'pending', 'shipped', 'returned']
      - name: order_amount
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 10000

  - name: stg_customers
    description: "Staged customers with typed columns"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: email
        data_tests:
          - unique
          - not_null
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"

  - name: stg_products
    description: "Staged products with typed columns"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null
      - name: unit_price
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000

Your models/marts/schema.yml should look like this:

version: 2

models:
  - name: fct_orders
    description: "Fact table containing order transactions"
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_amount
        data_tests:
          - not_null
    meta:
      elementary:
        timestamp_column: "order_date"
    data_tests:
      - elementary.volume_anomalies:
          timestamp_column: order_date
          time_bucket:
            period: day
            count: 1

  - name: dim_customers
    description: "Customer dimension"
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: dim_products
    description: "Product dimension"
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

Your pipeline now has automated guardrails catching bad data before it reaches consumers. Next up, you'll deploy Elementary for anomaly detection and a full observability dashboard.

Add Elementary for Anomaly Detection and Observability

Your data quality tests catch known problems. But what about unknown ones? A sudden drop in order volume or an unexpected spike in null values will not trigger a rule-based test because you never wrote a rule for it.

Elementary solves this by learning what normal looks like and alerting you when something deviates. In this step, you will create the materialization macro Elementary needs on Snowflake, initialize its metadata tables, and generate an HTML observability dashboard.

In this step, get ready to:
  • Create the Snowflake materialization override macro and initialize Elementary's metadata tables.
  • Run your test suite to generate baseline anomaly detection data.
  • Install the Elementary CLI and generate an HTML observability report.
Create the materialization macro and initialize Elementary

You already added the Elementary package to packages.yml and configured the Elementary schema in dbt_project.yml during the previous step. The last piece Elementary needs on Snowflake is a materialization override macro.

Why does Elementary need a materialization override?

Starting with dbt 1.8+, the built-in test materialization changed in a way that prevents Elementary from writing results to its metadata tables. The override macro tells dbt to use Elementary's Snowflake-specific materialization instead, which correctly inserts test results into the elementary_test_results table.

  • Create a new file called elementary_materialization.sql inside your macros/ folder and paste the following code:
{% materialization test, adapter='snowflake' %}
    {{ return(elementary.materialization_test_snowflake()) }}
{% endmaterialization %}

What does this macro do?

This macro overrides dbt's default test materialization specifically for the Snowflake adapter. When dbt runs any test, it routes through Elementary's custom logic instead. This logic inserts results into Elementary's incremental metadata tables so they can power the observability report.

  • Save macros/elementary_materialization.sql.
  • Re-install packages to ensure everything is in sync by running:
dbt deps
  • Initialize Elementary's metadata tables in your Snowflake warehouse by running:
dbt run --select elementary

You should see dbt create a set of tables in an elementary schema in your Snowflake database. The output shows multiple models completing successfully.

What did this command create?

Elementary stores all observability data in its own schema. The dbt run --select elementary command creates tables like elementary_test_results, dbt_run_results, and dbt_models. These tables are empty for now. They will be populated automatically by Elementary's on-run-end hooks during future dbt executions.

Seeing errors during the Elementary run?

If you see a "cannot insert into a view" error, check that your dbt_project.yml does not have a global +materialized: view config that overrides Elementary's settings. The Elementary models need to be incremental tables, not views.

If the run fails with a permissions error, confirm your Snowflake role has CREATE SCHEMA privileges on the database.

help me troubleshoot Elementary run errors in my Snowflake dbt project

Run tests to generate baseline anomaly data

The volume_anomalies test you configured on fct_orders in the previous step is now ready to collect data. Elementary needs at least one test run to establish a baseline before it can detect anomalies.

  • Run your full test suite to populate Elementary's metadata tables by running:
dbt test

You should see all tests pass, including the Elementary volume anomalies test. The test output includes your dbt-expectations tests and the native dbt tests alongside the Elementary test.

What happens during this test run?

Elementary's on-run-end hooks automatically capture every test result and insert it into the elementary_test_results table. The volume anomalies test records today's row count for fct_orders as a data point.

With only one data point, Elementary cannot detect anomalies yet. In a production pipeline, you would run tests on a schedule so the baseline accumulates over days or weeks. After enough history, Elementary flags statistical deviations automatically.

Tests failing after adding the materialization macro?

If tests that previously passed now fail, try running dbt run --select elementary --full-refresh to recreate the metadata tables cleanly. Then run dbt test again.

help me fix Elementary test failures after adding the materialization macro

Install the Elementary CLI and generate the observability report

The Elementary CLI is a separate Python tool that reads from the metadata tables you just populated. It generates a self-contained HTML observability report showing test results, run history, and anomaly detection status.

  • Install the Elementary CLI with the Snowflake connector by running:
pip install elementary-data[snowflake]

What is the edr command?

The elementary-data[snowflake] package installs a CLI tool called edr. This tool connects to your warehouse using a connection profile, reads Elementary's metadata tables, and produces reports or sends alerts.

  • Generate a connection profile for the Elementary CLI by running:
dbt run-operation elementary.generate_elementary_cli_profile

This prints a YAML profile block to your terminal. You need to add it to your profiles file so the CLI can find your Elementary schema.

  • Copy the printed YAML output.
  • Open ~/.dbt/profiles.yml and paste it as a new top-level entry named elementary. Make sure it sits at the same indentation level as your existing ecommerce_analytics profile.
  • Fill in any missing credential fields (password or key path) if they appear as placeholders.
  • Save ~/.dbt/profiles.yml.
  • Generate the HTML observability report by running:
edr report

The CLI generates an HTML file and opens it in your default browser. You should see a dashboard with sections for test results, model run history, and data lineage.

What should I see in the report?

The report shows a summary of all test executions, including your dbt-expectations tests and the Elementary volume anomalies test. Since you only have one data point, the anomaly detection section shows the test as passing with no anomalies detected yet.

In production, this dashboard becomes your single pane of glass for data quality. It tracks trends over time and highlights exactly when and where quality degraded.

Report is empty or shows no test results?

Make sure you ran dbt test after executing dbt run --select elementary. Elementary only captures results from test runs that happen after its metadata tables exist.

Also verify that the elementary profile in ~/.dbt/profiles.yml has the correct schema name matching your Elementary configuration.

help me troubleshoot an empty Elementary report

✔️ Awesome, I've got everything!

Great. Double check you have saved macros/elementary_materialization.sql and confirmed your Elementary report loaded successfully in the browser.

ⓧ I'd like to double check the full code

Here is the new file you created in this step:

{% materialization test, adapter='snowflake' %}
    {{ return(elementary.materialization_test_snowflake()) }}
{% endmaterialization %}

Your dbt_project.yml, packages.yml, and models/marts/schema.yml remain unchanged from the previous step. The Elementary package configuration and volume anomalies test were already in place.

You now have a fully operational observability layer. Elementary is tracking your test results, building anomaly baselines, and producing dashboards you can share with your team. Next up, you will set up Slim CI with GitHub Actions so every pull request automatically validates your pipeline.

Set Up Slim CI with GitHub Actions and dbt Clone

Your pipeline is now fully tested and observable. The final piece of a production-grade data platform is automated testing on every code change.

In this step, you will create a GitHub Actions workflow that uses dbt clone to pre-populate incremental models via Snowflake's zero-copy cloning. This means your CI/CD pipeline tests incremental models in incremental mode, not full refresh.

In this step, get ready to:
  • Initialize a Git repository and create a GitHub Actions CI workflow file.
  • Configure GitHub repository secrets for Snowflake credentials and push to GitHub.
  • Open a test PR, verify CI runs, and generate dbt documentation.
Initialize Git and create the CI workflow

Before pushing to GitHub, you need a local Git repository and a workflow file that defines your CI pipeline.

  • Initialize a Git repository in your project root by running this command:
git init -b main
  • Stage and commit all your project files by running these commands:
git add .
git commit -m "Initial commit: dbt project with medallion architecture"

What do these commands do?

The git init -b main command creates a new Git repository with main as the default branch name.

The git add . command stages every file in the current directory. The git commit saves a snapshot of all staged files as your first commit.

Next, you need a production manifest so that the CI workflow can compare modified models against a known state. The CI uses --state ./prod-manifest to determine which models changed.

  • Create the prod-manifest directory and copy your current manifest into it by running:
mkdir prod-manifest
cp target/manifest.json prod-manifest/manifest.json

Why do you need a prod-manifest?

The dbt clone and dbt build commands use --state to compare the PR branch against a baseline. This baseline is your production manifest.

In a real team environment, this artifact would come from a scheduled production job. For this project, you are copying the manifest from your local build.

  • Confirm the manifest was copied by running:
ls prod-manifest/

You should see manifest.json listed in the output.

  • Create the .github/workflows directory structure by running:
mkdir -p .github/workflows
  • Create the CI workflow file at .github/workflows/dbt_ci.yml in VS Code and paste the following content:
name: dbt Slim CI

on:
  pull_request:
    branches: [main]

env:
  DBT_PROFILES_DIR: .
  SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
  SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
  SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
  SNOWFLAKE_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
  SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
  SNOWFLAKE_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}

jobs:
  dbt-ci:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dbt
        run: |
          pip install dbt-snowflake

      - name: Install dbt packages
        run: dbt deps

      - name: Clone incremental models
        run: |
          dbt clone --select state:modified+,config.materialized:incremental,state:old --state ./prod-manifest

      - name: Build modified models
        run: |
          dbt build --select state:modified+ --state ./prod-manifest

What does this workflow do?

  • The on: pull_request trigger runs this workflow every time someone opens a PR targeting the main branch.
  • The env block reads Snowflake credentials from GitHub Secrets so they are never hardcoded.
  • The dbt clone step uses Snowflake's zero-copy cloning to pre-populate incremental models in the CI schema. This lets them run in incremental mode instead of doing a full refresh.
  • The dbt build --select state:modified+ step only builds models that changed in the PR, plus their downstream dependents.
  • Save .github/workflows/dbt_ci.yml.
  • Confirm the file exists by running:
ls .github/workflows/

You should see dbt_ci.yml listed in the output.

Don't see the file?

Make sure you created the nested directories with mkdir -p .github/workflows before saving the file. The -p flag creates parent directories recursively.

Help me troubleshoot my missing workflow file

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before continuing.

ⓧ I'd like to double check the full code

Here is the complete .github/workflows/dbt_ci.yml file:

name: dbt Slim CI

on:
  pull_request:
    branches: [main]

env:
  DBT_PROFILES_DIR: .
  SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
  SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
  SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
  SNOWFLAKE_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
  SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
  SNOWFLAKE_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}

jobs:
  dbt-ci:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dbt
        run: |
          pip install dbt-snowflake

      - name: Install dbt packages
        run: dbt deps

      - name: Clone incremental models
        run: |
          dbt clone --select state:modified+,config.materialized:incremental,state:old --state ./prod-manifest

      - name: Build modified models
        run: |
          dbt build --select state:modified+ --state ./prod-manifest
Configure GitHub secrets and push to GitHub

Your workflow references Snowflake credentials via ${{ secrets.* }} syntax. You need to create a GitHub repository and store those credentials as repository secrets.

  • Create a new GitHub repository and push your code by running:
gh repo create ecommerce-analytics-dbt --public --source=. --remote=origin --push

What does this command do?

The gh repo create command creates a new repository on GitHub, sets the current directory as the source, adds it as the origin remote, and pushes your committed code in one step.

If you have not authenticated the GitHub CLI yet, run gh auth login first and follow the prompts.

Getting an authentication error?

If you see an error about authentication, run gh auth login and choose HTTPS as your protocol. Follow the browser-based login flow to authenticate.

Help me fix my GitHub CLI authentication

Now add your Snowflake credentials as repository secrets. The workflow needs six secrets to connect to your warehouse.

  • Open your repository on GitHub.
  • Click Settings in the repository navigation bar.
  • In the left sidebar under Security, select Secrets and variables.
  • Click Actions.
  • Click New repository secret.
  • Add each of the following secrets one at a time (use the same values from your profiles.yml):
  • SNOWFLAKE_ACCOUNT (your Snowflake account identifier).
  • SNOWFLAKE_USER (your Snowflake username).
  • SNOWFLAKE_PASSWORD (your Snowflake password).
  • SNOWFLAKE_ROLE (your Snowflake role).
  • SNOWFLAKE_WAREHOUSE (your Snowflake warehouse name).
  • SNOWFLAKE_DATABASE (your Snowflake database name).

Why use secrets instead of hardcoding credentials?

GitHub encrypts repository secrets and only decrypts them at runtime inside the Actions runner. This keeps your Snowflake credentials safe even if the repository is public.

You should now see all six secrets listed on the Actions secrets page in your repository settings.

Test the CI workflow and generate dbt docs

With your code pushed and secrets configured, you can trigger the CI workflow by opening a pull request.

  • Create a new branch and make a small change by running:
git checkout -b test-ci
echo "# Test CI" >> README.md
git add .
git commit -m "Test CI workflow"
git push -u origin test-ci
  • Open a pull request on GitHub targeting the main branch by running:
gh pr create --base main --head test-ci --title "Test CI workflow" --body "Testing dbt Slim CI"

What happens when you open the PR?

Because your workflow triggers on pull_request events targeting main, GitHub automatically starts running the dbt Slim CI workflow.

The dbt clone step uses Snowflake's zero-copy cloning to set up the CI schema, and dbt build runs only modified models.

  • Click the Actions tab in your GitHub repository to watch the workflow run.
  • Click on the running workflow to see the individual step logs.

You should see each step (checkout, Python setup, dbt install, dbt deps, dbt clone, dbt build) complete with green checkmarks.

CI workflow failing?

Check that all six secrets are named exactly as the workflow expects: SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD, SNOWFLAKE_ROLE, SNOWFLAKE_WAREHOUSE, SNOWFLAKE_DATABASE.

Verify the prod-manifest/manifest.json file was committed to your repository. Without it, the --state flag has nothing to compare against.

Help me debug my failing CI workflow

Finally, generate your dbt documentation locally. This produces a data lineage graph and model documentation site.

  • Switch back to your main branch by running:
git checkout main
  • Generate dbt documentation by running:
dbt docs generate

You should see output confirming that catalog.json and manifest.json were written to the target/ directory.

What did dbt docs generate produce?

The manifest.json file contains your entire project's dependency graph, model definitions, and test configurations. The catalog.json file contains column-level metadata pulled directly from Snowflake.

Together they power the dbt documentation site, which you can view locally with dbt docs serve.

Your pipeline now has automated CI testing on every pull request. Next up, the Secret Mission challenges you to optimize Snowflake performance with clustering keys and late-arriving data handling.

Secret mission

Snowflake Performance Optimization and Late-Arriving Data

Your pipeline is correct, but is it fast? In production, fact tables grow to billions of rows and data arrives late. This stretch challenge adds Snowflake clustering keys for query performance and a lookback window to catch late-arriving records. These are the patterns that separate a learning project from a production system.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses Snowflake free trial credits for storage and compute. There are no ongoing compute costs unless you manually run queries or schedule dbt jobs.

Resources you used:

  • Snowflake database (ecommerce_analytics) with bronze, silver, gold, and elementary schemas.
  • Local dbt project folder on your Desktop (including the Python virtual environment).
  • GitHub repository containing your dbt project and CI workflow.

Keep everything running

No action needed. Choose this if you're still actively building or plan to extend the pipeline soon.

  • Your data stays in Snowflake. Storage costs are minimal on a free trial.
  • Your Snowflake warehouse only charges credits when actively running queries. If you're not running dbt, you're not spending credits.
  • Your local project folder and GitHub repository remain in place for future work.

Pause - I'll come back to this later

Suspend your Snowflake warehouse to ensure no accidental compute charges while you're away. Your data and local files stay intact.

  • In the Snowflake UI, open a new worksheet and run this command to suspend your warehouse:
  • Your data remains in the database at minimal storage cost.
  • Your local project folder and GitHub repository are untouched.
ALTER WAREHOUSE your_warehouse_name SUSPEND;

Resume when you're back

When you return, resume your warehouse by running ALTER WAREHOUSE your_warehouse_name RESUME; in a Snowflake worksheet.

Delete - I don't want to use this again

Remove all project resources completely. This is irreversible.

  • Drop the entire Snowflake database by running this command in a Snowflake worksheet:
DROP DATABASE IF EXISTS ecommerce_analytics;

This removes all schemas, tables, snapshots, and Elementary metadata in one command.

  • Delete your local project folder by running the appropriate command for your operating system.

macOS

rm -rf ~/Desktop/ecommerce_analytics

Windows

rmdir /s /q %USERPROFILE%\Desktop\ecommerce_analytics
  • Delete your GitHub repository by navigating to your repository page on GitHub.
  • Click Settings (the tab below your repository name).
  • Scroll to the bottom of the page to the Danger Zone section.
  • Click Delete this repository and follow the confirmation prompts.

Deletion is permanent

Dropping the Snowflake database and deleting the GitHub repository cannot be undone. Make sure you've saved any code or notes you want to keep before proceeding.

That's a Wrap!

That's a Wrap!

Nice work. You just built the exact pipeline senior data engineers build in production.

You've learned how to:

  • Build a full medallion architecture (Bronze/Silver/Gold) with incremental models, MERGE logic, and SCD Type 2 dimension tracking using dbt snapshots.
  • Deploy a production data quality framework with dbt-expectations tests and Elementary anomaly detection for automated observability.
  • Set up Slim CI with GitHub Actions using dbt clone for fast, accurate PR testing with incremental models.
  • Secret Mission: Added Snowflake clustering keys and a lookback window for late-arriving data handling to optimize production performance.

Ready to quiz yourself?