Dynamic Tables versus streams and tasks on a mutable feed
RAW_EVENTS is append-only at 60M rows/day and feeds two Dynamic Tables: DT_SILVER with a 10-minute target lag, and DT_GOLD, which reads DT_SILVER with its own 10-minute lag. A separate team runs a nightly MERGE updating about 2% of the prior 30 days' rows, and occasionally re-inserts the last 7 days wholesale. Since that job started, DT_GOLD sometimes goes four hours without a refresh, and refresh credits have tripled. DT_GOLD's definition joins DT_SILVER to a mutable DIM_CUSTOMER and stamps every row with CURRENT_TIMESTAMP() as PROCESSED_AT. The team is debating whether to delete and recreate both Dynamic Tables with a shorter target lag, or to migrate everything to streams and tasks. Refreshes run on a dedicated M warehouse.
1. Explain why DT_GOLD stopped refreshing on schedule and why credits tripled, given that RAW_EVENTS is append-only.
Read the worked answer
Append-only on the base table does not make the pipeline incremental - the definition has to be incremental too.
Three separate mechanisms are interacting:
- Upstream DML invalidates partitions. An UPDATE or a 7-day re-insert changes existing micro-partitions; the affected Dynamic Table partitions can no longer be appended to and must be re-derived. The 2% nightly update is small in rows but spans 30 days of partitions, so the recompute footprint is much larger than 2% suggests.
- CURRENT_TIMESTAMP() is non-deterministic. The same input rows produce different output on every evaluation, so the result cannot be incrementally maintained; the object is pushed toward full refresh behaviour. This alone can explain the credit tripling.
- Chained lag compounds. DT_GOLD cannot start until DT_SILVER's new partitions exist, so a 10-minute lag on each hop is a floor, not an average - and target lag is a lower bound Snowflake tries to meet, never a guarantee.
The proposal to shorten the lag makes every symptom worse: more frequent refreshes over the same invalidated partitions.
2. Recommend an architecture. Justify where you keep Dynamic Tables and where you replace them.
Read the worked answer
Split the pipeline by mutation characteristics rather than migrating wholesale.
Keep a Dynamic Table where the shape is append-only and deterministic. A silver layer that filters, casts, and flattens RAW_EVENTS qualifies. Fix it by replacing CURRENT_TIMESTAMP() with a column carried in the data (ingest timestamp), which restores incrementality - that is the single highest-value change in this setup.
Use streams and tasks where there are updates, deletes, or mutable joins. The gold layer joins a mutable DIM_CUSTOMER and receives the nightly MERGE; express it as a stream on the silver layer plus a task running MERGE/UPSERT on a schedule you control. You then pay for exactly the changed rows and can see the affected-row counts, which a Dynamic Table hides from you.
Accept the trade-off: you now own scheduling, idempotency, and failure retry for the gold layer. That is the price of control, and it is justified here because the current lag is unbounded and unattributable.
3. What would you change about how the team reasons about 'target lag' before any of this is implemented?
Read the worked answer
Target lag is a target, not an SLA, and the setup shows the team treating it as one. Two consequences follow.
- Publish the real objective. Measure end-to-end freshness (event time → gold row visible) and alert on it. A 10-minute target lag with a 4-hour tail is functioning as designed from Snowflake's side; the SLO is broken from the business's side.
- Do not chase lag by tightening it. Shorter target lag on a non-incremental definition means more frequent full recomputes. Lag and cost move in opposite directions until the definition is made incremental.
Operationally, instrument DT refresh history plus the stream/task run history in one view so latency regressions are attributable to a hop. And agree with the upstream team on a change-notification protocol: a wholesale 7-day re-insert should be a scheduled, announced event, because it is a known trigger for multi-hour downstream lag.