BigQuery Loader

The BigQuery loader pulls tables from a BigQuery dataset into your data warehouse — warehouse-to-warehouse loading.

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 BigQuery’s own record of what changed, using the APPENDS and CHANGES table functions. The second exists because the first is only as honest as the column it reads: an upstream job that updates rows without changing them, but moves their timestamp, makes the loader re-deliver rows that did not really change, and an edit that moves no timestamp is never seen at all.

Prerequisites

• A GCP service account with roles/bigquery.jobUser in the project and roles/bigquery.dataViewer on the dataset you want to read. That covers everything the loader runs, including the change feed.
• A connected Warehouse as the load target, with write permissions on the target schema

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.

Change detection

Set per stream (per table), not per loader, because what a table supports is a property of that table.

Timestamp cursor (default)

Compares a date or timestamp column against the last run’s position. By default Zeotap picks the first such column in catalog order; Cursor column overrides that. The Snowflake loader page describes the picker and its limits in full, and they are the same here.

Warehouse change feed

Reads BigQuery’s change history instead of a column. Two scopes:

New records only works on any regular table with no setup. APPENDS reads the dataset’s time-travel window, which every table has. An updated row is not reported at all, so an upstream rewrite that changes nothing about the record delivers nothing. The trade is permanent: a genuine later edit to that record is also never delivered, rows deleted at the source stay in the target, and nothing will tell you so.

Inserts, updates and deletes needs change history switched on first:

ALTER TABLE `my_dataset`.`my_table` SET OPTIONS (enable_change_history = TRUE);

This needs permission to update the table (bigquery.tables.update, which BigQuery Data Editor has), so it is usually run by whoever owns the table rather than by the loader’s own service account. It is not retroactive — only changes made after that statement can be read, which is why the first change-feed run always takes a full snapshot of the table before it starts reading the feed. Switch it on at least twenty minutes before the stream’s first change-feed run: each read stops fifteen minutes behind BigQuery’s clock (see Freshness), and the first run checks the minute before that.

The stream picker says which scope each table supports. A table without change history is offered for new records only, with the statement above as the way to read everything. Views, materialized views, external tables, clones and snapshots are not offered at all, because neither function reads them.

BigQuery reports an update as two rows — the old values and the new — and reports every change in the interval rather than the net one. Zeotap collapses them to one row per primary key, the record’s state at the end of the interval, which is why this scope needs a key.

Changing a stream’s scope rebuilds its table. A table filled with new records only never received the updates and deletes made in the meantime, so switching to inserts, updates and deletes starts again from a full snapshot rather than leaving those rows stale.

Deletions and primary keys

These work exactly as on the Snowflake loader — see Deletions and Primary keys. Deletions are available only with inserts, updates and deletes and remove the row by default, and a merged change-feed stream upserts on the columns you name, never on an inferred key. BigQuery does not enforce primary keys, so Zeotap checks the key you name against the source when you save the loader and refuses one with duplicates.

Freshness

A change-feed read never reaches the present:

CHANGES does not report the last few minutes, and a row written in the last moments before a read may not be readable yet. Each run reads up to a point that far back, and the next run starts exactly there, so nothing is skipped — it simply arrives on the following run. A table created more recently than the lag cannot be seeded yet; the run says so and the next one succeeds.

Retention is your downtime budget

Both functions reach back only as far as the dataset’s time-travel window — between two and seven days, seven by default:

ALTER SCHEMA `my_dataset` SET OPTIONS (max_time_travel_hours = 168);

A loader paused, failing, or not scheduled for longer than that cannot be served incrementally. Zeotap reads a fresh snapshot of the table on the next run rather than skipping the gap. What it does with that snapshot depends on the sync mode:

The same happens when the source table has been recreated (CREATE OR REPLACE). A stream’s first change-feed run, and a switch to the change feed from a timestamp cursor, still replace the table: there is no change-feed history in it yet to keep.

The stream picker shows the window next to the toggle; when the dataset’s settings cannot be read, it shows nothing and the loader assumes the two-day minimum.

A run reads at most one day of changes. A loader that has fallen further behind advances a day per run and catches up over successive runs.

When the change feed is the wrong choice

The change feed records what happened to a table, so how the table is maintained upstream matters:

The first run of a change-feed stream reads the whole table through a query, and BigQuery limits a query’s result to 10 GB compressed. A larger table cannot be seeded this way; read it with a timestamp cursor or full refresh, which do not have that limit.

Cost

BigQuery bills by bytes scanned. A change-feed stream scans the whole table on its first run and on every rebuild, and each interval read scans the table’s change history for that interval. Saving a merged change-feed stream also runs one query over the key columns to check they are unique. Raising max_time_travel_hours adds time-travel storage cost under physical storage billing.

Troubleshooting

Next Steps

• Creating a Loader — the general setup flow
• Snowflake Loader — the same change detection, on Snowflake
• Models — building on the loaded tables