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

# Snowflake dynamic tables

> Replicate high-throughput DynamoDB and Amazon Keyspaces tables into Snowflake by streaming every change into an append-only table and letting a Snowflake dynamic table keep the latest row per key.

> **Note:**
>
> Dynamic tables are in early access. Contact the Artie team to turn them on for your company.

## Overview

By default, Artie keeps a Snowflake table up to date by merging each batch of changes into it. Each merge rewrites the affected micro-partitions, so very high change rates, common with DynamoDB and Amazon Keyspaces, can need a large warehouse to keep up.

With **dynamic tables**, Artie never merges. It streams every change into an append-only table with [Snowpipe Streaming](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-streaming-overview), and a [Snowflake dynamic table](https://docs.snowflake.com/en/user-guide/dynamic-tables-intro) keeps the latest row for each primary key. You keep querying the table by its usual name, which becomes a view.

Use this when you want to:

* Replicate a DynamoDB or Keyspaces table whose write volume is too high for merges to keep up with.
* Keep a full log of every change next to the current state of each row.

## How it works

For a table named `ORDERS`, Artie creates three objects in the destination schema:

| Object                  | Type          | What it holds                                                                                                         |
| ----------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------- |
| `ORDERS__ARTIE_CHANGES` | Table         | Every change Artie has read, appended in the order it arrives. Deletes are kept as rows with `__ARTIE_DELETE = TRUE`. |
| `ORDERS__ARTIE_LATEST`  | Dynamic table | The latest change for each primary key, built from `ORDERS__ARTIE_CHANGES`.                                           |
| `ORDERS`                | View          | The rows you query. It reads `ORDERS__ARTIE_LATEST` and leaves out deleted rows.                                      |

Each change gets an `__ARTIE_SEQ` number that grows over time, and the dynamic table keeps the row with the highest `__ARTIE_SEQ` for each primary key:

```sql
CREATE DYNAMIC TABLE ANALYTICS.PUBLIC.ORDERS__ARTIE_LATEST
  TARGET_LAG = '1 minute'
  WAREHOUSE = DWH
  REFRESH_MODE = INCREMENTAL
AS SELECT * FROM ANALYTICS.PUBLIC.ORDERS__ARTIE_CHANGES
QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY __ARTIE_SEQ DESC NULLS LAST) = 1;
```

The dynamic table uses the pipeline's warehouse and refreshes incrementally, so each refresh only reads changes that arrived since the last one. The view hides `__ARTIE_SEQ`, and also `__ARTIE_DELETE` unless the pipeline has **Soft-delete rows** turned on (see [Deletes](#deletes)).

Rows loaded by a backfill get `__ARTIE_SEQ = 0`, so any change streamed for the same key, before or after the backfill, wins over the backfilled row.

> **Info:**
>
> **Freshness depends on the target lag.** A row reaches `ORDERS__ARTIE_CHANGES` as soon as Artie streams it, and reaches the `ORDERS` view after the dynamic table's next refresh. Snowflake refreshes the dynamic table often enough to keep it within its [target lag](#target-lag) of the changes table.

## Before you start

* **Source:** DynamoDB or Amazon Keyspaces.

* **Destination:** Snowflake, with [key-pair authentication](/docs/destinations/snowflake#required-settings). Dynamic tables are fed by Snowpipe Streaming, which needs a key pair.

* **Privileges:** on top of the usual destination privileges, Artie's role needs `CREATE DYNAMIC TABLE`, `CREATE VIEW` and `CREATE PIPE` (for Snowpipe Streaming) on the destination schema, and `USAGE` on the pipeline's warehouse. Artie checks these when it validates the destination. These examples use `ARTIE_TRANSFER_ROLE` and `DWH`, the defaults in the [service account script](/docs/destinations/snowflake#required-settings), with the `ANALYTICS.PUBLIC` schema:

  ```sql
  GRANT CREATE DYNAMIC TABLE ON SCHEMA ANALYTICS.PUBLIC TO ROLE ARTIE_TRANSFER_ROLE;
  GRANT CREATE VIEW ON SCHEMA ANALYTICS.PUBLIC TO ROLE ARTIE_TRANSFER_ROLE;
  GRANT CREATE PIPE ON SCHEMA ANALYTICS.PUBLIC TO ROLE ARTIE_TRANSFER_ROLE;
  GRANT USAGE ON WAREHOUSE DWH TO ROLE ARTIE_TRANSFER_ROLE;
  ```

* **No existing table with the same name.** `ORDERS` becomes a view, so it can't already be a regular table. Enable dynamic tables on a table before it's deployed. If `ORDERS` already exists as a table, Artie stops before streaming any changes, with an error ending in `is a regular table, so it can't become the dynamic table's view: drop or rename it, then re-backfill`.

> **Warning:**
>
> **The whole pipeline switches to Snowpipe Streaming.** When any table in a pipeline uses a dynamic table, Artie writes to Snowflake with Snowpipe Streaming for that pipeline, even if **Snowpipe Streaming** isn't turned on in its settings.

## Steps

**Step 1: Grant the privileges**

Run the grants in [Before you start](#before-you-start) as a role that can grant them, such as `ACCOUNTADMIN`.

**Step 2: Turn on dynamic tables**

Edit the pipeline, open the table's advanced settings, and check **Dynamic table**. Set **Target lag** if you want something other than 1 minute. If you don't see **Dynamic table**, ask the Artie team to turn dynamic tables on for your company.

**Step 3: Deploy the pipeline**

Artie backfills the table into `ORDERS__ARTIE_CHANGES`. The first time it streams a batch of changes for the table, it creates `ORDERS__ARTIE_LATEST` and the `ORDERS` view.

**Step 4: Query the view**

Query `ORDERS` as you would any other table:

```sql
SELECT ORDER_ID, STATUS, UPDATED_AT
FROM ANALYTICS.PUBLIC.ORDERS
WHERE STATUS = 'shipped';
```

Artie recreates the view with `COPY GRANTS` whenever the table gains columns, so grants on `ORDERS` stay in place.

## Target lag

The target lag is how far behind `ORDERS__ARTIE_CHANGES` the dynamic table is allowed to fall. It defaults to `1 minute`.

To change it, edit the pipeline, open the table's advanced settings, set **Target lag**, and deploy. After the deploy, Artie applies it to the dynamic table the next time it writes to the table.

* **Format:** `<number> <unit>`, where the unit is `second`, `minute`, `hour` or `day`, singular or plural. For example `90 seconds`, `5 minutes` or `1 day`.
* **Minimum:** 1 minute. Snowflake doesn't allow a lower target lag.

A longer target lag means fewer refreshes, so less warehouse time. Snowflake only resumes the warehouse for a refresh when the changes table has new rows. See [Understanding costs for dynamic tables](https://docs.snowflake.com/en/user-guide/dynamic-tables/cost).

## Deletes

A delete at the source is appended to `ORDERS__ARTIE_CHANGES` as a row with `__ARTIE_DELETE = TRUE`. It becomes the latest row for its key, so the dynamic table keeps it as a tombstone.

* **Soft-delete rows off (default):** the `ORDERS` view leaves out deleted rows and doesn't have an `__ARTIE_DELETE` column.
* **Soft-delete rows on:** the view keeps deleted rows and shows `__ARTIE_DELETE`. A deleted row keeps its other column values only if the delete event carries them. For DynamoDB, that needs a stream view type of `NEW_AND_OLD_IMAGES`.

## Backfills

A re-backfill has to empty `ORDERS__ARTIE_CHANGES` first. Streamed rows always have a higher `__ARTIE_SEQ` than backfilled rows, so without emptying the table, a backfill couldn't replace rows that are out of date.

In the backfill dialog, choose **Empty tables first** or **Drop and recreate**. For a dynamic table, **Drop and recreate** empties `ORDERS__ARTIE_CHANGES` instead of dropping it, since dropping it would break the dynamic table built on it. The dynamic table and view stay in place.

**Keep existing rows** and **Rebuild and swap** aren't available for dynamic tables. Starting one returns `table "public.orders" uses a dynamic table, so it can only be backfilled after truncating or dropping it`.

## Schema changes

When the source gains a column, Artie adds it to `ORDERS__ARTIE_CHANGES` and the dynamic table picks it up on its next refresh. Artie then recreates the `ORDERS` view so it includes the new column. If a refresh doesn't add the column to the dynamic table, Artie recreates the dynamic table with `CREATE OR REPLACE ... COPY GRANTS`, which runs one full refresh.

Artie's [automatic schema changes](/docs/guides/artie/schema-evolution#automatic-schema-changes) skip tables that use dynamic tables, since Artie manages their columns itself.

## Monitoring

Snowflake suspends a dynamic table after five refreshes in a row fail, and doesn't resume it on its own. Artie checks the dynamic table's state at most once a minute while it writes to the table. If Snowflake suspended it because of errors, Artie runs one manual refresh and, if that works, resumes it. A dynamic table that someone suspended by hand is left alone.

To check a dynamic table yourself:

```sql
SHOW DYNAMIC TABLES LIKE 'ORDERS__ARTIE_LATEST' IN SCHEMA ANALYTICS.PUBLIC;

SELECT name, state, refresh_action, refresh_start_time, refresh_end_time
FROM TABLE(ANALYTICS.INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME => 'ANALYTICS.PUBLIC.ORDERS__ARTIE_LATEST'))
ORDER BY refresh_start_time DESC
LIMIT 20;
```

## Limitations

* **DynamoDB and Amazon Keyspaces sources only.** Other sources fail validation with `dynamic tables are only supported for DynamoDB and Amazon Keyspaces sources`.
* **Snowflake destinations only.**
* **Can't be switched on or off after the table is deployed.** `ORDERS` would have to change between a table and a view. Remove the table from the pipeline and add it again instead.
* **Primary keys can't change after the table is deployed.** The dynamic table's `PARTITION BY` can't be altered. Remove the table and add it again instead.
* **Not combined with** history mode, append-only mode, or [soft partitioning](/docs/guides/artie/soft-partitioning).
* **The changes table keeps every change.** Artie doesn't remove old rows from `ORDERS__ARTIE_CHANGES`, so it grows with your change volume, and you pay Snowflake storage for it.

## Frequently asked questions

**Can I query the changes table directly?**

Yes. `ORDERS__ARTIE_CHANGES` is a regular table with every change Artie has read, including `__ARTIE_SEQ` and `__ARTIE_DELETE`. Order by `__ARTIE_SEQ` to see a key's changes in order. Don't write to it, rename it, or drop it, since the dynamic table reads from it by name.

**Why does a row show up in the changes table before the view?**

The view reads the dynamic table, which Snowflake refreshes on its own schedule to stay within the target lag. A row is in `ORDERS__ARTIE_CHANGES` as soon as Artie streams it, and in `ORDERS` after the next refresh.

**What happens if a batch is written twice?**

If Artie retries a batch, its rows are streamed again with the same `__ARTIE_SEQ` values. The copies are identical, so the dynamic table still keeps the same row for each key.

**Can I grant access to the view?**

Yes. Grant `SELECT` on `ORDERS` as usual. Artie recreates the view and, when needed, the dynamic table with `COPY GRANTS`, so your grants are kept.