Multi-Table Inserts in Snowflake: One Scan, Many Targets

Nov 23, 2025 • 9 min read

When you design ELT pipelines in Snowflake, you’ll often find yourself loading the same source data into multiple target tables:

Splitting customers by region
Writing to Current and History tables at the same time
Sending the same rows into Test and Backup tables
Fanning out data into multiple downstream domains

The naive approach is to write multiple INSERT statements, each with its own SELECT from the source. That works, but it comes with a cost:

Each INSERT re-scans the source table
You pay more for compute
Your code becomes repetitive and harder to maintain

Snowflake’s Multi-Table Insert feature solves this in a clean and elegant way:

you write one SELECT, and Snowflake routes the rows into many target tables.

In this article, we’ll:

Explain what Multi-Table Inserts are
Show the difference between INSERT ALL and INSERT FIRST
Walk through step-by-step examples
Wrap up with best practices for production ELT

1. What Are Multi-Table Inserts?

A Multi-Table Insert in Snowflake lets you:

Run one source query (SELECT), and
Depending on conditions, insert each row into one or more target tables.

You describe the routing logic using WHEN … THEN INTO … clauses.

Snowflake supports two main modes:

INSERT ALL – a row can go into multiple target tables
INSERT FIRST – a row goes into only the first matching target table

High-Level Syntax

Unconditional multi-table insert:

INSERT [OVERWRITE] ALL
INTO target_table_1 (col1, col2, ...)
VALUES (expr1, expr2, ...)
INTO target_table_2 (col1, col2, ...)
VALUES (expr1, expr2, ...)
SELECT ...
FROM   source_table;

Conditional multi-table insert:

INSERT [OVERWRITE] { FIRST | ALL }
    WHEN <condition_1> THEN
        INTO target_table_1 (col1, col2, ...)
            VALUES (expr1, expr2, ...)
    WHEN <condition_2> THEN
        INTO target_table_2 (col1, col2, ...)
            VALUES (expr1, expr2, ...)
    [ELSE
        INTO fallback_table (col1, col2, ...)
            VALUES (expr1, expr2, ...)]
SELECT ...
FROM   source_table;

The important part:

the SELECT at the bottom is executed once, and Snowflake applies the routing logic on top of that result set.

2. INSERT ALL vs INSERT FIRST (When to Use Which)

Think of it like this:

INSERT FIRST

Snowflake evaluates your WHEN conditions in order
As soon as a condition matches, that row is inserted into the corresponding table
No further conditions are checked for that row

Use INSERT FIRST when each row belongs in exactly one table.

Example: “If status is ACTIVE, go to CURRENT; otherwise go to HISTORY.”

INSERT ALL

Snowflake evaluates all WHEN conditions (or even no conditions)
A row can be inserted into multiple tables if it matches multiple conditions

Use INSERT ALL when a row can (or should) be duplicated across targets.

Example: “Insert all rows into TEST and BACKUP; some special rows also into an AUDIT table.”

3. Step-by-Step: How to Use Multi-Table Inserts

Let’s build this up in a repeatable way you can use in your ELT patterns.

Step 1 – Define Your Source Query

Start with the simplest piece: what data are you reading once?

Example:

SELECT
    ID,
    NAME,
    REGION,
    STATUS
FROM CUSTOMERS;

You may include joins, filters, projections, etc. The key is:

this is the logical single scan that will feed all your targets.

Step 2 – Decide Your Target Tables and Rules

Ask:

Which tables should receive rows?
Under what conditions? (region, status, date range, etc.)
Should rows go to one target or many?

This determines:

Whether you pick FIRST or ALL
The sequence of your WHEN clauses
Whether you need an ELSE branch for “everything else”

Step 3 – Map Columns and Add Metadata

For each target table:

Align the column list in the INTO clause with the target schema
Decide what values to insert:
- Direct columns from the source (ID, REGION, STATUS, …)
- Derived values (CURRENT_DATE, UUID_STRING(), constants, etc.)

Example metadata fields:

LOAD_DATE
BATCH_ID
SOURCE_SYSTEM

Step 4 – Write the Multi-Table Insert Statement

Combine everything:

INSERT ALL or INSERT FIRST
WHEN / ELSE branches
Final SELECT at the bottom

Step 5 – Test on a Small Sample

Before running on full data:

Limit the source (WHERE + LIMIT)
Compare counts between source and targets
Validate a few rows manually

After that, you can plug it into your regular ELT orchestration.

4. Example 1 — Split Customers by Region

Goal:

Take all customers and distribute them into three regional tables:

CUSTOMERS_EU
CUSTOMERS_US
CUSTOMERS_OTHER

Each row should end up in exactly one table → we can use either

INSERT FIRST or INSERT ALL with mutually exclusive conditions.

Here we’ll show INSERT ALL with ELSE because it’s easy to read.

INSERT ALL
    WHEN REGION = 'EU' THEN
        INTO CUSTOMERS_EU (ID, NAME, REGION)
        VALUES (ID, NAME, REGION)

    WHEN REGION = 'US' THEN
        INTO CUSTOMERS_US (ID, NAME, REGION)
        VALUES (ID, NAME, REGION)

    ELSE
        INTO CUSTOMERS_OTHER (ID, NAME, REGION)
        VALUES (ID, NAME, REGION)

SELECT
    ID,
    NAME,
    REGION
FROM CUSTOMERS;

What’s happening:

Snowflake executes the SELECT from CUSTOMERS once.
For each row:
- If REGION = 'EU' → goes to CUSTOMERS_EU.
- Else if REGION = 'US' → goes to CUSTOMERS_US.
- Otherwise → goes to CUSTOMERS_OTHER.

5. Example 2 — Load Current and History in a Single Pass

This is a very common pattern in data warehousing: maintain both a Current and a History table.

Goal:

If a customer is currently active, write to CUSTOMERS_CURRENT
For all other cases, write to CUSTOMERS_HISTORY
Always record LOAD_DATE for audit purposes

Since each row should end up in only one table, we use INSERT FIRST.

INSERT FIRST
    WHEN STATUS = 'ACTIVE' THEN
        INTO CUSTOMERS_CURRENT (ID, NAME, STATUS, LOAD_DATE)
        VALUES (ID, NAME, STATUS, CURRENT_DATE)

    ELSE
        INTO CUSTOMERS_HISTORY (ID, NAME, STATUS, LOAD_DATE)
        VALUES (ID, NAME, STATUS, CURRENT_DATE)

SELECT
    ID,
    NAME,
    STATUS
FROM STAGING_CUSTOMERS;

Notes:

INSERT FIRST checks the WHEN STATUS = 'ACTIVE' condition first.
Active customers go to CUSTOMERS_CURRENT.
Everyone else goes to CUSTOMERS_HISTORY.
You can extend this with more conditions (e.g., WHEN STATUS = 'SUSPENDED' THEN ...).

6. Example 3 — Duplicate Inserts into Test and Backup Tables

Sometimes you simply want to clone inserts into multiple environments:

A TEST table for QA
A BACKUP table for recovery or investigation

Every row should go into both tables → use INSERT ALL without conditions.

INSERT ALL
    INTO CUSTOMERS_TEST (ID, NAME, REGION)
    VALUES (ID, NAME, REGION)

    INTO CUSTOMERS_BACKUP (ID, NAME, REGION)
    VALUES (ID, NAME, REGION)

SELECT
    ID,
    NAME,
    REGION
FROM CUSTOMERS_SOURCE;

Here:

No WHEN clauses are needed.
Every row returned by the SELECT goes to both CUSTOMERS_TEST and CUSTOMERS_BACKUP.

You can still mix in conditions if needed, for example:

INSERT ALL
    INTO CUSTOMERS_TEST (ID, NAME, REGION)
    VALUES (ID, NAME, REGION)

    INTO CUSTOMERS_BACKUP (ID, NAME, REGION)
    VALUES (ID, NAME, REGION)

    WHEN REGION = 'EU' THEN
        INTO CUSTOMERS_EU_AUDIT (ID, NAME, REGION, LOAD_DATE)
        VALUES (ID, NAME, REGION, CURRENT_TIMESTAMP)

SELECT
    ID,
    NAME,
    REGION
FROM CUSTOMERS_SOURCE;

In this extended version, EU customers also get written into an audit table.

7. Best Practices for Multi-Table Inserts

To make Multi-Table Inserts production-ready and efficient, keep these tips in mind.

1. Optimize the Source

SELECT

Since the source is scanned once, make that scan as efficient as possible:

Project only the columns you actually need
Filter early when possible (WHERE clause)
Pre-aggregate or pre-join if it reduces data volume

2. Choose

FIRST

vs

ALL

Intentionally

Use INSERT FIRST when each row should go to one and only one target
Use INSERT ALL when:
- Rows may need to be duplicated across multiple targets, or
- You find the ALL + ELSE form easier to read for mutually exclusive branches

3. Keep Column Types Aligned

Snowflake type-checks every branch:

Column positions in INTO (...) VALUES (...) must match the target table’s schema
Expressions in VALUES must be compatible with the target types

If types don’t match, the statement will fail even if the condition never triggers that branch—so keep schemas consistent.

4. Consider Constraints and Keys

Different target tables may:

Have different primary key strategies
Enforce unique constraints
Have different clustering keys or micro-partitioning behaviour

Be careful with duplicates and ensure your logic won’t violate constraints in any branch.

5. Always Insert Metadata

Add standard metadata to all targets:

LOAD_DATE / LOAD_TS
BATCH_ID
SOURCE_SYSTEM
JOB_NAME

This makes:

Reruns safer
Debugging easier
Lineage and audit requirements easier to satisfy

8. When Should You Use Multi-Table Inserts?

Multi-Table Inserts shine in scenarios where:

You fan out from a single source to multiple tables
Your routing logic is straightforward and based on row attributes
You care about cost efficiency and simpler ELT code

They might not be ideal if:

Each target requires very different transformation logic
You need complex procedural flows, loops, or conditional orchestration

(then you might prefer a stored procedure or a higher-level orchestration tool)

9. Summary

Multi-Table Inserts in Snowflake give you a powerful pattern:

One source scan, multiple target tables.

With INSERT ALL and INSERT FIRST, you can:

Split data by attributes (e.g., region, status)
Maintain Current and History tables in one go
Write to Test, Backup, and Audit tables from a single query
Cut down redundant scans and reduce compute costs

Once we get comfortable with the pattern, you’ll find yourself refactoring a lot of “copy-paste” INSERT statements into one clean, declarative multi-table insert.

Tagged: Data Cloud

Back to All Stories Next Post: Spark Optimisation Tips