Skip to main content

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 → XSMALL or SMALL.
    • Large historical load → LARGE or multi-cluster.

💡 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.

✅ 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_HISTORY and QUERY_HISTORY in ACCOUNT_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 filesLoad single huge files
Use Parquet for big dataStick to CSV without need
Enable AUTO-SUSPEND after loadKeep warehouses running idle
Monitor LOAD_HISTORY and QUERY_HISTORYIgnore slow load patterns
Separate load and BI warehousesMix ETL with user queries