Introduction

Last updated: July 2026

Most replication is not a one-time copy. The source keeps changing: a customer upgrades their plan, an order gets a refund, a late webhook rewrites a row you loaded yesterday. If your pipeline only ever appends, those changes pile up as duplicates, and every downstream query has to defend itself with a qualify row_number() or a DISTINCT ON. An upsert avoids that. It matches each incoming row against what is already there, updates the ones that exist, and inserts the ones that do not.

Sling does this in incremental mode. It is worth knowing how, because the difference between an upsert and an append-only load is a single line of YAML, and the failure mode is quiet: you get the wrong answer, not an error. This guide walks through the three cases that trip people up (a single primary key, a composite primary key, and a schema that changes mid-stream) with a real Postgres-to-Snowflake run behind every claim. The same configuration works for any of Sling’s 40-plus connectors. We use Snowflake here because it supports every merge strategy out of the box.

What an Upsert Actually Is in Sling

Sling’s incremental mode has two jobs, and it is easy to conflate them. The update_key decides which rows to read: Sling reads the current maximum value from the target table and pulls only source rows newer than that. The primary_key decides what to do with them: match on that key, update matches, insert the rest.

The modes documentation lays out the three combinations:

That last row is the trap. Drop the primary_key and a changed source row is no longer recognized as an existing record. It gets appended, and now you have two of it. If you want updates to land as updates, you need a primary key. Everything below assumes both keys are set.

One design detail is worth calling out. Sling does not keep a separate state file for database targets. The “checkpoint” is a MAX(update_key) query run directly against the target table, so the target table is the state. You can read the exact function, getIncrementalValueViaDB, in the source. Because of this, you can see where a stream is by querying the target yourself, and resetting a stream is just truncating or dropping the table.

Getting Started

Install the Sling CLI if you have not already.

# macOS
brew install slingdata-io/sling/sling

# Windows (Scoop)
scoop bucket add sling https://github.com/slingdata-io/scoop-sling.git
scoop install sling

# Linux
curl -LO 'https://github.com/slingdata-io/sling-cli/releases/latest/download/sling_linux_amd64.tar.gz'
tar xf sling_linux_amd64.tar.gz && chmod +x sling

Set up the two connections and test them:

sling conns set POSTGRES type=postgres host=... user=... database=... password=... port=5432
sling conns set SNOWFLAKE type=snowflake account=... user=... password=... database=... warehouse=...

sling conns test POSTGRES
sling conns test SNOWFLAKE

The Replication File

Here is the whole thing. Two streams: one with a single primary key, one with a composite key. Both merge on their key and read incrementally on updated_at.

# replication.yaml
source: POSTGRES
target: SNOWFLAKE

defaults:
  mode: incremental
  object: analytics.{stream_table}

streams:
  # Single primary key. Sling upserts on customer_id and only pulls
  # rows where updated_at is newer than the last run.
  public.customers:
    primary_key: customer_id
    update_key: updated_at

  # Composite primary key. A row is identified by the pair
  # (customer_id, address_id), so late-arriving edits to one address
  # merge in place instead of duplicating.
  public.customer_addresses:
    primary_key: [customer_id, address_id]
    update_key: updated_at

First Run: The Initial Load

On the first run there is nothing in the target, so every row is an insert. Sling stages the data into a temporary table, then creates the final table and loads it.

$ sling run -r replication.yaml
INF [1 / 2] running stream demo_upserts.customers
INF getting checkpoint value (updated_at)
INF writing to target database [mode: incremental]
INF created table "DEMO_UPSERTS"."CUSTOMERS_TMP"
INF created table "DEMO_UPSERTS"."CUSTOMERS"
INF inserted 20000 rows into "DEMO_UPSERTS"."CUSTOMERS" in 17 secs
...
INF [2 / 2] running stream demo_upserts.customer_addresses
INF inserted 30000 rows into "DEMO_UPSERTS"."CUSTOMER_ADDRESSES" in 14 secs
INF Sling Replication Completed in 34s | 2 Successes | 0 Failures

Twenty thousand customers, thirty thousand addresses. Nothing surprising yet. The interesting part is the second run.

Late-Arriving Data: Updates Merge, They Do Not Duplicate

This is the case people worry about, and it is the whole reason to set a primary key. Between runs, two things happened on the source: customers 7 and 42 were edited (upgraded to a platinum tier, lifetime spend revised), and two thousand brand-new customers were added. Both the edits and the inserts carry a newer updated_at.

Run the same file again:

$ sling run -r replication.yaml
INF [1 / 2] running stream demo_upserts.customers
INF getting checkpoint value (updated_at)
INF writing to target database [mode: incremental]
INF inserted 2002 rows into "DEMO_UPSERTS"."CUSTOMERS" in 13 secs
...
INF [2 / 2] running stream demo_upserts.customer_addresses
WRN no data or records found in stream. Nothing to do.
INF inserted 0 rows into "DEMO_UPSERTS"."CUSTOMER_ADDRESSES" in 14 secs
INF Sling Replication Completed in 30s | 2 Successes | 0 Failures

Two things to read here. The customers stream touched exactly 2002 rows (the two edits plus the two thousand inserts), not the full twenty thousand. And the addresses stream, which had no changes at all, found nothing new and did a clean no-op. Sling does not rewrite tables that have not moved.

The word “inserted” in that log line is Sling’s generic term for “wrote to the target”; it covers both the updates and the inserts. What matters is what landed. Before the run, customer 7 was on the bronze tier. After:

select customer_id, loyalty_tier, lifetime_spend
from analytics.customers
where customer_id in (7, 42);

| CUSTOMER_ID | LOYALTY_TIER | LIFETIME_SPEND |
+-------------+--------------+----------------+
|           7 | platinum     |     9999.99    |
|          42 | platinum     |     9999.99    |

The edits landed as updates. And the row count tells the rest of the story:

select count(*) as customers, count(distinct customer_id) as distinct_ids
from analytics.customers;

| CUSTOMERS | DISTINCT_IDS |
+-----------+--------------+
|     22000 |        22000 |

Twenty-two thousand, not twenty-four thousand. The two edited rows were updated in place, the two thousand new rows were inserted, and the count of distinct IDs equals the count of rows. No duplicates. That is the guarantee the primary key buys you.

What Sling Runs Under the Hood

None of this is magic. On a target with a native MERGE (Postgres, Snowflake, SQL Server, Oracle, BigQuery, and more), Sling builds one merge statement per stream. Running with --debug shows the exact SQL, generated by GenerateMergeSQLWithStrategy:

MERGE INTO "DEMO_UPSERTS"."CUSTOMERS" tgt
USING (SELECT ... FROM "DEMO_UPSERTS"."CUSTOMERS_TMP") src
ON (src."CUSTOMER_ID" = tgt."CUSTOMER_ID")
WHEN MATCHED THEN UPDATE SET "FULL_NAME" = src."FULL_NAME", ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...)

The ON clause is where the primary key shows up, which is exactly what changes for a composite key.

Composite Keys: When One Column Is Not Enough

The customer_addresses table is keyed on the pair (customer_id, address_id). A single customer has a billing address and a shipping address, and each is its own row. A single-column key would be wrong here: keying on customer_id alone would treat both addresses as the same record and clobber one with the other.

Declaring a composite key is just a list:

public.customer_addresses:
  primary_key: [customer_id, address_id]
  update_key: updated_at

Sling folds every column in the list into the merge condition. The generated ON clause for this stream reads:

ON (src."CUSTOMER_ID" = tgt."CUSTOMER_ID" and src."ADDRESS_ID" = tgt."ADDRESS_ID")

A row only counts as a match when both key columns agree. Edit the shipping address for customer 5 and it updates that one row; the billing address for the same customer is a different key and is left untouched. Checked the same way as before, the thirty thousand addresses still map to thirty thousand distinct (customer_id, address_id) pairs after re-runs. No accidental collapse, no duplication.

Type Evolution: When the Schema Changes Mid-Stream

Sources do not hold still. Someone adds a churn_risk column to the customers table, populates it for a slice of rows, and nobody tells your pipeline. Some tools fail the run here. Others silently drop the column. Sling adds it.

The behavior is governed by the add_new_columns target option, which defaults to true. Nothing in the replication file changes. After a churn_risk numeric column appeared on the source and five hundred rows were updated to carry a value, the next incremental run picked it up:

$ sling run -r replication.yaml --streams public.customers
INF getting checkpoint value (updated_at)
INF writing to target database [mode: incremental]
INF inserted 500 rows into "DEMO_UPSERTS"."CUSTOMERS" in 13 secs

Before the run, the target had six columns and no churn_risk. After, the column is there:

select column_name, data_type
from information_schema.columns
where table_name = 'CUSTOMERS' and column_name = 'CHURN_RISK';

| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| CHURN_RISK  | NUMBER    |

Sling altered the Snowflake table to add the column, then upserted the five hundred rows. The count stayed at twenty-two thousand. These were updates to existing customers, so no new rows appeared; they just gained a value in a column that did not exist a minute earlier. Rows that predate the column keep a NULL there, which is exactly right.

Some teams treat schema drift as an incident rather than a convenience. If you would rather a surprise column fail the run, set add_new_columns: false under target_options. And if a column’s type actually changes (not a new column, but an int widening to bigint), adjust_column_type: true lets Sling widen the target type instead of erroring. The blog has a fuller treatment of the trade-offs in how Sling deals with schema evolution.

Choosing a Merge Strategy

The default upsert, update matches and insert the rest, is what Sling calls update_insert. It is not the only option. The merge strategy is a target option, and there are four:

Each database has a sensible default. Postgres and Snowflake default to update_insert; MySQL, Redshift, and ClickHouse default to delete_insert because they lack a native MERGE. Override only when you have a reason:

streams:
  # append-only event log: never update an existing event
  public.events:
    primary_key: event_id
    update_key: event_ts
    target_options:
      merge_strategy: insert

  # dimension table: enrich known products, never create new ones
  public.products:
    primary_key: product_id
    target_options:
      merge_strategy: update

A note on deletes, since it is the first thing people ask: a standard upsert never removes rows. If a record disappears from the source, it stays in the target. When you need delete-aware syncs, the delete_missing target option (soft or hard) reconciles the target to the source’s key set. Otherwise, soft-delete with a deleted_at column, or run full-refresh on tables small enough to reload.

Verification

An upsert is only doing its job if three things hold after every run: no duplicates, composite keys respected, and updates actually landing. Here is how to check each one directly.

-- 1. No duplicates: row count equals distinct-key count
select count(*) as rows,
       count(distinct customer_id) as distinct_keys
from analytics.customers;
-- 22000 | 22000

-- 2. Composite key holds too
select count(*) as rows,
       count(distinct customer_id || '-' || address_id) as distinct_pairs
from analytics.customer_addresses;
-- 30000 | 30000

-- 3. Updates landed (spot-check a row you changed)
select customer_id, loyalty_tier, churn_risk
from analytics.customers
where customer_id = 7;
-- 7 | platinum | 0.655

If rows ever exceeds distinct_keys, something is appending when it should be merging. That is almost always a missing or wrong primary_key.

Conclusion

Upserts in Sling come down to one rule: set a primary_key and updates merge; omit it and everything appends. Once the key is there, the rest follows. Composite keys are a list. Late-arriving edits update in place with no duplicates. A column that appears mid-stream gets added rather than dropped. None of it needs a side-state store or a custom merge script. The target table is the checkpoint, and the database’s own MERGE does the work.

Next Steps

• Schedule the replication with cron, an orchestrator, or the Sling Platform so upserts run continuously.
• Read the merge strategy reference to match the strategy to each table’s shape.
• Join the Sling Discord or open an issue on GitHub.

Related Guides

• Export PostgreSQL to Snowflake — the full source-to-target setup this guide builds on
• Snowflake to Postgres — the reverse direction, incremental included
• Postgres to DuckDB — the same upsert config against a local warehouse
• How Sling deals with schema evolution — the deeper story behind add_new_columns