Snapshot Tasks

Snapshot tasks are a crucial part of Gluesync’s data synchronization, enabling you to migrate or initialize large datasets efficiently. This chapter explains how to maximize snapshot performance by choosing between INSERT and UPSERT operations, tuning snapshot writing concurrency, and using logical partitioning on large source tables.

Overview

During a snapshot task, Gluesync copies the entire dataset from the source to the target. The way records are written—either as INSERTs or UPSERTs—directly impacts performance and data integrity.

INSERT vs UPSERT: Performance and use cases

UI options for INSERT and UPSERT during snapshot tasks

TRUNCATE before snapshot

Gluesync 2.1 introduces the ability to perform a TRUNCATE operation on the target table before executing a snapshot task. This is particularly useful when you want to ensure a clean slate before loading fresh data.

Key features:

Clean Data Loading: Removes all existing data from the target table before the snapshot begins
Per-snapshot control: Choose whether to enable TRUNCATE on a per-snapshot basis
Agent-level safety: Can be disabled at the target agent level for security

How to use

Navigate to the entity’s play tab
Select "Run Snapshot"
Select your preferred write mode (INSERT/UPSERT): for INSERT mode, TRUNCATE before snapshot is enabled by default;
Click "Start" to start the entity

Agent-level configuration

For security reasons, TRUNCATE before snapshot can be disabled at the target agent level:

Go to the target agent’s configuration
Navigate to the "Advanced Settings" tab
Locate the "Disable TRUNCATE on Snapshot" parameter
Set to true to disable TRUNCATE operations (default is false)

When enabled, this setting prevents any TRUNCATE operations during snapshots for all entities using this target agent, regardless of individual snapshot settings.

Mode	Description & Best Practice
INSERT	Faster, recommended when the target table is empty. Each record is written as a new row. No existing data is checked or merged. Use this for initial loads or when you are certain there is no overlap with existing data.
UPSERT	Slower, but safer when the target table may already contain data. Each incoming record is either inserted as new or merged with an existing row if a match is found. Use this when you need to preserve and update existing records.

Mode

Description & Best Practice

INSERT

Faster, recommended when the target table is empty. Each record is written as a new row. No existing data is checked or merged. Use this for initial loads or when you are certain there is no overlap with existing data.

UPSERT

Slower, but safer when the target table may already contain data. Each incoming record is either inserted as new or merged with an existing row if a match is found. Use this when you need to preserve and update existing records.

For best performance, use INSERT when possible. Use UPSERT only if you expect existing data in the target.

Enhancing write performance with snapshot writing concurrency

Starting from Gluesync v2.0.10, you can dramatically improve snapshot performance by configuring the Snapshot writing concurrency parameter. This controls the number of parallel threads used to write data during a snapshot task.

Default: 1 (no parallelism)
Maximum: 8 (high parallelism)

By increasing concurrency, you can achieve up to 4x faster snapshot completion times, as demonstrated in recent benchmarks and customer feedback.

Increasing concurrency can put more load on your target database. Monitor system resources and adjust the setting according to your environment’s capacity.

How to configure

You can set the Snapshot writing concurrency in the entity configuration wizard:

For large datasets or high-performance targets, set a higher value (up to 8).
For limited resources or when targeting smaller databases, use a lower value.

Scheduling snapshot tasks

Snapshot tasks can be executed on-demand or scheduled to run automatically at specific times using the Chronos Scheduler. Scheduling snapshots is particularly useful for:

Regular data refreshes from source to target
Off-hours data migrations to minimize impact on production systems
Periodic full data synchronization to ensure data consistency

To set up scheduled snapshots, navigate to the Chronos Scheduler module, create a new schedule, and select "Run Snapshot" as the action type. For detailed instructions, see the Chronos Scheduler documentation.

Accelerate initial loads with bulk load

Large, one-off snapshots can also take advantage of the Bulk load feature. Instead of replaying every INSERT individually, Core Hub can stage the snapshot output in the target’s bulk-ingestion format (CSV + COPY, Parquet + GCS load job, SqlBulkCopy, etc.) and apply the dataset in a single batch. This approach is ideal when:

You are populating empty targets and want minimum load time before CDC kicks in.
Targets expose high-throughput primitives such as COPY INTO (Snowflake) or WRITE_TRUNCATE load jobs (BigQuery).
You need deterministic windows to coordinate snapshots with downstream jobs controlled by Chronos.

Bulk load and snapshot tasks are complementary: use snapshots to capture the full dataset, then let Bulk load handle the fastest possible ingest into the destination.

Enabling Bulk Load on existing entities

You can easily enable bulk load capabilities on an already configured entity without the need to delete or recreate it.

To update your configuration, navigate to the Entities section and click the edit icon for your selected entity. From the Object Browser, open the Settings dialog and scroll down to the Optional settings and fine tuning area. Within the Bulk load settings panel, you can enable the specific bulk operations you require:

Bulk for Snapshot: Enables high-speed bulk ingestion specifically during the initial snapshot phase.
Bulk for CDC: Applies bulk operations to accelerate data transfer during continuous Change Data Capture.

Once you have toggled the desired flags, simply save your changes to apply the new configuration immediately.

Resuming an interrupted snapshot

If a snapshot task is paused, interrupted, or fails before completion, Gluesync can automatically resume the data load from the exact point it left off, rather than starting over.

To resume a snapshot, simply click the Start button on the entity again.

The snapshot will automatically resume from its last saved state unless you enable the Reset any existing checkpoint option in the configuration panel. If you check this flag before starting the entity, the previous progress will be completely discarded, and the snapshot will restart entirely from scratch.

Boosting read performance using logical partitioning for large tables

For very large source tables, you can improve snapshot performance by enabling logical partitioning on the read side.

Logical partitioning splits the snapshot read into multiple logical ranges over a single source column and processes those ranges in parallel. This helps reduce total snapshot duration and makes better use of available resources when snapshotting large fact or history tables.

To learn how to configure and tune this feature, see Logical partitioning for snapshot tasks.

Apply SQL WHERE clauses at source database level

Starting from Gluesync v2.1, you can apply SQL WHERE clauses at source database level to filter out data before it is read by Core Hub. This can be particularly useful when you want to limit the amount of data read from the source database, which can improve performance and reduce the load on the source database.

To configure and tune this feature, access the entity settings and apply a WHERE clause to the source table (the WHERE keyword can be omitted).

Real-world scenarios and customer insights

INSERT with high concurrency is ideal for initial migrations to an empty table—customers report significantly reduced snapshot times.
UPSERT is preferred for ongoing syncs where the target may already have data, especially when data integrity is critical.
Customers have found that adjusting concurrency helps balance speed and system stability, especially on cloud-managed databases.

SQL Pre and Post Snapshot commands

You can access this functionality by entering the settings of the single entity. It is particularly valuable for optimizing high-volume data loads or managing specific integrity requirements on the target system.

Common use cases

Configuring pre and post snapshot commands is recommended for the following scenarios:

Index management: To significantly improve write performance during a large snapshot, you can configure a Pre-Snapshot command to drop or disable non-primary indexes on the target table, and a Post-Snapshot command to rebuild or re-enable them once the load is finished.
Constraint management: Temporarily disable referential integrity constraints (Foreign Keys) or check constraints during the load process and re-enable them afterwards.
Custom auditing: Insert records into custom logging tables to track the precise start and completion times of snapshot operations.
Environment preparation: Execute specific stored procedures to prepare the target database for incoming data.

Configuration

To configure these commands:

Click the edit button of the specific entity in the pipeline and click on settings.
Scroll down to the Entity settings section.
You will find two specific fields:
- SQL Pre-Snapshot start command: Enter the SQL statement to execute before the snapshot process initiates.
- SQL Post-Snapshot stop command: Enter the SQL statement to execute after the snapshot process concludes.

Ensure that the database user configured for the Target agent has the necessary privileges to execute these commands (e.g., ALTER TABLE or DROP INDEX permissions if you intend to modify table structures).

If the Pre-Snapshot command fails (returns a SQL error), the entire snapshot task will be aborted to prevent data inconsistencies. Verify that your SQL syntax is compatible with the target database dialect.

Large Data Snapshot Playbook (Best Practices)

When migrating massive tables (e.g., 10M+ rows), performance depends on balancing the read load on the source and the write load on the target. Follow this decision matrix:

Scenario A: Empty Target Table (Initial Load)
- Mode: Use INSERT + TRUNCATE before snapshot.
- Source (Read): Enable Logical Partitioning (e.g. 3-5 partitions) to read in parallel without killing the source CPU.
- Target (Write): Set Snapshot writing concurrency to a higher value (e.g. 4 to 8).
Scenario B: Target Table Already Contains Data
- Mode: Use UPSERT.
- Caution: UPSERT is significantly slower because it must check for existing records[cite: 1608]. Keep concurrency moderate (e.g., 2-4) to avoid target database locks.
Scenario C: Source is Slower than Target (e.g., Legacy Mainframe to Cloud DB)
- Action: Do not over-provision target concurrency. If the source cannot serve data fast enough, a high write concurrency will not help and might cause timeouts. Use moderate Logical Partitioning on the source (e.g. 2-3) and match it with a low/moderate write concurrency on the target (e.g. 2-3).

Summary

To maximize snapshot task performance:

Prefer INSERT for empty targets, UPSERT for merging/updating.
Use the Snapshot writing concurrency parameter (v2.0.10+) to leverage multi-threaded writes.
Use source table partitioning and, on large tables, logical partitioning to boost read performance.
Monitor your system and adjust concurrency and partitioning settings for optimal throughput and reliability.