cswork / libraryOpen practice workspace

Articles · Snowflake

Snowflake Dynamic Tables Interview Questions: Target Lag, Refresh Modes, and Pipeline Failures

Prepare for Snowflake Dynamic Tables interviews with target-lag reasoning, current refresh modes, refresh-history diagnosis, and safe pipeline recovery.

Snowflake Dynamic Tables interview questions become much easier when you separate three ideas: the freshness objective, the processing strategy, and the observed refresh history. TARGET_LAG describes desired freshness relative to upstream data; refresh mode describes how Snowflake maintains the result; history tells you what actually happened. Treating those as one timer produces weak diagnoses.

This guide uses Snowflake’s current documentation and an original three-layer pipeline. The history rows and calculations are invented for practice and were checked locally; they are not customer telemetry or a reported interview question. Begin with Implement a Snowflake Query Client if you want a focused SQL-and-client exercise first.

Explain target lag from the root data

Official documentation: Snowflake defines target lag for a dynamic table relative to its root or base tables, not simply relative to the immediately upstream dynamic table. It is a target that the scheduler works toward, not a guaranteed refresh interval or service-level agreement. A shorter target generally requires more frequent work and can increase compute cost.

Consider raw_orders → clean_orders → serving_metrics. Suppose clean_orders has a five-minute target and serving_metrics has a ten-minute target. At 12:11, both contain root-table changes through 12:07. The observed data lag is four minutes. It is not fifteen minutes. Adding the two configured targets mistakes independent freshness objectives for serial sleep durations.

The strongest interview answer identifies the timestamp being compared. Ask: “Fresh relative to which root data, measured at what current time, and using which successful refresh?” If the root last changed at 12:07 and serving exposes that change by 12:11, observed lag is four minutes. A later failed refresh does not retroactively erase that last successful data timestamp.

TARGET_LAG = DOWNSTREAM has a different intent. It tells an intermediate table to refresh when a downstream table requires it rather than maintain an independent periodic freshness target. If no downstream consumer needs refreshes, do not assume the intermediate table runs on its own schedule.

Distinguish configured target from observed behavior

Use three columns in your reasoning:

SignalQuestion it answersIt does not prove
Target lagHow fresh should this table aim to be?Exact start time or completion guarantee
Data timestampThrough what upstream point is the result current?Why the next refresh failed
Refresh durationHow long did one attempt take?End-to-end freshness by itself
Refresh stateDid the attempt succeed, skip, or fail?Whether downstream readers saw partial data
Refresh actionIncremental, full, or reinitialization behaviorBusiness correctness of the query

A target can be missed because a refresh takes longer than the target, the warehouse is unavailable or overloaded, an upstream table is suspended, the definition is no longer supported for the selected mode, or a pipeline became stale beyond a recovery boundary. Start with history and scheduling state instead of immediately resizing the warehouse.

In the original exercise, serving succeeded at 12:11 with a data timestamp of 12:07. A 12:24 attempt failed and retained that timestamp. A later successful row at 12:31 advanced the data timestamp to 12:29 and recorded REINITIALIZE. Six local checks confirmed the four-minute lag, rejected target addition, preserved the last successful timestamp after failure, and recognized the recovery action. This model does not execute Snowflake SQL.

Compare current refresh modes accurately

Current official behavior checked September 10, 2026: Snowflake documents FULL, INCREMENTAL, AUTO, and the newer ADAPTIVE mode. The documentation also describes CUSTOM_INCREMENTAL for advanced user-defined refresh logic. Because this surface is evolving, state the version and account availability you actually see rather than repeating an older three-mode list.

FULL recomputes the dynamic table. INCREMENTAL processes changes when the query uses supported constructs. AUTO resolves to a concrete strategy at creation time; it does not continuously switch strategy on every refresh. ADAPTIVE, introduced in public preview in May 2026, normally uses incremental processing but can reinitialize when internal heuristics judge that rebuilding is cheaper after large upstream changes.

That distinction leads to a good scenario answer. An append-heavy supported query with small regular changes is a natural incremental candidate. A definition containing unsupported incremental constructs needs full behavior or a redesign. A workload with small changes punctuated by large overwrites may suit adaptive behavior if the feature is available and its reinitialization cost is acceptable.

Do not choose mode from a slogan. Ask how much data changes, which operators appear, whether a stable row identity exists, how expensive reinitialization is, and whether downstream freshness can tolerate it. Then confirm actual mode and action through metadata and history.

Diagnose a stale Dynamic Tables pipeline

Start at the table users report as stale, then walk upstream. Record current time, target lag, last successful data timestamp, last attempt, duration, state, error, refresh action, and reinitialization reason. Compare tables using the same root-data event. This prevents a dashboard timestamp, task completion time, and data timestamp from being mixed.

A practical diagnostic query uses DYNAMIC_TABLE_REFRESH_HISTORY and selects fields such as table name, state, data timestamp, refresh start/end, refresh action, and reinitialization reason. Exact columns and retention should be taken from the current account documentation. Query INFORMATION_SCHEMA.DYNAMIC_TABLES for scheduling state and reported lag signals as a complementary view.

Classify the failure before changing configuration:

  1. A failed row with an unchanged data timestamp is an execution problem.
  2. Repeated durations beyond the freshness target indicate that the current compute/query design cannot meet the objective.
  3. A DOWNSTREAM intermediate with no active consuming refresh may be behaving as configured.
  4. A REINITIALIZE row needs its reason and cost inspected; it is not automatically an outage.
  5. A table stale beyond MAX_DATA_EXTENSION_TIME_IN_DAYS may require recreation according to Snowflake’s current modification guidance.

Do not claim that increasing warehouse size will always fix the issue. It may help compute-bound refreshes, but unsupported SQL, suspended scheduling, poor locality, permission errors, or a broken upstream definition require different action. State the next observation that would distinguish those causes.

Explain reinitialization and safe recovery

Reinitialization is a full rebuild used when the maintained incremental state cannot continue or when adaptive logic chooses a rebuild. It can be expected after particular definition or mode changes. Snowflake’s current modify documentation distinguishes transitions that require reinitialization from metadata-only changes; for example, changing target lag changes scheduling rather than data semantics.

A safe recovery answer includes validation. Capture the pre-change definition and history. Make the smallest supported change. Observe the first successful refresh, compare row counts and key aggregates against an independent query, inspect the new data timestamp, and monitor at least one subsequent normal refresh. A green state alone does not prove the transformed data is correct.

If a refresh failed after an upstream schema change, identify whether the selected mode can absorb that change. Recreating the table can restore operation but may incur a full rebuild and temporarily affect freshness. Explain how readers are protected while validation runs and how you would roll back the definition.

Choose Dynamic Tables or Streams and Tasks

Dynamic Tables fit a declarative result whose freshness target and supported query can drive maintenance. Streams and Tasks fit workflows requiring explicit orchestration, procedural steps, side effects, or precise control over consumption and retries. The decision is not “new feature versus old feature.” It follows the pipeline contract.

Ask whether the output is expressible as a maintained query, whether every step is a table transformation, whether an external call or stored procedure is required, how backfills are initiated, and what recovery evidence operators need. A mixed architecture can be valid: dynamic tables maintain relational layers while tasks coordinate operations outside that model.

The related Snowflake Streams and Tasks guide covers offsets, MERGE, and reruns in detail. Use it after you can explain why the declarative pipeline is or is not sufficient.

Work through three interview follow-ups

“The serving table has a ten-minute target. Why did it refresh after only four minutes?” A target is a maximum desired freshness lag, not a command to wait exactly ten minutes. Snowflake coordinates refreshes across the graph and can refresh sooner. Verify the root data timestamp and successful history before concluding that the schedule is wrong.

“Both targets are being met, so can the dashboard still be wrong?” Yes. Freshness says how recently the maintained query incorporated upstream data; it does not prove that the SQL expresses the intended metric. A misplaced filter, duplicated join, timezone boundary, or late-arriving event policy can produce a fresh but incorrect result. Pair operational checks with an independent correctness query over a bounded window.

“AUTO was selected, so will Snowflake change between full and incremental each run?” Current documentation says AUTO chooses its concrete refresh mode when the dynamic table is created. Inspect the resulting mode. Do not infer per-refresh switching from the word “auto.” Adaptive behavior is a separate mode with its own requirements and reinitialization signals.

For each answer, lead with the semantic rule, apply it to the supplied timestamps or definition, and name the confirming query. Avoid jumping directly to a product feature. Interviewers can then see whether you can turn a vague symptom into a falsifiable diagnosis.

Build a recovery explanation with checkpoints

Before changing a failed pipeline, write down what readers currently see and the last known-good data timestamp. Preserve the failing definition and exact error. This creates a comparison point and avoids a recovery that restores scheduling while silently changing business logic.

Next, choose one intervention: resume scheduling, correct an unsupported construct, restore permission, change compute, or recreate after an incompatible change. Explain why the observed evidence selects that intervention. If several causes remain plausible, run the cheapest discriminating check first. A warehouse resize should follow duration or queue evidence; a definition edit should follow compilation or support evidence.

After recovery, validate in layers. First confirm a successful refresh and advancing data timestamp. Then compare counts, null rates, distinct keys, and one business aggregate against an independently computed source query. Finally, observe the next scheduled refresh so the result is not merely a one-time manual success. Record refresh action and reinitialization reason because a rebuild may have different cost and latency from steady-state operation.

Close with an operational limit: the exercise history is synthetic, so it proves the arithmetic and decision sequence only. A real Snowflake environment is required to validate account-specific feature availability, privileges, warehouse behavior, history retention, and the exact fields returned by current table functions.

Sources and Further Reading