Introduction:

A batch job that took two and a half hours dropped to twenty-eight minutes.

Same warehouse. X-Small and Small, nothing upsized.

Nobody touched the compute. The code changed shape instead.

The instinct everyone has, and why it's wrong

When a Snowflake job runs long, the first move is almost always the same: bump the warehouse size. Sometimes that helps. Often it just makes an expensive job finish a little faster and cost more per minute.

The job in question wasn't compute-starved. It was waiting. Every stored procedure ran, finished, and only then did the next one start, one after another, in a straight line. Most of that 2.5 hours wasn't computation. It was the gap between steps that didn't depend on each other but ran as if they did.

That gap is what Snowflake's ASYNC execution is built to close, and it's a different fix than a bigger warehouse.

The pattern: one parent, many children, run at once

The structure is simple to describe. A parent stored procedure calls a set of child procedures with ASYNC, and independent children execute concurrently instead of sequentially:

ASYNC (CALL USP_COPY_EXECUTE (:v_table_id, :v_process_uuid));

The parent batches the calls, fires them, then uses AWAIT ALL; before moving to the next batch. That single line is doing real work: it guarantees every child in the batch finishes before the next batch starts, so dependent steps still run in the right order even though independent ones no longer wait on each other.

Reported results on this pattern run as high as a 9-fold improvement on an XS warehouse. That's an industry figure, not ours. Our own measured result was 2.5 hours to 28 minutes on an XS and S warehouse, with the idle time between steps nearly eliminated. Dane, one of our engineers, built this out on an enterprise client's pipeline, and the four landmines below are exactly what he ran into getting there.

What breaks if you just wrap existing code in ASYNC

This is the part that catches teams off guard, because none of it shows up until procedures actually run at the same time.

LAST_QUERY_ID() stops being reliable. It's session-scoped, and once child procedures are executing concurrently, completion order varies and calls can step on each other's results. The fix is SQLID, which is block-scoped and doesn't have that race condition.

SQLID inside TABLE(RESULT_SCAN(SQLID)) doesn't work directly. It has to be assigned to a variable first:

v_sql_execution_query_id := SQLID;

SELECT "state" INTO :v_load_warehouse_state
FROM TABLE(RESULT_SCAN(:v_sql_execution_query_id));

Temp tables with a fixed name collide. If every concurrent child writes to the same temp table name, they're reading and writing on top of each other. The fix is a UUID generated per child, folded into the table name, and accessed through IDENTIFIER():

v_result_temp_table := 'TEMP_RESULT_' || REPLACE(:v_task_uuid, '-', '_');

CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:v_result_temp_table) (...);

None of these three are edge cases. They are what happens the first time procedures written for sequential execution actually run at the same time.

The limit nobody reads until they hit it

There's a fourth one, and it isn't a coding mistake, it's a platform limit that shared objects run straight into.

If concurrent children write to a shared object, most commonly a logging table, Snowflake enforces a hard cap on concurrent DML against that object. Cross it and the job fails with:

000625 (57014): your statement was aborted because the number of waiters for this lock exceeds the 20 statements limit.

The fix isn't clever, it's discipline: batch concurrent execution at 20 or fewer, and leave margin below that ceiling rather than running right up against it. This is also why the parent proc needs a real batching loop, not a single fire-everything-at-once call. If the process involves more than one type of operation, route each type through the batch with IF/THEN, and build the loop in load-dependency order, a LOAD_SEQUENCE value in configuration makes that ordering explicit instead of implicit in code.

Design it in, don't retrofit it

Here's the part worth sitting with. Every one of these four problems is cheap to design around from the start and expensive to fix after the fact. Retrofitting an existing sequential pipeline means finding every LAST_QUERY_ID() call, every shared temp table name, and every place a shared logging table might get hammered by more than 20 concurrent writers, then testing that none of it breaks under real concurrency instead of a single-threaded test run.

A config-driven parent, batch size, per-task warehouse assignment, and LOAD_SEQUENCE all pulled from a table instead of hardcoded, buys something beyond the initial speed gain: the ability to scale warehouse size or cluster count later to hit a new performance or cost target without touching the stored procedure code. That's a different kind of win than "the job runs faster." It's a pipeline that can be retuned instead of rewritten.

The lesson isn't "use ASYNC." It's that a straight-line pipeline and a concurrent one aren't the same code with a keyword added. They're different designs, and the difference is invisible right up until the first time two children run at once.

If you've built this pattern in Snowflake, what caught you off guard first, the query ID race, the temp table collision, or the lock-waiter limit? I read every reply.

If your batch pipelines were built sequentially and nobody has stress-tested what happens when steps actually run concurrently, that's exactly the kind of architecture gap the AI Readiness Assessment surfaces before it shows up as a production incident. Link is in the comments.

Follow Reeves Smith for practical insights on AI, enterprise data strategy, and governance.

Originally published at https://www.linkedin.com.