Articles in this section

See more

Edit CDC Offset Files

  • Updated
Follow

Overview

The CDC offset file records the position from which a Debezium-based CDC flow resumes reading the source database's change feed — for example, the MySQL binlog file and position, the PostgreSQL or SQL Server log sequence number, the Oracle SCN, or the MongoDB oplog timestamp. The connector writes to this file as it streams events, and reads from it on every restart.

In normal operation you do not need to look at offset files. Recovery is automatic: when a flow restarts after a stop, a JVM crash, or a node failover, the connector picks up from the last flushed position and replays from there. See Recovery and Resumability for how automatic recovery works.

A small number of edge cases require manual intervention on the offset file — most often because the recorded position refers to data the connector can no longer read, or because a source-side recovery has moved the consumable window past the connector's last known position. In the past these edits required server-side filesystem access. Etlworks now provides an in-UI editor that inspects the offset, edits it, restores it from a backup, or fully resets CDC state — each with the same safety checks and confirmations. This article documents that editor.

Three ways to reach the editor

The same CDC offset editor is reachable from three places:

  • From the CDC connection editor — click the Offset File button in the bottom toolbar. Loads the offset (and, if applicable, history) file associated with the connection. See Edit offsets from a CDC connection.
  • From Explorer — browse to a CDC Offset and History connection, right-click an offset file, choose Edit CDC Offset. Loads the specific file you clicked. See Edit offsets from Explorer.
  • From a saved CDC flow — open the flow, click the Offset File button in the flow footer toolbar. Loads the active offset and history files the flow actually uses at runtime, resolving any tokenized paths in the connection settings, and lists both standard editor backups and automatic backups from the flow's Offset Backup connection when one is configured. See Edit offsets from a CDC flow.

All three entry points open the same modal, go through the same backend, and honor the same safety checks. The flow entry point is the most capable of the three because it resolves the runtime file paths and surfaces both kinds of backups.

When to use this feature

Editing the offset file is an advanced troubleshooting operation. Use it when one of the following applies:

  • The recorded position points at data the source can no longer provide. For example, MySQL has rotated past the binlog file referenced in the offset, PostgreSQL has reclaimed the WAL position behind the connector's replication slot, or SQL Server has truncated the change-tracking range. The flow fails on startup with a "position not found" or equivalent error.
  • The recorded position contains a record the connector cannot process. The flow fails repeatedly at the same position with a deserialization error, a schema mismatch, or an unrecoverable parse failure. After confirming that the offending record can be safely skipped, you can move the position past it.
  • A source-side recovery, restore, or migration has changed the log position you should resume from. For example, the source database was restored from a backup, point-in-time recovery moved the consumable window, or the database was failed over to a replica with a different log position.
  • You need to roll back a recent bad edit or a bad state. Use Restore Standard Backup to revert to the state that existed before your most recent Save Offset or Reset CDC State. Or, when working from a flow that has an Offset Backup connection with automatic backup configured, use Restore Backup Connection to revert to a scheduled automatic backup taken at a known-good point in time. See Restore from the Standard backup folder and Restore from the Backup Connection.
  • You want to fully reset CDC state. Use Reset CDC State to delete the current offset, history, and known snapshot sidecar files — a standard backup zip is created first, so the state is not lost. See Reset CDC State.
  • Support is guiding you through a recovery. Etlworks support sometimes asks customers to confirm or edit the current offset content. The editor lets you do that without granting filesystem access to the integrator host.
  • You need to inspect the current resume position without changing it. Open the editor and use Close to leave the file unchanged.

If the symptom is “the flow keeps re-snapshotting on every restart” or “the flow shows Running but no events are produced,” review Recovery and Resumability first — the cause is often elsewhere (deleted offset file, unsafe offset path, read-only volume) and editing the offset will not help.

Warnings and prerequisites

Read this section before opening the editor.

  • Stop the CDC flow first. Editing the offset while the connector is running can leave the file in an inconsistent state, and the connector may overwrite your edit on its next flush. As of Etlworks 9.6.11, the editor also enforces this — when it detects a running flow, Save Offset, Reset CDC State, and the restore buttons are disabled and the backend rejects mutation requests. Read-only inspection remains available. See Editor behavior while the flow is running.
  • An incorrect edit can cause skipped events or duplicates. If the new position is ahead of the actual last-processed event, events between the old and new positions will not be delivered to the destination. If the new position is behind, events between the new and old positions will be replayed. Replayed events are absorbed by destinations that load with MERGE or upsert; skipped events are not recoverable without re-snapshotting.
  • A restored older backup changes where the CDC flow resumes. Restoring can replay events, skip events, trigger a re-snapshot, or fail on startup depending on connector settings and source log availability. Restore only after you have confirmed that the source database can still serve records from the backup's recorded position.
  • An invalid position can fail the flow. A position the source database cannot resolve will fail the flow on the next start with the same kind of error you may already be debugging. The editor validates that the JSON is well-formed and that a backup file can be read as a Debezium/Kafka Connect offset map. It does not validate that the position or the backup's recorded position is still available in MySQL binlogs, PostgreSQL WAL, SQL Server CDC ranges, Oracle SCN history, or the MongoDB oplog. That check must be done against the source.
  • Understand the source's position format before editing. The position is a JSON object whose keys depend on the source database — for example, MySQL uses file and pos, PostgreSQL uses lsn, SQL Server uses commit_lsn and change_lsn, Oracle uses scn, MongoDB uses sec and ord. The editor preserves whatever shape the connector already wrote; do not change keys, only their values.
  • Etlworks creates a backup before every mutating action, but you are responsible for validating the change. The backup makes a bad edit or a bad reset reversible; it does not make the edit or reset correct.

When in doubt, contact Etlworks support before saving, restoring, or resetting. The editor's read-only display of the current offset is safe to share; it does not contain credentials.

Edit offsets from a CDC connection

Use this path when you are working on a specific CDC connection and want the editor to load the offset file Etlworks would use at runtime for that connection.

  1. Open the connection from Connections in the Etlworks UI.
  2. Stop the CDC flow that uses this connection, if it is currently running.
  3. In the connection editor's bottom toolbar, click the Offset File button.
  4. The CDC offset file modal opens with the offset file for this connection loaded. The path shown at the top of the modal is the resolved location, which is either the value of the connection's Offset File Name field if set, or the default name Etlworks derives from the connection name. In both cases the file lives under the tenant-aware {app.data}/debezium_data directory.
  5. If the offset file does not exist yet (the connection has never run), the modal still opens but the Status reads as not found and there is nothing to edit. This typically means the flow has not run yet, or the offset file has been deleted — see Offset or history file was deleted for the recovery path in that case.

Restore sources available here. When opened from a CDC connection, the editor only surfaces backups from the Standard backup folder. Backups written by an Offset Backup connection are not shown because the editor does not know which flow's backup connection to consult — that requires the flow context. If you need to restore from a scheduled automatic backup, open the editor from the flow instead (see Edit offsets from a CDC flow).

The rest of the workflow — reading entries, editing the position, validating, saving, restoring, or resetting — is the same across all three entry points and is described in The CDC offset editor below.

Edit offsets from Explorer

Use this path when you are working in Explorer, browsing a CDC Offset and History storage connection that may contain offset files for multiple CDC connections side by side.

  1. Open Explorer in the Etlworks UI.
  2. Expand the CDC Offset and History connection that holds the offset file you want to edit. See CDC Storage Connectors for how this connection type is configured.
  3. Stop the CDC flow whose offset file you are about to edit, if it is currently running.
  4. Right-click the offset file in the file list and choose Edit CDC Offset. The action is enabled only for offset files. History files (and any other file in the same folder) do not show the action — see Limitations and safety checks.
  5. The CDC offset file modal opens with the chosen file loaded.

An offset file in this view is recognized by its name: the filename contains offset and does not contain history. For a CDC connection whose name produces files like orders_pipeline_offset.dat and orders_pipeline_history.dat, only the first is editable through this menu.

Restore sources available here. Same as the connection entry point — only the Standard backup folder is consulted, because Explorer has no flow context.

Edit offsets from a CDC flow

Added in Etlworks 9.6.11. Use this path when you want the editor to behave exactly the way the runtime does: resolve tokenized file paths, use the flow's actual file names, and (when configured) surface automatic backups from the flow's Offset Backup connection alongside the standard editor backups.

  1. Open the saved CDC flow from Flows.
  2. Stop the flow if it is currently running.
  3. In the flow footer toolbar, click the Offset File button.
  4. The CDC offset file modal opens. The editor resolves the CDC connection used by the flow and loads the active offset and history files — the same files the runtime uses on the next start.

Two things this entry point does that the other two do not:

  • Resolves tokenized paths. If the CDC connection's Offset File Name or DDL History File Name is tokenized (for example, contains {connection_name} or {app.data}), the editor resolves the tokens against the flow context and opens the actually-in-use file. This works even when the same tokenized CDC connection is shared across multiple flows — opening the editor from a specific flow always shows that flow's files. See Tokenized offset and history paths for the token resolution rules.
  • Surfaces automatic backups from the Offset Backup connection. If the flow has a named connection called Offset Backup in its Connections tab, and the CDC connection has Automatically backup offset every (minutes) set to a positive value, the editor also lists backups written to that connection's configured folder. Those backups appear as a second restore source alongside the standard editor backups. See Restore from the Backup Connection.

Tokenized offset and history paths

The CDC connection's Offset File Name and DDL History File Name fields accept token expressions so that a single tokenized CDC connection can be reused across multiple flows without file-name collisions. When the editor is opened from a flow, tokens are resolved the same way the runtime resolves them.

Token sources and precedence

The editor resolves tokens from these sources, in this order:

  1. Flow Parameters (flow properties, set on the flow itself).
  2. Flow Variables.
  3. Standard resolvable tokens — most importantly {app.data}, which resolves to the tenant-aware data directory the runtime is using.

If the same token name is defined as both a Flow Parameter and a Flow Variable, the Flow Parameter value wins. This matches how the runtime resolves the same fields, so the editor and the runtime always look at the same file.

Path resolution rules

  • Relative paths are resolved under the tenant-aware {app.data}/debezium_data directory. For example, the value to_mongo/offsets_and_history/{connection_name}/offset.dat resolves to {app.data}/debezium_data/to_mongo/offsets_and_history/<connection_name>/offset.dat.
  • Absolute paths are used as-is (no {app.data} prefix). This is the case regardless of whether the value contains tokens — if the resolved path starts with a filesystem root, no prefix is applied.

Example

A CDC connection sets:

Offset File Name:      to_mongo/offsets_and_history/{connection_name}/offset.dat
DDL History File Name: to_mongo/offsets_and_history/{connection_name}/ddl_history.dat

A CDC flow using this connection defines a Flow Parameter:

connection_name = MongoDB Atlas CDC

When the editor is opened from this flow, it loads:

{app.data}/debezium_data/to_mongo/offsets_and_history/MongoDB Atlas CDC/offset.dat
{app.data}/debezium_data/to_mongo/offsets_and_history/MongoDB Atlas CDC/ddl_history.dat

Which is the same pair of files the runtime uses when it starts this flow.

The CDC offset editor

All three entry points open the same modal, titled CDC offset file. The modal shows the resolved file metadata at the top, one editable section per offset entry in the middle, and a footer with the action buttons. If restore sources are available, they are listed above the footer.

File metadata

The header shows four read-only values:

  • Offset file — the resolved absolute path of the file on the integrator host, under the tenant-aware {app.data}/debezium_data directory.
  • Status — either Found (the file exists and was read successfully) or a not-found state when the connection has never run or the file has been removed.
  • Size and Last modified — current file size in bytes and the timestamp of the last write. Useful for confirming that the file is the one the connector is actually using.
  • Backup folder — the Standard backup folder, always under the tenant-aware {app.data}/debezium_data/backup directory.

Offset entries

Below the header the modal shows one Offset entry N block for each entry in the file. Most CDC connections have a single entry; configurations that capture multiple databases or named partitions can have more than one. For each entry the modal displays:

  • Key — the entry's identifying key, shown as read-only context. This typically includes the logical connector name and a server descriptor. Do not attempt to change the key — the connector matches entries by key on read.
  • Position — the editable JSON value that describes where to resume. The format depends on the source database. This is the field you change. The editor is a standard Etlworks code editor with JSON syntax highlighting and dark-mode support.

The Position JSON keys are connector-specific. Typical examples:

Source Position keys you will see
MySQL file, pos, ts_sec, transaction_id
PostgreSQL lsn, txId, ts_usec
SQL Server commit_lsn, change_lsn, event_serial_no
Oracle scn, commit_scn
DB2 / AS/400 change_lsn (DB2), seq (AS/400 journal sequence)
MongoDB sec, ord, h

Edit only the values that need to change. Leave any keys you do not understand at their current values. The connector ignores unrecognized keys, but missing keys it expects will cause it to fail on restart.

Footer actions

The footer at the bottom of the modal shows up to five actions:

  • Restore Standard Backup (left) — opens the list of backups from the Standard backup folder that match the currently loaded offset. When there are no matching backups this control is not offered.
  • Restore Backup Connection (left, only when the editor is opened from a CDC flow and the flow has an eligible Offset Backup connection) — opens the list of automatic backups from the flow's Offset Backup connection. See Restore from the Backup Connection.
  • Close — dismisses the modal without saving.
  • Reset CDC State (red, destructive) — removes the active offset, history, and known sidecar state files after creating a standard backup zip. See Reset CDC State.
  • Reset edits — restores the position JSON to the value that was loaded from disk, discarding any edits made in this session. Only affects the editor field; nothing is written to disk.
  • Save Offset (primary) — validates the edited JSON and writes it after a confirmation prompt. See JSON validation and saving.

JSON validation and saving

The Save Offset button is disabled until two conditions hold:

  • The position JSON parses successfully (well-formed JSON).
  • The position JSON differs from the value that was loaded (you have actually made an edit).

If the JSON is malformed — missing a closing brace, an unquoted key, a trailing comma — the editor highlights the error and the button stays disabled.

Clicking Save Offset opens a confirmation dialog that summarizes the action and reminds you that a standard backup will be created in the Standard backup folder. Confirm to write the new value. On success the UI shows a success notification with the path of the backup zip — record it if you may need to revert.

The save operation itself is atomic: the new file is written to a temporary location, validated as readable, and then swapped into place. If any step fails, the original file is left unchanged.

Reset edits discards any changes you have made in this session and reloads the position from disk. Use this before you save when you want to try again from the original value.

Backups and restore

The editor writes a backup before every mutating action, and can restore from those backups directly. There are two kinds of backups the editor can consult, depending on the entry point.

Standard backup folder

The Standard backup folder is the tenant-aware directory {app.data}/debezium_data/backup. The editor writes a zip to this folder before every Save Offset and every Reset CDC State, so any accidental change can be reverted from the same session.

What's in each standard backup zip:

  • The active offset file at the moment of the backup.
  • The matching history file if it exists.
  • Any known snapshot or ad-hoc snapshot sidecar state files that belong with the offset. Some connectors — MongoDB, PostgreSQL, and AS/400 in particular — may not use a standard DDL history file but do use per-flow sidecar files for ad-hoc or resumable snapshot state. Those are preserved in the zip when present.

Backup filenames combine the current offset file's identity with a timestamp formatted per the CDC connection's Backup file timestamp format setting. When no format is configured, the same default used by the CDC automatic-backup path is applied. See Automatically Backing Up Offset and History Files for the timestamp format tokens.

Backup filtering: only backups linked to the active file are shown

The Standard backup folder is shared across every CDC connection on the tenant, so it can accumulate backups from many different offset files. The editor filters what it shows so you cannot accidentally restore an unrelated connection's backup on top of the active one.

Filtering rules:

  • Only backups whose contents map back to the currently loaded active offset and history files are listed. Backups for other offset files are hidden even if they are in the same folder.
  • Backups are sorted newest to oldest.
  • The list shows readable context for each backup: file name, size, last-modified timestamp, whether the backup contains a valid offset entry, whether it contains a matching history entry, the offset record count, and a short preview of the offset content when it can be read.
  • Backups that cannot be parsed as a Debezium/Kafka Connect offset map are listed as invalid and cannot be restored. They are shown so you can identify and remove them; the restore button is disabled on the row.
  • Only valid readable backups can be selected for restore.

Restore from the Standard backup folder

Click Restore Standard Backup in the editor footer. The editor lists all standard backups for the currently loaded offset (filtered per Backup filtering), most recent first.

Select a backup and confirm. Restore is double-confirmed: the first prompt names the backup, and the second prompt reminds you that the active offset will be overwritten and that the CDC flow's resume position will change.

On restore, the editor:

  • Overwrites the active offset file with the offset from the backup zip.
  • Writes the history file from the backup only if the backup includes a history entry and the active connector uses a history file. Connectors without a standard history file are not affected.
  • Writes any snapshot sidecar files included in the backup zip, when the connector uses them.
  • Shows a success notification and refreshes the editor to display the restored state.

Restore is blocked when the selected backup is invalid, unreadable, empty, missing, or not linked to the active offset file. These conditions are surfaced on the row and the restore button is disabled.

Restore from the Backup Connection

When the editor is opened from a saved CDC flow (see Edit offsets from a CDC flow) and the flow has an eligible Offset Backup connection, a second restore control appears in the footer: Restore Backup Connection.

Eligibility. All three conditions must hold:

  • The flow has a named connection called Offset Backup in its Connections tab.
  • The CDC connection has Automatically backup offset every (minutes) configured to a positive value.
  • The backup connection contains at least one automatic backup file linked to the active offset.

Where backups are read from. The editor uses the same path resolution as the runtime backup processor:

  • The Offset Backup connection's configured Directory (or the equivalent field for that connection type) is honored. Backups are not assumed to be at the bucket, share, or folder root.
  • Automatic backups are listed separately from standard editor backups.
  • The same filtering rules apply — only backups linked to the active offset are shown, newest to oldest, invalid ones flagged.

Selection, confirmation, and restore mechanics are identical to Restore from the Standard backup folder: double confirmation, overwrite of active offset and history files, sidecar restoration, blocked-for-invalid, success notification and refresh on completion.

Reset CDC State

Added in Etlworks 9.6.11, superseding the earlier “Delete files” action. Use Reset CDC State when you want to fully clear the CDC connector's local state so the next start behaves like a first-ever start — for example, when neither an edit nor a restore can recover the flow, or when you want to force a fresh snapshot from a clean state.

Clicking the button opens a double confirmation. The button is intentionally red and destructive.

On confirm, the editor:

  • Creates a standard backup zip of the current state (offset + history + known sidecar files) in the Standard backup folder. If you change your mind after resetting, this zip can be restored using Restore Standard Backup.
  • Deletes the active offset file.
  • Deletes the matching history file if it exists.
  • Deletes known ad-hoc / resumable snapshot sidecar state files that belong with the offset, when they exist.

What the next run does after a reset depends on the connector and its snapshot mode:

  • With an initial or ad-hoc initial snapshot mode, the next start performs a fresh snapshot of every monitored table.
  • With schema_only, the connector captures schema only and begins streaming from the current source position — existing rows are not backfilled.
  • With never, the connector may fail on startup because there is no offset to resume from and no snapshot to fall back to.

See Snapshot Management for the full behavior per snapshot mode.

Reset CDC State is a superset of the manual delete-files procedure traditionally used for CDC reset (see Resetting a CDC Flow). Use the button in place of manual file deletion — it also removes snapshot sidecar files a manual delete would miss, and it makes the reset reversible.

Editor behavior while the flow is running

As of Etlworks 9.6.11, the offset editor detects whether the CDC flow is currently running and enforces a safety guard on mutations:

  • Save Offset, Reset CDC State, and both restore buttons are disabled in the UI while the flow is running.
  • The backend also rejects mutation requests submitted while the flow is running, so a stale UI or a direct API call cannot bypass the guard.
  • Read-only inspection remains available — you can open the editor, view the current offset, browse backup listings, and see restore eligibility, but you cannot commit any change.

To make any change, stop the CDC flow first, then reopen the editor. This applies to every entry point, but is particularly relevant when opening the editor from a saved CDC flow — the flow is easily left running by accident.

Limitations and safety checks

The editor enforces several rules to prevent accidental damage. All three entry points share the same backend, so the same rules apply regardless of how the editor was opened.

  • History files are not editable. The Edit CDC Offset action does not appear for history files in Explorer, and the backend rejects any attempt to open one. History files have a different on-disk format and a different purpose — they record the schema of every monitored table at each DDL change so that historical events can still be interpreted — and they are not safe to edit through a simple JSON editor.
  • The Explorer action is only available on connections of type CDC Offset and History. Other file-storage connection types do not show the action, and the backend rejects requests originating from any other connection type.
  • File names with path separators are rejected. Filenames containing / or \, blank filenames, or anything that looks like a path-traversal attempt (such as .. segments) is rejected by the backend before the file is opened.
  • Files outside the tenant's debezium data folder are rejected. The backend resolves the file inside the tenant-aware {app.data}/debezium_data directory and rejects any request that would resolve outside it. This applies to both Explorer and connection entry points.
  • Files that do not look like offset files are rejected. The filename must contain offset and must not contain history. This rule is enforced server-side, not just in the UI.
  • The file must exist before it can be edited. If the connection has never run, the offset file has not been created yet, and there is nothing for the editor to load.
  • Mutations are refused while the CDC flow is running. See Editor behavior while the flow is running.
  • The editor validates JSON syntax and backup file readability, not source log availability. A well-formed offset or a readable backup can still reference a source log position that has been purged. Confirming that the source database can still serve records from a given position is a separate check that the editor cannot perform.

Troubleshooting notes

The Offset File button is missing on a connection

The button appears in the bottom toolbar of the connection editor for Debezium-backed CDC connections. If you do not see it on what you believe is a CDC connection, the connection type is probably something else — for example, a regular database connection that was created without the CDC flow type. Check the connection's connector class in Connections.

Edit CDC Offset is missing from the Explorer right-click menu

Three possible reasons:

  • The connection is not a CDC Offset and History connection. The action only appears for that connection type.
  • The file is not an offset file. The action only appears when the filename contains offset and does not contain history.
  • The right-clicked item is a folder, not a file.

Save Offset stays disabled

The button is disabled until the JSON is well-formed and it differs from what was loaded and the CDC flow is not running. Check all three. The most common cause is an unbalanced brace or a trailing comma in the edited position; the second most common is that the flow was left running.

Save, Reset, and Restore are all greyed out

The CDC flow using this offset file is currently running. Stop the flow, then reopen the editor. See Editor behavior while the flow is running.

Restore is greyed out on a specific backup

The backup is present in the list but cannot be restored. Typical causes:

  • The backup is invalid or unreadable — its offset content cannot be parsed as a Debezium/Kafka Connect offset map. This is flagged in the backup row.
  • The backup is empty or missing a required entry.
  • The backup is not linked to the currently loaded active offset file. Normally the backup filtering hides those, but a partial match may render as visible but non-restorable.

Choose a different backup, or contact support if all backups for this connection are flagged invalid.

Restore Backup Connection is not offered

The Backup Connection restore control appears only when the editor is opened from a CDC flow that satisfies all of the following:

  • The flow has a named connection called Offset Backup in its Connections tab.
  • The CDC connection has Automatically backup offset every (minutes) set to a positive value.
  • The backup connection actually contains at least one automatic backup file that maps back to the active offset.

If any condition is not met, only Restore Standard Backup is offered. When opened from a CDC connection or from Explorer, this control never appears because there is no flow context to identify the Offset Backup connection.

Reset CDC State completed but the files are still there

Reset removes files that the CDC connector currently uses. If the connection uses tokenized paths and the resolved paths at reset time did not match a live file, that file is not deleted. This is rare but can happen when a tokenized path resolves to different files in different contexts.

Reopen the editor from the same context you intended to reset (in particular, from the same flow if the reset was intended for that flow's tokenized files) and try again. Confirm the paths shown in the editor header match the files you expect to remove.

The flow fails on restart after an edit or a restore

If the flow fails at the position you set or restored, the source database almost certainly cannot resolve it — for example, the binlog file you named has been purged, the PostgreSQL LSN is outside the replication slot's window, the Oracle SCN is too old, and so on. Either move the position to one the source can still serve, or restore an older backup and contact support.

The modal opens but there are no offset entries

The file exists but is empty or contains no recognized entries. This usually means the connector started, created the file, and then failed before flushing any position. Consider running Reset CDC State to clear and re-initialize on the next start, or restore an earlier good backup with Restore Standard Backup if one exists.

See also

Related articles