> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Exactly-Once Delivery

Status: In development.

An export delivers rows exactly once when every row of the source reaches the destination one time: none missing, none
twice, whatever fails along the way. What Amp guarantees today depends on the destination. This page explains when a
row can arrive twice, and how to find and remove the copies.

:::warning
At Amp v0.0.42, every export to Snowflake
[fails as it starts](/managing-amp/pipelines/destinations#register-a-snowflake-destination), without writing a row, so
nothing reaches Snowflake on this release. What this page says about Snowflake applies once a release fixes that:
exports will then deliver at least once, until exactly-once delivery to Snowflake ships.
:::

## Prerequisites

The examples check the export that [Export a dataset](/managing-amp/pipelines#export-a-dataset) creates, which writes
`acme/eth_mainnet@1.0.0`'s `blocks` and `transactions` up to block 24,000,099. They need:

* What [Exports](/managing-amp/pipelines#prerequisites) needs, including `jq`, `psql`, the database's connection URL in
  `WAREHOUSE_URL`, and the `warehouse` PostgreSQL [destination](/managing-amp/pipelines/destinations), whose
  `data_schema` is `eth`.
* [`ampsql`](/clis/ampsql#install), to read the source.
* For the Snowflake examples, the same export through a Snowflake destination whose schemas are `ETH` and `AMP`, like
  the one in [Register a Snowflake destination](/managing-amp/pipelines/destinations#register-a-snowflake-destination),
  and a way to run SQL in it.
* To [remove copies](/managing-amp/pipelines/exactly-once#remove-them), a database role that can delete from and drop the destination's tables, and delete
  from `amp_export_state`.

## What each destination guarantees

An export writes each microbatch's rows, then records the microbatch's last block in the destination's
[export state](/managing-amp/pipelines/destinations#export-state), and commits. When an export starts again, it
continues after the last block recorded (see [Resuming](/managing-amp/pipelines/resuming)). Whether a crash between the
two can leave rows behind depends on whether the database keeps them in the same transaction:

| Destination | Guarantee after a crash                                       | Why                                                                                                                                                                                             |
| ----------- | ------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL  | Exactly once                                                  | The rows and the position are committed in one transaction. A crash discards both, and the next run writes the microbatch again.                                                                |
| Snowflake   | None at v0.0.42: every export fails. At least once after that | Loading rows into Snowflake commits them right away, before the position that acknowledges them. A crash in between leaves rows the position doesn't cover, and the next run writes them again. |

On Snowflake, the copies a crash leaves are whole rows, identical to the originals, for a contiguous range of blocks: at
most one microbatch, so at most `worker.microbatch_max_interval` blocks (100,000 by default), per crash. They're most
likely while an export catches up on history, when its microbatches are large enough to be loaded in several parts. A
load that was running when the worker died can also finish on Snowflake's side afterwards.

Microbatches without rows load nothing, so they can't leave copies.

## Two exports writing one table

On both destinations, nothing stops two exports from writing the same table at once. Their rows mix, and each moves the
other's position, which can repeat or skip blocks. This happens when:

* **Two exports share a table** through the same destination: exports with different table lists, or of different
  dataset versions, that both include it. Amp only refuses an export identical to one that's active.
* **A worker process is cut off rather than stopped.** A process that's still finishing a microbatch can overlap the
  export's next run.

Give each destination table a single export, and stop a worker's process before replacing it.

## Rows from replaced blocks

Under the `append` [reorg policy](/managing-amp/pipelines#reorg-policies), a destination table keeps the rows of blocks
a reorg replaced, beside the new ones. They aren't copies: they hold what the chain said before the reorg. The check
below tells them apart, since they don't match their replacements. Use `replace` or `soft-delete` to have Amp handle
them.

## Find duplicate rows

::::steps
### Compare row counts

Pick a block at or below the table's position, then count the source's and the destination's rows up to it. They must
be equal. These examples use block 24,000,099, where the export in
[Export a dataset](/managing-amp/pipelines#export-a-dataset) ends. In the source, with [`ampsql`](/clis/ampsql):

:::terminal
```bash
ampsql --format jsonl "SELECT COUNT(*) AS row_count FROM \"acme/eth_mainnet\".transactions WHERE _block_num <= 24000099"
```

```text
{"row_count":17153}
```
:::

In the destination, here Snowflake, where the export writes `ETH.TRANSACTIONS`:

```sql
SELECT COUNT(*) FROM ETH.TRANSACTIONS WHERE _BLOCK_NUM <= 24000099;
```

On PostgreSQL, like the `warehouse` destination of [Exports](/managing-amp/pipelines#prerequisites), where the export
writes `eth.transactions`:

:::terminal
```bash
psql "$WAREHOUSE_URL" -tAc 'SELECT COUNT(*) FROM eth.transactions WHERE _block_num <= 24000099'
```

```text
17153
```
:::

More rows in the destination means copies or rows from replaced blocks.

### Find the blocks

List the blocks whose rows aren't all different:

```sql
SELECT _BLOCK_NUM, COUNT(*) AS c, COUNT(DISTINCT HASH(*)) AS d
FROM ETH.TRANSACTIONS
WHERE _BLOCK_NUM <= 24000099
GROUP BY 1
HAVING c <> d
ORDER BY 1;
```

A range written twice shows as consecutive blocks where `c` is twice `d`. On PostgreSQL, use `md5(t::text)` for a row's
hash, with `t` the table's alias:

```sql
SELECT _block_num, COUNT(*) AS c, COUNT(DISTINCT md5(t::text)) AS d
FROM eth.transactions AS t
WHERE _block_num <= 24000099
GROUP BY 1
HAVING COUNT(*) <> COUNT(DISTINCT md5(t::text))
ORDER BY 1;
```

When the counts differ but no block shows up, the extra rows come from replaced blocks rather than copies.
::::

This only finds copies in tables whose rows are all different in the source, like `blocks`, `transactions`, and `logs`.
A derived table can legitimately hold identical rows.

## Remove them

First make sure the export isn't writing while you fix the table. If its job's status is `COMPLETED`, `STOPPED`, or
`FATAL`, it has finished. Otherwise, [stop it](/managing-amp/pipelines#stop-it) and wait until its status is `STOPPED`.
Stopping a job that has already finished succeeds without changing its status, so an export that reached its end block
stays `COMPLETED`. Then either:

* **Remove the copies** in the range you found. On Snowflake, for blocks 24,000,050 to 24,000,099:

  ```sql
  CREATE TEMPORARY TABLE ETH.DEDUPED AS
    SELECT DISTINCT * FROM ETH.TRANSACTIONS WHERE _BLOCK_NUM BETWEEN 24000050 AND 24000099;
  BEGIN;
  DELETE FROM ETH.TRANSACTIONS WHERE _BLOCK_NUM BETWEEN 24000050 AND 24000099;
  INSERT INTO ETH.TRANSACTIONS SELECT * FROM ETH.DEDUPED;
  COMMIT;
  ```

* **Export the table again**: drop it, and delete its rows from `amp_export_state`, so the next export of it is a fresh
  one. Match both its schema and its name: a table of the same name in another schema has its own rows, which must
  stay.

  ```sql
  DROP TABLE ETH.TRANSACTIONS;
  DELETE FROM AMP.AMP_EXPORT_STATE WHERE TARGET_SCHEMA = 'ETH' AND TARGET_TABLE = 'TRANSACTIONS';
  ```

Then create the export again. It continues the tables that kept their position, and exports the dropped ones from the
beginning.

## Limitations

* **Snowflake exports fail at v0.0.42**, as the warning above says. Once they work, they deliver at least once, until
  exactly-once delivery to Snowflake ships.
* **Nothing keeps two exports out of one table**, on either destination.
* **Each table is delivered on its own.** An export's tables are written separately, so they can be at different blocks
  at any moment, and a crash affects each one separately.