Database table configuration options
The configuration options in Relational Database Management Systems (RDBMS) options enable you to manage settings within different extraction Data Flow modes, including Standard Extraction, CDC (Change Data Capture), and System Versioning.
Standard extraction configuration
Configuration options
The configuration options are:
Show All:
Selecting this option presents all existing selected schema tables, including both checked and unchecked tables.
Show Selected Tables:
Choosing this option displays only the selected schema tables, i.e., checked tables.
Edit Time Period for All Tables:
Use this option to set the same time period value for all tables using the Incremental (date/timestamp-based Increment) extract method. This ensures consistency across tables.
Example
Suppose 3 different tables with an Incremental extract method have different Incremental date ranges. By selecting the Edit Time Period for All Tables option and configuring a start date, Data Integration applies the change to all selected incremental (date) based tables.
You can find the identical configuration options within MariaDB under the System Versioning extraction mode, and the process remains unchanged; be sure to designate System versioning as your chosen extraction mode.
CDC extraction configuration
The CDC extraction configuration options provides access on certain RDBMSs that offer support for Change Data Capture, including MySQL, SQL Server, Oracle, and PostgreSQL.
Configuration options
The following configuration options are available:
Show All:
This option displays all selected schema tables, as described earlier.
Show Selected Tables:
Like the standard menu, this option shows only the selected schema tables.
Set Initial Migration for All Tables:
By selecting this option, all selected tables' statuses change to Waiting For Migration. When you trigger the Data Flow, an initial migration process fetches all table data from the database. Afterward, tables may have a Streaming status for CDC.
Remove Initial Migration for All Tables:
This option changes the status of all selected tables to Streaming. With this option, Data Integration skips the initial migration process and captures changes directly through CDC.
Remove initial migration
When you trigger the Data Flow, there is no need for an initial migration process, and Data Integration fetches table changes (CRUD commands) via CDC.
Example Use Case - Skipping the Initial Migration (CDC): If a user already has all historical data on the Data Warehouse (DWH) and wants to skip the migration process, follow these steps:
- Check all desired tables from the relevant schemas on the UI.
- Click Remove Initial Migration for All Tables to change table statuses to Streaming.
- Ensure you define match key(s) for each selected table (for Upsert-Merge loading mode).
- Enable the stream.
- After a successful Data Flow run, check the target table's data alignment.
This effectively skips the migration process, but note that before affecting existing production Target table(s), make sure to perform the migration skipping process on each selected table using a Target table prefix (e.g., skip_migration_test_<table_name>) to create a temporary table. After Data Integration pushes data to the temporary target table, compare data types and data to the existing production table. If everything aligns, remove the prefix and write to the existing Target table.
You can find the identical configuration options within Microsoft SQL Server under the Change Tracking extraction mode. The only difference is that if you opt for the Remove Initial Migration for All Tables option, the table status will switch to Tracked instead of Streaming as it does in CDC. Make sure to specify Change Tracking as your selected extraction mode.
