Snowflake's Automatic Compression: Trust But Verify

Snowflake's Automatic Compression: Trust But Verify

While Snowflake compresses data automatically, certain data types and patterns compress better than others. Optimize your schema for maximum compression.

Compression by Data Type

Best Compression (10-50x):

• Repeated values (low cardinality)
• Sorted numeric columns
• Date/timestamp columns
• Boolean columns

Good Compression (5-10x):

• Text with patterns
• Numeric columns
• Most VARCHAR columns

Poor Compression (2-3x):

• Random/unique strings
• Binary data (BLOBs)
• Already-compressed data
• UUIDs, hashes, tokens

Schema Optimization

-- BAD: Storing everything as VARCHAR
CREATE TABLE orders (
order_id VARCHAR,
order_date VARCHAR,
amount VARCHAR,
is_shipped VARCHAR
);

-- GOOD: Specific data types compress better
CREATE TABLE orders (
order_id INTEGER,
order_date DATE,
amount NUMBER(10,2),
is_shipped BOOLEAN
);

Compression Impact

• VARCHAR version: 10GB → 5GB compressed (2x)
• Optimized version: 10GB → 1.5GB compressed (6.7x)

Normalize for Better Compression

-- BAD: Denormalized with repeated values
CREATE TABLE orders (
order_id INTEGER,
customer_name VARCHAR, -- Repeated
customer_address VARCHAR, -- Repeated
product_name VARCHAR,
amount NUMBER
);
-- 1M orders → 5GB compressed

-- GOOD: Normalized with lookup table
CREATE TABLE orders (
order_id INTEGER,
customer_id INTEGER, -- Integers compress better
product_name VARCHAR,
amount NUMBER
);
-- 1M orders → 1.5GB compressed (3x better)

Monitoring Compression Ratios

SELECT
TABLE_NAME,
ROW_COUNT,
BYTES / (1024*1024*1024) as UNCOMPRESSED_GB,
COMPRESSED_BYTES / (1024*1024*1024) as COMPRESSED_GB,
ROUND((BYTES / COMPRESSED_BYTES), 2) as COMPRESSION_RATIO
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE SCHEMA_NAME = 'PUBLIC'
ORDER BY COMPRESSED_BYTES DESC;

Real-World Example

An e-commerce company stored all product data as VARCHAR. 50M rows, uncompressed 200GB, compressed 95GB (2.1x), storage cost $23/month.

After optimization using INTEGER, NUMBER, BOOLEAN, and foreign keys: Uncompressed 200GB, compressed 18GB (11x), storage cost $4.32/month. Savings: $18.68/month × scale.

Key Takeaways

• Use specific data types (INTEGER, DATE, BOOLEAN) instead of VARCHAR
• Snowflake can't compress already-compressed or encrypted data
• Normalize to reduce repeated values
• Monitor compression ratios and optimize poorly-compressing tables
• Typical compression: 5-10x, optimal can reach 20-50x