Logistics Analytics with dbt and Snowflake

Build an ELT pipeline with dbt, Snowflake ML, and Power BI for logistics.

Introduction

30 Second Summary

Every time you track a package online, a logistics company is running analytics on millions of shipments to figure out which ones are on track and which ones might arrive late. Predicting delays before they happen is what separates reactive operations from proactive ones.

In this project, you will build a complete logistics analytics pipeline that transforms raw supply chain data into business insights and machine learning predictions. You will use Snowflake as your data warehouse, dbt Cloud to model and test your data, train a classification model to predict delivery delays, and visualize everything in a Power BI dashboard.

What You'll Build

A production-ready ELT pipeline that takes raw order and shipment data, transforms it through a layered dbt project into a star schema, trains a machine learning model to predict late shipments, and delivers analytics through an interactive dashboard.

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

  • A Snowflake data warehouse with role-based access controls and a full dbt project containing staging, intermediate, and mart layers.
  • A data quality framework with built-in and custom dbt tests that validate shipment SLA compliance.
  • A trained Snowflake ML Classification model that predicts delivery delays using gradient boosting, with evaluation metrics and batch inference results.
  • A Power BI dashboard showing delivery KPIs, supplier scorecards, shipping mode analysis, and ML prediction insights.
  • Secret Mission: a dynamic supplier reliability index that combines ML predictions with historical performance to create a composite risk score.

Are there any prerequisites?

This project assumes strong SQL knowledge and familiarity with data warehousing concepts like schemas and tables. You should also understand ELT patterns and have a basic grasp of ML concepts such as training, inference, and model evaluation.

Set Up Snowflake and Configure Access Controls

A production data warehouse needs proper access controls from the very first day. Without them, any user or tool can read, modify, or delete data across the entire system.

In this step, you'll create your Snowflake trial account and set up the foundational infrastructure. This includes a compute warehouse, separate databases for raw and transformed data, and a dedicated role that dbt will use to run transformations.

In this step, get ready to:
  • Sign up for a Snowflake 30-day trial account.
  • Create a virtual warehouse, databases, and schemas.
  • Configure role-based access controls and a service user for dbt.
Sign up for a Snowflake trial

Snowflake offers a 30-day free trial with $400 in credits. No credit card is required.

  • Open signup.snowflake.com in your browser.
  • Fill in your first name, last name, email, and company name.
  • On the edition selection screen, choose Enterprise.
  • For the cloud provider, select AWS.
  • For the region, select US East (N. Virginia).
  • Click GET STARTED.
  • Check your email for the activation link from Snowflake and click it to activate your account.
  • Create a username and password when prompted, then sign in to the Snowflake web interface.

Why Enterprise edition?

Enterprise edition gives you full ACCOUNTADMIN access, which you need to create roles, users, and manage privileges. This is the same setup a senior data engineer would configure in a real production environment.

Not seeing the activation email?

Check your spam folder. The email comes from Snowflake and can take 1-2 minutes to arrive. If it still hasn't appeared after 5 minutes, try signing up again with the same email.

Help me troubleshoot my Snowflake activation email

Create your warehouse, databases, and schemas

Your Snowflake account needs three foundational pieces: a warehouse (compute), databases (storage), and schemas (organization within databases). You'll create all of these using SQL in a worksheet.

  • In the Snowflake web interface, click Worksheets in the left sidebar.
  • Click the + button in the top right, then select SQL Worksheet.
  • Paste the following SQL into the worksheet to create your infrastructure:
-- Create a dedicated warehouse for dbt transformations
CREATE WAREHOUSE TRANSFORMING
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- Create separate databases for raw data and analytics output
CREATE DATABASE RAW;
CREATE DATABASE ANALYTICS;

-- Create schemas for each transformation layer
CREATE SCHEMA ANALYTICS.STAGING;
CREATE SCHEMA ANALYTICS.INTERMEDIATE;
CREATE SCHEMA ANALYTICS.MARTS;

What does this SQL do?

  • TRANSFORMING is an X-Small warehouse that provides compute power for dbt. It auto-suspends after 5 minutes of inactivity to save credits.
  • RAW is reserved for ingested source data. ANALYTICS is where dbt writes transformed models.
  • The three schemas (STAGING, INTERMEDIATE, MARTS) mirror the dbt transformation layers you'll build in later steps.
  • Select all the SQL in the worksheet, then click the Run button (or press Ctrl+Enter).
  • Confirm the output shows a success message for each statement.

Verify your databases were created by running this query:

SHOW DATABASES;

You should see RAW, ANALYTICS, and SNOWFLAKE_SAMPLE_DATA in the results.

What is SNOWFLAKE_SAMPLE_DATA?

This is a pre-loaded shared database that comes with every Snowflake trial account. It contains the TPC-H dataset, an industry-standard supply chain schema with 8 tables including orders, line items, suppliers, and parts. You'll use this as your raw source data instead of loading your own files.

Seeing an error when running the SQL?

Make sure your active role is ACCOUNTADMIN. Check the role selector in the bottom-left of the worksheet. Only ACCOUNTADMIN has permission to create warehouses, databases, and schemas.

Help me fix my Snowflake SQL errors

Configure role-based access controls

In production, you never give tools like dbt full admin access. Instead, you create a dedicated role with only the privileges it needs. This principle of least privilege is a core RBAC pattern.

  • In the same worksheet, paste and run the following SQL to create the TRANSFORMER role, grant it privileges, and create a service user for dbt:
-- Create a role specifically for dbt transformations
CREATE ROLE TRANSFORMER;

-- Grant warehouse access
GRANT USAGE ON WAREHOUSE TRANSFORMING TO ROLE TRANSFORMER;

-- Grant database-level access
GRANT USAGE ON DATABASE RAW TO ROLE TRANSFORMER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE TRANSFORMER;

-- Grant schema-level access in ANALYTICS
GRANT USAGE ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;

-- Grant creation privileges so dbt can build models
GRANT CREATE TABLE ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT CREATE TABLE ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT CREATE TABLE ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;

-- Create a service user for dbt Cloud
CREATE USER DBT_USER
    PASSWORD = 'your-secure-password-here'
    DEFAULT_ROLE = TRANSFORMER;

GRANT ROLE TRANSFORMER TO USER DBT_USER;

What does this SQL do?

  • TRANSFORMER is a custom role that can use the warehouse and create tables/views in ANALYTICS, but cannot drop databases or manage other users.
  • The USAGE grants allow the role to see and query objects. The CREATE grants allow it to write new models.
  • DBT_USER is a service account that dbt Cloud will authenticate as. It inherits the TRANSFORMER role's permissions.

Choose a strong password for DBT_USER

Replace your-secure-password-here with a real password you'll remember. You'll need it in the next step when connecting dbt Cloud to Snowflake.

  • Select all the SQL and click Run (or press Ctrl+Enter).
  • Verify the role was created by running:
SHOW ROLES;

You should see TRANSFORMER in the list of roles.

  • Verify the user was created by running:
SHOW USERS;

You should see DBT_USER alongside your own admin user.

Seeing 'Insufficient privileges' errors?

Only the ACCOUNTADMIN role can create roles and users. Check the role selector in the bottom-left corner of your worksheet and switch to ACCOUNTADMIN if it shows a different role.

Help me troubleshoot Snowflake privilege errors

✔️ Awesome, I've got everything!

Great. Double check you've saved your worksheet so you can reference it later.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run in this step:

-- Create a dedicated warehouse for dbt transformations
CREATE WAREHOUSE TRANSFORMING
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- Create separate databases for raw data and analytics output
CREATE DATABASE RAW;
CREATE DATABASE ANALYTICS;

-- Create schemas for each transformation layer
CREATE SCHEMA ANALYTICS.STAGING;
CREATE SCHEMA ANALYTICS.INTERMEDIATE;
CREATE SCHEMA ANALYTICS.MARTS;

-- Create a role specifically for dbt transformations
CREATE ROLE TRANSFORMER;

-- Grant warehouse access
GRANT USAGE ON WAREHOUSE TRANSFORMING TO ROLE TRANSFORMER;

-- Grant database-level access
GRANT USAGE ON DATABASE RAW TO ROLE TRANSFORMER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE TRANSFORMER;

-- Grant schema-level access in ANALYTICS
GRANT USAGE ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;

-- Grant creation privileges so dbt can build models
GRANT CREATE TABLE ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.STAGING TO ROLE TRANSFORMER;
GRANT CREATE TABLE ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.INTERMEDIATE TO ROLE TRANSFORMER;
GRANT CREATE TABLE ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;
GRANT CREATE VIEW ON SCHEMA ANALYTICS.MARTS TO ROLE TRANSFORMER;

-- Create a service user for dbt Cloud
CREATE USER DBT_USER
    PASSWORD = 'your-secure-password-here'
    DEFAULT_ROLE = TRANSFORMER;

GRANT ROLE TRANSFORMER TO USER DBT_USER;

Your Snowflake environment is fully configured with proper governance controls. Next up, you'll connect dbt Cloud to this infrastructure so it can start building transformation models.

Connect dbt Cloud to Snowflake

Your Snowflake warehouse is ready with databases, schemas, and a dedicated TRANSFORMER role. Now you need a tool that can read from your raw data sources and write transformed models into your ANALYTICS database.

dbt Cloud is the transformation layer that sits between your raw data and your analytics-ready tables. In this step, you will create a dbt Cloud account, connect it to Snowflake using the credentials you set up in Step 1, and initialize your project repository.

In this step, get ready to:
  • Create a free dbt Cloud Developer account and a new project.
  • Configure the Snowflake connection with your warehouse, database, and role.
  • Set up development credentials, test the connection, and initialize a managed repository.
Create a dbt Cloud account and project

dbt Cloud's free Developer plan gives you a browser-based IDE, job scheduling, and up to 3,000 model builds per month. That is more than enough for this project.

  • Open your browser and navigate to getdbt.com.
  • Click Sign Up in the top right corner.
  • Fill in your name, email, and a password, then click Create my account.
  • Verify your email address by clicking the link in the confirmation email dbt sends you.
  • Once signed in, you will see the dbt Cloud dashboard. Click Create project.
  • Enter logistics_analytics as the project name.

Why not use Partner Connect?

Snowflake offers a Partner Connect shortcut that auto-creates a dbt Cloud account and connection. However, manually configuring the connection teaches you proper RBAC setup and credential management.

In production environments, senior engineers configure connections manually to control exactly which role and warehouse each tool uses. That is what you are practicing here.

Configure the Snowflake connection

dbt Cloud needs four pieces of information to connect to your Snowflake account: the account identifier, the role to assume, the target database, and the warehouse to use for compute.

  • On the project setup screen, click Add new connection.
  • Select Snowflake as the data platform.

You will now see the connection fields. Before filling them in, you need to find your Snowflake account identifier.

  • Switch to your Snowflake browser tab.
  • Look at the URL in your browser address bar. It will look something like https://orgname-accountname.snowflakecomputing.com.
  • Copy the portion before .snowflakecomputing.com. This is your account identifier (for example, myorg-myaccount).

Find your account identifier faster

You can also find it in Snowflake by clicking your profile avatar in the bottom left corner. Your account identifier appears under your account name.

  • Switch back to dbt Cloud and fill in the connection fields:
  • In the Account field, paste your Snowflake account identifier (e.g. myorg-myaccount).
  • In the Role field, enter TRANSFORMER.
  • In the Database field, enter ANALYTICS.
  • In the Warehouse field, enter TRANSFORMING.
  • Click Save.

What do these connection fields mean?

The Role tells Snowflake which permissions to apply when dbt runs queries. TRANSFORMER has read access to raw data and write access to the ANALYTICS schemas.

The Database is where dbt writes its output models. The Warehouse provides the compute resources for running those transformation queries.

Connection save failing?

Make sure your account identifier does not include https:// or .snowflakecomputing.com. It should only be the middle portion (e.g. myorg-myaccount).

help me troubleshoot my dbt Cloud connection to Snowflake

Set up development credentials and initialize the project

Development credentials tell dbt which Snowflake user to authenticate as when you run queries from the IDE. You will use the DBT_USER service account you created in Step 1.

  • After saving the connection, dbt Cloud prompts you to configure Development credentials.
  • Select Username & Password as the authentication method.
  • In the Username field, enter DBT_USER.
  • In the Password field, enter the password you set for DBT_USER in Step 1.
  • In the Schema field, enter dev. This is your personal development schema where dbt writes models during development.
  • Click Test connection.

MFA prompt incoming

Snowflake requires multi-factor authentication (MFA) for username/password logins. When you test the connection, you will receive a push notification on the Duo Mobile app.

If you have not set up Duo Mobile yet, Snowflake will walk you through enrolling your device. Download the Duo Mobile app on your phone and follow the on-screen prompts to complete enrollment before testing the connection.

  • Approve the MFA push notification on your phone when prompted.
  • Wait for the green Connection test succeeded message to appear.
  • Click Save.

Connection test failed?

Double check that your DBT_USER password matches exactly what you set in Snowflake. Passwords are case-sensitive.

If you see a timeout error, ensure you approved the Duo MFA push notification within the time window. Try clicking Test connection again and approve the notification promptly.

help me fix my dbt Cloud connection test failure

Now you need to set up a repository for your dbt project files. A managed repository means dbt Cloud hosts the Git repo for you.

  • On the repository setup screen, select Managed as the repository type.
  • Enter logistics-analytics as the repository name.
  • Click Create.

dbt Cloud creates your repository and generates the default project structure. You should see a success message confirming your project is ready.

  • Click Start developing in the IDE to open the dbt Cloud IDE.
  • When prompted, click Initialize dbt project to generate the default project files.

The IDE generates a dbt_project.yml file and a models/example/ directory with sample models. You will replace these with your own models in the next steps.

What is dbt_project.yml?

This is the configuration file at the root of every dbt project. It defines the project name, version, and tells dbt where to find your models, tests, seeds, and macros.

You will customize this file in Step 4 to configure materializations for each layer (views for staging, tables for marts).

  • In the file explorer on the left side of the IDE, click on dbt_project.yml to confirm it exists.
  • Verify you can see the models/ folder containing an example/ subdirectory.

Don't see any files in the IDE?

Make sure you clicked Initialize dbt project when prompted. If you missed the prompt, look for a green Initialize your project button in the IDE header area.

help me initialize my dbt project in dbt Cloud

Your dbt Cloud project is connected to Snowflake and ready to build models. Next up, you will create your first staging models that pull from the TPC-H supply chain dataset and rename columns into business-friendly formats.

Build the Staging Layer

Your dbt Cloud project is connected to Snowflake and ready to transform data.

The staging layer is where raw source data first enters your dbt project. Its job is to rename cryptic column names (like l_shipdate) into business-friendly names (like ship_date) and compute useful fields. In this step, you'll define your data sources and build three staging models that clean the TPC-H dataset into a logistics-ready format.

In this step, get ready to:
  • Define source connections pointing to the TPC-H dataset in Snowflake.
  • Build three staging models that rename columns and compute delivery metrics.
  • Configure materializations and run your models successfully.
Define your data sources

Before dbt can read from the TPC-H tables, it needs a source definition. This YAML file tells dbt where the raw data lives and which tables are available.

  • In the dbt Cloud IDE, expand the models folder in the file tree on the left.
  • Right-click the models folder and select New Folder.
  • Name the folder staging.
  • Right-click the new staging folder and select New File.
  • Name the file _sources.yml.
  • Paste the following source definition into the file:
version: 2

sources:
  - name: tpch
    database: snowflake_sample_data
    schema: tpch_sf1
    tables:
      - name: orders
        columns:
          - name: o_orderkey
            tests:
              - unique
              - not_null
      - name: lineitem
      - name: supplier
        columns:
          - name: s_suppkey
            tests:
              - unique
              - not_null
      - name: nation
      - name: region
      - name: part

What does this file do?

The database and schema fields point dbt to the read-only SNOWFLAKE_SAMPLE_DATA.TPCH_SF1 dataset that comes pre-loaded in every Snowflake trial account.

The tests on o_orderkey and s_suppkey validate that these primary keys are unique and never null. dbt will run these checks whenever you execute dbt test.

  • Save the file by pressing Ctrl+S.

Seeing a YAML parsing error?

YAML is whitespace-sensitive. Make sure each indentation level uses exactly two spaces, not tabs. The tables: key should be indented under the source entry, and each - name: item should align at the same level.

Help me fix a YAML indentation error in my dbt sources file.

What is a dbt source?

A source is dbt's way of declaring raw data that lives outside your project. Instead of hardcoding table names in SQL, you reference them with {{ source('tpch', 'orders') }}. This gives you lineage tracking, freshness checks, and a single place to update if the source location ever changes.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

version: 2

sources:
  - name: tpch
    database: snowflake_sample_data
    schema: tpch_sf1
    tables:
      - name: orders
        columns:
          - name: o_orderkey
            tests:
              - unique
              - not_null
      - name: lineitem
      - name: supplier
        columns:
          - name: s_suppkey
            tests:
              - unique
              - not_null
      - name: nation
      - name: region
      - name: part
Build the staging models

Each staging model reads from one source table and outputs clean, renamed columns. The naming convention stg_tpch__<table> tells anyone on the team exactly where this model's data originates.

  • Right-click the staging folder and select New File.
  • Name the file stg_tpch__orders.sql.
  • Paste the following model into the file:
with source as (
    select * from {{ source('tpch', 'orders') }}
),

renamed as (
    select
        o_orderkey as order_key,
        o_custkey as customer_key,
        o_orderstatus as order_status,
        o_totalprice as total_price,
        o_orderdate as order_date,
        o_orderpriority as order_priority,
        o_clerk as clerk,
        o_shippriority as ship_priority,
        o_comment as comment
    from source
)

select * from renamed

What does this model do?

The with source CTE pulls all rows from the raw ORDERS table using the source reference you just defined. The renamed CTE maps each cryptic TPC-H column (like o_custkey) to a readable name (like customer_key).

This two-CTE pattern (source then renamed) is the standard dbt staging convention. It keeps the model easy to read and maintain.

  • Save stg_tpch__orders.sql.

File not appearing in the tree?

Make sure you created the file inside the models/staging/ folder, not at the project root. If it's in the wrong place, right-click the file and select Rename to move it to the correct path.

Help me fix my file location in the dbt Cloud IDE.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

with source as (
    select * from {{ source('tpch', 'orders') }}
),

renamed as (
    select
        o_orderkey as order_key,
        o_custkey as customer_key,
        o_orderstatus as order_status,
        o_totalprice as total_price,
        o_orderdate as order_date,
        o_orderpriority as order_priority,
        o_clerk as clerk,
        o_shippriority as ship_priority,
        o_comment as comment
    from source
)

select * from renamed

Now create the line items model. This one is the most important for logistics analysis because it contains shipment dates and computed delivery metrics.

  • Create a new file in the staging folder named stg_tpch__line_items.sql.
  • Paste the following model into the file:
with source as (
    select * from {{ source('tpch', 'lineitem') }}
),

renamed as (
    select
        l_orderkey as order_key,
        l_partkey as part_key,
        l_suppkey as supplier_key,
        l_linenumber as line_number,
        l_quantity as quantity,
        l_extendedprice as extended_price,
        l_discount as discount,
        l_tax as tax,
        l_returnflag as return_flag,
        l_linestatus as line_status,
        l_shipdate as ship_date,
        l_commitdate as commit_date,
        l_receiptdate as receipt_date,
        l_shipinstruct as ship_instructions,
        l_shipmode as ship_mode,
        l_comment as comment,
        datediff(day, l_shipdate, l_receiptdate) as transit_days,
        datediff(day, l_commitdate, l_receiptdate) as days_past_commit
    from source
)

select * from renamed

What are the computed fields?

transit_days calculates how many days the shipment actually took (from ship date to receipt date). This is the real delivery time.

days_past_commit measures how many days past the promised delivery date the shipment arrived. A positive value means the shipment was late. These two fields power all the SLA analysis you'll build later.

  • Save stg_tpch__line_items.sql.

Double underscore in the filename?

Yes, the naming convention uses a double underscore (__) between the source name and the table name. This is a dbt community convention: stg_<source>__<table>. Make sure your filename has two underscores, not one.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

with source as (
    select * from {{ source('tpch', 'lineitem') }}
),

renamed as (
    select
        l_orderkey as order_key,
        l_partkey as part_key,
        l_suppkey as supplier_key,
        l_linenumber as line_number,
        l_quantity as quantity,
        l_extendedprice as extended_price,
        l_discount as discount,
        l_tax as tax,
        l_returnflag as return_flag,
        l_linestatus as line_status,
        l_shipdate as ship_date,
        l_commitdate as commit_date,
        l_receiptdate as receipt_date,
        l_shipinstruct as ship_instructions,
        l_shipmode as ship_mode,
        l_comment as comment,
        datediff(day, l_shipdate, l_receiptdate) as transit_days,
        datediff(day, l_commitdate, l_receiptdate) as days_past_commit
    from source
)

select * from renamed

The last staging model handles supplier data.

  • Create a new file in the staging folder named stg_tpch__suppliers.sql.
  • Paste the following model into the file:
with source as (
    select * from {{ source('tpch', 'supplier') }}
),

renamed as (
    select
        s_suppkey as supplier_key,
        s_name as supplier_name,
        s_address as address,
        s_nationkey as nation_key,
        s_phone as phone,
        s_acctbal as account_balance,
        s_comment as comment
    from source
)

select * from renamed

What does this model do?

This model cleans the SUPPLIER table columns into readable names. The nation_key field will later join to the NATION table to enrich suppliers with their geographic region.

  • Save stg_tpch__suppliers.sql.

Seeing a compilation error on save?

Check that your {{ source('tpch', 'supplier') }} reference matches the source name in _sources.yml exactly. The first argument must be tpch (lowercase) and the second must match a table name listed in that file.

Help me fix a dbt source reference error.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

with source as (
    select * from {{ source('tpch', 'supplier') }}
),

renamed as (
    select
        s_suppkey as supplier_key,
        s_name as supplier_name,
        s_address as address,
        s_nationkey as nation_key,
        s_phone as phone,
        s_acctbal as account_balance,
        s_comment as comment
    from source
)

select * from renamed
Configure materializations and run your models

dbt needs to know how to materialize each layer of your project. Staging models should be views (lightweight, always fresh). Mart models will be tables (faster queries for dashboards).

  • Open dbt_project.yml from the root of your project in the file tree.
  • Add the following models: block at the bottom of the file, below the clean-targets: section:
models:
  logistics_analytics:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

What does this configuration do?

Each folder under models/ gets its own materialization strategy. Views are cheap and always reflect the latest source data. Tables are pre-computed and faster to query.

The +schema setting routes each layer into its own schema in your ANALYTICS database. This keeps your warehouse organized and makes permissions management straightforward.

  • Save dbt_project.yml.

Indentation looks wrong?

The models: key must be at the root level (no indentation). The project name logistics_analytics: sits two spaces in, and each layer (staging:, intermediate:, marts:) sits four spaces in.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

name: 'logistics_analytics'
version: '1.0.0'

profile: 'default'

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

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

models:
  logistics_analytics:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

Now run your staging models to confirm everything compiles and executes against Snowflake.

  • In the dbt Cloud IDE, click the command bar at the bottom of the screen.
  • Type the following command and press Enter:
dbt run
  • Wait for the run to complete. You should see green checkmarks next to all three staging models in the run results.

What should I see?

The output should show three successful model runs: stg_tpch__orders, stg_tpch__line_items, and stg_tpch__suppliers. Each creates a view in the ANALYTICS database under the staging schema. The run should complete in a few seconds.

Seeing a database error?

If you see a permission denied error, confirm that your TRANSFORMER role has USAGE granted on the SNOWFLAKE_SAMPLE_DATA database. This database is shared and read-only, but your role still needs USAGE to query it.

If you see a "relation not found" error, double check that the table names in _sources.yml are lowercase (orders, lineitem, supplier) and match exactly.

Help me debug a failed dbt run for my staging models.

Your staging layer is built and running. You now have clean, business-friendly views sitting on top of the raw TPC-H data. Next up, you'll join these models together into an intermediate layer that calculates on-time delivery performance.

Build the Intermediate and Mart Layers

Your staging models give you clean, consistently named versions of the raw TPC-H tables. Now you need to combine them into business-meaningful datasets that answer logistics questions like "which suppliers deliver on time?" and "how does shipping mode affect transit speed?"

In this step, you will build the transformation layers that turn cleaned data into analytics-ready tables. The intermediate layer joins and enriches your staging models, while the mart layer organizes everything into a star schema with fact and dimension tables.

In this step, get ready to:
  • Create an intermediate model that joins line items, orders, and suppliers with delivery performance calculations.
  • Build mart-layer models including a fact table and three dimension tables.
  • Run dbt to materialize all models and verify them in Snowflake.
Create the intermediate shipment performance model

The intermediate layer is where you combine data from multiple staging models and add business logic. Your int_shipment_performance model joins line items with orders and suppliers, then calculates whether each shipment arrived on time.

  • In the dbt Cloud IDE file tree, right-click the models folder and select New Folder.
  • Name it intermediate.
  • Right-click the new intermediate folder and select New File.
  • Name the file int_shipment_performance.sql.
  • Paste the following SQL into the file:
with line_items as (
    select * from {{ ref('stg_tpch__line_items') }}
),

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

suppliers as (
    select * from {{ ref('stg_tpch__suppliers') }}
),

joined as (
    select
        li.order_key,
        li.line_number,
        li.supplier_key,
        s.supplier_name,
        o.order_date,
        o.order_priority,
        li.ship_date,
        li.commit_date,
        li.receipt_date,
        li.ship_mode,
        li.quantity,
        li.extended_price,
        li.discount,
        li.tax,
        li.transit_days,
        li.days_past_commit,
        -- On-time flag: true if received by the committed date
        case
            when li.receipt_date <= li.commit_date then true
            else false
        end as is_on_time,
        -- Net price after discount
        li.extended_price * (1 - li.discount) as net_price,
        -- Total charge including tax
        li.extended_price * (1 - li.discount) * (1 + li.tax) as total_charge
    from line_items li
    inner join orders o on li.order_key = o.order_key
    inner join suppliers s on li.supplier_key = s.supplier_key
)

select * from joined

What does this model do?

  • The three CTEs at the top pull in your staging models using {{ ref() }}, which tells dbt the dependency order.
  • The joined CTE connects each line item to its parent order and its supplier using inner joins on the key columns.
  • The is_on_time flag is the core delivery KPI. It compares the actual receipt date against the committed delivery date.
  • The net_price and total_charge fields apply discount and tax so downstream models get clean financials.
  • Save int_shipment_performance.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • In the dbt Cloud command bar at the bottom, run:
dbt run --select int_shipment_performance

You should see a green success message showing 1 view created in your ANALYTICS.INTERMEDIATE schema.

Seeing a compilation error?

  • Check that the file is inside the models/intermediate/ folder, not in models/staging/.
  • Verify that your staging model names in the ref() calls match exactly: stg_tpch__line_items, stg_tpch__orders, stg_tpch__suppliers (with double underscores).
  • Make sure you saved the file before running the command.

Help me debug my dbt intermediate model compilation error

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before moving on.

ⓧ I'd like to double check the full code

with line_items as (
    select * from {{ ref('stg_tpch__line_items') }}
),

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

suppliers as (
    select * from {{ ref('stg_tpch__suppliers') }}
),

joined as (
    select
        li.order_key,
        li.line_number,
        li.supplier_key,
        s.supplier_name,
        o.order_date,
        o.order_priority,
        li.ship_date,
        li.commit_date,
        li.receipt_date,
        li.ship_mode,
        li.quantity,
        li.extended_price,
        li.discount,
        li.tax,
        li.transit_days,
        li.days_past_commit,
        case
            when li.receipt_date <= li.commit_date then true
            else false
        end as is_on_time,
        li.extended_price * (1 - li.discount) as net_price,
        li.extended_price * (1 - li.discount) * (1 + li.tax) as total_charge
    from line_items li
    inner join orders o on li.order_key = o.order_key
    inner join suppliers s on li.supplier_key = s.supplier_key
)

select * from joined
Build the mart-layer fact and dimension tables

The mart layer organizes your data into a star schema. You will create one fact table (fct_shipments) containing measurable events, and three dimension tables providing descriptive context about suppliers, shipping modes, and dates.

Why a star schema?

A star schema separates measurable business events (facts) from descriptive attributes (dimensions). This makes queries faster in columnar warehouses like Snowflake because the query engine can scan only the columns it needs.

It also simplifies the experience for BI tools like Power BI, which automatically detect star schema relationships for drag-and-drop analytics.

  • In the dbt Cloud IDE file tree, right-click the models folder and select New Folder.
  • Name it marts.
  • Right-click the marts folder and create a new file called fct_shipments.sql.
  • Paste the following SQL into the file:
-- Fact table: one row per shipment line item with delivery metrics
with shipment_performance as (
    select * from {{ ref('int_shipment_performance') }}
)

select
    order_key,
    line_number,
    supplier_key,
    supplier_name,
    order_date,
    order_priority,
    ship_date,
    commit_date,
    receipt_date,
    ship_mode,
    quantity,
    extended_price,
    discount,
    tax,
    net_price,
    total_charge,
    transit_days,
    days_past_commit,
    is_on_time
from shipment_performance

What does this model do?

The fact table selects all columns from the intermediate model. It acts as the central table in your star schema that Power BI and analysts will query for shipment-level metrics. Keeping it as a dedicated mart model gives you a clear contract point between transformation logic and reporting.

  • Save fct_shipments.sql.
  • Create a new file in the marts folder called dim_suppliers.sql.
  • Paste the following SQL:
-- Dimension: supplier attributes enriched with geographic data
with suppliers as (
    select * from {{ ref('stg_tpch__suppliers') }}
),

nations as (
    select
        n_nationkey as nation_key,
        n_name as nation_name,
        n_regionkey as region_key
    from {{ source('tpch', 'nation') }}
),

regions as (
    select
        r_regionkey as region_key,
        r_name as region_name
    from {{ source('tpch', 'region') }}
),

final as (
    select
        s.supplier_key,
        s.supplier_name,
        s.address,
        s.phone,
        s.account_balance,
        n.nation_name,
        r.region_name
    from suppliers s
    inner join nations n on s.nation_key = n.nation_key
    inner join regions r on n.region_key = r.region_key
)

select * from final

What does this model do?

This dimension enriches suppliers with their geographic location by joining directly to the TPC-H nation and region source tables. Notice it uses both {{ ref() }} for the staging model and {{ source() }} for the raw tables that don't have staging models.

  • Save dim_suppliers.sql.
  • Create a new file in the marts folder called dim_shipping_modes.sql.
  • Paste the following SQL:
-- Dimension: shipping mode categories with speed rankings
with shipping_modes as (
    select distinct
        ship_mode as shipping_mode,
        case ship_mode
            when 'REG AIR' then 'Air'
            when 'AIR' then 'Air'
            when 'RAIL' then 'Rail'
            when 'SHIP' then 'Sea'
            when 'TRUCK' then 'Road'
            when 'MAIL' then 'Mail'
            when 'FOB' then 'FOB'
            else 'Other'
        end as mode_category,
        case ship_mode
            when 'REG AIR' then 1
            when 'AIR' then 2
            when 'RAIL' then 3
            when 'TRUCK' then 4
            when 'SHIP' then 5
            when 'MAIL' then 6
            when 'FOB' then 7
            else 8
        end as speed_rank
    from {{ ref('stg_tpch__line_items') }}
)

select * from shipping_modes

What does this model do?

This dimension extracts the 7 distinct TPC-H shipping modes and maps each to a human-readable category (Air, Rail, Sea, Road, Mail, FOB). The speed_rank column gives you a sortable ordering from fastest to slowest for dashboards.

  • Save dim_shipping_modes.sql.
  • Create one more file in the marts folder called dim_dates.sql.
  • Paste the following SQL:
-- Dimension: date spine covering the full TPC-H order date range
with date_spine as (
    select
        dateadd(day, seq4(), '1992-01-01'::date) as date_day
    from table(generator(rowcount => 2557))
),

final as (
    select
        date_day,
        year(date_day) as year,
        quarter(date_day) as quarter,
        month(date_day) as month,
        dayofweek(date_day) as day_of_week,
        dayname(date_day) as day_name,
        monthname(date_day) as month_name,
        year(date_day) || '-Q' || quarter(date_day) as year_quarter
    from date_spine
)

select * from final

What does this model do?

This generates a date dimension using Snowflake's generator() function. It produces 2,557 rows (roughly 7 years of dates from 1992) covering the full TPC-H order date range. Each row breaks a date into year, quarter, month, and day-of-week for time-based slicing in Power BI.

  • Save dim_dates.sql.

You won't see results in Snowflake yet. You will run all models together in the next substep.

✔️ Awesome, I've got everything!

Make sure all four mart files are saved before moving on.

ⓧ I'd like to double check the full code

with shipment_performance as (
    select * from {{ ref('int_shipment_performance') }}
)

select
    order_key,
    line_number,
    supplier_key,
    supplier_name,
    order_date,
    order_priority,
    ship_date,
    commit_date,
    receipt_date,
    ship_mode,
    quantity,
    extended_price,
    discount,
    tax,
    net_price,
    total_charge,
    transit_days,
    days_past_commit,
    is_on_time
from shipment_performance
with suppliers as (
    select * from {{ ref('stg_tpch__suppliers') }}
),

nations as (
    select
        n_nationkey as nation_key,
        n_name as nation_name,
        n_regionkey as region_key
    from {{ source('tpch', 'nation') }}
),

regions as (
    select
        r_regionkey as region_key,
        r_name as region_name
    from {{ source('tpch', 'region') }}
),

final as (
    select
        s.supplier_key,
        s.supplier_name,
        s.address,
        s.phone,
        s.account_balance,
        n.nation_name,
        r.region_name
    from suppliers s
    inner join nations n on s.nation_key = n.nation_key
    inner join regions r on n.region_key = r.region_key
)

select * from final
with shipping_modes as (
    select distinct
        ship_mode as shipping_mode,
        case ship_mode
            when 'REG AIR' then 'Air'
            when 'AIR' then 'Air'
            when 'RAIL' then 'Rail'
            when 'SHIP' then 'Sea'
            when 'TRUCK' then 'Road'
            when 'MAIL' then 'Mail'
            when 'FOB' then 'FOB'
            else 'Other'
        end as mode_category,
        case ship_mode
            when 'REG AIR' then 1
            when 'AIR' then 2
            when 'RAIL' then 3
            when 'TRUCK' then 4
            when 'SHIP' then 5
            when 'MAIL' then 6
            when 'FOB' then 7
            else 8
        end as speed_rank
    from {{ ref('stg_tpch__line_items') }}
)

select * from shipping_modes
with date_spine as (
    select
        dateadd(day, seq4(), '1992-01-01'::date) as date_day
    from table(generator(rowcount => 2557))
),

final as (
    select
        date_day,
        year(date_day) as year,
        quarter(date_day) as quarter,
        month(date_day) as month,
        dayofweek(date_day) as day_of_week,
        dayname(date_day) as day_name,
        monthname(date_day) as month_name,
        year(date_day) || '-Q' || quarter(date_day) as year_quarter
    from date_spine
)

select * from final
Run all models and verify materializations

Your dbt_project.yml already configures how each layer materializes: staging and intermediate as views, marts as tables. Time to execute everything and confirm the results land in Snowflake.

What is materialization?

Materialization controls how dbt persists model results in your warehouse. Views are lightweight (query runs on read), while tables store pre-computed results (faster reads, uses storage). Staging models as views avoids duplicating raw data. Mart models as tables give dashboards fast query performance.

  • Run the full project by entering this command in the dbt Cloud command bar:
dbt run

You should see all 8 models complete successfully: 3 staging views, 1 intermediate view, and 4 mart tables.

What should I see?

The dbt output shows each model with a green checkmark and its materialization type. Look for lines like:

  • OK created view model ANALYTICS.STAGING.stg_tpch__orders
  • OK created view model ANALYTICS.INTERMEDIATE.int_shipment_performance
  • OK created table model ANALYTICS.MARTS.fct_shipments

The final summary line should read Completed successfully. Done. PASS=8.

Seeing failures in the output?

  • If a mart model fails with a "relation does not exist" error, the intermediate model may not have compiled. Run dbt run --select int_shipment_performance first to confirm it works.
  • If you see a permissions error, switch to the Snowflake worksheet and verify that the TRANSFORMER role has CREATE TABLE privileges on the ANALYTICS.MARTS schema.
  • If dim_suppliers fails with a source not found error, check that your _sources.yml includes both nation and region in the tables list.

Help me debug my dbt run failures

Your data warehouse now has a complete star schema with clean, analytics-ready tables. Next up, you will add automated data quality tests and generate documentation so the pipeline stays trustworthy as it scales.

Implement Data Quality Tests and Documentation

Your mart layer is built and ready for consumption. But in a production environment, data pipelines break silently. A supplier key might go missing, a ship mode could contain an unexpected value, or a shipment might slip past its SLA without anyone noticing.

This is where data quality testing comes in. dbt lets you define assertions about your data that run automatically with every build. In this step, you will add built-in tests, write a custom SLA compliance test, and generate full project documentation with lineage tracking.

In this step, get ready to:
  • Add built-in dbt tests (unique, not_null, accepted_values, relationships) to your mart models.
  • Write a custom generic test that validates shipment SLA compliance.
  • Generate dbt documentation and explore the full lineage graph.
Add built-in dbt tests to your mart models

dbt ships with four built-in test types that cover the most common data quality assertions. You define them in a YAML schema file alongside your model definitions.

What are the four built-in dbt tests?

  • unique checks that no two rows share the same value in a column.
  • not_null checks that every row has a value (no NULLs) in a column.
  • accepted_values checks that every value in a column belongs to a defined list.
  • relationships checks referential integrity between two tables (like a foreign key constraint).
  • In the dbt Cloud IDE file explorer, right-click the models/marts folder.
  • Select **Create file** and name it _schema.yml.
  • Paste the following YAML into the new file:
version: 2

models:
  - name: fct_shipments
    description: "Fact table containing shipment-level delivery performance metrics for logistics analysis."
    columns:
      - name: order_key
        tests:
          - not_null
      - name: line_number
        tests:
          - not_null
      - name: supplier_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_suppliers')
              field: supplier_key
      - name: ship_mode
        tests:
          - not_null
          - accepted_values:
              values: ['REG AIR', 'AIR', 'RAIL', 'SHIP', 'TRUCK', 'MAIL', 'FOB']
      - name: is_on_time
        tests:
          - not_null

  - name: dim_suppliers
    description: "Dimension table for supplier attributes including geography."
    columns:
      - name: supplier_key
        tests:
          - unique
          - not_null

  - name: dim_shipping_modes
    description: "Dimension table for shipping mode categories and speed rankings."
    columns:
      - name: shipping_mode
        tests:
          - unique
          - not_null

  - name: dim_dates
    description: "Date dimension for time-based analysis."
    columns:
      - name: date_day
        tests:
          - unique
          - not_null

What does this schema file do?

This file defines automated assertions that dbt checks every time you run your project. If any test fails, dbt stops the pipeline and reports exactly which assertion was violated.

  • The relationships test on supplier_key ensures every shipment references a valid supplier in dim_suppliers.
  • The accepted_values test on ship_mode catches any unexpected shipping methods that might appear in future data loads.
  • The unique tests on dimension tables enforce primary key integrity.
  • Save _schema.yml by pressing **Ctrl+S**.

YAML indentation errors?

YAML is whitespace-sensitive. Make sure you use spaces (not tabs) and that each nested level is indented by exactly 2 spaces. The dbt Cloud IDE will show a red error indicator if your YAML is malformed.

help me fix YAML indentation errors in my dbt schema file

Write a custom generic test for SLA compliance

The built-in tests cover generic data quality patterns. But logistics systems also need domain-specific validations. A shipment that arrives more than a certain number of days past its committed delivery date is an SLA breach that your pipeline should flag automatically.

What is a dbt generic test?

A generic test is a reusable SQL template written in Jinja. You define it once, then apply it to any model and column by referencing it in your YAML schema files. dbt compiles the template with your parameters and runs the resulting query. If the query returns any rows, the test fails.

  • In the dbt Cloud IDE file explorer, locate the tests folder at the root of your project.
  • Right-click the tests folder and select **Create folder**.
  • Name the new folder generic.
  • Right-click the tests/generic folder and select **Create file**.
  • Name the file test_sla_compliance.sql.
  • Paste the following code into the file:
{% test sla_compliance(model, column_name, max_days_past_commit) %}

select
    {{ column_name }}
from {{ model }}
where days_past_commit > {{ max_days_past_commit }}

{% endtest %}

What does this test do?

This test accepts a model, a column name, and a threshold (max_days_past_commit). It selects rows from the model where days_past_commit exceeds the threshold. Any rows returned mean those shipments violated the SLA.

You could apply this test to fct_shipments in your schema YAML with a threshold like 7 or 14 days. For now, having the test defined and available is the key deliverable.

  • Save test_sla_compliance.sql by pressing **Ctrl+S**.
  • Run all tests by typing the following into the dbt Cloud command bar at the bottom of the IDE and pressing **Enter**:
dbt test

You should see output confirming all tests passed. The total number of tests depends on your schema definitions, but expect around 15+ passing assertions.

Seeing test failures?

If a test fails, dbt shows which assertion was violated and the number of failing rows. Check that your _schema.yml column names match exactly what your SQL models produce. For example, ship_mode must match the alias in your staging model.

help me debug failing dbt tests in my logistics project

Generate dbt documentation and explore the lineage graph

dbt can auto-generate a documentation site for your entire project. This includes model descriptions from your YAML files, column-level details, and a visual lineage graph showing how data flows from sources through transformations to your final marts.

  • Generate your project documentation by typing the following into the dbt Cloud command bar and pressing **Enter**:
dbt docs generate

You should see a success message confirming the documentation catalog was built.

  • Click the **book icon** (View Docs) in the top-left area of the dbt Cloud IDE to open the documentation site.
  • In the documentation site, click the **lineage graph icon** (bottom-right corner) to open the full DAG visualization.

You should see a directed acyclic graph showing your TPC-H sources on the left, flowing through staging models, then the intermediate layer, and finally into your four mart tables on the right.

Why does auto-generated documentation matter?

In production data teams, documentation drifts out of date within weeks if maintained manually. dbt generates documentation directly from your code and YAML definitions, so it stays accurate as long as your project compiles.

The lineage graph is especially valuable for impact analysis. When a source table changes, you can immediately see which downstream models and dashboards are affected.

  • Click on the fct_shipments node in the lineage graph to see its description and column details pulled from your _schema.yml.

Don't see the lineage graph?

Make sure you ran dbt docs generate successfully before clicking the docs icon. If the graph is empty, try running dbt run first to ensure all models are compiled, then regenerate the docs.

help me see my dbt lineage graph

✔️ Awesome, I've got everything!

Great. Double check you have saved both _schema.yml and test_sla_compliance.sql.

ⓧ I'd like to double check the full code

Here is the complete _schema.yml file:

version: 2

models:
  - name: fct_shipments
    description: "Fact table containing shipment-level delivery performance metrics for logistics analysis."
    columns:
      - name: order_key
        tests:
          - not_null
      - name: line_number
        tests:
          - not_null
      - name: supplier_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_suppliers')
              field: supplier_key
      - name: ship_mode
        tests:
          - not_null
          - accepted_values:
              values: ['REG AIR', 'AIR', 'RAIL', 'SHIP', 'TRUCK', 'MAIL', 'FOB']
      - name: is_on_time
        tests:
          - not_null

  - name: dim_suppliers
    description: "Dimension table for supplier attributes including geography."
    columns:
      - name: supplier_key
        tests:
          - unique
          - not_null

  - name: dim_shipping_modes
    description: "Dimension table for shipping mode categories and speed rankings."
    columns:
      - name: shipping_mode
        tests:
          - unique
          - not_null

  - name: dim_dates
    description: "Date dimension for time-based analysis."
    columns:
      - name: date_day
        tests:
          - unique
          - not_null

And the complete custom test file:

{% test sla_compliance(model, column_name, max_days_past_commit) %}

select
    {{ column_name }}
from {{ model }}
where days_past_commit > {{ max_days_past_commit }}

{% endtest %}

Your data quality framework is in place. Every future dbt run will automatically validate data integrity across your pipeline. Next up, you will train a machine learning model directly in Snowflake to predict which shipments are likely to arrive late.

Train a Delivery Delay Prediction Model with Snowflake ML

Your dbt project now delivers clean, tested data through a full star schema. The next question is: can you predict which shipments will arrive late before they ship?

In this step, you will train a machine learning model directly inside Snowflake using its native ML Classification function. The model uses gradient boosting to learn patterns from your fct_shipments data and predict whether future shipments will be on time.

In this step, get ready to:
  • Create a training view that selects the right features from your mart layer.
  • Train a binary classification model and run inference on your data.
  • Evaluate model accuracy with precision, recall, and a confusion matrix.
Create the training view

Machine learning models need a curated set of input features and a target label. You will create a view that selects the columns most relevant to predicting delivery delays from your fct_shipments table.

  • Open a new SQL worksheet in the Snowflake web UI.
  • Set your context to the ANALYTICS database and MARTS schema by running this SQL:
-- Set the working context
USE DATABASE ANALYTICS;
USE SCHEMA MARTS;
USE WAREHOUSE TRANSFORMING;
  • Create the training view by running this SQL:
-- Create a view with selected features and the target label
CREATE OR REPLACE VIEW ANALYTICS.MARTS.SHIPMENT_TRAINING_DATA AS
SELECT
    ship_mode,
    order_priority,
    supplier_key,
    quantity,
    extended_price,
    transit_days,
    is_on_time
FROM ANALYTICS.MARTS.FCT_SHIPMENTS;

What does this view do?

This view selects six feature columns and one target label (is_on_time) from your fact table. Snowflake ML treats every column except the target as a training feature.

String columns like ship_mode and order_priority are automatically treated as categorical features. Numeric columns like quantity and extended_price are treated as continuous features.

  • Verify the view was created by running a quick preview:
-- Preview the training data
SELECT * FROM SHIPMENT_TRAINING_DATA LIMIT 10;

You should see 10 rows with columns for ship_mode, order_priority, supplier_key, quantity, extended_price, transit_days, and is_on_time.

View not creating?

Make sure you are running as the ACCOUNTADMIN role (or a role with CREATE VIEW privileges on the MARTS schema). Also confirm you ran the USE DATABASE ANALYTICS command first.

help me troubleshoot why my Snowflake view is not being created

Train the classification model and run inference

Snowflake ML Classification uses a gradient boosting algorithm to learn patterns in your training data. You pass a reference to your view and specify the target column. Snowflake handles the rest.

  • Train the model by running this SQL:
-- Train a binary classification model to predict on-time delivery
CREATE OR REPLACE SNOWFLAKE.ML.CLASSIFICATION ANALYTICS.MARTS.DELIVERY_DELAY_MODEL(
    INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'ANALYTICS.MARTS.SHIPMENT_TRAINING_DATA'),
    TARGET_COLNAME => 'IS_ON_TIME'
);

What does this command do?

SNOWFLAKE.ML.CLASSIFICATION creates a schema-level model object. The INPUT_DATA parameter takes a reference to your training view using SYSTEM$REFERENCE. This passes your role's privileges to the training process, which runs with limited permissions by default.

The model automatically withholds a random sample of your data (up to 20%) to generate evaluation metrics. Training on 6 million rows may take 2-5 minutes depending on your warehouse.

Training time expectations

The X-Small warehouse will take longer to train. If you want faster training, temporarily resize to Small with ALTER WAREHOUSE TRANSFORMING SET WAREHOUSE_SIZE = 'SMALL'. Remember to resize back afterwards to conserve credits.

Once training completes, you will see a success message. The model object now lives in ANALYTICS.MARTS.DELIVERY_DELAY_MODEL.

  • Run inference on your data and save predictions to a new table by running this SQL:
-- Run predictions on the training data and save results
CREATE OR REPLACE TABLE ANALYTICS.MARTS.SHIPMENT_PREDICTIONS AS
SELECT
    *,
    ANALYTICS.MARTS.DELIVERY_DELAY_MODEL!PREDICT(INPUT_DATA => {*}) AS predictions
FROM ANALYTICS.MARTS.SHIPMENT_TRAINING_DATA;

What does PREDICT do?

The !PREDICT method generates a classification prediction for each row. The {*} wildcard expansion creates an object from all columns in the row, automatically mapping feature names to their values. The result is a VARIANT column containing the predicted class and class probabilities.

  • Preview your predictions by running:
-- Check the prediction results
SELECT * FROM ANALYTICS.MARTS.SHIPMENT_PREDICTIONS LIMIT 10;

You should see your original feature columns plus a predictions column containing a JSON object with the predicted class and probability scores for each class.

Getting a privilege error on PREDICT?

Ensure you are running as the same role that created the model (ACCOUNTADMIN). The model owner receives the mladmin role by default, which grants access to all model methods including PREDICT.

help me fix a privilege error when calling PREDICT on my Snowflake ML Classification model

Evaluate model accuracy

Snowflake automatically generates evaluation metrics during training by holding back a portion of your data. You can review precision, recall, F1 scores, and a confusion matrix to understand how well the model performs.

  • View the per-class evaluation metrics by running:
-- Show precision, recall, and F1 for each class
CALL ANALYTICS.MARTS.DELIVERY_DELAY_MODEL!SHOW_EVALUATION_METRICS();

What do these metrics mean?

The output shows metrics for each class (true and false for is_on_time). Precision measures how many predictions of that class were correct. Recall measures how many actual instances of that class the model found. F1 is the harmonic mean of both.

  • View the confusion matrix by running:
-- Show the confusion matrix comparing actual vs predicted classes
CALL ANALYTICS.MARTS.DELIVERY_DELAY_MODEL!SHOW_CONFUSION_MATRIX();

How to read the confusion matrix

The confusion matrix shows counts of actual vs. predicted class combinations. High counts on the diagonal (actual = predicted) mean the model is performing well. Off-diagonal counts represent misclassifications.

You should see the evaluation results displayed in a tabular format in your worksheet output. The confusion matrix shows how many shipments were correctly and incorrectly classified.

SHOW_EVALUATION_METRICS returning empty results?

Evaluation is enabled by default (evaluate: TRUE). If you see empty results, the model may still be training. Wait a few minutes and try again. You can also confirm the model exists by running SHOW SNOWFLAKE.ML.CLASSIFICATION IN SCHEMA ANALYTICS.MARTS.

help me troubleshoot why SHOW_EVALUATION_METRICS returns no results for my Snowflake ML model

Why Snowflake ML instead of dbt Python models?

Snowflake ML Classification runs entirely in SQL and uses native gradient boosting. The alternative (dbt Python models with scikit-learn via Snowpark) requires accepting Snowflake Third Party Terms for Anaconda packages and adds configuration complexity.

The Snowflake-native approach keeps everything in one platform. It also demonstrates a cutting-edge platform feature that is directly relevant to production ML workflows.

Your ML model is trained, predictions are saved, and you have evaluation metrics to prove it works. Next up, you will visualize all of this in a Power BI dashboard.

Build the Power BI Dashboard

Your data pipeline is complete. You have raw supply chain data flowing through dbt transformations into clean mart tables, and a trained ML model predicting delivery delays.

Now you need to make all of that work visible to business stakeholders. In this step, you will connect Power BI Desktop to your Snowflake ANALYTICS database and build a four-page dashboard covering delivery KPIs, supplier performance, shipping mode analysis, and ML prediction results.

In this step, get ready to:
  • Install Power BI Desktop and connect it to your Snowflake ANALYTICS database.
  • Build four report pages with KPIs, supplier rankings, shipping analysis, and ML predictions.
  • Add slicers for date range, region, and order priority to enable interactive filtering.
Install Power BI Desktop and connect to Snowflake

Power BI Desktop is a free Windows application for building interactive reports. It connects natively to Snowflake without requiring any additional drivers.

  • Press the Windows key to open your search bar.
  • Type Microsoft Store and press Enter to open it.
  • In the Microsoft Store search bar, type Power BI Desktop and press Enter.
  • Click Install (or Get) on the Power BI Desktop listing.
  • Wait for the download and installation to complete. This may take a few minutes depending on your internet speed.
  • Once installed, click Open to launch Power BI Desktop.

Why install from the Microsoft Store?

The Microsoft Store version updates automatically with each monthly release. It also does not require administrator privileges to install.

Power BI Desktop may show a sign-in screen or welcome dialog on first launch. You can close these or skip sign-in for now.

Now connect Power BI to your Snowflake data warehouse.

  • In Power BI Desktop, click Get Data on the Home ribbon.
  • In the categories on the left, select Database.
  • Select Snowflake from the list, then click Connect.

A Snowflake connection dialog appears with fields for your server and warehouse.

  • In the Server field, enter your Snowflake account URL. It looks like your-account-identifier.snowflakecomputing.com (without https://).
  • In the Warehouse field, enter TRANSFORMING.
  • Expand Advanced options.
  • In the Role name field, enter TRANSFORMER.
  • In the Database field, enter ANALYTICS.
  • Under Data Connectivity mode, select Import.
  • Click OK.

Find your Snowflake account URL

In Snowflake, click your profile icon in the bottom left corner. Select Account, then click the copy icon next to your account URL. Remove the https:// prefix before pasting it into Power BI.

Power BI now prompts for your Snowflake credentials.

  • Select Snowflake as the authentication type (username/password).
  • Enter DBT_USER as the User name.
  • Enter the password you set for DBT_USER in Step 1.
  • Click Connect.

The Navigator window opens, showing schemas and tables inside your ANALYTICS database.

  • Expand ANALYTICS, then expand the MARTS schema.
  • Check the boxes next to FCT_SHIPMENTS, DIM_SUPPLIERS, DIM_SHIPPING_MODES, DIM_DATES, and SHIPMENT_PREDICTIONS.
  • Click Load.

Power BI imports the data from Snowflake. This may take 1-2 minutes given that FCT_SHIPMENTS contains over 6 million rows.

Why Import mode instead of DirectQuery?

Import mode loads data into Power BI's in-memory engine, making your visuals render instantly. DirectQuery sends a live query to Snowflake on every interaction, which consumes warehouse credits and adds latency.

For a development dashboard, Import mode gives you the best performance at zero ongoing compute cost.

Now define the relationships between your fact and dimension tables.

  • Click the Model view icon in the left sidebar (it looks like a diagram with connected boxes).
  • Drag the supplier_key field from FCT_SHIPMENTS onto supplier_key in DIM_SUPPLIERS.
  • Drag the ship_mode field from FCT_SHIPMENTS onto shipping_mode in DIM_SHIPPING_MODES.
  • Drag the order_date field from FCT_SHIPMENTS onto date_day in DIM_DATES.

You should see relationship lines connecting your fact table to each dimension. This star schema layout powers all the filtering and slicing you will build next.

Connection failed or tables not showing?

Make sure you removed the https:// prefix from your Snowflake server URL. The format should be account-id.snowflakecomputing.com only.

Confirm you entered TRANSFORMER in the Role name field. Without the correct role, the ANALYTICS tables are not visible.

Help me troubleshoot my Power BI Snowflake connection

Build the four report pages

Your data model is ready. Now you will build four report pages that tell the logistics performance story from different angles.

  • Click the Report view icon in the left sidebar (it looks like a bar chart).

You will start with a DAX measure that calculates the on-time delivery rate. This measure powers several visuals across your pages.

  • In the Data pane on the right, right-click FCT_SHIPMENTS.
  • Select New measure.
  • In the formula bar at the top, enter the following DAX formula:
On-Time Rate = DIVIDE(
    COUNTROWS(FILTER(FCT_SHIPMENTS, FCT_SHIPMENTS[is_on_time] = TRUE())),
    COUNTROWS(FCT_SHIPMENTS)
)

What does this measure do?

This DAX measure counts shipments where is_on_time is TRUE and divides by the total number of shipments. The result is a percentage that responds dynamically to any slicer or filter applied to the page.

  • Press Enter to confirm the measure.

Now build Page 1: Delivery Performance KPIs.

  • Double-click the page tab at the bottom (it says Page 1) and rename it to Delivery Performance.
  • In the Visualizations pane, click the Card visual icon.
  • Drag your On-Time Rate measure into the Values field well.
  • Resize the card and position it in the top-left corner of the canvas.
  • Add a second Card visual. Drag order_key from FCT_SHIPMENTS into its Values well and set the aggregation to Count (Distinct) to show total shipments.
  • Now add a trend line. Click the Line chart visual icon in the Visualizations pane.
  • Drag order_date from FCT_SHIPMENTS to the X-axis well.
  • Drag the On-Time Rate measure to the Y-axis well.

You should see a card showing the on-time rate percentage and a line chart showing delivery trends over time.

Now build Page 2: Supplier Scorecard.

  • Click the + icon at the bottom of the page tabs to add a new page.
  • Rename it to Supplier Scorecard.
  • Add a Table visual from the Visualizations pane.
  • Drag supplier_name from DIM_SUPPLIERS and the On-Time Rate measure into the table's Columns well.
  • Click the column header On-Time Rate in the visual to sort suppliers by on-time performance descending.
  • Add a Clustered bar chart visual next to the table.
  • Drag supplier_name from DIM_SUPPLIERS to the Y-axis and On-Time Rate to the X-axis.

Now build Page 3: Shipping Mode Analysis.

  • Add a new page and rename it to Shipping Mode Analysis.
  • Add a Clustered column chart visual.
  • Drag mode_category from DIM_SHIPPING_MODES to the X-axis.
  • Drag extended_price from FCT_SHIPMENTS to the Y-axis (it will sum automatically).
  • Add a second visual: a Clustered bar chart.
  • Drag mode_category to the Y-axis and transit_days from FCT_SHIPMENTS to the X-axis.
  • Click the dropdown arrow next to transit_days in the X-axis well and change the aggregation to Average.

What does this page show?

The first chart compares total shipping cost by mode category (Air, Rail, Road, Sea, Mail, FOB). The second shows average transit days per mode. Together they visualize the cost-vs-speed tradeoff across shipping methods.

Finally, build Page 4: ML Predictions.

  • Add a new page and rename it to ML Predictions.
  • You need to create a histogram of predicted probabilities. In the Data pane, right-click the probability column in SHIPMENT_PREDICTIONS that represents the predicted delay probability.
  • Select New group.
  • Change Group type from List to Bin. Set the bin size to 0.1 and click OK.
  • Add a Clustered column chart visual.
  • Drag the newly created bins field to the X-axis.
  • Drag the same probability column to the Y-axis and change its aggregation to Count.
  • Add a Card visual. Drag the predicted class column from SHIPMENT_PREDICTIONS to its Values well, aggregated as Count (Distinct), to show the number of predicted outcomes.
  • Add another Card visual showing the total row count of SHIPMENT_PREDICTIONS (drag any column and set aggregation to Count) to display total predictions scored.

What does the histogram show?

The histogram displays how predicted delay probabilities are distributed across your shipments. Tall bars near 0.0 mean the model is confident most shipments arrive on time. Bars near 1.0 indicate shipments the model predicts will be late.

Add slicers and save the report

Slicers let dashboard viewers filter all visuals on a page by selecting a date range, region, or priority level. You will add three slicers that apply across your report.

  • Navigate back to the Delivery Performance page tab.
  • In the Visualizations pane, click the Slicer visual icon.
  • Drag order_date from FCT_SHIPMENTS into the slicer's Field well. Power BI automatically creates a date range slicer with a Between slider.
  • Add a second Slicer visual.
  • Drag region_name from DIM_SUPPLIERS into its Field well. This creates a dropdown list of regions.
  • Add a third Slicer visual.
  • Drag order_priority from FCT_SHIPMENTS into its Field well.
  • Position all three slicers at the top of the page.

To make the slicers work across all pages, you need to sync them.

  • Select one of your slicers.
  • In the View ribbon tab, click Sync slicers.
  • In the Sync slicers panel, check all four page names to sync the slicer across every page.
  • Repeat for each of the other two slicers.

Copy slicers to other pages quickly

Select all three slicers (hold Ctrl and click each one), press Ctrl+C, navigate to another page, and press Ctrl+V. The synced slicers will appear in the same position on each page.

Now save your report.

  • Press Ctrl+S.
  • Choose a location on your computer (Desktop is fine).
  • Name the file logistics_analytics_dashboard.pbix and click Save.

Test your slicers by selecting a region and observing that the KPI cards and charts update across all pages.

Slicers not filtering other visuals?

Make sure the relationships are correctly defined in Model view. If region_name is not filtering the fact table, confirm the supplier_key relationship between FCT_SHIPMENTS and DIM_SUPPLIERS exists and is active.

Help me fix my Power BI slicers not filtering correctly

Secret mission

Build a Dynamic Supplier Reliability Index

Your pipeline predicts delays, but can it rank suppliers by reliability? Combine ML predictions with historical performance to create a composite risk score that updates automatically with every dbt run. This is the kind of proactive analytics that separates senior engineers from the rest.

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 a Snowflake 30-day trial with $400 in free credits (no credit card required), a free dbt Cloud Developer account, and a free Power BI Desktop install. There are no ongoing costs unless you exceed your trial credits.

Resources you used:

  • Snowflake trial account (free for 30 days, $400 credits, auto-expires).
  • Snowflake virtual warehouse (TRANSFORMING) and databases (RAW, ANALYTICS).
  • Snowflake ML Classification model object (stored in ANALYTICS database).
  • dbt Cloud Developer account (free forever, no action needed).
  • Power BI Desktop (free local install, no ongoing cost).

Keep everything running

No action needed. Choose this if you're still actively experimenting with your pipeline or want to try new dbt models and ML features.

  • Leave your Snowflake trial active to continue querying your marts and running predictions.
  • Monitor your credit usage in Account > Billing inside Snowflake.
  • Your warehouse auto-suspends after 5 minutes of inactivity, so no credits are consumed while idle.
  • Your dbt Cloud account and Power BI Desktop remain free indefinitely.

Pause - I'll come back to this later

Shut down running processes to conserve your Snowflake trial credits while keeping all your project files and models intact.

  • Your Snowflake warehouse already auto-suspends after 5 minutes of inactivity by default. No manual action needed.
  • Avoid running any scheduled dbt Cloud jobs while paused to prevent unnecessary warehouse wake-ups.
  • Your dbt Cloud project, Power BI reports, and all Snowflake objects (databases, schemas, ML model) remain saved and ready when you return.
  • Remember that your Snowflake trial expires after 30 days regardless of usage.

Delete - I don't want to use this again

Remove all project resources and start fresh. Your Snowflake trial will auto-expire after 30 days even if you take no action.

  • In a Snowflake worksheet, run DROP DATABASE IF EXISTS analytics; and DROP DATABASE IF EXISTS raw; to remove your project databases.
  • Run DROP WAREHOUSE IF EXISTS transforming; to remove your virtual warehouse.
  • In dbt Cloud, go to Account Settings > Projects and delete your logistics_analytics project if desired.
  • Uninstall Power BI Desktop from Settings > Apps > Apps & features if you no longer need it.
  • The Snowflake trial account itself will auto-expire after 30 days with no further action required.

That's a Wrap!

That's a Wrap!

You just built an enterprise-grade logistics analytics pipeline with integrated machine learning from scratch!

From raw supply chain data to a live dashboard with ML-powered delay predictions, you now have a production-ready system that demonstrates end-to-end data engineering skills.

Here's what you accomplished:

  • Designed and deployed a full dbt project with staging, intermediate, and mart layers following the analytics engineering workflow, complete with a data quality framework using built-in and custom tests.
  • Trained and evaluated a Snowflake ML Classification model to predict shipment delays using gradient boosting, then ran batch inference to generate predictions on your data.
  • Delivered a Power BI dashboard with delivery KPIs, supplier scorecards, shipping mode analysis, and ML prediction insights connected to your Snowflake warehouse.
  • Built a dynamic supplier reliability index combining ML prediction probabilities with historical performance to create a composite risk scoring system.

Ready to quiz yourself?