snowflake-core-workflow-b

# Snowflake Core Workflow B — Data Transformation ## Overview Build ELT pipelines using streams (change data capture), tasks (scheduling), and dynamic tables (declarative transforms). ## Prerequisites - Data loaded into Snowflake (via `snowflake-core-workflow-a`) - Understanding of ELT vs ETL patterns - Role with `CREATE TASK`, `CREATE STREAM` privileges ## Instructions ### Step 1: Create a Stream for Change Data Capture ```sql -- Track changes on the raw orders table CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders APPEND_ONLY = FALSE; -- Append-only stream (lighter weight, inserts only) CREATE OR REPLACE STREAM events_stream ON TABLE raw_events APPEND_ONLY = TRUE; -- Check what's changed since last consumption SELECT * FROM orders_stream; -- METADATA$ACTION = 'INSERT' | 'DELETE' -- METADATA$ISUPDATE = TRUE if row is part of an UPDATE -- METADATA$ROW_ID = unique row identifier ``` ### Step 2: Create a Task to Process Stream Data ```sql -- Transform task runs when stream has data CREATE OR REPLACE TASK transform_orders WAREHOUSE = TRANSFORM_WH SCHEDULE = '5 MINUTE' WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') AS MERGE INTO dim_orders AS target USING ( SELECT order_id, customer_id, amount::DECIMAL(12,2) AS amount, order_date::TIMESTAMP_NTZ AS order_date, CASE WHEN amount >= 1000 THEN 'high_value' WHEN amount >= 100 THEN 'medium_value' ELSE 'standard' END AS order_tier, CURR...