Snowflake Architecture and Workload Design

Two things go wrong on a Snowflake platform often enough to be worth designing against. An engineer tunes a table layout, runs the query twice, and reports a dramatic speedup that nobody else can reproduce. And on a Monday morning someone finds that Friday’s MERGE zeroed a column, after the retention window the team never looked at has closed. These look unrelated, one about speed and one about recovery, but they come from the same property of the storage layer: table data lives in files that are never edited, and each write produces a new version of the table by adding and removing whole files. Caching, Time Travel, cloning and a good part of the storage bill follow from that.

The layers themselves, how warehouses are sized and scaled, and what each layer bills for are covered in Snowflake Architecture: Three Layers and What Each One Bills For, and queueing, concurrency limits and workload classes in Warehouse Workload Isolation and Concurrency. Here the focus is on the consequences of the storage design for the workloads that run on it: how to measure a query honestly, what a warehouse’s auto-suspend setting does to latency, how Time Travel and clones work and what retention costs, and how those decisions combine into a design. The mechanisms are demonstrated with small Python programs that model the behavior. They are stand-ins written for this article and contain no Snowflake code; every Snowflake statement shown is illustrative and was not run against an account.

Files that are never edited

Dageville and colleagues, in the paper that introduced Snowflake’s design, describe tables as horizontally partitioned into large immutable files, with column values grouped and compressed inside each file. Immutability follows from the object storage underneath: an object can be replaced but not modified in place. The paper then states the consequence for writes: an insert, update, delete or merge produces a newer version of the table by adding and removing whole files, and the removed files are retained for a configurable time, which is what lets the system read earlier versions of a table. In current documentation these files are micro-partitions, each holding between 50 MB and 500 MB of uncompressed data, with metadata such as the value range of every column kept for each one. That metadata is what pruning uses to skip files that cannot match a filter.

This is the multi-version scheme described in MVCC and snapshots, with the file as the unit of versioning. A table version is a set of file identifiers. Reading the table at a point in time means reading the set that was current then. Five behaviors follow, and the rest of the article takes them in turn.

• A query result stays valid exactly as long as the set of files it read is unchanged, which is the basis of result reuse.
• A file’s contents never change, so a copy of it held on a warehouse’s disks can never be stale; it can only become unreferenced.
• Time Travel is the decision to keep removed files for a while instead of deleting them.
• A clone is a new list of file identifiers pointing at existing files, so it copies no data.
• Storage cost grows with the files a write replaces, and that depends on how the changed rows are spread across files, not on how many rows changed.

The model below captures this and nothing else. A file store holds immutable tuples of rows; a table is a list of versions, each a set of file ids with a timestamp in days; an update rewrites every file that contains a matching row, and the replaced files are recorded with the time they left the current version. Rows are (key, day, amount) tuples, and a row is given a hypothetical 100 bytes so storage can be reported in bytes. The storage method sorts files into active, within the retention window (time travel) and within the following fail-safe period.

"""Stand-in model of versioned, immutable files (not Snowflake code)."""
import itertools

ROW_BYTES = 100                       # hypothetical bytes per row
_ids = itertools.count(1)

class FileStore:
    """Immutable files shared by tables and clones."""
    def __init__(self):
        self.files = {}               # file_id -> tuple of rows (key, day, value)
    def add(self, rows):
        fid = next(_ids)
        self.files[fid] = tuple(rows)
        return fid

class Table:
    def __init__(self, store, retention_days=1, failsafe_days=7):
        self.store, self.retention, self.failsafe = store, retention_days, failsafe_days
        self.versions = [(0.0, frozenset())]   # (time in days, set of file ids)
        self.removed = {}                      # file_id -> time the file left the current version
    @property
    def current(self):
        return self.versions[-1][1]
    def _commit(self, now, add, remove):
        new = (self.current - set(remove)) | set(add)
        for f in remove:
            self.removed[f] = now
        self.versions.append((now, frozenset(new)))
    def insert(self, now, rows, rows_per_file):
        add = [self.store.add(rows[i:i + rows_per_file]) for i in range(0, len(rows), rows_per_file)]
        self._commit(now, add, [])
    def update(self, now, pred, fn):
        """Rewrite every file that holds a matching row; returns number of files rewritten."""
        add, remove = [], []
        for f in self.current:
            rows = self.store.files[f]
            if any(pred(r) for r in rows):
                add.append(self.store.add([fn(r) if pred(r) else r for r in rows]))
                remove.append(f)
        self._commit(now, add, remove)
        return len(remove)
    def rows_at(self, t):
        """Read the version that was current at time t, if its files are still retained."""
        files = [fs for (ts, fs) in self.versions if ts <= t][-1]
        return sorted(r for f in files for r in self.store.files[f])
    def retained_ok(self, now, t):
        return now - t <= self.retention
    def clone(self, now, at=None):
        c = Table(self.store, self.retention, self.failsafe)
        files = self.current if at is None else [fs for (ts, fs) in self.versions if ts <= at][-1]
        c.versions = [(now, frozenset(files))]          # copies the file list, not the files
        return c
    def storage(self, now):
        """Bytes by state: active, time travel, fail-safe (files removed longer ago are purged)."""
        size = lambda ids: sum(len(self.store.files[f]) for f in ids) * ROW_BYTES
        active = size(self.current)
        tt = size(f for f, t in self.removed.items() if now - t < self.retention)
        fs = size(f for f, t in self.removed.items()
                  if self.retention <= now - t < self.retention + self.failsafe)
        return active, tt, fs

Two fixes of the same size make the central point. A table holds 20 days of orders at 100 rows per day, loaded in day order, so each file holds one day. One correction changes 50 rows that all belong to day 7; the other changes 50 rows spread across all 20 days.

from vtable import *

def build():
    t = Table(FileStore(), retention_days=1)
    # 20 days of orders, 100 rows per day, loaded in day order: one file per day
    t.insert(0.0, [(day * 100 + i, day, 10.0) for day in range(20) for i in range(100)], 100)
    return t

fix = lambda r: (r[0], r[1], 11.0)
for name, pred in [("clustered: 50 rows, all from day 7", lambda r: r[1] == 7 and r[0] % 2 == 0),
                   ("scattered: 50 rows, spread over days", lambda r: r[0] % 40 == 0)]:
    t = build()
    active0 = t.storage(0.0)[0]
    changed = sum(1 for f in t.current for r in t.store.files[f] if pred(r))
    n = t.update(1.0, pred, fix)
    active, tt, fs = t.storage(1.0)
    print(f"{name}\n  rows changed {changed}, files rewritten {n}, "
          f"time-travel bytes {tt} ({tt / active0:.0%} of table)")

clustered: 50 rows, all from day 7
  rows changed 50, files rewritten 1, time-travel bytes 10000 (5% of table)
scattered: 50 rows, spread over days
  rows changed 50, files rewritten 20, time-travel bytes 200000 (100% of table)

Both statements change 50 rows out of 2,000. The clustered fix replaces one file and leaves 5% of the table’s bytes in the retention window. The scattered fix replaces every file and leaves a full copy of the table behind. Row counts do not predict the cost; the number of distinct files touched does.

One caveat on the model’s granularity. The paper describes whole-file replacement, and the model uses it as the worst case. Snowflake’s current documentation on storage for Time Travel and Fail-safe says usage is calculated as a percentage of the table that changed, and that only the information needed to restore changed rows is kept, apart from drops and truncates. The exact physical retention unit is therefore something to measure on your own tables with the storage metrics view shown later, rather than assume from either description. The qualitative rule, that scattered changes retain more than clustered ones, is what the model is for.

Caching: three places an answer can come from

A query’s answer or its inputs can be found in more than one place before the engine reads remote storage. The caches differ in what they hold, how long they live, and who can use them, and a measurement is only meaningful if you know which one it hit.

Persisted results

When a query finishes, Snowflake keeps its result, and an identical later query can return that stored result instead of executing. The documentation lists the conditions: the new query must match the earlier one exactly, and it states that any difference in syntax, including lowercase versus uppercase or the use of table aliases, inhibits reuse. The query cannot use functions such as UUID_STRING, RANDOM or RANDSTR, external functions, or hybrid tables. The table data must be unchanged, the micro-partitions must not have been reclustered or consolidated, relevant configuration options must be unchanged, and the executing role must have the required privileges on the tables. A persisted result is purged after 24 hours; each reuse resets that period, up to 31 days from the first execution. The parameter USE_CACHED_RESULT turns reuse on or off at account, user or session level, and it is on by default. The result cache glossary entry summarizes these rules; the conditions are easier to remember once each is traced to the versioned-file model.

Condition	Why it exists, in the file model
Exact query text	The stored result is looked up by the statement, so a different spelling is a different key even when the meaning is identical.
No non-deterministic or external functions	A result that depends on randomness or outside state is not a function of the table’s files, so replaying it would be wrong.
Table data unchanged	The result is valid for one set of files. Any write that replaces a file the query read changes the set.
No reclustering or consolidation	These operations replace files without changing any row, but the file set differs, so the stored result is not reused.
Role has privileges on the tables	The stored result must not become a way to read data the new caller could not read.

The stand-in engine below adds a result cache keyed by the exact query text and the table’s version number, a local disk cache of file indexes, and pruning on per-file day ranges. Reading a file costs a hypothetical 100 ms from remote storage and 10 ms from the local cache; these constants are invented, and only the ratio and the mechanism matter.

"""Stand-in for a query engine with pruning, a local file cache and a result cache."""
import random

REMOTE_MS, LOCAL_MS = 100, 10          # hypothetical read time per file: remote storage vs warehouse disk cache

class Engine:
    def __init__(self, files, version=1):
        self.files = files             # list of (min_day, max_day, rows) -- per-file metadata + data
        self.version = version         # changes on every write
        self.local = set()             # file indexes cached on the warehouse's disks
        self.results = {}              # (exact query text, table version) -> result
    def suspend(self):
        self.local.clear()             # documented: the warehouse cache is dropped on suspend and resize
    def write(self):
        self.version += 1
    def run(self, sql, lo, hi, use_result_cache=True, deterministic=True):
        key = (sql, self.version)
        if use_result_cache and deterministic and key in self.results:
            return self.results[key], 0, 0, "result cache"
        scanned = [i for i, (mn, mx, _) in enumerate(self.files) if mx >= lo and mn <= hi]   # prune on min/max
        ms = sum(LOCAL_MS if i in self.local else REMOTE_MS for i in scanned)
        self.local.update(scanned)
        total = sum(v for i in scanned for (d, v) in self.files[i][2] if lo <= d <= hi)
        if deterministic:
            self.results[key] = total
        return total, ms, len(scanned), "scan"

def make_table(layout, days=200, rows_per_day=10, per_file=50, seed=1):
    rnd = random.Random(seed)
    rows = [(d, rnd.randint(1, 100)) for d in range(days) for _ in range(rows_per_day)]
    if layout == "scattered":
        rnd.shuffle(rows)              # arrival order unrelated to day
    files = []
    for i in range(0, len(rows), per_file):
        chunk = rows[i:i + per_file]
        files.append((min(d for d, _ in chunk), max(d for d, _ in chunk), chunk))
    return files

Seven runs of one aggregate over 10 days of a 200-day table, sorted by day so the filter prunes to 2 of 40 files, show how reuse behaves.

from warehouse import *
Q = "SELECT SUM(amount) FROM orders WHERE order_day BETWEEN 40 AND 49"
e = Engine(make_table("sorted"))
print(f"table: {len(e.files)} files")
def show(label, r): print(f"{label:46s} -> {r[3]:12s} files={r[2]:3d} ms={r[1]}")
show("1 first run", e.run(Q, 40, 49))
show("2 identical text again", e.run(Q, 40, 49))
show("3 lower-case 'select', result cache on", e.run(Q.replace("SELECT", "select"), 40, 49))
show("4 same text after a write to the table", (e.write(), e.run(Q, 40, 49))[1])
show("5 same text, again", e.run(Q, 40, 49))
show("6 non-deterministic query (RANDOM-like)", e.run(Q + " ORDER BY RANDOM()", 40, 49, deterministic=False))
e.suspend()
show("7 after suspend, result cache off", e.run(Q, 40, 49, use_result_cache=False))

table: 40 files
1 first run                                    -> scan         files=  2 ms=200
2 identical text again                         -> result cache files=  0 ms=0
3 lower-case 'select', result cache on         -> scan         files=  2 ms=20
4 same text after a write to the table         -> scan         files=  2 ms=20
5 same text, again                             -> result cache files=  0 ms=0
6 non-deterministic query (RANDOM-like)        -> scan         files=  2 ms=20
7 after suspend, result cache off              -> scan         files=  2 ms=200

Run 2 never touches storage: same text, same version, so the answer comes from the result cache. Run 3 differs only by lower-case select and misses the result cache, but it is still cheap, 20 ms instead of 200, because the two files it reads are in the local cache from run 1. That distinction between two caches matters in practice. A write in run 4 changes the table version and forces a scan; the model’s write replaces no files, so the local cache still serves the reads, whereas a real write that replaced files would also pay remote reads for the new ones. Run 5 hits the new result. A query marked non-deterministic is never stored, and after a suspend with the result cache disabled in run 7 the full remote cost returns.

The warehouse’s local cache

A running virtual warehouse keeps table data it has read on its own disks, and later queries on the same warehouse read from there when they can. The Snowflake documentation states that the cache is dropped when the warehouse is suspended and also when it is resized. The 2016 paper adds how the cache is organized: files are assigned to worker nodes by consistent hashing over file names, so repeated reads of a file land on the same node, and when the set of nodes changes the assignment is adjusted lazily and LRU replacement gradually refills the caches. Because files are immutable, nothing in this cache ever needs invalidation, which is why a simple scheme works.

The cache creates a direct trade-off with the auto-suspend setting, which exists to stop idle warehouses from consuming credits. A short timeout saves credits but discards the cache every time users pause; a long timeout keeps the cache and bills the idle tail. The documentation recommends different timeouts for different workloads, around 10 minutes or more for BI-style SELECT workloads whose users benefit from the cache, about 5 minutes for ad hoc work, and immediate suspension for task-driven warehouses. The simulation makes the shape of the trade-off concrete under an assumed workload.

The workload is hypothetical: over 10 hours, users arrive in sessions of 3 to 8 queries, 20 seconds apart, with a mean of 12 minutes between sessions. The first query after a resume reads cold and takes 7 seconds; later queries find a warm cache and take 2 seconds. Cost follows the documented X-Small rate of 1 credit per hour, billed per second with a 60-second minimum each time the warehouse starts, including the idle seconds before it suspends. Resume delay and queueing are not modeled, and the assumption that only the first query is cold is generous to short timeouts.

import random

RATE = 1.0              # credits per hour: X-Small, per the Snowflake warehouse documentation
MIN_BILL = 60           # seconds billed at least each time the warehouse starts
WARM_S, COLD_S = 2.0, 7.0    # hypothetical query latency with / without a warm local cache

def workload(seed=11, hours=10):
    """Hypothetical BI usage: sessions of 3-8 queries, 20 s apart, with random gaps between sessions."""
    rnd, t, out = random.Random(seed), 0.0, []
    while t < hours * 3600:
        t += rnd.expovariate(1 / 720)               # mean 12 minutes between sessions
        for k in range(rnd.randint(3, 8)):
            out.append(t + 20 * k)
        t = out[-1]
    return [x for x in out if x < hours * 3600]

def simulate(arrivals, suspend_after):
    billed, starts, lat, last_end, running, warm = 0.0, 0, [], None, False, False
    seg_start = None
    for a in arrivals:
        if running and a - last_end > suspend_after:           # idle long enough: it suspended
            billed += max(MIN_BILL, (last_end + suspend_after) - seg_start)
            running, warm = False, False                       # suspend drops the local cache
        if not running:
            running, seg_start, starts = True, a, starts + 1
        lat.append(WARM_S if warm else COLD_S)
        # the first query after a resume reads cold; later ones are warm only if their files were cached.
        warm = True
        last_end = a + lat[-1]
    billed += max(MIN_BILL, (last_end + suspend_after) - seg_start)
    return billed / 3600 * RATE, starts, sum(lat) / len(lat)

arr = workload()
print(f"{len(arr)} queries over 10 h")
print(f"{'auto-suspend':>12s} {'credits':>8s} {'starts':>7s} {'mean latency':>13s}")
for T in (60, 300, 600, 1800, 3600):
    c, s, l = simulate(arr, T)
    print(f"{T:10d} s {c:8.2f} {s:7d} {l:11.2f} s")

275 queries over 10 h
auto-suspend  credits  starts  mean latency
        60 s     2.08      47        2.85 s
       300 s     4.59      29        2.53 s
       600 s     6.49      17        2.31 s
      1800 s     9.02       3        2.05 s
      3600 s    10.33       2        2.04 s

Going from a 60-second to a 600-second timeout cuts cold starts from 47 to 17 and mean latency from 2.85 to 2.31 seconds, and costs 4.4 more credits over the 10 hours. That is about 0.15 credits for each cold start avoided, each of which saves 5 seconds for the person waiting. Whether that is a good price depends on who waits: for an executive dashboard it may be, for a nightly job it never is. The curve flattens once the timeout exceeds most of the gaps between sessions, and 3,600 seconds buys almost nothing over 1,800 while adding 1.3 credits. The point to carry over is the method: the right timeout is read from the gap distribution of the workload on that warehouse, which is one more reason to give each workload class its own warehouse instead of tuning one setting for a mixed one.

Measuring a query honestly

Consider the speedup that nobody could reproduce. Two table layouts of the same data, one with rows in arrival order and one sorted by day, run the same query twice. The naive report takes the second run of each.

from warehouse import *
Q = "SELECT SUM(amount) FROM orders WHERE order_day BETWEEN 40 AND 49"

print("naive: run twice, report the second run")
for layout in ("scattered", "sorted"):
    e = Engine(make_table(layout))
    e.run(Q, 40, 49)
    total, ms, nfiles, how = e.run(Q, 40, 49)
    print(f"  {layout:10s} second run: {ms} ms via {how}")

print("controlled: result cache off, cold and warm local cache, files scanned recorded")
for layout in ("scattered", "sorted"):
    e = Engine(make_table(layout))
    cold = e.run(Q, 40, 49, use_result_cache=False)
    warm = e.run(Q, 40, 49, use_result_cache=False)
    print(f"  {layout:10s} files {cold[2]:2d}/{len(e.files)}  cold {cold[1]:5d} ms  warm {warm[1]:4d} ms  result {cold[0]}")

naive: run twice, report the second run
  scattered  second run: 0 ms via result cache
  sorted     second run: 0 ms via result cache
controlled: result cache off, cold and warm local cache, files scanned recorded
  scattered  files 40/40  cold  4000 ms  warm  400 ms  result 4500
  sorted     files  2/40  cold   200 ms  warm   20 ms  result 4500

The naive measurement reports 0 ms for both layouts, because the second run was served from the result cache; the layout difference has vanished from the data. The controlled measurement turns the result cache off and separates cold from warm. The sorted layout scans 2 of 40 files and takes 200 ms cold; the scattered layout scans all 40 and takes 4,000 ms, a 20x difference that the pruning metadata explains and that reflects the real work. The warm figures, 20 ms and 400 ms, keep the same ratio but are 10x smaller than the cold ones, which is why comparing a warm run of one layout to a cold run of the other would produce a wrong answer in either direction.

A protocol follows from this, and each step has a Snowflake setting or observable behind it:

• Disable result reuse for the session before the measurement (ALTER SESSION SET USE_CACHED_RESULT = FALSE), and restore it afterward.
• Run each variant on a warehouse whose cache state you control. Suspending the warehouse, or resizing it, drops the local cache per the documentation, so a cold run starts from there; a warm run is a second execution on the same running warehouse.
• Record the cache state with each number. Query History and the Query Profile report percentage scanned from cache, bytes scanned, and partitions scanned against partitions total, and the profile shows a Query Result Reuse operator when a result was reused.
• Compare partitions scanned and bytes scanned across variants before comparing time. These do not depend on cache state, and they explain a time difference when there is one.
• Repeat each variant several times and compare distributions. The model is deterministic; a real warehouse also has other users, which is why a single run proves little.

-- Illustrative statements, not run against an account.
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT SUM(amount) FROM orders WHERE order_day BETWEEN '2024-02-09' AND '2024-02-18';
-- then read bytes scanned, partitions scanned/total and percentage scanned from cache
-- from Query History or the Query Profile for this statement
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

The same discipline applies in reverse for dashboards. If a dashboard query text is identical for every viewer and the underlying tables change only at load time, result reuse makes the second and later viewers cheap, which is a design benefit worth having: keep the text stable by generating it from one template, and avoid including per-request elements such as a timestamp literal or a random token, which make every query unique. A load that finishes late in the evening and invalidates the results just before a morning peak is the other side of the same fact, and one remedy is a warm-up query that runs the dashboard’s statements after the load.

Time Travel: keeping the files a statement removed

If a write only adds new files and marks old ones as no longer part of the current version, then the old version can be read as long as the old files exist. Snowflake Time Travel is the policy that keeps them for a retention period. The documentation lists three capabilities within that period: querying data as it was before it was updated or deleted, cloning tables, schemas and databases at a past point, and restoring dropped objects with UNDROP. The syntax selects the past state by timestamp, by offset in seconds, or by the ID of a statement.

-- Illustrative statements, not run against an account.
SELECT * FROM orders AT(TIMESTAMP => '2024-02-09 08:00:00'::timestamp_tz);
SELECT * FROM orders AT(OFFSET => -300);                      -- five minutes ago
SELECT * FROM orders BEFORE(STATEMENT => '<query id of the bad MERGE>');

CREATE TABLE orders_fixed CLONE orders BEFORE(STATEMENT => '<query id of the bad MERGE>');
UNDROP TABLE orders;

The retention period depends on edition and table type, and it is a property you set rather than a given.

Table type	Time Travel retention	Fail-safe	Longest recoverable history
Permanent, Standard Edition	0 or 1 day (default 1)	7 days	8 days
Permanent, Enterprise Edition or higher	0 to 90 days (default 1)	7 days	97 days
Transient	0 or 1 day (default 1)	None	1 day
Temporary	0 or 1 day (default 1)	None	1 day

Fail-safe is a different thing. It is a seven-day period after Time Travel ends, during which only Snowflake can recover the data, by a support request, on a best-effort basis that may take hours to days. The documentation states it is not a means of accessing historical data after the retention period; a platform’s recovery plan should treat Time Travel as the only recovery path under the team’s own control. A retention of 0 days disables Time Travel for the object. Raising the retention keeps data scheduled for removal longer but does not bring back data already in Fail-safe; lowering it lets data outside the new window move to Fail-safe through a background process rather than at once.

Worked recovery

A hypothetical Friday statement uses the wrong predicate and zeroes the amount for three of five days in an orders table. It is found six hours later. The model replays the sequence: the statement runs at day 1.0, retention is 1 day, the problem is found at 1.25, and the repair clones the table as it stood just before the statement.

from vtable import *
store = FileStore()
orders = Table(store, retention_days=1)
orders.insert(0.0, [(i, i // 100, 10.0) for i in range(500)], 100)      # 5 files, one per day
good_total = sum(r[2] for r in orders.rows_at(0.0))

# Day 1, 09:00: a MERGE with a wrong predicate zeroes the amount of every row of days 2-4
BAD = 1.0
orders.update(BAD, lambda r: r[1] >= 2, lambda r: (r[0], r[1], 0.0))
print("expected total:", good_total, "| total after bad statement:",
      sum(r[2] for r in orders.rows_at(BAD)))

NOW = 1.25                                  # found six hours later
BEFORE = BAD - 0.001                        # "just before the statement"
print("inside retention window:", orders.retained_ok(NOW, BEFORE))
files_before = len(store.files)
fixed = orders.clone(NOW, at=BEFORE)        # CREATE TABLE fixed CLONE orders BEFORE(STATEMENT => ...)
print("clone total:", sum(r[2] for r in fixed.rows_at(NOW)))
print("clone holds", len(fixed.current), "file ids;",
      len(fixed.current & orders.current), "are also in the live table;",
      len(fixed.current - orders.current), "exist only because the window retained them")
print("files in the store before and after the clone:", files_before, "and", len(store.files))
print("three days later, inside window:", orders.retained_ok(4.0, BEFORE))

expected total: 5000.0 | total after bad statement: 2000.0
inside retention window: True
clone total: 5000.0
clone holds 5 file ids; 2 are also in the live table; 3 exist only because the window retained them
files in the store before and after the clone: 8 and 8
three days later, inside window: False

The live table now sums to 2,000 instead of 5,000. Reading just before the statement gives back 5,000, and the clone holds five file ids, two of which are still in the live table and three of which exist only because the retention window kept the files the bad statement replaced. The store holds 8 files before and after the clone, so the clone copied no data. Three days later the same read is outside the window and fails. In Snowflake the recovery then continues with decisions that are yours and that no feature makes for you: compare the clone with the live table, then either swap them or merge only the affected rows back. ALTER TABLE orders SWAP WITH orders_fixed renames the two in one transaction and needs ownership of both tables, but a swap discards writes that landed on the live table after the bad statement, so a table that kept loading for six hours needs a targeted merge from the clone instead.

Three documented rules shape runbooks. Creating an object with the name of a dropped one creates a new object and does not restore the dropped one, so UNDROP must come before any re-creation. When a database or schema is dropped, its children are retained for the parent’s period, not their own. And a clone made while the retention is 0 can fail with a data-not-available error if transactions are running. The write-side counterpart of a recovery runbook is a pipeline that can be rerun safely; see Building Incremental and Idempotent Pipelines, since recovering the table is only half of repairing the day’s load.

Time Travel is also not the same as an open table format’s snapshots, though it is the same idea. Iceberg and Delta tables keep named versions that the owner expires explicitly, as described in Open Table Formats: Snapshots, Transactions, and Catalogs, whereas Snowflake’s window is a retention parameter on the object. The window also works against erasure: deleted rows remain readable until retention and Fail-safe end, a constraint treated in Access, Retention, and Erasure Across Data Copies.

What retention costs: rewritten files, not changed rows

Retained files occupy storage and are billed as such. How much is retained per day is the number of files each write replaced, so the unit of cost is the file. For a table of N files where each of k changed rows falls in a uniformly random file, the expected number of files replaced is

\[ E[\text{files replaced}] = N\left(1 – \left(1 – \tfrac{1}{N}\right)^{k}\right) \]

Here N is the file count and k the number of changed rows; the term in parentheses is the chance that one particular file receives none of the k rows. With N = 1,000 and k = 2,000, about 86.5% of the files are replaced, although only 2% of the rows changed. If the same k rows are all in the newest 20 files, 2% of the files are replaced. The simulation applies a daily 2,000-row change to a 100,000-row table of 1,000 files under both patterns, for 120 days, and reports retained bytes as a multiple of the table’s size for different retention settings. The fail-safe period is 7 days as documented, and the daily change count and row size are hypothetical.

import random
from vtable import *

FILES, ROWS = 1000, 100                      # 100,000 rows, 1,000 files, 10 MB at 100 B/row
DAILY = 2000                                 # rows changed per day (2% of the table)

def run(mode, retention, days=120, seed=3):
    rnd = random.Random(seed)
    t = Table(FileStore(), retention_days=retention, failsafe_days=7)
    t.insert(0.0, [(i, i // ROWS, 1.0) for i in range(FILES * ROWS)], ROWS)   # file k holds keys k*100..k*100+99
    base = t.storage(0.0)[0]
    for d in range(1, days + 1):
        if mode == "scattered":              # keys anywhere in the table
            keys = set(rnd.sample(range(FILES * ROWS), DAILY))
        else:                                # the newest 20 files' keys: recent partition only
            lo = (FILES - 20) * ROWS
            keys = set(range(lo, lo + DAILY))
        t.update(float(d), lambda r: r[0] in keys, lambda r: (r[0], r[1], r[2] + 1))
    a, tt, fs = t.storage(float(days))
    return base, a, tt, fs

print(f"{'pattern':10s} {'retention':>9s} {'time travel / table':>20s} {'fail-safe / table':>18s}")
for mode in ("scattered", "clustered"):
    for ret in (1, 7, 30, 90):
        base, a, tt, fs = run(mode, ret)
        print(f"{mode:10s} {ret:7d} d {tt / base:19.2f}x {fs / base:17.2f}x")

pattern    retention  time travel / table  fail-safe / table
scattered        1 d                0.89x              6.07x
scattered        7 d                6.11x              6.08x
scattered       30 d               26.10x              6.11x
scattered       90 d               78.14x              6.07x
clustered        1 d                0.02x              0.14x
clustered        7 d                0.14x              0.14x
clustered       30 d                0.60x              0.14x
clustered       90 d                1.80x              0.14x

The expected-value formula predicts 0.865 table sizes per day for the scattered pattern, and the simulation agrees: 0.89 for a 1-day window (one day’s batch, close to the expected value), 6.1 for 7 days, 26 for 30 days and 78 for 90 days. With scattered updates, a 90-day retention holds history equal to 78 tables. The clustered pattern holds 1.8 table sizes at 90 days, a 43-fold difference for the same number of changed rows. Fail-safe adds a fixed 7 days of the same churn regardless of retention: 6.1 table sizes under the scattered pattern and 0.14 under the clustered one, which is why the fail-safe line barely moves with the retention setting but is large for a high-churn table.

Three design rules follow, and each reduces the bytes replaced instead of only the retention. Set retention per table from its recovery requirement and its churn, not account-wide: a rarely changed dimension can carry 30 days, while a table that is rewritten daily across its whole width costs about as much per retained day as the table itself. Arrange load and update patterns so changes concentrate in few files, for example by loading in an order that matches the dominant update key and by merging recent partitions only; the layout work in Partitioning, Compaction, and Table Maintenance applies, and reclustering itself replaces files, so it also generates retained bytes. And make intermediate tables transient, so a staging table that is fully rewritten every run carries at most one day of Time Travel and no Fail-safe.

-- Illustrative statements, not run against an account.
CREATE TRANSIENT TABLE stg_orders_load (order_id NUMBER, order_day DATE, amount NUMBER(12,2))
  DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE dim_customer SET DATA_RETENTION_TIME_IN_DAYS = 30;   -- Enterprise Edition or higher

SELECT table_name, active_bytes, time_travel_bytes, failsafe_bytes, retained_for_clone_bytes
FROM my_db.information_schema.table_storage_metrics
WHERE table_schema = 'SALES'
ORDER BY time_travel_bytes + failsafe_bytes DESC;

The metrics view is the way to check whether a real table behaves like the model: TIME_TRAVEL_BYTES and FAILSAFE_BYTES as multiples of ACTIVE_BYTES show which tables carry the most history. The cost of a retention decision in money, together with backups and cross-region copies, is worked through in Disaster Recovery and Cost Engineering for Data Platforms; the point here is that the multiples above come from the update pattern and can be reduced at the source.

Clones as environments

A clone copies a list of files, so a full production-sized copy of a schema is available in seconds. The documentation says a clone shares storage with its source until either side changes, and then each new file is stored separately; cloned objects do not inherit the source’s privileges unless COPY GRANTS is specified for table and view creation, and unconsumed stream records are not cloned. That makes a clone a good fit for testing a migration or a model change against real data volumes, and for the repair pattern above.

Storage ownership has a cost pattern worth knowing. The table-storage view documentation says storage bytes are owned by, and billed to, the table where they were first added; when a clone shares files with its source and the source later replaces them, the replaced files are kept because the clone still references them and are reported as RETAINED_FOR_CLONE_BYTES. A long-lived dev clone of a table that production rewrites heavily therefore holds the old files for as long as the clone exists, even if retention is short. The practical rules are to give clones an owner and an expiry, drop them when the test ends, and watch the retained-for-clone figure on high-churn tables.

Clones fit an environment-promotion flow where pipeline code is tested against data shaped like production, as in Data Pipeline CI/CD and Environment Promotion. They do not remove the need to decide what the test environment may contain: a clone of a production table contains its personal data, with the same erasure obligations, and masking and access rules have to be applied to the clone as deliberately as to the source.

Putting the mechanisms into a design

Consider a hypothetical analytics platform with five workloads on one account: a nightly load into raw tables, hourly transformations, executive dashboards, ad hoc analyst queries, and a test environment for pipeline changes. Isolating them on separate warehouses, setting queue timeouts and capping batch concurrency are covered in the workload-isolation article. The decisions above add a second layer to each warehouse and table, and they differ across the workloads.

Workload	Warehouse suspend setting	What to rely on	Table type and retention	Measurement note
Nightly load	Suspend soon after the load; nothing benefits from a warm cache	Idempotent reruns; Time Travel on the targets for a bad load	Raw tables permanent with retention from the recovery window; load staging transient with 0 days	Judge by rows per credit, not latency
Hourly transformations	Short; cache hits from one run rarely help the next	Clustered changes: merge recent partitions only	Marts permanent with moderate retention; scratch tables transient	Track partitions replaced per run, since that sets retained bytes
Executive dashboards	Long enough to bridge the gaps inside a session (the simulation’s curve)	Result reuse by stable query text; local cache for the rest	Read-only consumers of marts; no retention needed beyond the table’s own	Disable result reuse when benchmarking, enable it in production
Ad hoc analysis	About 5 minutes, in line with the documented guidance for ad hoc work	Pruning on well-laid-out tables; Time Travel reads to compare with yesterday	Reads only; analysts get clones for experiments with an expiry	Compare bytes and partitions scanned
Test environment	Short; tests tolerate cold reads	Clones of production schemas, dropped after the run	Transient clones or schemas	Cost of clones appears as retained-for-clone bytes on the source

Nothing in the table is a Snowflake default; each cell is a choice with a stated mechanism, and each should be revisited when the workload’s pattern changes. The suspend setting follows the gap distribution, the result-reuse policy follows how repeatable the query text is, and the retention follows both the recovery requirement and the churn. These are the quantities that the model programs compute, and a team can compute the same quantities from query history and the storage metrics view before choosing.

Failure modes

Failure	Mechanism	Mitigation
A benchmark shows an implausible speedup	The second run was served by result reuse or a warm cache	Disable result reuse, record cache state, compare bytes and partitions scanned
Dashboards are slow only in the first minutes of the day	The warehouse suspended overnight, and the cache and results expired or were invalidated by the load	Warm-up queries after the load; a suspend setting matched to the gap distribution
Result reuse never happens for a dashboard	Query text differs per request, a non-deterministic function is used, or the table changes often	Generate text from one template; remove per-request literals; separate volatile parts
Recovery is impossible on Monday	Retention is 1 day, or was lowered, and the problem predates the window	Set retention from the recovery requirement; verify the setting; keep an independent backup
Retention bill grows faster than the data	High-churn table with scattered updates, long retention	Concentrate changes, shorten retention, make staging transient, monitor time-travel bytes
Swap after recovery loses new writes	The swap replaces the live table with a copy from before the later loads	Merge affected rows from the clone, or pause writers and rerun later loads
Dev clones keep inflating storage	Clones reference files the source has replaced	Expire clones; watch retained-for-clone bytes
Erased data is still readable	Deleted rows persist through Time Travel and Fail-safe	Plan erasure around the retention window, as covered in the erasure article

References

• Snowflake Documentation, Using persisted query results
• Snowflake Documentation, Optimizing the warehouse cache
• Snowflake Documentation, Understanding & using Time Travel
• Snowflake Documentation, Understanding and viewing Fail-safe
• Snowflake Documentation, Storage costs for Time Travel and Fail-safe
• Snowflake Documentation, TABLE_STORAGE_METRICS view
• Snowflake Documentation, Cloning considerations
• Snowflake Documentation, Temporary and transient tables
• Snowflake Documentation, Overview of warehouses
• Dageville et al., The Snowflake Elastic Data Warehouse (SIGMOD 2016)