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.
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
- Module 7 overview, File formats, Data lake vs warehouse vs lakehouse (Iceberg and
MERGE) - Module 6: Databases for SQL Server on AWS
- Tools: Docker; AWS CLI v2 (
learn-sandbox) only for the optional AWS part
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
| Option | How it works | Good for | Watch out for |
|---|---|---|---|
| Nightly full extract | SELECT * | Small tables | Load on source, long windows, misses intra-day changes and deletes |
| Watermark extract | WHERE updated_at > @last | Tables with reliable updated_at | Misses deletes; relies on every writer updating the column |
| DMS full load + CDC | Reads the log (MS-Replication or MS-CDC) | Ongoing replication of many tables with low source impact | Needs source configuration and log retention; at-least-once delivery; schema changes need care |
| Native SQL Server CDC + your own reader | cdc.fn_cdc_get_all_changes_* | Small, controlled use; local practice | You 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 equivalent | What's different |
|---|---|---|
SQL Server CDC (sys.sp_cdc_enable_table) | DMS reading MS-CDC or MS-Replication data | DMS manages reading, checkpointing and delivery; you still configure the source |
| Transactional replication to a reporting server | DMS CDC to S3 or another database | Target is often the lake, not another SQL Server |
| Azure Data Factory CDC / mapping data flow CDC | DMS task with CDC | DMS is a dedicated service with its own task settings |
| Azure Database Migration Service | AWS DMS | Same name idea; AWS DMS also does ongoing replication |
| SSIS slowly changing dimension | Spark/Athena MERGE into Iceberg | Merge 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_owneron 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-10that 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 asLATE_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
- 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. - AWS DMS → Endpoints → Create endpoint → Source, engine Microsoft SQL Server, access via Secrets Manager, SSL mode verify-full where possible.
- 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. - 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.accountsanddbo.postings. - 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-latestCREATE 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
// 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)
-- 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
ALTER DATABASE ... SET RECOVERY FULL— with the simple recovery model, the log is truncated at checkpoints and CDC cannot rely on it.sys.sp_cdc_enable_db/sp_cdc_enable_table— create thecdcschema, change tables and Agent capture/cleanup jobs. On RDS usemsdb.dbo.rds_cdc_enable_dbfor the database step.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.sys.fn_cdc_increment_lsn(@lsn)— start just after the last processed LSN, so nothing is read twice.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).__$operationmapping — 1 delete, 2 insert, 4 update after-image; mapped to DMS-styleD/I/U.ORDER BY __$start_lsn, __$seqval— commit order, then order within the transaction.sys.fn_cdc_map_lsn_to_time— the commit time of the transaction; this is the ordering column for merges downstream.- 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.
row_number() OVER (PARTITION BY account_id ORDER BY commit_ts DESC)— keeps only the newest change per key in this batch.WHEN MATCHED AND s.op = 'D' THEN DELETE— a delete removes the row from the current state (the change history keeps the evidence).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.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
CDCLatencySourceandCDCLatencyTarget⚠ 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
Dexplicitly. - 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
| Symptom | Likely cause | Fix |
|---|---|---|
| Task fails: "database is not in full recovery model" or log not available | Recovery model or log backups not configured | Set full recovery, take a full backup, configure log backups/retention |
| No changes captured on RDS | CDC not enabled with rds_cdc_enable_db, or table not enabled | Run the RDS procedure; enable each table |
Target files lack the Op column for full-load rows | IncludeOpForFullLoad not set | Set it so full-load rows show I |
| Current-state table shows deleted accounts | Merge ignores D | Add WHEN MATCHED AND op = 'D' THEN DELETE |
| Balance goes backwards after a restart | Merge took the last processed file | Use the commit timestamp guard |
cdc.fn_cdc_get_all_changes_... error "insufficient number of arguments" or invalid range | LSN range outside retention or from > to | Start 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/ --recursiveExercises
- 🟢 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.csas JSON Lines withop,commit_tsand the row, then implement the merge in C# (an in-memory dictionary keyed byaccount_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
Expected output after the setup script: I for both accounts, U for the balance change, U for the closure, D for the deletion — in commit order. The second run starts from fn_cdc_increment_lsn(<saved>), which is greater than fn_cdc_get_max_lsn() when nothing changed, so it prints "No new changes". If the first run prints nothing, check that SQL Server Agent is running (MSSQL_AGENT_ENABLED=true): the capture job populates the change tables.
Keep Dictionary<string, (AccountRow Row, DateTime CommitTs)>. For each change: if the key exists and change.CommitTs <= existing.CommitTs, ignore it; else if op == "D", remove the key (and remember the delete's timestamp in a tombstone dictionary so an older insert cannot resurrect it); else upsert. Test: generate a sequence of changes, compute the expected final state, then apply a random permutation many times with a fixed seed and assert the same result. The tombstone is the subtle part — without it, I arriving after D out of order recreates the row.
The CDC position is lost, so restart with a new full load + CDC for the affected tables into a new prefix (for example cdc-reload-<date>/). For accounts (a state table), the reload replaces the current state: rebuild accounts_current from the full load, then apply CDC. For postings (an event table), de-duplicate on the posting id against what is already in the lake — the merge key, not the file, decides — so postings captured before the outage are not counted twice. Communicate the expected time to finish the full load; meanwhile give risk the last reconciled balances with a clear "as at" timestamp. Afterwards: increase log retention and alarm on replication latency well before it approaches retention.
Knowledge check
Answer: Copies all existing rows once, while caching changes that happen during the load, then applies ongoing changes from the transaction log continuously.
Answer: To order changes by when they were committed at the source, so merges are correct even when files are processed out of order or replayed.
Answer: MS-CDC. MS-Replication (transactional replication) requires a primary key.
Answer: Late-arriving (business time). It arrives in order; reports for that past date must be recomputed.
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
A company needs to replicate ongoing changes from an on-premises SQL Server database to Amazon S3 with minimal impact on the source and must be able to apply deletes downstream. Which solution meets these requirements?
A. A nightly SQL Agent job that exports full tables to CSV and uploads them with the AWS CLI. B. AWS DMS with a full-load-and-cdc task to an S3 target, including the operation column and a commit timestamp column. C. AWS Glue crawlers pointed at the SQL Server database every hour. D. AWS DataSync from the SQL Server data files.
Answer: B. Log-based CDC with operation and ordering columns. A misses intraday changes and deletes and loads the source heavily. C catalogues schemas, it does not capture changes. D copies files, not consistent database changes.
CDC files in S3 contain inserts, updates and deletes for an accounts table, sometimes delivered out of order after task restarts. Analysts need a table with the latest state of each account, queryable in Athena. What should the data engineer do?
A. Create an external CSV table over the CDC files and filter op <> 'D'.
B. Use an Apache Iceberg table and run MERGE INTO using the latest change per key ordered by the source commit timestamp, deleting on D.
C. Overwrite the table with the most recently arrived file each hour.
D. Use a Glue crawler with schema change detection.
Answer: B. Iceberg supports row-level MERGE; ordering by commit time handles out-of-order delivery. A keeps every historical version and old deleted rows. C loses data and ordering. D only updates the schema.
A bank wants to migrate a SQL Server database to Amazon RDS with minimal downtime. Which approach is most appropriate?
A. Take a backup, restore it on RDS, and accept the data written after the backup is lost. B. Use AWS DMS full load plus ongoing replication (CDC), then cut over when replication lag is near zero. C. Use S3 Transfer Acceleration to copy the .mdf files. D. Rebuild the schema manually and re-enter data.
Answer: B. Full load + CDC keeps the target in sync until cutover. A loses data. C copies files that RDS cannot attach directly this way. D is not realistic.
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.
Orchestration: Step Functions, EventBridge and MWAA
Run Harbourline's transaction pipeline as an event-driven Step Functions workflow — triggered by S3 through EventBridge, waiting on a Glue job, retrying safely and alerting on failure — and know when EventBridge Scheduler, Glue workflows or Apache Airflow (MWAA) fit better.
Validation and data quality: rules, quarantine and reconciliation
Build Harbourline's data-quality gate: row-level rules with reason codes in Python and C#, a Glue Data Quality (DQDL) ruleset, a quarantine zone with a reject report, control totals that prove nothing was lost, and an audit record for every run.