End-to-End Snowflake Project
A full capstone build: raw ingestion, semi-structured JSON, idempotent Bronze/Silver/Gold pipelines, streams and tasks, masking and row access policies, time travel recovery, clustering, resource monitors, and monitoring — one orders analytics platform synthesizing the whole track.
Quick answer
The capstone builds a complete Snowflake platform: least-privilege roles and isolated warehouses first, then ingestion (COPY INTO or Snowpipe), JSON handling, idempotent bronze/silver/gold pipelines with streams and tasks, masking and row policies, Time Travel recovery, tuning, cost controls and monitoring.
- Set up roles and warehouses before loading any data.
- Choose Snowpipe or batch COPY by how files arrive.
- Every layer must be safe to re-run.
1. Roles + warehouses loader, transformer, analyst; etl_wh, bi_wh
2. Ingestion stage + file format + Snowpipe for orders JSON
3. Bronze → Silver stream + task + MERGE (idempotent)
4. Gold dynamic table for daily revenue
5. Governance masking on email, row policy by region
6. Operations resource monitor, freshness alert, runbookTwenty Modules, One Real Build
Where you are: you have learned Snowflake architecture, SQL basics, roles and security, stages and COPY INTO, semi-structured data with VARIANT and FLATTEN, warehouses and performance, ELT and the medallion architecture, MERGE and idempotency, time travel and zero-copy cloning, Snowpipe, streams and tasks, dynamic tables, data sharing, governance with masking and row access policies, clustering and performance tuning, cost management, dbt on Snowflake, and production operations. This lesson is where all of that stops being separate topics and becomes one system.
You will build a real, working orders analytics platform for a fictional e-commerce company — from raw files landing in cloud storage all the way to governed, monitored, BI-ready Gold marts — naming the specific module and concept behind every step as you go.
The Scenario: Orders Analytics for an E-Commerce Company
The company, "Northwind Retail," receives two kinds of raw data continuously through the day: order header and line-item data as JSON files (one file per batch of orders, produced by the order service), and customer and product reference data as CSV extracts from an operational database, dropped nightly. Both land as files in an S3 bucket before Snowflake ever sees them.
The business asks for three things: a daily revenue dashboard finance can trust, a customer lifetime-value view sales can use, and confidence that customer PII (email, phone) is never exposed to anyone outside of support and compliance roles. All three requirements shape the architecture below.
| Layer | Contents | Refresh pattern | Concepts from earlier lessons |
|---|---|---|---|
| RAW | Untouched JSON order files, CSV customer/product extracts, full audit metadata. | Continuous via Snowpipe + nightly batch COPY. | Stages, file formats, COPY INTO, Snowpipe. |
| SILVER | Deduplicated, typed, flattened, one-row-per-entity tables. | Incremental via stream + task MERGE. | VARIANT/FLATTEN, MERGE idempotency, streams and tasks. |
| GOLD | Business marts: daily revenue, customer LTV, product performance. | Incremental refresh from Silver, streams+tasks or dynamic tables. | ELT/medallion architecture, dynamic tables. |
| Governance layer | Masking policies, row access policies, RBAC roles. | Applied at object creation, enforced on every query. | Roles and security basics, governance module. |
| Ops layer | Resource monitor, alerts, dashboards, runbooks. | Continuous. | Production operations and monitoring. |
S3: raw orders JSON, raw customer/product CSV
|
v
RAW.ORDERS_JSON, RAW.CUSTOMERS_CSV, RAW.PRODUCTS_CSV (Snowpipe + COPY INTO)
|
v (streams track changes)
SILVER.ORDERS, SILVER.ORDER_LINES, SILVER.CUSTOMERS, SILVER.PRODUCTS (MERGE, deduped)
|
v (streams + tasks incremental refresh)
GOLD.DAILY_REVENUE, GOLD.CUSTOMER_LTV, GOLD.PRODUCT_PERFORMANCE
|
v
BI tool / analysts, governed by masking + row access policies + RBACBuild Phase 1: Warehouses, Databases, Schemas, and RBAC
Following the least-privilege design from the roles and security basics module, this project does not use one shared admin role. It creates functional roles matched to each stage of the pipeline, and separate warehouses so loading, transformation, and BI querying do not contend for the same compute.
CREATE WAREHOUSE IF NOT EXISTS WH_LOAD_XS
WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE WAREHOUSE IF NOT EXISTS WH_TRANSFORM_S
WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE WAREHOUSE IF NOT EXISTS WH_BI_S
WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;CREATE DATABASE IF NOT EXISTS NORTHWIND_ANALYTICS;
CREATE SCHEMA IF NOT EXISTS NORTHWIND_ANALYTICS.RAW;
CREATE SCHEMA IF NOT EXISTS NORTHWIND_ANALYTICS.SILVER;
CREATE SCHEMA IF NOT EXISTS NORTHWIND_ANALYTICS.GOLD;
CREATE SCHEMA IF NOT EXISTS NORTHWIND_ANALYTICS.OPS;CREATE ROLE IF NOT EXISTS RAW_LOADER;
CREATE ROLE IF NOT EXISTS TRANSFORMER;
CREATE ROLE IF NOT EXISTS ANALYST_READER;
CREATE ROLE IF NOT EXISTS SUPPORT_PII_READER;
GRANT USAGE ON WAREHOUSE WH_LOAD_XS TO ROLE RAW_LOADER;
GRANT USAGE ON WAREHOUSE WH_TRANSFORM_S TO ROLE TRANSFORMER;
GRANT USAGE ON WAREHOUSE WH_BI_S TO ROLE ANALYST_READER;
GRANT USAGE ON DATABASE NORTHWIND_ANALYTICS TO ROLE RAW_LOADER, ROLE TRANSFORMER, ROLE ANALYST_READER, ROLE SUPPORT_PII_READER;
GRANT USAGE, CREATE TABLE ON SCHEMA NORTHWIND_ANALYTICS.RAW TO ROLE RAW_LOADER;
GRANT USAGE, CREATE TABLE ON SCHEMA NORTHWIND_ANALYTICS.SILVER TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA NORTHWIND_ANALYTICS.GOLD TO ROLE ANALYST_READER, ROLE TRANSFORMER;
GRANT SELECT ON ALL TABLES IN SCHEMA NORTHWIND_ANALYTICS.GOLD TO ROLE ANALYST_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA NORTHWIND_ANALYTICS.GOLD TO ROLE ANALYST_READER;
GRANT ROLE RAW_LOADER TO USER SVC_LOADER;
GRANT ROLE TRANSFORMER TO USER SVC_TRANSFORM;
GRANT ROLE ANALYST_READER TO USER DASHBOARD_SVC;
GRANT ROLE SUPPORT_PII_READER TO USER SUPPORT_LEAD;Build Phase 2: Stages, File Formats, and COPY INTO for Raw Data
Raw ingestion always starts the same way: define a file format that matches the source exactly, an external stage pointing at the S3 location, and a RAW table with a metadata column that records where and when each row came from — the audit trail that makes debugging a bad load possible later.
CREATE OR REPLACE FILE FORMAT NORTHWIND_ANALYTICS.RAW.JSON_FORMAT
TYPE = JSON
STRIP_OUTER_ARRAY = TRUE;
CREATE OR REPLACE FILE FORMAT NORTHWIND_ANALYTICS.RAW.CSV_FORMAT
TYPE = CSV
FIELD_DELIMITER = ','
SKIP_HEADER = 1
NULL_IF = ('', 'NULL')
EMPTY_FIELD_AS_NULL = TRUE;
CREATE OR REPLACE STAGE NORTHWIND_ANALYTICS.RAW.ORDERS_STAGE
URL = 's3://northwind-raw/orders/'
STORAGE_INTEGRATION = northwind_s3_integration
FILE_FORMAT = NORTHWIND_ANALYTICS.RAW.JSON_FORMAT;
CREATE OR REPLACE STAGE NORTHWIND_ANALYTICS.RAW.REFERENCE_STAGE
URL = 's3://northwind-raw/reference/'
STORAGE_INTEGRATION = northwind_s3_integration
FILE_FORMAT = NORTHWIND_ANALYTICS.RAW.CSV_FORMAT;CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.RAW.ORDERS_JSON (
raw_payload VARIANT,
file_name STRING,
loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.RAW.CUSTOMERS_CSV (
customer_id STRING,
full_name STRING,
email STRING,
phone STRING,
region STRING,
signup_date DATE,
file_name STRING,
loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
COPY INTO NORTHWIND_ANALYTICS.RAW.ORDERS_JSON (raw_payload, file_name)
FROM (
SELECT $1, METADATA$FILENAME
FROM @NORTHWIND_ANALYTICS.RAW.ORDERS_STAGE
)
FILE_FORMAT = (FORMAT_NAME = NORTHWIND_ANALYTICS.RAW.JSON_FORMAT)
ON_ERROR = 'CONTINUE';
COPY INTO NORTHWIND_ANALYTICS.RAW.CUSTOMERS_CSV (customer_id, full_name, email, phone, region, signup_date, file_name)
FROM (
SELECT $1, $2, $3, $4, $5, $6, METADATA$FILENAME
FROM @NORTHWIND_ANALYTICS.RAW.REFERENCE_STAGE/customers/
)
FILE_FORMAT = (FORMAT_NAME = NORTHWIND_ANALYTICS.RAW.CSV_FORMAT)
ON_ERROR = 'CONTINUE';Build Phase 3: Snowpipe for Continuous Order Ingestion
Order JSON files arrive throughout the day, not on a fixed schedule. Rather than polling with a scheduled COPY INTO task, this is exactly the case Snowpipe was built for: event-driven, near- continuous loading triggered by new files landing in the stage.
CREATE OR REPLACE PIPE NORTHWIND_ANALYTICS.RAW.ORDERS_PIPE
AUTO_INGEST = TRUE
AS
COPY INTO NORTHWIND_ANALYTICS.RAW.ORDERS_JSON (raw_payload, file_name)
FROM (
SELECT $1, METADATA$FILENAME
FROM @NORTHWIND_ANALYTICS.RAW.ORDERS_STAGE
)
FILE_FORMAT = (FORMAT_NAME = NORTHWIND_ANALYTICS.RAW.JSON_FORMAT)
ON_ERROR = 'CONTINUE';
-- The S3 event notification pointed at this pipe's notification channel
-- triggers loads automatically as files land; no polling task needed.
SELECT SYSTEM$PIPE_STATUS('NORTHWIND_ANALYTICS.RAW.ORDERS_PIPE');Reference data (customers, products) does not need this — it arrives once nightly, so a scheduled task calling COPY INTO on a fixed interval is simpler and easier to reason about than a pipe.
Build Phase 4: Flattening Order Line Items with VARIANT and FLATTEN
Each order JSON payload contains a nested array of line items. RAW stores the entire payload as VARIANT, untouched. Silver is where that nested structure becomes proper relational rows — one row per order header, one row per line item — using dot notation for scalar fields and LATERAL FLATTEN for the nested array.
{
"order_id": "O-10021",
"customer_id": "C-4410",
"order_ts": "2026-09-10T14:22:00Z",
"status": "completed",
"line_items": [
{ "sku": "SKU-100", "qty": 2, "unit_price": 19.99 },
{ "sku": "SKU-207", "qty": 1, "unit_price": 54.50 }
]
}CREATE OR REPLACE VIEW NORTHWIND_ANALYTICS.RAW.ORDERS_PARSED AS
SELECT
raw_payload:order_id::STRING AS order_id,
raw_payload:customer_id::STRING AS customer_id,
raw_payload:order_ts::TIMESTAMP_NTZ AS order_ts,
raw_payload:status::STRING AS status,
raw_payload:line_items AS line_items,
file_name,
loaded_at
FROM NORTHWIND_ANALYTICS.RAW.ORDERS_JSON;
CREATE OR REPLACE VIEW NORTHWIND_ANALYTICS.RAW.ORDER_LINES_PARSED AS
SELECT
o.order_id,
li.value:sku::STRING AS sku,
li.value:qty::NUMBER AS qty,
li.value:unit_price::NUMBER(12,2) AS unit_price,
o.loaded_at
FROM NORTHWIND_ANALYTICS.RAW.ORDERS_PARSED o,
LATERAL FLATTEN(input => o.line_items) li;Build Phase 5: Idempotent Silver Pipeline with Deduplication
Order files can be redelivered, and the same order can appear in more than one file if the source system retries. The Silver MERGE has to be idempotent: running it twice on the same input must not create duplicate rows or double-count revenue. This follows the MERGE idempotency module directly — dedupe the source with QUALIFY ROW_NUMBER() before the match, and use an update condition based on recency so an out-of-order replay cannot overwrite a newer record with an older one.
CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.SILVER.ORDERS (
order_id STRING,
customer_id STRING,
status STRING,
order_ts TIMESTAMP_NTZ,
updated_at TIMESTAMP_NTZ
);
MERGE INTO NORTHWIND_ANALYTICS.SILVER.ORDERS tgt
USING (
SELECT order_id, customer_id, status, order_ts, loaded_at AS updated_at
FROM NORTHWIND_ANALYTICS.RAW.ORDERS_PARSED
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY loaded_at DESC
) = 1
) src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
customer_id = src.customer_id,
status = src.status,
order_ts = src.order_ts,
updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, status, order_ts, updated_at)
VALUES (src.order_id, src.customer_id, src.status, src.order_ts, src.updated_at);CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.SILVER.ORDER_LINES (
order_id STRING,
sku STRING,
qty NUMBER,
unit_price NUMBER(12,2),
updated_at TIMESTAMP_NTZ
);
MERGE INTO NORTHWIND_ANALYTICS.SILVER.ORDER_LINES tgt
USING (
SELECT order_id, sku, qty, unit_price, loaded_at AS updated_at
FROM NORTHWIND_ANALYTICS.RAW.ORDER_LINES_PARSED
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id, sku
ORDER BY loaded_at DESC
) = 1
) src
ON tgt.order_id = src.order_id AND tgt.sku = src.sku
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
qty = src.qty, unit_price = src.unit_price, updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, sku, qty, unit_price, updated_at)
VALUES (src.order_id, src.sku, src.qty, src.unit_price, src.updated_at);| Design choice | Why it is here |
|---|---|
| QUALIFY ROW_NUMBER() dedup on source. | A redelivered file must not create two rows for the same order. |
| updated_at recency check in WHEN MATCHED. | A replayed older file cannot overwrite a newer, already-merged record. |
| Composite key (order_id, sku) for line items. | The natural grain of a line item is order + product, not order alone. |
Build Phase 6: Streams and Tasks Drive Silver-to-Gold Refresh
Rebuilding GOLD.DAILY_REVENUE from scratch on every run would work, but wastes compute as the orders table grows. Following the streams and tasks module, a stream on SILVER.ORDERS tracks what changed, and a task consumes it into Gold on a schedule — gated with WHEN SYSTEM$STREAM_HAS_DATA(...) so it does not spin up a warehouse for nothing.
CREATE OR REPLACE STREAM NORTHWIND_ANALYTICS.SILVER.ORDERS_STREAM
ON TABLE NORTHWIND_ANALYTICS.SILVER.ORDERS;
CREATE OR REPLACE TASK NORTHWIND_ANALYTICS.OPS.REFRESH_GOLD_REVENUE
WAREHOUSE = WH_TRANSFORM_S
SCHEDULE = '15 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('NORTHWIND_ANALYTICS.SILVER.ORDERS_STREAM')
AS
MERGE INTO NORTHWIND_ANALYTICS.GOLD.DAILY_REVENUE tgt
USING (
SELECT
DATE(order_ts) AS order_date,
COUNT(DISTINCT order_id) AS order_count,
SUM(qty * unit_price) AS revenue_usd
FROM NORTHWIND_ANALYTICS.SILVER.ORDERS o
JOIN NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol ON ol.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'fraud')
GROUP BY 1
) src
ON tgt.order_date = src.order_date
WHEN MATCHED THEN UPDATE SET order_count = src.order_count, revenue_usd = src.revenue_usd
WHEN NOT MATCHED THEN INSERT (order_date, order_count, revenue_usd)
VALUES (src.order_date, src.order_count, src.revenue_usd);
ALTER TASK NORTHWIND_ANALYTICS.OPS.REFRESH_GOLD_REVENUE RESUME;Build Phase 7: Protect PII with Masking and Row Access Policies
Customer email and phone must never be visible to a general analyst role, only to support and compliance. Regional sales reps should only ever see orders from their own region. Both rules are enforced once, at the object level, rather than trusted to every downstream query — this is the governance-by-default pattern from the roles and security and governance modules.
CREATE OR REPLACE MASKING POLICY NORTHWIND_ANALYTICS.SILVER.EMAIL_MASK AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SUPPORT_PII_READER', 'COMPLIANCE_ADMIN') THEN val
ELSE REGEXP_REPLACE(val, '^.+(@.+)$', '***\\1')
END;
ALTER TABLE NORTHWIND_ANALYTICS.SILVER.CUSTOMERS
MODIFY COLUMN email SET MASKING POLICY NORTHWIND_ANALYTICS.SILVER.EMAIL_MASK;
CREATE OR REPLACE MASKING POLICY NORTHWIND_ANALYTICS.SILVER.PHONE_MASK AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SUPPORT_PII_READER', 'COMPLIANCE_ADMIN') THEN val
ELSE 'XXX-XXX-' || RIGHT(val, 4)
END;
ALTER TABLE NORTHWIND_ANALYTICS.SILVER.CUSTOMERS
MODIFY COLUMN phone SET MASKING POLICY NORTHWIND_ANALYTICS.SILVER.PHONE_MASK;CREATE OR REPLACE ROW ACCESS POLICY NORTHWIND_ANALYTICS.GOLD.REGION_POLICY
AS (region STRING) RETURNS BOOLEAN ->
CURRENT_ROLE() IN ('COMPLIANCE_ADMIN', 'ANALYST_READER')
OR EXISTS (
SELECT 1 FROM NORTHWIND_ANALYTICS.OPS.SALES_REP_REGIONS r
WHERE r.role_name = CURRENT_ROLE() AND r.region = region
);
ALTER TABLE NORTHWIND_ANALYTICS.GOLD.PRODUCT_PERFORMANCE
ADD ROW ACCESS POLICY NORTHWIND_ANALYTICS.GOLD.REGION_POLICY ON (region);| Policy | Protects against | Who bypasses it |
|---|---|---|
| EMAIL_MASK / PHONE_MASK | Analysts or BI tools seeing raw customer contact info. | SUPPORT_PII_READER, COMPLIANCE_ADMIN. |
| REGION_POLICY | A sales rep for one region seeing another region's figures. | COMPLIANCE_ADMIN, ANALYST_READER (sees all regions). |
Build Phase 8: Prove Time Travel Recovery Actually Works
A recovery plan you have never tested is a guess. Before this pipeline is considered production ready, the team runs a deliberate recovery drill: intentionally corrupt Silver data with a bad MERGE, then use Time Travel to inspect and restore it — the same pattern from the time travel and zero-copy cloning module.
-- Simulate an accidental bad update
UPDATE NORTHWIND_ANALYTICS.SILVER.ORDERS SET status = 'cancelled' WHERE 1=1;
-- Find the query_id right before the bad statement
SELECT query_id, query_text, start_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%UPDATE NORTHWIND_ANALYTICS.SILVER.ORDERS%'
ORDER BY start_time DESC
LIMIT 5;
-- Restore using Time Travel BEFORE the bad statement
CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.SILVER.ORDERS_RECOVERED AS
SELECT * FROM NORTHWIND_ANALYTICS.SILVER.ORDERS
BEFORE (STATEMENT => '<bad_update_query_id>');
-- Validate row counts and a few known order_ids match expectations,
-- then swap the recovered table into place.
ALTER TABLE NORTHWIND_ANALYTICS.SILVER.ORDERS SWAP WITH NORTHWIND_ANALYTICS.SILVER.ORDERS_RECOVERED;CREATE TABLE ORDERS_DRILL CLONE ORDERS and run the corruption/recovery drill on the clone. Zero-copy cloning makes this nearly free in storage cost and removes any risk to production data during the rehearsal.Build Phase 9: Add a Clustering Key as Order Lines Grows
SILVER.ORDER_LINES is the largest table in the platform and grows every day. Most queries against it filter by a date range (via the joined order) or by product SKU. As the table passes the point where natural clustering from insert order stops matching query patterns, a clustering key keeps pruning effective — straight from the performance and clustering module.
-- Check current clustering health before deciding
SELECT SYSTEM$CLUSTERING_INFORMATION('NORTHWIND_ANALYTICS.SILVER.ORDER_LINES', '(order_id)');
ALTER TABLE NORTHWIND_ANALYTICS.SILVER.ORDER_LINES
CLUSTER BY (order_id);
-- Re-check after Snowflake's automatic reclustering has run
SELECT SYSTEM$CLUSTERING_INFORMATION('NORTHWIND_ANALYTICS.SILVER.ORDER_LINES', '(order_id)');Build Phase 10: A Resource Monitor Caps Runaway Spend
Following the cost management module, every warehouse in this project is attached to a resource monitor with a monthly credit quota and staged notifications, so a runaway query or a misconfigured task schedule cannot silently burn an unbounded amount of credit before anyone notices.
CREATE OR REPLACE RESOURCE MONITOR NORTHWIND_MONTHLY_MONITOR
WITH CREDIT_QUOTA = 500
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATE
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND
ON 110 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE WH_LOAD_XS SET RESOURCE_MONITOR = NORTHWIND_MONTHLY_MONITOR;
ALTER WAREHOUSE WH_TRANSFORM_S SET RESOURCE_MONITOR = NORTHWIND_MONTHLY_MONITOR;
ALTER WAREHOUSE WH_BI_S SET RESOURCE_MONITOR = NORTHWIND_MONTHLY_MONITOR;| Threshold | Action | Why staged instead of one cutoff |
|---|---|---|
| 75% | Notify only. | Early warning while there is still time to investigate calmly. |
| 90% | Notify again. | Escalates urgency without breaking anything yet. |
| 100% | SUSPEND — new queries blocked, running queries finish. | Hard stop for new spend while avoiding aborting in-flight work. |
| 110% | SUSPEND_IMMEDIATE — running queries cancelled too. | Last-resort circuit breaker if 100% was somehow bypassed. |
Build Phase 11: Monitor the Whole Pipeline End to End
The final build phase wires up the production operations patterns from the previous lesson: task history monitoring, a freshness alert on the Gold layer, and a small operational dashboard so the on-call rotation can answer "is Northwind Retail's data platform healthy?" without guessing.
CREATE OR REPLACE ALERT NORTHWIND_ANALYTICS.OPS.REVENUE_FRESHNESS_ALERT
WAREHOUSE = WH_LOAD_XS
SCHEDULE = '15 MINUTE'
IF (
EXISTS (
SELECT 1 FROM (
SELECT MAX(order_date) AS latest_date FROM NORTHWIND_ANALYTICS.GOLD.DAILY_REVENUE
) WHERE latest_date < DATEADD('hour', -2, CURRENT_TIMESTAMP())
)
)
THEN
CALL NORTHWIND_ANALYTICS.OPS.NOTIFY_ONCALL('GOLD.DAILY_REVENUE freshness SLO breached');
ALTER ALERT NORTHWIND_ANALYTICS.OPS.REVENUE_FRESHNESS_ALERT RESUME;SELECT
'task_failures_24h' AS metric,
COUNT(*) AS value
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
WHERE state = 'FAILED'
UNION ALL
SELECT 'gold_revenue_hours_behind',
DATEDIFF('hour', MAX(order_date), CURRENT_TIMESTAMP())
FROM NORTHWIND_ANALYTICS.GOLD.DAILY_REVENUE
UNION ALL
SELECT 'load_errors_24h',
COUNT(*)
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'NORTHWIND_ANALYTICS.RAW.ORDERS_JSON',
START_TIME => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
WHERE error_count > 0;The Finished Gold Layer: Three Marts the Business Actually Uses
With Silver clean and governed, Gold is where the business questions get answered directly. These three marts are what finance, sales, and merchandising actually query day to day.
CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.GOLD.CUSTOMER_LTV AS
SELECT
c.customer_id,
c.region,
COUNT(DISTINCT o.order_id) AS lifetime_orders,
SUM(ol.qty * ol.unit_price) AS lifetime_revenue_usd,
MIN(o.order_ts) AS first_order_ts,
MAX(o.order_ts) AS last_order_ts
FROM NORTHWIND_ANALYTICS.SILVER.CUSTOMERS c
JOIN NORTHWIND_ANALYTICS.SILVER.ORDERS o ON o.customer_id = c.customer_id
JOIN NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol ON ol.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'fraud')
GROUP BY 1, 2;CREATE OR REPLACE TABLE NORTHWIND_ANALYTICS.GOLD.PRODUCT_PERFORMANCE AS
SELECT
ol.sku,
c.region,
SUM(ol.qty) AS units_sold,
SUM(ol.qty * ol.unit_price) AS revenue_usd
FROM NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol
JOIN NORTHWIND_ANALYTICS.SILVER.ORDERS o ON o.order_id = ol.order_id
JOIN NORTHWIND_ANALYTICS.SILVER.CUSTOMERS c ON c.customer_id = o.customer_id
WHERE o.status NOT IN ('cancelled', 'fraud')
GROUP BY 1, 2;GOLD.DAILY_REVENUE was already built incrementally in Part 07 via the stream+task MERGE. Together, these three marts are the entire business-facing surface of the platform — everything upstream exists to make these three tables correct, fresh, and safe to query.
Build Phase 12: Prove Silver and Gold Are Actually Correct, Not Just Populated
A pipeline that runs without erroring is not the same as a pipeline that produces correct numbers. Before this build is handed off, it goes through a fixed set of assertions — the same discipline dbt's generic tests (unique, not_null, relationships) encode, written here as plain SQL so the logic is visible even without a dbt project sitting on top of Snowflake. Every assertion returns zero rows when the pipeline is healthy; any row returned is a failed test.
-- FAIL if SILVER.ORDERS has more than one row per order_id
SELECT order_id, COUNT(*) AS row_count
FROM NORTHWIND_ANALYTICS.SILVER.ORDERS
GROUP BY order_id
HAVING COUNT(*) > 1;
-- FAIL if any order is missing a customer_id or order_ts
SELECT order_id
FROM NORTHWIND_ANALYTICS.SILVER.ORDERS
WHERE customer_id IS NULL OR order_ts IS NULL;
-- FAIL if SILVER.ORDER_LINES has a null qty or a non-positive unit_price
SELECT order_id, sku
FROM NORTHWIND_ANALYTICS.SILVER.ORDER_LINES
WHERE qty IS NULL OR unit_price IS NULL OR unit_price <= 0;-- FAIL if an order line references an order that does not exist in SILVER.ORDERS
-- (an orphaned line item, most likely from a partial or out-of-order load)
SELECT ol.order_id
FROM NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol
LEFT JOIN NORTHWIND_ANALYTICS.SILVER.ORDERS o ON o.order_id = ol.order_id
WHERE o.order_id IS NULL;
-- FAIL if GOLD.CUSTOMER_LTV references a customer_id absent from SILVER.CUSTOMERS
SELECT ltv.customer_id
FROM NORTHWIND_ANALYTICS.GOLD.CUSTOMER_LTV ltv
LEFT JOIN NORTHWIND_ANALYTICS.SILVER.CUSTOMERS c ON c.customer_id = ltv.customer_id
WHERE c.customer_id IS NULL;
-- FAIL if GOLD.DAILY_REVENUE's total does not reconcile with a from-scratch
-- recomputation over Silver for the same date range (a drift check, not just a
-- shape check -- this is what catches a MERGE bug that produces plausible but
-- wrong numbers)
SELECT g.order_date, g.revenue_usd AS gold_revenue, s.revenue_usd AS recomputed_revenue
FROM NORTHWIND_ANALYTICS.GOLD.DAILY_REVENUE g
JOIN (
SELECT DATE(o.order_ts) AS order_date, SUM(ol.qty * ol.unit_price) AS revenue_usd
FROM NORTHWIND_ANALYTICS.SILVER.ORDERS o
JOIN NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol ON ol.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'fraud')
GROUP BY 1
) s ON s.order_date = g.order_date
WHERE ABS(g.revenue_usd - s.revenue_usd) > 0.01;| Test category | What it catches | Where it runs |
|---|---|---|
| Uniqueness (one row per key). | A dedup regression in the Silver MERGE — the exact bug the QUALIFY ROW_NUMBER() logic in Phase 5 exists to prevent. | After every Silver MERGE task run. |
| Not-null on required columns. | A parsing bug in the FLATTEN step silently producing incomplete rows instead of failing loudly. | After every Silver MERGE task run. |
| Referential integrity, Silver to Silver. | An order line landing before its parent order header, or an order that never arrived. | After every Silver MERGE task run. |
| Referential integrity, Silver to Gold. | A Gold mart built from stale or partially-refreshed Silver data. | After every Gold refresh task run. |
| Revenue reconciliation (recompute and diff). | A logic bug in the incremental MERGE that produces a number that looks reasonable but is wrong — row-count checks alone would miss this. | Nightly, as a scheduled task, since a full recompute is heavier than the incremental refresh itself. |
CREATE OR REPLACE TASK NORTHWIND_ANALYTICS.OPS.RUN_PIPELINE_TESTS
WAREHOUSE = WH_TRANSFORM_S
AFTER NORTHWIND_ANALYTICS.OPS.REFRESH_GOLD_REVENUE
AS
CALL NORTHWIND_ANALYTICS.OPS.ASSERT_ZERO_ROWS(
'orphaned_order_lines',
'SELECT ol.order_id FROM NORTHWIND_ANALYTICS.SILVER.ORDER_LINES ol
LEFT JOIN NORTHWIND_ANALYTICS.SILVER.ORDERS o ON o.order_id = ol.order_id
WHERE o.order_id IS NULL'
);
-- ASSERT_ZERO_ROWS is a small stored procedure: run the query, and if it
-- returns any rows, insert a failure record into OPS.TEST_FAILURES and call
-- NOTIFY_ONCALL -- the same alerting path Phase 11 already wires up, so a
-- failed data-quality test surfaces exactly like a failed task or a stale mart.AFTER NORTHWIND_ANALYTICS.OPS.REFRESH_GOLD_REVENUE guarantees the tests always run against the Gold data that was just produced, never against a stale prior run or a still-in-progress refresh — the same dependency-graph pattern from the streams and tasks module, applied to testing instead of transformation.Null-rate testing deserves a specific callout beyond a flat not-null check: a column that is "mostly populated" can still hide a creeping regression that a strict not-null assertion misses if the upstream source occasionally sends a legitimate null. Tracking the null rate as a trend, not just a boolean pass/fail, catches that kind of slow drift before it becomes a hard failure.
-- Track the null rate on a column that is allowed some nulls, and alert
-- only if it drifts meaningfully from its historical baseline
SELECT
DATE(order_ts) AS order_date,
COUNT(*) AS total_rows,
COUNT_IF(discount_code IS NULL) AS null_discount_codes,
ROUND(COUNT_IF(discount_code IS NULL) / COUNT(*) * 100, 2) AS null_rate_pct
FROM NORTHWIND_ANALYTICS.SILVER.ORDERS
WHERE order_ts >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 1 DESC;
-- FAIL the test if today's null_rate_pct deviates from the 30-day average
-- by more than a few standard deviations -- a sudden jump usually means an
-- upstream field rename or a broken optional-field mapping, not a business change.| Test cadence | What runs at that cadence | Why this frequency |
|---|---|---|
| Every Silver MERGE run (every few minutes). | Uniqueness, not-null, and Silver-to-Silver referential integrity. | These are cheap, fast checks — catching a dedup regression within minutes instead of discovering it the next morning. |
| Every Gold refresh run (every 15 minutes). | Silver-to-Gold referential integrity. | Matches the Gold refresh cadence, so a mart is never left visibly out of sync with the Silver it was built from. |
| Nightly. | Revenue reconciliation (full recompute and diff) and 30-day null-rate trend checks. | These are heavier queries that scan more history than the incremental pipeline touches; running them nightly balances thoroughness against compute cost. |
Build Phase 13: A Runbook So the Next Person Doesn't Have to Reverse-Engineer This
A pipeline only this lesson's author understands is a pipeline that breaks at 2 a.m. with nobody able to fix it. The last build phase is not code at all — it is the lightweight runbook that ties together every table, every refresh mechanism, and every alert built above into something an on-call engineer who did not build this platform can actually use.
| Table | Refreshed by | Owner | If it looks wrong, check |
|---|---|---|---|
| RAW.ORDERS_JSON | ORDERS_PIPE (Snowpipe, continuous). | Data engineering — ingestion. | SYSTEM$PIPE_STATUS() and COPY_HISTORY for load errors. |
| RAW.CUSTOMERS_CSV / RAW.PRODUCTS_CSV | Nightly scheduled COPY INTO task. | Data engineering — ingestion. | TASK_HISTORY for the nightly load task; confirm the nightly S3 export actually landed. |
| SILVER.ORDERS / SILVER.ORDER_LINES | MERGE off RAW views, run inline with load or on a short schedule. | Data engineering — transformation. | RUN_PIPELINE_TESTS results in OPS.TEST_FAILURES; QUERY_HISTORY for the MERGE statement. |
| GOLD.DAILY_REVENUE | REFRESH_GOLD_REVENUE task, gated on ORDERS_STREAM, every 15 minutes. | Analytics engineering. | REVENUE_FRESHNESS_ALERT status; TASK_HISTORY for REFRESH_GOLD_REVENUE. |
| GOLD.CUSTOMER_LTV / GOLD.PRODUCT_PERFORMANCE | Rebuilt on a scheduled task from Silver (currently full rebuild; candidate for incremental refresh as volume grows). | Analytics engineering. | TASK_HISTORY for the rebuild task; row counts against the prior day's run. |
| Masking / row access policies | Applied once at object creation; not "refreshed." | Security and governance. | Role grants (SUPPORT_PII_READER, COMPLIANCE_ADMIN) and OPS.SALES_REP_REGIONS mapping table. |
1. Is GOLD.DAILY_REVENUE stale?
-> Check REVENUE_FRESHNESS_ALERT status and the pipeline health
dashboard query (Phase 11) for 'gold_revenue_hours_behind'.
2. Did a data-quality test fail overnight?
-> SELECT * FROM NORTHWIND_ANALYTICS.OPS.TEST_FAILURES
WHERE failed_at > DATEADD('day', -1, CURRENT_TIMESTAMP());
3. Did a load fail?
-> Check COPY_HISTORY for RAW.ORDERS_JSON and RAW.CUSTOMERS_CSV,
and SYSTEM$PIPE_STATUS() for ORDERS_PIPE specifically.
4. Did a task stop running entirely (not just fail once)?
-> TASK_HISTORY, filtered to state != 'SUCCEEDED', last 24-48 hours.
-> Confirm the task is actually RESUMEd -- a task left SUSPENDED
after a maintenance window is the single most common false alarm.
5. Still unclear?
-> Escalate to the owning team from the table above, with the
query_id or task name already in hand -- not just "the dashboard
looks wrong."Freshness SLOs, stated as numbers a runbook can be checked against
A runbook that says "the dashboard should be fresh" gives the on-call engineer nothing to compare against. Every mart in this platform has an explicit, written freshness SLO, matched to how the business actually consumes it — the same distinction the requirements-gathering step of a system-design interview would draw out before proposing an architecture.
| Mart | Freshness SLO | Consumer | What breaching the SLO means |
|---|---|---|---|
| GOLD.DAILY_REVENUE | 15 minutes behind the latest merged Silver order. | Finance revenue dashboard, near-real-time. | Finance may be looking at numbers that do not yet reflect the last hour of orders. |
| GOLD.CUSTOMER_LTV | 24 hours (rebuilt once daily). | Sales team, planning and outreach. | A customer's LTV segment used in an outreach list may be one day stale — acceptable for this use case. |
| GOLD.PRODUCT_PERFORMANCE | 24 hours (rebuilt once daily). | Merchandising, weekly and monthly planning. | Not time-sensitive enough to justify incremental refresh; a daily rebuild is intentionally simple. |
Notice that only DAILY_REVENUE gets the tighter 15-minute SLO and the incremental stream+task treatment from Phase 6 — CUSTOMER_LTV and PRODUCT_PERFORMANCE are rebuilt from scratch daily because nothing in their stated business use actually needs sub-day freshness. Matching each mart's refresh investment to its real SLO, rather than making everything "as fresh as possible," is the same trade-off discipline the system-design capstone module argues for when reasoning through an unfamiliar interview prompt.
Ownership, in one line per team
- ✓Data engineering — ingestion: owns Snowpipe, stages, file formats, and the RAW layer; first responder for load failures and pipe status.
- ✓Data engineering — transformation: owns the Silver MERGE logic, streams, and the pipeline test suite; first responder for dedup or referential-integrity test failures.
- ✓Analytics engineering: owns the Gold marts, their refresh schedules, and freshness SLOs; first responder for stale-dashboard reports from the business.
- ✓Security and governance: owns masking policies, row access policies, and role-grant reviews; first responder for access or PII-exposure incidents.
- ✓Platform/on-call rotation: owns the resource monitor thresholds and the pipeline health dashboard itself; routes an incident to the right team above rather than debugging every layer personally.
With the pipeline tested against explicit assertions and documented against explicit ownership and SLOs, "the pipeline is done" stops being a subjective judgment call and becomes a checklist anyone on the team can verify against — which is the actual bar a capstone build like this one is meant to clear before it would be called production-ready.
That checklist, restated plainly: every table has a passing test suite, a named owner, a written freshness SLO, and a documented first-check query. A pipeline that cannot answer all four for every one of its tables is not finished, no matter how correct its SQL looks in isolation.
Five Realistic Failure Scenarios for This Exact Pipeline
| Scenario | What happens without the design decisions above | How this pipeline handles it |
|---|---|---|
| An order file is redelivered by the source system. | A naive INSERT-only pipeline would double the order and double-count revenue. | The Silver MERGE dedupes by order_id with QUALIFY and checks updated_at recency, so a replay is a no-op. |
| A JSON file arrives with a malformed line-items array. | A strict COPY INTO with ON_ERROR = ABORT_STATEMENT would fail the entire batch, blocking every other order that day. | ON_ERROR = CONTINUE plus COPY_HISTORY triage isolates the bad file without blocking the rest of the load. |
| A bad UPDATE accidentally cancels every order in Silver. | With no recovery plan, the team would have to rebuild from raw files manually under time pressure. | Time Travel BEFORE the bad query_id restores the table; the rehearsed drill means the team already knows the exact steps. |
| A support engineer's role grant is accidentally revoked. | They can no longer see customer email/phone to help a customer, and the failure looks like a bug rather than an access issue. | The masking policy condition is centralized on the role name, so restoring the SUPPORT_PII_READER grant is the entire fix — no code change needed. |
| A scheduled task silently stops running for two days. | GOLD.DAILY_REVENUE goes stale and finance makes decisions on old numbers without knowing it. | The REVENUE_FRESHNESS_ALERT fires on staleness directly, independent of whether the task itself reported success or failure. |
| A data-quality test fails but nobody is watching OPS.TEST_FAILURES. | Silent corruption of Gold marts persists for days because the tests ran but nobody looked. | RUN_PIPELINE_TESTS calls NOTIFY_ONCALL on any failure, routing test results into the same alert channel as task failures and freshness breaches. |
| A new engineer inherits this pipeline with no context. | Hours are spent reverse-engineering which table refreshes how, before an incident can even be triaged. | The Phase 13 runbook maps every table to its refresh mechanism, owner, and first-check query. |
How to Walk Through This Project in an Interview
A strong answer walks end to end, naming the reason for each decision rather than just listing SQL: raw JSON and CSV files land in S3 and are loaded via Snowpipe (continuous) and scheduled COPY INTO (batch), parsed with VARIANT and LATERAL FLATTEN for nested line items, merged into Silver with deduplication and recency checks for idempotency, refreshed into Gold marts incrementally via streams and tasks, protected with masking policies on PII and row access policies for regional access, tested for recoverability with Time Travel, tuned with a clustering key once the fact table grew large enough to justify it, capped with a resource monitor, and monitored with freshness alerts and an operational dashboard built on ACCOUNT_USAGE and INFORMATION_SCHEMA. The story is not "I wrote some SQL" — it is "here is why each layer exists and what failure it prevents."
Questions you should be ready to answer about your own version of this build
- ✓Why does the Silver MERGE dedupe with QUALIFY ROW_NUMBER() instead of trusting the source not to send duplicates?
- ✓Why is Snowpipe used for orders but a scheduled task used for reference data?
- ✓What would happen if you removed the WHEN SYSTEM$STREAM_HAS_DATA condition from the Gold refresh task?
- ✓How would you prove your Time Travel recovery plan actually works before you need it in production?
- ✓Why is the masking policy condition based on role name rather than hardcoded usernames?
- ✓What would make you decide SILVER.ORDER_LINES needs a clustering key, versus not?
- ✓If GOLD.DAILY_REVENUE went stale for three hours, which alert should have fired, and would it have used ACCOUNT_USAGE or INFORMATION_SCHEMA?
🎯 Key Takeaways
- ✓A real Snowflake platform is not one pipeline — it is warehouses, roles, ingestion, transformation, governance, performance, cost control, and operations working together.
- ✓Least-privilege roles and workload-isolated warehouses are set up before any data is loaded, not retrofitted afterward.
- ✓Snowpipe fits continuously arriving files; scheduled COPY INTO fits predictable batch files — the right tool depends on arrival pattern, not habit.
- ✓VARIANT and LATERAL FLATTEN turn nested JSON into clean relational rows without losing the raw payload.
- ✓Idempotent MERGE design (dedup + recency checks) is what makes redelivered or replayed data safe.
- ✓Streams and tasks (or dynamic tables) keep Gold marts fresh without rebuilding from scratch on every run.
- ✓Masking policies and row access policies enforce PII and regional protection centrally, at the object, not per query.
- ✓A recovery plan is only real once it has been rehearsed with Time Travel on a cloned table, not just designed on paper.
- ✓Clustering, resource monitors, and monitoring/alerting are what keep a working pipeline reliable and affordable months after launch, not just on day one.
Quick check
What should be set up before any data is loaded?
HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
