> ## Documentation Index
> Fetch the complete documentation index at: https://artie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Rebuild and swap backfills

> Reload a Snowflake table without downtime. Artie backfills into a new table and swaps it in when the load finishes, so the table stays queryable with complete data the whole time.

> [!NOTE]
>
> Rebuild and swap is in early access. Contact the Artie team to enable it for your account.

## Overview

**Rebuild and swap** is a [backfill](/docs/pipelines/backfill) option for Snowflake destinations. Artie loads the backfill into a separate table next to the destination table, then swaps the two with [`ALTER TABLE ... SWAP WITH`](https://docs.snowflake.com/en/sql-reference/sql/alter-table) once every row is loaded. The destination table keeps serving its current rows until the swap, and the swap itself is atomic.

Use this when you want to:

* Fully reload a table that dashboards, dbt models or other jobs query continuously, without an empty or partially loaded window.
* Re-sync a table whose data has drifted from the source, including rows that were deleted at the source but are still in the destination.
* Keep the pre-reload data around to compare against or roll back to.

Compared with the other backfill options:

| | Do nothing | Truncate | Drop table | Rebuild and swap |
| - | - | - | - | - |
| Table during the backfill | Current rows, updated in place | Empty, then partially loaded | Missing, then partially loaded | Current rows, unchanged |
| Removes rows no longer in the source | No | Yes | Yes | Yes |
| Keeps grants, tags and policies | Yes | Yes | No | Yes |
| Table if the backfill fails | Partially updated | Partially loaded | Partially loaded | Unchanged |
| Extra storage | None | None | None | A second copy of the table |
| Destinations | All | All | All | Snowflake |

## How it works

Artie backfills into a rebuild table named `<TABLE>_<suffix>`, where `<suffix>` is five random characters generated for each attempt. While the backfill runs, CDC changes for the table are held in Kafka, so the destination table keeps the rows it had when the backfill started. When the backfill completes, Artie swaps the tables, drops the old rows, and applies the held changes to the rebuilt table.

In Snowflake, the rebuild table is created with `CREATE OR REPLACE TABLE ... CLONE ... COPY GRANTS`, and the swap is a single `ALTER TABLE ... SWAP WITH` statement. Cloning copies no data, so creating the rebuild table is fast at any table size.

> [!NOTE]
>
> **Progress is reported against the destination table.** The backfill writes to `<TABLE>_<suffix>`, but row counts and progress in the dashboard show under the destination table's name. When you select more than one table, each table swaps as soon as its own backfill finishes.

## Before you start

* **Feature needs to be enabled.** Until it is, the **Rebuild and swap** option doesn't appear in the backfill dialog, and the API returns `rebuild and swap backfills are not enabled for this company`.

* **Artie's role owns the destination table.** The swap renames the destination table, and Snowflake only lets the table's owner do that. Artie creates destination tables with its own role, so this is already true unless someone transferred ownership of the table to another role. Check the owner:

  ```sql theme={null}
  SHOW GRANTS ON TABLE ANALYTICS.PUBLIC.ORDERS;
  ```

  The row with `privilege = OWNERSHIP` should name the role Artie connects with. These examples use `ARTIE_TRANSFER_ROLE`, the default in the [service account script](/docs/destinations/snowflake#required-settings). Replace it with your role's name. If another role owns the table, transfer it back:

  ```sql theme={null}
  GRANT OWNERSHIP ON TABLE ANALYTICS.PUBLIC.ORDERS TO ROLE ARTIE_TRANSFER_ROLE COPY CURRENT GRANTS;
  ```

  > [!WARNING]
  >
  > Ownership is only checked at the swap, after the whole table has loaded. Without it, the backfill fails at the end with `failed to swap tables`, followed by Snowflake's error. The destination table is unchanged, but you have to fix ownership and run the backfill again.

* **Artie's role has `CREATE TABLE` on the destination schema.** The [service account script](/docs/destinations/snowflake#required-settings) grants this.

* **The schema has room for a second copy of the table.** The rebuild table holds a full copy while the backfill runs, and the old rows stay after the swap if you keep them.

## Steps

**1. Open the backfill dialog**

Open the pipeline overview page, click **Backfill tables**, and select the tables to reload.

**2. Select Rebuild and swap**

In the **Existing rows** step, select **Rebuild and swap**. To keep the pre-swap rows, also check **Keep the old table for rollback, until the next rebuild and swap**. See [Roll back a swap](#roll-back-a-swap).

**3. Start the backfill**

Review the summary and confirm.

To start the same backfill through the API, call [Backfill pipeline tables](/docs/api-reference/pipelines/backfill-pipeline-tables) with `beforeBackfill` set to `swap_table`:

```bash theme={null}
curl -X POST "https://api.artie.com/pipelines/7f1c2a9e-4b3d-4e8a-9c61-2d5f0b8e3a17/backfill" \
  -H "Authorization: Bearer $ARTIE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "tableUUIDs": ["c3e8a1d4-5f2b-4a9c-8e7d-1b6f3c0a9e52"],
    "beforeBackfill": "swap_table",
    "keepOldTable": true
  }'
```

**4. Resume automatic clustering, if the table used it**

Snowflake suspends automatic clustering on a clone, so the rebuilt table has it suspended. After the swap, resume it as Artie's role, which owns the table:

```sql theme={null}
ALTER TABLE ANALYTICS.PUBLIC.ORDERS RESUME RECLUSTER;
```

**5. Recreate streams on the table, if you have any**

A [Snowflake stream](https://docs.snowflake.com/en/user-guide/streams-intro) follows the table object, not its name. After the swap, existing streams track the old rows, and they go stale once that table is dropped. Recreate them on the rebuilt table.

## What carries over

The rebuild table is a [Snowflake clone](https://docs.snowflake.com/en/user-guide/object-clone) of the destination table, created with `COPY GRANTS`.

| Property | After the swap |
| - | - |
| Columns and data types | Kept. The rebuild starts from the destination table's current columns, and new source columns are added during the backfill as usual. |
| Grants | Kept, except `OWNERSHIP`, which stays with Artie's role. |
| Tags | Kept. |
| Masking and row access policies | Kept. |
| Table and column comments | Kept. |
| Clustering key | Kept. Automatic clustering is suspended until you resume it. |
| Search optimization | Kept. |
| Object parameters, such as `DATA_RETENTION_TIME_IN_DAYS` | Kept. |
| Views on the table | Keep working. Views resolve the table by name. |
| Streams on the table | Not carried over. Recreate them. |
| Time Travel history | Starts when the rebuild table was created. Older history stays with the old table. |
| Rows no longer in the source | Removed. The rebuilt table only has rows the backfill read from the source. |

## Roll back a swap

With **Keep the old table for rollback, until the next rebuild and swap** checked, Artie leaves the old rows in `<TABLE>_<suffix>` after the swap. They stay there until the next rebuild and swap of the same table, which drops them before it starts loading. Unchecked, Artie drops the old rows right after the swap.

Find the kept table:

```sql theme={null}
SHOW TABLES LIKE 'ORDERS\\_%' IN SCHEMA ANALYTICS.PUBLIC;
```

Swap it back as Artie's role, which owns both tables:

```sql theme={null}
ALTER TABLE ANALYTICS.PUBLIC.ORDERS SWAP WITH ANALYTICS.PUBLIC.ORDERS_K3P9X;
```

> [!WARNING]
>
> After a manual rollback, `ORDERS` holds the pre-backfill rows. CDC changes applied since the swap are in `ORDERS_K3P9X`, and Artie keeps writing new changes to `ORDERS`. Run a new backfill to bring the table back in line with the source.

## Limitations

* **Snowflake only.** The dashboard doesn't offer the option for other destinations, and the API rejects it with `rebuild and swap backfills are only supported for Snowflake destinations`. Use **Truncate** or **Drop table** instead.
* **No history tables.** A history table keeps rows the source no longer has, and a rebuild would drop them. The API rejects it with `history tables cannot be backfilled with rebuild and swap`. Use **Truncate** or **Drop table** for history tables.
* **Whole tables only.** The swap replaces the whole table, so the backfill has to cover every row:
  * A backfill limited to specific source schemas or databases is rejected with `cannot drop/truncate tables before backfilling if only backfilling from certain schemas` (or `certain databases`). Backfill those with **Do nothing**.
  * A restart limited to a primary key range is rejected with `a rebuild and swap backfill cannot be limited to a primary key range`. Restart without a range.
* **No soft partitioning.** The backfill fails before loading any rows with `swapping tables is not supported with soft partitioning`. Use **Truncate** or **Drop table** for tables with [soft partitioning](/docs/guides/artie/soft-partitioning).

## Frequently asked questions

> **Does the destination table get CDC changes during the backfill?**
>
> No. As with any backfill, CDC changes for the table are held in Kafka until the backfill completes. The destination table keeps the rows it had when the backfill started. After the swap, Artie applies the held changes to the rebuilt table and the table goes back to streaming.

> **What happens if I cancel the backfill, or it fails?**
>
> The destination table is unchanged, since it only changes at the swap. The partly loaded rebuild table stays in the schema until the next rebuild and swap of that table, which drops it before it starts loading.

> **What happens if I restart the backfill?**
>
> A restart is a new attempt with a new rebuild table and a new suffix. Artie drops the previous attempt's rebuild table before it starts loading.

> **Can I query the rebuild table while the backfill runs?**
>
> Yes. `<TABLE>_<suffix>` is a regular table in the same schema and fills up as the backfill runs. Don't write to it or rename it, since Artie swaps it in by name.

> **Why does Artie's role need to own the destination table?**
>
> `ALTER TABLE ... SWAP WITH` renames both tables, and [Snowflake requires](https://docs.snowflake.com/en/sql-reference/sql/alter-table) the role running it to have `OWNERSHIP` on both. Artie's role owns the rebuild table because it created it. The destination table is renamed too, so Artie's role has to own it as well. Grants such as `ALL PRIVILEGES` don't include `OWNERSHIP`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.