I85PREP
⌘ K
SnowPro Core study materials
SnowPro Core study resources

Snowpipe on 1.6 Million Tiny Files a Day

SnowPro Core · Put your knowledge to work in context.

All case studies

Snowpipe on 1.6 Million Tiny Files a Day

An events platform writes JSON to an external stage. Volume is 1.6 million files per day, averaging 25 KB compressed, loaded by a single Snowpipe. The pipe keeps up, but credit consumption is far above the original estimate, dashboards show roughly 35 minutes of lag against a target of about 1 minute, and when a malformed record appears the entire file is rejected and an engineer re-loads by hand after fixing it. Compliance now requires that bad rows be quarantined rather than blocking the pipeline, and the platform team cannot change the upstream producer's file-emission logic this quarter.

1. Why is Snowpipe cost so far above the estimate, and what is the highest-leverage change given the producer cannot be modified?

Read the worked answer

The file count is the problem. Snowpipe's serverless compute is charged per file with meaningful per-file overhead and a per-file minimum, so 1.6 million 25 KB files carry roughly the same overhead as 1.6 million files 10,000× their size. Storage and byte volume are almost irrelevant to the bill here.

Highest-leverage fix, given the producer is fixed:

  1. Add a compaction layer between the producer and the pipe. Land files in a raw external stage, then use a scheduled task or an external process to aggregate them into 100–250 MB compressed files that the pipe actually loads. This cuts the file count by orders of magnitude and is the whole cost story.
  2. Benefits compound downstream. Larger files produce fewer, better-clustered micro-partitions, which also reduces pruning problems and reclustering later.
  3. The 35-minute lag is consistent with queue/backlog effects on a large number of tiny files; file aggregation usually addresses it at the same time. Verify state with SYSTEM$PIPE_STATUS (pending file count, last ingested timestamp) before assuming the pipe itself is unhealthy.

Note Snowpipe is also serverless spend that warehouse resource monitors do not govern - the only real control is fewer files.

2. How do you satisfy the compliance requirement that bad rows be quarantined rather than blocking the load?

Read the worked answer

Change from file-level rejection to row-level isolation:

  1. Land raw first. Load the file contents as VARIANT (or raw text) into a landing table with COPY INTO ... ON_ERROR = 'CONTINUE', so a malformed record does not abort the whole file. The default, ABORT_STATEMENT, is exactly the behaviour the team is fighting; SKIP_FILE keeps the file unloaded and is no better for this goal.
  2. Parse and route in SQL. A downstream task flattens the landing table and writes parse failures - with the file name, line number, and error text - into a QUARANTINE table while good rows flow to the modelled table. Compliance gets an auditable record instead of a manual re-load.
  3. Validate before promoting. Use COPY INTO ... VALIDATION_MODE = 'RETURN_ERRORS' on a sample or on new file patterns to catch schema drift before it reaches production.
  4. Close the loop. COPY_HISTORY retains per-file load status and error counts; alert on it rather than relying on someone noticing a reject.

The key judgement is that ON_ERROR decides whether a file survives; only a landing-then-parse design decides whether a row survives.