Change data capture utilizes the SQL Server Agent to log insertions, updates, and deletions occurring in a table. So, it makes these data changes accessible to be easily consumed using a relational format. - learn.microsoft.com
This is useful when data from different databases is sucked up into a data lake, this way we can easily keep the data lake current with changes we care about.
You can use sample databases from Microsoft like AdventureWorks or Northwind or you can just invent your own. For simplicity and reasons of being a nerd I did the latter.
A. Create a container for the SQL Server with settings ready for CDC
Local Docker SQL Container
Spin up a DockerSQL Container. This will start the container with Product ID (PID) as Developer Edition.
capture_instance is the new table name. NULL will set the default based on the params example cdc.dbo_person_CT. You could set this to be foo_person_changes_CT (the _CT is always suffixed by default)
supports_net_changes is for when the column changes frequently, so only capture the net change (ie the last one)
captured_column_list these are column you want to track, you have to include the primary key
This will take a few seconds to complete as its creating jobs. The capture job captures the data as it changes from the log file. The cleanup job purges the captured data, it will only keep it for 3 days by default. You can change this with @retention= which needs to be in minutes.
If you want to disable the table you can use this SQL