Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

Change data capture from SQL Server with AWS DMS

Stream inserts, updates and deletes from Harbourline's SQL Server core-banking tables into the S3 data lake with AWS DMS full load plus CDC, then merge them into a current-state table that copes with late and out-of-order changes.

advanced⏱ 1 h 50 min🔴 Paid — optionalDEA-C01SAA-C03

Assumes: .NET 10 LTS · Microsoft.Data.SqlClient 7.1.2 · AWS SDK for .NET v4 (AWSSDK.DatabaseMigrationService 4.1.0.2) · AWS DMS 3.5.x · SQL Server 2022 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Explain change data capture (CDC) and why it beats nightly full extracts for a busy SQL Server.
  • Prepare SQL Server (on-premises, EC2 or Amazon RDS) for AWS DMS: recovery model, MS-CDC versus MS-Replication, permissions and log retention.
  • Configure a DMS full-load-and-CDC task that writes Parquet to S3 with an operation column and a commit timestamp, encrypted with KMS.
  • Choose between DMS Serverless and a replication instance.
  • Merge a stream of changes into a current-state table correctly, including deletes, late-arriving and out-of-order records.
  • Practise CDC for free with SQL Server in Docker and a C# change reader.

Prerequisites

Why this matters in banking

Harbourline's core banking runs on SQL Server. The data team's nightly job runs SELECT * FROM dbo.accounts and SELECT * FROM dbo.postings WHERE posted_at >= yesterday. As the bank grows, the extract takes three hours, blocks the batch window and still misses things: a posting that was updated after the extract, an account closed and reopened the same day, a row deleted by a correction. Risk wants intraday balances; the extract cannot provide them.

CDC reads the database's transaction log instead of querying tables. Every committed insert, update and delete is captured in order, with very little load on the source, and delivered within seconds or minutes. The lake then holds both the history of changes (great for audit) and a current-state copy (great for reporting) — if you merge the changes correctly. Getting the merge wrong is how a closed account reappears in a regulatory report.

The idea in plain language

A database writes every change to its transaction log before it changes the data files. Change data capture reads that log and turns it into a list of events: "row 42 inserted with these values", "row 42 updated: balance 100 → 80", "row 17 deleted".

AWS Database Migration Service (DMS) connects to a source database and a target (here, S3). A task can do a full load (copy every existing row once), then switch to CDC (copy every change after the full load started). DMS writes the changes as files, adding an operation column (I, U, D) and, if you ask, a commit timestamp.

Analogy: a full load is photocopying every page of the ledger once. CDC is then receiving a carbon copy of every new entry the clerks write, in the order they wrote them. To know today's balance you start from the photocopy and apply the carbons in order — and you must not apply Tuesday's carbon after Wednesday's.

What problem the AWS service solves

OptionHow it worksGood forWatch out for
Nightly full extractSELECT *Small tablesLoad on source, long windows, misses intra-day changes and deletes
Watermark extractWHERE updated_at > @lastTables with reliable updated_atMisses deletes; relies on every writer updating the column
DMS full load + CDCReads the log (MS-Replication or MS-CDC)Ongoing replication of many tables with low source impactNeeds source configuration and log retention; at-least-once delivery; schema changes need care
Native SQL Server CDC + your own readercdc.fn_cdc_get_all_changes_*Small, controlled use; local practiceYou build delivery, retries, checkpoints

DMS Serverless provisions and scales capacity for you (in DMS capacity units, DCUs), billed for what runs. DMS with replication instances gives you a fixed instance you size and patch. For a learning or bursty workload Serverless removes sizing guesswork; for a long-running, steady replication a right-sized instance can be cheaper. Both support SQL Server sources and S3 targets ⚠ VERIFY the current supported-endpoints list for DMS Serverless before designing.

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SQL Server CDC (sys.sp_cdc_enable_table)DMS reading MS-CDC or MS-Replication dataDMS manages reading, checkpointing and delivery; you still configure the source
Transactional replication to a reporting serverDMS CDC to S3 or another databaseTarget is often the lake, not another SQL Server
Azure Data Factory CDC / mapping data flow CDCDMS task with CDCDMS is a dedicated service with its own task settings
Azure Database Migration ServiceAWS DMSSame name idea; AWS DMS also does ongoing replication
SSIS slowly changing dimensionSpark/Athena MERGE into IcebergMerge logic is explicit SQL or Spark code
Debezium on Event Hubs (Kafka API)Debezium on Amazon MSK (MSK Connect)Alternative when the bank standardises on Kafka

Architecture

Security-boundary view. DMS runs in private subnets and reaches the on-premises database over the bank's VPN or Direct Connect, with TLS to SQL Server. Source credentials live in Secrets Manager and DMS reads them through an IAM role. DMS writes to S3 through a gateway endpoint using a role that can only write under cdc/ in the raw bucket and encrypt with alias/hl-m07-data.

Preparing SQL Server

DMS reads ongoing changes in one of two ways:

  • MS-Replication (for tables with a primary key): DMS sets up or uses transactional replication publications to capture changes.
  • MS-CDC (for tables without a primary key, and on Amazon RDS for SQL Server): native change data capture tables.

Common requirements (check the DMS SQL Server source documentation for your version):

  • Full recovery model — the log must keep changes until DMS has read them; with simple recovery the log is truncated.
  • Log backups and retention that keep log records long enough for DMS to catch up after an outage. On RDS you set a CDC retention/polling configuration.
  • Permissions for the DMS login (for example db_owner on the source database for setup on self-managed SQL Server, or the documented minimal set).
  • SQL Server Agent running, because CDC capture and cleanup jobs run as Agent jobs.

On Amazon RDS for SQL Server, you cannot run sys.sp_cdc_enable_db directly; you use the RDS stored procedure:

-- RDS for SQL Server: enable CDC for the database, then for each table.
EXEC msdb.dbo.rds_cdc_enable_db 'CoreBanking';
GO
USE CoreBanking;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'accounts', @role_name = NULL, @supports_net_changes = 1;

Late-arriving and out-of-order records

Two different problems, often confused:

  • Late-arriving data (business time): a posting with value_date = 2026-11-10 that is inserted on 2026-11-14. The change arrives on time; the business date is in the past. Partitioning by ingestion date (as DMS does) keeps files manageable; your reports must recompute 10 November when it arrives. The batch pipeline flags these as LATE_ARRIVING.
  • Out-of-order delivery (processing time): two changes to the same row land in the lake in a different order from how they were committed — for example because a task was restarted and re-sent a batch, or because you processed two files in parallel. If you "take the last file processed", you can overwrite a newer balance with an older one.

The rule that fixes out-of-order delivery: order by the source commit position, not by arrival. Ask DMS for a commit timestamp column (and, if precision matters, the source log sequence via transaction metadata), keep the latest change per key by that order, and make the merge idempotent so replaying a file changes nothing.

Step-by-step implementation

Practise locally: SQL Server with CDC in Docker

Run SQL Server 2022 Developer edition with SQL Server Agent enabled, create dbo.accounts, enable CDC, and make some changes. This is free and teaches what DMS reads.

Read the changes from C#

Use cdc.fn_cdc_get_all_changes_dbo_accounts between two log sequence numbers (LSNs) and map __$operation (1 delete, 2 insert, 3 update-before, 4 update-after) to D/I/U. Save the last LSN as a checkpoint.

Design the DMS task (or deploy it with credits)

Source endpoint (SQL Server, TLS, Secrets Manager), target endpoint (S3, Parquet, TimestampColumnName, CdcPath, date partitioning, SSE-KMS), table mappings for dbo.accounts and dbo.postings, migration type full-load-and-cdc.

Merge into a current-state table

Keep the latest change per key by commit timestamp, then MERGE INTO an Iceberg table: delete when the latest operation is D, update when matched, insert when not matched.

Validate

Compare row counts and a control total (sum of balances) between the source and the current-state table at a known point in time. DMS data validation can also compare source and target for supported targets ⚠ VERIFY whether validation applies to S3 targets in your DMS version.

AWS Console instructions

  1. Secrets Manager → Store a new secret → Credentials for other database (or Other type of secret) with the DMS login; encrypt with alias/hl-m07-data.
  2. AWS DMS → Endpoints → Create endpoint → Source, engine Microsoft SQL Server, access via Secrets Manager, SSL mode verify-full where possible.
  3. Endpoints → Create endpoint → Target, engine Amazon S3. Endpoint settings: DataFormat=parquet, TimestampColumnName=commit_ts, CdcPath=cdc, DatePartitionEnabled=true, EncryptionMode=SSE_KMS, ServerSideEncryptionKmsKeyId=<key ARN>, service access role.
  4. Serverless replications → Create replication: migration type Full load and change data capture, choose the endpoints, minimum and maximum DCUs, private subnets, and table mappings selecting dbo.accounts and dbo.postings.
  5. Start the replication, then watch Table statistics for inserts/updates/deletes per table.

AWS CLI

export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1

# Target endpoint: Parquet in the raw bucket under cdc/, commit timestamp column, KMS encryption.
aws dms create-endpoint --endpoint-identifier hl-m07-s3-cdc --endpoint-type target --engine-name s3 \
  --s3-settings '{
    "BucketName": "hl-m07-raw-111122223333",
    "BucketFolder": "cdc",
    "ServiceAccessRoleArn": "arn:aws:iam::111122223333:role/hl-m07-dms-s3",
    "DataFormat": "parquet",
    "ParquetVersion": "parquet-2-0",
    "TimestampColumnName": "commit_ts",
    "IncludeOpForFullLoad": true,
    "CdcPath": "changes",
    "DatePartitionEnabled": true,
    "EncryptionMode": "SSE_KMS",
    "ServerSideEncryptionKmsKeyId": "arn:aws:kms:us-east-1:111122223333:key/REPLACE"
  }' \
  --tags Key=Project,Value=cea-lab Key=Lab,Value=m07-cdc Key=Environment,Value=learn Key=Owner,Value=learner

# Table mappings: two tables from dbo.
cat > mappings.json <<'EOF'
{ "rules": [
  { "rule-type": "selection", "rule-id": "1", "rule-name": "accounts", "object-locator": { "schema-name": "dbo", "table-name": "accounts" }, "rule-action": "include" },
  { "rule-type": "selection", "rule-id": "2", "rule-name": "postings", "object-locator": { "schema-name": "dbo", "table-name": "postings" }, "rule-action": "include" }
] }
EOF

# Serverless replication config: full load, then CDC; capacity 1-4 DCU.
aws dms create-replication-config --replication-config-identifier hl-m07-core-cdc \
  --source-endpoint-arn arn:aws:dms:us-east-1:111122223333:endpoint:SOURCE \
  --target-endpoint-arn arn:aws:dms:us-east-1:111122223333:endpoint:TARGET \
  --replication-type full-load-and-cdc --table-mappings file://mappings.json \
  --compute-config '{"MinCapacityUnits":1,"MaxCapacityUnits":4,"ReplicationSubnetGroupId":"hl-m07-dms-subnets","VpcSecurityGroupIds":["sg-0123"],"MultiAZ":false}'

aws dms start-replication --replication-config-arn arn:aws:dms:us-east-1:111122223333:replication-config:REPLACE \
  --start-replication-type start-replication
aws dms describe-replications --query 'Replications[].{Id:ReplicationConfigIdentifier,Status:Status}'

Setting names such as IncludeOpForFullLoad, TimestampColumnName, CdcPath and DatePartitionEnabled are documented S3 endpoint settings; check the exact capacity-unit range allowed for MinCapacityUnits and MaxCapacityUnits in the current DMS Serverless documentation ⚠ VERIFY.

Infrastructure as Code

resource "aws_dms_s3_endpoint" "cdc_target" {
  endpoint_id                       = "hl-m07-s3-cdc"
  endpoint_type                     = "target"
  bucket_name                       = "hl-m07-raw-${data.aws_caller_identity.me.account_id}"
  bucket_folder                     = "cdc"
  service_access_role_arn           = aws_iam_role.dms_s3.arn
  data_format                       = "parquet"
  parquet_version                   = "parquet-2-0"
  timestamp_column_name             = "commit_ts"
  include_op_for_full_load          = true
  cdc_path                          = "changes"
  date_partition_enabled            = true
  encryption_mode                   = "SSE_KMS"
  server_side_encryption_kms_key_id = var.kms_key_arn
  tags                              = local.tags
}

resource "aws_dms_replication_config" "core" {
  replication_config_identifier = "hl-m07-core-cdc"
  replication_type              = "full-load-and-cdc"
  source_endpoint_arn           = aws_dms_endpoint.sqlserver.endpoint_arn
  target_endpoint_arn           = aws_dms_s3_endpoint.cdc_target.endpoint_arn
  table_mappings                = file("${path.module}/mappings.json")
  compute_config {
    min_capacity_units           = 1
    max_capacity_units           = 4
    replication_subnet_group_id  = aws_dms_replication_subnet_group.private.id
    vpc_security_group_ids       = [aws_security_group.dms.id]
    multi_az                     = false
  }
  tags = local.tags
}

Complete code

1. Free local practice: SQL Server with CDC in Docker

# SQL Server 2022 Developer edition (free for development) with SQL Server Agent (needed by CDC jobs).
# Learning shortcut: SA password in an environment variable on your own machine only.
docker run -d --name hl-m07-sql -e ACCEPT_EULA=Y -e MSSQL_PID=Developer -e MSSQL_AGENT_ENABLED=true \
  -e "MSSQL_SA_PASSWORD=Local-Only-Pa55word!" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
setup-cdc.sql
CREATE DATABASE CoreBanking;
GO
ALTER DATABASE CoreBanking SET RECOVERY FULL;   -- log keeps changes until read
GO
USE CoreBanking;
CREATE TABLE dbo.accounts (
  account_id  char(14)      NOT NULL PRIMARY KEY,   -- ACC- + 10 digits
  customer_id char(12)      NOT NULL,
  currency    char(3)       NOT NULL,
  status      varchar(8)    NOT NULL,
  balance     decimal(18,2) NOT NULL,
  updated_at  datetime2(3)  NOT NULL DEFAULT sysutcdatetime()
);
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'accounts', @role_name = NULL;
GO
INSERT dbo.accounts (account_id, customer_id, currency, status, balance)
VALUES ('ACC-1000000001', 'CUS-00000001', 'GBP', 'open', 1500.00),
       ('ACC-1000000002', 'CUS-00000002', 'EUR', 'open', 250.00);
UPDATE dbo.accounts SET balance = 1420.50, updated_at = sysutcdatetime() WHERE account_id = 'ACC-1000000001';
UPDATE dbo.accounts SET status = 'closed', balance = 0, updated_at = sysutcdatetime() WHERE account_id = 'ACC-1000000002';
DELETE dbo.accounts WHERE account_id = 'ACC-1000000002';

2. A C# change reader with a checkpoint

CdcReader.cs
// dotnet add package Microsoft.Data.SqlClient --version 7.1.2
using System.Globalization;
using Microsoft.Data.SqlClient;

// Learning shortcut: local container credentials from an environment variable. In AWS, read from Secrets Manager.
var connectionString = Environment.GetEnvironmentVariable("HL_SQL")
    ?? "Server=localhost,1433;Database=CoreBanking;User Id=sa;Password=Local-Only-Pa55word!;TrustServerCertificate=True";
var checkpointFile = "accounts.lsn";

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();

// From: the LSN after our checkpoint (or the earliest available). To: the current maximum LSN.
byte[] fromLsn = File.Exists(checkpointFile)
    ? await ScalarAsync<byte[]>(conn, "SELECT sys.fn_cdc_increment_lsn(@lsn)", new SqlParameter("@lsn", Convert.FromHexString(await File.ReadAllTextAsync(checkpointFile))))
    : await ScalarAsync<byte[]>(conn, "SELECT sys.fn_cdc_get_min_lsn('dbo_accounts')");
var toLsn = await ScalarAsync<byte[]>(conn, "SELECT sys.fn_cdc_get_max_lsn()");

if (CompareLsn(fromLsn, toLsn) > 0)
{
    Console.WriteLine("No new changes.");
    return;
}

await using var cmd = new SqlCommand(
    """
    SELECT __$start_lsn, __$seqval, __$operation, sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS commit_ts,
           account_id, currency, status, balance
    FROM cdc.fn_cdc_get_all_changes_dbo_accounts(@from, @to, N'all')
    ORDER BY __$start_lsn, __$seqval
    """, conn);
cmd.Parameters.AddWithValue("@from", fromLsn);
cmd.Parameters.AddWithValue("@to", toLsn);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var op = reader.GetInt32(2) switch { 1 => "D", 2 => "I", 4 => "U", _ => "?" }; // 3 = before-image, skipped by 'all'
    var commitTs = reader.GetDateTime(3);
    Console.WriteLine(string.Create(CultureInfo.InvariantCulture,
        $"{op} {commitTs:yyyy-MM-ddTHH:mm:ss.fff} {reader.GetString(4)} {reader.GetString(5)} {reader.GetString(6),-6} {reader.GetDecimal(7),10:0.00}"));
}

// Save the checkpoint only after the batch is safely written downstream.
await File.WriteAllTextAsync(checkpointFile, Convert.ToHexString(toLsn));

static async Task<T> ScalarAsync<T>(SqlConnection c, string sql, params SqlParameter[] parameters)
{
    await using var command = new SqlCommand(sql, c);
    command.Parameters.AddRange(parameters);
    return (T)(await command.ExecuteScalarAsync())!;
}

static int CompareLsn(byte[] a, byte[] b) => a.AsSpan().SequenceCompareTo(b);

3. Merging DMS output into a current-state Iceberg table (Athena)

merge_accounts.sql
-- Raw CDC files catalogued as hl_m07_raw.cdc_accounts with columns op, commit_ts, account_id, ... (from DMS)
-- and a partition column ingest_date taken from the DMS date-partitioned folder names.
-- Target: an Iceberg table that always holds the latest state per account.
CREATE TABLE IF NOT EXISTS hl_m07_curated.accounts_current (
  account_id string, customer_id string, currency string, status string, balance decimal(18,2),
  source_commit_ts timestamp
)
LOCATION 's3://hl-m07-curated-111122223333/accounts_current/'
TBLPROPERTIES ('table_type' = 'ICEBERG');

MERGE INTO hl_m07_curated.accounts_current t
USING (
  -- Latest change per key by SOURCE commit time, not by file arrival: fixes out-of-order delivery.
  SELECT * FROM (
    SELECT c.*, row_number() OVER (PARTITION BY account_id ORDER BY commit_ts DESC) AS rn
    FROM hl_m07_raw.cdc_accounts c
    -- Only files that arrived since the last merge (by ingestion-date partition). Overlap is harmless:
    -- the commit-timestamp guard below ignores anything not newer than the current row.
    WHERE c.ingest_date >= date '2026-11-17'
  ) WHERE rn = 1
) s
ON t.account_id = s.account_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.commit_ts > t.source_commit_ts THEN
  UPDATE SET customer_id = s.customer_id, currency = s.currency, status = s.status,
             balance = s.balance, source_commit_ts = s.commit_ts
WHEN NOT MATCHED AND s.op <> 'D' THEN
  INSERT (account_id, customer_id, currency, status, balance, source_commit_ts)
  VALUES (s.account_id, s.customer_id, s.currency, s.status, s.balance, s.commit_ts);

Code explained line by line

  1. ALTER DATABASE ... SET RECOVERY FULL — with the simple recovery model, the log is truncated at checkpoints and CDC cannot rely on it.
  2. sys.sp_cdc_enable_db / sp_cdc_enable_table — create the cdc schema, change tables and Agent capture/cleanup jobs. On RDS use msdb.dbo.rds_cdc_enable_db for the database step.
  3. sys.fn_cdc_get_min_lsn('dbo_accounts') — the oldest LSN still available for that capture instance; changes older than the cleanup retention are gone, which is why consumers must not fall behind.
  4. sys.fn_cdc_increment_lsn(@lsn) — start just after the last processed LSN, so nothing is read twice.
  5. cdc.fn_cdc_get_all_changes_dbo_accounts(@from, @to, N'all') — every change in the range; 'all' returns after-images of updates (operation 4) and skips before-images (3).
  6. __$operation mapping — 1 delete, 2 insert, 4 update after-image; mapped to DMS-style D/I/U.
  7. ORDER BY __$start_lsn, __$seqval — commit order, then order within the transaction.
  8. sys.fn_cdc_map_lsn_to_time — the commit time of the transaction; this is the ordering column for merges downstream.
  9. Writing the checkpoint last — if the program crashes before writing downstream, the next run re-reads the same range (at-least-once), so downstream must be idempotent.
  10. row_number() OVER (PARTITION BY account_id ORDER BY commit_ts DESC) — keeps only the newest change per key in this batch.
  11. WHEN MATCHED AND s.op = 'D' THEN DELETE — a delete removes the row from the current state (the change history keeps the evidence).
  12. WHEN MATCHED AND s.commit_ts > t.source_commit_ts — an older change arriving late cannot overwrite a newer state: this is the out-of-order guard, and it makes replays harmless.
  13. WHEN NOT MATCHED AND s.op <> 'D' — a delete for a row we never saw is ignored instead of inserting a ghost.

Security considerations

⚠ Learning shortcut — not for production

The local container uses the sa login and a password in an environment variable, with TrustServerCertificate=True. In production, DMS uses a dedicated login with the documented minimum permissions, credentials in Secrets Manager (rotated), TLS with certificate verification, and the replication runs in private subnets reaching the source over VPN or Direct Connect — never over the public internet.

  • CDC copies everything that changes, including columns you may not want in the lake (national insurance numbers, notes). Use DMS table-mapping transformation rules to remove columns, or exclude tables, before data leaves the source.
  • Raw CDC files contain PII and history — including values that were later corrected or deleted. Restrict the cdc/ prefix to the pipeline role and a small operations group.
  • Encrypt the S3 target with SSE-KMS and the DMS resources with the same customer managed key.
  • Monitor lag: if DMS falls behind beyond the source's log retention, you need a new full load. Alarm on CDCLatencySource and CDCLatencyTarget ⚠ VERIFY metric names for Serverless replications.

Compliance review required

Erasure requests under UK GDPR must reach every copy, including raw change history in the lake. Decide with the data-protection officer how long raw CDC files are kept, and how a deletion in the source is honoured in history (for example by expiring raw changes after a short retention and keeping only the curated, minimised table).

Banking use case

Harbourline replicates dbo.accounts and dbo.postings from core banking with DMS full load + CDC. Postings flow into the raw zone every minute; a Glue job every 15 minutes merges accounts into an Iceberg accounts_current table and appends validated postings to the curated transactions table. The risk team gets intraday balances; the nightly reconciliation compares sum(balance) from accounts_current with the core-banking end-of-day figure, and any difference becomes a BREAK to investigate.

Common mistakes

  • Simple recovery model on the source → CDC cannot work reliably. Use full recovery and log backups.
  • Ordering by file name or arrival time → old values overwrite new ones after a restart. Order by commit timestamp/LSN.
  • Ignoring deletes → closed and deleted accounts live forever in reports. Handle D explicitly.
  • Letting DMS lag beyond log retention → changes lost; a full reload is the only fix. Alarm on latency.
  • Treating CDC as exactly-once → duplicates after task restarts. Make merges idempotent.
  • Replicating every column "just in case" → PII sprawl. Select and transform columns at the source.

Troubleshooting

SymptomLikely causeFix
Task fails: "database is not in full recovery model" or log not availableRecovery model or log backups not configuredSet full recovery, take a full backup, configure log backups/retention
No changes captured on RDSCDC not enabled with rds_cdc_enable_db, or table not enabledRun the RDS procedure; enable each table
Target files lack the Op column for full-load rowsIncludeOpForFullLoad not setSet it so full-load rows show I
Current-state table shows deleted accountsMerge ignores DAdd WHEN MATCHED AND op = 'D' THEN DELETE
Balance goes backwards after a restartMerge took the last processed fileUse the commit timestamp guard
cdc.fn_cdc_get_all_changes_... error "insufficient number of arguments" or invalid rangeLSN range outside retention or from > toStart from fn_cdc_get_min_lsn; check retention

Cost and cleanup

DMS Serverless bills for capacity used per hour (DCUs) and DMS replication instances bill per instance-hour plus storage; check the DMS pricing page and the AWS Pricing Calculator for current rates ⚠ VERIFY — the public page does not list the per-DCU rate directly. A SQL Server source on Amazon RDS adds licence-included instance costs. Free alternative (recommended for this module): do all of the hands-on work with the local container and the C# reader, and treat the DMS configuration as a design exercise.

# Local cleanup
docker rm -f hl-m07-sql
# AWS cleanup, if you deployed
aws dms stop-replication --replication-config-arn <arn>
aws dms delete-replication-config --replication-config-arn <arn>
aws dms delete-endpoint --endpoint-arn <source-arn>
aws dms delete-endpoint --endpoint-arn <target-arn>
aws s3 rm s3://hl-m07-raw-111122223333/cdc/ --recursive

Exercises

  • 🟢 Guided: Run the local container, enable CDC, make five changes (including a delete) and print them with CdcReader.cs. Run it twice and show the second run prints "No new changes".
  • 🟡 Challenge: Write the changes from CdcReader.cs as JSON Lines with op, commit_ts and the row, then implement the merge in C# (an in-memory dictionary keyed by account_id) with the same out-of-order guard. Unit test it with shuffled input.
  • 🔴 Live problem: After a network outage of six hours, the DMS replication shows an error that required log records are no longer available. Risk needs intraday balances by 14:00. Plan the recovery, including how you avoid double counting postings already in the lake.

Assignment

Produce a design document for "Core banking CDC to the Harbourline lake": source preparation checklist, DMS settings, IAM and network diagram, merge logic, data-quality checks, retention and erasure approach, and a cost estimate. Include the local C# reader as a proof of concept.

Solutions

Knowledge check

Interview questions

  • "How would you get SQL Server data into a lake without hurting production?" — Log-based CDC with DMS (full load + CDC), read replicas or the secondary for the full load where possible, private connectivity and least-privilege logins.
  • "How do you handle deletes in a data lake?" — Keep the change history in raw, and apply deletes to a current-state table with a MERGE (Iceberg/Hudi/Delta), guarded by commit order.
  • "What can go wrong with CDC?" — Lag beyond log retention, schema changes, duplicates after restarts, missing deletes, PII sprawl.
  • "DMS Serverless or instance?" — Serverless for variable or unknown load and less management; instances for steady, long-running replication where a right-sized instance is cheaper.

Certification practice

Completion checklist

  • I can explain log-based CDC and full load + CDC
  • I enabled CDC locally and read changes from C# with a checkpoint
  • I can list the S3 endpoint settings that matter (format, Op, commit timestamp, partitioning, KMS)
  • I can write an idempotent, out-of-order-safe merge including deletes
  • I removed the local container and any DMS resources

Key takeaways

  • CDC reads the transaction log: low source impact, captures updates and deletes, delivered at least once.
  • Prepare SQL Server properly: full recovery, log retention, CDC or replication, Agent running; on RDS use rds_cdc_enable_db.
  • Ask DMS for an operation column and a commit timestamp; order by commit time, never by arrival.
  • Keep raw change history for audit and an Iceberg current-state table for reporting, merged idempotently.
  • Practise locally for free; DMS itself is a paid service — design first.

Further reading

Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page