Build a Retail Revenue Pipeline

Build a tested dbt and Snowflake pipeline for trusted retail revenue.

Introduction

30 Second Summary

A retailer can look profitable on a dashboard while duplicate payment events quietly inflate the total. That bad number can shape real business decisions.

In this project, you will build a browser-based retail revenue pipeline with dbt and Snowflake. The pipeline catches unreliable payment data before reporting $545 in trusted revenue.

What You'll Build

Picture running your finished pipeline to see an inflated $760 total corrected to $545 of trusted revenue.

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

  • A trusted daily revenue mart that reports four paid order dates plus $545 in total revenue. Its data tests stop unreliable inputs before they reach the result.
  • A customer-value mart that shows each customer's paid-order count. It also shows their first paid order. Their latest paid order appears beside their lifetime value.
  • A daily deployment that runs the complete build on schedule. Its run history shows logs. Its DAG traces raw payment events through every model to trusted revenue.
  • Secret Mission: A refund-aware mart that preserves $545 in gross revenue. It proves that a $25 refund produces $520 in net revenue.

Are there any prerequisites?

You'll need basic SQL plus active Snowflake Trial and dbt accounts. Everything runs in Microsoft Edge on Windows with no local installation.

Before We Start

This opening checkpoint locks in what you are building before any hands-on work begins. You are committing to a browser-only retail revenue pipeline that prevents duplicate or failed payment events from inflating reported revenue.

Set Up the Guardrailed Snowflake Workspace

Your retail pipeline needs a protected place to run before it can transform unreliable payments. Snowflake provides the data workspace. A resource monitor keeps its warehouse within a daily credit limit.

In this step, you will create the guarded Snowflake workspace. You will connect it to dbt through a browser-based workflow.

In this step, get ready to:
  • Create the guarded Snowflake raw workspace.
  • Connect Snowflake to your existing dbt account.
  • Initialize the dbt Studio project.
Create the guarded Snowflake resources

The raw workspace holds customers, orders, and payment events. Its warehouse uses a daily credit limit plus a short idle timeout to control trial usage.

Protect Your Trial Balance

This setup touches your Snowflake trial balance, so the guardrails go in first. The assigned warehouse suspends at the daily one-credit threshold.

  • Keep your account on the Snowflake Trial.
  • Avoid adding a payment card.
  • Avoid upgrading to paid billing.
  • Press the Windows key to open Windows search.
  • Type Microsoft Edge into the search bar.
  • Press Enter to open Microsoft Edge.
  • Sign in to your Snowflake Trial account.
  • Select ACCOUNTADMIN as your current role.
  • Create a SQL File for the project setup.
  • Build the compute guardrails by pasting this first section into the SQL File:
-- Run this file with the ACCOUNTADMIN role selected in Snowflake.

CREATE OR REPLACE RESOURCE MONITOR RETAIL_DBT_MONITOR
  WITH CREDIT_QUOTA = 1
       FREQUENCY = DAILY
       START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 100 PERCENT DO SUSPEND_IMMEDIATE;

CREATE OR REPLACE WAREHOUSE RETAIL_DBT_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  RESOURCE_MONITOR = RETAIL_DBT_MONITOR;

What Do These Guardrails Do?

  • The RETAIL_DBT_MONITOR resource monitor has a daily credit quota of 1.
  • The suspension trigger stops the assigned warehouse when usage reaches the threshold.
  • The RETAIL_DBT_WH warehouse starts at the X-Small size.
  • The AUTO_SUSPEND setting stops compute after 60 seconds of inactivity.
  • The AUTO_RESUME setting allows Snowflake to restart the warehouse when new work arrives.
  • Select the guardrail statements in the SQL File.
  • Run the selected statements using the control above the SQL File.

You should see successful statuses for the resource monitor and warehouse statements.

Guardrails Not Created?

Confirm that your current role is ACCOUNTADMIN. Only an account administrator can create the resource monitor.

Compare the selected statements with the code above. Make sure the final semicolon is included.

Help me troubleshoot the Snowflake resource monitor or warehouse setup.

  • Build the database layer by pasting this next section below the warehouse statement:
CREATE OR REPLACE DATABASE RETAIL_ANALYTICS;
CREATE OR REPLACE SCHEMA RETAIL_ANALYTICS.RAW;
CREATE OR REPLACE SCHEMA RETAIL_ANALYTICS.ANALYTICS;

USE WAREHOUSE RETAIL_DBT_WH;

CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_CUSTOMERS (
  customer_id NUMBER,
  first_name VARCHAR,
  last_name VARCHAR,
  customer_segment VARCHAR,
  country_code VARCHAR,
  created_at DATE,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_ORDERS (
  order_id NUMBER,
  customer_id NUMBER,
  order_date DATE,
  order_status VARCHAR,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What Does This Section Create?

  • The RETAIL_ANALYTICS database becomes the home for the retail pipeline.
  • The RAW schema stores operational source data.
  • The ANALYTICS schema provides the destination for dbt models.
  • The customer table captures identity plus customer attributes.
  • The order table captures each checkout order plus its status.
  • Select the new database statements in the SQL File.
  • Run the selected statements using the SQL File controls.

You should see successful statuses for the database, schemas, warehouse selection, and two raw tables.

Database Statements Failed?

Confirm that RETAIL_DBT_WH was created by the previous code section. The warehouse selection depends on that resource.

Check that each object name starts with RETAIL_ANALYTICS where shown.

Help me troubleshoot the Snowflake database or raw table statements.

  • Extend the raw layer by pasting this section below the order table:
CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_PAYMENTS (
  payment_event_id VARCHAR,
  order_id NUMBER,
  payment_method VARCHAR,
  payment_status VARCHAR,
  amount NUMBER(10,2),
  processed_at TIMESTAMP,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO RETAIL_ANALYTICS.RAW.RAW_CUSTOMERS
  (customer_id, first_name, last_name, customer_segment, country_code, created_at)
VALUES
  (1, 'Alice', 'Chen', 'consumer', 'US', '2026-01-05'),
  (2, 'Mateo', 'Ruiz', 'business', 'US', '2026-02-11'),
  (3, 'Priya', 'Shah', 'consumer', 'GB', '2026-03-18'),
  (4, 'Noah', 'Williams', 'business', 'CA', '2026-04-09'),
  (5, 'Sofia', 'Rossi', 'consumer', 'IT', '2026-05-22');

What Does This Data Represent?

The payment table records gateway events at payment-event grain. Its status plus amount fields let the pipeline separate successful revenue from failed attempts.

The customer insert adds five people from several segments and countries. Later models can preserve every customer even when no successful payment exists.

  • Select the payment table statement plus the customer insert.
  • Run the selected statements using the SQL File controls.

You should see a successful table status plus confirmation that five customer rows were inserted.

Customer Rows Missing?

Confirm that RAW_CUSTOMERS was created before its insert statement ran.

Check every customer row for matching parentheses plus quoted text values.

Help me troubleshoot the RAW_CUSTOMERS insert.

  • Complete the raw data load by pasting this final setup section below the customer insert:
INSERT INTO RETAIL_ANALYTICS.RAW.RAW_ORDERS
  (order_id, customer_id, order_date, order_status)
VALUES
  (1001, 1, '2026-09-01', 'completed'),
  (1002, 1, '2026-09-10', 'completed'),
  (1003, 2, '2026-09-11', 'completed'),
  (1004, 3, '2026-09-12', 'cancelled'),
  (1005, 4, '2026-09-14', 'completed'),
  (1006, 2, '2026-09-15', 'completed');

INSERT INTO RETAIL_ANALYTICS.RAW.RAW_PAYMENTS
  (payment_event_id, order_id, payment_method, payment_status, amount, processed_at)
VALUES
  ('PAY-001', 1001, 'card', 'succeeded', 120.00, '2026-09-01 10:00:00'),
  ('PAY-002', 1002, 'wallet', 'succeeded', 80.00, '2026-09-10 11:00:00'),
  ('PAY-002', 1002, 'wallet', 'succeeded', 80.00, '2026-09-10 11:00:00'),
  ('PAY-003', 1003, 'card', 'succeeded', 250.00, '2026-09-11 13:30:00'),
  ('PAY-004', 1004, 'card', 'failed', 75.00, '2026-09-12 09:15:00'),
  ('PAY-005', 1005, 'bank_transfer', 'succeeded', 95.00, '2026-09-14 16:45:00'),
  ('PAY-006', 1006, 'card', 'failed', 60.00, '2026-09-15 08:20:00');

Why Is the Payment Feed Messy?

The order insert adds six orders with completed plus cancelled statuses. The payment insert adds seven gateway events.

The two identical PAY-002 rows model a repeated gateway event. The PAY-004 plus PAY-006 rows model failed payment attempts.

  • Select the order insert plus the payment insert.
  • Run the selected statements using the SQL File controls.

You should see confirmation that six order rows plus seven payment rows were inserted.

Raw Inserts Failed?

Run the customer insert before the order insert. This keeps the setup sequence aligned with the supplied data.

Check that both PAY-002 rows remain in the payment insert. The duplicate is intentional.

Help me troubleshoot the RAW_ORDERS or RAW_PAYMENTS insert.

Before you check the raw payment table, what row count do you expect to see?

  • Confirm the payment load by running this query in the Snowflake SQL File:
SELECT COUNT(*) FROM RETAIL_ANALYTICS.RAW.RAW_PAYMENTS;

What Does This Check Prove?

The query counts every gateway event stored in RAW_PAYMENTS. It includes both copies of PAY-002 plus the failed events.

You should see 7 in the result. Good work. The raw payment feed now contains the exact reliability problem this project is designed to catch.

Count Is Not Seven?

Run the complete payment insert once after creating RAW_PAYMENTS. Recreating the table before the insert gives you a clean retry.

Compare all seven payment rows with the supplied setup code. Keep both successful PAY-002 rows.

Help me fix an unexpected RAW_PAYMENTS row count.

Connect Snowflake to the existing dbt account

Partner Connect creates the browser project plus its managed repository. It also creates the connection that dbt uses to reach Snowflake.

Why Partner Connect?

Partner Connect supplies the project connection plus credentials through a browser workflow. This keeps the setup inside your existing Snowflake and dbt accounts.

The activation also creates development plus deployment environments. You can focus on the retail pipeline without generating local key files.

  • Select Admin in Snowflake.
  • Select Partner Connect.
  • Choose dbt from the available partners.
  • Grant access to RETAIL_ANALYTICS in the connection flow.
  • Select your existing dbt account during activation.
  • Complete the Partner Connect activation.
  • Set the generated connection's Database field to RETAIL_ANALYTICS.
  • Set its Warehouse field to RETAIL_DBT_WH.

The browser bridge is now in place. Your dbt account has a managed project, repository, connection, environments, credentials, and the PC_DBT_ROLE role.

Partner Connect Did Not Finish?

Confirm that your Snowflake role still shows ACCOUNTADMIN. Confirm that you selected the existing dbt account during activation.

Return to the Partner Connect flow if the generated project does not appear in dbt.

Help me troubleshoot the Snowflake Partner Connect activation for dbt.

Initialize the connected dbt project

The Partner Connect role now needs explicit access to the guarded warehouse plus the retail database. The project configuration then defines where dbt finds models plus tests.

  • Switch back to Snowflake.
  • Select ACCOUNTADMIN as your current role.
  • Create a second SQL File for the post-connect grants.
  • Grant the generated dbt role access by pasting this code into the new SQL File:
-- Run this file with the ACCOUNTADMIN role after Partner Connect creates PC_DBT_ROLE.

GRANT USAGE ON WAREHOUSE RETAIL_DBT_WH TO ROLE PC_DBT_ROLE;
GRANT ALL ON DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON SCHEMA RETAIL_ANALYTICS.RAW TO ROLE PC_DBT_ROLE;
GRANT ALL ON SCHEMA RETAIL_ANALYTICS.ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON ALL TABLES IN DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON FUTURE TABLES IN DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;

What Do These Grants Allow?

  • Warehouse usage lets PC_DBT_ROLE run dbt queries through RETAIL_DBT_WH.
  • Database plus schema privileges let dbt reach the raw and analytics areas.
  • Existing-table privileges cover the raw tables you created.
  • Future-table privileges cover tables added later in the same database.
  • Select every grant statement in the SQL File.
  • Run the selected statements using the SQL File controls.

You should see successful statuses for all six grants.

Grant Statements Failed?

Confirm that Partner Connect created PC_DBT_ROLE before you run this file. Confirm that your current role is ACCOUNTADMIN.

Check that RETAIL_DBT_WH plus RETAIL_ANALYTICS still exist.

Help me troubleshoot the PC_DBT_ROLE grants.

  • Switch back to the generated project in dbt.
  • Select Initialize your project.
  • Remove the generated example directory using its file-tree menu.
  • Open dbt_project.yml from the project file tree.
  • Replace its contents with this project configuration:
name: 'retail_reliability'
version: '1.0.0'
config-version: 2

model-paths: ['models']
test-paths: ['tests']

clean-targets:
  - 'target'
  - 'dbt_packages'

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

How Is the Project Organized?

  • The project is named retail_reliability at version 1.0.0.
  • dbt reads model files from models.
  • dbt reads singular data tests from tests.
  • Staging plus intermediate models materialize as views for rapid iteration.
  • Business-facing marts materialize as tables for a stable serving layer.
  • Save dbt_project.yml.
  • Select Start developing in Studio IDE.

You should enter the Studio IDE without a Snowflake connection error.

Studio Cannot Connect?

Confirm that the connection uses database RETAIL_ANALYTICS plus warehouse RETAIL_DBT_WH.

Confirm that every statement in setup/post_connect_grants.sql completed successfully.

Help me troubleshoot the dbt Studio connection to Snowflake.

Before you run the final data check, what count should the raw payment source return?

  • Switch back to the Snowflake SQL File from earlier.
  • Verify the raw payment count by running:
SELECT COUNT(*) FROM RETAIL_ANALYTICS.RAW.RAW_PAYMENTS;

What Does the Final Query Confirm?

The query confirms that all seven supplied payment events remain in the raw source. The duplicate event plus failed attempts stay available for the reliability lesson in the next step.

You should see 7 in the result.

  • Switch back to the connected project in dbt Studio IDE.
  • Confirm that Studio IDE remains open without a Snowflake connection error.

That is the foundation in place. Your guarded Snowflake workspace now serves realistic retail data to the initialized dbt project.

✔️ Awesome, I've got everything!

Great. Double check that the Snowflake query returns 7 plus the connected dbt Studio IDE opens successfully.

ⓧ I'd like to double check the full code

Compare your Snowflake setup SQL File with the complete setup/snowflake_setup.sql file below.

-- Run this file with the ACCOUNTADMIN role selected in Snowflake.

CREATE OR REPLACE RESOURCE MONITOR RETAIL_DBT_MONITOR
  WITH CREDIT_QUOTA = 1
       FREQUENCY = DAILY
       START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 100 PERCENT DO SUSPEND_IMMEDIATE;

CREATE OR REPLACE WAREHOUSE RETAIL_DBT_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  RESOURCE_MONITOR = RETAIL_DBT_MONITOR;

CREATE OR REPLACE DATABASE RETAIL_ANALYTICS;
CREATE OR REPLACE SCHEMA RETAIL_ANALYTICS.RAW;
CREATE OR REPLACE SCHEMA RETAIL_ANALYTICS.ANALYTICS;

USE WAREHOUSE RETAIL_DBT_WH;

CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_CUSTOMERS (
  customer_id NUMBER,
  first_name VARCHAR,
  last_name VARCHAR,
  customer_segment VARCHAR,
  country_code VARCHAR,
  created_at DATE,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_ORDERS (
  order_id NUMBER,
  customer_id NUMBER,
  order_date DATE,
  order_status VARCHAR,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW.RAW_PAYMENTS (
  payment_event_id VARCHAR,
  order_id NUMBER,
  payment_method VARCHAR,
  payment_status VARCHAR,
  amount NUMBER(10,2),
  processed_at TIMESTAMP,
  _loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO RETAIL_ANALYTICS.RAW.RAW_CUSTOMERS
  (customer_id, first_name, last_name, customer_segment, country_code, created_at)
VALUES
  (1, 'Alice', 'Chen', 'consumer', 'US', '2026-01-05'),
  (2, 'Mateo', 'Ruiz', 'business', 'US', '2026-02-11'),
  (3, 'Priya', 'Shah', 'consumer', 'GB', '2026-03-18'),
  (4, 'Noah', 'Williams', 'business', 'CA', '2026-04-09'),
  (5, 'Sofia', 'Rossi', 'consumer', 'IT', '2026-05-22');

INSERT INTO RETAIL_ANALYTICS.RAW.RAW_ORDERS
  (order_id, customer_id, order_date, order_status)
VALUES
  (1001, 1, '2026-09-01', 'completed'),
  (1002, 1, '2026-09-10', 'completed'),
  (1003, 2, '2026-09-11', 'completed'),
  (1004, 3, '2026-09-12', 'cancelled'),
  (1005, 4, '2026-09-14', 'completed'),
  (1006, 2, '2026-09-15', 'completed');

INSERT INTO RETAIL_ANALYTICS.RAW.RAW_PAYMENTS
  (payment_event_id, order_id, payment_method, payment_status, amount, processed_at)
VALUES
  ('PAY-001', 1001, 'card', 'succeeded', 120.00, '2026-09-01 10:00:00'),
  ('PAY-002', 1002, 'wallet', 'succeeded', 80.00, '2026-09-10 11:00:00'),
  ('PAY-002', 1002, 'wallet', 'succeeded', 80.00, '2026-09-10 11:00:00'),
  ('PAY-003', 1003, 'card', 'succeeded', 250.00, '2026-09-11 13:30:00'),
  ('PAY-004', 1004, 'card', 'failed', 75.00, '2026-09-12 09:15:00'),
  ('PAY-005', 1005, 'bank_transfer', 'succeeded', 95.00, '2026-09-14 16:45:00'),
  ('PAY-006', 1006, 'card', 'failed', 60.00, '2026-09-15 08:20:00');

What Should This File Create?

This complete setup creates the resource monitor, warehouse, retail database, raw schemas, analytics schema, and three raw tables. It loads five customers, six orders, and seven payment events.

Compare your second SQL File with the complete setup/post_connect_grants.sql file below.

-- Run this file with the ACCOUNTADMIN role after Partner Connect creates PC_DBT_ROLE.

GRANT USAGE ON WAREHOUSE RETAIL_DBT_WH TO ROLE PC_DBT_ROLE;
GRANT ALL ON DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON SCHEMA RETAIL_ANALYTICS.RAW TO ROLE PC_DBT_ROLE;
GRANT ALL ON SCHEMA RETAIL_ANALYTICS.ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON ALL TABLES IN DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;
GRANT ALL ON FUTURE TABLES IN DATABASE RETAIL_ANALYTICS TO ROLE PC_DBT_ROLE;

What Should These Grants Cover?

This file gives PC_DBT_ROLE access to the guarded warehouse plus the retail database. It covers both existing and future tables.

Compare the Studio IDE project configuration with the complete dbt_project.yml file below.

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

model-paths: ['models']
test-paths: ['tests']

clean-targets:
  - 'target'
  - 'dbt_packages'

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

What Should This Configuration Control?

This complete configuration defines the project paths plus cleanup targets. It materializes staging and intermediate models as views while marts become tables.

Your workspace is connected and ready. Next, you will build a naive revenue model that exposes exactly why raw payment events cannot be trusted.

Expose the Untrusted Revenue

Your guarded Snowflake workspace now holds seven raw payment events. The business still needs proof that those events can produce a trustworthy revenue number.

A direct total trusts every gateway event it receives. In this step, you will use dbt to turn that risk into visible evidence.

In this step, get ready to:
  • Declare the customer, order, and payment tables as raw sources.
  • Preview a temporary revenue model that sums every raw payment amount.
  • Run a temporary uniqueness test that exposes the repeated payment event.
Declare the raw sources

A dbt source maps a logical name to a raw database table. Source freshness records how old that input is allowed to become before dbt raises a warning or error.

  • Use the new-file control beside models in the dbt Studio project file tree to create models/sources.yml.
  • Add the shared source configuration plus the customer declaration by entering this first block:
version: 2

sources:
  - name: retail_raw
    description: Raw operational exports for the retail storefront.
    database: RETAIL_ANALYTICS
    schema: RAW
    config:
      freshness:
        warn_after: {count: 12, period: hour}
        error_after: {count: 24, period: hour}
      loaded_at_field: _loaded_at
    tables:
      - name: customers
        identifier: RAW_CUSTOMERS
        description: One current record per storefront customer.
        columns:
          - name: customer_id
            data_tests:
              - not_null

What Does This Source Block Establish?

The source name retail_raw points dbt to the RETAIL_ANALYTICS.RAW location. Models can now refer to these tables through a stable logical name.

The freshness thresholds use _loaded_at to measure source age. The customer test requires every customer_id to contain a value.

  • Save models/sources.yml.
  • Confirm the editor shows no YAML syntax warning for the customer declaration.
  • Place your cursor after the customer not_null test. Append the order and payment declarations with this block:
      - name: orders
        identifier: RAW_ORDERS
        description: One record per checkout order, including cancelled orders.
        columns:
          - name: order_id
            data_tests:
              - not_null
      - name: payments
        identifier: RAW_PAYMENTS
        description: Payment gateway events that can contain retries and failed attempts.
        columns:
          - name: payment_event_id
            data_tests:
              - not_null
          - name: order_id
            data_tests:
              - not_null

What Do These Declarations Add?

The orders source points to RAW_ORDERS. Its first test requires a populated order identifier.

The payments source points to RAW_PAYMENTS. Its initial tests require populated payment and order identifiers.

  • Save models/sources.yml.
  • Confirm the editor accepts all three table declarations without a YAML syntax warning.
  • Find this payment event test block:
          - name: payment_event_id
            data_tests:
              - not_null

What Does the Current Test Cover?

The current test checks whether every payment event has an identifier. It does not yet check whether an identifier appears more than once.

  • Add the temporary unique test directly below not_null so the block looks like this:
          - name: payment_event_id
            data_tests:
              - not_null
              - unique

What Does the Temporary Test Measure?

The unique test checks whether each payment_event_id occurs once. Keeping it beside not_null gives the raw identifier two separate quality checks.

  • Save models/sources.yml.
  • Confirm the temporary unique entry aligns with the existing not_null entry.

Your source contract now covers all three raw tables. It also contains the temporary payment-event uniqueness check needed for this investigation.

Create the naive revenue model

A mart presents business-facing data for reporting. This temporary mart reads the raw payment source directly so you can measure its result before adding any cleaning logic.

  • Use the new-folder control beside models in the dbt Studio project file tree to create models/marts.
  • Use the new-file control beside models/marts to create models/marts/fct_daily_revenue.sql.
  • Build the temporary revenue total by entering this query:
select sum(amount) as gross_revenue from {{ source('retail_raw', 'payments') }}

What Does This Model Do?

The source() function resolves the logical retail_raw.payments source to its Snowflake table. It also records the dependency in the dbt graph.

The sum(amount) expression combines the numeric payment amounts into one value. The result is labelled gross_revenue for the preview.

  • Save models/marts/fct_daily_revenue.sql.

✔️ I Have Both Files

Good progress. Your source declarations and temporary revenue model are ready for the first preview.

ⓧ I'd Like to Double Check the Full Code

Compare your complete models/sources.yml file with this version.

version: 2

sources:
  - name: retail_raw
    description: Raw operational exports for the retail storefront.
    database: RETAIL_ANALYTICS
    schema: RAW
    config:
      freshness:
        warn_after: {count: 12, period: hour}
        error_after: {count: 24, period: hour}
      loaded_at_field: _loaded_at
    tables:
      - name: customers
        identifier: RAW_CUSTOMERS
        description: One current record per storefront customer.
        columns:
          - name: customer_id
            data_tests:
              - not_null
      - name: orders
        identifier: RAW_ORDERS
        description: One record per checkout order, including cancelled orders.
        columns:
          - name: order_id
            data_tests:
              - not_null
      - name: payments
        identifier: RAW_PAYMENTS
        description: Payment gateway events that can contain retries and failed attempts.
        columns:
          - name: payment_event_id
            data_tests:
              - not_null
              - unique
          - name: order_id
            data_tests:
              - not_null

What Should This File Contain?

The full file declares the shared freshness configuration plus the customer, order, and payment sources. The temporary unique test belongs under payment_event_id.

Compare your complete models/marts/fct_daily_revenue.sql file with this version.

select sum(amount) as gross_revenue from {{ source('retail_raw', 'payments') }}

What Should This File Contain?

The temporary model contains one direct aggregate over the raw payment source. Its only output column is gross_revenue.

Before you preview the model, predict the total that all raw gateway rows will produce.

  • Preview the temporary revenue model from the dbt Studio command bar by running:
dbt show --select "fct_daily_revenue"

What Did the Preview Reveal?

The preview displays GROSS_REVENUE as 760.00.

The direct sum includes every raw payment row. It therefore counts successful events plus failed attempts.

The repeated PAY-002 event contributes an extra 80.00.

The two failed attempts contribute another 135.00.

You have captured the problem in a visible metric. The raw feed currently reports more revenue than the retailer truly earned.

Preview Not Showing 760.00?

Confirm that fct_daily_revenue.sql reads from retail_raw and payments. Check that the model does not contain a status filter or grouping clause.

Compare the database and schema values in models/sources.yml with the full file above.

Help me troubleshoot the unexpected dbt revenue preview.

Turn the duplicate into a reproducible failure

A uniqueness data test returns failing records when the tested value repeats. This gives the team a repeatable quality gate instead of relying on someone to notice an inflated total.

Before you run the test, predict whether every raw payment event ID will prove unique.

  • Test the raw payment source from the dbt Studio command bar by running:
dbt test --select "source:retail_raw.payments"

This Failure Is Intentional

The command finishes with a failed uniqueness test for payment_event_id. The other raw payment tests pass.

This failure proves the source does not satisfy the temporary uniqueness expectation. Keep the test in place so the next step begins from reproducible evidence.

  • Select the failed uniqueness test in the run results.
  • Open its Details view.
  • Confirm that the failure details identify two records for PAY-002.

The duplicated PAY-002 value now appears in the test evidence. Your inflated metric has a specific source-level cause.

Did Every Test Pass?

Compare the payment_event_id block in models/sources.yml with the full file above. The unique entry must align with not_null under data_tests.

Help me find why the temporary payment uniqueness test passed.

You now have an inflated revenue result plus a repeatable test failure that explains it. Next, you will build a clean payment layer that restores the trusted revenue total.

Deduplicate Payments and Recover Revenue

The failing raw-payment test exposed the weakness behind the $760 total. Your dbt project is still treating every gateway event as revenue.

This step moves cleanup into a staging layer. You will use deduplication to keep one successful record per payment event before revenue reaches the mart.

In this step, get ready to:
  • Build clean staging models for the raw order and payment feeds.
  • Create a trusted paid-order grain.
  • Replace the inflated mart with trusted daily revenue.
Build clean staging models

Raw sources record what arrived from operational systems. Staging models turn those records into reliable inputs with explicit rules.

  • Switch back to models/sources.yml in dbt Studio.
  • Remove the temporary unique test from payments.payment_event_id.
  • Save models/sources.yml.
  • Confirm that raw payment duplicates are permitted by running:
dbt build

What does this command prove?

The build evaluates the models with their current tests. Removing the raw uniqueness test allows the duplicated gateway event to remain in the source without failing the project.

That clears the intended failure. Raw duplicates are now accepted because the staging layer owns the cleanup contract.

Does the raw uniqueness test still fail?

Check that you removed only the unique entry beneath payment_event_id. Keep the not_null test in place.

Save models/sources.yml before running the build again.

help me remove the temporary raw payment uniqueness test

  • Create models/staging/stg_orders.sql with the code below by using the file creation control in dbt Studio:
select
  order_id,
  customer_id,
  order_date,
  order_status,
  _loaded_at
from {{ source('retail_raw', 'orders') }}

What does this model do?

  • The model selects the order fields needed by downstream revenue logic.
  • The source() reference creates a dependency on the raw orders source.
  • The _loaded_at field preserves the source loading timestamp.
  • Save models/staging/stg_orders.sql.
  • Confirm that the order staging view builds by running:
dbt build

What does this build check?

The build creates stg_orders as a view because the existing project configuration assigns view materialization to staging models.

You should see the order staging model complete successfully with the available source tests.

Does the order model fail?

Check that stg_orders.sql sits inside models/staging.

Compare the source('retail_raw', 'orders') reference with the source declaration in models/sources.yml.

help me debug the stg_orders dbt model

Payment cleanup needs a window function because the same gateway event can appear more than once. Ranking each event group gives the model a repeatable way to select one record.

  • Create models/staging/stg_payments.sql with the code below by using the file creation control in dbt Studio:
with ranked_payment_events as (
  select
    payment_event_id,
    order_id,
    payment_method,
    payment_status,
    amount,
    processed_at,
    _loaded_at,
    row_number() over (
      partition by payment_event_id
      order by _loaded_at desc
    ) as payment_event_rank
  from {{ source('retail_raw', 'payments') }}
)

select
  payment_event_id,
  order_id,
  payment_method,
  amount,
  processed_at,
  _loaded_at
from ranked_payment_events
where payment_event_rank = 1
  and payment_status = 'succeeded'

How does the cleanup work?

  • The ranked_payment_events common table expression groups records by payment_event_id.
  • The row_number() calculation ranks the newest loaded record first within each event group.
  • The payment_event_rank = 1 filter retains one record per gateway event.
  • The payment_status = 'succeeded' filter prevents failed attempts from becoming revenue.
  • Save models/staging/stg_payments.sql.
  • Confirm that the cleaned payment view builds by running:
dbt build

What does this build check?

The build creates the payment staging view from the raw feed. The duplicated PAY-002 event is reduced to one successful record inside that view.

You should see both staging models complete successfully.

Does the payment model fail?

Check the parentheses around row_number() and the ranked_payment_events common table expression.

Confirm that payment_status remains inside the ranked query because the final filter needs it.

help me debug the stg_payments deduplication model

The raw source can contain duplicate events. The cleaned payment model needs a uniqueness contract so downstream models can trust its grain.

  • Create models/staging/staging.yml with the order contract below by using the file creation control in dbt Studio:
version: 2

models:
  - name: stg_orders
    description: Clean order records at one row per order.
    columns:
      - name: order_id
        description: Order primary key.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed', 'cancelled']

What does the order contract protect?

  • The key tests require one non-null record per order_id.
  • The customer identifier must be present on every staged order.
  • The accepted-values test restricts order status to the two values present in the retail feed.
  • Save models/staging/staging.yml.

The editor should show the YAML without an indentation warning.

  • Add the payment contract below the stg_orders block:
  - name: stg_payments
    description: Deduplicated successful payment events at one row per payment event.
    columns:
      - name: payment_event_id
        description: Payment gateway event primary key after deduplication.
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_orders')
                field: order_id
      - name: payment_method
        data_tests:
          - accepted_values:
              arguments:
                values: ['card', 'wallet', 'bank_transfer']

What does the payment contract protect?

  • Uniqueness is enforced on stg_payments.payment_event_id after deduplication.
  • The relationship test requires each paid event to match an order in stg_orders.
  • The accepted-values test protects the known payment-method set.
  • Save models/staging/staging.yml.
  • Confirm that the staging contracts pass by running:
dbt build

What does this build prove?

The build runs the staging models with their new tests. A successful result proves that payment events are unique after cleanup even though the raw feed remains duplicated.

You should see the available models and staging tests complete successfully.

Does a staging test fail?

Check the indentation beneath each data_tests key. YAML uses spaces to determine which tests belong to each column.

Confirm that the payment relationship points to ref('stg_orders').

help me debug the staging data tests

Create the trusted paid-order grain

The marts need a reusable row for each paid order. This intermediate model joins cleaned payments to orders before aggregating their revenue.

  • Create models/intermediate/int_paid_orders.sql with the code below by using the file creation control in dbt Studio:
select
  orders.order_id,
  orders.customer_id,
  orders.order_date,
  sum(payments.amount) as gross_revenue
from {{ ref('stg_orders') }} as orders
inner join {{ ref('stg_payments') }} as payments
  on orders.order_id = payments.order_id
group by
  orders.order_id,
  orders.customer_id,
  orders.order_date

What does this model do?

  • The inner join keeps orders with trusted successful payments.
  • The grouping creates one row per paid order.
  • The gross_revenue calculation supports orders with more than one valid payment event.
  • The ref() calls create dependencies on both staging models.
  • Save models/intermediate/int_paid_orders.sql.
  • Confirm that the paid-order view builds by running:
dbt build

What does this build check?

The build creates int_paid_orders as a view after its staging dependencies pass. The resulting grain is one trusted row per paid order.

You should see the intermediate model complete successfully after both staging models.

Does the paid-order model fail?

Check that both join references use the exact staging model names.

Confirm that every selected order field also appears in the group by section.

help me debug the paid-order grain

Replace the naive revenue mart

The temporary mart sums raw gateway rows directly. Repointing it to the paid-order grain makes the cleanup rules part of every reported revenue value.

  • Return to models/marts/fct_daily_revenue.sql in dbt Studio.
  • Replace its temporary query with the final model below:
select
  order_date as revenue_date,
  count(*) as paid_order_count,
  sum(gross_revenue) as gross_revenue
from {{ ref('int_paid_orders') }}
group by order_date

What does the final mart do?

  • The model groups trusted paid orders by their order date.
  • The paid_order_count field counts trusted paid orders for each date.
  • The gross_revenue field sums revenue after payment cleanup.
  • Save models/marts/fct_daily_revenue.sql.

Before you run the complete build, do you expect the raw duplicate to fail a test or the staged payment data to pass?

  • Build the complete available graph by running:
dbt build

What does the complete build prove?

The build follows the dependency graph from sources through staging and intermediate models into the mart. It also evaluates the available data tests against the cleaned outputs.

You should see every available model and test complete successfully.

The pipeline now accepts messy raw events while enforcing uniqueness at the trusted staging boundary.

Before you check the total, do you expect the corrected revenue to remain at $760 or fall after the cleanup rules?

  • Calculate the trusted total from the finished mart by running:
dbt show --inline "select sum(gross_revenue) as gross_revenue from {{ ref('fct_daily_revenue') }}"

What does this query check?

The inline query reads the built revenue mart through ref('fct_daily_revenue'). It sums the four daily revenue rows into one verification value.

You should see GROSS_REVENUE equal 545.00.

The deduplication rule removes one extra $80 event. The status filter removes $135 from failed attempts.

That is the key correction complete. Your mart now reports $545 from four paid order dates instead of repeating unreliable gateway data.

Still seeing $760?

Confirm that fct_daily_revenue.sql reads from ref('int_paid_orders') instead of the raw payments source.

Check that stg_payments.sql applies both final filters.

help me trace why the revenue total is still inflated

✔️ Awesome, I've got everything!

Your available models and tests pass. Double check that every changed file is saved before moving on.

ⓧ I'd like to double check the full code

Your existing dbt_project.yml should still contain these materialization rules.

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

model-paths: ['models']
test-paths: ['tests']

clean-targets:
  - 'target'
  - 'dbt_packages'

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

What should this file confirm?

Staging and intermediate models use views. Mart models use tables.

Your final models/sources.yml should allow duplicate raw payment event identifiers.

version: 2

sources:
  - name: retail_raw
    description: Raw operational exports for the retail storefront.
    database: RETAIL_ANALYTICS
    schema: RAW
    config:
      freshness:
        warn_after: {count: 12, period: hour}
        error_after: {count: 24, period: hour}
      loaded_at_field: _loaded_at
    tables:
      - name: customers
        identifier: RAW_CUSTOMERS
        description: One current record per storefront customer.
        columns:
          - name: customer_id
            data_tests:
              - not_null
      - name: orders
        identifier: RAW_ORDERS
        description: One record per checkout order, including cancelled orders.
        columns:
          - name: order_id
            data_tests:
              - not_null
      - name: payments
        identifier: RAW_PAYMENTS
        description: Payment gateway events that can contain retries and failed attempts.
        columns:
          - name: payment_event_id
            data_tests:
              - not_null
          - name: order_id
            data_tests:
              - not_null

What should this file confirm?

The raw payment event identifier retains its null check. Its temporary uniqueness test is gone.

Your models/staging/stg_orders.sql file should look like this.

select
  order_id,
  customer_id,
  order_date,
  order_status,
  _loaded_at
from {{ source('retail_raw', 'orders') }}

What should this file confirm?

The model selects the order fields used by the paid-order grain.

Your models/staging/stg_payments.sql file should look like this.

with ranked_payment_events as (
  select
    payment_event_id,
    order_id,
    payment_method,
    payment_status,
    amount,
    processed_at,
    _loaded_at,
    row_number() over (
      partition by payment_event_id
      order by _loaded_at desc
    ) as payment_event_rank
  from {{ source('retail_raw', 'payments') }}
)

select
  payment_event_id,
  order_id,
  payment_method,
  amount,
  processed_at,
  _loaded_at
from ranked_payment_events
where payment_event_rank = 1
  and payment_status = 'succeeded'

What should this file confirm?

The model ranks duplicate events and retains one successful payment record per event identifier.

Your current models/staging/staging.yml file should contain the contracts available at this point.

version: 2

models:
  - name: stg_orders
    description: Clean order records at one row per order.
    columns:
      - name: order_id
        description: Order primary key.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed', 'cancelled']

  - name: stg_payments
    description: Deduplicated successful payment events at one row per payment event.
    columns:
      - name: payment_event_id
        description: Payment gateway event primary key after deduplication.
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_orders')
                field: order_id
      - name: payment_method
        data_tests:
          - accepted_values:
              arguments:
                values: ['card', 'wallet', 'bank_transfer']

What should this file confirm?

The staging contracts enforce trusted keys and valid values. The payment relationship test connects each payment to an available staged order.

Your models/intermediate/int_paid_orders.sql file should look like this.

select
  orders.order_id,
  orders.customer_id,
  orders.order_date,
  sum(payments.amount) as gross_revenue
from {{ ref('stg_orders') }} as orders
inner join {{ ref('stg_payments') }} as payments
  on orders.order_id = payments.order_id
group by
  orders.order_id,
  orders.customer_id,
  orders.order_date

What should this file confirm?

The intermediate model joins trusted payments to orders and returns one revenue row per paid order.

Your final models/marts/fct_daily_revenue.sql file should look like this.

select
  order_date as revenue_date,
  count(*) as paid_order_count,
  sum(gross_revenue) as gross_revenue
from {{ ref('int_paid_orders') }}
group by order_date

What should this file confirm?

The mart groups paid-order revenue by date and produces the trusted daily metric.

Your payment feed now has a trusted boundary. Next up, you will add customer-value metrics and broader contracts across the complete model graph.

Ship Tested Revenue and Customer Marts

Your dbt pipeline now turns unreliable payment events into a trusted $545 revenue total. A business-ready analytics product also needs reusable customer metrics.

This step expands the layered model graph in Snowflake. Data tests protect each model grain as the pipeline changes.

In this step, get ready to:
  • Build the customer staging model and customer-value mart.
  • Document each model grain and enforce its data contracts.
  • Run the complete quality gate and inspect the connected DAG.
Complete the layered model graph

The customer mart needs a clean customer input before it can calculate lifetime value. Your existing materialization settings keep the staging model as a view.

  • Expand the models/staging folder in the Studio IDE file explorer.
  • Create models/staging/stg_customers.sql inside that folder.
  • Add the customer staging query by pasting this code:
select
  customer_id,
  first_name,
  last_name,
  customer_segment,
  country_code,
  created_at,
  _loaded_at
from {{ source('retail_raw', 'customers') }}

What Does This Model Do?

  • The model reads the declared customers source through source().
  • The selected columns preserve customer identity and segmentation data.
  • The _loaded_at column keeps the source loading timestamp available downstream.
  • Save models/staging/stg_customers.sql.
  • Verify the new staging model by running:
dbt build

What Does This Build Check?

This build resolves the new source dependency before creating the customer view. A successful result proves that the model compiles against the connected retail database.

You should see stg_customers complete successfully with the existing models and tests.

Customer Staging Model Not Building?

Check that stg_customers.sql sits inside models/staging. Confirm that the source name remains retail_raw.

Ask for help with the failing model output: help me debug my stg_customers dbt model

The customer-value mart combines every customer with any trusted paid orders. A left join keeps customers who have not placed a paid order.

  • Expand the models/marts folder in the Studio IDE file explorer.
  • Create models/marts/dim_customer_value.sql inside that folder.
  • Add the customer-value calculation by pasting this code:
select
  customers.customer_id,
  customers.first_name,
  customers.last_name,
  customers.customer_segment,
  customers.country_code,
  coalesce(count(paid_orders.order_id), 0) as paid_order_count,
  min(paid_orders.order_date) as first_paid_order_date,
  max(paid_orders.order_date) as latest_paid_order_date,
  coalesce(sum(paid_orders.gross_revenue), 0) as lifetime_value
from {{ ref('stg_customers') }} as customers
left join {{ ref('int_paid_orders') }} as paid_orders
  on customers.customer_id = paid_orders.customer_id
group by
  customers.customer_id,
  customers.first_name,
  customers.last_name,
  customers.customer_segment,
  customers.country_code

How Does the Customer Mart Work?

  • The two ref() calls connect the mart to the customer staging model and paid-order model.
  • The left join retains every customer from stg_customers.
  • The date functions identify each customer's first and latest paid orders.
  • The coalesce() expressions give customers without paid orders zero-valued metrics.
  • Save models/marts/dim_customer_value.sql.
  • Preview the customer mart by running:
dbt show --select "dim_customer_value"

What Does This Preview Prove?

This command builds the selected model as needed and previews its rows. The result exposes the customer-level grain before you add its formal contracts.

You should see Alice Chen with paid_order_count equal to 2 and lifetime_value equal to 200.00.

That is the customer view working. The mart now retains all five customers while calculating paid activity for each one.

Customer Values Look Wrong?

Confirm that the join begins with stg_customers and uses a left join to int_paid_orders. Check that the join condition compares the two customer_id columns.

Ask for help comparing the result with the expected customer grain: help me debug my customer-value mart

Add contracts and business documentation

A model contract states what one row represents. It also turns assumptions about keys and relationships into checks that run with the pipeline.

  • Open models/staging/staging.yml from the Studio IDE file explorer.
  • Add the following customer model definition immediately below the models: line:
  - name: stg_customers
    description: Clean customer records at one row per customer.
    columns:
      - name: customer_id
        description: Customer primary key.
        data_tests:
          - unique
          - not_null
      - name: customer_segment
        data_tests:
          - accepted_values:
              arguments:
                values: ['consumer', 'business']

What Does This Contract Protect?

  • The description records that each row represents one customer.
  • The unique and not_null tests protect the customer primary key.
  • The accepted-values test limits customer_segment to the two supported business categories.
  • Save models/staging/staging.yml.
  • Verify the customer contract by running:
dbt build

What Does This Contract Build Check?

The build now executes the customer key and segment tests with the existing staging contracts. A successful result confirms that the raw customer records satisfy the staging rules.

You should see the staging models and their data tests pass.

Customer Contract Test Failing?

Check the indentation beneath stg_customers. Confirm that arguments: contains the accepted values list.

Ask for help with the YAML structure: help me fix my staging contract

The mart contracts document the paid-order grain and the two business-facing outputs. Each definition places tests beside the metric or key it protects.

  • Create models/marts/marts.yml inside the models/marts folder.
  • Add the paid-order contract by pasting this first section:
version: 2

models:
  - name: int_paid_orders
    description: One row per order with at least one trusted successful payment event.
    columns:
      - name: order_id
        description: Paid order primary key.
        data_tests:
          - unique
          - not_null
      - name: gross_revenue
        data_tests:
          - not_null

What Does the Paid-Order Contract Protect?

  • The model description records the one-row-per-paid-order grain.
  • The order key tests prevent duplicate or missing paid-order identifiers.
  • The revenue test requires every trusted paid order to have a value.
  • Save models/marts/marts.yml.
  • Verify the paid-order contract by running:
dbt build

What Does This Mart Build Check?

The build evaluates the paid-order key and revenue contracts. Passing tests confirm that the reusable intermediate grain still holds.

You should see int_paid_orders and its tests pass.

Paid-Order Contract Not Passing?

Confirm that the model name is int_paid_orders. Check that the key column is order_id.

Ask for help with the contract output: help me debug the paid-order contract

  • Add the daily revenue contract below the int_paid_orders definition:
  - name: fct_daily_revenue
    description: Trusted gross revenue and paid-order count by order date.
    columns:
      - name: revenue_date
        description: Calendar date on which paid orders were placed.
        data_tests:
          - unique
          - not_null
      - name: gross_revenue
        description: Sum of deduplicated successful payment amounts.
        data_tests:
          - not_null

What Does the Revenue Contract Protect?

  • The description identifies the mart as trusted daily revenue.
  • The date tests enforce one non-null row per paid order date.
  • The revenue test prevents a daily row from carrying a missing total.
  • Save models/marts/marts.yml.
  • Verify the revenue contract by running:
dbt build

What Does This Revenue Build Check?

The build adds the daily date and revenue tests to the quality gate. Passing tests confirm that the corrected revenue mart still has a reliable daily grain.

You should see the daily revenue model and its tests pass.

Revenue Contract Not Passing?

Check that the model name matches fct_daily_revenue. Confirm that the tested date column is revenue_date.

Ask for help with the failing revenue contract: help me debug the revenue mart tests

  • Add the customer-value contract below the fct_daily_revenue definition:
  - name: dim_customer_value
    description: Customer-level paid-order history and lifetime value, including customers with no paid orders.
    columns:
      - name: customer_id
        description: Customer primary key.
        data_tests:
          - unique
          - not_null
      - name: paid_order_count
        data_tests:
          - not_null
      - name: lifetime_value
        data_tests:
          - not_null

What Does the Customer Contract Protect?

  • The model description records that customers without paid orders remain in the mart.
  • The customer key tests enforce one row per customer.
  • The metric tests require populated order counts and lifetime values.
  • Save models/marts/marts.yml.
  • Verify the customer-value contract by running:
dbt build

What Does This Customer Build Check?

The build now evaluates all three documented model contracts. A successful result confirms that the customer mart preserves its primary key and required metrics.

You should see every model contract in marts.yml pass.

Customer Mart Contract Not Passing?

Confirm that the definition remains beneath the top-level models: key. Check that paid_order_count and lifetime_value match the SQL model aliases.

Ask for help comparing the YAML and SQL identifiers: help me fix the customer mart contract

Generic tests protect structure. A singular business-rule test can enforce a condition that applies across complete revenue rows.

  • Expand the tests folder in the Studio IDE file explorer.
  • Create tests/assert_non_negative_revenue.sql inside that folder.
  • Add the non-negative revenue rule by pasting this code:
select
  revenue_date,
  gross_revenue
from {{ ref('fct_daily_revenue') }}
where gross_revenue < 0

How Does This Business-Rule Test Work?

  • The query returns only revenue rows that violate the rule.
  • A valid revenue mart returns zero rows from this test.
  • The ref() call adds the revenue mart to the test's dependency graph.
  • Save tests/assert_non_negative_revenue.sql.
  • Verify the business rule by running:
dbt build

What Does This Quality Gate Check?

The build executes the singular test with every generic data test. Passing output proves that no daily revenue row contains a negative amount.

You should see the non-negative revenue test pass with the rest of the project.

Business-Rule Test Failing?

Confirm that the test file sits directly inside tests. Check that the query references fct_daily_revenue and filters gross_revenue below zero.

Ask for help interpreting the returned rows: help me debug the non-negative revenue test

✔️ Awesome, I've got everything!

Great. Save every changed file before running the final quality gate.

ⓧ I'd like to double check the full code

Compare each file below with your project. These are the complete files for this step.

select
  customer_id,
  first_name,
  last_name,
  customer_segment,
  country_code,
  created_at,
  _loaded_at
from {{ source('retail_raw', 'customers') }}

Customer Staging Reference

This full-file reference selects the final customer columns from the declared raw source.

select
  customers.customer_id,
  customers.first_name,
  customers.last_name,
  customers.customer_segment,
  customers.country_code,
  coalesce(count(paid_orders.order_id), 0) as paid_order_count,
  min(paid_orders.order_date) as first_paid_order_date,
  max(paid_orders.order_date) as latest_paid_order_date,
  coalesce(sum(paid_orders.gross_revenue), 0) as lifetime_value
from {{ ref('stg_customers') }} as customers
left join {{ ref('int_paid_orders') }} as paid_orders
  on customers.customer_id = paid_orders.customer_id
group by
  customers.customer_id,
  customers.first_name,
  customers.last_name,
  customers.customer_segment,
  customers.country_code

Customer Mart Reference

This full-file reference preserves every customer and calculates their trusted paid-order metrics.

version: 2

models:
  - name: stg_customers
    description: Clean customer records at one row per customer.
    columns:
      - name: customer_id
        description: Customer primary key.
        data_tests:
          - unique
          - not_null
      - name: customer_segment
        data_tests:
          - accepted_values:
              arguments:
                values: ['consumer', 'business']

  - name: stg_orders
    description: Clean order records at one row per order.
    columns:
      - name: order_id
        description: Order primary key.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed', 'cancelled']

  - name: stg_payments
    description: Deduplicated successful payment events at one row per payment event.
    columns:
      - name: payment_event_id
        description: Payment gateway event primary key after deduplication.
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_orders')
                field: order_id
      - name: payment_method
        data_tests:
          - accepted_values:
              arguments:
                values: ['card', 'wallet', 'bank_transfer']

Staging Contracts Reference

This full-file reference documents all three staging models and their final data tests.

version: 2

models:
  - name: int_paid_orders
    description: One row per order with at least one trusted successful payment event.
    columns:
      - name: order_id
        description: Paid order primary key.
        data_tests:
          - unique
          - not_null
      - name: gross_revenue
        data_tests:
          - not_null

  - name: fct_daily_revenue
    description: Trusted gross revenue and paid-order count by order date.
    columns:
      - name: revenue_date
        description: Calendar date on which paid orders were placed.
        data_tests:
          - unique
          - not_null
      - name: gross_revenue
        description: Sum of deduplicated successful payment amounts.
        data_tests:
          - not_null

  - name: dim_customer_value
    description: Customer-level paid-order history and lifetime value, including customers with no paid orders.
    columns:
      - name: customer_id
        description: Customer primary key.
        data_tests:
          - unique
          - not_null
      - name: paid_order_count
        data_tests:
          - not_null
      - name: lifetime_value
        data_tests:
          - not_null

Mart Contracts Reference

This full-file reference documents the intermediate model and both business-facing marts.

select
  revenue_date,
  gross_revenue
from {{ ref('fct_daily_revenue') }}
where gross_revenue < 0

Revenue Test Reference

This full-file reference returns only daily revenue rows that violate the non-negative rule.

Build and inspect the trusted products

The complete graph now has source rules and model contracts. The final checks prove that fresh inputs produce tested business outputs.

  • Evaluate the declared source freshness rules by running:
dbt freshness --resource-type source

What Does the Freshness Check Measure?

This command compares each source's loading timestamp with the configured warning and error thresholds. Passing output confirms that the retail sources remain within those limits.

You should see the retail_raw sources pass their freshness checks.

Before you run the full build, do you expect every model and data test to pass?

  • Run the complete project quality gate with this command:
dbt build

What Does the Complete Build Prove?

The build follows every source() and ref() dependency in order. It also runs the generic and singular data tests.

You should see every model and data test pass. Your complete layered graph now satisfies its quality gate.

  • Preview the trusted daily revenue mart by running:
dbt show --select "fct_daily_revenue"

What Does the Revenue Preview Confirm?

This preview displays the table-materialized daily mart. It lets you confirm the final business grain from the warehouse result.

You should see four paid order dates. Their gross_revenue values total 545.00.

Before you preview the customer mart, what paid-order count and lifetime value should Alice Chen have?

  • Preview the completed customer-value mart by running:
dbt show --select "dim_customer_value"

What Does the Customer Preview Confirm?

This preview displays customer metrics from the table-materialized mart. It confirms that the left join retained customers without paid orders.

You should see all five customers. Alice Chen has 2 paid orders and a lifetime_value of 200.00.

  • Open models/marts/fct_daily_revenue.sql in Studio IDE.
  • Select the DAG view.
  • Confirm that the graph connects the raw sources to staging models.
  • Confirm that the graph continues through int_paid_orders to fct_daily_revenue.

You should see the source, staging, intermediate, and mart layers connected through source() and ref() dependencies.

Final Checks Not Passing?

Open the first failed build item because downstream failures can follow from it. Compare the reported model or test name with the exact file definitions above.

If the DAG omits a model, confirm that its SQL uses the expected source() or ref() call.

Ask for help interpreting the final output: help me debug my complete dbt quality gate

Your retail pipeline now ships tested revenue and customer-value marts from one documented graph. Next, you will commit this validated project and schedule its production build.

Schedule the Production Build

Your tested retail graph in dbt Studio already turns unreliable payment events into $545 of trusted revenue. The models are ready for a production schedule.

Manual runs depend on you remembering to start them. A deployment job moves the same validated graph onto a daily schedule with logs for every run.

In this step, get ready to:
  • Publish the tested project to the protected primary branch.
  • Configure a Production environment for the existing Snowflake connection.
  • Schedule a daily deploy job with an observable successful run.
Publish the validated branch

The deployment job reads code from the protected primary branch. A synced commit gives the job a fixed project revision identified by its commit SHA.

  • Return to Version Control in dbt Studio.
  • Enter Build trusted retail revenue pipeline as the commit message.
  • Select Commit and sync.

You should see confirmation that the complete project is committed to the managed repository.

  • Merge the synced development branch into the protected primary branch using the managed repository merge flow.

Good progress. The protected primary branch now contains the tested retail pipeline under the new commit.

Unable to merge the branch?

  • Confirm that Commit and sync completed for the development branch.
  • Confirm that the commit message is Build trusted retail revenue pipeline.
  • Check that your dbt account can merge changes into the protected primary branch.

help me troubleshoot a dbt managed repository branch merge.

Configure the production deployment

A deployment environment connects the project to a specific Snowflake target. The deploy job uses that environment to run source freshness before building the complete graph.

  • Select Orchestration in the dbt navigation.
  • Select Environments.
  • Choose Create Environment.
  • Select Deployment as the environment type.

You should see the deployment form with fields for the environment name and connection.

  • Enter Production as the environment name.
  • Select the existing Snowflake connection profile.

The form should now show the Production name with the existing connection selected.

  • Set the target schema to ANALYTICS.
  • Create the deployment environment.

You should see Production listed as a Deployment environment using the ANALYTICS target schema.

How does Production stay guarded?

The Production environment reuses your existing Snowflake connection profile. Its ANALYTICS target keeps production models in the intended schema.

Your RETAIL_DBT_WH still suspends after 60 seconds of inactivity. RETAIL_DBT_MONITOR still enforces the one-credit daily limit.

  • Choose Create job from the Production environment page.
  • Choose Deploy job.

The deploy job form should show dbt build as its default command.

  • Enter Retail revenue daily build as the job name.
  • Retain dbt build as the job command.

The job now points at the complete tested graph in the Production environment.

  • Enable Run source freshness.

You should see source freshness positioned before the dbt build step.

Your existing cost guardrails remain active while this job is scheduled.

  • Enable Run on schedule.
  • Choose Specific hours.

The schedule form should now show controls for a UTC hour and active days.

  • Set the UTC hour to 6.
  • Confirm that every day remains selected.
  • Save the job configuration.

You should see Retail revenue daily build scheduled for hour 6 UTC on every day.

Verify the first production run

Before you start the job, do you expect both the freshness check and the tested graph build to succeed?

  • Select Run now for Retail revenue daily build.
  • Wait for the run to reach its final status.

You should see a Success result for the manual run. That completes the production handoff for your tested retail graph.

What did this run prove?

The source freshness step checked the raw retail feeds before any models ran. The build step then created the complete dependency graph in the correct order.

Passing tests show that the deployed commit still protects model grain and relationships. The negative-revenue rule also remains active.

  • Open the successful run overview.
  • Confirm that the environment is Production.
  • Confirm that the overview shows a commit SHA.
  • Inspect the source freshness step logs.
  • Inspect the dbt build step logs.
  • Confirm that the overview shows model timing.
  • Confirm that every data test passed.

The overview now provides one place to trace the environment and project revision. It also records the result of every production step.

Production run did not succeed?

  • Confirm that the job uses the Production environment.
  • Confirm that the protected primary branch contains Build trusted retail revenue pipeline.
  • Open the failed step logs to identify whether freshness or the graph build stopped the run.

help me diagnose the failed dbt deploy job, or use dbt's official getting-help guide to reach the dbt Community Forum.

A DAG turns the run into an explainable lineage path. It shows how raw payment events flow through each trusted layer.

  • Return to dbt Studio.
  • Select fct_daily_revenue in the Studio IDE.
  • Open its DAG view.
  • Trace the lineage from the raw payment source to fct_daily_revenue.

You should see the source, staging, intermediate, and mart layers connected through source() and ref() dependencies.

Secret mission

Make Revenue Refund-Aware

Gross revenue can look correct while refunded money remains invisible. Extend the pipeline with a tested refund feed and reconcile $545.00 in gross revenue into $520.00 in net revenue.

Clean Up Your Resources

Clean Up Your Resources

Your work in the Snowflake Trial stays at $0 while the trial remains active. dbt stays free on the Developer plan within its monthly allowance.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

Leaving the daily deployment job enabled uses Snowflake trial balance whenever the warehouse runs.

The one-credit daily resource monitor limits warehouse use. The 60-second auto-suspend setting cuts idle time.

Your current setup does not enter paid on-demand billing by itself.

  • Keep the Snowflake account unupgraded.
  • Avoid adding a payment card.

Resources you used:

  • The RETAIL_ANALYTICS database containing the raw customer, order, payment, and refund tables. It also contains the dbt-built staging views, intermediate view, and mart tables.
  • The RETAIL_DBT_WH X-Small warehouse used to run the pipeline.
  • The RETAIL_DBT_MONITOR one-credit daily resource monitor.
  • The Retail revenue daily build deploy job scheduled for UTC hour 6.

Keep everything running

No action needed. Choose this if you want the daily job to keep rebuilding and testing your refund-aware revenue pipeline.

  • Leave the Retail revenue daily build job enabled.
  • Retain the one-credit daily resource monitor.
  • Retain the 60-second warehouse auto-suspend setting.
  • Keep the dbt managed repository synced with your refund-aware project files.

Pause - I'll come back to this later

Stop scheduled runs to preserve your trial balance. Keep the project resources so you can resume from the same refund-aware pipeline later.

  • In dbt, return to the Retail revenue daily build job.
  • Open the job's Settings.
  • Disable Run on schedule.

The daily schedule is now paused. Your job configuration and run history remain available.

  • In Snowflake, select the ACCOUNTADMIN role.
  • Create a SQL File for the suspension command.
  • Suspend the guarded warehouse by running this command:
ALTER WAREHOUSE RETAIL_DBT_WH SUSPEND;

What does this command do?

The command stops active compute for RETAIL_DBT_WH. The warehouse can automatically resume when you return and run the pipeline again.

  • Confirm Snowflake reports that the statement completed successfully.

Your schedule is paused and the warehouse is suspended. Your tested models remain ready for another run.

Delete - I don't want to use this again

Deletion permanently removes the scheduled job and Snowflake project resources. Your dbt managed project requires a separate review because its Partner Connect connection may still be in use.

  • In dbt, select Deploy.
  • Select Jobs.
  • Select the Retail revenue daily build job.
  • Select Settings.
  • Select Edit.
  • Select Delete job.

The daily deployment no longer appears in the jobs list. Snowflake cannot receive another scheduled run from this job.

  • Return to Snowflake.
  • Select the ACCOUNTADMIN role.
  • Create a new SQL File for the cleanup statements.
  • Remove the Snowflake project resources by running these statements:
DROP DATABASE IF EXISTS RETAIL_ANALYTICS;
DROP WAREHOUSE IF EXISTS RETAIL_DBT_WH;
DROP RESOURCE MONITOR IF EXISTS RETAIL_DBT_MONITOR;

What do these statements remove?

  • The database statement removes the raw tables, staging views, intermediate view, and mart tables inside RETAIL_ANALYTICS.
  • The warehouse statement removes RETAIL_DBT_WH and its compute configuration.
  • The resource monitor statement removes RETAIL_DBT_MONITOR.
  • Confirm Snowflake reports that all three statements completed successfully.

The metered Snowflake resources are now removed. The deleted job cannot trigger another production build.

What stays in dbt?

The dbt managed repository remains after these statements. The Production environment remains too.

Partner Connect objects also remain. They need a separate review before removal when the connection is still in use.

Nice Work!

Nice Work!

You did it! Your browser-only retail revenue pipeline now turns unreliable payment events into trusted business metrics with dbt in Snowflake.

You've learned how to:

  • Build a layered retail pipeline that transforms raw Snowflake tables into trusted daily-revenue marts. The same model graph produces customer-value metrics for every customer.
  • Detect a revenue overstatement of 760.00. Repair it to 545.00 through successful-payment filtering plus event deduplication.
  • Protect the production DAG with source freshness plus data tests. Automate the validated project through a daily deployment with observable run history.
  • Secret Mission: Extended the pipeline with a refund-aware mart. Reconciled 545.00 gross revenue against 25.00 refunded revenue to produce 520.00 net revenue.

Ready to quiz yourself?