Snowflake Time Travel Interview Questions: Recovery, Retention, and Cloning

An accidental UPDATE resets two orders to zero. A valid order arrives one minute later. Restoring the whole table to its earlier state repairs the corrupted amounts but deletes the good new order. Snowflake Time Travel can expose the historical data; it cannot decide which later business events you should preserve.

For Snowflake Time Travel interview questions, separate three decisions: whether the required history is retained, which historical boundary represents the intended state, and how to reconcile that state with current data. Recover into a separate object before proposing a production replacement.

Evidence boundary: Current Snowflake documentation supports the feature rules. The order ledger is an original synthetic recovery exercise, checked with a Python state model. The SQL illustrates a review workflow; it was not executed against Snowflake and is not a ready-to-run production repair script. The scope is ordinary Snowflake tables unless another object type is explicitly named.

For related pipeline and warehouse reasoning, use the verified Data Engineer practice collection.

What is the first question in a Time Travel recovery?

Ask which object, incident, and target state the request refers to. “Recover yesterday's data” is incomplete without an exact table identity, time zone, relevant statement or transaction, and an explanation of what went wrong.

Then inspect retention rather than reciting the maximum supported window. Snowflake documents a one-day default. Standard Edition allows zero or one day; Enterprise and higher permit up to 90 days for permanent tables. Temporary and transient tables remain limited to zero or one day. Effective retention can also reflect an account-level minimum. Snowflake Time Travel documentation

In an interview, say: “The account's edition is not enough. I need the table kind and effective retention at the time of the incident.” A table configured for one day does not acquire 90 days of history merely because its account can support that setting.

Use inspection evidence such as SHOW TABLES and its retention information, plus incident/query history. Verify that you are inspecting the intended database and schema, not an identically named development object. Do not change retention settings as your first recovery action: increasing the window cannot resurrect data that has already aged out.

The same reasoning applies to a table created recently. A request for a time before that object existed is not a valid snapshot request simply because it falls within an account's nominal retention duration.

AT and BEFORE identify different historical boundaries

Snowflake supports historical access using a timestamp, a relative offset, or a statement identifier where the object supports those options. AT includes changes at the specified boundary. BEFORE(STATEMENT) selects the point immediately before the referenced statement completes, not necessarily before it starts. Concurrent commits between start and completion can therefore appear in the result. Statement identifiers for this feature must refer to queries within the last 14 days; an older retained state may need a timestamp instead. AT and BEFORE reference

That completion detail is an excellent reasoning test. Suppose a bad statement starts at 10:01, another session commits a valid insert at 10:01:30, and the bad statement finishes at 10:02. “Before the bad statement” is ambiguous until you explain which boundary you mean.

If the recovery contract is specifically the state before its start, choose and verify that earlier timestamp rather than assuming the statement form supplies it. If the goal is to remove only the bad statement's effect, preserving intervening legitimate commits may be desirable. The incident determines the correct choice.

Here is a read-only timestamp illustration for the synthetic ledger:

SELECT order_id, amount
FROM orders AT (
  TIMESTAMP => '2026-10-01 10:00:00 +00:00'::TIMESTAMP_TZ
)
ORDER BY order_id;

The timestamp is illustrative; replace it with a verified point inside the real table's retained history. The explicit offset prevents a human reader from confusing local wall-clock time with UTC. A relative offset can be useful for a quick investigation, but “five minutes ago” moves every time you rerun it. Record a fixed boundary for a reproducible incident review.

Work the recovery ledger before choosing a command

Use this original sequence, with no schema changes or concurrent writers beyond the events listed:

Event	State and control total
10:00, valid state	Order 1 = 100; order 2 = 200. Total 300.
10:02, accidental update commits	Both existing amounts become zero. Total 0.
10:03, valid insert commits	Order 3 = 50 is added. Current total 50.
Desired reconciled state	Restore 1 and 2; preserve 3. Total 350.

A historical copy at 10:00 contains two rows totaling 300. Replacing the current table wholesale with that copy loses order 3. Leaving current data untouched preserves the new order but leaves two corrupted amounts. Neither result satisfies the stated recovery contract.

The expected repair has three rows totaling 350. That number is not a Snowflake guarantee; it follows from the explicit exercise assumptions. If later legitimate adjustments also changed orders 1 or 2, blindly copying their old amounts would be wrong. Ask for those events before approving the reconciliation.

An answer can be technically sophisticated yet operationally unsafe if it skips this ledger. The interviewer should hear the difference between restoring a snapshot and reversing a specific incident's effects.

Why clone into a separate recovery object?

A historical clone gives you a separately named object to inspect and compare without immediately replacing the current table. Snowflake's cloning documentation describes initially shared storage and independent changes afterward; zero-copy does not mean an unlimited, unrelated backup system. Snowflake cloning documentation

For the ledger's fixed historical boundary:

CREATE TABLE recovery_orders CLONE orders AT (
  TIMESTAMP => '2026-10-01 10:00:00 +00:00'::TIMESTAMP_TZ
);

SELECT COUNT(*) AS recovered_rows,
       SUM(amount) AS recovered_total
FROM recovery_orders;

This creates an object and requires suitable privileges and a collision-free name. It is an illustrative operation for a permitted sandbox or an approved recovery procedure, not an instruction to create objects in production without authorization.

Compare current and recovered values before designing any write:

SELECT c.order_id, c.amount AS current_amount,
       h.amount AS historical_amount
FROM orders c
JOIN recovery_orders h ON c.order_id = h.order_id
WHERE c.amount <> h.amount;

Under the fixture's non-null amount assumption, this identifies orders 1 and 2. A real nullable field needs a null-aware comparison; otherwise a change involving NULL can disappear from an ordinary inequality filter. Inspect unmatched keys separately too. This inner join alone cannot prove that every later insert, deletion, or correction is accounted for.

Before any repair, prove that the recovery side has one row per intended key. A many-to-many comparison or merge can duplicate work or make update behavior ambiguous. A unique-looking total is not a substitute for key-level evidence.

What belongs in a safe reconciliation proposal?

Describe the affected key population, the fields to restore, and the later events to preserve. For this fixture, only orders 1 and 2 need amount restoration. Order 3 must survive unchanged. Avoid a broad replacement justified only by matching an old total.

Establish control totals before and after, but include row-level assertions. The proposal should predict three distinct IDs, amounts 100/200/50, total 350, and no duplicate order IDs. Two incorrect amounts could cancel out and leave a convincing sum, so the sum alone is insufficient.

Discuss write coordination. If normal writers continue changing the affected rows while you compare and apply a repair, your reviewed assumptions can become stale. The operational plan needs a controlled window or an explicit conflict-detection strategy appropriate to the system. Do not promise that a recovery clone itself freezes production writers.

Also identify downstream consequences: a pipeline may already have exported the corrupted values, a report may have cached them, and a consumer may have processed a change event. Repairing the source table does not automatically establish that every downstream system is reconciled. That requires its own evidence and authorized procedure.

Your interview deliverable is a justified plan with observable acceptance criteria. You do not need to improvise a destructive update statement to show competence. Naming the missing information is often the strongest part of the answer.

When should you use UNDROP rather than historical SELECT?

Historical SELECT investigates retained row history of an existing object. A clone makes a separately named working copy. UNDROP addresses a dropped object, subject to its supported behavior and retention window. Snowflake documents restoration of the most recently dropped table version and a name collision when a current table already uses that name. UNDROP TABLE reference

Suppose someone drops orders and then creates a new orders. Saying “just run UNDROP TABLE orders” ignores the collision and risks confusion between two different object identities. Inspect table history, creation/drop times, schema, and the intended version before choosing a recovery path.

Do not delete the replacement table merely to free its name. Preserve evidence and seek an approved naming or recovery procedure. The new table may already contain valid writes or have consumers depending on it. A correct command applied to the wrong object still produces an incorrect recovery.

The article's ordinary-table examples also should not be generalized to every Snowflake object. Hybrid tables have separate Time Travel restrictions, including limits on supported boundary forms, and UNDROP TABLE does not support them. State the object type rather than assuming every table-like feature shares one recovery contract.

Can a database clone cover every child table's history?

A database or schema's configured retention does not prove that every child table can supply the requested historical point. A child with a shorter available history can prevent the historical clone from completing. Snowflake provides an option to ignore tables with insufficient retention, but ignoring means omitting those tables, not restoring their missing history. Historical cloning considerations

In a practice case, put a seven-day permanent table and a one-day transient table under the same schema, then request a three-day-old clone. The useful question is not only whether a command succeeds. Ask which objects would exist in the resulting recovery set and whether that set meets the application's consistency requirements.

A clone missing a small configuration table can be unusable even if it contains most of the data volume. Inventory required objects and verify their histories separately. State exclusions explicitly rather than celebrating partial command success as complete recovery.

How do Fail-safe and backup planning fit?

Snowflake describes Fail-safe as a non-configurable seven-day, best-effort recovery service after Time Travel, operated by Snowflake for extreme failures. It is not a user-queryable extension of the retention window or a guaranteed immediate rollback route. Fail-safe documentation

Do not tell an interviewer that every table gets 90 days plus seven days of convenient recovery. Table kind, configured retention, feature limitations, and the recovery method all matter. A backup or business-continuity design must additionally address failure domains, access loss, tested restoration, and the organization's required recovery point and recovery time.

Sources and Further Reading

• Snowflake: Understanding and using Time Travel.
• Snowflake: AT and BEFORE.
• Snowflake: Cloning considerations.
• Snowflake: UNDROP TABLE.
• Snowflake: Understanding and viewing Fail-safe.