Snowflake has become the default warehouse for many data teams, and the engineers who know how it actually works, not just how to write a SELECT against it, are the ones trusted with production pipelines and the monthly bill.
This path goes from Snowflake's architecture to production pipelines: how storage and compute are separated, how data gets in, how semi-structured JSON is queried, how Time Travel and cloning save you, why queries are slow and how to make them cheap, and how to build change-driven pipelines with streams, tasks and dynamic tables, all secured with role-based access and masking. Commands are Snowflake SQL; practice the syntax in Sqlism's Snowflake playground.
Snowflake separates storage (your tables, stored as compressed micro-partitions in cloud object storage) from compute (virtual warehouses that run queries). Many warehouses can read the same data at once without competing, and you pay for compute by the second while a warehouse runs, so sizing and suspending warehouses is an engineering decision.
CREATE WAREHOUSE etl_wh
WAREHOUSE_SIZE = 'SMALL'
AUTO_SUSPEND = 60 -- seconds of inactivity before suspending
AUTO_RESUME = TRUE;
USE WAREHOUSE etl_wh;Files are loaded from a stage, an internal or external (S3, Azure Blob, GCS) location. COPY INTO bulk-loads files and remembers which ones it has already loaded, so re-running it doesn't duplicate data. Snowpipe runs the same load automatically as new files arrive, for near-real-time ingestion.
CREATE STAGE raw_stage
URL = 's3://my-bucket/orders/'
STORAGE_INTEGRATION = s3_int
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"');
COPY INTO raw.orders
FROM @raw_stage
ON_ERROR = 'CONTINUE'; -- skip bad rows, keep loadingSnowflake stores JSON, Avro and Parquet in a VARIANT column and lets you query it with path notation (payload:customer.id) and cast with ::. LATERAL FLATTEN turns a nested array into rows, which is how you unpack order lines from an order JSON document.
SELECT r.payload:order_id::NUMBER AS order_id,
r.payload:customer.email::STRING AS email,
item.value:sku::STRING AS sku,
item.value:qty::NUMBER AS quantity
FROM raw.order_events r,
LATERAL FLATTEN(input => r.payload:items) item;Time Travel lets you query, clone or restore data as it was at an earlier point within the retention period. Zero-copy cloning creates a full copy of a table, schema or database in seconds without duplicating storage, which is perfect for test environments and "before the deploy" backups.
-- What did the table look like an hour ago?
SELECT * FROM sales.orders AT (OFFSET => -3600);
-- Restore an accidentally dropped table
UNDROP TABLE sales.orders;
-- Instant, storage-free test copy of production
CREATE DATABASE analytics_test CLONE analytics_prod;Snowflake skips micro-partitions whose min/max values can't match your filter. That's pruning, and it's the biggest performance lever you have. Filter on well-clustered columns, avoid wrapping them in functions, select only the columns you need, and read the query profile for remote spilling, which means the warehouse is too small for the operation.
-- Prunes well: direct filter on the date column
SELECT order_id, amount FROM sales.orders
WHERE order_date >= '2026-09-01';
-- For very large tables filtered by the same columns
ALTER TABLE sales.orders CLUSTER BY (order_date);Each warehouse size step roughly doubles the credits per hour, so an oversized warehouse burns money even on small queries. Right-size per workload, keep auto-suspend short, and put resource monitors on warehouses so spend triggers alerts or suspends compute before it surprises finance.
CREATE RESOURCE MONITOR bi_monthly
WITH CREDIT_QUOTA = 200
TRIGGERS ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = bi_monthly;A stream records inserts, updates and deletes on a table since you last consumed it, which is change data capture inside Snowflake. A task runs SQL on a schedule or when a stream has data. Dynamic tables go a step further: you declare the query and a target freshness, and Snowflake keeps the result up to date for you.
CREATE STREAM orders_stream ON TABLE raw.orders;
CREATE TASK merge_orders
WAREHOUSE = etl_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO core.orders t USING orders_stream s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount);
ALTER TASK merge_orders RESUME;ALTER TASK ... RESUME is the most common reason a "scheduled" pipeline never runs.Snowflake access is role-based: privileges are granted to roles and roles to users. Build functional roles (analyst, engineer, loader) instead of granting to individuals. Protect sensitive columns with masking policies so the same query shows real values to authorized roles and masked values to everyone else.
CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
CASE WHEN CURRENT_ROLE() IN ('PII_READER') THEN val
ELSE '***MASKED***' END;
ALTER TABLE core.customers MODIFY COLUMN email
SET MASKING POLICY mask_email;Build a small but production-shaped Snowflake pipeline end to end.
Snowflake data engineer interviews test architecture understanding (warehouses, micro-partitions, caching), loading patterns, Time Travel, streams and tasks, and cost and performance trade-offs.
25 questions covering everything above. Score 80% or higher to earn your Cloud Data Engineer (Snowflake) badge on your dashboard.
25 questions · pass at 80%+ to earn your Cloud Data Engineer (Snowflake) badge on your dashboard. One-time payment, lifetime access.