Bulk load

Bulk load turns change streams into high-throughput batches: the Core Hub groups every insert, update, and delete that occurred during a mirroring cycle, stages them with database-native bulk APIs, then replays them on target tables with minimal round-trips.

Overview

Bulk load complements real-time CDC by letting you defer the “apply” phase to a single, optimized batch per cycle. The Core Hub prepares a staging table that mirrors the target schema, automatically enriches missing column values, and applies the combined dataset with database-specific statements (MERGE, DELETE/INSERT, or bulk copy).

Why use bulk load

Performance boost: Bulk APIs (COPY INTO, SqlBulkCopy, Oracle Array Bindings, Parquet loads on BigQuery, etc.) drastically reduce the number of statements executed against the target.
Predictable windows: Mirroring cycles run in clear phases (collect → merge → apply), which simplifies scheduling alongside snapshots or Chronos jobs.
Data quality: Missing column values can be backfilled just before the merge, ensuring target rows remain complete even if source logs omit fields.
Cross-platform consistency: The same flow works for IBM i, Oracle, PostgreSQL, Snowflake, SQL Server, MySQL, and Google BigQuery, with only the load primitives changing.

Core workflow

Phase Description

Phase	Description
Collect	Agents capture CDC events during the mirroring cycle and send them to Core Hub buffers.
Combine	Core Hub runs a merge algorithm that collapses multiple events on the same primary key (e.g., insert+delete ⇒ skip, consecutive updates ⇒ single update).
Stage	A staging table receives the batched dataset through the fastest mechanism for the destination (Csv + COPY INTO on Snowflake, SqlBulkCopy on SQL Server, Parquet + GCS load jobs on BigQuery, etc.).
Enrich	Optional update joins or MERGE statements backfill columns flagged with `_UP = 'Y'` when the source log did not include a value.
Apply	Deletes run first (based on `ID_OLD`), then inserts (and remaining updates, often executed as delete+insert for speed). Staging tables are truncated or dropped at the end of the cycle.

Collect

Agents capture CDC events during the mirroring cycle and send them to Core Hub buffers.

Combine

Core Hub runs a merge algorithm that collapses multiple events on the same primary key (e.g., insert+delete ⇒ skip, consecutive updates ⇒ single update).

Stage

A staging table receives the batched dataset through the fastest mechanism for the destination (Csv + COPY INTO on Snowflake, SqlBulkCopy on SQL Server, Parquet + GCS load jobs on BigQuery, etc.).

Enrich

Optional update joins or MERGE statements backfill columns flagged with _UP = 'Y' when the source log did not include a value.

Apply

Deletes run first (based on ID_OLD), then inserts (and remaining updates, often executed as delete+insert for speed). Staging tables are truncated or dropped at the end of the cycle.

Staging table blueprint

Every target receives a staging table that contains:

All target columns with the same names and types.
<column>_OLD fields for primary keys (needed when a key changes).
<column>_UP CHAR(1) flags for non-key columns to mark whether the value must be fetched from the target before applying.
An OPERATION column (values I, U, D).

Oracle uses a global temporary table (ON COMMIT PRESERVE ROWS).
MySQL creates a fresh temporary table at every cycle.
BigQuery and Snowflake reuse persistent staging tables and rely on WRITE_TRUNCATE or explicit TRUNCATE after each run.

Data ingestion patterns

IBM i & PostgreSQL: Export batched rows to unique CSV files, then ingest via fast COPY statements inside the staging schema.
Oracle: Prefer Array Bindings for network efficiency; OracleBulkCopy is also available when binding is not possible.
SQL Server: Use SqlBulkCopy (or equivalent) to stream DataTables directly into the staging table.
MySQL: Temporary staging tables are populated through multi-row INSERT statements generated from CSV payloads.
Snowflake: Upload CSV payloads via PUT and load them with COPY INTO … FILE_FORMAT=(NULL_IF=('NULL')).
Google BigQuery: Produce Parquet files, upload them to GCS, and trigger a load job with WRITE_TRUNCATE for atomic refreshes.

Merge and apply operations

Update join / MERGE: Before replaying, Core Hub can sync staging rows with the live target to fill any _UP = 'Y' columns (recommended when the source log omits unchanged fields).
Deletion phase: DELETE … WHERE ID IN (SELECT ID_OLD FROM staging) or database-specific MERGE … WHEN MATCHED THEN DELETE.
Insertion phase: INSERT INTO target (…) SELECT … FROM staging WHERE OPERATION IN ('I','U'), often covering updates as delete+insert for better write throughput.
Cleanup: Staging tables are truncated or dropped according to the database capability, preparing the environment for the next mirroring cycle.

Supported targets

Bulk load is validated on:

PostgreSQL;
Snowflake;
Microsoft SQL Server;
MySQL;
MariaDB;
Google BigQuery.

Additional targets can reuse the same staging pattern as long as they expose a bulk-ingest primitive.

Prerequisites

Target credentials must allow DDL on staging schemas plus DML on destination tables (see Target table creation).
Agents must be configured with snapshots or CDC tasks that populate bulk cycles (Snapshot tasks).
Sufficient disk space or object storage must be available for intermediary CSV/Parquet artifacts.

Best practices

Generate unique file names (table + replication ID + timestamp) to avoid collisions when uploading staging files.
Skip the update-join query when logs already contain full column values to save time.
Run delete statements before inserts to prevent duplicate key violations.
Monitor batch duration from the Core Hub dashboards to tune mirroring cycles and Chronos schedules.
Automate cleanup of temporary artefacts (local CSV/Parquet files, GCS buckets, Snowflake stage files).

Troubleshooting

Symptom Action

Symptom	Action
Staging table retains rows between cycles	Verify the correct `TRUNCATE TABLE` (Snowflake) or `WRITE_TRUNCATE` load job (BigQuery) runs after each apply phase.
Updates missing column values	Confirm `_UP` flags are set correctly and that the MERGE/UPDATE join executes before delete/insert phases.
Bulk copy throttling or timeouts	Split very large CSV/Parquet files into multiple smaller batches or increase agent polling intervals to smooth the load.
Violation of PRIMARY KEY constraint in object '#dbo_…_tmp'	This error occurs on the target database (like SQL Server) when the source sends multiple rows with the exact same key in a single batch. While this could happen we recommend verify whether your records are actually unique or if a rollback has suddenly happened at the source DB level. Gluesync won’t block with this error, it will just log it and attempt to solve it automatically by doing an UPSERT operation, but you should investigate the root cause. Note that if this error involves many records it may drag down performance of your replication.

Staging table retains rows between cycles

Verify the correct TRUNCATE TABLE (Snowflake) or WRITE_TRUNCATE load job (BigQuery) runs after each apply phase.

Updates missing column values

Confirm _UP flags are set correctly and that the MERGE/UPDATE join executes before delete/insert phases.

Bulk copy throttling or timeouts

Split very large CSV/Parquet files into multiple smaller batches or increase agent polling intervals to smooth the load.

Violation of PRIMARY KEY constraint in object '#dbo_…_tmp'

This error occurs on the target database (like SQL Server) when the source sends multiple rows with the exact same key in a single batch. While this could happen we recommend verify whether your records are actually unique or if a rollback has suddenly happened at the source DB level. Gluesync won’t block with this error, it will just log it and attempt to solve it automatically by doing an UPSERT operation, but you should investigate the root cause. Note that if this error involves many records it may drag down performance of your replication.

Foreign keys and bulk load

Bulk load phases temporarily insert rows in staging tables that may reference parent records not yet applied in the target. Databases that implement deferrable constraints (or let you pause constraint checks per session) can absorb this write pattern with no extra steps. When a target cannot defer foreign-key validation, the merge phase would fail as soon as the staging rows touch a constrained table.

MySQL and MariaDB limitations

MySQL and MariaDB do not currently expose deferrable constraints or a safe, transactional switch to postpone foreign-key evaluation. Although SET foreign_key_checks=0 exists, it operates cluster-wide, is not honored by all storage engines, and does not guarantee that secondary sessions (or background jobs) will continue to respect referential integrity during the bulk cycle. Because of these constraints, Gluesync does not enable bulk load for MySQL or MariaDB targets when foreign keys are present.

For workloads that require strict referential integrity on MySQL/MariaDB, you can:

Continue using the standard CDC apply path (no staging) so that operations execute in the same order they were captured.
Restructure the target schema to move foreign keys to downstream analytical layers (e.g., replicated views) while keeping the landing tables constraint-free.
Stage data into an intermediate schema without constraints, then apply custom stored procedures that enforce the needed checks in controlled order.