Skip to contentManaged Kafka is live: Apache Kafka on AWS for half the cost of MSK

Sources

Snowflake source: setup and configuration

Configure Snowflake as a source in Artie with key-pair authentication, a read-only service user, and change tracking on each table you replicate.

What you’ll need

  • Your Snowflake account identifier
  • A role that can create users, roles and warehouses (SECURITYADMIN and SYSADMIN)
  • Ownership of each table you want to replicate, to turn on change tracking
  • A primary key on each table, either declared in Snowflake or set as a primary key override in Artie

Setup

  1. Generate a key pair

    Snowflake sources sign in with key-pair authentication. Generate an RSA key pair:

    Bash
    openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

    rsa_key.pub goes on the Snowflake user in the next step. You’ll paste rsa_key.p8 into Artie at the end.

  2. Create a role, service user and warehouse

    Replace the RSA_PUBLIC_KEY value with the contents of rsa_key.pub, without the BEGIN and END lines.

    setup.sql
    USE ROLE SECURITYADMIN;
    CREATE ROLE IF NOT EXISTS ARTIE_READER;
    CREATE USER IF NOT EXISTS ARTIE_READER
      TYPE = SERVICE
      RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...'
      DEFAULT_ROLE = ARTIE_READER
      DEFAULT_WAREHOUSE = ARTIE_WH;
    GRANT ROLE ARTIE_READER TO USER ARTIE_READER;
    
    USE ROLE SYSADMIN;
    CREATE WAREHOUSE IF NOT EXISTS ARTIE_WH
      WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
  3. Grant read access

    Grant the role access to the warehouse and to each table you want to replicate:

    grants.sql
    GRANT USAGE ON WAREHOUSE ARTIE_WH TO ROLE ARTIE_READER;
    GRANT USAGE ON DATABASE ANALYTICS TO ROLE ARTIE_READER;
    GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE ARTIE_READER;
    GRANT SELECT ON TABLE ANALYTICS.PUBLIC.ORDERS TO ROLE ARTIE_READER;
  4. Turn on change tracking

    As the table owner, turn on change tracking for each table:

    SQL
    ALTER TABLE ANALYTICS.PUBLIC.ORDERS SET CHANGE_TRACKING = TRUE;

    Change tracking only records changes made after it’s turned on. Artie snapshots each table first, so nothing before that is missed.

  5. Check the table's retention

    Artie reads changes from the table’s Time Travel history, so the table needs a DATA_RETENTION_TIME_IN_DAYS of at least 1. That’s Snowflake’s default.

    If the pipeline is down longer than the table’s retention, it stops with change history is no longer available and the table needs a backfill. On Enterprise Edition, a longer retention gives you more time:

    SQL
    ALTER TABLE ANALYTICS.PUBLIC.ORDERS SET DATA_RETENTION_TIME_IN_DAYS = 7;
  6. Add the source in Artie

    In the pipeline wizard, choose Snowflake as the source and enter your account identifier, the service user (ARTIE_READER), the private key from rsa_key.p8, the warehouse (ARTIE_WH) and the database.

Advanced

How does Artie read changes from Snowflake?

Artie snapshots each table once, then reads its changes with Snowflake’s CHANGES clause:

  • Every 5 minutes, Artie reads each table’s changes since the last read, ending 10 seconds behind Snowflake’s clock so a transaction that’s still committing isn’t missed.
  • After an outage, Artie catches up in windows of up to one hour and saves its place after each one.
  • Up to 4 tables are read at a time.

Each read runs a query on your warehouse, even when a table hasn’t changed. A small warehouse with AUTO_SUSPEND = 60 keeps that cost low.

Schema changes

When a column is added or dropped, Artie reads the table’s columns again and keeps syncing. If a change window still doesn’t match the new columns, for example because it ends before a column was added, the pipeline stops with columns changed and the table’s name.

Primary keys

Snowflake doesn’t enforce primary keys, so Artie uses the key declared on the table. If a table has no declared key, or the declared key isn’t unique, set a primary key override in the table’s settings.

A row whose primary key is NULL stops the sync with an error that names the column. Choose key columns that are never NULL.

Frequently asked questions

Can I replicate dbt models?

Incremental dbt models work as-is. dbt table models run CREATE OR REPLACE TABLE on every run, which drops the table’s change history, and the new table starts without change tracking. On its next read, Artie stops the whole pipeline, not just that table, with change tracking is not enabled. Turning change tracking back on doesn’t recover it, because the recreated table has no history from before the run, so the pipeline then stops with change history is no longer available. Use an incremental materialization for tables you replicate with Artie.