Data Loading Optimization
Improving performance in Snowflake for data loading and ongoing maintenance requires a mix of design strategies, configuration tuning, and operational best practices. Here’s a comprehensive, structured guide:
⚡ Performance Tuning for Data Loading & Maintenance in Snowflake
✅ 1. Use Optimal File Sizes for Bulk Loads
-
Best Size: 100 MB – 250 MB compressed per file (for cloud storage like S3, Azure Blob, GCS).
-
Why?
- Too small → Too many files → Overhead on metadata processing.
- Too large → Limits parallelism.
✔ Best Practice: Split big files into chunks ~ 200 MB using tools like Snowflake CLI, AWS S3 CLI, or Spark.
✅ 2. Use Multiple Files for Parallel Loading
-
Snowflake uses one thread per file during load.
-
Rule of Thumb: More files = more parallelism.
- Example: Loading 1 TB → better with 500 files of 200 MB each than 1 file of 1 TB.
✔ Use COPY INTO with pattern matching:
COPY INTO my_table
FROM @my_stage
PATTERN='.*[.]csv.gz'
FILE_FORMAT=(TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"')
ON_ERROR='CONTINUE';
✅ 3. Enable AUTO-INGEST for Continuous Loading
- Use Snowpipe for near-real-time loading.
- Reduces manual triggers and latency.
- External stages + Event notifications (S3, Azure Event Grid, GCS Pub/Sub).
✅ 4. Compress Data Before Uploading
-
Use GZIP, BZIP2, or Snappy.
-
Benefits:
- Reduces network transfer time.
- Snowflake auto-decompresses during load.
✅ 5. Match Warehouse Size to Load Volume
-
Use bigger warehouses for large parallel loads.
-
Example:
- Small incremental load →
XSMALLorSMALL. - Large historical load →
LARGEor multi-cluster.
- Small incremental load →
💡 Scale Up Temporarily: Load big data → then scale down or suspend.
✅ 6. Use Correct File Format
-
Parquet or ORC > CSV for large-scale loads.
-
Benefits:
- Columnar storage → Faster COPY and query.
- Supports schema evolution.
✅ 7. Leverage Multi-Cluster Warehouses for Concurrent Loads
- High-concurrency pipelines? Enable:
ALTER WAREHOUSE load_wh
SET MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3
SCALING_POLICY = 'ECONOMY';
✅ 8. Avoid Table Locks During Loads
- Use staging tables + MERGE pattern instead of direct updates on production tables.
- Example:
MERGE INTO target_table t
USING staging_table s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);
✅ 9. Optimize Micro-Partitions
- Why important? Snowflake organizes data in micro-partitions (16 MB each). Poor clustering → slow queries.
✔ Best Practices:
- Cluster on frequently filtered columns using
CLUSTER BY. - Example:
CREATE TABLE orders CLUSTER BY (customer_id);
- Re-cluster periodically (manual or automated with Tasks + Streams).
✅ 10. Manage Table Maintenance
-
Vacuuming is automatic in Snowflake (you don’t do it).
-
But you should:
- Use
ALTER TABLE … RECLUSTER(if natural clustering deteriorates). - Drop or archive unused tables to save storage.
- Use
✅ 11. Use COPY Options for Efficiency
VALIDATION_MODE=RETURN_ERRORS→ Validate before full load.MAX_FILE_SIZE→ Control chunking.ON_ERROR='CONTINUE'→ Skip bad records instead of failing the batch.
✅ 12. Automate with Streams & Tasks
- Streams: Track changes for incremental loads.
- Tasks: Automate ELT and clustering.
- Example:
CREATE TASK recluster_task
WAREHOUSE = maintenance_wh
SCHEDULE = 'USING CRON 0 3 * * * UTC'
AS
ALTER TABLE orders RECLUSTER;
✅ 13. Monitor & Tune Performance
-
Use Query Profile for load jobs → Identify bottlenecks.
-
Check
LOAD_HISTORYandQUERY_HISTORYinACCOUNT_USAGE. -
Optimize:
- File count
- Warehouse size
- Network throughput
📊 High-Performance Data Loading Flow
✅ Quick Do’s & Don’ts
| ✅ Do’s | ❌ Don’ts |
|---|---|
| Use 100–250 MB compressed files | Load single huge files |
| Use Parquet for big data | Stick to CSV without need |
| Enable AUTO-SUSPEND after load | Keep warehouses running idle |
Monitor LOAD_HISTORY and QUERY_HISTORY | Ignore slow load patterns |
| Separate load and BI warehouses | Mix ETL with user queries |