Matillion ETL Shared Job
Author: Matillion
Date Posted: Oct 29, 2023
Last Modified: Oct 29, 2023
Author: Matillion
Date Posted: Oct 29, 2023
Last Modified: Oct 29, 2023
CDC Microsoft SQL Server
Load change data from a Microsoft SQL Server table with Change Data Capture (CDC) enabled.
In Microsoft SQL Server, Change Data Capture (CDC) can be enabled on a table so that all data changes are captured into a separate change table. Loading this change data into your data warehouse using a Matillion ETL orchestration job is an efficient method to keep your tables up to date with your source SQL Server database.
This download contains two shared jobs that demonstrate how Matillion ETL can use the SQL Server CDC change data.
Stage latest changes Parameters
| Parameter | Description |
|---|---|
| host | MSSQL hostname |
| database | MSSQL database containing the CDC tables |
| username | The username to use to authenticate with MSSQL |
| password_manager_entry | Name of the Project Group Password Manager entry to use to authenticate with MSSQL |
| capture_instance | The MSSQL CDC capture instance. This usually has the form [schema]_[table name], e.g. dbo_Accounts |
| change_type | Retrieve all CDC records since the last update, or the net CDC records. Options are all or net |
| previous_max_lsn | The maximum LSN value from the previous batch of extracted CDC data. If left blank, all currently available CDC records for the capture instance will be loaded |
| warehouse | Snowflake warehouse to use to load the data |
| target_database | Snowflake database to store the target table |
| target_schema | Snowflake schema containing the target table |
| target_table | Name of the target table in Snowflake |
Process latest changes Parameters
| Parameter | Description |
|---|---|
| host | MSSQL hostname |
| database | MSSQL database containing the CDC tables |
| username | The username to use to authenticate with MSSQL |
| password_manager_entry | Name of the Project Group Password Manager entry to use to authenticate with MSSQL |
| capture_instance | The MSSQL CDC capture instance. This usually has the form [schema]_[table name], e.g. dbo_Accounts |
| warehouse | Snowflake warehouse to use to load the data |
| target_database | Snowflake database to store the target table |
| target_schema | Snowflake schema containing the target table |
| target_table | Name of the target table in Snowflake |
Prerequisites
A SQL Server source database table with Change Data Capture enabled.
Downloads
Licensed under: Matillion Free Subscription License
- Download METL-aws-sf-1.50.9-Load-Microsoft-SQL-Server-CDC-data.melt
- Platform: AWS
- Target: Snowflake
- Version: 1.50.9 or higher