Snowflake Architecture and Engineering

Data Engineering & Modern Data Platforms · Lesson 53 · Modern Data Platforms and Cloud

Lesson Overview

Databricks Lakehouse Platform put a Delta commit behind completion-v3 when Spark was the writer and Unity Catalog was the grant boundary. Finance still wants a SQL warehouse that resumes for a dashboard, suspends when the office is empty, and does not make an engineer operate _delta_log. Snowflake is that engine. It stores tables in its own managed storage, prunes them with metadata on micro-partitions, and runs queries on virtual warehouses you size separately from storage. The lake prefixes from Data Lake Architecture remain the ingest record. A storage integration and a stage let COPY read a closed run prefix. The Snowflake transaction is the commit for any grain this platform serves.

This lesson is the architecture and the engineering of that landing. It is not a tour of every feature flag. Micro-partitions, warehouse sizing, stages, COPY, Snowpipe at the design level, time travel, fail-safe, zero-copy clones, and clustering are the mechanisms you need to load TechLearningPro without deleting the lake and without doubling a file. Credit rates change. The lesson names what consumes credits and points at Snowflake's credit documentation for the number.

If Databricks already serves completion, Snowflake is a replica that stores source_snapshot_id and does not reshape the metric, which is the rule from Data Lakehouse Architecture. If Snowflake is the server, the publication row lives here and the lakehouse table is not a second definition. Iceberg tables inside Snowflake are a documented interoperability feature. They are not assumed to have every behavior this lesson describes for native tables. Test the feature you promise.

Learning Objectives

By the end of this lesson you will be able to:

1. Explain storage, virtual warehouses, and cloud services as three layers, and say which layer a query actually spends.
2. Describe a micro-partition well enough to predict when a filter prunes and when a clustering key is wasted.
3. Size a load warehouse and a BI warehouse separately, with auto-suspend, and choose a multi-cluster policy for concurrency.
4. Load a closed S3 prefix through a storage integration and COPY INTO, without embedding keys and without PURGE on the lake.
5. Use load history and FORCE deliberately so a retry does not duplicate and a reload is a choice.
6. Set time-travel retention for the audit window and distinguish it from fail-safe.
7. Clone a table for a backfill test without copying bytes up front, and cluster only when pruning fails.
8. Place Snowpipe, streams, and tasks on the right clocks, and keep an open hour off the served publication.

Prerequisites

• Lesson 6 and lesson 7: SQL you will write inside this engine. Window functions do not change because the vendor did.
• Lesson 8: transactions and isolation. Snowflake's commit is the warehouse's, not a file rename.
• Lesson 10: grain and conformed dimensions. Native tables still need them.
• Lesson 13: ELT. Raw tables in Snowflake are a load of the lake, then SQL builds curated.
• Lesson 17: the gate before publication. A successful COPY is not the gate.
• Lesson 47: prefix freshness versus the commit readers use.
• Lesson 48: closed run prefixes and markers. COPY from the parent hour is the partial-read bug on a new engine.
• Lesson 49: one server or a replica with source_snapshot_id.
• Lesson 51: do not promise Iceberg parity you have not tested. This lesson's pruning story is the native micro-partition.

Detailed Core Concepts

Three layers

Snowflake's architecture separates three things that a single-host database glues together.

Storage holds table data as micro-partitions in the vendor's managed storage in your cloud region, and it holds internal stages. You pay for the compressed bytes over time. You do not provision disks. A drop of a table enters time travel and then fail-safe rather than freeing bytes in the same second, which matters when someone "cleans up" a raw table to save money and the bill barely moves for a week.

Virtual warehouses are compute clusters. An X-Small warehouse is the smallest size in the documented size list. Each step up (Small, Medium, Large, and onward through the sizes your edition lists) adds compute, historically about twice the servers of the previous size, and credit consumption scales with that size. Confirm the size list and the credit table in the current Snowflake documentation before you write a cost model. Warehouses do not share CPU with each other. A load on tlp_load_wh does not take cores away from tlp_bi_wh. They do read the same storage. That is the separation.

Cloud services authenticate, compile SQL, plan, and talk to the metadata that makes pruning possible. They also hold the result cache: an identical query against unchanged data can return from that cache, for a bounded time the docs specify, without using your warehouse. The cache is a gift to a dashboard that repeats a statement. It is not an SLO. A statement that is not identical, or data that changed, uses the warehouse. Do not size the warehouse at zero because you saw a cache hit on Tuesday.

A query's path is: cloud services compile and prune using metadata, the warehouse scans the micro-partitions that survived pruning, results return, and cloud services may cache them. If the warehouse is suspended, auto-resume starts it and the query waits for that start. The wait belongs in the dashboard's latency budget.

Micro-partitions

Snowflake stores a table as immutable micro-partitions. The documentation describes each as a contiguous unit of storage, on the order of 50 MB to 500 MB of uncompressed data, stored columnar and compressed. You do not set that size. The load and the INSERT or COPY produce them. Each micro-partition carries metadata: the range of values per column, null counts, distinct counts, and other statistics the planner uses. A filter that falls outside a partition's range skips that partition. The query profile shows partitions scanned versus partitions total. That ratio is the pruning story. A full scan of a small table is fine. A full scan of a year of progress events for one course, every Monday, is a clustering or a modeling problem.

Natural clustering follows insertion order. A COPY of event_date=2026-11-10 writes partitions that often prune well on event_date with no clustering key. Updates and merges of old days mix values into new micro-partitions and can degrade that order. A clustering key tells automatic clustering which columns to keep co-located. Automatic clustering consumes credits. Add a key when the profile shows poor pruning on a filter you actually run, such as event_date after months of random merges. Do not add a key on learner_id because it is the primary key. High cardinality and a filter you never apply is a credit leak. SYSTEM$CLUSTERING_INFORMATION is the documented function that reports clustering depth. Read it when you are deciding, not as a daily page.

Search optimization is a different service, for selective point lookups, and it also consumes credits. It is not a clustering key. Mention it only when a real point-lookup workload shows up. Completion's Monday query is an aggregate, not a point lookup.

Warehouses as products

Create two warehouses at minimum.

tlp_load_wh runs COPY and the merge into curated. It can be Small or Medium until a measured load says otherwise. It suspends after a short idle period. It does not serve dashboards.

tlp_bi_wh serves finance and the studio's SQL. It suspends quickly too. If many dashboards start together, a multi-cluster warehouse adds clusters up to a maximum. The scaling policy STANDARD favors concurrency and starts clusters more readily. ECONOMY favors fewer credits and allows queries to queue. A finance close that must not queue picks STANDARD and a max cluster count you can afford. A research warehouse picks ECONOMY or stays single-cluster. The minimum cluster count, if greater than one, keeps clusters running and burns credits even when idle. Leave the minimum at 1 unless you have measured a resume latency you cannot accept.

sqlCopy
CREATE WAREHOUSE IF NOT EXISTS tlp_load_wh
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 3600;

CREATE WAREHOUSE IF NOT EXISTS tlp_bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

AUTO_SUSPEND = 60 is seconds. A value of 0, where the parameter allows it, disables suspend. Do not set that on tlp_bi_wh. Statement timeouts stop a runaway Cartesian join from holding a cluster. They are part of the warehouse, not an afterthought.

A resource monitor caps credits. The documented shape is a monthly quota with triggers: notify at a threshold, suspend at the quota. Put the load warehouse and the BI warehouse on monitors with different quotas so a bad COPY loop cannot spend the dashboard's budget, and a dashboard cannot be the reason you notice the loop. The quota number is a business decision. The existence of the monitor is the engineering decision.

Warehouses accrue credits while resumed. The billing granularity, including any minimum charge when a warehouse resumes, is in the credit-consumption documentation. Read it when you estimate. A design that resumes a 2X-Large every minute to copy one file will look cheap in a diagram and loud on the monitor. Batch the files. Suspend.

Stages and the storage integration

A stage is a pointer to files. An external stage points at S3, Azure, or GCS. The identity should be a storage integration, not an access key in the stage definition. The integration is a Snowflake object that names an IAM role and the locations it may use. Only ACCOUNTADMIN, or a role you granted CREATE INTEGRATION, creates it. The handshake is: create the integration, DESC INTEGRATION, put STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID into the AWS role trust policy, and grant that role access to the prefix. The location list is an allow list. Keep it to the raw prefixes the load needs.

sqlCopy
CREATE STORAGE INTEGRATION IF NOT EXISTS tlp_s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/snowflake_tlp_raw'
  STORAGE_ALLOWED_LOCATIONS = (
    's3://tlp-raw/learning/',
    's3://tlp-raw/commerce/'
  );

CREATE STAGE IF NOT EXISTS raw.progress_stage
  URL = 's3://tlp-raw/learning/progress/'
  STORAGE_INTEGRATION = tlp_s3_int
  FILE_FORMAT = (TYPE = PARQUET);

STORAGE_ALLOWED_LOCATIONS does not include s3://tlp-landing/. The seat file's email never becomes a stage a transformer can browse. That is the lesson 13 boundary, enforced in the integration rather than in a slide.

Internal stages (@%table, @~, named internal) hold files Snowflake manages. Use them for exports you will load inside the account. Do not use them as the system of replay for progress. The lake prefix is that system, until you explicitly move the system of replay and extend retention. Two systems of replay with different clocks is how a restatement disagrees with itself.

COPY, load history, and PURGE

COPY INTO reads the stage and writes micro-partitions. For Parquet, match columns by name so a new field at the end of the file does not land in the wrong column:

sqlCopy
COPY INTO raw.progress
FROM @raw.progress_stage
PATTERN = '.*event_date=2026-11-10/hour=13/run_id=20261110T140500Z/.*'
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = 'ABORT_STATEMENT';

The PATTERN is the marked run prefix from lesson 48, not .*hour=13/.*, which would include an open run and a superseded run. ON_ERROR = ABORT_STATEMENT fails the load. SKIP_FILE and CONTINUE exist and will load a partial hour while reporting errors. Do not use them on purchase lines. A skipped file is a completeness hole. The lesson 17 gate would rather see a failed load than a short table with a green task.

Snowflake records files loaded successfully and skips them on later COPY for a retention window the COPY INTO documentation specifies (this has been 64 days for a long time; confirm it). A retry of the same statement does not duplicate those files. That is the idempotency of the load. FORCE = TRUE reloads them and will duplicate rows if the table already has them. Use FORCE only when you have removed the previous rows for that path, or when you are loading into an empty repair table. The orchestrator must not set FORCE as a default retry flag.

PURGE = TRUE deletes the staged files after a successful load. On an external stage that points at tlp-raw, purge deletes the lake. The topic may already have expired. The object was the surviving copy, from lesson 42. PURGE belongs on an internal stage you meant to empty. It does not belong on progress_stage. Put that in the code review checklist as a rejected token.

VALIDATION_MODE = RETURN_ERRORS (and the related validation modes in the same document) parses without loading. Use it when a new Parquet shape arrives and you want the error rows before a commit. It is not a substitute for the business gate.

Snowpipe is a loader, not a snapshot

Snowpipe runs COPY in response to file notifications, on Snowflake-managed compute, and it consumes credits under that service. It is the right tool when a closed object must land in raw within minutes and a scheduled task is too coarse. It is the wrong tool to point at a prefix the sink is still writing if the notification fires per object. Each object becomes visible in the raw table as it loads. A consumer who sums the raw table intra-hour rebuilds the promotion-hour bug from lesson 48 inside SQL. Snowpipe loads files. Publication still waits for the marker and the completeness check, then merges into curated and flips the publication row. If you cannot express "only this run prefix" in the notification path, stay with a scheduled COPY of the marker's prefix. Boring and closed beats continuous and partial.

Time travel, fail-safe, and clones

Time travel lets you query a table as of a timestamp, an offset, or a statement, and undrop objects, within DATA_RETENTION_TIME_IN_DAYS. The maximum depends on edition. Historically, Standard edition is one day and Enterprise and above can be set up to 90 days. Confirm the current edition matrix before you promise a 90-day audit in a contract. Set the parameter on the curated database to the audit window you are allowed, and set raw to the replay window you need. A default of one day on a table you thought was covered for a month is a quiet audit failure.

sqlCopy
SELECT *
FROM curated.completion
AT (TIMESTAMP => '2026-11-10 12:00:00'::TIMESTAMP_LTZ);

Prefer the publication row's statement id or a clone taken at publish time when the auditor needs "what we served," because a timestamp typed from memory sits on a clock. BEFORE (STATEMENT => '...') is documented if you saved the query id of the publish.

Fail-safe is a further seven-day period Snowflake documents as non-configurable, after time travel ends, for Snowflake to restore data in a disaster. Users do not query fail-safe. It is not your audit tool and it is not a place you tell an analyst to look. It does mean a dropped table can linger in the bill. Do not drop raw to save this week's credits and expect the storage line to fall on the same day.

Zero-copy clone creates a table or schema that shares micro-partitions until one side is modified:

sqlCopy
CREATE TABLE curated.completion_repair CLONE curated.completion;

The clone is the backfill sandbox. Changes to the repair table write new partitions and do not change the source until you explicitly merge them back. Clones are the right way to test a restatement. Copying the table with CREATE TABLE AS SELECT rewrites every byte and is the slow cousin. Clones still need the source's time-travel retention to be valid at clone time. A clone is not a backup in another region by itself. Replication and failover are separate documented features if the requirement is regional loss. Do not call a clone a disaster-recovery plan.

Streams, tasks, and the curated merge

A stream on raw.progress records changes so a task can merge only what landed. The stream offset advances when a DML statement consumes the stream successfully. A SELECT you run to debug may or may not be the consuming statement the current documentation describes. Read that page before you debug in production and assume the offset is unchanged. The safe pattern is a task whose body is a single merge that reads the stream, in a transaction, scheduled on tlp_load_wh.

sqlCopy
CREATE TASK curated.merge_completion
  WAREHOUSE = tlp_load_wh
  SCHEDULE = 'USING CRON 20 * * * * UTC'
  AS
  MERGE INTO curated.completion AS t
  USING (
    SELECT *
    FROM raw.progress_stream
  ) AS s
  ON t.learner_id = s.learner_id
   AND t.lesson_id = s.lesson_id
   AND t.attempt_number = s.attempt_number
   AND t.definition_version = 'completion-v3'
  WHEN MATCHED AND s.change_sequence > t.change_sequence THEN UPDATE SET
    completed_at = s.completed_at,
    event_time = s.event_time,
    change_sequence = s.change_sequence
  WHEN NOT MATCHED THEN INSERT (
    learner_id, lesson_id, attempt_number, definition_version,
    completed_at, event_time, change_sequence
  ) VALUES (
    s.learner_id, s.lesson_id, s.attempt_number, 'completion-v3',
    s.completed_at, s.event_time, s.change_sequence
  );

The stream in that sketch is only correct if it already exposes the curated grain. A stream on raw events does not. The real task body is the lesson 13 SQL that aggregates the window, restricted to keys the stream says changed, or a merge from a staged window table the COPY just filled. Do not paste a stream of raw events into a completion grain and hope the match key collapses them. The sketch shows the MERGE shape and the ordering predicate. The USING subquery in production is the aggregate, tested against a fixture. Hide that aggregate in a view with a name, curated.completion_window_v3, so the task is not the only copy of the definition.

The task must not run the open hour. Gate it on the marker, which may live in a small table the lake publisher writes, or on a control table the COPY updates only after a closed prefix loads and completeness passes. Then insert curated.publication. A stream that includes every new raw row will happily merge an open hour if you let it. The task's WHEN clause in the schedule is a poor place for that business rule. A control table is an explicit one.

Serverless tasks exist and bill differently from warehouse tasks. This design uses a warehouse task so the load warehouse is the one place you look when the merge is expensive. You can move to a serverless task after you have a credit measurement, not before.

Governance

Roles, not individual users, own grants. loader can insert into raw and cannot select curated into a personal schema to "fix" it. transformer can merge curated and insert publication. reporter can select the publication view and cannot select raw. A role with ACCOUNTADMIN is not the job's role. The storage integration stays with the platform role.

Masking policies and row access policies are documented features for columns you must store and still restrict. They are not how you handle email on the seat file. That column does not land, from lesson 13. Masking a column you should have dropped is a control after the miss. Use row access policies when a reporter may see one course and not another and the table is legitimately shared. They do not replace the grain.

Native tables and Iceberg

This lesson's pruning, clustering, clone, and time-travel behavior is the native table behavior Snowflake documents. Snowflake can also query Iceberg tables, with a catalog configuration that has changed as the feature has matured. If completion's server is the Iceberg snapshot from lesson 51, Snowflake might be a reader of that snapshot rather than a second copy. Read the current Iceberg limitations page before you promise clones, fail-safe, or clustering on that object. If the page says a feature applies to native tables, believe it. A replica loaded by COPY into a native table has the behaviors in this lesson and lags the source snapshot by the job interval. That is often the better finance landing, because the BI warehouse then has a boring native table and a visible source_snapshot_id.

Architecture and Design Diagrams

mermaidCopy
flowchart TB
    subgraph lake [Lake]
        marker[Closed run prefix]
        landing[Landing prefix excluded]
    end

    subgraph sf [Snowflake]
        integ[Storage integration]
        stage[External stage]
        rawT[raw tables]
        task[Merge task on tlp_load_wh]
        curatedT[curated native table]
        pub[publication]
        bi[tlp_bi_wh]
    end

    marker --> stage
    integ --> stage
    stage --> rawT
    rawT --> task
    task --> curatedT
    task --> pub
    pub --> bi
    landing -.-> stage

The dotted edge does not exist. The integration's allow list is why.

mermaidCopy
sequenceDiagram
    participant Job as Load task
    participant Stage as External stage
    participant Raw as raw.progress
    participant Cur as curated.completion
    participant Pub as publication

    Job->>Stage: COPY from the marked run prefix
    alt File already in load history
        Stage-->>Job: Skip file
    else New closed file
        Stage->>Raw: Write micro-partitions
    end
    Job->>Job: Completeness including dead letters
    alt Gate fails
        Job-->>Pub: Do not insert
    else Gate passes
        Job->>Cur: MERGE the window
        Job->>Pub: Insert served statement
    end

PURGE is absent. FORCE is absent. The open hour is not in the pattern.

Technical Explanation

What pruning looks like in a review

The query profile for a completion aggregate filtered to one event_date should scan a small fraction of partitions if the table was loaded day by day and not then rewritten at random. Record the fraction in the design note for the first month. When the fraction climbs after weeks of merges, look at clustering depth before you scale tlp_bi_wh from Medium to 2X-Large. Scaling the warehouse makes a full scan faster and more expensive. It does not fix the metadata. Clustering might. A daily aggregate table at course grain might be the better model, and it is smaller than a clustering project. Lesson 10 still applies: if Monday reads a grain you can store, store it. Do not scan the fact because the fact exists.

Load history versus business idempotency

File-level skip stops a retried COPY from reloading the same object. It does not stop two different run prefixes that contain the same event_id from both loading after a compaction published a new prefix and you copied both. The marker protocol says only the current run is authoritative. The PATTERN must name that run. A merge into curated on the grain then collapses anything that duplicated across a mistake. Defense in depth: the right prefix, then a merge, then a uniqueness check before publication. COPY into a raw table that is insert-only can hold duplicates from a bad pattern. Curated must not.

Credits you can attribute

WAREHOUSE_METERING_HISTORY in SNOWFLAKE.ACCOUNT_USAGE is the documented view for warehouse credit consumption. Snowpipe and automatic clustering show up in their own usage views, which the account-usage documentation lists. Attribute the publish to tlp_load_wh, the dashboards to tlp_bi_wh, and clustering to the clustering service if you turned it on. A single shared warehouse named COMPUTE_WH makes this attribution a guess. Rename and split before the first month of real load, not after finance asks why the number moved.

Security of the stage

The stage can list object keys. Keys were designed in lesson 48 to avoid learner_id. The Parquet body still has identifiers. reporter cannot LIST the stage. loader can. A user-stage (@~) full of exports of raw progress is a second copy with the user's privileges. Disable that habit. Unload curated aggregates to an internal stage with a short life if a partner extract is required, and keep the extract on the curated grain, not the raw event.

Edition and retention, written as a contract

Write DATA_RETENTION_TIME_IN_DAYS next to the table owner and the edition you bought. If the edition caps you at one day and the auditor wants thirty, the gap is a purchasing decision, not a SQL trick. Fail-safe does not close that gap for the auditor, because they cannot query it. A zero-copy clone into a longer-retention database is only available if the target allows it. Check the clone and retention pages before you invent a workaround that copies personal data into a second account.

Practical Implementation or Design Walkthrough

Problem

Load closed progress and purchase prefixes into Snowflake raw tables, merge a curated completion table if Snowflake is the server, or load a replica of a published Delta version if it is not. Keep BI off the load warehouse. Do not purge the lake.

Decide the role

For the walkthrough, Snowflake is the server for finance's copy of completion only if lesson 52 is not already publishing the metric. The default in this course, once both platforms exist, is: Databricks publishes completion-v3, Snowflake raw holds purchase lines for finance's own grain if they own that grain, and completion in Snowflake is a replica. The walkthrough shows the replica load and the native merge pattern, and it tells you to enable one of them as the server. Not both as writers of the metric.

Steps

1. Create roles loader, transformer, reporter. Create databases or schemas raw and curated. Grant reporter select on curated only.
2. Create the storage integration with the allow list limited to tlp-raw learning and commerce. Finish the IAM trust with DESC INTEGRATION. Create file format TYPE = PARQUET and the external stage. Confirm tlp-landing is absent.
3. Create raw.progress and raw.purchase_line with the contract columns. COPY a lab run prefix. Retry the same COPY. Row count does not double. Confirm load history skipped the files.
4. Run a validation mode on a deliberately bad file in a side prefix. Do not CONTINUE past it on the real table.
5. Create tlp_load_wh and tlp_bi_wh as above. Run COPY on the load warehouse. Run a select as reporter with the BI warehouse in the session. Show the load warehouse can suspend and the select still resumes only the BI warehouse.
6. If this account is the server: merge the lesson 13 window, run the uniqueness and completeness checks, insert curated.publication with the query id or a monotonic publish id. If this account is the replica: copy from the published snapshot's export or from an Iceberg share you have tested, and set source_snapshot_id. Do not recompute completion with a local tweak.
7. Clone curated completion to completion_repair. Change the clone. Show the source count unchanged.
8. Set retention to the audit window the edition allows. Document fail-safe as not an auditor query. Schedule clustering only if a month-later profile says pruning failed.
9. Search the task SQL for PURGE and FORCE. Both should be absent on the lake stage.

Expected evidence

A retry that does not duplicate, a stage allow list, two warehouse names on two statements, a publication or source_snapshot_id column, and a clone test. The query profile for a one-day filter shows pruning, or you have written why the table is too small for the ratio to matter.

Enterprise Real-World Scenarios

One warehouse, two jobs, a queued close

COMPUTE_WH is a Large warehouse with auto-suspend off, because suspend "made the first query slow." The morning COPY of a full raw prefix, including a mistaken parent-hour pattern, scans and writes for forty minutes. Finance's close dashboard queues behind it. Someone scales the warehouse to 2X-Large. Both the mistake and the dashboard get more expensive. The partial hour loads. Revenue moves. The dead-letter gap is invisible because ON_ERROR was CONTINUE during an earlier incident and nobody put it back.

The repair splits tlp_load_wh and tlp_bi_wh, sets suspend to 60 seconds, and sets the load pattern to the marker's run prefix. ON_ERROR returns to ABORT_STATEMENT. Completeness runs before publication. The Large warehouse that never suspended is dropped after a week of metering history shows the BI warehouse is enough. The resume latency goes into the dashboard's budget, which is still far under the forty minutes the queue was adding. Scaling was the expensive way to share a machine the architecture had already separated.

PURGE on the external stage

A template from an internal-stage tutorial includes PURGE = TRUE. The first successful load of event_date=2026-11-10 deletes the Parquet under the run prefix. The Delta or Spark restatement, which still treats the lake as the pre-image, fails the next day. Kafka retention has passed for that day. The Snowflake raw table is now the only copy, and it was loaded with CONTINUE, so it is short. Time travel on the Snowflake table can restore the loaded rows. It cannot restore the files PURGE deleted from S3. Those are S3 deletes, visible in the bucket if versioning was on, and gone if it was not.

The repair turns on S3 versioning for tlp-raw if the recovery point requires it, restores the objects if versions exist, and removes PURGE from every external stage statement in Git. A check in CI rejects PURGE next to STORAGE_INTEGRATION. The raw table's completeness query is published as red until the restored objects and the dead-letter topic agree. The incident review does not blame Snowflake for deleting what the statement asked to delete. The template was the defect. Internal-stage recipes do not transfer to the lake.

Best Practices

• Split load and BI warehouses. Auto-suspend both. Leave MIN_CLUSTER_COUNT at 1 unless resume time is a measured problem.
• Put resource monitors on credit quotas before the first large load.
• Use a storage integration. Allow-list raw prefixes. Leave landing off the integration.
• COPY the marker's run prefix. Match Parquet columns by name. ON_ERROR = ABORT_STATEMENT for financial and completion inputs.
• Rely on load history for retries. Do not set FORCE on the scheduled path. Do not PURGE an external lake stage.
• Keep Snowpipe on closed files. Do not let per-object loads become the served hour.
• Set time-travel retention to the audit window the edition allows. Do not tell auditors to use fail-safe.
• Clone to test a restatement. Merge back on purpose.
• Add clustering keys only after the profile shows pruning failure. Re-check with SYSTEM$CLUSTERING_INFORMATION.
• Gate publication on completeness, including dead letters. Store a publish token or source_snapshot_id.
• Give reporter curated select. Keep raw and stages on loader.
• Attribute credits with account-usage views per warehouse and per serverless feature.
• Read the Iceberg limitations page before you treat an Iceberg table as a native one.
• One server for completion-v3. This account either publishes it or copies a published snapshot.

Common Mistakes and Anti-Patterns

A shared warehouse that never suspends. Load and BI contend, and you pay for idle size. Split them and suspend.

PATTERN on the parent hour. You load an open run. The table looks fresh and is partial. Name the run id.

ON_ERROR = CONTINUE left on after a rescue. The next bad file disappears into a load log nobody reads. Abort, then fix the file.

FORCE as the retry default. Load history was already the retry mechanism. Force duplicates it.

PURGE on the lake stage. You delete the pre-image lesson 42 told you to keep. Purge internal stages only.

Clustering every table on the first day. Credits for a depth the planner did not need. Wait for the profile.

MIN_CLUSTER_COUNT greater than 1 on a warehouse that is idle all night. You asked for clusters to stay up. The monitor will agree.

Recomputing completion in a Snowflake model when Databricks already publishes it. Two Mondays. Store source_snapshot_id and select it.

Pointing Snowpipe at the sink prefix and summing raw for the promo hour. Continuous ingest of an open prefix is not a version. Publish on the marker.

Dropping a table to save storage the same day. Time travel and fail-safe retain bytes. Read the retention you set before you promise the bill will fall.

ACCOUNTADMIN as the task owner. A compromised task definition is a full account. Use transformer.

Troubleshooting and Design Review

Symptom: A retried task doubled raw row counts.

Likely cause: FORCE = TRUE, or two patterns loaded two run prefixes of the same hour, or an INSERT path that does not use COPY history.

Investigation: LOAD_HISTORY or the copy history view the docs name for your interface, the statement text, the patterns, and whether a merge was skipped straight to an insert.

Corrective action: delete or swap out the duplicated load using time travel if you are inside retention, then reload once without FORCE. Fix the pattern. Add the uniqueness check before publication so curated never takes the duplicate.

Symptom: Finance queries queue every morning for about as long as the load.

Likely cause: one warehouse.

Investigation: WAREHOUSE_METERING_HISTORY and the query history for queued time versus execution time. The warehouse name on both statement types.

Corrective action: split warehouses. Do not raise the size until the queue is actually execution time on the BI warehouse.

Symptom: Time travel to last Tuesday fails, and the edition is Enterprise.

Likely cause: DATA_RETENTION_TIME_IN_DAYS is still the one-day default on that table.

Investigation: SHOW TABLES or the table parameter. The publication row's timestamp.

Corrective action: set the retention to the audit window. You cannot time-travel past what was retained. Rebuild from the lake if the files still exist. Change the parameter before the next publish, not after the auditor arrives.

Symptom: Storage did not drop after a large DROP TABLE.

Likely cause: time travel plus fail-safe.

Investigation: the retention parameter and the table's dropped state. Account usage storage views.

Corrective action: wait out the windows, or accept the bytes. Do not open a second cleanup path that deletes the lake stage. If a table was dropped in error, undrop it while time travel allows.

Symptom: A one-day filter scans nearly every partition after a quiet first month.

Likely cause: merges rewrote micro-partitions without regard to date, or the filter is a function on the column that blocks pruning, such as a cast the planner cannot see through.

Investigation: the query profile's partition counts, the SQL text, clustering information if a key exists.

Corrective action: filter on the raw date column. If the profile is still wide, consider a clustering key on event_date and compare credits against a small aggregate table. Do not jump the BI warehouse two sizes as the only change.

A design review asks: which warehouse loads, and which serves? What is the suspend time? Does the integration allow list include landing? Does any statement purge the external stage? What is the COPY pattern, and does it include a run id? What happens on retry without FORCE? What is the retention in days, and what is the edition cap? Where is publication or source_snapshot_id? Which usage view will show a clustering surprise?

Interview Questions and Answers

Q1. What are the three layers, and which one do you scale when a dashboard is slow?

Storage holds micro-partitions. Virtual warehouses execute queries. Cloud services compile, authorize, prune, and may serve a result cache. I scale or split the warehouse only after the profile shows the time is execution, not queueing behind a load on the same warehouse, and not a pruning failure. I do not scale storage. I do not disable suspend to make the first click faster until I have priced the idle credits.

Q2. What is a micro-partition, and why don't you set its size?

It is Snowflake's immutable columnar storage unit, documented on the order of 50 to 500 MB uncompressed, with metadata for pruning. The service creates it when you load or write. I influence layout with load order and, if needed, a clustering key. I do not pick a file size the way I pick a Parquet row group in the lake. The query profile tells me whether the metadata is doing its job.

Q3. When does a clustering key help, and when is it a tax?

It helps when filters you run no longer prune because micro-partitions mix values, which SYSTEM$CLUSTERING_INFORMATION and the profile will show. It is a tax when the table is small, when the natural load order already prunes event_date, or when the key is a high-cardinality column nobody filters. Automatic clustering spends credits continuously. I add it as a response to a measurement, and I re-measure.

Q4. How do you load s3://tlp-raw without putting an AWS key in Snowflake?

A storage integration names the IAM role and the allowed locations. DESC INTEGRATION yields the Snowflake IAM user and external id for the role trust policy. An external stage uses that integration and a Parquet file format. The allow list omits landing. COPY INTO uses MATCH_BY_COLUMN_NAME and a pattern that includes the closed run id. No access key in the stage DDL.

Q5. Why is a retry of COPY safe, and how do people defeat it?

Snowflake skips files it has already loaded successfully, within the load-history window the COPY docs specify. A retry of the same files does not duplicate. FORCE = TRUE defeats that and inserts again. Two different keys for the same logical hour also both load, because they are different files. I keep FORCE off the schedule, and I name only the current run prefix. Curated is still a merge with a uniqueness gate, because file skip is not the business grain.

Q6. What does PURGE do on an external stage pointed at the lake?

It deletes the source objects after a successful load. Those objects are the replay copy if Kafka has expired. I use purge on internal stages I intended to empty. I forbid it on tlp-raw. S3 versioning is the recovery if someone does it once. Time travel inside Snowflake does not restore the deleted objects.

Q7. How do time travel and fail-safe differ?

Time travel is queryable by users for DATA_RETENTION_TIME_IN_DAYS, edition-capped. I set it to the audit window. Fail-safe is a further seven-day window Snowflake documents as not configurable and not a user query surface, for disaster recovery by Snowflake. I do not point auditors at fail-safe. I do warn finance that dropped tables can keep billing through both windows.

Q8. What is a zero-copy clone good for in this platform?

A repair or a test of a restatement. The clone shares micro-partitions until I write. The source stays on the published definition. I merge back only through the reviewed statement. A clone is not a second region and not a license to compute a private completion-v4 in the repair schema and point a dashboard at it.

Q9. Where does Snowpipe fit, and what clock does it not own?

It loads new files with low delay, on its own credit meter, when notifications are reliable and the files are closed. It does not decide that an hour is complete. Per-object loads of a live prefix make raw look queryable mid-hour. I still publish curated from the marker and the completeness check. If I cannot filter to closed runs, I use a scheduled COPY and skip Snowpipe for that dataset.

Q10. Databricks already publishes completion. What do you store in Snowflake?

A replica of the published snapshot, with source_snapshot_id and definition_version, loaded by a job that does not rewrite the metric. Reporters select that table. If the numbers disagree, the server wins and the load is wrong. I do not maintain a local SQL variant that treats checkpoints differently. That fork is the lesson 49 incident.

Q11. Why might a filter on event_date stop pruning even though the column exists?

The predicate wraps the column in a function the planner cannot prune through, or merges have mixed dates across micro-partitions. I look at the query text and the partition counts in the profile before I add a clustering key or a larger warehouse. Fixing the predicate is free. Clustering is not.

Hands-On Exercises

Exercise: Load a closed prefix and keep the lake

Problem statement. Design the Snowflake landing for progress and for completion. Decide whether this account is the server or the replica. Prove a retry does not duplicate, a purge cannot see the lake stage, and the BI warehouse is not the loader.

Tasks.

1. State server or replica. If replica, name the column that carries the upstream snapshot. If server, name the publication table.
2. Write the storage integration allow list and the stage URL. Explain why landing is absent.
3. Write the COPY for one run prefix, including MATCH_BY_COLUMN_NAME and ON_ERROR. Omit PURGE and FORCE. Say what a retry should do to the row count.
4. Create two warehouses in DDL, with suspend and a multi-cluster policy only on the BI side if you justify it. Put a resource monitor in words, with quotas as placeholders.
5. Show a clone used as a repair sandbox, and the rule for merging it back.
6. Set a retention value and cite the edition cap you must check. State what fail-safe is not for.
7. Describe the completeness gate, including dead letters, relative to publication.
8. Name the account-usage view you would open if credits moved and the publish's row counts did not.

Expected deliverables. The DDL sketches, the server-or-replica sentence, the retry expectation, and the credit view name.

Evaluation rubric.

You pass when a reviewer can see the statement that loads Tuesday, the statement that must not delete Tuesday in S3, and the warehouse the dashboard is allowed to resume.

Lesson Summary

Snowflake separates storage, virtual warehouses, and cloud services. Micro-partitions carry the pruning metadata. You scale warehouses, you do not scale disks, and you split load from BI so a COPY cannot queue the close. A storage integration reaches the lake without keys, on an allow list that omits landing. COPY of a marked run prefix is idempotent through load history until someone sets FORCE or points at two runs. PURGE on that stage deletes the pre-image. Snowpipe accelerates file load and does not define a closed hour. Time travel is the queryable audit, edition-capped. Fail-safe is not. Clones are how you rehearse a restatement. Clustering is a measured response to bad pruning, and it costs credits. Publication, or source_snapshot_id on a replica, is still the only way Monday knows which rows are official.

The platform lessons that follow, on each cloud and on cost and security, should be able to assume this landing: a named warehouse, a named stage, a retention number, and a single definition of completion.

Further Reading

• Snowflake architecture — storage, compute, and cloud services. The key-concepts page is the picture this lesson started from.
• Micro-partitions and data clustering — partition metadata, clustering keys, and the cost of automatic clustering.
• Virtual warehouses — sizes, multi-cluster policies, auto-suspend, and credit behavior. Read the current credit table with this page.
• CREATE STORAGE INTEGRATION — the allow list and the IAM role binding.
• COPY INTO table — MATCH_BY_COLUMN_NAME, ON_ERROR, FORCE, PURGE, validation mode, and load history.
• Time Travel — retention by edition, and what fail-safe is not.
• Zero-copy cloning — clones share storage until one side changes. Confirm retention rules on the same page before you treat a clone as a backup.
• Data Lakehouse Architecture — the server and replica rule this landing must not fork.
• Databricks Lakehouse Platform — the other place completion might already be published.