Medallion Architecture in Snowflake

Introduction

30 Second Summary

Every time you place an online order, that transaction passes through multiple layers of processing before it becomes a revenue number on a dashboard. Raw data arrives messy, duplicated, and untyped. It needs progressive refinement before anyone can trust it for business decisions.

In this project, you will build a complete 3-layer data pipeline in Snowflake that processes e-commerce order data from S3 through Bronze, Silver, and Gold layers. You will bulk-load historical orders, simulate an incremental load with duplicates and updates, and solve the deduplication problem using the industry-standard MERGE pattern.

What You'll Build

You will run a query against your Gold layer and see daily revenue totals broken down by order status, produced from data that started as messy CSV files and flowed through your pipeline without a single duplicate.

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

• A working Medallion Architecture where you can drop a CSV into S3, run your pipeline, and watch cleaned data appear in Silver with duplicates and bad records filtered out.
• Both backfill and incremental loading patterns demonstrated side by side. You can show exactly how a new file with updated records gets handled without creating duplicates.
• A Gold layer producing daily revenue summaries that automatically reflect late-arriving and updated orders. Query it and see business-ready metrics instantly.
• Secret Mission: Automate your Bronze-to-Silver pipeline with Snowflake Streams and Tasks so new data flows through without you lifting a finger.

Are there any prerequisites?

This project assumes you have a Snowflake account (a free trial works perfectly), an AWS account with S3 access, and the AWS CLI installed and configured. You should be comfortable writing SQL queries.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Snowflake and S3

Before you can move any data through a pipeline, you need the infrastructure to support it. Your Medallion Architecture needs a database with separate schemas for each layer (Bronze, Silver, Gold), a warehouse to execute queries, and a bridge between Snowflake and the S3 bucket where your raw files land.

In this step, you will create all the Snowflake objects, prepare sample order data as CSV files, and connect everything through an external stage so Snowflake can read directly from S3.

In this step, get ready to:

• Create the warehouse, database, and three schemas in Snowflake.
• Create CSV data files and upload them to S3.
• Create a file format and external stage linking Snowflake to S3.

Create the warehouse, database, and schemas

Snowflake keeps compute (warehouses) separate from storage (databases). You need a warehouse to run queries and a database to hold your three medallion layers as separate schemas.

• Sign in to your Snowflake account at app.snowflake.com.
• Open a new SQL worksheet by clicking Projects in the left navigation, then Worksheets, then the + button and selecting SQL Worksheet.

• Create your warehouse, database, and schemas by pasting and running the following SQL:

-- Create warehouse
CREATE OR REPLACE WAREHOUSE ORDERS_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

USE WAREHOUSE ORDERS_WH;

-- Create database and schemas
CREATE OR REPLACE DATABASE ORDERS_PIPELINE;

CREATE SCHEMA ORDERS_PIPELINE.BRONZE;
CREATE SCHEMA ORDERS_PIPELINE.SILVER;
CREATE SCHEMA ORDERS_PIPELINE.GOLD;

What does this SQL do?

• ORDERS_WH is an XSMALL warehouse that auto-suspends after 60 seconds of inactivity to save credits.
• ORDERS_PIPELINE is your database, which holds all pipeline objects.
• BRONZE, SILVER, and GOLD schemas separate your data layers inside the same database.

• Select all the SQL in the worksheet and click the Run button (or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
• Confirm the output pane shows success messages for each statement.

Seeing an error about privileges?

Make sure you are using a role with CREATE WAREHOUSE and CREATE DATABASE privileges. On a free trial account, use the ACCOUNTADMIN role by running USE ROLE ACCOUNTADMIN; at the top of your worksheet.

help me fix a privilege error when creating a warehouse in Snowflake

Create the CSV data files and upload to S3

Your pipeline needs raw order data to process. You will create two CSV files that simulate hourly order dumps from an e-commerce system.

• Open your terminal.
• Move to your Desktop by running this command:

cd ~/Desktop

• Create a project folder and move into it by running these commands:

mkdir medallion-data
cd medallion-data

• Create the historical orders file by saving the following as orders_historical.csv:

order_id,customer_id,amount,status,order_date
1001,C100,250.00,pending,2024-01-15
1002,C101,89.99,shipped,2024-01-15
1003,C102,432.50,delivered,2024-01-16
1004,C100,67.25,pending,2024-01-16
1005,C103,199.99,shipped,2024-01-17
1006,C104,55.00,cancelled,2024-01-17
1007,C105,312.75,delivered,2024-01-18
1008,C101,148.50,shipped,2024-01-18
1009,C106,,pending,2024-01-19
1010,C107,725.00,pending,2024-01-19

What is in this file?

This file has 10 historical orders with different statuses. Notice row 1009 has a blank amount field. This simulates real-world messy data that your Silver layer will handle later.

• Create the incremental orders file by saving the following as orders_incremental.csv:

order_id,customer_id,amount,status,order_date
1001,C100,250.00,shipped,2024-01-15
1004,C100,67.25,shipped,2024-01-16
1011,C108,189.00,pending,2024-01-20
1012,C109,445.75,pending,2024-01-20
1013,C110,-50.00,pending,2024-01-20

Why does this file have duplicates?

Orders 1001 and 1004 already exist in the historical file, but their status changed from pending to shipped. This simulates real incremental loads where records get updated. Order 1013 has a negative amount to test your data quality filters later.

• Upload both files to your S3 bucket under the medallion/ prefix by running these commands:

aws s3 cp orders_historical.csv s3://[[BUCKET_NAME="your-bucket-name"]]/medallion/orders_historical.csv
aws s3 cp orders_incremental.csv s3://[[BUCKET_NAME="your-bucket-name"]]/medallion/orders_incremental.csv

• Verify the upload by listing the files in your bucket:

aws s3 ls s3://[[BUCKET_NAME="your-bucket-name"]]/medallion/

You should see both orders_historical.csv and orders_incremental.csv listed with their file sizes and timestamps.

Seeing 'upload failed' or 'access denied'?

Confirm your AWS CLI is configured with credentials that have write access to the bucket. Run aws sts get-caller-identity to check which account is active. Also confirm the bucket name is correct and already exists in your AWS account.

help me fix an S3 upload failure from AWS CLI

Create the file format and external stage

Snowflake needs two things to read your S3 files: a file format describing the CSV structure, and an external stage pointing at the S3 location.

• Switch back to your Snowflake worksheet and paste the following SQL below your previous statements:

-- Create file format
USE SCHEMA ORDERS_PIPELINE.BRONZE;

CREATE OR REPLACE FILE FORMAT csv_format
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  SKIP_HEADER = 1
  NULL_IF = ('', 'NULL');

-- Create external stage (replace with your bucket and credentials)
CREATE OR REPLACE STAGE orders_stage
  URL = 's3://your-bucket-name/medallion/'
  CREDENTIALS = (AWS_KEY_ID = 'your-access-key-here' AWS_SECRET_KEY = 'your-secret-key-here')
  FILE_FORMAT = csv_format;

What does this SQL do?

• csv_format tells Snowflake to skip the header row, handle quoted fields, and treat empty strings as NULL.
• orders_stage is an external stage that points directly at your S3 bucket's medallion/ prefix using your AWS credentials.

• Replace your-bucket-name with your actual S3 bucket name.
• Replace your-access-key-here and your-secret-key-here with the AWS access key ID and secret access key that have read access to your bucket.
• Select these new statements and click Run.

Use the same credentials as your CLI

The AWS credentials you use here should match the ones configured in your AWS CLI (the same ones that successfully uploaded files in the previous substep). You can find them in your ~/.aws/credentials file.

Before you run the next command, what do you think it will return?

• Verify that Snowflake can see your S3 files by running:

-- Verify stage
LIST @orders_stage;

You should see a table with two rows listing orders_historical.csv and orders_incremental.csv with their file sizes.

• Confirm your database and schema context by running:

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

This returns ORDERS_PIPELINE and BRONZE. Your foundation is ready.

LIST shows zero rows?

Double check that the URL in your stage ends with medallion/ (trailing slash included). Also confirm the AWS credentials have read access to the bucket. If you see an authentication error, verify the key ID and secret key are pasted correctly with no extra spaces.

help me fix my Snowflake external stage not listing S3 files

✔️ Awesome, I've got everything!

Great. Double check you have saved your worksheet in Snowflake.

ⓧ I'd like to double check the full code

Your worksheet should contain two chunks in order:

• Chunk 1: warehouse, database, and schema creation (starting with CREATE OR REPLACE WAREHOUSE ORDERS_WH).
• Chunk 2: file format and external stage (starting with USE SCHEMA ORDERS_PIPELINE.BRONZE;).
• The final line should be LIST @orders_stage;.

Scroll up and confirm each chunk is present in order with no missing statements.

Your infrastructure is in place. Snowflake has a warehouse, a clean schema layout, and a direct line of sight into your S3 bucket. Next up, you will bulk-load that historical order data into your Bronze layer.

Backfill Bronze

Your database, schemas, and external stage are all wired up. Snowflake can see your CSV files sitting in S3.

Now you need to get that historical data into your Bronze layer. Real pipelines always start with a backfill. This is the bulk load of everything that existed before your pipeline went live. Bronze stores it exactly as it arrived, preserving every raw field as your audit trail.

In this step, get ready to:

• Create the bronze_orders table with all-VARCHAR columns to preserve raw data.
• Load historical order data from S3 using COPY INTO.
• Verify all 10 rows landed with their source filename tracked.

Create the bronze_orders table

Bronze is your raw layer. Every column is stored as VARCHAR so that data arrives exactly as the source system sent it. Type casting happens later in Silver.

• In your Snowflake worksheet, create the Bronze table by running this SQL:

USE SCHEMA ORDERS_PIPELINE.BRONZE;

-- Create Bronze table (all VARCHAR to preserve raw data)
CREATE OR REPLACE TABLE bronze_orders (
  order_id    VARCHAR,
  customer_id VARCHAR,
  amount      VARCHAR,
  status      VARCHAR,
  order_date  VARCHAR,
  source_file VARCHAR,
  loaded_at   TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

What does this code do?

Every business column (order_id, amount, etc.) is VARCHAR. This means if a source system sends a malformed date or a letter where a number should be, Bronze still accepts it. You never lose data at ingestion.

The two extra columns track lineage: source_file records which file the row came from, and loaded_at timestamps when it arrived in Snowflake.

Why store everything as VARCHAR?

This is the schema-on-read pattern. Bronze makes zero assumptions about data types. If a price field contains "N/A" or an order date is formatted differently than expected, the row still lands safely.

Type enforcement happens in the Silver layer, where you have explicit control over what passes and what gets filtered out.

• You should see a confirmation message: "Table BRONZE_ORDERS successfully created."

Don't see the success message?

• Make sure you ran the USE SCHEMA ORDERS_PIPELINE.BRONZE; line at the top. Without it, Snowflake does not know where to create the table.
• Check that you have the ORDERS_WH warehouse active. Run USE WAREHOUSE ORDERS_WH; if it suspended.
• help me debug my CREATE TABLE statement

Backfill historical data from S3

The table is empty and ready to receive data. COPY INTO is Snowflake's bulk-loading command. It pulls data from your external stage directly into the table.

• Load the historical orders file by running this command:

-- Backfill: load historical data
COPY INTO bronze_orders (order_id, customer_id, amount, status, order_date, source_file)
FROM (
  SELECT $1, $2, $3, $4, $5, METADATA$FILENAME
  FROM @orders_stage/orders_historical.csv
);

What does this command do?

• The FROM subquery selects columns by position ($1, $2, etc.) from the staged CSV file.
• METADATA$FILENAME is a Snowflake-provided column that captures the source filename. This populates source_file so you always know where each row came from.
• COPY INTO automatically skips files it has already loaded (unless you force it). This makes backfills safe to re-run.

• You should see a results panel showing the file loaded, the number of rows parsed (10), and the number of rows loaded (10).

Seeing 0 rows loaded?

• Run LIST @orders_stage; to confirm your files are visible. If nothing appears, your stage credentials or bucket path may be incorrect.
• If you already ran this command once, Snowflake skips previously loaded files. Run TRUNCATE TABLE bronze_orders; then re-run the COPY INTO to start fresh.
• help me troubleshoot my COPY INTO command

Verify the backfill

Your Bronze table should now contain every row from the historical file. Before you query it, think about what you expect to see.

• How many rows do you expect the count query to return? Consider: your orders_historical.csv had 10 data rows (plus a header that the file format skips).
• Confirm the row count by running this query:

-- Verify
SELECT COUNT(*) FROM bronze_orders;

You should see 10. Bronze accepted every single row, including the one with a missing amount (order 1009). No data was rejected.

• Now inspect the actual data and the lineage columns by running:

SELECT * FROM bronze_orders LIMIT 5;

• Confirm you see raw data with a source_file column showing the filename and a loaded_at timestamp on every row.

What should I see?

Every column contains raw text values. The amount column shows 250.00 as a string, not a number. The source_file column shows the S3 path to orders_historical.csv. This lineage tracking means you can always trace any row back to its origin file.

✔️ Awesome, I've got everything!

Great. Double check you have 10 rows in bronze_orders and can see both the source_file and loaded_at columns populated.

ⓧ I'd like to double check the full code

Here is the complete bronze.sql you should have run in this step:

USE SCHEMA ORDERS_PIPELINE.BRONZE;

-- Create Bronze table (all VARCHAR to preserve raw data)
CREATE OR REPLACE TABLE bronze_orders (
  order_id    VARCHAR,
  customer_id VARCHAR,
  amount      VARCHAR,
  status      VARCHAR,
  order_date  VARCHAR,
  source_file VARCHAR,
  loaded_at   TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Backfill: load historical data
COPY INTO bronze_orders (order_id, customer_id, amount, status, order_date, source_file)
FROM (
  SELECT $1, $2, $3, $4, $5, METADATA$FILENAME
  FROM @orders_stage/orders_historical.csv
);

-- Verify
SELECT COUNT(*) FROM bronze_orders;
SELECT * FROM bronze_orders LIMIT 5;

Your Bronze layer is loaded and tracking lineage. Next up, you will build the Silver layer to clean and type-cast this raw data into something queryable.

Build Silver (Naive Approach)

Bronze captured every raw order exactly as it arrived from S3. That's great for auditing, but the data is all VARCHAR text. A business analyst querying amounts would get string comparisons instead of numeric math.

The Silver layer is where you clean, type-cast, and validate data so downstream consumers can trust it. In this step, you'll build Silver using a simple CREATE TABLE AS SELECT approach that works perfectly for the initial load.

In this step, get ready to:

• Create silver_orders with proper data types and quality filters.
• Verify Silver contains only clean, valid records.

Create silver_orders with type casting

Bronze stores everything as text, but Silver needs real data types. A CREATE TABLE AS SELECT (CTAS) lets you build a new table by transforming data from another table in a single statement.

• In your Snowflake worksheet, create Silver orders by running this SQL:

USE SCHEMA ORDERS_PIPELINE.SILVER;

-- Naive Silver: works for initial load
CREATE OR REPLACE TABLE silver_orders AS
SELECT
  TRY_CAST(order_id AS NUMBER)       AS order_id,
  customer_id,
  TRY_CAST(amount AS NUMBER(10,2))   AS amount,
  UPPER(TRIM(status))                AS status,
  TRY_CAST(order_date AS DATE)       AS order_date,
  loaded_at
FROM ORDERS_PIPELINE.BRONZE.bronze_orders
WHERE TRY_CAST(order_id AS NUMBER) IS NOT NULL
  AND TRY_CAST(amount AS NUMBER(10,2)) > 0;

What does this code do?

• TRY_CAST attempts to convert VARCHAR values into real types (NUMBER, DATE). If the cast fails, it returns NULL instead of throwing an error.
• UPPER(TRIM(status)) normalizes status values by removing whitespace and uppercasing them. This prevents 'pending' and ' Pending ' from appearing as different statuses.
• The WHERE clause filters out rows with NULL order IDs or non-positive amounts. These are data quality rules that keep bad records out of Silver.

Why keep Bronze as all-VARCHAR?

Bronze preserves raw data exactly as it arrived. If a type conversion fails, you never lose the original value. You can always go back to Bronze to debug what went wrong or reprocess with different rules.

Query silver_orders to confirm clean data

Before you run the count, think about how many rows you expect. Bronze has 10 rows, but Silver filters out records with NULL amounts or invalid order IDs. How many rows do you think made it through?

• Verify the row count by running this query:

-- Verify: should be 9 rows (1 row filtered for NULL amount)
SELECT COUNT(*) FROM silver_orders;

You should see 9 rows. Order 1009 had an empty amount field in the CSV, which TRY_CAST converted to NULL. The amount > 0 filter excluded it from Silver.

Seeing 0 rows or an error?

• If you see 0 rows, check that you ran the Bronze backfill in the previous step. Run SELECT COUNT(*) FROM ORDERS_PIPELINE.BRONZE.bronze_orders; to confirm Bronze has 10 rows.
• If you see a "table does not exist" error, make sure your USE SCHEMA ORDERS_PIPELINE.SILVER; line ran successfully before the CREATE statement.

Help me debug my Silver table creation.

✔️ Awesome, I've got everything!

Great. Double check you've saved your worksheet.

ⓧ I'd like to double check the full code

Here is the complete silver_naive.sql worksheet:

USE SCHEMA ORDERS_PIPELINE.SILVER;

-- Naive Silver: works for initial load
CREATE OR REPLACE TABLE silver_orders AS
SELECT
  TRY_CAST(order_id AS NUMBER)       AS order_id,
  customer_id,
  TRY_CAST(amount AS NUMBER(10,2))   AS amount,
  UPPER(TRIM(status))                AS status,
  TRY_CAST(order_date AS DATE)       AS order_date,
  loaded_at
FROM ORDERS_PIPELINE.BRONZE.bronze_orders
WHERE TRY_CAST(order_id AS NUMBER) IS NOT NULL
  AND TRY_CAST(amount AS NUMBER(10,2)) > 0;

-- Verify: should be 9 rows (1 row filtered for NULL amount)
SELECT COUNT(*) FROM silver_orders;

Silver is looking clean. You have properly-typed data with quality filters catching bad records. But this CTAS approach has a hidden flaw. Next up, you'll see what happens when new data arrives and tries to update existing orders.

Break Silver with Incremental Load

Your Silver layer looks great with 9 clean rows. But in production, new data arrives constantly. Your orders_incremental.csv file is already sitting in S3 with 5 new rows, including updated statuses for orders that already exist in your pipeline.

How do you get this new data into Silver? The simplest approach is to INSERT the new rows directly. In this step, you'll try exactly that and see what happens.

In this step, get ready to:

• Load the incremental file into Bronze using COPY INTO.
• INSERT the new Bronze rows into Silver.
• Query Silver to see what happened to your data.

Load the incremental file into Bronze

Bronze is your raw audit trail. Every file that arrives gets loaded as-is, even if it contains records you've seen before. That's by design.

• In your Snowflake worksheet, run this COPY INTO to load the incremental file from your stage into Bronze:

USE SCHEMA ORDERS_PIPELINE.BRONZE;

-- Load incremental file into Bronze
COPY INTO bronze_orders (order_id, customer_id, amount, status, order_date, source_file)
FROM (
  SELECT $1, $2, $3, $4, $5, METADATA$FILENAME
  FROM @orders_stage/orders_incremental.csv
);

What does this code do?

This is the same COPY INTO pattern you used for the historical load, but now pointing at orders_incremental.csv. Bronze accepts everything as raw VARCHAR, preserving the data exactly as it arrived.

• Confirm Bronze now has 15 rows by running this count:

-- Bronze now has 15 rows (duplicates expected in raw layer)
SELECT COUNT(*) FROM bronze_orders;

You should see 15 rows. That's your original 10 historical rows plus the 5 new incremental rows. Bronze has duplicate order_ids for 1001 and 1004, and that's fine. The raw layer keeps everything.

Seeing 0 rows loaded?

Snowflake tracks which files have already been loaded and skips them by default. If you previously ran this COPY INTO and it succeeded, Snowflake won't reload the same file. Run the command with FORCE = TRUE appended to override this behavior.

Also confirm your file is in S3 by running LIST @orders_stage; and checking that orders_incremental.csv appears in the results.

Help me troubleshoot my COPY INTO command in Snowflake.

INSERT the new rows into Silver

Bronze has the new data. Now you need to get it into Silver so your clean layer stays current.

• Run this INSERT to push the incremental rows from Bronze into Silver with type casting and quality filters:

-- Naive insert into Silver (DO NOT DO THIS in production)
USE SCHEMA ORDERS_PIPELINE.SILVER;

INSERT INTO silver_orders
SELECT
  TRY_CAST(b.order_id AS NUMBER),
  b.customer_id,
  TRY_CAST(b.amount AS NUMBER(10,2)),
  UPPER(TRIM(b.status)),
  TRY_CAST(b.order_date AS DATE),
  b.loaded_at
FROM ORDERS_PIPELINE.BRONZE.bronze_orders b
WHERE b.source_file LIKE '%incremental%'
  AND TRY_CAST(b.order_id AS NUMBER) IS NOT NULL
  AND TRY_CAST(b.amount AS NUMBER(10,2)) > 0;

What does this code do?

• It selects only rows from the incremental file using the source_file column you tracked in Bronze.
• It applies the same type casts and quality filters as the original Silver build (filtering NULL order_ids and negative amounts).
• It INSERTs the results directly into the existing silver_orders table.

The INSERT should report 4 rows inserted. Order 1013 with its negative amount of -50.00 was filtered out by your quality check.

Expose the duplicate problem

Silver now has 13 rows: the original 9 plus 4 new ones. Before you move on, think about what's in those 4 rows. Two of them (order_ids 1001 and 1004) already existed in Silver with a status of PENDING. The incremental file has them again with a status of SHIPPED.

What do you think you'll see if you group by order_id and look for duplicates?

• Run this query to check for duplicate order_ids in Silver:

-- Expose the problem: duplicates!
SELECT order_id, COUNT(*) AS cnt
FROM silver_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

You'll see order_ids 1001 and 1004, each appearing twice. One row says PENDING, the other says SHIPPED. This is the problem. Silver is supposed to be your single source of truth, but it now has conflicting records for the same orders.

Why is this a problem?

If you just INSERT new rows, you have two options. You can INSERT everything and get duplicates (which you just saw). Or you can rebuild Silver from scratch every time, which loses the incremental pattern and gets expensive at scale.

What you really need is an operation that says: "if this order already exists, update it. If it's new, insert it." That's exactly what you'll build in the next step.

✔️ Awesome, I've got everything!

Great. You've confirmed the duplicate problem in Silver. Make sure you've saved your worksheet.

ⓧ I'd like to double check the full code

Here's everything you ran in this step as a single script:

USE SCHEMA ORDERS_PIPELINE.BRONZE;

-- Load incremental file into Bronze
COPY INTO bronze_orders (order_id, customer_id, amount, status, order_date, source_file)
FROM (
  SELECT $1, $2, $3, $4, $5, METADATA$FILENAME
  FROM @orders_stage/orders_incremental.csv
);

-- Bronze now has 15 rows (duplicates expected in raw layer)
SELECT COUNT(*) FROM bronze_orders;

-- Naive insert into Silver (DO NOT DO THIS in production)
USE SCHEMA ORDERS_PIPELINE.SILVER;

INSERT INTO silver_orders
SELECT
  TRY_CAST(b.order_id AS NUMBER),
  b.customer_id,
  TRY_CAST(b.amount AS NUMBER(10,2)),
  UPPER(TRIM(b.status)),
  TRY_CAST(b.order_date AS DATE),
  b.loaded_at
FROM ORDERS_PIPELINE.BRONZE.bronze_orders b
WHERE b.source_file LIKE '%incremental%'
  AND TRY_CAST(b.order_id AS NUMBER) IS NOT NULL
  AND TRY_CAST(b.amount AS NUMBER(10,2)) > 0;

-- Expose the problem: duplicates!
SELECT order_id, COUNT(*) AS cnt
FROM silver_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

You've hit the exact wall that every data engineer faces: naive INSERTs create duplicates, and rebuilding from scratch doesn't scale. Next up, you'll solve this with a single atomic operation that handles both updates and inserts cleanly.

Fix Silver with MERGE and Build Gold

In the last step, you saw the duplicate problem firsthand. Orders 1001 and 1004 appear twice in Silver with conflicting statuses.

The fix is the MERGE statement combined with ROW_NUMBER deduplication. This is the industry-standard pattern for idempotent incremental loads. It updates existing records when a newer version arrives and inserts truly new records, all in one atomic operation.

In this step, get ready to:

• Recreate Silver with a proper typed schema and load it using MERGE.
• Verify that duplicates are eliminated and updated statuses are reflected.
• Build the Gold aggregation layer for daily revenue metrics.

Drop and recreate Silver with a proper schema

The current silver_orders table was created by a naive INSERT that left duplicates behind. You need to start fresh with an explicitly typed schema.

• Switch to the Silver schema and create the new table by running this statement in your Snowflake worksheet:

USE SCHEMA ORDERS_PIPELINE.SILVER;

-- Drop and recreate Silver with proper schema
CREATE OR REPLACE TABLE silver_orders (
  order_id    NUMBER,
  customer_id VARCHAR,
  amount      NUMBER(10,2),
  status      VARCHAR,
  order_date  DATE,
  loaded_at   TIMESTAMP_NTZ
);

What does this do?

CREATE OR REPLACE TABLE drops the existing silver_orders (with its duplicates) and creates a clean table with explicit data types.

Every column now has the correct type enforced at the table level. order_id is a NUMBER, amount is NUMBER(10,2), and order_date is a proper DATE.

• Confirm the table was created by running:

SELECT COUNT(*) FROM silver_orders;

You should see 0 rows. The table exists but is empty, ready for the MERGE.

Run the MERGE statement to deduplicate and load

The MERGE statement compares a source dataset against a target table. It decides row-by-row whether to UPDATE an existing record or INSERT a new one.

• Run this MERGE statement to load deduplicated data from Bronze into Silver:

-- MERGE: the production pattern for idempotent incremental loads
MERGE INTO silver_orders AS target
USING (
  -- Deduplicate Bronze: keep only the latest version of each order
  SELECT
    TRY_CAST(order_id AS NUMBER)       AS order_id,
    customer_id,
    TRY_CAST(amount AS NUMBER(10,2))   AS amount,
    UPPER(TRIM(status))                AS status,
    TRY_CAST(order_date AS DATE)       AS order_date,
    loaded_at
  FROM ORDERS_PIPELINE.BRONZE.bronze_orders
  WHERE TRY_CAST(order_id AS NUMBER) IS NOT NULL
    AND TRY_CAST(amount AS NUMBER(10,2)) > 0
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY loaded_at DESC
  ) = 1
) AS source
ON target.order_id = source.order_id

WHEN MATCHED AND source.loaded_at > target.loaded_at THEN
  UPDATE SET
    target.customer_id = source.customer_id,
    target.amount      = source.amount,
    target.status      = source.status,
    target.order_date  = source.order_date,
    target.loaded_at   = source.loaded_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status, order_date, loaded_at)
  VALUES (source.order_id, source.customer_id, source.amount, source.status, source.order_date, source.loaded_at);

What does this MERGE do?

The USING subquery reads all of Bronze, filters out invalid rows (NULL order_id, non-positive amount), then uses QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) = 1 to keep only the most recent version of each order.

QUALIFY is a Snowflake-specific clause that filters rows after window functions run. It replaces the need for a nested subquery.

The ON clause joins source to target by order_id. WHEN MATCHED updates the row if the source is newer. WHEN NOT MATCHED inserts it as a new row. Because the target is currently empty, every row hits the INSERT path this first time.

Before you verify the count, predict: how many unique valid orders do you think made it into Silver? Remember that order 1009 has a NULL amount and order 1013 has a negative amount.

• Check the row count by running:

-- Verify: no duplicates, updated statuses
SELECT COUNT(*) FROM silver_orders;

You should see 11 rows. From the 13 unique order_ids across both files in Bronze, 2 were filtered: order 1009 (NULL amount) and order 1013 (negative amount). The remaining 11 orders each appear exactly once.

• Confirm the updated statuses by running:

SELECT order_id, status FROM silver_orders WHERE order_id IN (1001, 1004);

Both orders should show SHIPPED. The MERGE picked the latest version from the incremental file, replacing the old PENDING status.

Why is this pattern called idempotent?

You can run this MERGE as many times as you want and the result is always the same. It does not create duplicates on repeated runs.

If no new data arrives in Bronze, the MERGE matches every row and skips updates because source.loaded_at > target.loaded_at evaluates to false.

Seeing an unexpected row count?

Make sure you ran the CREATE OR REPLACE TABLE statement before the MERGE. If you ran only the MERGE against the old table (with duplicates still in it), the ON clause would have matched multiple rows per order_id.

Re-run the CREATE OR REPLACE TABLE statement from the previous chunk, then re-run the MERGE.

Help me debug my MERGE statement

• Run one final check to confirm there are no duplicates:

SELECT order_id, COUNT(*) AS cnt
FROM silver_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

This should return zero rows. The duplicate problem is solved.

Build the Gold aggregation layer

With Silver clean and deduplicated, you can now build the Gold layer. Gold tables contain business-ready aggregates that analysts and dashboards consume directly.

• Switch to the Gold schema and create the daily revenue summary by running:

USE SCHEMA ORDERS_PIPELINE.GOLD;

-- Gold: daily revenue summary
CREATE OR REPLACE TABLE daily_order_summary AS
SELECT
  order_date,
  status,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_revenue,
  AVG(amount)     AS avg_order_value
FROM ORDERS_PIPELINE.SILVER.silver_orders
GROUP BY order_date, status
ORDER BY order_date, status;

What does this Gold table provide?

The daily_order_summary table groups Silver data by order_date and status. Each row gives you the count of orders, total revenue, and average order value for that day-status combination.

This is the table you would point a BI dashboard at. Analysts never touch Bronze or Silver directly.

• Verify the Gold layer by running:

-- Verify
SELECT * FROM daily_order_summary;

You should see rows grouped by date and status, with columns for order_count, total_revenue, and avg_order_value.

Gold table is empty?

Make sure the MERGE into Silver completed successfully first. The Gold table reads from ORDERS_PIPELINE.SILVER.silver_orders. If Silver is empty, Gold will be too.

Run SELECT COUNT(*) FROM ORDERS_PIPELINE.SILVER.silver_orders; to confirm Silver has data before recreating Gold.

Help me troubleshoot my Gold layer

✔️ Awesome, I've got everything!

Great. Make sure you have saved your SQL in a file called silver_merge.sql and gold.sql for reference.

ⓧ I'd like to double check the full code

Here is the complete silver_merge.sql. Confirm each section is present in your worksheet:

USE SCHEMA ORDERS_PIPELINE.SILVER;

-- Drop and recreate Silver with proper schema
CREATE OR REPLACE TABLE silver_orders (
  order_id    NUMBER,
  customer_id VARCHAR,
  amount      NUMBER(10,2),
  status      VARCHAR,
  order_date  DATE,
  loaded_at   TIMESTAMP_NTZ
);

-- MERGE: the production pattern for idempotent incremental loads
MERGE INTO silver_orders AS target
USING (
  -- Deduplicate Bronze: keep only the latest version of each order
  SELECT
    TRY_CAST(order_id AS NUMBER)       AS order_id,
    customer_id,
    TRY_CAST(amount AS NUMBER(10,2))   AS amount,
    UPPER(TRIM(status))                AS status,
    TRY_CAST(order_date AS DATE)       AS order_date,
    loaded_at
  FROM ORDERS_PIPELINE.BRONZE.bronze_orders
  WHERE TRY_CAST(order_id AS NUMBER) IS NOT NULL
    AND TRY_CAST(amount AS NUMBER(10,2)) > 0
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY loaded_at DESC
  ) = 1
) AS source
ON target.order_id = source.order_id

WHEN MATCHED AND source.loaded_at > target.loaded_at THEN
  UPDATE SET
    target.customer_id = source.customer_id,
    target.amount      = source.amount,
    target.status      = source.status,
    target.order_date  = source.order_date,
    target.loaded_at   = source.loaded_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status, order_date, loaded_at)
  VALUES (source.order_id, source.customer_id, source.amount, source.status, source.order_date, source.loaded_at);

-- Verify: no duplicates, updated statuses
SELECT COUNT(*) FROM silver_orders;

SELECT order_id, status FROM silver_orders WHERE order_id IN (1001, 1004);

And here is the complete gold.sql:

USE SCHEMA ORDERS_PIPELINE.GOLD;

-- Gold: daily revenue summary
CREATE OR REPLACE TABLE daily_order_summary AS
SELECT
  order_date,
  status,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_revenue,
  AVG(amount)     AS avg_order_value
FROM ORDERS_PIPELINE.SILVER.silver_orders
GROUP BY order_date, status
ORDER BY order_date, status;

-- Verify
SELECT * FROM daily_order_summary;

Your medallion pipeline is complete. Data flows from raw CSV in S3 through Bronze (raw append-only), Silver (clean and deduplicated via MERGE), and Gold (business-ready aggregates). Next up, the Secret Mission shows you how to automate the Bronze-to-Silver flow with Snowflake Streams and Tasks.

Secret mission

Automate with Streams and Tasks

In production, nobody manually runs MERGE statements. Can you wire up Snowflake Streams and Tasks to create a fully automated Bronze-to-Silver pipeline that processes new data the moment it arrives?

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. The Snowflake warehouse costs money while it is running, but auto-suspends after 60 seconds of inactivity.

Cost warning

Your ORDERS_WH warehouse (XS size) costs approximately $2/hour when actively running queries. It auto-suspends after 1 minute of inactivity, so you are only charged for seconds of actual compute.

If you completed the Secret Mission, your silver_merge_task can resume the warehouse every minute when new data arrives. Make sure the task is suspended if you are not actively using it.

Resources you used:

• Snowflake: ORDERS_PIPELINE database (contains BRONZE, SILVER, GOLD schemas with all tables, stages, file formats, streams, and tasks).
• Snowflake: ORDERS_WH warehouse (XSMALL size).
• AWS S3: CSV files under your bucket's medallion/ prefix.
• Local: orders_historical.csv and orders_incremental.csv files on your computer.

Keep everything running

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

• Your warehouse auto-suspends after 60 seconds of inactivity, so it will not cost anything while idle.
• Confirm the task is suspended by running SHOW TASKS; in your Snowflake worksheet. The state column should show suspended.
• Your S3 files remain in place for future experimentation.

Pause - I'll come back to this later

Shut down running compute to avoid any charges while you are away. Your data and objects stay intact.

• Suspend the warehouse by running this in your Snowflake worksheet:

ALTER WAREHOUSE ORDERS_WH SUSPEND;

• Suspend the task (if you completed the Secret Mission) by running:

ALTER TASK ORDERS_PIPELINE.BRONZE.silver_merge_task SUSPEND;

• When you come back, resume the warehouse with ALTER WAREHOUSE ORDERS_WH RESUME; and pick up where you left off.

Delete - I don't want to use this again

Remove all Snowflake objects and S3 files to start completely fresh.

• Drop the entire database (removes all schemas, tables, stages, file formats, streams, and tasks inside it) by running:

DROP DATABASE ORDERS_PIPELINE;

• Drop the warehouse by running:

DROP WAREHOUSE ORDERS_WH;

• Delete the S3 files by running this in your local terminal:

aws s3 rm s3://[[BUCKET_NAME="your-bucket-name"]]/medallion/ --recursive

• Delete the local CSV files (orders_historical.csv and orders_incremental.csv) from wherever you saved them on your computer.

Nice Work!

Nice Work!

You just built a production-ready medallion data pipeline in Snowflake that ingests raw order data from S3, deduplicates and cleans it, and produces business-ready revenue metrics.

You've learned how to:

• Build a 3-layer Medallion Architecture (Bronze, Silver, Gold) in Snowflake that progressively refines raw order data from S3 into dashboard-ready aggregates.
• Solve the duplicate and late-arriving record problem using MERGE with ROW_NUMBER deduplication for idempotent incremental loads that handle both backfill and real-time ingestion.
• Produce a Gold aggregation layer with daily revenue summaries by status, ready for business dashboards and reporting.
• Secret Mission: Automated the entire Bronze-to-Silver pipeline with Snowflake Streams and Tasks for hands-free incremental processing.

Ready to quiz yourself?