Big files, real risks, and how not to overspend
Snowflake load parallelism depends on format and file options as well as file count. Qualifying large uncompressed CSV files support parallel scanning. Multiple moderately sized files can help, but one file is not universally one thread. Start with roughly 100–250 MB compressed parts as a guideline and benchmark; it is not a hard size limit.
Mindset shift: FastLoad vs. COPY INTO
- Teradata FastLoad: One big file can still saturate the system by turning up sessions; the client feeds all AMPs in parallel.
- Snowflake COPY INTO: file-level concurrency matters, but supported formats/options can also permit within-file parallel scanning, including qualifying large uncompressed CSV files.
A few large files can constrain some loading patterns, but parallelism also depends on format and loader options. Benchmark before concluding that resizing cannot help or that splitting is mandatory.
Shape files for parallelism
Goal: Present many independent parts so Snowflake can load them concurrently.
- Target size: Treat ≤ 250 MB compressed as “small” for parallel copy; sweet spot ≈ 100–250 MB per file.
- Prefer a format suited to the workload: Parquet and ORC are columnar; Avro is row-oriented. Parsing and compression costs vary.
- Avoid extremes: Very large files starve parallelism; ultra-tiny files add orchestration overhead. Aim for a few hundreds of MB per part.
Right-size the warehouse to files in flight
- Few files → X-Small/Small is typically optimal.
- Hundreds of files → consider Medium/Large/XL so additional execution slots actually work.
- Autosuspend/Autoresume: Enable AUTO_SUSPEND (≈ 60–120 s) and AUTO_RESUME. Batch work to avoid paying multiple minimum billing windows.
More warehouse capacity helps only if you expose enough files concurrently to occupy the extra slots.
Cost traps (and how to dodge them)
- Big warehouse + few files → idle slots = more credits, no speed. Split files or size down.
- Idle billing → long gaps without AUTO_SUSPEND. Batch tightly; suspend fast.
- Heavy parse costs → publish Parquet upstream when possible.
Guardrail: configure Resource Monitors for covered warehouse usage and separately monitor serverless services; a warehouse monitor does not cap every service charge.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThroughput tuning for bulk-loading
- Match slots to files: Ensure each batch exposes enough files to keep the warehouse busy. If you have ~40 parts, Medium may beat Small on wall-clock; pick the cheapest combo = (duration × credits/sec) that meets your SLA.
- Format matters: CSV/JSON parsing is CPU-heavier than Parquet; convert at the source when you can.
Teradata → Snowflake: what to emulate
- FastLoad playbook: In Teradata you saturated parallel lanes by raising sessions; in Snowflake you achieve the same by publishing many file parts.
- Keep the hot path simple: Stage first, transform later; avoid constraints and extra work at load time.
- Size for measured parsing, file concurrency, within-file scanning, I/O and data-volume bottlenecks.
Small-loads vs. huge-loads pattern
- Small files (≤ 250 MB): Run
COPYon a small load warehouse. If you must launch many independent COPY statements simultaneously, enable multi-cluster on that warehouse and submit COPY in parallel sessions; Snowflake scales out clusters when there’s queuing and suspends them as the surge drains. - For large loads, test moderate file parts and warehouse sizes; 250 MB is a guideline, not a hard limit, and supported inputs may scan in parallel within a file.
Two purpose-built load warehouses—one tuned for small-file surges, one sized for large split-file batches—keep performance high and costs predictable.
Minimal, repeatable runbook
- Consider roughly 100–250 MB compressed parts as a starting recommendation, then measure format-specific load throughput and cost.
- Pick warehouse size based on files available concurrently; enable AUTO_SUSPEND/RESUME.
- If many jobs: enable multi-cluster and launch
COPYin parallel sessions; let Snowflake scale out/back automatically. - Monitor spend: Resource Monitors; compare Small vs. Medium/Large to find the cheapest config meeting your SLA.
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book