ELT and Medallion Architecture in Snowflake

ELT Means Load First, Transform Inside Snowflake

ELT stands for Extract, Load, Transform. Data is extracted from source systems, loaded into the warehouse, and transformed after it lands. This is different from older ETL patterns where data is heavily transformed before it enters the warehouse. Snowflake is built for ELT because it can store raw data cheaply, scale compute separately, and run large SQL transformations inside the platform.

Medallion architecture is a practical way to organize ELT. It separates your warehouse into layers: Raw, Silver, and Gold. Raw keeps source-shaped data. Silver cleans and standardizes it. Gold turns it into business-ready facts, dimensions, and marts. The names matter less than the discipline: each layer has a different trust level and purpose.

Sources
  application databases
  SaaS tools
  JSON files
  event streams
  partner exports
      |
      v
RAW schema
  source-shaped, replayable, lightly touched
      |
      v
SILVER schema
  typed, cleaned, deduped, standardized
      |
      v
GOLD schema
  facts, dimensions, marts, dashboards, data products

Layering Prevents Warehouse Chaos

Without layers, every team creates its own "clean" table. Finance has one revenue query. Product has another. Marketing has a third. Nobody knows which table is official. When a number is wrong, the team spends hours tracing copied SQL through dashboards and scratch schemas. Medallion architecture gives the warehouse a path from messy input to trusted output.

Raw Stores Source-Shaped Data With Load Metadata

The Raw layer should preserve what arrived from the source with minimal transformation. You may add technical metadata such as source file, loaded_at, batch_id, record hash, or connector timestamp. You should avoid heavy business logic here. Raw is your evidence layer. If a downstream model breaks, Raw lets you replay and investigate.

CREATE SCHEMA IF NOT EXISTS RETAIL.RAW;

CREATE OR REPLACE TABLE RETAIL.RAW.ORDERS (
  order_id STRING,
  customer_id STRING,
  order_ts STRING,
  status STRING,
  total_usd STRING,
  source_file STRING,
  source_row_number NUMBER,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  batch_id STRING
);

Silver Turns Source Data Into Reliable Entities

Silver is where technical cleaning happens. Types become real types. Names become consistent. Duplicate source records are resolved. Required keys are checked. Timestamps are normalized. Deleted records and late updates are handled. Silver should still be close to real-world entities: orders, customers, products, payments, shipments, tickets, sessions.

CREATE SCHEMA IF NOT EXISTS RETAIL.SILVER;

CREATE OR REPLACE TABLE RETAIL.SILVER.ORDERS AS
SELECT
  order_id::STRING AS order_id,
  customer_id::STRING AS customer_id,
  TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
  LOWER(status)::STRING AS status,
  TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd,
  source_file,
  source_row_number,
  loaded_at,
  batch_id
FROM RETAIL.RAW.ORDERS
WHERE order_id IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY loaded_at DESC, source_file DESC, source_row_number DESC
) = 1;

• ✓Cast important fields into stable Snowflake types.
• ✓Standardize names such as customer_id instead of CustomerID, custId, and CUSTOMER_ID in different tables.
• ✓Deduplicate using a deterministic business rule.
• ✓Keep source metadata so Silver records remain traceable.
• ✓Do not bury final business metrics here; save those for Gold.

Gold Publishes Business-Ready Marts

Gold is for business consumption. It contains facts, dimensions, aggregates, and marts designed for specific decision-making workflows. A finance mart might define recognized revenue. A product mart might define active users. A support mart might define first-response time. Gold should have clear definitions, owners, tests, and access controls.

CREATE SCHEMA IF NOT EXISTS RETAIL.GOLD;

CREATE OR REPLACE TABLE RETAIL.GOLD.DAILY_REVENUE AS
SELECT
  DATE(order_ts) AS order_date,
  COUNT(*) AS order_count,
  COUNT(DISTINCT customer_id) AS customer_count,
  SUM(total_usd) AS gross_revenue_usd,
  AVG(total_usd) AS avg_order_value_usd
FROM RETAIL.SILVER.ORDERS
WHERE status NOT IN ('cancelled', 'fraud')
GROUP BY 1;

Gold Often Uses Facts and Dimensions

A fact table records measurable events: orders, payments, shipments, page views, support tickets. A dimension table describes entities: customers, products, stores, dates, sales reps. This pattern makes analytics easier because metrics and descriptive attributes are separated but joinable.

CREATE OR REPLACE TABLE RETAIL.GOLD.DIM_CUSTOMERS AS
SELECT
  customer_id,
  MIN(order_ts) AS first_order_ts,
  MAX(order_ts) AS latest_order_ts,
  COUNT(*) AS lifetime_orders,
  SUM(total_usd) AS lifetime_revenue_usd
FROM RETAIL.SILVER.ORDERS
GROUP BY customer_id;

CREATE OR REPLACE TABLE RETAIL.GOLD.FCT_ORDERS AS
SELECT
  order_id,
  customer_id,
  DATE(order_ts) AS order_date,
  status,
  total_usd
FROM RETAIL.SILVER.ORDERS;

dbt Fits Naturally With Medallion Modeling

You can build medallion layers with plain SQL, tasks, stored procedures, or orchestration tools. Many Snowflake teams use dbt because it organizes SQL transformations as version-controlled models with dependencies, tests, documentation, and environment-aware deployments.

models/
  staging/
    sources.yml
    stg_orders.sql
    stg_customers.sql
  intermediate/
    int_orders_enriched.sql
    int_customer_first_order.sql
  marts/
    finance/
      fct_orders.sql
      daily_revenue.sql
    product/
      product_sales_mart.sql

-- models/staging/stg_orders.sql
SELECT
  order_id::STRING AS order_id,
  customer_id::STRING AS customer_id,
  TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
  LOWER(status) AS status,
  TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd
FROM {{ source('raw', 'orders') }}

-- models/marts/finance/daily_revenue.sql
SELECT
  DATE(order_ts) AS order_date,
  SUM(total_usd) AS revenue_usd
FROM {{ ref('stg_orders') }}
WHERE status NOT IN ('cancelled', 'fraud')
GROUP BY 1

View, Table, or Incremental Model?

Not every transformation should become a physical table. Not every transformation should stay a view. Materialization is a tradeoff between freshness, cost, query speed, storage, and complexity. In Snowflake, this decision directly affects compute usage and user experience.

Tests Are Part of the Architecture

A medallion pipeline without tests is just a sequence of SQL statements. Tests define the promises each layer makes. Raw tests ask whether data arrived. Silver tests ask whether entities are valid. Gold tests ask whether metrics are trustworthy.

-- Silver: order_id should be unique and non-null.
SELECT order_id, COUNT(*) AS row_count
FROM RETAIL.SILVER.ORDERS
GROUP BY order_id
HAVING order_id IS NULL OR COUNT(*) > 1;

-- Silver: totals should be valid.
SELECT *
FROM RETAIL.SILVER.ORDERS
WHERE total_usd IS NULL OR total_usd < 0;

-- Gold: revenue should reconcile with Silver for the same business rule.
WITH silver_revenue AS (
  SELECT DATE(order_ts) AS order_date, SUM(total_usd) AS revenue_usd
  FROM RETAIL.SILVER.ORDERS
  WHERE status NOT IN ('cancelled', 'fraud')
  GROUP BY 1
)
SELECT g.order_date, g.gross_revenue_usd, s.revenue_usd
FROM RETAIL.GOLD.DAILY_REVENUE g
JOIN silver_revenue s USING (order_date)
WHERE ABS(g.gross_revenue_usd - s.revenue_usd) > 0.01;

Lineage Explains Where a Number Came From

Lineage is the chain from source to final output. When someone asks why revenue changed, lineage tells you which raw files, Silver models, Gold marts, SQL definitions, and dashboard filters were involved. Without lineage, every metric dispute becomes a search party.

GOLD.DAILY_REVENUE
  depends on SILVER.ORDERS
    depends on RAW.ORDERS
      loaded from @ORDERS_STAGE
        copied from s3://company-orders/prod/orders/YYYY/MM/DD/

Definition:
  revenue includes orders where status NOT IN ('cancelled', 'fraud')
  revenue uses total_usd after source-system discount calculation
  order_date is DATE(order_ts) in UTC

• ✓Every Gold metric should point back to its source tables and business rule.
• ✓dbt ref/source graphs help automate lineage for SQL models.
• ✓Load metadata connects modeled rows to source files or batches.
• ✓Query history and access history provide operational lineage during incidents.
• ✓Lineage should be understandable to humans, not only tools.

Every Gold Table Needs an Owner

A table without an owner becomes a rumor. Someone uses it, nobody knows if it is correct, and nobody feels responsible when it breaks. Ownership is not just a Snowflake OWNERSHIP privilege. It is a product responsibility: definition, quality, access, cost, freshness, and communication.

-- Example documentation fields to keep in dbt docs, a catalog, or a metadata table:
object_name: RETAIL.GOLD.DAILY_REVENUE
technical_owner: analytics-engineering@company.com
business_owner: finance-analytics@company.com
freshness_sla: available by 7:00 AM America/New_York
grain: one row per order_date
primary_consumers: executive revenue dashboard, finance monthly close
definition: excludes cancelled and fraud orders

Freshness Is a Business Promise

A table can be perfectly modeled and still useless if it arrives too late. Freshness should be stated in business terms: the executive dashboard must be ready by 7:00 AM Eastern, customer support metrics must update every 15 minutes, finance close tables must be final by the third business day.

CREATE OR REPLACE TABLE OPS.DATASET_FRESHNESS (
  dataset_name STRING,
  expected_by STRING,
  latest_data_ts TIMESTAMP_NTZ,
  checked_at TIMESTAMP_NTZ,
  status STRING
);

INSERT INTO OPS.DATASET_FRESHNESS
SELECT
  'RETAIL.GOLD.DAILY_REVENUE',
  '07:00 America/New_York',
  MAX(order_date)::TIMESTAMP_NTZ,
  CURRENT_TIMESTAMP(),
  CASE
    WHEN MAX(order_date) >= CURRENT_DATE() - 1 THEN 'OK'
    ELSE 'STALE'
  END
FROM RETAIL.GOLD.DAILY_REVENUE;

Not Every Model Should Rebuild From Scratch

Full refresh is simple: rebuild the whole target table. Incremental processing is efficient: process only new or changed records. The right choice depends on data volume, source behavior, correction patterns, and how expensive the transformation is.

Use Layers as Access Boundaries

Medallion layers are not only logical modeling layers. They are also useful security boundaries. Raw often contains sensitive fields and source-system mess. Silver may still have detailed customer data. Gold should expose approved, governed, documented outputs to broad users.

-- Broad users get Gold, not Raw.
GRANT USAGE ON DATABASE RETAIL TO ROLE ANALYST_READER;
GRANT USAGE ON SCHEMA RETAIL.GOLD TO ROLE ANALYST_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA RETAIL.GOLD TO ROLE ANALYST_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA RETAIL.GOLD TO ROLE ANALYST_READER;

-- Transformation role gets Raw and modeled schemas.
GRANT USAGE ON SCHEMA RETAIL.RAW TO ROLE DBT_TRANSFORMER;
GRANT SELECT ON ALL TABLES IN SCHEMA RETAIL.RAW TO ROLE DBT_TRANSFORMER;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA RETAIL.SILVER TO ROLE DBT_TRANSFORMER;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA RETAIL.GOLD TO ROLE DBT_TRANSFORMER;

Medallion Design Affects Performance and Cost

Layering can save money or waste money depending on how it is implemented. If every model rebuilds every row every hour, cost explodes. If every dashboard repeats expensive raw parsing, cost explodes. Good medallion design precomputes stable business outputs, avoids repeated heavy work, and uses warehouses sized for the workload.

• ✓Use Raw for storage and replay, not for every dashboard query.
• ✓Use Silver to avoid repeating type casts, dedupe logic, and JSON parsing.
• ✓Use Gold to avoid repeating business metric logic in every dashboard.
• ✓Use separate warehouses for load, transform, BI, and ad hoc workloads when needed.
• ✓Use Query Profile and warehouse metering to find repeated expensive transformations.

SELECT
  warehouse_name,
  user_name,
  query_text,
  total_elapsed_time / 1000 AS seconds_elapsed,
  bytes_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND query_text ILIKE '%RAW.%'
  AND bytes_scanned > 1000000000
ORDER BY bytes_scanned DESC
LIMIT 20;

Common Medallion Anti-Patterns

Hands-On Lab: Build Orders Raw, Silver, and Gold

This lab is the minimum practical medallion project. You will create a raw table, clean it into Silver, publish a Gold mart, and write checks that prove the pipeline is usable.

CREATE OR REPLACE DATABASE MEDALLION_LAB;
CREATE OR REPLACE SCHEMA MEDALLION_LAB.RAW;
CREATE OR REPLACE SCHEMA MEDALLION_LAB.SILVER;
CREATE OR REPLACE SCHEMA MEDALLION_LAB.GOLD;

CREATE OR REPLACE TABLE MEDALLION_LAB.RAW.ORDERS (
  order_id STRING,
  customer_id STRING,
  order_ts STRING,
  status STRING,
  total_usd STRING,
  loaded_at TIMESTAMP_NTZ
);

INSERT INTO MEDALLION_LAB.RAW.ORDERS VALUES
  ('O-1', 'C-1', '2026-09-01 10:00:00', 'completed', '100.00', CURRENT_TIMESTAMP()),
  ('O-2', 'C-2', '2026-09-01 11:00:00', 'cancelled', '50.00', CURRENT_TIMESTAMP()),
  ('O-1', 'C-1', '2026-09-01 10:05:00', 'completed', '100.00', DATEADD(minute, 1, CURRENT_TIMESTAMP())),
  ('O-3', 'C-1', 'bad timestamp', 'completed', '25.00', CURRENT_TIMESTAMP());

CREATE OR REPLACE TABLE MEDALLION_LAB.SILVER.ORDERS AS
SELECT
  order_id,
  customer_id,
  TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
  LOWER(status) AS status,
  TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd,
  loaded_at
FROM MEDALLION_LAB.RAW.ORDERS
WHERE order_id IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY loaded_at DESC
) = 1;

CREATE OR REPLACE TABLE MEDALLION_LAB.GOLD.DAILY_REVENUE AS
SELECT
  DATE(order_ts) AS order_date,
  COUNT(*) AS order_count,
  SUM(total_usd) AS revenue_usd
FROM MEDALLION_LAB.SILVER.ORDERS
WHERE status = 'completed'
  AND order_ts IS NOT NULL
GROUP BY 1;

Lab checks

• ✓Why does O-1 appear only once in Silver?
• ✓Why does O-3 not contribute to Daily Revenue?
• ✓Which query would catch the bad timestamp?
• ✓Should analysts query RAW.ORDERS or GOLD.DAILY_REVENUE?
• ✓What metadata would you add before making this production?

How to Explain ELT and Medallion Architecture in an Interview

A strong answer sounds like this: In Snowflake, I prefer ELT because the warehouse can store raw source data and scale compute for transformations. I organize data into Raw, Silver, and Gold. Raw preserves source-shaped data with load metadata for replay. Silver standardizes types, names, deduplicates records, and creates reliable entities. Gold publishes business-ready marts, facts, dimensions, and metrics with clear owners and tests. I would use dbt or a similar workflow to manage SQL dependencies, tests, documentation, CI/CD, and environments. I would also include security boundaries, freshness monitoring, cost controls, and a backfill plan.

Questions you should answer out loud

• ✓What is the difference between ETL and ELT?
• ✓What belongs in Raw, Silver, and Gold?
• ✓Why should dashboards avoid querying Raw tables?
• ✓How do dbt refs, sources, tests, and docs support medallion architecture?
• ✓When would you choose a view versus a table versus an incremental model?
• ✓How would you monitor freshness and quality for a Gold table?