Capturing CDC Changes in Aternity

Introduction

Aternity is a digital experience management platform that provides insights into user interactions with applications and services. Capturing Change Data Capture (CDC) changes in Aternity allows organizations to monitor and analyze data modifications in real-time, enabling proactive decision-making and performance optimization.

In this article, we'll explore how to integrate CDC changes into Aternity for enhanced visibility and analysis: Understanding Change Data Capture (CDC) in Database Systems

Integration Overview: Integrating CDC changes into Aternity involves leveraging Aternity's data ingestion capabilities to capture and process CDC data from the source database. This process typically includes configuring the CDC source, defining the data extraction method, and configuring Aternity to consume and analyze the CDC data.

Step-by-Step Integration Process

Step 1. Configure CDC Source

First, ensure that CDC is properly configured on the source database. This involves enabling CDC on the database and relevant tables, as outlined in the database documentation.

-- Enable CDC on the database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'MyTable',
    @role_name = NULL,
    @supports_net_changes = 1;

Step 2. Extract CDC Data

Next, extract CDC data from the source database using an appropriate method. This may involve querying the CDC tables directly or using database connectors or middleware tools to retrieve the CDC changes.

public List<CDCChange> GetCDCChanges()
{
    List<CDCChange> changes = new List<CDCChange>();
    
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        
        SqlCommand command = new SqlCommand("SELECT * FROM cdc.dbo_MyTable_CT", connection);
        SqlDataReader reader = command.ExecuteReader();
        
        while (reader.Read())
        {
            // Parse CDC change data and populate CDCChange object
            CDCChange change = new CDCChange();
            change.Operation = (CDCOperation)reader["__$operation"];
            change.TableName = (string)reader["__$tablename"];
            // Populate other CDC change properties
            // ...
            
            changes.Add(change);
        }
    }
    
    return changes;
}

Step 3. Transform and Enrich Data

Transform and enrich the extracted CDC data as needed to meet the requirements of Aternity. This may involve mapping CDC changes to Aternity's data schema, applying data transformations, and enriching the data with additional context or metadata.