Data Engineering · Topic 12 of 16

Dynamic Tables and Staging Layers

Design declarative pipelines with dynamic tables: target lag, refresh modes, downstream lag and cost, plus raw, staging and curated table layers.

  • 5 min
  • Includes a diagram
In a nutshell
  • Dynamic table: query plus TARGET_LAG plus WAREHOUSE; minimum lag is 60 seconds; DOWNSTREAM lets upstream tables follow dependents.
  • Refresh modes: INCREMENTAL, FULL and AUTO; AUTO is decided at creation.
  • Shorter lag means more refreshes and more credits.

What it is

A dynamic table is a table defined by a query. You declare the query and how stale the result may be, and Snowflake refreshes it for you. It replaces hand built streams, tasks and MERGE statements when the logic is a SELECT.

Staging layers are the tables a pipeline passes through on the way from raw files to reporting. An Architect decides how many layers there are, what each stores and which table type each uses.

Source tablesbase dataqueryDynamic tabledeclared with TARGET_LAGrefresh on lagDynamic tablenext layerread byBI tools

How it works

Creating a dynamic table

CREATE OR REPLACE DYNAMIC TABLE orders_clean
  TARGET_LAG = '5 minutes'
  WAREHOUSE = transform_wh
  REFRESH_MODE = AUTO
AS
  SELECT o.id, o.amount, c.region
  FROM raw_orders o JOIN customers c ON o.customer_id = c.id;
  • TARGET_LAG is the maximum staleness you accept. It is a time such as seconds, minutes, hours or days, with a 60 second minimum, or DOWNSTREAM.
  • WAREHOUSE runs the refreshes. You pay for that compute, plus storage for the stored result.
  • REFRESH_MODE is AUTO, FULL or INCREMENTAL.
  • INITIALIZE is ON_CREATE (default) or ON_SCHEDULE.

Refresh modes

ModeWhat happensChoose it when
INCREMENTALOnly changes in the sources are processedFew rows change between refreshes and the query is supported
FULLThe whole query is rerunThe query is not supported incrementally, or most rows change
AUTOSnowflake picks incremental if it can, otherwise fullYou are unsure, but check which mode it chose

AUTO is decided when the table is created. If it picks full, the table can become expensive as the source grows, so check SHOW DYNAMIC TABLES or the refresh history for the mode in use.

Target lag and chains

In a chain of dynamic tables, an intermediate table with TARGET_LAG = DOWNSTREAM refreshes only when the table that depends on it needs fresh data to meet its own lag. DOWNSTREAM means "refresh only as often as the tables that depend on me need". In a chain, set a real lag on the final table and DOWNSTREAM on the ones upstream. Snowflake then schedules the whole chain to meet the final lag, and no layer refreshes more often than needed.

Raw tableloaded by SnowpipefeedsDynamic table Alag DOWNSTREAMfeedsDynamic table Blag 10 minutesservesReports

A shorter lag means more refreshes and more warehouse credits. A refresh with nothing new in the sources does very little work, but a very short lag on a large full refresh table is costly.

Exam tip

Lag is a target, not a guarantee. If refreshes take longer than the lag, the table falls behind, so size the warehouse for refresh duration.

Operating dynamic tables

  • ALTER DYNAMIC TABLE ... SUSPEND stops refreshes and RESUME restarts them. ALTER DYNAMIC TABLE ... REFRESH forces a manual refresh.
  • Refresh history is available through the DYNAMIC_TABLE_REFRESH_HISTORY table function in INFORMATION_SCHEMA and the matching ACCOUNT_USAGE view.
  • A dynamic table is read only. You cannot run INSERT, UPDATE or DELETE on it, because the query defines the content.
  • Dynamic tables can read other dynamic tables, views and tables, so they form a graph.
NeedBetter fit
Declarative SELECT with joins and aggregates, freshness in minutesDynamic table
Procedural logic, calls to external systems, custom error handlingStreams, tasks and stored procedures
Single table aggregate read often, no warehouse to manageMaterialized view

A streams and tasks pipeline gives full control over timing and logic. A dynamic table gives less code and automatic dependency handling, but only for what a query can express.

Staging layers and tables

A common design uses three layers, often called raw, staging (or refined) and curated.

LayerContentTypical table typeWhy
RawFiles landed as is, often a VARIANT column plus load metadataTransient or permanentKeep the original for replay
StagingTyped, deduplicated, cleaned dataTransientCan be rebuilt from raw, so skip Fail safe cost
Curated and consumptionBusiness models, aggregates, secure viewsPermanentNeeds Time Travel and Fail safe protection
Filescloud storageCOPY or SnowpipeRaw tableVARIANT plus metadataclean and typeStaging tabletransientmodelCurated tablepermanent

Design points an Architect weighs:

  • Replay. Keep raw data long enough to rebuild downstream layers after a logic bug.
  • Cost. Transient tables and a Time Travel retention of 0 or 1 day on staging remove most storage overhead.
  • Isolation. Separate schemas per layer make grants simple: loaders write to raw, analysts read only curated.
  • Load method. Snowpipe or COPY fills raw. Dynamic tables, or streams with tasks, move data between later layers.
  • Stage versus table. A stage holds files; a staging table holds rows. Data that is queried repeatedly belongs in a table.

Common mistakes

Setting a short lag on every table in a chain. Use DOWNSTREAM for the upstream tables.

Trusting AUTO without checking. It may fall back to full refresh and cost far more than expected.

Expecting to edit a dynamic table. It is read only, and changing the content means changing the query.

Making every layer permanent. Staging is rebuildable and does not need Fail safe storage.

What to remember for the exam

  • Dynamic table: query plus TARGET_LAG plus WAREHOUSE; minimum lag is 60 seconds; DOWNSTREAM lets upstream tables follow dependents.
  • Refresh modes: INCREMENTAL, FULL and AUTO; AUTO is decided at creation.
  • Shorter lag means more refreshes and more credits.
  • Use streams and tasks for procedural logic; use dynamic tables for declarative SQL.
  • Raw, staging and curated layers: transient for rebuildable staging, permanent for curated.

Check yourself

  • Three dynamic tables feed each other and only the last one needs a 10 minute lag. What lag do the first two use?

    DOWNSTREAM, so they refresh only as often as the final table needs.

  • Which refresh mode must you use if the query cannot be maintained incrementally?

    FULL. AUTO would also fall back to full in that case.

  • Why make the staging layer transient?

    The data can be rebuilt from raw, and transient tables avoid Fail safe storage cost.

Topic 12 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.