CData Sync history mode provides a method for analyzing historical data in your data sources. History mode is a slowly changing dimension that stores and manages relatively static data (current and historical) in a data warehouse. That data can change slowly (but unpredictably) over time.
In Apache Kafka, the schema is regularly evolving (and often by design as the organization's data needs evolve). CData Sync history mode fits in line with this concept and allows you to easily manage changing schema from producers and reflect those changes easily for the consumers.
In CData, you can use history mode (via the Enable History Mode option) to track the change history for data rows (records) and see how your data changes over time. History mode is available for all connectors that support replication by incremental check columns.
CData supports a combined approach to analyzing historical data. That is, the Enable History Mode option offers both robust tracking for auditing as well as time-series analysis.
History mode works on a per-table basis. So, you can decide which tables you want to analyze and then activate the option for those tables only.
In standard mode, Sync merges and updates existing rows whereas in history mode, Sync appends updated rows to the database table.
When you activate history mode:
- Sync maintains a full history of data changes for each data record in the source database table.
- Sync records those change versions to the corresponding table in your destination database table.
To enable this functionality, Sync includes three new columns in the destination table. These columns are defined in the following table:
| Column Name | Column Type | Description |
| _cdatasync_active | Boolean | Specifies whether a record is active. |
| _cdatasync_start | Datetime | Specifies the datetime value of the incremental check column at the time the data record becomes active. This value indicates when the record was created or modified in the source table, based on a timestamp that increments with each data update. |
| _cdatasync_end | Datetime | Specifies the datetime value of the incremental check column at the time the data record becomes inactive. A null value in this column indicates that the record is active. |
| _cdatasync_operation | Varchar | Specifies the operation to use: Insert (I), Update (U), or Delete (D). Note: This column applies only when you use Change Data Capture (CDC). |
| _cdatasync_version | Varchar(100) | Specifies the version for each change in the format that is saved in the CSRS table. Note: This column applies only when you use CDC. |
Restrictions and limitations
With history mode, the following restrictions apply:
- The source table must support an incremental check column.
- The source table must contain a primary key.
- The incremental check column must be a timestamp (datetime) column.
- The incremental check column cannot be a pseudocolumn because pseudocolumns do not have a value in the response and are used as criteria only.
- The destination table cannot exist. (Use the Drop Table option on the Advanced tab to re-create the table with history mode active.)
In addition, support for history mode is limited to the following destinations:
- Apache Kafka
- SQL Server
- MySQL
- PostgreSQL
- Oracle DB
- Snowflake
- Databricks
- Redshift
Note: Additional destinations will be added in the future.
History mode activation for a job
To activate history mode for a job in Sync:
- Navigate to the Jobs tab.
- Click the job that contains the tables from which you want to select.
- Scroll to Job Settings and click Add Tables. In the Add Tables modal that opens, select your tables.
- Click Add Selected Tables to add the selected tables and to return to the Job Settings page.
- Click the Advanced tab to open the advanced settings.
- Scroll to Replicate Options and select Enable History Mode.
- Click Save Changes (not shown) in the upper right of the Job Settings page to save and activate history mode for your selected tables.
History mode activation for a task within a job
You can enable history mode for tasks, as well, from the Advanced tab in the task settings, as shown below:
In the advanced settings for a task, history mode has three options:
- Inherited From Job: If this option is specified, the task inherits history mode from the job.
- True: This option enables history mode for the task.
- False This option disables history mode for the task.
History mode is disabled in the task settings also if a table does not support incremental check columns.
Effects of changing the source table
When you change the source table by inserting, updating, or deleting rows, the destination is affected in various ways, as described in the following table:
| Source Change | Destination Effect |
| Inserted Row | A row is added to the destination table. _cdatasync_active is set to |
| Updated Row |
|
| Deleted Row | The current row in the destination table is updated. _cdatasync_active is set to |
Free trial & more information
Visit our CData Sync page to read more information about CData Sync. To try out history mode, download a free 30-day trial! As always, our world-class Support Team is ready to answer any questions you may have.
