Competitive Product Mapping Warehouse

Model competitive product data into a Snowflake warehouse with SCD Type 2 history.

Introduction

30 Second Summary

E-commerce companies track whether their products appear on competitor marketplaces. Every week, a new batch of mapping data arrives showing which products were found, which look similar, and which are missing entirely.

In this project, you will build a data warehouse that transforms raw competitive product mapping spreadsheets into analyst-ready tables, tracks how match statuses change over time, and calculates match rate metrics per client.

What You'll Build

You run dbt build and in under a minute, three weekly batches of mapping spreadsheets flow through a staging layer, land in a dimensional model, and get snapshot-tracked for historical changes. You then query your metrics mart to see match rates per client and prove exactly when a product's status changed from "Not Found" to "Same."

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

  • A batch loading pipeline where you drop in new mapping data and immediately see updated match rates per client and platform without writing any new SQL.
  • A full SCD Type 2 history of every product's competitive match status over time, with exact validity timestamps showing when each status was current.
  • A metrics mart showing match rate percentage per client, coverage by category, and flags for clients with suspiciously low match rates.
  • Secret Mission: Write custom data quality tests that enforce business rules, flagging batches with anomalous "Not Found" rates and catching "Same" matches that are missing a competitor URL.

Are there any prerequisites?

You should be comfortable with SQL (JOINs, CTEs, GROUP BY) and basic command line and Git usage. Familiarity with dbt concepts (models, seeds, tests, materializations) from a prior project is helpful but not required.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters. When competitive product mapping data changes over time, you need a warehouse that remembers every version of every row so you can answer "when did this change?" with confidence.

Set Up Snowflake and dbt

To build a dimensional model, you need two things: a warehouse to store and compute your data, and a transformation tool to define how raw data becomes clean, structured tables.

In this step, you will sign up for a Snowflake trial account (free, no credit card), create the database and warehouse resources you need, then install dbt locally and connect it to Snowflake.

In this step, get ready to:
  • Sign up for a Snowflake trial account and record your account identifier.
  • Create a database, warehouse, schema, and role in Snowflake.
  • Install dbt-snowflake, scaffold a project, and verify the connection.
Sign up for a Snowflake trial account

Snowflake's trial gives you a production-grade cloud data warehouse with $400 in free credits for 30 days. No credit card required.

  • Open your browser and navigate to signup.snowflake.com.
  • Fill in your name, email, and create a password.
  • On the edition selection screen, choose Standard edition.
  • For the cloud provider, select AWS.
  • For the region, select Europe (Frankfurt) which is eu-central-1.
  • Click GET STARTED.
  • Check your email for a verification link and click it to activate your account.
  • After signing in to Snowsight, note your account identifier from the URL bar. The URL looks like https://app.snowflake.com/orgname/accountname/. Your account identifier is the orgname-accountname portion (separated by a hyphen).

Where is my account identifier?

Click your avatar in the bottom-left corner of Snowsight and select Connect a tool to Snowflake. This shows your account identifier in the orgname-accountname format. Write it down because you will need it when configuring dbt.

Not receiving the verification email?

Check your spam or junk folder. The email comes from Snowflake with a subject line about activating your account. If it still does not arrive after a few minutes, try signing up again with a different email address.

help me troubleshoot my Snowflake trial signup

Create your Snowflake database, warehouse, and role

Snowflake charges credits only when a warehouse is actively running queries. You will create the resources and immediately suspend the warehouse to avoid burning credits while you finish setup.

  • In Snowsight, click Worksheets in the left sidebar.
  • Click the + Worksheet button to open a new SQL worksheet.
  • Paste the following SQL into the worksheet and run all statements by clicking Run All (or press Cmd+Shift+Enter on macOS, Ctrl+Shift+Enter on Windows):
-- Create a role for dbt transformations
CREATE ROLE IF NOT EXISTS TRANSFORM_ROLE;
GRANT ROLE TRANSFORM_ROLE TO ROLE SYSADMIN;

-- Create the warehouse (XS to minimize cost)
CREATE WAREHOUSE IF NOT EXISTS TRANSFORM_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- Create the database and schema
CREATE DATABASE IF NOT EXISTS PRODUCT_MAPPING_DB;
CREATE SCHEMA IF NOT EXISTS PRODUCT_MAPPING_DB.DBT_DEV;

-- Grant permissions to the role
GRANT USAGE ON WAREHOUSE TRANSFORM_WH TO ROLE TRANSFORM_ROLE;
GRANT ALL ON DATABASE PRODUCT_MAPPING_DB TO ROLE TRANSFORM_ROLE;
GRANT ALL ON SCHEMA PRODUCT_MAPPING_DB.DBT_DEV TO ROLE TRANSFORM_ROLE;

-- Suspend warehouse to stop credit usage
ALTER WAREHOUSE TRANSFORM_WH SUSPEND;

What do these commands do?

  • TRANSFORM_ROLE is a dedicated role dbt will use to run queries, keeping permissions separate from your admin account.
  • TRANSFORM_WH is an extra-small warehouse that auto-suspends after 60 seconds of inactivity and auto-resumes when dbt sends a query.
  • PRODUCT_MAPPING_DB is the database where all your models will land. DBT_DEV is the schema within it.
  • The final SUSPEND halts the warehouse immediately so no credits are consumed while you continue setup.
  • After running, you should see a success message for each statement in the results panel at the bottom.
  • Now grant the role to your user. Run this statement in the same worksheet, replacing the username with your own Snowflake login name:
GRANT ROLE TRANSFORM_ROLE TO USER [[SNOWFLAKE_USER="your Snowflake username (e.g. JSMITH)"]];

Always suspend when stepping away

Your $400 in trial credits are consumed only while warehouses are running. The AUTO_SUSPEND = 60 setting helps, but explicitly suspending with ALTER WAREHOUSE TRANSFORM_WH SUSPEND; when you take a break is the safest habit.

Seeing an error about insufficient privileges?

Make sure you are running the worksheet as the ACCOUNTADMIN role. Check the role selector in the top-right corner of the worksheet. On a fresh trial account, your user defaults to ACCOUNTADMIN, but if it was changed, switch it back.

help me fix a Snowflake permissions error when creating resources

Install dbt and scaffold the project

dbt needs a Python virtual environment to keep its dependencies isolated. You will create one, install dbt-snowflake (which includes dbt-core as a dependency), then scaffold a new project.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a Python virtual environment by running this command:
python3 -m venv dbt-env
  • Activate the virtual environment:

macOS

source dbt-env/bin/activate

Windows

dbt-env\Scripts\activate

You should see (dbt-env) appear at the start of your terminal prompt. This confirms the virtual environment is active.

  • Install dbt-snowflake by running:
pip install dbt-snowflake==1.12.0

What is dbt-snowflake?

The dbt-snowflake package installs both the Snowflake adapter and dbt-core (the transformation engine) as a single dependency. Pinning to 1.12.0 ensures you get the latest stable release.

The installation takes about a minute. You will see pip downloading and installing packages.

  • Confirm dbt installed correctly by running:
dbt --version

You should see output showing dbt-core and dbt-snowflake both at version 1.12.0.

Seeing command not found for dbt?

Make sure your virtual environment is still active. Look for (dbt-env) at the start of your prompt. If it disappeared, re-activate it with source dbt-env/bin/activate (macOS) or dbt-env\Scripts\activate (Windows).

help me troubleshoot dbt installation issues

  • Scaffold a new dbt project by running:
dbt init product_mapping --skip-profile-setup

Why skip profile setup?

The --skip-profile-setup flag tells dbt to create the project folder without launching the interactive connection wizard. You will configure the connection profile manually in the next chunk, which gives you full control over every setting.

  • Move into the new project folder by running:
cd product_mapping
  • Confirm the project folder was created by running:
ls

You should see folders like models, seeds, tests, macros, and a dbt_project.yml file.

Now you need to configure the connection profile so dbt knows how to reach Snowflake. The profile lives at ~/.dbt/profiles.yml.

  • Create the .dbt directory and profiles file by running:
mkdir -p ~/.dbt && touch ~/.dbt/profiles.yml
  • Open ~/.dbt/profiles.yml in VS Code by running:
code ~/.dbt/profiles.yml
  • Paste the following configuration into the file, replacing the placeholder values with your own Snowflake credentials:
product_mapping:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: [[SNOWFLAKE_ACCOUNT="orgname-accountname"]]
      user: [[SNOWFLAKE_USER="your Snowflake username"]]
      password: your-snowflake-password
      role: TRANSFORM_ROLE
      database: PRODUCT_MAPPING_DB
      warehouse: TRANSFORM_WH
      schema: DBT_DEV
      threads: 1

What do these profile fields mean?

  • account is your Snowflake account identifier in orgname-accountname format. You noted this from the URL earlier.
  • user is the login name you created during Snowflake signup.
  • password is the password you set during signup. Replace the placeholder with your actual password.
  • role, database, warehouse, and schema match the resources you just created in Snowsight.
  • Save profiles.yml (Cmd+S on macOS, Ctrl+S on Windows).

Next, update the project configuration file to match the structure this project needs.

  • Open dbt_project.yml in VS Code by running:
code dbt_project.yml
  • Replace the entire contents of dbt_project.yml with the following:
name: 'product_mapping'
version: '1.0.0'

profile: 'product_mapping'

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

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

seeds:
  product_mapping:
    +schema: raw

models:
  product_mapping:
    staging:
      +materialized: view
    marts:
      +materialized: table

What does this configuration do?

  • seeds.product_mapping.+schema: raw tells dbt to load seed CSV files into a schema called raw instead of the default schema.
  • models.product_mapping.staging.+materialized: view means staging models will be created as views (lightweight, no storage cost).
  • models.product_mapping.marts.+materialized: table means mart models will be persisted as tables for fast query performance.
  • Save dbt_project.yml.

Before you run the verification, predict the result. Do you think dbt will successfully connect to Snowflake on the first try?

  • Verify the connection by running:
dbt debug

You should see output ending with All checks passed! confirming dbt can reach Snowflake, authenticate, and access the database, warehouse, and schema.

✔️ I see All checks passed!

Your dbt installation is connected to Snowflake and ready to go.

ⓧ I see a connection error

Check these common causes:

  • Account identifier format: It must be orgname-accountname with a hyphen, not a dot or slash.
  • Username: This is your Snowflake login name (often all uppercase), not your email address.
  • Password: Check for typos. The password is case-sensitive.
  • Profile name: Both dbt_project.yml (profile: 'product_mapping') and profiles.yml (top-level key product_mapping:) must match exactly.

Fix the value in ~/.dbt/profiles.yml, save, and run dbt debug again.

help me fix my dbt debug connection error

✔️ Awesome, I've got everything!

Great. Make sure you have saved both dbt_project.yml and ~/.dbt/profiles.yml.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml that should be in your product_mapping folder:

name: 'product_mapping'
version: '1.0.0'

profile: 'product_mapping'

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

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

seeds:
  product_mapping:
    +schema: raw

models:
  product_mapping:
    staging:
      +materialized: view
    marts:
      +materialized: table

And here is what your ~/.dbt/profiles.yml should look like (with your own credentials substituted):

product_mapping:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: orgname-accountname
      user: YOUR_USERNAME
      password: your-snowflake-password
      role: TRANSFORM_ROLE
      database: PRODUCT_MAPPING_DB
      warehouse: TRANSFORM_WH
      schema: DBT_DEV
      threads: 1

Your warehouse is provisioned and dbt is connected. Next up, you will load competitive product mapping data as seed CSVs and build a staging layer that cleans it into a predictable format.

Load Mapping Batches and Build Staging

In the previous step you connected dbt to Snowflake. Now it is time to give your warehouse some data to work with.

Raw product mapping spreadsheets arrive messy. Match statuses come in mixed case, there are no timestamps, and nothing tells you which weekly batch a record belongs to. A staging layer cleans all of this into a predictable contract so every downstream model can trust the data without repeating the same cleanup logic.

In this step, get ready to:
  • Create three seed CSV files representing weekly mapping batches.
  • Build a staging model that unions, cleans, and tags every record with its batch.
  • Run the pipeline and verify clean output in Snowflake.
Create the seed CSV files

dbt seeds are CSV files that live in your project and get loaded directly into the warehouse as tables. They are perfect for small, version-controlled datasets like the batch mapping spreadsheets you will model.

  • In VS Code, create a new file inside the seeds/ folder of your product_mapping project called batch_001_mappings.csv and paste the following content:
source_product_id,source_product_url,competitor_product_url,competitor_sku,match_status,client_id,product_category,source_platform,competitor_platform
SKU-1001,https://marketplace-a.eu/p/SKU-1001,https://marketplace-b.eu/p/BX-2001,BX-2001,Same,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1002,https://marketplace-a.eu/p/SKU-1002,https://marketplace-b.eu/p/BX-2002,BX-2002,Similar,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1003,https://marketplace-a.eu/p/SKU-1003,,,Not Found,client_alpha,Home & Garden,marketplace_a,marketplace_b
SKU-2001,https://marketplace-c.eu/p/SKU-2001,https://marketplace-d.eu/p/DX-3001,DX-3001,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2002,https://marketplace-c.eu/p/SKU-2002,https://marketplace-d.eu/p/DX-3002,DX-3002,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2003,https://marketplace-c.eu/p/SKU-2003,,,Not Found,client_beta,Footwear,marketplace_c,marketplace_d
SKU-2004,https://marketplace-c.eu/p/SKU-2004,,,Not Found,client_beta,Footwear,marketplace_c,marketplace_d
  • Create a second file called batch_002_mappings.csv in the same seeds/ folder and paste:
source_product_id,source_product_url,competitor_product_url,competitor_sku,match_status,client_id,product_category,source_platform,competitor_platform
SKU-1001,https://marketplace-a.eu/p/SKU-1001,https://marketplace-b.eu/p/BX-2001,BX-2001,Same,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1002,https://marketplace-a.eu/p/SKU-1002,https://marketplace-b.eu/p/BX-2002,BX-2002,Similar,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1003,https://marketplace-a.eu/p/SKU-1003,,,Not Found,client_alpha,Home & Garden,marketplace_a,marketplace_b
SKU-1004,https://marketplace-a.eu/p/SKU-1004,https://marketplace-b.eu/p/BX-2004,BX-2004,Same,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-2001,https://marketplace-c.eu/p/SKU-2001,https://marketplace-d.eu/p/DX-3001,DX-3001,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2002,https://marketplace-c.eu/p/SKU-2002,https://marketplace-d.eu/p/DX-3002,DX-3002,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2003,https://marketplace-c.eu/p/SKU-2003,,,Not Found,client_beta,Footwear,marketplace_c,marketplace_d
SKU-2004,https://marketplace-c.eu/p/SKU-2004,,,Not Found,client_beta,Footwear,marketplace_c,marketplace_d
  • Create a third file called batch_003_mappings.csv in seeds/ and paste:
source_product_id,source_product_url,competitor_product_url,competitor_sku,match_status,client_id,product_category,source_platform,competitor_platform
SKU-1001,https://marketplace-a.eu/p/SKU-1001,https://marketplace-b.eu/p/BX-2001,BX-2001,Same,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1002,https://marketplace-a.eu/p/SKU-1002,https://marketplace-b.eu/p/BX-2002,BX-2002,Similar,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-1003,https://marketplace-a.eu/p/SKU-1003,https://marketplace-b.eu/p/BX-2099,BX-2099,Same,client_alpha,Home & Garden,marketplace_a,marketplace_b
SKU-1004,https://marketplace-a.eu/p/SKU-1004,https://marketplace-b.eu/p/BX-2004,BX-2004,Same,client_alpha,Electronics,marketplace_a,marketplace_b
SKU-2001,https://marketplace-c.eu/p/SKU-2001,https://marketplace-d.eu/p/DX-3001,DX-3001,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2002,https://marketplace-c.eu/p/SKU-2002,https://marketplace-d.eu/p/DX-3002,DX-3002,Same,client_beta,Clothing,marketplace_c,marketplace_d
SKU-2003,https://marketplace-c.eu/p/SKU-2003,https://marketplace-d.eu/p/DX-3099,DX-3099,Same,client_beta,Footwear,marketplace_c,marketplace_d
SKU-2004,https://marketplace-c.eu/p/SKU-2004,,,Not Found,client_beta,Footwear,marketplace_c,marketplace_d
SKU-2005,https://marketplace-c.eu/p/SKU-2005,,,,client_beta,Accessories,marketplace_c,marketplace_d
  • Confirm the files exist by running:
ls seeds/

You should see batch_001_mappings.csv, batch_002_mappings.csv, and batch_003_mappings.csv listed.

What is in batch 3?

Batch 3 intentionally contains status changes. SKU-1003 was "Not Found" in batches 1 and 2, but in batch 3 a competitor listing appeared so its status is now "Same". The same happened for SKU-2003. These changes will become important when you build history tracking with snapshots in a later step.

Batch 3 also has a row (SKU-2005) with an empty match_status. You will use this intentionally bad data to test data quality rules later.

  • Load the seed files into Snowflake by running:
dbt seed

You should see output ending with a summary showing all three seeds loaded successfully.

What should I see?

dbt prints a START line and an OK line for each seed file, then a final summary. Look for lines like OK loaded seed file for each of the three batch files, followed by Completed successfully and PASS=3 in the totals.

Seeing a Database Error?

If dbt reports a connection error, your warehouse may be suspended. Return to Snowsight and run ALTER WAREHOUSE TRANSFORM_WH RESUME; then retry dbt seed.

If you see a CSV parsing error, check that each file has the exact same header row and that there are no trailing spaces or extra commas.

help me debug my dbt seed error

Build the staging model

Your seed tables now hold the raw batch data. The staging model will union all three batches together, standardise field casing, trim whitespace, and stamp each row with its batch identifier.

  • Create the folder models/staging/ inside your product_mapping project.
  • Create a file called stg_product_mappings.sql inside models/staging/ and paste the batch references and union logic:
with batch_001 as (
    select *, 'batch_001' as batch_id from {{ ref('batch_001_mappings') }}
),

batch_002 as (
    select *, 'batch_002' as batch_id from {{ ref('batch_002_mappings') }}
),

batch_003 as (
    select *, 'batch_003' as batch_id from {{ ref('batch_003_mappings') }}
),

unioned as (
    select * from batch_001
    union all
    select * from batch_002
    union all
    select * from batch_003
),

What does this code do?

Each CTE selects all columns from one seed table and appends a literal batch_id string so you can always tell which weekly delivery a row came from. The unioned CTE stacks all three together into one stream.

The {{ ref() }} function tells dbt about the dependency. dbt will always run seeds before this model because of these references.

  • Now add the cleaning logic directly below the code you just pasted (still in the same file):
cleaned as (
    select
        source_product_id,
        source_product_url,
        competitor_product_url,
        competitor_sku,
        lower(trim(match_status)) as match_status,
        lower(trim(client_id)) as client_id,
        lower(trim(product_category)) as product_category,
        lower(trim(source_platform)) as source_platform,
        lower(trim(competitor_platform)) as competitor_platform,
        batch_id
    from unioned
    where source_product_id is not null
)

select * from cleaned

What does the cleaning logic do?

The cleaned CTE lowercases and trims all text fields that downstream models will filter or group on. This prevents issues where "Same" and "same" are treated as different statuses.

The where source_product_id is not null filter drops any rows without a product identifier, which would be useless downstream.

  • Save stg_product_mappings.sql.
  • Run the staging model by executing:
dbt run

You should see a line showing OK created view model for stg_product_mappings, followed by Completed successfully.

Why a view?

Your dbt_project.yml already configures staging models as views. Views are rebuilt instantly on each run and do not store duplicate data. They are ideal for staging layers that simply reshape existing tables.

Model failed to compile?

Make sure your file is saved at exactly models/staging/stg_product_mappings.sql. dbt only picks up files in the paths configured in dbt_project.yml.

Check that the unioned CTE ends with a comma before cleaned as ( begins. A missing comma between CTEs is the most common SQL syntax error.

help me fix my dbt staging model compilation error

✔️ Awesome, I've got everything!

Great. Double check you have saved stg_product_mappings.sql and that dbt run reported success.

ⓧ I'd like to double check the full code

Your models/staging/stg_product_mappings.sql should contain both chunks above joined together. Scroll up and confirm you have: the three batch CTEs, the unioned CTE, the cleaned CTE, and the final select * from cleaned at the bottom. The file starts with with batch_001 as ( and ends with select * from cleaned.

Add schema tests and verify clean output

Schema tests document your staging model's contract and catch problems before they reach downstream tables. You will define tests on the key columns now.

  • Create a file called _staging.yml inside models/staging/ and paste:
version: 2

models:
  - name: stg_product_mappings
    description: Cleaned and unioned product mapping data from all batch deliveries. One row per mapping per batch.
    columns:
      - name: source_product_id
        description: The product identifier from the source marketplace.
        tests:
          - not_null
      - name: match_status
        description: Classification of the match result.
        tests:
          - not_null
          - accepted_values:
              values: ['same', 'similar', 'not_found']
      - name: client_id
        description: Identifier for the client who commissioned the mapping.
        tests:
          - not_null
      - name: batch_id
        description: Identifier for the delivery batch.
        tests:
          - not_null

What do these tests enforce?

The not_null tests ensure critical columns are never empty. The accepted_values test on match_status will flag any row whose status is not one of same, similar, or not_found. You will see this test actually fail in a later step because of the intentionally bad data in batch 3.

  • Save _staging.yml.

Before you query Snowflake, what do you think you will see in the staging view's match_status column? Will the values be mixed case like the raw CSVs, or all lowercase?

  • Switch to the Snowsight SQL worksheet and run this query to verify the staging output:
SELECT
    match_status,
    batch_id,
    COUNT(*) AS row_count
FROM PRODUCT_MAPPING_DB.DBT_DEV.STG_PRODUCT_MAPPINGS
GROUP BY match_status, batch_id
ORDER BY batch_id, match_status;

Every match_status value appears in lowercase (same, similar, not_found), and each row carries a batch_id column showing which delivery it came from. You will also see a row with a NULL match_status from SKU-2005 in batch 3. That is the intentional bad data you will catch with tests later.

Query returns no rows?

Make sure you ran both dbt seed and dbt run before querying. The view depends on the seed tables existing in Snowflake.

If you see a "does not exist" error for STG_PRODUCT_MAPPINGS, verify the schema in Snowsight matches your profiles.yml target schema. Your view lives in PRODUCT_MAPPING_DB.DBT_DEV.

my Snowflake query cannot find the staging view

Your staging layer is live. Three batches of mapping data flow through a single clean view with standardised fields and batch tracking. Next up, you will shape this data into a dimensional model so analysts can answer questions like "what is our match rate per client?" with a simple query.

Build the Dimensional Model

Your staging layer delivers clean, consistent data. But an analyst asking "what's our match rate per client?" still needs to write complex SQL joining across raw columns every single time.

A star schema pre-computes relationships into descriptive dimension tables and a central fact table. In this step, you'll build three dimensions and one fact table so that question becomes a single JOIN.

In this step, get ready to:
  • Create dimension models for products, platforms, and clients.
  • Build a fact table connecting all dimensions with match results.
  • Query match rates per client and discover a limitation with overwritten history.
Create the dimension models

Dimensions describe the "who, what, where" of your data. You'll create three: one for products, one for platform pairs, and one for clients.

  • Create a models/marts/ directory by running this command:
mkdir models/marts
  • Create a new file called dim_products.sql inside models/marts/ and paste this code:
with products as (
    select
        source_product_id,
        source_product_url,
        product_category,
        source_platform
    from {{ ref('stg_product_mappings') }}
),

deduped as (
    select distinct
        source_product_id,
        source_product_url,
        product_category,
        source_platform
    from products
)

select * from deduped

What does this code do?

This model pulls every product from staging and deduplicates them. A product like SKU-1001 appears in multiple batches, but the dimension table keeps only one row per unique product with its category and source platform.

  • Create another file called dim_platforms.sql inside models/marts/ and paste this code:
with platforms as (
    select distinct
        source_platform,
        competitor_platform
    from {{ ref('stg_product_mappings') }}
)

select
    source_platform || '_vs_' || competitor_platform as platform_pair_id,
    source_platform,
    competitor_platform
from platforms

What does this code do?

This dimension captures the unique marketplace matchups. It concatenates source_platform and competitor_platform into a human-readable platform_pair_id like marketplace_a_vs_marketplace_b.

  • Save both files.
  • Run these two dimension models by running this command:
dbt run --select "dim_products dim_platforms"

You should see output showing both models completed successfully, with a status of SUCCESS for each.

Seeing a compilation error?

  • Confirm both files are saved inside models/marts/ (not models/staging/).
  • Check that the ref('stg_product_mappings') call uses the exact model name from your staging file (no typos, no .sql extension).
  • Make sure your virtual environment is still active (you should see the venv name in your terminal prompt).

Help me debug my dbt run

  • Create the third dimension file called dim_clients.sql inside models/marts/ and paste this code:
with client_batches as (
    select
        client_id,
        min(batch_id) as first_batch,
        max(batch_id) as latest_batch,
        count(distinct batch_id) as total_batches
    from {{ ref('stg_product_mappings') }}
    group by client_id
)

select * from client_batches

What does this code do?

This dimension summarises each client's participation across batches. It calculates when they first appeared, their most recent batch, and how many batches they've been in. Analysts can use this to spot clients who dropped off or just onboarded.

  • Save dim_clients.sql and run it by running this command:
dbt run --select "dim_clients"

You should see dim_clients complete with SUCCESS status. All three dimensions are now materialized as tables in Snowflake.

dim_clients failing?

  • The GROUP BY clause must include client_id only. Check you haven't accidentally included other columns.
  • Confirm your staging model is still working by running dbt run --select stg_product_mappings first.

Help me debug my dim_clients model

Create the fact table and schema tests

The fact table is the centre of your star schema. It holds one row per mapping record with foreign keys pointing to each dimension.

  • Create a file called fct_product_mappings.sql inside models/marts/ and paste this code:
with mappings as (
    select
        source_product_id,
        source_product_url,
        competitor_product_url,
        competitor_sku,
        match_status,
        client_id,
        product_category,
        source_platform,
        competitor_platform,
        batch_id
    from {{ ref('stg_product_mappings') }}
)

select
    source_product_id,
    competitor_platform,
    client_id,
    product_category,
    source_platform,
    source_product_url,
    competitor_product_url,
    competitor_sku,
    match_status,
    batch_id
from mappings

What does this code do?

The fact table selects every mapping record from staging, preserving batch history. Each row carries the source_product_id, client_id, and competitor_platform as join keys to the dimension tables.

Because it's configured as a table materialization in dbt_project.yml, dbt will create a physical table in Snowflake rather than a view.

Now you'll add schema tests to validate your dimensional model. These ensure keys are unique, not null, and that relationships between tables hold.

  • Create a file called _marts.yml inside models/marts/ and paste this first section:
version: 2

models:
  - name: fct_product_mappings
    description: Fact table of product mapping results. One row per mapping record per batch.
    columns:
      - name: source_product_id
        description: Foreign key to dim_products.
        tests:
          - not_null
      - name: client_id
        description: Foreign key to dim_clients.
        tests:
          - not_null
          - relationships:
              to: ref('dim_clients')
              field: client_id

  - name: dim_products
    description: Product dimension with unique source products and their categories.
    columns:
      - name: source_product_id
        description: Primary key.
        tests:
          - unique
          - not_null

What does this YAML do?

The relationships test on client_id checks that every value in the fact table's client_id column exists in dim_clients. This is referential integrity enforcement. If a mapping references a client that somehow isn't in the dimension, dbt flags it as a test failure.

  • Add the remaining dimension entries below the dim_products block in the same file:
  - name: dim_platforms
    description: Platform pair dimension showing source vs competitor marketplace combinations.
    columns:
      - name: platform_pair_id
        description: Primary key combining source and competitor platform.
        tests:
          - unique
          - not_null

  - name: dim_clients
    description: Client dimension with batch participation metadata.
    columns:
      - name: client_id
        description: Primary key.
        tests:
          - unique
          - not_null

  - name: metrics_match_rates
    description: Match rate metrics per client based on current snapshot state.
    columns:
      - name: client_id
        description: Primary key.
        tests:
          - unique
          - not_null

What about metrics_match_rates?

You're declaring tests for a model you haven't built yet. dbt safely ignores YAML entries for models that don't exist as .sql files. Adding it now means the tests activate automatically when you create that model in the next step.

  • Save fct_product_mappings.sql and _marts.yml.
  • Run the full project build (models plus tests) by running this command:
dbt build

You should see all models complete with SUCCESS and all tests pass with PASS.

Tests failing on relationships?

  • The relationships test checks that every client_id in the fact table exists in dim_clients. If dim_clients failed to build, this test will also fail. Run dbt run first to confirm all models build before running tests.
  • Check indentation in _marts.yml. YAML is whitespace-sensitive. The to: and field: keys must be indented under relationships:.

Help me fix my failing tests

✔️ Awesome, I've got everything!

Great. Confirm you've saved all five files in models/marts/: dim_products.sql, dim_platforms.sql, dim_clients.sql, fct_product_mappings.sql, and _marts.yml.

ⓧ I'd like to double check the full code

Scroll up and confirm each of the following chunks is present in your files, in order:

  • dim_products.sql: the products CTE selecting four columns, then the deduped CTE with SELECT DISTINCT.
  • dim_platforms.sql: one CTE with SELECT DISTINCT on the two platform columns, then the final select concatenating them into platform_pair_id.
  • dim_clients.sql: one CTE called client_batches with min, max, and count(distinct) aggregations grouped by client_id.
  • fct_product_mappings.sql: one CTE called mappings selecting all 10 columns from staging, then a final select reordering them.
  • _marts.yml: five model entries (fct_product_mappings, dim_products, dim_platforms, dim_clients, metrics_match_rates) with unique and not_null tests on primary keys, plus a relationships test on fct_product_mappings.client_id.
Query match rates and discover the limitation

Your dimensional model is live in Snowflake. Time to prove it answers the analyst's question: "what's our match rate per client?"

  • Switch back to Snowsight in your browser.
  • Open a SQL worksheet and run this query to calculate match rate per client:
-- Match rate per client using the latest batch only
select
    f.client_id,
    c.total_batches,
    count(*) as total_mappings,
    sum(case when f.match_status in ('same', 'similar') then 1 else 0 end) as matched,
    round(
        sum(case when f.match_status in ('same', 'similar') then 1 else 0 end) * 100.0
        / count(*), 2
    ) as match_rate_pct
from product_mapping_db.dbt_dev.fct_product_mappings f
join product_mapping_db.dbt_dev.dim_clients c
    on f.client_id = c.client_id
where f.batch_id = c.latest_batch
group by f.client_id, c.total_batches
order by match_rate_pct desc;

You should see two rows: one for client_alpha and one for client_beta, each with their current match rate percentage. One simple JOIN answered the question.

Why filter to the latest batch?

The fact table holds rows from ALL batches. Without the where f.batch_id = c.latest_batch filter, the match rate would blend historical and current data. Filtering to the latest batch gives the current-state picture.

Now for the real test. You know that SKU-1003 changed status from "not_found" in batch 1 to "same" in batch 3. Before you run the next query, predict: can you find WHEN that change happened, with exact validity timestamps?

  • Run this query to look at the full history for SKU-1003:
-- Attempt to find when SKU-1003 changed status
select
    source_product_id,
    match_status,
    batch_id
from product_mapping_db.dbt_dev.fct_product_mappings
where source_product_id = 'SKU-1003'
order by batch_id;

You see three rows showing the status per batch. You can tell it changed between batch_002 and batch_003. But there are no timestamps showing when each status was actually valid or when it expired.

What's the limitation here?

The fact table stores every batch row, so you CAN see different statuses across batch IDs. But you cannot answer "this status was current from January 5 until January 12" because there are no validity date columns.

In a production pipeline, batches arrive at irregular intervals. Without valid_from and valid_to timestamps, you cannot reconstruct the timeline of changes. This is the exact problem that SCD Type 2 history tracking solves.

Your dimensional model answers the "current state" question beautifully. But it falls short on the "when did this change?" question. Next up, you'll add dbt snapshots to capture SCD Type 2 history with proper validity timestamps.

Capture History with dbt Snapshots

Your dimensional model answers "what's the match rate right now?" perfectly. But it has a blind spot: when a product's status changes from "Not Found" to "Same," the old status disappears.

You discovered this at the end of the last step. The fact table only holds the latest state, so you cannot answer "when did this product's match status change?" In this step, you'll implement SCD Type 2 history tracking using dbt snapshots to preserve every version of a row with exact validity timestamps.

In this step, get ready to:
  • Create a snapshot configuration that detects changes using the check strategy.
  • Simulate temporal data flow and verify SCD Type 2 history with validity timestamps.
  • Build a metrics mart powered by snapshot history showing match rates per client.
Define the snapshot configuration

dbt snapshots work by comparing the current state of your data against what was stored previously. When a value changes, dbt invalidates the old row and inserts a new one with fresh timestamps.

  • Create a new file called _snapshots.yml inside the snapshots/ directory of your project by pasting this configuration:
snapshots:
  - name: snap_product_mappings
    relation: ref('stg_product_mappings')
    description: SCD Type 2 snapshot tracking changes in product match status over time.
    config:
      schema: snapshots
      unique_key: "source_product_id || '-' || competitor_platform"
      strategy: check
      check_cols:
        - match_status
        - competitor_product_url
        - competitor_sku

What does this configuration do?

  • relation: ref('stg_product_mappings') tells dbt to snapshot the staging view you built earlier.
  • unique_key uses a concatenated expression (source_product_id || '-' || competitor_platform) to uniquely identify each product-platform combination.
  • strategy: check compares the listed columns between runs. If any value differs, dbt creates a new row and invalidates the old one.
  • check_cols lists the three columns to monitor for changes.

Why check strategy instead of timestamp?

The timestamp strategy requires a reliable updated_at column in your source data. Batch-delivered spreadsheets have no such column. Each batch is a full snapshot of current state delivered weekly.

The check strategy solves this by directly comparing column values between runs. If match_status changed from 'not_found' to 'same', dbt detects the difference regardless of timestamps.

  • Save snapshots/_snapshots.yml.
Simulate temporal flow and verify SCD Type 2 history

To see snapshots in action, you need to simulate what happens over time: first capture the state before a product changed, then capture the state after. You will temporarily remove batch 3 from your staging model, run the snapshot, then add it back and snapshot again.

  • Open models/staging/stg_product_mappings.sql in VS Code.
  • Find these lines:
batch_003 as (
    select *, 'batch_003' as batch_id from {{ ref('batch_003_mappings') }}
),
  • Comment out that entire CTE by adding -- at the start of each line.
  • In the same file, find this line inside the unioned CTE:
    union all
    select * from batch_003
  • Comment out those two lines as well.
  • Save stg_product_mappings.sql.

Your staging model now only includes batches 1 and 2. This represents the state before the status change happened.

  • Run the first snapshot to capture this initial state:
dbt snapshot

What should I see?

dbt creates a new table called snap_product_mappings in the snapshots schema. The output shows 1 snapshot completed successfully. Every row from batches 1 and 2 is inserted with dbt_valid_from set to now and dbt_valid_to set to NULL (meaning "currently valid").

  • Now uncomment the batch 3 lines you commented out. Restore stg_product_mappings.sql to its original state with all three batches included.
  • Save stg_product_mappings.sql.

Batch 3 contains status changes: SKU-1003 moved from "not_found" to "same" on marketplace_b, and SKU-2003 moved from "not_found" to "same" on marketplace_d.

  • Before you run the snapshot again, what do you think will happen to the rows for SKU-1003 and SKU-2003? Will dbt add new rows, update existing rows, or do nothing?
  • Load the restored seed data and run the snapshot again:
dbt seed && dbt snapshot

dbt detects that match_status changed for those two products. It invalidates the old rows (sets dbt_valid_to to the current timestamp) and inserts new rows with the updated status.

  • Switch to your Snowsight SQL worksheet and run this query to verify the SCD Type 2 history:
select
    source_product_id,
    competitor_platform,
    match_status,
    dbt_valid_from,
    dbt_valid_to
from PRODUCT_MAPPING_DB.SNAPSHOTS.SNAP_PRODUCT_MAPPINGS
where source_product_id in ('SKU-1003', 'SKU-2003')
order by source_product_id, dbt_valid_from;

You should see two rows for each product: one with match_status = 'not_found' and a filled dbt_valid_to (the old record), and one with match_status = 'same' and dbt_valid_to = NULL (the currently valid record).

Only seeing one row per product?

Make sure you commented out the batch 3 lines before the first dbt snapshot and uncommented them before the second. If both runs saw the same data, no change was detected.

You can drop the snapshot table and retry by running DROP TABLE IF EXISTS PRODUCT_MAPPING_DB.SNAPSHOTS.SNAP_PRODUCT_MAPPINGS; in Snowsight, then repeat the two-step snapshot process.

help me troubleshoot why my dbt snapshot is not showing SCD Type 2 history rows

Build the metrics mart from snapshot history

Now that you have historical data with validity timestamps, you can build a metrics model that calculates match rates from the currently valid snapshot rows. This gives you live metrics that automatically update when new snapshots capture changes.

  • Create a new file models/marts/metrics_match_rates.sql and paste the first CTE that filters to currently-valid rows:
with current_snapshot as (
    select
        source_product_id,
        competitor_platform,
        client_id,
        product_category,
        match_status,
        dbt_valid_from,
        dbt_valid_to
    from {{ ref('snap_product_mappings') }}
    where dbt_valid_to is null
),

What does this CTE do?

The where dbt_valid_to is null filter keeps only the currently-valid rows from the snapshot table. Rows with a filled dbt_valid_to are historical (superseded) records. This gives you the latest known status for each product without losing the history.

  • Directly below that CTE in the same file, add the aggregation logic and final select:

match_rates as (
    select
        client_id,
        count(*) as total_products,
        sum(case when match_status = 'same' then 1 else 0 end) as same_count,
        sum(case when match_status = 'similar' then 1 else 0 end) as similar_count,
        sum(case when match_status = 'not_found' then 1 else 0 end) as not_found_count,
        round(
            sum(case when match_status in ('same', 'similar') then 1 else 0 end) * 100.0
            / nullif(count(*), 0),
            2
        ) as match_rate_pct,
        round(
            sum(case when match_status = 'not_found' then 1 else 0 end) * 100.0
            / nullif(count(*), 0),
            2
        ) as not_found_rate_pct
    from current_snapshot
    group by client_id
)

select * from match_rates

What does this aggregation produce?

  • total_products: the number of currently-valid product mappings per client.
  • same_count, similar_count, not_found_count: breakdowns by match status.
  • match_rate_pct: percentage of products with a successful match (same + similar).
  • not_found_rate_pct: percentage still unmatched. High values flag a problem.
  • Save models/marts/metrics_match_rates.sql.
  • Run the full build to materialize the metrics table:
dbt build
  • Switch to Snowsight and query your new metrics mart:
select * from PRODUCT_MAPPING_DB.DBT_DEV.METRICS_MATCH_RATES;

You should see one row per client with their match rate percentage. Both clients now show improved match rates compared to what the raw batches alone would suggest, because the snapshot reflects the latest valid state after the status changes.

Seeing a compilation error about snap_product_mappings?

The metrics model depends on the snapshot table. If you dropped and haven't re-run your snapshot, the ref('snap_product_mappings') reference has nothing to point at. Run dbt snapshot first, then dbt build again.

help me fix a compilation error in my metrics_match_rates model

✔️ Awesome, I've got everything!

Great. Double check you saved both snapshots/_snapshots.yml and models/marts/metrics_match_rates.sql.

ⓧ I'd like to double check the full code

Your snapshots/_snapshots.yml should look like this:

snapshots:
  - name: snap_product_mappings
    relation: ref('stg_product_mappings')
    description: SCD Type 2 snapshot tracking changes in product match status over time.
    config:
      schema: snapshots
      unique_key: "source_product_id || '-' || competitor_platform"
      strategy: check
      check_cols:
        - match_status
        - competitor_product_url
        - competitor_sku

Your models/marts/metrics_match_rates.sql has two CTEs. Scroll up and confirm you have both the current_snapshot CTE (filtering by dbt_valid_to is null) and the match_rates aggregation CTE, ending with select * from match_rates.

Also confirm stg_product_mappings.sql is fully restored with all three batches (no commented-out lines remaining).

Your warehouse now preserves the full history of every product mapping change. Next up, you'll add data quality tests that catch bad data before it corrupts these metrics.

Add Data Quality Tests

Your dimensional model and snapshots are working, but right now nothing stops bad data from flowing into your metrics. A "Same" match with no competitor URL, or a batch where most results are "Not Found," would silently corrupt your match rate reporting.

In this step, you will run your existing schema tests, plant intentionally bad data to prove your pipeline catches it, then write a custom singular test that enforces a business rule no schema test can cover.

In this step, get ready to:
  • Run schema tests against your staging and marts layers to confirm data integrity.
  • Plant bad data and write a custom singular test that catches business rule violations.
  • Fix the bad data and generate full project documentation with a lineage graph.
Run your existing schema tests

Your _staging.yml and _marts.yml files already declare schema tests: not_null on key columns, accepted_values on match_status, unique on primary keys, and a relationships test from fact to dimension.

  • Run the full test suite by running this command:
dbt test

You should see all tests pass with a summary line showing the total number of tests and zero failures.

What do schema tests check?

Schema tests validate the shape and integrity of your data. They confirm columns are not null, values stay within allowed lists, primary keys are unique, and foreign keys resolve to the referenced table.

These tests catch structural problems. But they cannot enforce business rules like "a Same match must always have a competitor URL." That requires a custom test.

Seeing test failures already?

If a test fails at this point, check that your seed data loaded correctly by running dbt seed first. Ensure your _staging.yml accepted_values list matches exactly: ['same', 'similar', 'not_found'].

help me debug my dbt test failures

Plant bad data and write a custom singular test

Schema tests proved your data structure is sound. Now you will intentionally introduce a business rule violation to show the gap that schema tests cannot cover.

  • Open seeds/batch_003_mappings.csv in VS Code.
  • Find the last row (SKU-2005) which currently has an empty match_status field.
  • Change that row's match_status from empty to Same, leaving competitor_product_url and competitor_sku empty.

The modified row should look like this:

SKU-2005,https://marketplace-c.eu/p/SKU-2005,,,Same,client_beta,Accessories,marketplace_c,marketplace_d

Why is this bad data?

A "Same" match status means you found the exact same product on a competitor platform. If that is true, there must be a competitor URL proving the match exists. A "Same" with no URL is a data quality violation that would corrupt your match rate metrics.

  • Save batch_003_mappings.csv and reload the seed data by running:
dbt seed

Now run the test suite to see if your existing schema tests catch this violation:

  • Run schema tests by running:
dbt test

All tests pass. The accepted_values test does not flag it because "same" is a valid status. The not_null test does not flag it because match_status has a value. The business rule violation slips through silently.

Your schema tests have a blind spot

This is the exact scenario that corrupts production reporting. The data passes every structural check but violates a business rule. You need a custom test to catch it.

A singular test in dbt is a standalone SQL file in the tests/ directory. If the query returns any rows, the test fails. You will write one that asserts: no row should have match_status = 'same' with a null or empty competitor_product_url.

  • Create a new file tests/assert_same_has_competitor_url.sql and add the following query:
select
    source_product_id,
    match_status,
    competitor_product_url
from {{ ref('stg_product_mappings') }}
where match_status = 'same'
  and (competitor_product_url is null or trim(competitor_product_url) = '')

What does this test do?

This query searches for rows that claim a "same" match but provide no competitor URL as proof. If the query returns zero rows, the test passes. If it returns any rows, dbt reports those as failures.

The trim() check handles the case where a value looks present but is just whitespace.

Before you run this, do you think the test will pass or fail given the bad row you just planted?

  • Run the test suite again by running:
dbt test

You should see the assert_same_has_competitor_url test FAIL. It caught the SKU-2005 row that your schema tests missed.

Test did not fail?

Check that you saved batch_003_mappings.csv with Same in the match_status column for SKU-2005. Confirm you ran dbt seed after editing the CSV so the new data is loaded into Snowflake.

help me debug why my singular test is not catching the bad row

Fix the bad data and generate documentation

You proved your custom test works. Now revert the bad data and confirm the full pipeline builds cleanly.

  • Open seeds/batch_003_mappings.csv in VS Code.
  • Find the SKU-2005 row and change its match_status back from Same to empty (delete the word, leaving two consecutive commas).
  • Save batch_003_mappings.csv.

The row should look like this again:

SKU-2005,https://marketplace-c.eu/p/SKU-2005,,,,client_beta,Accessories,marketplace_c,marketplace_d
  • Run a full project build to confirm everything passes by running:
dbt build

You should see seeds loaded, models built, snapshots captured, and all tests passing with zero failures.

Still seeing test failures?

Make sure the SKU-2005 row has an empty match_status field (two consecutive commas with nothing between them). If you accidentally left a space, trim() will resolve it, but double-check the CSV formatting.

help me fix my dbt build failures after reverting the bad data

Now generate project documentation so you can see the full lineage of your warehouse.

  • Generate the documentation catalog by running:
dbt docs generate
  • Serve the documentation site locally by running:
dbt docs serve

Your browser opens to http://localhost:8080 showing an interactive documentation site.

  • Click the lineage graph icon (bottom-right corner of the page) to view the full DAG.

You should see the complete flow: seed files feeding into stg_product_mappings, which feeds the snapshot, dimensions, fact table, and metrics mart.

What does the lineage graph prove?

The lineage graph shows every dependency in your project. If a seed changes, you can trace exactly which models, snapshots, and tests are affected. This is how production teams do impact analysis before modifying upstream data.

  • Press Ctrl+C in your terminal to stop the docs server when you are finished exploring.

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files and that dbt build passes cleanly.

ⓧ I'd like to double check the full code

Here is your singular test file. Confirm it matches:

select
    source_product_id,
    match_status,
    competitor_product_url
from {{ ref('stg_product_mappings') }}
where match_status = 'same'
  and (competitor_product_url is null or trim(competitor_product_url) = '')

Your seeds/batch_003_mappings.csv should have SKU-2005 with an empty match_status field (not "Same"). Your _staging.yml and _marts.yml should be unchanged from the previous step.

Your warehouse now has automated guardrails. Schema tests enforce structure, and your custom singular test enforces a business rule that would otherwise slip through. Next up, the Secret Mission challenges you to write reusable generic test macros that scale across your entire pipeline.

Secret mission

Advanced Data Quality Rules

Production mapping pipelines need business-rule tests that go beyond schema validation. In this mission, you write reusable generic test macros and analytical singular tests that catch data anomalies automatically. These are the exact patterns EU analytics engineering teams test for in interviews.

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. Your Snowflake warehouse only consumes credits while actively running queries, and your local project folder takes minimal disk space.

Cost warning

Your $400 Snowflake trial credits are more than sufficient for this project (typical usage: under $1). However, credits expire after 30 days regardless of usage. If you want to continue experimenting, keep an eye on your remaining credit balance in the Snowflake UI.

Resources you used:

  • Snowflake trial account with PRODUCT_MAPPING_DB database, TRANSFORM_WH warehouse, and DBT_DEV schema.
  • Local product_mapping project folder and Python virtual environment on your Desktop.

Keep everything running

No action needed. Choose this if you are still actively building or want to experiment with additional models and snapshots.

  • Your Snowflake warehouse auto-suspends after 5 minutes of inactivity, so credits only tick down during active compute.
  • Your local project folder and virtual environment remain on your Desktop ready for the next session.
  • The trial expires after 30 days regardless of usage. No credit card is required, so you will not be charged.

Pause - I'll come back to this later

Shut down running processes to free up memory and ensure the warehouse is not consuming credits while you are away. Your files and Snowflake data stay intact.

  • In Snowsight, open a SQL worksheet and manually suspend the warehouse by running:
ALTER WAREHOUSE TRANSFORM_WH SUSPEND;
  • In your terminal, deactivate the Python virtual environment by running:
deactivate
  • Resume anytime within 30 days. Re-activate your venv and the warehouse will auto-resume on your next dbt run.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is irreversible.

  • In Snowsight, open a SQL worksheet and drop the database by running:
DROP DATABASE PRODUCT_MAPPING_DB;
  • Let the Snowflake trial expire naturally after 30 days. No credit card is on file, so no charges will apply.
  • Delete your local project folder and virtual environment. On macOS or Linux, run:
cd ~/Desktop
rm -rf product_mapping
  • On Windows, run:
cd ~\Desktop
Remove-Item -Recurse -Force product_mapping

Nice Work!

Nice Work!

Nice work! You just built a production-style competitive product mapping warehouse on Snowflake, complete with dimensional modeling, SCD Type 2 history tracking, and automated data quality enforcement.

You've learned how to:

  • Build a dimensional model (3 dimensions + 1 fact table) on Snowflake that transforms raw competitive product mapping spreadsheets into analyst-ready tables you can query with a single JOIN.
  • Implement SCD Type 2 history tracking using dbt snapshots with the check strategy, proving you can answer "when did this product's match status change?" with exact validity timestamps.
  • Create a metrics mart calculating match rates per client and status-change flags, backed by automated data quality tests that enforce business rules across the mapping pipeline.
  • Secret Mission: Wrote reusable generic test macros and analytical singular tests that catch data anomalies like orphan matches, duplicate mappings, and suspiciously high Not Found rates.

Ready to quiz yourself?