Snowflake Loader

The Snowflake loader pulls tables from a Snowflake database into your data warehouse — warehouse-to-warehouse loading, most commonly Snowflake into BigQuery.

It offers two ways to read incrementally. A timestamp cursor compares a column such as UPDATED_AT against the position of the last run. A change feed reads Snowflake’s own record of what changed, using the CHANGES clause over a table with change tracking enabled. The second exists because the first is only as honest as the column it reads: a table whose timestamps are rewritten by an upstream refresh re-delivers rows nothing about the record actually changed in.

Prerequisites

• A Snowflake account, with a warehouse for running queries
• A Snowflake user with USAGE on the database, schema and warehouse, and SELECT on the tables you want to read
• A connected Warehouse as the load target, with write permissions on the target schema

Authentication

Two methods, both configured when you add the loader.

Account identifier

The Account Identifier field takes myorg-my_account (organization-account) or the legacy locator form xy12345.us-east-1.

It is not the full URL. https://myorg-my_account.snowflakecomputing.com is a browser address; the identifier is the myorg-my_account part. Zeotap normalises common variations, but a full URL with a scheme is rejected rather than guessed at.

Configuration

Sync modes

Merge mode is what you want for anything a model, audience or trait reads, because Zeotap’s own engine queries the loaded table directly — duplicate rows are not a staging-area detail you can ignore downstream.

Change detection

Set per stream (per table), not per loader, because change tracking is enabled table by table on your Snowflake account.

Timestamp cursor (default)

Compares a monotonically increasing column against the last run’s position:

SELECT ... FROM MY_TABLE WHERE UPDATED_AT > '<last position>' ORDER BY UPDATED_AT

By default Zeotap picks the first column whose type is a date or a timestamp, in catalog order — which is not necessarily the column you would have chosen. Cursor column overrides that choice.

The picker lists the stream’s date and timestamp columns, with the auto-detected one named so you can see what you are overriding. Leave it on Auto-detected and nothing changes from how loaders have always read.

Pick the column that moves when the record changes. A table with both CREATED_AT and UPDATED_AT auto-detects CREATED_AT, because it comes first — and CREATED_AT never moves after insert, so every later edit is missed. Nothing reports this: the runs succeed and the rows are simply never re-read. It is the single most common way a cursor loader quietly goes stale.

Three things the picker will not let you do, each because the alternative fails silently:

• Only date and timestamp columns are offered. A monotonically increasing integer version column is a reasonable thing to want, and is refused: every warehouse builds its comparison from the cursor’s declared type, and a type with no comparison produces no WHERE clause at all — a full-table read that ignores your choice while reporting success.
• A column that does not exist fails the run, naming what you typed. It does not fall back to auto-detection, because a loader reading a different column than the one you chose re-delivers or skips rows with nothing to say so.
• The setting only appears on warehouse sources (Snowflake, BigQuery, Databricks, ClickHouse, Redshift) — the ones that guess. A SaaS loader’s cursor is declared by its connector, so there is nothing to choose between.

The approach’s limitation is not a bug but a property of it: if anything rewrites the timestamp without changing the record — a nightly refresh, a pipeline that re-materialises the table, a MERGE from an upstream system — every touched row is re-delivered. And the converse, which the change feed does not share: an edit that moves no timestamp is invisible to a cursor forever.

Warehouse change feed

Reads Snowflake’s CHANGES clause, which reports the difference between two versions of a table.

Enable change tracking on each table first:

ALTER TABLE MY_TABLE SET CHANGE_TRACKING = TRUE;

This needs OWNERSHIP (or equivalent) on the table, so it is usually a DBA action rather than something the loader’s own user can do. It is not retroactive — only changes committed after that statement can be read, which is why the loader’s first run always takes a full snapshot of the table before it starts reading the feed.

Two scopes:

New records only is the answer to the upstream-refresh problem: an updated row is not reported at all, so a rewrite that changes nothing about the record delivers nothing. The trade is permanent and worth stating plainly — a genuine later edit to that record is also never delivered, and nothing anywhere will tell you so.

Deletions

Available only with inserts, updates and deletes, since the other scope never reports one.

Marking is the gentler primitive but is not the default, deliberately. It obliges every query against that table — including audiences, traits and identity resolution — to filter _ss_deleted = false, forever, including queries written later by people who do not know the column exists. One that forgets keeps deleted people in live segments, which fails silently. Removing the row needs nothing from downstream, because the table then means current state, which is what every consumer already assumes.

Primary keys

A merged change-feed stream upserts on the columns you name, and only those. Nothing is inferred for one — no catalog lookup, no column called id.

That is stricter than merge mode is elsewhere, and the reason is the failure mode. A merge reduces each batch to one row per key before upserting, so a key that does not identify a row does not produce an error — it silently combines distinct records into one row and reports success. Snowflake does not enforce uniqueness even on a declared PRIMARY KEY constraint, so nothing downstream catches it either. Zeotap checks the key you name against the source when you save the loader and refuses a key with duplicates.

Retention is your downtime budget

The change feed reaches back only as far as the table’s DATA_RETENTION_TIME_IN_DAYS:

A loader paused, failing, or simply not scheduled for longer than that cannot be served incrementally. Zeotap rebuilds the table from a fresh snapshot on the next run rather than skipping the gap — correct, but a full reload. The loader’s stream picker shows each table’s retention next to the toggle for this reason.

-- Widen the window on an Enterprise account:
ALTER TABLE MY_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 7;

A run whose backlog is wider than one read may carry advances as far as it can and continues on the next run, so a loader that has fallen behind catches up over successive runs rather than attempting one enormous read.

Data types

Snowflake types map to the target warehouse as follows.

Timestamp precision

Snowflake’s timestamps default to nanosecond precision (TIMESTAMP_NTZ(9)). BigQuery’s TIMESTAMP is microsecond precision, so sub-microsecond digits are dropped in transit. This is a silent truncation, not an error — if two rows differ only below the microsecond, they will be indistinguishable in the target.

Epoch timestamps stored as numbers

A column holding epoch nanoseconds as a NUMBER is loaded as an integer, not a timestamp — Zeotap maps by declared type, not by what the values look like. It is also not a cursor candidate, so a table whose only time column is stored this way will not auto-detect a cursor.

If you need it as a real timestamp, expose a view that converts it, and point the loader at the view:

CREATE OR REPLACE VIEW MY_TABLE_V AS
SELECT *, TO_TIMESTAMP_NTZ(EVENT_TIME_NANOS, 9) AS EVENT_TIME
FROM MY_TABLE;

The scale argument matters: 9 for nanoseconds, 6 for microseconds, 3 for milliseconds, 0 for seconds. Getting it wrong shifts every value by orders of magnitude rather than failing.

Statement timeout

Every session the loader opens sets a server-side statement timeout (default two hours) so a query orphaned by a dead worker cannot keep a warehouse billing. A very wide change-feed interval on a large table can hit it; narrowing the loader’s schedule so each run covers less is the fix.

Troubleshooting

Next Steps

• Creating a Loader — the general setup flow
• Warehouses — connecting the target warehouse
• Models — building on the loaded tables