Snowflake dynamic tables

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, and a Snowflake dynamic table 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:

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:

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

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.

Before you start

• Source: DynamoDB or Amazon Keyspaces.

• Destination: Snowflake, with key-pair authentication. 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, with the ANALYTICS.PUBLIC schema:

  SQLCopyCopied

  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.

Steps

1. Grant the privileges

   Run the grants in Before you start as a role that can grant them, such as ACCOUNTADMIN.

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.

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.

4. Query the view

   Query ORDERS as you would any other table:

   SQLCopyCopied

   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.

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

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