Skip to main content

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:

  1. 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
  2. It sees only Partition 3 matches the filter.

  3. 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​

FeatureMetadata Cache (Services Layer)Result Cache (Final Results)Local Disk Cache (Warehouse SSD)
StoresMicro-partition stats (min/max, row counts, locations)Final query output (24 hrs)Data blocks read into SSD
ScopeUsed at query planningUsed at query result returnUsed during query execution
PersistencePermanent (until data changes)24 hours onlyLost when warehouse suspends
Cost ImpactReduces data scannedSkips execution (0 credits)Reduces re-scan cost, but needs warehouse
Use CasePartition pruningRe-run identical queriesFaster 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) in WHERE clause).
  • βœ… 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.