Rebuild and swap backfills

Rebuild and swap is in early access. Contact the Artie team to enable it for your account.

Overview

Rebuild and swap is a 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 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.

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:
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. Replace it with your role’s name. If another role owns the table, transfer it back:
GRANT OWNERSHIP ON TABLE ANALYTICS.PUBLIC.ORDERS TO ROLE ARTIE_TRANSFER_ROLE COPY CURRENT GRANTS;
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 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

Open the backfill dialog

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

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.

Start the backfill

Review the summary and confirm.To start the same backfill through the API, call Backfill pipeline tables with beforeBackfill set to swap_table:

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
  }'

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:

ALTER TABLE ANALYTICS.PUBLIC.ORDERS RESUME RECLUSTER;

Recreate streams on the table, if you have any

A Snowflake stream 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 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:

SHOW TABLES LIKE 'ORDERS\\_%' IN SCHEMA ANALYTICS.PUBLIC;

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

ALTER TABLE ANALYTICS.PUBLIC.ORDERS SWAP WITH ANALYTICS.PUBLIC.ORDERS_K3P9X;

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.

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 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.

​Overview

​How it works

​Before you start

​Steps

​What carries over

​Roll back a swap

​Limitations

​Frequently asked questions

Overview

How it works

Before you start

Steps

What carries over

Roll back a swap

Limitations

Frequently asked questions