Snowflake Capture

Overview

Snowflake Capture reads changes from configured Snowflake base tables by using the CHANGES clause. It writes records to a local GoldenGate trail for downstream processing.

Capture can resume from a saved Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) position if Snowflake retains the required change history. Set Time Travel retention to cover expected outages and catch-up. The CHANGES clause does not expose Snowflake transaction IDs or individual row commit times, so the trail does not include those details. See Snowflake CHANGES clause and Snowflake Time Travel. For more information, see Snowflake CHANGES clause and Snowflake Time Travel.

How Capture Runs

Extract checks the configured tables at regular intervals and writes available row changes to the trail. It saves a position so it can restart from that point. Retain Snowflake change history long enough to cover expected outages and catch-up.

Setting Up Snowflake Capture

Prerequisites

Warehouse, Role, and Privileges

A Snowflake virtual warehouse provides the compute resources for SELECT … CHANGES queries. Larger or more concurrent capture workloads might need more capacity. Use query latency, queueing, catch-up rate, and Snowflake credit consumption to assess warehouse size. Configure auto-resume and auto-suspend settings to meet cost and recovery objectives, and monitor the warehouse during capture.

Use a dedicated least-privilege Snowflake role when practical. Grant access only to the warehouse and captured objects. The capture role generally needs these privileges:

Privilege or Visibility Purpose
USAGE on the virtual warehouse Run capture queries on the selected compute resource.
USAGE on the database and schema Resolve the configured catalog.schema.table names.
SELECT on each captured table Read rows and change data through CHANGES.
Metadata visibility Resolve objects and inspect tracking and effective retention settings during validation.
ALTER on a table (optional) Required only when Add Trandata is used to enable CHANGE_TRACKING automatically

Enable Change Tracking and Plan Retention

Enable change tracking before you start Extract. Add Trandata can enable it if the role has ALTER privilege. Else, run the Snowflake ALTER TABLE statement with a role that has the required privilege.

ALTER TABLE "CATALOG_A"."SALES"."ORDERS" SET CHANGE_TRACKING = TRUE;

Inspect the effective Time Travel retention for each captured table. Increase retention only to a value allowed for the account, table type, and edition. Higher retention can increase storage costs. For more information, see Snowflake Time Travel and Snowflake ALTER TABLE.

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE "CATALOG_A"."SALES"."ORDERS";

ALTER TABLE "CATALOG_A"."SALES"."ORDERS" SET DATA_RETENTION_TIME_IN_DAYS = 7;

Set retention to cover the oldest position from which Extract might need to recover, plus the outage and catch-up time the deployment must tolerate. Increasing retention does not restore change history that has already aged out of Time Travel.

Use Fully Qualified Object Names

In Snowflake, a three-part name has the form catalog.schema.table, where catalog is the Snowflake database. Use three-part names in TABLE parameter entries, even if a shorter name resolves in the current session context.

TABLE "CATALOG_A"."SALES"."ORDERS"; TABLE "CATALOG_B"."HR"."EMPLOYEES";

Use double quotation marks for names that contain lowercase letters, spaces, punctuation, or other case-sensitive characters. Snowflake normally resolves unquoted identifiers as uppercase; quoted identifiers preserve their exact spelling. If Extract cannot find a table, check the spelling and quoting of each name component.

Extract resolves wildcard or regular-expression entries to concrete base tables when it starts. It does not discover new matching tables while it is running. Restart Extract after validating a new table’s change tracking, privileges, retention, and baseline. If any resolved table is invalid, Extract fails instead of silently skipping it.

Data Types

Supported Snowflake Source Data Types

Snowflake Capture supports all Snowflake source data types available for capture from supported base tables. The following common types and aliases are examples; this list is not exhaustive:

Note: VARIANT, OBJECT, ARRAY, GEOGRAPHY, and GEOMETRY values are written to the Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) trail in JSON format.

Snowflake Database Operations

Supported Operations

Snowflake Capture supports INSERT, UPDATE, and DELETE changes on captured base tables. It writes the corresponding row-level records to the GoldenGate trail.

Snowflake CHANGES reports the net change to each row during a capture interval, so the number of trail records can differ from the number of source DML operations. For example, several updates to one row during an interval might appear as a single UPDATE that reflects the net result. An insert followed by a delete during the same interval might leave no row change to capture. For more information, see Snowflake CHANGES clause.

Unsupported Operations

Snowflake DDL and object changes are not captured as trail events. These changes include CREATE, ALTER, DROP, rename, and schema changes. Apply compatible target schema changes separately, and validate them. The CHANGES clause does not provide source transaction IDs, original transaction boundaries, rollback or savepoint events, or individual row commit times. The trail does not preserve these details. See Data Availability and Trail Semantics.

Add, Info, and Delete Trandata

Use Add Trandata in the Administration Service or Admin Client to enable CHANGE_TRACKING for a configured table. Add Trandata can update the Snowflake table, so the role must have ALTER privilege. If tracking is already enabled, use Info Trandata to check its status. Info Trandata reports whether the table is ready for capture; it does not correct an invalid start time or restore expired history.

ADD TRANDATA "CATALOG_A"."SALES"."ORDERS" INFO TRANDATA "CATALOG_A"."SALES"."ORDERS"

Use Delete Trandata only after removing the table from capture and confirming that no Extract needs its change history. Delete Trandata disables CHANGE_TRACKING. Enabling tracking again does not recreate the missing interval. Run a new initial load and coordinate a new CDC start to establish a complete baseline.

DELETE TRANDATA "CATALOG_A"."SALES"."ORDERS"

Table Changes and Wildcard Mappings

At startup, Extract validates a table added explicitly to its configuration along with the existing capture set. If the table does not have CHANGE_TRACKING or has insufficient retention, Extract fails to start for the configured set; it does not silently omit the table. Extract also expands wildcard entries only at startup. Restart Extract and plan a baseline to include matching tables that are created later. If a source table is dropped, renamed, recreated, or loses change tracking while Extract is running, the next query for that table can fail. Extract then stops rather than continuing with an unknown gap. Re-enabling tracking does not backfill the period when tracking was unavailable. Remove the table from the capture set or establish a new coordinated initial-load and CDC baseline before resuming it. Snowflake DDL is not written to the trail. Apply compatible schema changes to the target separately. Then validate the source object, target mapping, and Extract before resuming. Do not assume that Snowflake propagates a source schema change automatically.

Adding a Table to an Existing Extract

A newly added table needs an initial load aligned with its CDC start position. Adding a TABLE entry alone can result in missing or duplicate rows. For the simplest onboarding, create a separate Extract for the new table. Load it as of a chosen timestamp T0, then start CDC at T0 + 1 millisecond. If the table must join an existing Extract, stop the Extract and record its saved position. Align the new table’s initial-load snapshot with the position from which the Extract will resume. Verify CHANGE_TRACKING, retention, and access. Add the fully qualified TABLE entry, then restart Extract with the saved checkpoint intact. Confirm that the target has no gaps or duplicate rows. If you cannot establish the matching snapshot boundary, use a separate Extract instead of rewinding the existing Extract.

Adding a Snowflake Connection

Create the Snowflake connection in the Administration Service, test it, and record its alias. In release 26.3, create Snowflake connections in the web user interface. Admin Client does not support creating this connection type.

To create and validate a Snowflake connection:

  1. In the navigation menu, select DB Connections.
  2. Select Add Connection. In some releases, this control appears as +.
  3. In Connection Alias, enter a unique name for the connection.
  4. From Technology Type, select Snowflake.
  5. In Technology Settings, provide the following information:
  1. In Authentication Settings, select a supported authentication type and provide its required values. For key-pair authentication, select KEY_PAIR, then enter Username and Private Key. If the private key is encrypted, enter Private Key Password.

  2. In Common Settings, specify the Classpath for the client libraries required by the deployed Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) release. For more information, see Snowflake Capture Client Dependencies.

  3. If the deployment accesses Snowflake through an HTTP proxy, configure Proxy Settings. Provide the Proxy URL and Proxy Port, and enter proxy credentials or truststore and keystore details if required. If the deployment has direct network connectivity to Snowflake, leave Proxy Settings blank.
  4. Select Submit.
  5. On the DB Connections page, select DB Login to validate the saved connection.

A successful DB Login validates the saved connection information. It does not validate the Extract configuration. Use the connection alias in the Extract parameter file.

Note: Field names and availability can vary by release.

Using Initial Load Extract

Coordinating the Snapshot with CDC

Snowflake Capture CDC reads changes but does not load existing table contents. Use a separate non-CDC Initial Load Extract to establish the target baseline. For precise instantiation, select one Snowflake timestamp T0, use SQLPREDICATE to read the initial-load table as of T0, and start CDC at T0 + 1 millisecond. Snowflake CHANGES uses millisecond timestamp resolution and includes the end marker, so the one-millisecond offset prevents the snapshot boundary from being captured twice. See Snowflake CHANGES clause.

Use the same time-zone interpretation and precision for the initial-load predicate and the CDC BEGIN value. BEGIN TIMESTAMP is interpreted in the Extract host time zone and normalized internally to UTC. Keep Extract hosts synchronized, and document the selected time zone for operators.

Initial Load Example

EXTRACT sninit

SOURCEDB USERIDALIAS sf_capture

TABLE "CATALOG_A"."SALES"."ORDERS", SQLPREDICATE "AT (TIMESTAMP => '2026-10-05 10:00:00.123'::TIMESTAMP_NTZ)";

Use SQLPREDICATE to coordinate the Time Travel snapshot in this release. Replace the example SQL timestamp with the selected T0, and verify that the predicate reads the table version at that time. The current release does not support the Snowflake-specific INITIALLOADOPTIONS SNAPSHOT option.

Starting CDC After the Snapshot

ADD EXTRACT sncdc, TRANLOG, BEGIN 2026-10-05 10:00:00.124

ADD EXTTRAIL ./dirdat/ss, EXTRACT sncdc

This example loads the table as of 10:00:00.123 and starts CDC at 10:00:00.124. If tracking was enabled after T0 or T0 is outside Time Travel retention, the baseline is invalid. Choose a later valid T0 and repeat the coordinated load.

Using Change Data Capture Extract

Adding an Extract and a Local Trail

Create a transaction-log Extract and configure a local EXTTRAIL. Snowflake Capture writes records to a local trail. Configure downstream distribution and Replicat for the target as required by your topology.

ADD EXTRACT sncdc, TRANLOG, BEGIN NOW

ADD EXTTRAIL ./dirdat/ss, EXTRACT sncdc

Example Parameter File

EXTRACT sncdc

JVMOPTIONS CLASSPATH ggjava/ggjava.jar:/path/to/snowflake/libs/*

SOURCEDB USERIDALIAS sf_capture

TABLE "CATALOG_A"."SALES"."ORDERS"; TABLE "CATALOG_B"."HR"."EMPLOYEES";

Note: Use the Snowflake connection alias configured in the Administration Service. Keep table names fully qualified, and quote identifiers when needed. Do not place passwords, private-key passphrases, or other secrets in the parameter file.

Startup Validation

Before capture starts, Extract validates each configured table for object resolution and access, CHANGE_TRACKING, and Time Travel retention. When Snowflake metadata is available, Extract also checks that the requested start time is not earlier than the recorded tracking-enable time. If any table is invalid, Extract stops and reports the affected objects. It does not start partial capture or silently skip a table. A CHANGES query is the final check for history availability, so Snowflake errors can also stop Extract at runtime. Correct any retention or tracking validation failure before restarting. Do not bypass validation by editing checkpoint files or continuing with a subset of tables. To capture a subset, remove the other tables from the Extract configuration and handle the target baseline.

Positioning the Extract

BEGIN TIMESTAMP

Use BEGIN TIMESTAMP when capture must start from a known point. The value must be within Time Travel retention for every configured table and must not precede the table’s CHANGE_TRACKING availability. Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) interprets the value as local time in the Extract host time zone and normalizes it internally to UTC. Keep the host time zone consistent across recovery hosts.

If no change exists at the exact timestamp, Extract starts at that position and continues with later windows. Do not use a future start time to schedule Extract. Start or schedule Extract separately, and choose a timestamp within valid retained history for every configured table.

To set a custom start time in the WebUI:

  1. In the Administration Service, select Extract, then select Alter.
  2. In Extract Options, select Custom Time for Begin.
  3. Enter the timestamp and apply the change.

When using adminclient, run:

ADD EXTRACT sncdc, TRANLOG, BEGIN 2026-10-05 10:00:00.124

BEGIN NOW

Use BEGIN NOW when only changes after the accepted current boundary are required. BEGIN NOW does not backfill earlier changes. If the target needs existing rows, perform a coordinated initial load first.

When using adminclient, run:

ADD EXTRACT sncdc, TRANLOG, BEGIN NOW

Data Availability and Trail Semantics

Snowflake Change-Data Availability

Snowflake CHANGES can return changes only for periods when CHANGE_TRACKING was active and the requested history remains within Time Travel retention. Plan retention to cover the longest expected outage and catch-up period. See Snowflake CHANGES clause and Snowflake Time Travel. If a requested interval has aged out, increasing retention does not restore the expired history. Re-establish a consistent target baseline with an initial load and a new CDC position within the history that remains available. For more information, see Snowflake Time Travel.

Row Images and Column Metadata
Trail Operation Image Captured
INSERT After-image of the row.
UPDATE Before- and after-images of the row.
DELETE Before-image of the row.

The trail includes the source data columns and identifies primary-key columns when the source table has a primary key. GoldenGate does not use unique constraints as capture keys. If a source table has no primary key, validate the target apply key and configure KEYCOLS or an equivalent Replicat mapping as needed.

Transaction Boundaries

Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) groups Snowflake changes into pseudo-transactions for trail processing. These are not the original Snowflake transactions. Trail transaction boundaries do not preserve a source transaction that changes multiple tables as one atomic unit. Rolled-back source work does not appear as committed changes in CHANGES, and the trail does not contain rollback or savepoint events.

Trail Timestamps, Lag, and Audit Interpretation

Snowflake CHANGES does not return a source commit timestamp for each row. GG for DAA assigns a trail timestamp from the end of the capture window, so replaying the same window produces the same timestamp. This timestamp is deterministic for the capture window; it is not the actual source commit time for each row.

Heartbeat rows captured from Snowflake receive the same window-derived timestamp. Account for the window size and polling delay when calculating lag from heartbeat rows.

Security and Authentication

In the Administration Service, configure and store the Snowflake connection under DB Connections. Refer to it by alias in the Extract configuration. For key-pair authentication, assign the matching public key to the Snowflake user, and protect the private key and its passphrase with operating-system permissions and approved secret-management controls.

Multiple Extract

Separate Extract groups can capture the same Snowflake table independently while Snowflake retains its change history. Each Extract needs its own checkpoint, trail, and initial-load boundary. Plan warehouse capacity and retention for the combined workload. Do not configure multiple Extract groups to apply the same source changes to the same target unless you have an explicit target-side duplicate and conflict strategy.

CDC Configuration Reference

The following table lists the customer-facing Snowflake CDC settings for this release. Use the option spelling shown; values outside a documented range cause Extract validation to fail.

Option Default and Accepted Values Effect
TRANLOGOPTIONS FETCHBATCHSIZE <value> Default 100000; range 100-1000000. Controls the number of changes fetched per query. Adjust this value only when query size or capture throughput requires it.
TRANLOGOPTIONS FETCHWINDOWSIZE <seconds> Default 30 seconds; range 10–1000 seconds. Controls the interval between capture boundaries. Smaller values can reduce latency; larger values can reduce query overhead.
JVMOPTIONS CLASSPATH <path> Classpath for the installed Snowflake Capture package and its required client libraries. Makes the Snowflake JDBC client and supporting libraries available to Extract. Use the classpath for the installed GoldenGate release.
JVMOPTIONS CLASSPATH <path> BOOTOPTIONS <jvm options> Optional; use only when the deployment requires JVM settings such as an HTTP proxy. Passes JVM settings to the Snowflake client. Omit this option when no additional settings are required.
SOURCEDB USERIDALIAS <alias> Required connection alias. Selects the Snowflake connection configured in the Administration Service under DB Connections.
TABLE <catalog>.<schema>.<table> At least one resolved capture table. Selects source base tables. Prefer three-part names; quote case-sensitive or special identifiers.

Keep the Extract checkpoint files when restarting or adding a table. Changing or deleting these files can make the saved recovery position unusable.

Troubleshooting

Condition or Message Meaning and Recommended Action
CHANGE_TRACKING is not enabled for <table>. Enable CHANGE_TRACKING with a role that has ALTER privilege, or use Add Trandata. Restart from a position later than the tracking-enable time; Extract cannot serve an earlier position.
Time Travel retention is too low; current and required values are reported. Increase retention to an allowed value before the required history expires, or re-instantiate from a new snapshot and start CDC within the retained interval. Increasing retention after history expires does not restore it.
Requested start predates CHANGE_TRACKING availability. Choose a later timestamp and perform a coordinated initial load at that timestamp. Do not resume with a gap.
Object does not exist, access denied, or table cannot be resolved. Check the full catalog.schema.table spelling and quoting, database and schema USAGE, table SELECT, and metadata privileges. Confirm that the table has not been dropped or renamed.
CHANGES reports historical data is not available during runtime. The requested window might be outside retention, or tracking might have been disabled. Extract stops to avoid silent data loss. Re-establish the baseline; re-enabling tracking alone does not fill the gap.
Authentication or network connection failure. Validate the saved connection in the Administration Service. Check user or key status, role and warehouse access, Snowflake network policy, certificate trust, and Extract host connectivity.
High capture latency or growing lag. Check warehouse queueing, size, auto-resume behavior, concurrent workload, Snowflake query history, FETCHWINDOWSIZE, and FETCHBATCHSIZE. Confirm that retention still covers the oldest unprocessed position.
Restart Recovery and Progress

When Extract restarts, Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) supplies Snowflake Capture with the last completed trail position. Capture resumes from the saved position. Keep the Extract checkpoint files intact. If the files are lost and the position cannot be restored, you might need a new initial load. Use Extract STATS and the Extract report to check health, validation errors, and capture progress. During catch-up, compare the saved position with available Snowflake history to confirm that the remaining retention period is long enough for Extract to catch up.

Snowflake Capture Client Dependencies

For Snowflake database connections and CDC Extract, add the following client libraries to the classpath:

  1. Snowflake JDBC driver: net.snowflake » snowflake-jdbc, version 4.1.0

    If you use an encrypted private key for key-pair authentication, configure the following additional dependencies:

  2. Bouncy Castle PKIX library: org.bouncycastle » bcpkix-jdk18on, version 1.86

  3. Bouncy Castle provider library: org.bouncycastle » bcprov-jdk18on, version 1.86

References

Snowflake CHANGES clause

Snowflake Time Travel

Snowflake ALTER TABLE