Snowflake Streams and Tasks implement Change Data Capture and scheduled data transformation entirely within Snowflake, without any external orchestrator. A Stream is a change tracking object attached to a source table — it records every INSERT, UPDATE, and DELETE since the stream was last consumed. A Task is a scheduled SQL statement or stored procedure that executes on a configurable schedule (cron or fixed interval) using a Virtual Warehouse or serverless compute. Together they provide an Airflow-like scheduling capability native to Snowflake without any external tooling.
The canonical Streams and Tasks pattern for incremental ELT: a Snowpipe loads new raw data into a staging table, a Stream on the staging table captures the new rows, a Task runs on schedule and processes only the rows captured in the Stream since the last run, and writes the transformed data to the production fact table via a MERGE statement. This pattern produces incremental processing at any frequency without reading the full staging table on every run, making large-scale data warehouse ELT far more efficient than full-table reprocessing.
Analogy🏏Cricket
🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.