Pro ยท Lesson 6 of 13
Micro-partitions, clustering, search optimization, materialized views, Time Travel, and zero-copy clones.
Sign in to save your place.
Each micro-partition is a sealed box of rows, stored by column, with a label that says the smallest and largest value inside. A question about one day can skip every box whose label says it is a different day. Clustering is retaping the boxes so the labels get more useful. Time Travel is a camera roll of older boxes. A clone is a second name for the same boxes until someone changes a row.
Snowflake collects clustering metadata as data is loaded and uses it to skip micro-partitions. A clustering key asks the automatic clustering service to keep rows with similar key values in the same partitions. That service costs compute credits, and reclustering writes new partitions. Cluster large tables whose common filters match the key. Do not cluster a tiny lookup table.
Search optimization, Enterprise Edition and above, builds a search access path for selective point lookups. It is a background service. Queries are not faster until the path is built, and search_optimization_progress on SHOW TABLES tells you when that happened. It is the wrong tool for a wide date-range scan. Clustering is the better first try there.
A materialized view, also Enterprise and above, stores the result of a query from one table and maintains it. The optimizer might still scan the base table. Maintenance has a cost. Snowflake's decision guide says materialized views are for speeding a known query shape on one table, while pipelines with joins belong in dynamic tables.
| Tool | Edition | Use it when |
|---|---|---|
| Clustering key | All editions | Large table, repeated range or equality filters on the same columns |
| Search optimization | Enterprise+ | Very selective point lookups |
| Materialized view | Enterprise+ | A repeated aggregation or filter on a single table |
| Query acceleration | Enterprise+ | Outlier queries on a mixed warehouse. Covered in the warehouse lesson |
Time Travel lets you query, clone, or undrop data from the retention window. Standard Time Travel is up to 1 day on every edition. Extended Time Travel, up to 90 days, needs Enterprise Edition or higher. DATA_RETENTION_TIME_IN_DAYS = 0 is how you turn Time Travel off for an object that allows it, such as a transient table.
When the retention window ends, the bytes move to Fail-safe for 7 days. Fail-safe is not configurable, not queryable by you, and is a best-effort recovery performed by Snowflake. It is not a second Time Travel. You pay for the stored bytes in both states.
Transient tables skip Fail-safe. Temporary tables last for the session. Permanent tables get both Time Travel and Fail-safe. Pick transient for staging data you can reload.
SELECT * FROM orders AT (OFFSET => -900);
CREATE OR REPLACE TABLE orders_backup CLONE orders;
UNDROP TABLE orders;
CREATE TABLE ... CLONE makes a new object that shares the source micro-partitions. There is no extra storage charge until the source or the clone changes. Each side then owns its new partitions, and each side has its own Time Travel.
Cloning a database or schema at a past timestamp fails if a child table no longer has that history. IGNORE TABLES WITH INSUFFICIENT DATA RETENTION skips those children. IGNORE HYBRID TABLES exists when a hybrid table cannot be cloned the same way.
A clone is the cheap way to give dbt or a pull request its own copy of production. The capstone lesson uses that pattern. It is not a backup you can keep forever without storage cost, because every later change is stored.
Micro-partitions are why Snowflake can skip data, show you the past and copy a table instantly.
Step 1 of 3Each micro-partition records the minimum and maximum of every column. A filter on order_date lets Snowflake skip every partition whose range cannot match. This is called pruning.
SELECT * FROM orders WHERE order_date = '2026-10-01';Step 2 of 3An update writes new partitions and keeps the old ones for the retention period: 1 day by default, up to 90 days on Enterprise. You can query the table as it was.
SELECT * FROM orders AT (OFFSET => -3600); -- one hour agoStep 3 of 3A zero-copy clone points at the same partitions, so it costs no extra storage until one side changes. Only changed partitions are new.
CREATE TABLE orders_dev CLONE orders;Set retention to one day, which is valid on Standard Edition. Update a row, read the previous version with AT (OFFSET => -60) if you are still inside the window, and clone the table. The clone should match the current table until you update only one of them.
USE WAREHOUSE LEARN_WH;
USE DATABASE LEARN_DB;
USE SCHEMA PUBLIC;
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 1;
UPDATE orders SET amount = 99 WHERE order_id = 1;
SELECT amount FROM orders WHERE order_id = 1;
SELECT amount FROM orders AT (OFFSET => -60) WHERE order_id = 1;
CREATE OR REPLACE TABLE orders_clone CLONE orders;
UPDATE orders_clone SET amount = 1 WHERE order_id = 1;
SELECT 'live' AS which, amount FROM orders WHERE order_id = 1
UNION ALL
SELECT 'clone', amount FROM orders_clone WHERE order_id = 1;
Checked against Snowflake documentation in October 2026. Product behavior still depends on your edition, cloud, and region.