Snowflake dbt Incremental Pipeline

Build and repair a Snowflake dbt pipeline for late-arriving order events.

Introduction

30 Second Summary

A polished data interview answer can unravel as soon as a follow-up asks for evidence. General experience feels much less convincing when you cannot point to a failure you diagnosed yourself.

In this project, you will build a Snowflake interview-calibration pipeline. dbt runs the SQL that exposes a silent late-arriving-data failure.

What You'll Build

You will demo a repaired order pipeline that reports $250.00 in completed revenue while its reconciliation checks pass.

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

  • A current-order result with one row per order. You can show how window functions select the latest event from repeated records. You can trace JSON attributes into typed columns.
  • A reproducible late-arrival diagnosis that catches the silent $270.00 result. You can show how an ingestion-time lookback closes the watermark gap. You can explain how the order_id business key supports an idempotent merge.
  • An evidence-based interview scorecard grounded in Snowflake Query Profile findings. You can separate demonstrated strengths from topics that still need practice.
  • Secret Mission: Build a Snowflake dynamic table that maintains completed revenue by channel. Defend when it fits better than dbt incremental models. Explain when Streams plus Tasks provide the procedural control you need.

Are there any prerequisites?

You need an existing Snowflake account with object-creation access.

Your Mac also needs the listed local tools: VS Code, Git, Python 3.10 or later, plus dbt.

Before We Start

This checkpoint locks in why the lab matters before any hands-on work begins. You will frame the pipeline as interview evidence for your Snowflake, SQL, and dbt depth. You will also choose whether SQL accuracy, Snowflake explanation, or architecture defense matters most.

Prepare the Snowflake and dbt Lab

Your interview target is clear. The next risk is mistaking environment drift for a gap in your Snowflake or SQL knowledge.

A pinned dbt environment gives every later result a trustworthy starting point. This step also creates the Snowflake workspace that the pipeline uses.

In this step, get ready to:
  • Isolate dbt in a pinned virtual environment.
  • Provision the Snowflake lab workspace.
  • Prove the local profile can connect to Snowflake.
Create the pinned dbt environment

A virtual environment keeps this lab's packages separate from the rest of your Mac. The adapter requires Python 3.10 or later.

  • Check the installed Python version by running this command:
python3 --version

What does this command check?

The command reports which Python interpreter your Mac finds first. The version determines whether the pinned dbt adapter can run.

✔️ I see Python 3.10 or higher

Your Python interpreter meets the adapter requirement. Continue with the isolated project environment.

ⓧ I see an older version

  • Install Python 3.10 or later by following the official macOS guidance.
  • Press Cmd+Space after the installation finishes.
  • Type Terminal.
  • Press Enter.
  • Return to the Python version check above.

ⓧ Command not found

  • Install Python 3.10 or later by following the official macOS guidance.
  • Press Cmd+Space after the installation finishes.
  • Type Terminal.
  • Press Enter.
  • Return to the Python version check above.
  • Move to your Desktop by running this command:
cd ~/Desktop

What does this command do?

This moves Terminal to your Desktop. The project folder appears somewhere you can find easily.

  • Create the project folder and move into it by running these commands:
mkdir snowflake-interview-prep
cd snowflake-interview-prep

What do these commands do?

  • The first command creates the snowflake-interview-prep folder on your Desktop.
  • The second command makes that folder Terminal's current location.
  • Create the isolated environment and activate it by running these commands:
python3 -m venv .venv
source .venv/bin/activate

How does the environment stay isolated?

  • The first command creates .venv inside the project folder.
  • The second command updates the current shell to use that environment.
  • Your Terminal prompt should now begin with (.venv).
  • Install the pinned Snowflake adapter by running this command:
python3 -m pip install dbt-snowflake==1.12.0

What does this installation add?

  • The package installs the Snowflake adapter at version 1.12.0.
  • The compatible dbt Core package is installed in the same virtual environment.
  • Packages outside .venv remain unchanged.

Package installation failed?

Confirm that your Terminal prompt begins with (.venv). Check that the Python version from the earlier command is 3.10 or later.

Use this if the installation still fails: Help me diagnose my dbt adapter installation.

  • Check the installed dbt versions by running this command:
dbt --version

What does this version check prove?

The output identifies the active dbt Core version. It also lists the installed Snowflake adapter version.

✔️ I see dbt 1.12.0

That removes the local version uncertainty. Your lab now uses dbt Core 1.12.0 with the Snowflake adapter at 1.12.0.

ⓧ I see a different version

  • Confirm that your Terminal prompt begins with (.venv).
  • Reactivate the environment with the activation command above when the prefix is missing.
  • Repeat the pinned installation command above.
  • Return to the dbt version check.

ⓧ Command not found

  • Reactivate the environment with the activation command above.
  • Repeat the pinned installation command above.
  • Return to the dbt version check.
Provision the Snowflake workspace

The local adapter needs a real warehouse plus a dedicated database workspace. Snowsight gives you a SQL worksheet for creating all three resources together.

  • Return to the Terminal session with the active .venv.
  • Create the snowflake directory by running this command:
mkdir snowflake

Why use a separate directory?

The snowflake directory keeps account-level SQL separate from dbt models. This file becomes a reusable setup script.

  • Press Cmd+Space to open macOS search.
  • Type Visual Studio Code.
  • Press Enter.
  • Use the VS Code folder picker to select ~/Desktop/snowflake-interview-prep.
  • Use the file sidebar to create setup.sql inside the snowflake directory.
  • Add the workspace setup SQL by pasting this code into snowflake/setup.sql:
CREATE WAREHOUSE IF NOT EXISTS INTERVIEW_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS INTERVIEW_PREP;
CREATE SCHEMA IF NOT EXISTS INTERVIEW_PREP.DBT_DEV;

What does this setup create?

  • The first statement creates INTERVIEW_WH at size XSMALL.
  • The warehouse begins in a suspended state.
  • The database statement creates INTERVIEW_PREP.
  • The schema statement creates INTERVIEW_PREP.DBT_DEV.
  • Save snowflake/setup.sql.
  • Confirm that setup.sql appears inside the snowflake directory in the file sidebar.
  • Sign in to Snowsight through your browser.
  • Create a SQL worksheet for the lab.
  • Paste the contents of snowflake/setup.sql into the worksheet.

You remain in control of the compute because the warehouse begins suspended. Its 60 second auto-suspend setting limits idle runtime.

Before you run the worksheet, which Snowflake objects do you expect the three statements to create?

  • Run every statement in the worksheet.

You should see successful results for the warehouse named INTERVIEW_WH. You should also see successful results for the database and schema.

You now have the Snowflake foundation in place. The workspace is ready to receive dbt objects.

Snowflake rejected a statement?

Confirm that your active Snowflake role can create warehouses. Confirm that the same role can create databases.

Use this if a permission or syntax problem remains: Help me diagnose my Snowflake setup worksheet.

Configure dbt and verify the connection

dbt needs a project definition plus a connection profile. Local environment variables supply your Snowflake identity without placing those values in the profile.

  • Switch back to the snowflake-interview-prep folder in VS Code.
  • Use the file sidebar to create .gitignore inside snowflake-interview-prep.
  • Add the local-only paths by pasting this content into .gitignore:
.venv/
.env
target/
logs/
dbt_packages/

What stays out of version control?

The file keeps the virtual environment plus dbt build output out of Git. It also keeps the populated .env file local.

  • Save .gitignore.
  • Confirm that .gitignore appears in the file sidebar.
  • Use the file sidebar to create .env.example inside snowflake-interview-prep.
  • Add the identity placeholders by pasting this content into .env.example:
SNOWFLAKE_ACCOUNT=your-account-identifier
SNOWFLAKE_USER=your-username
SNOWFLAKE_ROLE=your-object-creation-role

Why keep an example file?

The example documents the three values required by the profile. The placeholders make the file safe to keep in version control.

  • Save .env.example.
  • Confirm that .env.example appears in the file sidebar.
  • Use the file sidebar to create dbt_project.yml inside snowflake-interview-prep.
  • Define the dbt project by pasting this content into dbt_project.yml:
name: snowflake_interview_prep
version: '1.0.0'
config-version: 2
profile: snowflake_interview_prep

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
clean-targets: ["target", "dbt_packages"]

How does dbt read this project?

  • The project name links this folder to the matching local profile.
  • The path settings tell dbt where later models and tests belong.
  • The clean targets identify generated directories that dbt can remove.
  • Save dbt_project.yml.
  • Confirm that dbt_project.yml appears in the file sidebar.

Project file not recognized?

Confirm that the filename ends in .yml. Confirm that VS Code did not add a hidden text extension.

Use this if dbt cannot find the project file: Help me check my dbt project location.

  • Use the file sidebar to create profiles.yml inside snowflake-interview-prep.
  • Define the Snowflake connection by pasting this content into profiles.yml:
snowflake_interview_prep:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      authenticator: externalbrowser
      database: INTERVIEW_PREP
      warehouse: INTERVIEW_WH
      schema: DBT_DEV
      threads: 4

How does the profile connect?

  • The profile reads your Snowflake account identity from .env.
  • The browser authenticator sends sign-in through your browser.
  • The target points dbt at INTERVIEW_PREP.DBT_DEV.
  • The profile gives dbt four execution threads.
  • Save profiles.yml.
  • Confirm that profiles.yml appears in the file sidebar.

Profile showing YAML errors?

Keep the indentation exactly as shown. Use spaces throughout the file.

Use this if the profile still fails to parse: Help me inspect my dbt profile indentation.

  • Return to the activated Terminal session.
  • Create the local environment file by running this command:
cp .env.example .env

What does this copy protect?

The command creates .env from the safe template. The populated file remains local because .gitignore excludes it.

  • Switch back to VS Code.
  • Open .env from the file sidebar.
  • Replace every placeholder with its matching Snowflake identity value.
  • Save .env.

Which value belongs in each field?

  • Use your Snowflake account identifier for SNOWFLAKE_ACCOUNT.
  • Use your Snowflake username for SNOWFLAKE_USER.
  • Use your object-creation role for SNOWFLAKE_ROLE.

✔️ Awesome, I've got everything!

  • Confirm that every setup file is saved.
  • Continue to the connection check below.

ⓧ I'd like to double check the full code

.venv/
.env
target/
logs/
dbt_packages/

What should this file contain?

Compare each ignored path with your saved .gitignore file.

SNOWFLAKE_ACCOUNT=your-account-identifier
SNOWFLAKE_USER=your-username
SNOWFLAKE_ROLE=your-object-creation-role

What should this template contain?

The template should contain all three placeholder values. Keep your real Snowflake identity values in .env.

name: snowflake_interview_prep
version: '1.0.0'
config-version: 2
profile: snowflake_interview_prep

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
clean-targets: ["target", "dbt_packages"]

What should the project definition match?

The project name should match the top-level name in profiles.yml.

snowflake_interview_prep:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      authenticator: externalbrowser
      database: INTERVIEW_PREP
      warehouse: INTERVIEW_WH
      schema: DBT_DEV
      threads: 4

What should the profile target?

The profile should target the lab database plus its development schema. Browser authentication should remain enabled.

CREATE WAREHOUSE IF NOT EXISTS INTERVIEW_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS INTERVIEW_PREP;
CREATE SCHEMA IF NOT EXISTS INTERVIEW_PREP.DBT_DEV;

What should the Snowflake setup match?

The script should create the project warehouse plus the database workspace. The warehouse should start suspended.

  • Confirm that INTERVIEW_WH uses size XSMALL.
  • Confirm that INTERVIEW_WH uses an auto-suspend value of 60 seconds.
  • Confirm that INTERVIEW_PREP exists.
  • Confirm that INTERVIEW_PREP.DBT_DEV exists.

The connection check opens your browser for Snowflake authentication. Your password stays outside the project files.

Before you run the check, do you expect dbt to find both the local project and the Snowflake connection?

  • Return to the activated Terminal session.
  • Verify the complete dbt setup by running this command:
dbt debug --profiles-dir .

What does this connection check cover?

  • dbt checks the project configuration in the current directory.
  • dbt checks the local profile selected by the directory flag.
  • dbt checks the installed adapter compatibility.
  • dbt tests the connection to Snowflake.
  • Complete the Snowflake sign-in in the browser window.
  • Return to Terminal after authentication finishes.

You should see the project and profile checks complete without an error. The Snowflake connection check should also complete successfully.

That is the setup uncertainty removed. Your pinned dbt environment can now reach the dedicated Snowflake workspace.

Connection check failed?

Confirm that .env contains your real account identifier. Confirm that the username and role match your Snowflake access.

Check that the Terminal prompt still begins with (.venv). Rerun the connection check after correcting one issue.

Use this if the failure remains: Help me diagnose my dbt connection check.

Your lab is ready. Next, you will turn raw order events into the first correct current-order view.

Build the First Correct Order View

Your pinned dbt environment can now connect to Snowflake. The next risk sits inside the transformation logic.

Incremental processing adds state to a pipeline. A trusted baseline first proves the SQL grain against a small set of repeated order events.

You will also parse JSON attributes before using a window function to preserve one current row per order. This gives you a visible result before later batches add more complexity.

In this step, get ready to:
  • Load baseline order events into Snowflake with a dbt seed.
  • Build a staging view that parses attributes and deduplicates each order.
  • Create an incremental mart with tests and verify its baseline revenue.
Load the baseline order events

A dbt seed turns a version-controlled CSV file into a Snowflake table. This dataset includes two events for O101 so the staging model must choose the current one.

  • Press Cmd+Space to open macOS search.
  • Type Visual Studio Code into the search bar.
  • Press Enter to open VS Code.
  • Select File from the top menu.
  • Select Open Folder....
  • Select the existing snowflake-interview-prep folder.
  • Select Open.

You will see the existing project files in the Explorer view.

  • Create a folder named seeds inside snowflake-interview-prep from the Explorer view.

The seeds folder now appears beside your existing configuration files.

  • Select the seeds folder.
  • Select New File... from the Explorer view.
  • Enter raw_order_events.csv as the file name.
  • Populate seeds/raw_order_events.csv by pasting this dataset:
event_id,order_id,customer_id,customer_email,order_status,order_amount,event_ts,ingested_at,payload
E100,O100,C100,alice@example.com,completed,100.00,2026-09-01 10:00:00,2026-09-01 10:01:00,"{""channel"":""web"",""region"":""US""}"
E099,O101,C101,bob@example.com,completed,45.00,2026-09-01 09:59:00,2026-09-01 10:00:00,"{""channel"":""partner"",""region"":""US""}"
E101,O101,C101,bob@example.com,completed,50.00,2026-09-01 10:05:00,2026-09-01 10:06:00,"{""channel"":""partner"",""region"":""US""}"
E102,O102,C102,carol@example.com,pending,75.00,2026-09-01 10:10:00,2026-09-01 10:11:00,"{""channel"":""web"",""region"":""CA""}"

What does this dataset prove?

Each row represents an order event. The timestamps let the staging model determine which event is current.

  • Order O100 has one completed event worth 100.00.
  • Order O101 has two completed events with different timestamps.
  • Order O102 has one pending event worth 75.00.
  • The payload column stores channel and region attributes as JSON text.
  • Save seeds/raw_order_events.csv.
  • Switch back to the macOS Terminal from earlier.
  • Load the seed and confirm the project still builds by running:
dbt build --profiles-dir .

What does this command do?

The command processes resources in dependency order. At this point it loads raw_order_events.csv into the configured DBT_DEV schema.

The --profiles-dir . flag directs dbt to the profiles.yml file in the current project directory.

The seed finishes successfully. Your version-controlled event data now exists as INTERVIEW_PREP.DBT_DEV.RAW_ORDER_EVENTS.

Seed not loading?

Confirm the terminal is still inside snowflake-interview-prep. Check that the activated environment indicator is still visible.

Check that raw_order_events.csv sits directly inside seeds.

Ask for help with the exact terminal output: Why can't dbt load my raw_order_events seed?

✔️ Awesome, I've got everything!

Your baseline seed file is saved with all four order events.

ⓧ I'd like to double check the full code

event_id,order_id,customer_id,customer_email,order_status,order_amount,event_ts,ingested_at,payload
E100,O100,C100,alice@example.com,completed,100.00,2026-09-01 10:00:00,2026-09-01 10:01:00,"{""channel"":""web"",""region"":""US""}"
E099,O101,C101,bob@example.com,completed,45.00,2026-09-01 09:59:00,2026-09-01 10:00:00,"{""channel"":""partner"",""region"":""US""}"
E101,O101,C101,bob@example.com,completed,50.00,2026-09-01 10:05:00,2026-09-01 10:06:00,"{""channel"":""partner"",""region"":""US""}"
E102,O102,C102,carol@example.com,pending,75.00,2026-09-01 10:10:00,2026-09-01 10:11:00,"{""channel"":""web"",""region"":""CA""}"
Build the current-row staging view

The seed stores event history. The staging view establishes a clear model grain by preserving one current row for each order_id.

  • Return to VS Code.
  • Create a folder named models inside snowflake-interview-prep from the Explorer view.

The models folder now appears in the project tree.

  • Create a folder named staging inside models.
  • Create a new file named stg_order_events.sql inside models/staging.
  • Populate models/staging/stg_order_events.sql by pasting this transformation:
WITH typed_events AS (
    SELECT
        event_id::VARCHAR AS event_id,
        order_id::VARCHAR AS order_id,
        customer_id::VARCHAR AS customer_id,
        customer_email::VARCHAR AS customer_email,
        order_status::VARCHAR AS order_status,
        order_amount::NUMBER(10, 2) AS order_amount,
        event_ts::TIMESTAMP_NTZ AS event_ts,
        ingested_at::TIMESTAMP_NTZ AS ingested_at,
        PARSE_JSON(payload::VARCHAR) AS order_payload
    FROM {{ ref('raw_order_events') }}
)
SELECT
    event_id,
    order_id,
    customer_id,
    customer_email,
    order_status,
    order_amount,
    event_ts,
    ingested_at,
    order_payload:channel::VARCHAR AS sales_channel,
    order_payload:region::VARCHAR AS sales_region
FROM typed_events
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY event_ts DESC, ingested_at DESC, event_id DESC
) = 1

How does the staging view work?

  • The explicit casts give each field a stable Snowflake data type.
  • The PARSE_JSON call converts the payload text into a VARIANT value named order_payload.
  • The path expressions extract sales_channel and sales_region as strings.
  • The ROW_NUMBER() ordering ranks the newest event first within each order.
  • The QUALIFY condition keeps the row ranked 1.
  • Save models/staging/stg_order_events.sql.
  • Switch back to the macOS Terminal from earlier.
  • Build the staging view by running:
dbt build --profiles-dir .

What happens during this build?

The ref('raw_order_events') call creates a dependency on the seed. dbt loads that dependency before creating the staging view.

The successful result confirms that Snowflake accepts the casts, JSON traversal, window function, and QUALIFY filter.

You will see the seed complete before stg_order_events finishes successfully. The view now exposes one current row for each order.

Staging view not building?

Confirm that the model references raw_order_events with the same spelling as the CSV file.

Check the commas after each selected column. Pay particular attention to the line before PARSE_JSON.

Ask for help with the compiled failure: Why is my stg_order_events model failing to build?

✔️ Awesome, I've got everything!

Your staging view parses the payload and preserves one current event per order.

ⓧ I'd like to double check the full code

WITH typed_events AS (
    SELECT
        event_id::VARCHAR AS event_id,
        order_id::VARCHAR AS order_id,
        customer_id::VARCHAR AS customer_id,
        customer_email::VARCHAR AS customer_email,
        order_status::VARCHAR AS order_status,
        order_amount::NUMBER(10, 2) AS order_amount,
        event_ts::TIMESTAMP_NTZ AS event_ts,
        ingested_at::TIMESTAMP_NTZ AS ingested_at,
        PARSE_JSON(payload::VARCHAR) AS order_payload
    FROM {{ ref('raw_order_events') }}
)
SELECT
    event_id,
    order_id,
    customer_id,
    customer_email,
    order_status,
    order_amount,
    event_ts,
    ingested_at,
    order_payload:channel::VARCHAR AS sales_channel,
    order_payload:region::VARCHAR AS sales_region
FROM typed_events
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY event_ts DESC, ingested_at DESC, event_id DESC
) = 1
Build and verify the incremental mart

An incremental model preserves its existing table between runs. This first version uses the latest event_ts already stored as its processing boundary.

The mart keeps the current-order fields needed for reporting. Its baseline result gives you a precise reference point for future batches.

  • Return to VS Code.
  • Create a folder named marts inside models from the Explorer view.

The marts folder now appears beside staging.

  • Create a new file named fct_current_orders.sql inside models/marts.
  • Populate models/marts/fct_current_orders.sql by pasting this model:
{{ config(materialized='incremental') }}

SELECT
    order_id,
    customer_id,
    customer_email,
    order_status,
    order_amount,
    event_ts,
    ingested_at,
    sales_channel,
    sales_region
FROM {{ ref('stg_order_events') }}
{% if is_incremental() %}
WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }})
{% endif %}

How does the mart build?

  • The configuration materializes fct_current_orders as an incremental table.
  • The ref('stg_order_events') call places the staging view earlier in the dbt dependency graph.
  • The first run selects every current order because the table does not exist yet.
  • Later runs apply the is_incremental() filter against the maximum stored event_ts.
  • Save models/marts/fct_current_orders.sql.
  • Switch back to the macOS Terminal from earlier.
  • Build the incremental table by running:
dbt build --profiles-dir .

What does this build establish?

dbt processes the seed before the staging view. It then creates fct_current_orders as a table in INTERVIEW_PREP.DBT_DEV.

This first table contains current rows for O100, O101, and O102.

The mart finishes successfully. Your pipeline now reaches a reportable current-order table.

Incremental table not building?

Confirm that fct_current_orders.sql sits inside models/marts.

Check that every selected column exists in stg_order_events.sql with identical spelling.

Ask for help with the failing model: Why is my fct_current_orders incremental model failing?

✔️ Awesome, I've got everything!

Your first incremental mart matches the intended baseline configuration.

ⓧ I'd like to double check the full code

{{ config(materialized='incremental') }}

SELECT
    order_id,
    customer_id,
    customer_email,
    order_status,
    order_amount,
    event_ts,
    ingested_at,
    sales_channel,
    sales_region
FROM {{ ref('stg_order_events') }}
{% if is_incremental() %}
WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }})
{% endif %}

The table now exists. Data tests turn its grain and required values into executable assertions.

  • Create a new file named schema.yml inside models.
  • Populate models/schema.yml by pasting these model tests:
version: 2

models:
  - name: stg_order_events
    description: One current event per order after parsing and deduplication.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: sales_channel
        data_tests:
          - not_null

  - name: fct_current_orders
    description: Incrementally maintained current state of every order.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed', 'pending', 'cancelled']

What do these tests protect?

  • The not_null tests require every current order to have an identifier.
  • The unique tests enforce one row per order_id.
  • The staging test requires every row to have a sales_channel.
  • The accepted_values test limits order_status to the three expected states.
  • Save models/schema.yml.

Before you run the complete build, do you expect the duplicate O101 events to make the uniqueness tests pass or fail?

  • Run the models and their tests by using:
dbt build --profiles-dir .

What does the successful build prove?

The staging view selects the newer O101 event before the tests inspect the model. The duplicate historical events therefore become one current row.

The completed build proves that the seed, view, table, and generic tests work as one dependency graph.

You will see every model and test finish successfully. The pipeline now enforces its current-order grain.

Seeing a failed data test?

A uniqueness failure usually means the QUALIFY ROW_NUMBER() logic is missing or differs from the staging model shown above.

An accepted-values failure points to a spelling difference in the seed's order_status values.

Ask for help interpreting the test result: Why did my baseline dbt data test fail?

✔️ Awesome, I've got everything!

Your model tests match the baseline grain and status rules.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_order_events
    description: One current event per order after parsing and deduplication.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: sales_channel
        data_tests:
          - not_null

  - name: fct_current_orders
    description: Incrementally maintained current state of every order.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed', 'pending', 'cancelled']

A KPI query turns the passing build into an observable business result. It reports completed orders by channel before returning the overall total.

  • Create a folder named snowflake inside snowflake-interview-prep from the Explorer view.

The snowflake folder now appears beside models.

  • Create a new file named kpi_query.sql inside snowflake.
  • Populate snowflake/kpi_query.sql by pasting these queries:
SELECT
    sales_channel,
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed'
GROUP BY sales_channel
ORDER BY completed_revenue DESC;

SELECT
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed';

What do these queries measure?

The first query groups completed orders by sales_channel. This exposes the contribution from web and partner orders.

The second query removes the grouping. Its single row provides the baseline completed-order count and revenue total.

  • Save snowflake/kpi_query.sql.
  • Return to Snowsight.
  • Create a SQL worksheet.
  • Copy the contents of snowflake/kpi_query.sql into the worksheet.

Before you execute the queries, which O101 amount do you expect the current-order view to count?

  • Execute both SQL statements in the worksheet.

The channel result shows one web order worth 100.00 and one partner order worth 50.00.

The total result shows 2 completed orders with completed revenue of 150.00. That is the baseline locked in: your SQL selected the correct current event for O101.

KPI result looks different?

A total of 145.00 means the staging view selected the older O101 event. Check the descending timestamp order inside ROW_NUMBER().

A result with more than two completed orders means historical events reached the mart without being deduplicated.

Ask for help tracing the row choice: Why does my baseline KPI differ from 150.00?

✔️ Awesome, I've got everything!

Your saved KPI query reproduces the verified baseline result.

ⓧ I'd like to double check the full code

SELECT
    sales_channel,
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed'
GROUP BY sales_channel
ORDER BY completed_revenue DESC;

SELECT
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed';

Your baseline pipeline now produces one current row per order with verified revenue of 150.00. Next, you will feed it another event batch and test whether this incremental boundary holds.

Expose the Late-Arrival Defect

Your baseline dbt pipeline in Snowflake reports two completed orders with revenue of 150.00. That known result gives you a reliable point of comparison.

A successful job can still produce the wrong business result when an event-time watermark meets late-arriving data. This step creates that failure so you can diagnose it from evidence.

In this step, get ready to:
  • Add a late correction plus a new order to the seed data.
  • Combine both event feeds in the staging model.
  • Prove the incremental defect with a reconciliation test plus the KPI query.
Add the late events

A late-arriving event has an ingestion time that comes after its business event time. The new seed contains one correction plus one genuinely new order.

  • In the VS Code file tree from earlier, select the seeds folder.
  • Use the new-file control to create late_order_events.csv inside seeds.
  • Paste the following rows into seeds/late_order_events.csv:
event_id,order_id,customer_id,customer_email,order_status,order_amount,event_ts,ingested_at,payload
E200,O100,C100,alice@example.com,completed,80.00,2026-09-01 10:02:00,2026-09-01 11:00:00,"{""channel"":""web"",""region"":""US""}"
E201,O103,C103,dave@example.com,completed,120.00,2026-09-01 10:20:00,2026-09-01 11:01:00,"{""channel"":""web"",""region"":""US""}"

What do these rows represent?

  • The E200 event corrects O100 from 100.00 to 80.00.
  • The correction has an event_ts of 2026-09-01 10:02:00.
  • The correction has a later ingested_at value of 2026-09-01 11:00:00.
  • The E201 event introduces O103 with an amount of 120.00.
  • Save seeds/late_order_events.csv.
  • Confirm that late_order_events.csv is listed under seeds in the file tree.
  • Return to the terminal from earlier.
  • Load both seed files into Snowflake by running:
dbt seed --full-refresh --profiles-dir .

What does this command do?

The full refresh reloads the CSV files configured under the project's seed path. This gives Snowflake a fresh RAW_ORDER_EVENTS table plus a new LATE_ORDER_EVENTS table.

You should see both seed resources complete successfully. The new late-event rows are now available to the staging model.

Seed command not completing?

Check that late_order_events.csv sits directly inside seeds. Confirm that each data row has the same nine columns as the header.

If authentication opens in your browser again, finish the existing external browser sign-in flow.

Ask for help with the seed failure.

Combine both event feeds and add the test

The staging model needs one candidate set before it chooses the current event for each order. UNION ALL preserves every event from both seeds for that comparison.

  • Switch back to the existing models/staging/stg_order_events.sql tab.
  • Find the opening line WITH typed_events AS (.
  • Replace the opening CTE through its closing parenthesis with the following block:
WITH all_events AS (
    SELECT * FROM {{ ref('raw_order_events') }}
    UNION ALL
    SELECT * FROM {{ ref('late_order_events') }}
),
typed_events AS (
    SELECT
        event_id::VARCHAR AS event_id,
        order_id::VARCHAR AS order_id,
        customer_id::VARCHAR AS customer_id,
        customer_email::VARCHAR AS customer_email,
        order_status::VARCHAR AS order_status,
        order_amount::NUMBER(10, 2) AS order_amount,
        event_ts::TIMESTAMP_NTZ AS event_ts,
        ingested_at::TIMESTAMP_NTZ AS ingested_at,
        PARSE_JSON(payload::VARCHAR) AS order_payload
    FROM all_events
)

What does this code do?

  • The all_events CTE gathers rows from both seed relations.
  • The UNION ALL operation retains every candidate event.
  • The typed_events CTE applies the existing casts plus JSON parsing to the combined data.
  • The existing QUALIFY clause still chooses one current row for each order_id.
  • Save models/staging/stg_order_events.sql.
  • Search the file for ref('late_order_events').

You should find one reference to each seed above the existing casting logic. The deduplication query remains at the bottom of the file.

Staging file looks incomplete?

Make sure the replacement ends after the closing parenthesis for typed_events. Keep the existing final SELECT plus its QUALIFY clause below it.

Ask for help checking the staging CTE boundary.

A singular data test is a SQL query that returns the rows violating a business assertion. This test treats any completed-revenue total other than 250.00 as evidence of a defect.

  • Use the new-folder control in the VS Code file tree to create tests inside snowflake-interview-prep.

You should see tests beside the existing models and seeds folders.

  • Select the new tests folder.
  • Use the new-file control to create assert_completed_revenue.sql inside tests.
  • Paste the following test into tests/assert_completed_revenue.sql:
WITH actual AS (
    SELECT COALESCE(SUM(order_amount), 0) AS actual_revenue
    FROM {{ ref('fct_current_orders') }}
    WHERE order_status = 'completed'
)
SELECT actual_revenue
FROM actual
WHERE actual_revenue <> 250.00

How does the reconciliation work?

  • The actual CTE calculates completed revenue from fct_current_orders.
  • The final filter returns a row whenever actual_revenue differs from 250.00.
  • dbt treats a returned row as a failed data test.
  • Save tests/assert_completed_revenue.sql.
  • Confirm that assert_completed_revenue.sql appears under tests in the file tree.

Test file showing a syntax warning?

Check that the closing parenthesis appears before the final SELECT. Confirm that the model reference is spelled fct_current_orders.

Ask for help checking the reconciliation test syntax.

Use this comparison to confirm that each file matches the intended defect scenario:

✔️ Awesome, I've got everything!

Great. Save all three files before you run the pipeline.

ⓧ I'd like to double check the full code

Your seeds/late_order_events.csv file should match this reference:

event_id,order_id,customer_id,customer_email,order_status,order_amount,event_ts,ingested_at,payload
E200,O100,C100,alice@example.com,completed,80.00,2026-09-01 10:02:00,2026-09-01 11:00:00,"{""channel"":""web"",""region"":""US""}"
E201,O103,C103,dave@example.com,completed,120.00,2026-09-01 10:20:00,2026-09-01 11:01:00,"{""channel"":""web"",""region"":""US""}"

Late seed reference

This file contains exactly two new events. One corrects O100. The other creates O103.

Your models/staging/stg_order_events.sql file should match this reference:

WITH all_events AS (
    SELECT * FROM {{ ref('raw_order_events') }}
    UNION ALL
    SELECT * FROM {{ ref('late_order_events') }}
),
typed_events AS (
    SELECT
        event_id::VARCHAR AS event_id,
        order_id::VARCHAR AS order_id,
        customer_id::VARCHAR AS customer_id,
        customer_email::VARCHAR AS customer_email,
        order_status::VARCHAR AS order_status,
        order_amount::NUMBER(10, 2) AS order_amount,
        event_ts::TIMESTAMP_NTZ AS event_ts,
        ingested_at::TIMESTAMP_NTZ AS ingested_at,
        PARSE_JSON(payload::VARCHAR) AS order_payload
    FROM all_events
)
SELECT
    event_id,
    order_id,
    customer_id,
    customer_email,
    order_status,
    order_amount,
    event_ts,
    ingested_at,
    order_payload:channel::VARCHAR AS sales_channel,
    order_payload:region::VARCHAR AS sales_region
FROM typed_events
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY event_ts DESC, ingested_at DESC, event_id DESC
) = 1

Staging model reference

This file combines both seed relations before applying the existing casts. The final window function preserves one row at order_id grain.

Your tests/assert_completed_revenue.sql file should match this reference:

WITH actual AS (
    SELECT COALESCE(SUM(order_amount), 0) AS actual_revenue
    FROM {{ ref('fct_current_orders') }}
    WHERE order_status = 'completed'
)
SELECT actual_revenue
FROM actual
WHERE actual_revenue <> 250.00

Revenue test reference

This file returns the calculated revenue only when it violates the expected total. An empty result represents a passing reconciliation.

Run the pipeline and expose the defect

The lab can now compare pipeline execution with business correctness. The model command plus the reconciliation test provide separate pieces of evidence.

Before you run these commands, do you think a successful model run guarantees that the reconciliation test will pass?

  • Replay the seed-to-test sequence by running these commands:
dbt seed --full-refresh --profiles-dir .
dbt run --profiles-dir .
dbt test --profiles-dir .

What does this sequence prove?

  • The seed command reloads both event feeds.
  • The run command executes the staging model plus the existing incremental mart.
  • The test command checks structural assertions plus completed-revenue reconciliation.

The dbt run completes successfully. The reconciliation test fails because it returns actual_revenue of 270.00.

That failed test is useful evidence. You now have a repeatable case where execution success hides an incorrect business result.

Seeing a command error instead of the planned test failure?

The intended result is a completed model run followed by a failed revenue test. A compilation or connection error means the pipeline did not reach the designed defect.

Check the saved staging CTE plus the test file against the reference tab. Complete any external browser authentication request before retrying.

Ask for help separating the expected test failure from an environment error.

Before you execute the KPI query, which value for O100 do you think the incremental table kept?

  • Switch back to the Snowsight worksheet from the baseline check.
  • Paste the contents of snowflake/kpi_query.sql into the worksheet.
  • Execute both KPI statements.

You will see three completed orders with total completed revenue of 270.00. The channel result shows web at 220.00 plus partner at 50.00.

Why did the successful run produce the wrong total?

The staging model selects the 80.00 correction for O100. Its event_ts is still below the incremental table's previous maximum event time.

The naive mart excludes that correction through its event-time filter. It appends the newer O103 row while leaving stale O100 revenue at 100.00.

You have exposed a silent incremental defect with a failed test plus a visible KPI mismatch. Next, you will repair the business key and watermark strategy so the correction updates safely.

Repair the Incremental Model

The previous step proved that a green dbt run can preserve the wrong business state. The event-time watermark left O100 at 100.00 after its 80.00 correction arrived late.

Correctness now needs a stable business key. The repaired model uses order_id as the match key for Snowflake's MERGE.

It also needs a watermark that accounts for ingestion delay. A two-hour ingested_at lookback reprocesses recent rows safely.

In this step, get ready to:
  • Give the incremental model a stable merge key.
  • Add a two-hour ingestion-time lookback.
  • Prove the corrected revenue with data tests.
Configure stable merge behavior

An incremental merge needs a key that identifies the same business record across runs. The schema-change policy also defines how the model responds when its columns drift.

  • Switch back to models/marts/fct_current_orders.sql in VS Code.
  • Locate the existing config call at the top of the file.
  • Replace that call with this configuration:
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        incremental_strategy='merge',
        on_schema_change='fail'
    )
}}

What does this configuration do?

  • The unique_key='order_id' setting defines the model's business grain.
  • The incremental_strategy='merge' setting updates matching orders.
  • The on_schema_change='fail' setting stops the run when unexpected column drift appears.
  • Save models/marts/fct_current_orders.sql.
  • Check that dbt accepts the merge configuration by running this command:
dbt run --select fct_current_orders --profiles-dir .

What did this check prove?

You'll see fct_current_orders finish successfully. This confirms that dbt accepts the merge configuration before you change the watermark.

Configuration run failing?

  • Confirm that the config block has matching parentheses.
  • Complete the browser sign-in if dbt requests external authentication again.

Help me diagnose why the dbt merge configuration fails to run.

Replace the event-time watermark

The late correction carries an older event_ts value. Its newer ingested_at value reveals when the pipeline actually received it.

  • Select the query body from the first SELECT below the configuration through the closing {% endif %}.
  • Replace the selected query body with this code:
WITH source_rows AS (
    SELECT
        order_id,
        customer_id,
        customer_email,
        order_status,
        order_amount,
        event_ts,
        ingested_at,
        sales_channel,
        sales_region
    FROM {{ ref('stg_order_events') }}
    {% if is_incremental() %}
    WHERE ingested_at >= DATEADD(
        hour,
        -2,
        (
            SELECT COALESCE(
                MAX(ingested_at),
                '1900-01-01'::TIMESTAMP_NTZ
            )
            FROM {{ this }}
        )
    )
    {% endif %}
)
SELECT * FROM source_rows

How does the lookback work?

  • The source_rows common table expression selects the current-order columns from staging.
  • The is_incremental() block applies the watermark after the target table exists.
  • The DATEADD expression moves the ingestion watermark back by two hours.
  • The COALESCE fallback supplies an old timestamp when the target has no ingestion history.
  • Save models/marts/fct_current_orders.sql.
  • Apply the repaired watermark by running this command:
dbt run --select fct_current_orders --profiles-dir .

What did the repaired run do?

You'll see fct_current_orders finish successfully. The lookback makes the corrected O100 row eligible for the merge.

Repaired model not running?

  • Confirm that the incremental filter uses ingested_at.
  • Check that the final SELECT * FROM source_rows remains below the common table expression.
  • Complete the browser authentication flow if Snowflake requests it.

Help me troubleshoot the repaired incremental model.

✔️ Awesome, I've got everything!

Your repaired incremental model is saved with its merge key plus its ingestion-time lookback.

ⓧ I'd like to double check the full code

  • Compare models/marts/fct_current_orders.sql with this complete version:
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        incremental_strategy='merge',
        on_schema_change='fail'
    )
}}

WITH source_rows AS (
    SELECT
        order_id,
        customer_id,
        customer_email,
        order_status,
        order_amount,
        event_ts,
        ingested_at,
        sales_channel,
        sales_region
    FROM {{ ref('stg_order_events') }}
    {% if is_incremental() %}
    WHERE ingested_at >= DATEADD(
        hour,
        -2,
        (
            SELECT COALESCE(
                MAX(ingested_at),
                '1900-01-01'::TIMESTAMP_NTZ
            )
            FROM {{ this }}
        )
    )
    {% endif %}
)
SELECT * FROM source_rows

What should match?

Your file should contain one configuration block followed by the source_rows query. The final line should select from that common table expression.

Prove the repaired result

A successful model run proves that the SQL executed. The tests now determine whether the resulting business state is correct.

Before the test, predict whether the repaired merge now satisfies the revenue reconciliation.

  • Run every generic test plus the singular reconciliation test with this command:
dbt test --profiles-dir .

What should you see?

Every generic data test passes. The assert_completed_revenue singular test also passes because it returns zero failing rows.

Still seeing a failed test?

  • Confirm that tests/assert_completed_revenue.sql still compares revenue with 250.00.
  • Rerun the repaired model before testing again.
  • Confirm that unique_key='order_id' remains in the model configuration.

Help me diagnose the remaining dbt test failure.

Before the KPI check, predict how the corrected O100 row changes the channel total.

  • Switch back to snowflake/kpi_query.sql in VS Code.
  • Select the full contents of the file.
  • Copy the selected SQL.
  • Return to the Snowsight worksheet from earlier.
  • Paste the copied SQL into the worksheet.
  • Execute both queries.

The channel query shows web revenue of 200.00 from 2 completed orders. It shows partner revenue of 50.00 from 1 completed order.

The total query shows 3 completed orders worth 250.00. Your passing unique test confirms one row per order_id.

Revenue still showing 270.00?

  • Confirm that you saved the repaired fct_current_orders.sql file before the latest model run.
  • Check that the worksheet queries INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS.
  • Confirm that the model filter uses ingested_at >= DATEADD.

Help me find why the repaired pipeline still reports 270.00.

That is the difficult part done: your pipeline now turns a late correction into the right current state. Next, you will use Snowflake Query Profile to gather execution evidence for your interview defense.

Profile and Defend the Design

Your repaired dbt model now produces one current row per order. The passing reconciliation test proves that completed revenue is back to 250.00.

Senior interview answers need observable execution evidence. They also need explicit trade-offs. This step uses Snowsight to turn the repaired pipeline into an evidence-backed architecture defense.

In this step, get ready to:
  • Inspect the dbt-generated MERGE in Snowflake Query Profile.
  • Capture scan evidence from the completed-revenue query.
  • Build an interview scorecard with a 90-second architecture defense.
Inspect the generated MERGE

A Snowflake MERGE can insert matching source rows or update existing target rows. Its Query Profile shows what the warehouse actually changed during the repaired incremental run.

  • In the signed-in Snowsight session from earlier, select Monitoring in the navigation menu.
  • Select Query History.
  • Select Individual Queries.
  • Set the SQL Text filter to merge into.
  • Select the recent statement generated for the incremental run of FCT_CURRENT_ORDERS.

Before you inspect the profile, which row-change category do you expect to reflect the correction to O100?

  • Open Query Profile for the selected statement.
  • Select the Merge operator in the operator tree.
  • Record the inserted-row count as inserted row count.
  • Record the updated-row count as updated row count.
  • Record the deleted-row count as deleted row count.

You now have row-change evidence from the operator. That evidence connects the dbt configuration to work performed inside Snowflake.

Can't find the MERGE profile?

Confirm that the SQL Text filter contains merge into. Check that Query History covers the time of your repaired model run.

If several statements match, choose the one that targets FCT_CURRENT_ORDERS.

Help me locate and interpret the dbt-generated MERGE in Snowflake Query Profile.

Profile the KPI scan

The KPI query gives you a second execution profile to inspect. Its TableScan operator reveals how much data Snowflake scanned to produce the result.

  • Switch back to the Snowsight worksheet from earlier.
  • Paste the contents of snowflake/kpi_query.sql into the worksheet:
SELECT
    sales_channel,
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed'
GROUP BY sales_channel
ORDER BY completed_revenue DESC;

SELECT
    COUNT(*) AS completed_orders,
    SUM(order_amount) AS completed_revenue
FROM INTERVIEW_PREP.DBT_DEV.FCT_CURRENT_ORDERS
WHERE order_status = 'completed';

What does this SQL measure?

  • The first statement groups completed orders by sales channel.
  • The second statement calculates the completed-order total across every channel.
  • Both statements read from the repaired current-order table.

Before you run the SQL, do you expect the channel totals to reconcile with the 250.00 overall total?

  • Select all SQL in the worksheet.
  • Click Run.

You should see web revenue of 200.00. You should also see partner revenue of 50.00 and total completed revenue of 250.00.

  • Return to Query History from earlier.
  • Set the SQL Text filter to FCT_CURRENT_ORDERS.
  • Select the recent completed-revenue query.
  • Open Query Profile.
  • Select the TableScan operator.

The TableScan details answer how much work Snowflake performed. Record these values so your scorecard can cite the plan.

  • Record the bytes scanned as bytes scanned.
  • Record the percentage scanned from cache as cache percentage.
  • Record the partitions scanned as partitions scanned.
  • Record the partitions total as partitions total.

What does this profile prove?

Bytes scanned measures the volume read by the query. The cache percentage shows how much of that scan used cached data.

Comparing partitions scanned with partitions total helps you discuss micro-partition pruning.

This four-row table teaches you how to read the plan. It provides too little evidence for a clustering key recommendation.

Missing the TableScan details?

Confirm that you selected the completed-revenue query instead of the earlier MERGE. Expand the operator tree until you can select the scan that reads FCT_CURRENT_ORDERS.

Help me interpret the TableScan evidence from my Snowflake KPI query.

Build your interview defense

An interview scorecard separates claims you can demonstrate from topics you can only explain. A score of 0 means unsupported. A score of 1 means explainable. A score of 2 means demonstrated from this lab.

  • Return to VS Code.
  • Select New File in the Explorer sidebar.
  • Name the new file interview_scorecard.md.

You should see an empty interview_scorecard.md file inside snowflake-interview-prep.

  • Add the scorecard structure by pasting this template:
# Snowflake, SQL, and dbt Interview Scorecard

Scoring: 0 = unsupported, 1 = explainable, 2 = demonstrated from the lab.

| Area | Score | Evidence from this lab | Gap or next drill |
|---|---:|---|---|
| SQL grain and deduplication |  |  |  |
| Window functions and QUALIFY |  |  |  |
| JSON and VARIANT handling |  |  |  |
| Late-arriving data |  |  |  |
| dbt refs and DAG behavior |  |  |  |
| dbt incremental MERGE |  |  |  |
| dbt generic and singular tests |  |  |  |
| Schema evolution policy |  |  |  |
| Snowflake Query Profile |  |  |  |
| Micro-partition pruning evidence |  |  |  |
| Warehouse and cache explanation |  |  |  |
| Dynamic tables vs Streams and Tasks |  |  |  |
| Security and PII design |  |  |  |
| Cost and operational controls |  |  |  |
| 90-second architecture explanation |  |  |  |

## Required 90-second defense

1. State the model grain and business invariant.
2. Explain why the event-time watermark silently missed the correction.
3. Explain how the ingestion-time lookback and order_id MERGE restore idempotency.
4. Cite Query Profile evidence without claiming that this tiny dataset proves a production tuning change.
5. Choose dbt incremental models, dynamic tables, or Streams and Tasks for the next production requirement and name the deciding constraint.

How does the scorecard work?

Each row asks for a score plus evidence from the lab. The final column turns any unsupported or explainable topic into a specific next drill.

The defense prompts convert the table into a concise architecture explanation. Every claim should point to SQL output, test results, or Query Profile evidence.

  • Save interview_scorecard.md.
  • Confirm that interview_scorecard.md remains listed in the Explorer sidebar.

The file should show the score table followed by five numbered defense prompts.

Does the Markdown table look broken?

Check that every table row starts and ends with a vertical bar. Keep the separator row directly below the header row.

Help me fix the formatting in my interview scorecard without changing its scoring areas.

✔️ Awesome, I've got everything!

Great. Your scorecard scaffold is saved and ready for your evidence.

ⓧ I'd like to double check the full code

# Snowflake, SQL, and dbt Interview Scorecard

Scoring: 0 = unsupported, 1 = explainable, 2 = demonstrated from the lab.

| Area | Score | Evidence from this lab | Gap or next drill |
|---|---:|---|---|
| SQL grain and deduplication |  |  |  |
| Window functions and QUALIFY |  |  |  |
| JSON and VARIANT handling |  |  |  |
| Late-arriving data |  |  |  |
| dbt refs and DAG behavior |  |  |  |
| dbt incremental MERGE |  |  |  |
| dbt generic and singular tests |  |  |  |
| Schema evolution policy |  |  |  |
| Snowflake Query Profile |  |  |  |
| Micro-partition pruning evidence |  |  |  |
| Warehouse and cache explanation |  |  |  |
| Dynamic tables vs Streams and Tasks |  |  |  |
| Security and PII design |  |  |  |
| Cost and operational controls |  |  |  |
| 90-second architecture explanation |  |  |  |

## Required 90-second defense

1. State the model grain and business invariant.
2. Explain why the event-time watermark silently missed the correction.
3. Explain how the ingestion-time lookback and order_id MERGE restore idempotency.
4. Cite Query Profile evidence without claiming that this tiny dataset proves a production tuning change.
5. Choose dbt incremental models, dynamic tables, or Streams and Tasks for the next production requirement and name the deciding constraint.

Use your recorded MERGE statistics in the incremental-model evidence row. Use your recorded scan statistics in the Query Profile evidence rows.

  • Enter a score from 0 to 2 for every scorecard area.
  • Add one lab observation to every evidence cell.

Read the completed evidence column from top to bottom. You should see which claims are demonstrated by this lab and which claims still need practice.

Choose the right boundary

A dbt incremental model fits version-controlled transformations with tests and dependency management.

A dynamic table fits a declarative SQL pipeline that needs Snowflake-managed refreshes.

Streams and Tasks fit procedural processing such as custom MERGE logic. They also fit custom retries, external calls, or SCD Type 2 history.

  • Add one next drill to every area scored below 2.
  • State that the model grain is one current row per order_id.
  • Explain how the event-time watermark missed the late correction.
  • Explain how the ingestion-time lookback captures delayed records.
  • Explain how the order_id MERGE supports idempotent reruns.
  • Explain how on_schema_change='fail' makes unexpected schema changes visible.
  • State that the tiny dataset cannot justify production clustering changes.
  • Close with your tool choice for the next production requirement.

Before you start the timer, which claim in your defense will be hardest to support with evidence?

  • Set a 90-second timer.
  • Deliver the architecture defense aloud.

Your defense should connect each architecture claim to the lab evidence. It should also name the production questions that this tiny dataset leaves unanswered.

You have turned a repaired pipeline into evidence you can defend under follow-up questions. Your scorecard now shows exactly where your interview preparation is proven and where it needs another drill.

Secret mission

Replace the Aggregate with a Dynamic Table

Move the completed-revenue aggregate into a Snowflake-managed dynamic table with incremental refreshes. Verify its revenue by channel before defending when dynamic tables, dbt, or Streams and Tasks fit the requirement.

Clean Up Your Resources

Clean Up Your Resources

Your Snowflake resources can continue consuming compute or storage. Choose whether to keep the lab available, pause its active compute, or delete everything created for this project.

Cost warning

The dynamic table can trigger scheduled refresh work. The virtual warehouse consumes credits while running.

A short auto-suspend setting reduces unintended consumption. Pause or Delete is the safer choice when you have finished using the lab.

Resources you used:

  • Snowflake warehouse INTERVIEW_WH.
  • Snowflake database INTERVIEW_PREP, including its DBT_DEV schema.
  • Snowflake dynamic table INTERVIEW_PREP.DBT_DEV.DT_COMPLETED_REVENUE_BY_CHANNEL.
  • Repaired dbt models, seeds, test objects, and Query Profile evidence stored in Snowflake.
  • Local snowflake-interview-prep folder containing the project files, .venv, and .env.

Keep everything running

No action is needed. Choose this if you want to repeat the lab or use it for another mock interview.

  • Keep the Snowflake pipeline available for future demonstrations.
  • Leave INTERVIEW_WH configured with AUTO_SUSPEND = 60.
  • Keep the dynamic table scheduled only while you need refreshed aggregate results.
  • Keep interview_scorecard.md available for future interview drills.

Pause - I'll come back to this later

Suspend scheduled refresh work to limit active compute. Your database objects and local project files remain available for later.

  • Return to your existing Snowsight session.
  • Pause the dynamic table refreshes and warehouse compute by running this SQL in a worksheet:
ALTER DYNAMIC TABLE INTERVIEW_PREP.DBT_DEV.DT_COMPLETED_REVENUE_BY_CHANNEL SUSPEND;
ALTER WAREHOUSE INTERVIEW_WH SUSPEND;

What Do These Statements Pause?

  • The ALTER DYNAMIC TABLE statement suspends refreshes for DT_COMPLETED_REVENUE_BY_CHANNEL.
  • The ALTER WAREHOUSE statement suspends INTERVIEW_WH.

Both statements should complete without an error. Your Snowflake database and local project files remain in place.

Delete - I don't want to use this again

Remove every project resource when you want a clean reset. Everything created for this lab will be gone afterward.

  • Copy interview_scorecard.md outside snowflake-interview-prep if you want to preserve your interview evidence.
  • Delete the project-owned Snowflake objects by running this SQL in a Snowsight worksheet:
DROP DYNAMIC TABLE IF EXISTS INTERVIEW_PREP.DBT_DEV.DT_COMPLETED_REVENUE_BY_CHANNEL;
DROP DATABASE IF EXISTS INTERVIEW_PREP;
DROP WAREHOUSE IF EXISTS INTERVIEW_WH;

What Does This Cleanup Remove?

  • The DROP DYNAMIC TABLE IF EXISTS statement removes the scheduled aggregate.
  • The DROP DATABASE IF EXISTS statement removes INTERVIEW_PREP with its contained schema objects.
  • The DROP WAREHOUSE IF EXISTS statement removes INTERVIEW_WH.
  • Refresh the database list in Snowsight. You should no longer see INTERVIEW_PREP.
  • Refresh the warehouse list in Snowsight. You should no longer see INTERVIEW_WH.
  • Use your Mac's file browser to locate snowflake-interview-prep.
  • Move snowflake-interview-prep to the Bin.
  • Empty the Bin to permanently delete the local project files.
  • Search for snowflake-interview-prep to confirm the folder is no longer listed.

Nice Work!

Nice Work!

You did it! You built a production-shaped order pipeline with Snowflake plus dbt. You turned a silent revenue defect into evidence you can explain under interview pressure.

You've learned how to:

  • Made the one-row-per-order grain explicit with SQL window functions. Used ROW_NUMBER() plus QUALIFY to select the current event. Parsed JSON attributes into queryable sales fields.
  • Reproduced a silent late-arriving-data defect that reported completed revenue of $270.00. Exposed the stale order with a failing reconciliation test.
  • Repaired the model with an order_id merge key plus a two-hour ingestion lookback. Verified the corrected revenue of $250.00. Read Query Profile evidence for the generated merge. Captured proven strengths plus next drills in interview_scorecard.md.
  • Secret Mission: Created DT_COMPLETED_REVENUE_BY_CHANNEL as a Snowflake dynamic table that reports web revenue of $200.00 plus partner revenue of $50.00. Defended when declarative refreshes fit the requirement. Explained when dbt incremental models or Streams and Tasks provide a better fit.

Ready to quiz yourself?