About Openflow Connector for SQL Server (CDC)¶

This topic describes the basic concepts, workflow, and limitations of the Openflow Connector for SQL Server (CDC).

About the Openflow Connector for SQL Server (CDC)¶

The Openflow Connector for SQL Server (CDC) connects a SQL Server database instance to Snowflake and replicates data from selected tables in near real-time or on a schedule. The connector uses SQL Server Change Data Capture (CDC) to detect and apply changes to replicated tables. Change data is recorded in journal tables alongside the current state of the replicated tables.

Use cases¶

Use this connector if you’re looking to do the following:

• Synchronize SQL Server data with Snowflake for comprehensive, centralized reporting.
• Capture every individual row-level change from the source database, including intermediate states between polling intervals.

Supported SQL Server versions¶

The following SQL Server database versions and platforms are supported:

• Microsoft SQL Server 2025
• Microsoft SQL Server 2022
• Microsoft SQL Server 2019
• Microsoft SQL Server 2017
• Microsoft SQL Server 2016 (SP1 or later, Enterprise / Standard / Developer edition)
• Azure SQL Database
• Azure SQL Managed Instance
• AWS RDS for SQL Server
• Google Cloud SQL for SQL Server

Openflow requirements¶

• The runtime size must be at least Medium. For guidance on choosing a size and resizing the runtime later, see Runtime sizing.
• The connector doesn’t support multi-node Openflow runtimes. Configure the runtime for this connector with Min nodes and Max nodes set to 1.

Limitations¶

• You can’t run multiple connectors of the same type in a single runtime instance.
• The connector supports only username and password authentication with SQL Server.
• Each replicated table must have a primary key, a qualifying unique constraint, a qualifying unique index, or a user-declared logical key. For more information, see How the connector chooses a replication key.
• The connector doesn’t update existing records in the Snowflake database when a new NOT NULL column with a default value is added to one of the source databases.
• The connector doesn’t update existing records in the Snowflake database when a new column is added to the included list in the Column Filter JSON.
• The connector supports common source table schema changes during replication, such as adding and dropping columns. See Schema changes for the full list and a few unsupported change types.
• The connector doesn’t detect at runtime when you drop or modify the primary key, unique constraint, or unique index that it uses as the replication key, or when you alter or drop a logical-key column after the CDC capture instance exists. After any such change, restart replication for the affected table: see Restart table replication.
• When a new column is added to a source table and an update changes only that newly added column, SQL Server Change Data Capture doesn’t record those updates in the change table, so the connector can’t replicate them as individual change events. This is a SQL Server CDC limitation: a change is captured only when an update modifies at least one column that the table’s capture instance already tracks. The connector still reflects the latest value of the new column in the destination table after the connector switches to the new capture instance.
• The connector doesn’t support the truncate table operation.

Workflow¶

The following workflow outlines the steps to set up and run the Openflow Connector for SQL Server (CDC):

1. A SQL Server database administrator performs the following tasks:

   1. Enables Change Data Capture on each database using sys.sp_cdc_enable_db, then creates a capture instance for each table to be replicated using sys.sp_cdc_enable_table.
   2. Creates credentials for the connector.
   3. Deploys the Openflow CDC wrapper procedures so the connector can manage capture instances and apply source schema changes autonomously.
   4. (Optional) Provides the SSL certificate to connect to the SQL Server instance over SSL.

2. A Snowflake account administrator performs the following tasks:

   1. Creates a service user for the connector, a destination database to store replicated data, and a warehouse for the connector.
   2. Installs the connector.
   3. Specifies the required parameters for the connector flow definition.
   4. Runs the flow.

The connector does the following when run in Openflow:

1. Creates the schemas and destination tables matching the source tables configured for replication.

2. Begins replication according to the table replication lifecycle.

   For more information, see How tables are replicated.

How the connector works¶

The following sections describe how the connector works in various scenarios, including replication, snapshots of partitioned tables, changes in schema, and data retention.

Change Data Capture behavior¶

The connector uses SQL Server Change Data Capture (CDC) to detect changes in the source tables. CDC captures row-level insert, update, and delete activity from the SQL Server transaction log into dedicated change tables.

Because CDC preserves every individual change, the connector is suitable for both data synchronization use cases and audit or history use cases where every change to a row must be captured. For a comparison with the Openflow Connector for SQL Server, which uses Change Tracking instead, see Comparison of Openflow connectors for SQL Server.

Data replication¶

The connector supports replicating tables from multiple SQL Server databases in a single SQL Server instance. The connector creates replicated tables from different databases in separate schemas in the destination Snowflake database.

Reference replicated tables by combining the source database name, the source schema name, and the table name in the following format:

<database_name>.<schema_name>.<table_name>

For each schema in each source database being replicated, the connector creates a separate schema in the destination Snowflake database. The name of the destination schema is a combination of the source database name and the source schema name, separated by an underscore character (_) as shown in the following example:

<source_database_name>_<source_schema_name>

The connector creates tables in the destination schema with the same name as the source table name as shown in the following example:

<destination_database>.<destination_schema_name>.<source_table_name>

How tables are replicated¶

The connector replicates tables in the following stages:

1. Schema introspection: The connector discovers the columns in the source table, including the column names and types, then validates them against Snowflake’s and the connector’s limitations. Validation failures cause this stage to fail, and the cycle completes. After successful completion of this stage, the connector creates an empty destination table.
2. Snapshot load: The connector copies all data available in the source table into the destination table. If this stage fails, the connector stops replicating data. After successful completion, the data from the source table is available in the destination table. For how large partitioned tables are handled, see Snapshot of partitioned tables.
3. Incremental load: The connector reads new entries from the CDC change tables and applies those changes to the destination table. This process continues until the table is removed from replication. Failure at this stage permanently stops replication of the source table, until the issue is resolved.

For information on bypassing snapshot load and using the incremental load process, see Incremental replication.

Snapshot of partitioned tables¶

During snapshot load, the connector pages through each source table in primary key order. For a large table that is physically partitioned on the source, this ordering can force SQL Server to sort the whole table on every batch when no index delivers rows in the required order, which makes the snapshot extremely slow or prevents it from completing.

To avoid this, the connector snapshots a physically partitioned table one partition at a time. Reading a single partition lets SQL Server stream rows in index order without a sort, so each batch finishes quickly. This behavior is automatic: the connector detects partitioned tables and requires no configuration change. Non-partitioned tables are unaffected and continue to be read as a whole. This per-partition optimization applies only to the snapshot phase. Incremental load reads from the CDC change tables and isn’t affected.

How the connector chooses a replication key¶

The connector uses one column or set of columns from each source table as the replication key. The replication key uniquely identifies a row, drives the MERGE operation that applies CDC changes to the destination, and orders rows during the snapshot load.

For each table, the connector resolves the replication key in this order:

1. User-declared logical key. If the connector’s Table Key Configuration JSON parameter lists the table, the connector uses those columns as the replication key, overriding any primary key, unique constraint, or unique index on the table. For more information, see Specify a logical key for a table.
2. Primary key. The columns of the table’s enabled primary key constraint.
3. Unique constraint or unique index. If the table has no primary key, the connector looks for a qualifying unique constraint or unique index, as described in Qualifying unique constraints and unique indexes.
4. None. If no qualifying key is found, the connector can’t replicate the table. To resolve this, either add a primary key to the table, modify an existing constraint or index so it qualifies (see the following criteria), or declare a logical key on columns that uniquely identify rows.

Qualifying unique constraints and unique indexes¶

The connector evaluates a unique constraint or unique index as a candidate replication key only when:

• It’s unique and isn’t the primary key.
• It’s enabled (not disabled).
• It’s a standard B-tree index. Filtered indexes (those with a WHERE clause) are excluded.
• All columns covered by the constraint or index are NOT NULL.

Tiebreakers¶

When more than one candidate qualifies, the connector picks one deterministically using the following preferences, in order:

1. Among all candidates, a unique constraint is preferred over a unique index.
2. Among candidates of the same type, the candidate with the fewest columns is preferred.
3. Among candidates with the same column count, the candidate with the most numeric columns is preferred. The connector counts the following SQL Server types as numeric: INT, BIGINT, SMALLINT, TINYINT, DECIMAL, NUMERIC, MONEY, SMALLMONEY, FLOAT, REAL.
4. If a tie remains, the candidate with the lowest constraint or index name in alphabetical order is selected.

If you want a specific column set used regardless of the tiebreaker outcome, declare it as a logical key. For more information, see Specify a logical key for a table.

Replication key example¶

The following table has no primary key but has a unique constraint on a NOT NULL column. The constraint qualifies, and the connector replicates the table using the constraint as the replication key:

CREATE TABLE customers (
    email      NVARCHAR(255) NOT NULL,
    name       NVARCHAR(100),
    created_at DATETIME2 DEFAULT SYSUTCDATETIME(),
    CONSTRAINT uq_customers_email UNIQUE (email)
);

How the schema transition works¶

A SQL Server capture instance records changes against a fixed set of columns that’s frozen when the capture instance is created. When the schema of a source table changes, the connector can’t keep using the same capture instance, so it transitions to a new capture instance that reflects the updated schema. This transition happens in the following stages:

1. Schema change detection. The connector detects DDL changes through a polling mechanism: it periodically queries SQL Server’s cdc.ddl_history table for the tables it replicates. The polling interval is configurable and defaults to 30 seconds, so a schema change is picked up shortly after it occurs rather than in the same cycle.

2. New capture instance creation. When the connector detects a schema change, it creates a new capture instance that reflects the post-change column set. The new capture instance starts recording changes from the point at which it’s created.

3. Draining the old capture instance. The connector keeps reading from the old capture instance up to the point where the schema changed, so that no change committed under the old schema is lost. These changes are emitted using the old column set. All changes committed under the old schema are delivered to the destination before any changes recorded under the new schema, so the destination always receives changes in schema-consistent commit order.

4. Mini-snapshot. A gap exists between the point where the schema changed and the point where the new capture instance started recording. To fill this gap, the connector replays the changes in that gap from the old capture instance, joining them against the live source table so that newly added columns carry their current values. This mini-snapshot brings the destination table up to date with the new schema without a full re-snapshot of the whole table.

5. Switching to the new capture instance. After the mini-snapshot completes, the connector retires (drops) the old capture instance and resumes normal replication from the new one.

Replaying already-sent changes¶

When the connector detects a schema change, it might need to rewind and replay change records that it already sent. For example, records sent before a new column was detected don’t include a value for that column, so the connector replays the affected rows to populate the new column. Because the connector applies changes idempotently by primary key, replaying these records doesn’t create duplicate rows in the destination table.

A schema change during a schema transition¶

A second schema change can occur while the connector is still transitioning to a new capture instance because of an earlier change. The connector detects the follow-up change, provisions a newer capture instance that reflects the latest schema, and continues the transition against that newer instance, rewinding as needed so the affected changes are replayed under the most recent schema. No data is lost, and the transition completes against the most recent schema.

Oversized values¶

By default, the connector replicates individual values up to 16 MB. When the connector encounters a larger value, it marks the associated table as permanently failed and stops replicating it. To change how the connector handles oversized values (for example, to replace them with NULL instead), modify the Oversized Value Strategy destination parameter.

If your Snowflake account has the ENABLE_OPENFLOW_CDC_BASED_SQLSERVER_SSV2 parameter set to true, the per-value limit can be raised from 16 MB to 128 MB.

For details and instructions on enabling the 128 MB per-value limit, see Increase the oversized value limit.

For connection settings, see Always On Availability Groups.

Source database locking behavior¶

During the snapshot phase, the connector reads directly from the source tables to perform the initial full copy. Under SQL Server’s default READ COMMITTED isolation level, these read operations acquire shared locks on the source tables, which can lead to deadlocks if other database clients hold conflicting locks at the same time.

During incremental replication, the connector reads changes from dedicated CDC change tables, not from the source tables, so it doesn’t take shared locks on the source tables and isn’t subject to the deadlocks described here.

To avoid deadlocks during the snapshot phase without affecting the isolation level that other applications use, configure the connector to read under SNAPSHOT isolation. SNAPSHOT isolation reads from row versions instead of acquiring shared locks, so the connector’s snapshot queries no longer contend with concurrent writes on the source tables.

Because this approach makes SNAPSHOT isolation available only to the connector’s own sessions, it doesn’t change the default READ COMMITTED isolation level that other applications rely on. Avoid using Read Committed Snapshot Isolation (RCSI) for this purpose, because RCSI redefines the default isolation level for every connection to the database.

For the steps to enable SNAPSHOT isolation for the connector, see Read the source under SNAPSHOT isolation.

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = FALSE;

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = TRUE;

SELECT EMAIL__SNOWFLAKE_DELETED FROM my_table;

SELECT
    COALESCE(B, A__SNOWFLAKE_DELETED) AS A_RENAMED_TO_B
FROM my_table;

SELECT
    CASE
        WHEN B IS NOT NULL THEN B
        ELSE A__SNOWFLAKE_DELETED
    END AS A_RENAMED_TO_B
FROM my_table;

CREATE VIEW my_table_unified AS
SELECT
    *,
    COALESCE(B, A__SNOWFLAKE_DELETED) AS A_RENAMED_TO_B
FROM my_table;

Next steps¶

Review Openflow connectors for SQL Server: Data mapping to understand how the connector maps data types to Snowflake data types.

Review Set up the Openflow Connector for SQL Server (CDC) to set up the connector.