Snowflake Views, Dynamic Tables, Streams and Tasks

1.Views, materialized views and dynamic tables: properties and cost

Every transformation design starts with a basic choice: compute the result each time someone asks for it, or store it ahead of time. A standard view takes the first approach. In Snowflake, a view lets the result of a query be accessed as if it were a table. It can join two or more tables or other views into one logical object, and once it is defined you query it like any other table. Because a view stores only its definition, it uses no storage. The cost is that every query recomputes the result on a warehouse.

Materialized views and dynamic tables both store their result. A materialized view pre-computes its dataset, so reading it is faster than running the query against the base table. The difference is largest when the query runs often or is complex, and it helps most with expensive aggregation, projection and selection over large data sets. A dynamic table also materializes a query result. Instead of building a target table and writing your own code to keep it updated, you declare the transforming SQL and background automation keeps it current. Both stored options pay for that speed in the same way: they use storage, and they need compute to stay up to date.

Read the table as benefits against limitations. A view's benefits are that it is simple, easily defined and consumes no storage. Its limitations are that it is inflexible and slow, because it requires compute to generate results. A materialized view's benefits are fast results retrieval, a relatively simple definition, some flexibility and results that are always up to date. Its limitations are the compute needed to keep it up to date and the storage it consumes. A dynamic table's benefits are extremely fast retrieval, a very flexible definition, fine control on refresh and support for complex transformations. Its limitations are the same compute and storage cost, plus the need to decide carefully how often, when and how to refresh. The properties that separate the three are storage use, compute needed to stay current, flexibility and refresh control. Match them to the need: choose a view when simplicity and zero storage matter more than speed, a materialized view or dynamic table when reads must be fast and you accept paying compute and storage to keep results current, and a dynamic table when the transformation is complex or you need control over refresh. Snowflake documents object-specific limitations on separate pages (Limitations on Views and Working with Materialized Views); this lesson covers the summary comparison, not those full lists.

Views also affect change capture. You can create a stream on a view, including a secure view. However, a stream on a view needs change tracking turned on explicitly for the view and for its underlying tables, so that the hidden change-tracking columns are added to those tables. A view is therefore not free in a CDC design: it relies on metadata stored in the tables underneath it.

Sources12

2.Dynamic tables: target lag, refresh modes, costs and limits

A dynamic table definition answers three questions: what the table contains (the SELECT), how fresh it must be (TARGET_LAG), and how Snowflake refreshes it (REFRESH_MODE). After you create it, Snowflake parses the query, identifies its base tables, watches them for changes, and refreshes the table to stay within the lag you set.

CREATE OR ALTER DYNAMIC TABLE dt_orders
     ?  = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        order_id,
        customer_id,
        order_date,
        TRIM(UPPER(product_name)) AS product_name,
        quantity,
        unit_price,
        quantity * unit_price AS line_total,
        order_status
    FROM raw_orders
    WHERE order_status != 'returned';

Each refresh follows the same sequence. Snowflake detects that a base table has changed. It then computes either only the changed rows (incremental) or the whole result (full). Finally it applies the new results atomically, so readers never see a partial refresh. When dynamic tables read from one another, Snowflake infers the dependency graph from their queries, chooses a consistent snapshot timestamp, and refreshes them in dependency order. You don't declare dependencies or script an execution order. Each table in the chain keeps its own target lag. An intermediate table can use TARGET_LAG = DOWNSTREAM so that it refreshes only when the tables that depend on it need fresh data.

Cost and performance depend on the lag you choose. Target lag is a target, not a guarantee: actual lag can exceed it when refreshes take longer than expected. Dynamic tables are billed in three categories. Shortening the lag increases more than refresh frequency. It also adds Cloud Services work for scheduling.

As a rule of thumb, logic you can express as a SQL SELECT is a candidate for a dynamic table. Moving from batch to near-real-time freshness then means changing one parameter. There are firm limits, though. Dynamic tables don't support data fresher than 60 seconds (the minimum target lag) or strictly guaranteed refresh timing. Their definitions can't use stored procedures or external functions, or UDTFs outside of lateral joins. Workloads that need any of these belong with streams and tasks.

Sources3

3.Streams and tasks: explicit change capture and scheduling

When you need control that a dynamic table doesn't give you, you build the pipeline from two pieces. A stream records DML changes (inserts, including COPY INTO, plus updates and deletes) on a source object, along with metadata about each change. This is change data capture. A stream doesn't hold table data. It stores an offset, a bookmark in the source's version history, and returns change records by reading that history. Because of this you can create streams on standard tables, views, directory tables, dynamic tables, external tables and event tables, and on Apache Iceberg tables with limitations.

Several queries can read the same change data without moving the offset. It advances only when the stream is used in a DML transaction that commits. To have several statements see the same change records, wrap them in BEGIN ... COMMIT, because streams use repeatable read isolation. Each change record has the source's columns plus METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE, and METADATA$ROW_ID. An UPDATE appears as a DELETE/INSERT pair with METADATA$ISUPDATE set to TRUE.

A task runs the processing. Tasks can run SQL commands or stored procedures on a fixed schedule (an interval or USING CRON), or they can be triggered when a stream has new data. A newly created task starts suspended: run it once with EXECUTE TASK, or let it run continuously with ALTER TASK ... RESUME. Only one instance of a scheduled task runs at a time, so if a run is still going when the next one is due, that scheduled run is skipped. Triggered tasks suit ELT pipelines because they avoid frequent polling when new data arrives unpredictably.

CREATE TASK triggered_task_stream
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
  AS
    INSERT INTO completed_promotions
    SELECT order_id, order_total, order_time, promotion_id
    FROM orders_stream;

The compute model drives both cost and performance. Serverless tasks omit the WAREHOUSE parameter. Snowflake sizes them from recent runs, within SERVERLESS_TASK_MIN_STATEMENT_SIZE and SERVERLESS_TASK_MAX_STATEMENT_SIZE, up to a ceiling equivalent to an XXLARGE warehouse. User-managed tasks name a warehouse and give you full control of it.

Sources24

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A dynamic table with a very short target lag can deliver sub-minute freshness with a guaranteed refresh time.Why is that wrong?

   The minimum target lag is 60 seconds, and lag is a target rather than a guarantee. Actual lag can exceed it when refreshes run long.

   Covered in Dynamic tables: target lag, refresh modes, costs and limits

2. 2.A dynamic table definition can call a stored procedure or an external function to do part of the transformation.Why is that wrong?

   Dynamic tables don't support workloads that need stored procedures or external functions in the definition. Use streams and tasks for those.

   Covered in Dynamic tables: target lag, refresh modes, costs and limits

3. 3.Selecting from a stream consumes its change records.Why is that wrong?

   Only a committed DML transaction that uses the stream advances its offset. Any number of plain queries can read the same changes.

   Covered in Streams and tasks: explicit change capture and scheduling

4. 4.A serverless task will scale to whatever size the workload needs.Why is that wrong?

   Serverless tasks are capped at the equivalent of an XXLARGE warehouse. Larger workloads need a user-managed task with a bigger warehouse.

   Covered in Streams and tasks: explicit change capture and scheduling