Metadata Caching in Snowflake
πΉ What is Metadata Cache?β
Snowflake Metadata Cache is one of the main reasons queries can run so fast.
Every table in Snowflake is stored in micro-partitions (small blocks of data, typically 16MB). For each micro-partition, Snowflake automatically maintains metadata such as:
- Row count
- Min/Max values per column
- Distinct values (sometimes)
- Partition/file locations in cloud storage (S3, Azure, GCP)
- Statistics that help prune irrelevant partitions
π Snowflake caches this metadata in its services layer, so queries donβt need to read the entire dataset. Instead, they use metadata first to decide which micro-partitions are relevant and which ones can be skipped.
πΉ Why is it Important?β
- β Partition Pruning β Scans only the partitions that matter.
- β Faster Queries β Query planning is accelerated by pre-cached stats.
- β Lower Costs β Less data scanned = fewer compute credits.
- β Always Available β Metadata cache is permanent (not tied to a warehouse).
πΉ Example Scenarioβ
Table: SALES_TRANSACTIONSβ
CREATE OR REPLACE TABLE SALES_TRANSACTIONS (
TRANS_ID NUMBER,
CUSTOMER_ID NUMBER,
AMOUNT NUMBER,
TRANS_DATE DATE
);
Suppose this table has 1 billion rows stored across 1000 micro-partitions.
Query 1 β Full Scan (no pruning)β
SELECT COUNT(*)
FROM SALES_TRANSACTIONS;
- Snowflake checks metadata for row counts but still needs to scan all partitions.
- Costly and time-consuming on very large tables.
Query 2 β Pruning with Metadataβ
SELECT *
FROM SALES_TRANSACTIONS
WHERE TRANS_DATE = '2025-09-01';
π What happens internally:
-
Snowflake checks metadata cache:
- Partition 1:
2025-01-01 β 2025-03-31 - Partition 2:
2025-04-01 β 2025-06-30 - Partition 3:
2025-09-01 β 2025-09-30
- Partition 1:
-
It sees only Partition 3 matches the filter.
-
Query runs in milliseconds, skipping 999 partitions.
π This is partition pruning powered by metadata cache.
πΉ When Metadata Cache Helps Mostβ
- Queries with filters (
WHERE,BETWEEN,IN) - Aggregations (
MIN(),MAX(),COUNT()) on partitioned columns - Joins where partitioned columns are involved
- Queries on very large tables with predictable filtering patterns
πΉ Metadata Cache vs Other Cachesβ
| Feature | Metadata Cache (Services Layer) | Result Cache (Final Results) | Local Disk Cache (Warehouse SSD) |
|---|---|---|---|
| Stores | Micro-partition stats (min/max, row counts, locations) | Final query output (24 hrs) | Data blocks read into SSD |
| Scope | Used at query planning | Used at query result return | Used during query execution |
| Persistence | Permanent (until data changes) | 24 hours only | Lost when warehouse suspends |
| Cost Impact | Reduces data scanned | Skips execution (0 credits) | Reduces re-scan cost, but needs warehouse |
| Use Case | Partition pruning | Re-run identical queries | Faster re-reads on same warehouse |
πΉ Limitationsβ
β Doesnβt store actual results β only metadata, queries still need a warehouse. β Depends on partitioning β poor clustering means limited pruning. β Dynamic changes β frequent data updates may reduce pruning efficiency. β No manual control β you canβt manage/clear metadata cache directly.
πΉ Best Practicesβ
- β Load data carefully β design micro-partitions aligned with query filters (e.g., by date).
- β Use clustering keys for large tables where filters are predictable.
- β
Avoid transformations that block pruning (e.g.,
DATE(TRANS_DATE)inWHEREclause). - β Monitor query profiles in Snowsight to check % of partitions scanned vs pruned.
πΉ Visual Flow (Metadata Cache in Action)β
- β In short: Snowflakeβs metadata cache is like a map: instead of digging everywhere, it tells the engine exactly where to look. This makes queries on massive tables run lightning fast while keeping costs low.