Skip to Content
Cloud Engineering Academy
14. Advanced Banking Architecture

Reconciliation architecture

Design an automated reconciliation of Harbourline's internal ledger against partner and scheme files: idempotent file ingestion, control totals, a tiered matching engine, break handling with an audit trail, and a runnable C# matcher.

advanced⏱ 2 h 30 min🟢 FreeSAP-C03SAA-C03SCS-C03

Assumes: .NET 10 LTS (file-based apps) · AWS SDK for .NET v4 · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Explain what reconciliation proves, and why "both systems say success" is not the same as "the books agree".
  • Design file ingestion that is idempotent, checks control totals and rejects duplicate or out-of-sequence files.
  • Choose between set-based SQL matching and code-based matching, and combine them in tiers.
  • Model a break and its lifecycle, with maker/checker approval for adjustments and an audit trail.
  • Apply the 15-section template to a reconciliation workload, including failure scenarios.
  • Run and read a C# three-pass matcher that finds amount mismatches, missing items, timing differences and duplicates.

Prerequisites

Why this matters in banking

Every payment crosses at least two systems that each keep their own record: Harbourline's ledger and a settlement bank or payment scheme. Each system can be internally consistent and still disagree with the other. A customer's money is "sent" in our ledger but the scheme never received it. A scheme debits us twice because a file was loaded twice. A partner reports 205.50 where we recorded 250.50.

Reconciliation is the control that finds these differences every day, before a customer or an auditor finds them. A weak reconciliation produces a growing pile of unexplained items, then write-offs, then an uncomfortable conversation about safeguarding of customer funds ⚠ VERIFY which rules apply to your entity.

Educational content, not accounting or regulatory advice

Harbourline is fictional. Write-off thresholds, ageing limits, client-money handling and the books-and-records rules that govern reconciliations vary by entity and jurisdiction. Your finance control, compliance and internal audit teams own them ⚠ VERIFY locally. This lesson shows engineering patterns, not accounting policy.

The idea in plain language

Reconciliation compares two lists that should describe the same events and classifies every line:

ResultMeaningTypical cause
MatchedSame reference, amount, currencyHealthy
Amount mismatchSame reference, different valueKeying error, fee netting, FX rounding
Missing at partnerWe booked it, they did notNot submitted, rejected, still in flight
Missing internallyThey have it, we do notUnsolicited credit, our booking failed
Duplicate at partnerSame reference twiceFile loaded twice, partner retry
Timing differenceSame item, different value dateCut-off between systems

Anything other than "matched" is a break. A break is not an error message: it is a case with an owner, an age and a resolution.

Analogy: checking your bank statement against your own receipts. Most lines tick off. The ones that do not are the interesting ones: a receipt with no statement line (maybe it has not cleared), a statement line with no receipt (maybe fraud), and a line with the wrong amount (maybe a typo or a fee).

Why a design is needed, not just a script

At small scale a script works. At bank scale five things appear: files arrive late, twice or out of order; volumes reach millions of lines; rules need tolerances and many-to-one matching; people must work breaks with approvals; and an auditor will ask who wrote off what, when and why. Each is an architecture concern.

What problem the AWS services solve

NeedServiceHonest limits
Receive partner filesAWS Transfer Family (SFTP) into S3Per-endpoint hourly cost makes it a poor fit for a one-hour lab ⚠ VERIFY pricing
Detect arrivalS3 events to EventBridgeAt-least-once delivery: the same event can arrive twice
Orchestrate stepsStep Functions Standard256 KiB state limit: pass S3 paths
Bulk matchingAthena over Parquet, or a Glue jobSet-based, strong for exact and key-based joins; clumsy for tolerance logic
Rule-based matching.NET on Lambda or FargateYou own the code and its tests
File and break stateDynamoDB (idempotency, status) or Aurora (relational case management)DynamoDB needs access patterns planned up front
Case workYour own UI or a case-management productAWS has no reconciliation service; this is a build or buy decision

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SSIS file load plus SQL stored proceduresTransfer Family, S3 events, Step Functions, Athena or GlueEvent-driven and serverless; no scheduled polling job
Azure Data Factory pipelinesStep Functions plus GlueWorkflow state is visible per execution
SQL Server MERGE / full outer join matchingAthena SQL full outer joinSame SQL idea; storage is Parquet in S3
Service Bus duplicate detectionDynamoDB conditional put on file keyYou implement the "seen" check yourself
Dynamics or SharePoint case listsAurora plus an internal appBreak tables need audit columns and approvals designed in

Architecture

Security-boundary view

Files cross two boundaries: partner to edge, and edge to core. Nothing from the partner is trusted until validated, and the raw landing file is kept unmodified as evidence.

The 15-section design

Sections 1 to 3: requirements

SectionContent
1 BusinessReconcile the ledger against the settlement-bank statement and the scheme settlement report daily; resolve or escalate every break within defined ageing; give finance a signed daily position
2 FunctionalF1 Ingest files once. F2 Validate header and control totals. F3 Normalise to a canonical schema. F4 Match in passes. F5 Create breaks with reason codes. F6 Work breaks with maker/checker. F7 Produce a daily summary. F8 Re-run for a date without duplicates
3 Non-functionalDaily result by 08:00 for the previous day. 5 million lines processed in under 30 minutes. File ingestion exactly-once in effect. Evidence retained 7 years ⚠ VERIFY. Every break status change audited. Recovery: re-process any date from the raw landing file

Section 5: service selection

ChoiceWhyRejected alternativeTrade-off accepted
SQL tier (Athena) then code tier (C#)Cheap, fast bulk matching, then precise rules on the leftoversAll-in-codeTwo places for logic; document which pass owns which rule
DynamoDB recon_files for idempotencyConditional put gives atomic "first time" checkCheck S3 for existing object onlyAnother table; far safer under duplicate events
Aurora PostgreSQL for breaksRelational queries, joins, ageing reportsDynamoDBNeeds a database in a VPC; better for analyst-style queries
Step Functions per fileVisible failures, retries, redriveOne long LambdaPer-transition cost, negligible at daily scale
Raw landing kept unmodifiedEvidence and re-run sourceParse and discardStorage cost, small

Sections 6 and 7: network and security

  • Network. Partner SFTP uses a VPC-hosted Transfer Family endpoint with an allow-listed security group, or a public endpoint with IP allow-listing ⚠ VERIFY your bank's rule. Core processing has no internet path; use S3 and DynamoDB gateway endpoints (VPC endpoints).
  • Identity. One Transfer Family user per partner, each with a role scoped to that partner's prefix, SSH public key only, no passwords.
  • Data protection. KMS-encrypted buckets. Files contain account and counterparty data, so classify them as confidential.
  • Integrity. Record the SHA-256 of every raw file in recon_files. A changed file with the same name is a security event, not a retry.
  • Adjustments. The person proposing an adjustment cannot approve it. Enforce in the application, log both identities.

Sections 8 to 10: API, data and flow

  • API. Internal only: GET /breaks?status=open&ageDays=gt:2, POST /breaks/{id}/proposals, POST /breaks/{id}/approvals. Every POST uses an Idempotency-Key header (idempotency keys).
  • Database.
TableKeyPurpose
recon_files (DynamoDB)fileKey (partner + business date + sequence)One row per accepted file: sha256, line count, control total, status
breaks (Aurora)break_idreference, type, amounts, first_seen, status, owner, reason_code
break_events (Aurora, insert-only)event_idbreak_id, actor, action, from_status, to_status, at, comment
  • Data flow. (1) File lands. (2) Event reaches the queue. (3) Workflow computes hash, conditional-puts fileKey; if it already exists, stop as duplicate. (4) Validate header, trailer and control totals. (5) Normalise. (6) Match. (7) Write breaks and events. (8) Summary.

Section 11: failure scenarios

FailureDetectionAutomatic responseImpactManual step
Same file delivered twicefileKey exists in recon_filesSecond run stops, logs duplicateNoneNone
Same name, different contentHash differs for existing fileKeyReject, raise security alertDay delayedContact partner, investigate
Control total does not match linesValidation state failsQuarantine file, do not matchDay delayedRequest re-send
File missing at 07:00Scheduled check finds no fileKey for dateAlert operationsLate positionChase partner
Out-of-sequence fileSequence gap checkHold later file until gap filledPartial reconRequest missing file
Matcher crashes midwayWorkflow failureRedrive from failed state; writes are idempotent by (date, reference)NoneNone
Break table write throttled or DB failoverRetries, alarmRetry with backoffDelayNone unless prolonged
Wrong adjustment approvedAudit reviewNone: process controlFinancial misstatementReverse by a new, approved adjustment

Sections 12 to 15

  • Scalability. Volume grows with transaction count. Parquet partitioned by business_date keeps scans small. The 10x case adds Glue or Athena concurrency and moves the code tier to batches (for example Step Functions Distributed Map over file chunks ⚠ VERIFY limits).
  • Cost. Transfer Family endpoint hours dominate small estates; Athena scans and Aurora instance hours follow. State assumptions: files per day, lines per file, scan volume. Estimate with the AWS Pricing Calculator ⚠ VERIFY.
  • Implementation. Phase 1 ingestion and hashing. Phase 2 one partner end to end with synthetic files. Phase 3 matching with golden test files. Phase 4 case workflow. Phase 5 parallel run against the legacy process.
  • Compliance touchpoints.
AreaWhere it touchesReviewing teamEvidence
Books and records, safeguarding ⚠ VERIFYDaily position, retentionFinance control, complianceSigned daily summaries
Write-off and adjustment authorityMaker/checker rulesFinance, internal controlDelegation matrix, audit events
Third-party riskPartner file contract and SLAProcurement, riskContract and SLA terms
Data protectionCounterparty data in filesData protection officerRetention and access review

Step-by-step implementation

Define the canonical schema first

Pick one shape every partner file maps to: source, business_date, reference, amount (decimal), currency, value_date, direction, partner_line_id. Matching code never sees partner-specific formats.

Make ingestion idempotent

Compute the SHA-256 of the raw file. Put (fileKey, sha256) with a condition that fileKey does not exist. If the put fails, read the stored hash: equal means duplicate (ignore), different means tampering or a mislabeled file (alert).

Check header and trailer

Parse the header business date and the trailer count and control total, then compare with what you actually parsed. Reject the whole file on any mismatch. Never "fix" a partner file.

Match in passes, cheapest first

Pass 1 exact on reference, amount and currency (SQL). Pass 2 same reference, different amount (SQL or code). Pass 3 no reference: amount, currency and date window (code). Record which pass matched each pair; auditors ask.

Persist breaks with reason codes and events

Every status change inserts a row in break_events. Updating the break row alone loses history.

Prove it with golden files

Build small test files with known answers (the sample in this lesson) and keep them in the repository. A rule change that alters a golden result needs a deliberate update and a reviewer.

AWS Console instructions

  • Console → S3 → your landing bucket → Properties → Amazon EventBridge → Edit → On. This sends object events to the default event bus.
  • Console → EventBridge → Rules → Create rule, pattern with source aws.s3 and detail type Object Created, target an SQS queue.
  • Console → DynamoDB → Tables → Create table with partition key fileKey (String) and Point-in-time recovery on.
  • Console → Step Functions → State machines, open an execution to see exactly which state failed on a bad file.

AWS CLI

# Turn on EventBridge notifications for the landing bucket.
aws s3api put-bucket-notification-configuration --bucket hl-m14-recon-landing \
  --notification-configuration '{"EventBridgeConfiguration":{}}' \
  --profile learn-sandbox --region us-east-1

# Claim a file exactly once: fails with ConditionalCheckFailedException if already claimed.
aws dynamodb put-item --table-name hl-m14-recon-files \
  --item '{"fileKey":{"S":"SCHEME1#2026-11-17#0042"},"sha256":{"S":"abc123"},"status":{"S":"ACCEPTED"}}' \
  --condition-expression "attribute_not_exists(fileKey)" \
  --profile learn-sandbox --region us-east-1

# Find open breaks older than two days (Athena over an exported breaks table).
aws athena start-query-execution --work-group primary \
  --query-string "SELECT break_id, type, first_seen FROM recon.breaks WHERE status = 'OPEN' AND first_seen < date_add('day', -2, current_date)" \
  --profile learn-sandbox --region us-east-1

Infrastructure as Code

ReconciliationIngestStack.cs
using Amazon.CDK;
using Amazon.CDK.AWS.DynamoDB;
using Amazon.CDK.AWS.Events;
using Amazon.CDK.AWS.Events.Targets;
using Amazon.CDK.AWS.S3;
using Amazon.CDK.AWS.SQS;
using Constructs;
using DdbAttribute = Amazon.CDK.AWS.DynamoDB.Attribute;

namespace Harbourline.Architecture.Reconciliation;

public class ReconciliationIngestStack : Stack
{
    public ReconciliationIngestStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
    {
        var landing = new Bucket(this, "Landing", new BucketProps
        {
            Encryption = BucketEncryption.S3_MANAGED,
            BlockPublicAccess = BlockPublicAccess.BLOCK_ALL,
            EnforceSSL = true,
            Versioned = true,
            EventBridgeEnabled = true,
            RemovalPolicy = RemovalPolicy.DESTROY,
            AutoDeleteObjects = true,   // learning shortcut: production keeps raw files
        });

        new Table(this, "ReconFiles", new TableProps
        {
            PartitionKey = new DdbAttribute { Name = "fileKey", Type = AttributeType.STRING },
            BillingMode = BillingMode.PAY_PER_REQUEST,
            PointInTimeRecoverySpecification = new PointInTimeRecoverySpecification { PointInTimeRecoveryEnabled = true },
            RemovalPolicy = RemovalPolicy.DESTROY,
        });

        var dlq = new Queue(this, "FileEventsDlq", new QueueProps
        {
            Encryption = QueueEncryption.SQS_MANAGED,
            EnforceSSL = true,
            RetentionPeriod = Duration.Days(14),
        });
        var queue = new Queue(this, "FileEvents", new QueueProps
        {
            Encryption = QueueEncryption.SQS_MANAGED,
            EnforceSSL = true,
            VisibilityTimeout = Duration.Minutes(15),
            DeadLetterQueue = new DeadLetterQueue { MaxReceiveCount = 5, Queue = dlq },
        });

        new Rule(this, "FileArrived", new RuleProps
        {
            EventPattern = new EventPattern
            {
                Source = ["aws.s3"],
                DetailType = ["Object Created"],
                Detail = new Dictionary<string, object>
                {
                    ["bucket"] = new Dictionary<string, object> { ["name"] = new[] { landing.BucketName } },
                },
            },
            Targets = [new SqsQueue(queue)],
        });

        Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
        Amazon.CDK.Tags.Of(this).Add("Lab", "m14-l10");
        Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
        Amazon.CDK.Tags.Of(this).Add("Owner", "learner");
    }
}

DdbAttribute is an alias because Attribute alone collides with System.Attribute.

Complete code

The matcher below implements the three passes from the design. It uses decimal for money, never double, and is deterministic: the same inputs always give the same breaks.

Reconcile.cs
// Run with: dotnet run Reconcile.cs
// Three-pass reconciliation of our ledger against a partner statement.
var ours = new List<Entry>
{
    new("T1", "FP-1001", 100.00m, "GBP", new DateOnly(2026, 11, 2)),
    new("T2", "FP-1002", 250.50m, "GBP", new DateOnly(2026, 11, 2)),
    new("T3", "FP-1003", 75.00m,  "GBP", new DateOnly(2026, 11, 2)),
    new("T4", "FP-1004", 10.00m,  "GBP", new DateOnly(2026, 11, 3)),
    new("T5", "FP-1005", 5.00m,   "GBP", new DateOnly(2026, 11, 3)),
};
var theirs = new List<Entry>
{
    new("S1", "FP-1001", 100.00m, "GBP", new DateOnly(2026, 11, 2)),
    new("S2", "FP-1002", 205.50m, "GBP", new DateOnly(2026, 11, 2)),   // digits swapped
    new("S3", "",        10.00m,  "GBP", new DateOnly(2026, 11, 4)),   // no reference, a day later
    new("S4", "FP-1001", 100.00m, "GBP", new DateOnly(2026, 11, 2)),   // duplicate line
    new("S5", "FP-9999", 42.00m,  "GBP", new DateOnly(2026, 11, 3)),   // we have no record
};

var result = Reconciler.Run(ours, theirs, dateToleranceDays: 1);
Console.WriteLine($"Matched: {result.Matched}");
foreach (var b in result.Breaks)
    Console.WriteLine($"BREAK {b.Type,-18} ours={b.Ours?.Id ?? "-"} theirs={b.Theirs?.Id ?? "-"}  {b.Note}");
Console.WriteLine($"Net difference (ours - theirs): {ours.Sum(e => e.Amount) - theirs.Sum(e => e.Amount):0.00}");
return result.Breaks.Count == 5 ? 0 : 1;

public enum BreakType { AmountMismatch, MissingAtPartner, MissingInternal, DuplicateAtPartner }

public sealed record Entry(string Id, string Reference, decimal Amount, string Currency, DateOnly Date);

public sealed record Break(BreakType Type, Entry? Ours, Entry? Theirs, string Note);

public sealed record ReconResult(int Matched, IReadOnlyList<Break> Breaks);

public static class Reconciler
{
    public static ReconResult Run(IEnumerable<Entry> ours, IEnumerable<Entry> theirs, int dateToleranceDays)
    {
        var ourList = ours.ToList();
        var theirList = theirs.ToList();
        var used = new bool[theirList.Count];
        var breaks = new List<Break>();
        var matched = 0;

        int Find(Func<Entry, bool> predicate)
        {
            for (var i = 0; i < theirList.Count; i++)
                if (!used[i] && predicate(theirList[i])) return i;
            return -1;
        }

        // Pass 1: exact match on reference, amount and currency.
        var pass2 = new List<Entry>();
        foreach (var o in ourList)
        {
            var i = Find(t => t.Reference == o.Reference && t.Amount == o.Amount && t.Currency == o.Currency);
            if (i >= 0) { used[i] = true; matched++; } else pass2.Add(o);
        }

        // Pass 2: same reference but different amount or currency is a break to investigate.
        var pass3 = new List<Entry>();
        foreach (var o in pass2)
        {
            var i = Find(t => t.Reference == o.Reference && o.Reference.Length > 0);
            if (i >= 0)
            {
                used[i] = true;
                breaks.Add(new Break(BreakType.AmountMismatch, o, theirList[i],
                    $"{o.Amount:0.00} {o.Currency} vs {theirList[i].Amount:0.00} {theirList[i].Currency}"));
            }
            else pass3.Add(o);
        }

        // Pass 3: partner line without a reference: match on amount, currency and a date window.
        foreach (var o in pass3)
        {
            var i = Find(t => t.Reference.Length == 0 && t.Amount == o.Amount && t.Currency == o.Currency
                              && Math.Abs(t.Date.DayNumber - o.Date.DayNumber) <= dateToleranceDays);
            if (i >= 0) { used[i] = true; matched++; }
            else breaks.Add(new Break(BreakType.MissingAtPartner, o, null, "no partner line"));
        }

        // Whatever the partner sent that we never consumed.
        var ourReferences = ourList.Select(o => o.Reference).ToHashSet();
        for (var i = 0; i < theirList.Count; i++)
        {
            if (used[i]) continue;
            var t = theirList[i];
            breaks.Add(ourReferences.Contains(t.Reference)
                ? new Break(BreakType.DuplicateAtPartner, null, t, "reference already matched")
                : new Break(BreakType.MissingInternal, null, t, "we have no record"));
        }
        return new ReconResult(matched, breaks);
    }
}

Expected output:

Matched: 2
BREAK AmountMismatch     ours=T2 theirs=S2  250.50 GBP vs 205.50 GBP
BREAK MissingAtPartner   ours=T3 theirs=-  no partner line
BREAK MissingAtPartner   ours=T5 theirs=-  no partner line
BREAK DuplicateAtPartner ours=- theirs=S4  reference already matched
BREAK MissingInternal    ours=- theirs=S5  we have no record
Net difference (ours - theirs): -17.00

The SQL tier uses the same classification. De-duplicate each side first so repeated partner lines do not multiply rows.

-- Pass 1 and the "missing" classes in set-based form. Replace table names with your own.
WITH l AS (
  SELECT reference, currency, sum(amount) AS amount, count(*) AS n
  FROM recon.ledger WHERE business_date = DATE '2026-11-17' GROUP BY 1, 2
), p AS (
  SELECT reference, currency, sum(amount) AS amount, count(*) AS n
  FROM recon.partner WHERE business_date = DATE '2026-11-17' GROUP BY 1, 2
)
SELECT coalesce(l.reference, p.reference) AS reference,
       l.amount AS our_amount, p.amount AS partner_amount, p.n AS partner_lines,
       CASE WHEN l.reference IS NULL THEN 'MISSING_INTERNAL'
            WHEN p.reference IS NULL THEN 'MISSING_AT_PARTNER'
            WHEN p.n > l.n          THEN 'DUPLICATE_AT_PARTNER'
            WHEN l.amount <> p.amount THEN 'AMOUNT_MISMATCH'
            ELSE 'MATCHED' END AS result
FROM l FULL OUTER JOIN p ON l.reference = p.reference AND l.currency = p.currency;

Code explained line by line

  1. Entry is a record with decimal Amount. Records give value equality and with copies; money as decimal avoids binary floating-point errors.
  2. Find is a local function that scans partner lines not yet used. Marking a line used when it is consumed is what guarantees a partner line matches at most once.
  3. Pass 1 consumes exact matches. Everything that fails falls into pass2.
  4. Pass 2 matches on reference only. A hit here is not a clean match, so it becomes an AmountMismatch break carrying both amounts in its note.
  5. Pass 3 handles partner lines with a blank reference and tolerates a date difference. The tolerance is a parameter, because it is a business rule, not a constant.
  6. Leftover partner lines are split by one test: if their reference is one we already know, it is a duplicate; otherwise it is missing internally.
  7. The summary line prints a net difference. A reconciliation that reports counts but not value hides the money; finance reads both.
  8. In production, the matcher also records which pass produced each pair, writes breaks idempotently keyed by (business_date, reference), and never throws away the unmatched list.

Security considerations

⚠ Learning shortcut — not for production

The lab lands files in a bucket with AutoDeleteObjects and uses plain S3-managed encryption. Production keeps raw landing files immutable for the retention period (Object Lock, see regulatory reporting), encrypts with a customer-managed KMS key, scopes each partner to its own prefix, and never auto-deletes evidence.

  • Treat partner files as untrusted input. Limit size, validate encoding, and parse with a strict parser. A malformed file should be quarantined, not partially loaded.
  • No silent fixes. Never edit a partner file to make totals agree. The raw file and the discrepancy are the evidence.
  • Segregate duties. Whoever proposes an adjustment cannot approve it; enforce and log both identities.
  • Least privilege. The workflow role can read landing and write curated and the break tables; it cannot read other partners' prefixes or delete landing objects.
  • Log access to break data. Breaks reveal counterparty and customer details; see audit and compliance architecture.

Banking use case

Harbourline's FPS settlement bank delivers a daily statement at 06:00 and the scheme provides a settlement report at 06:30. The workflow reconciles the ledger to both. On 17 November the statement shows 205.50 where the ledger shows 250.50. The amount-mismatch break is created automatically, assigned to the payments-operations queue, and linked to the payment record. The analyst checks the original instruction, proposes a correction, and the team lead approves it. The break_events table now shows who found it, who proposed, who approved and when. The next day's summary shows the break closed with reason code PARTNER_KEYING_ERROR.

Common mistakes

  • Loading the same file twice because the event arrived twice. Make ingestion idempotent by file key and hash.
  • Counting instead of summing. Equal counts with different totals still means money is wrong. Check both.
  • Using double for amounts. Rounding differences create phantom breaks.
  • Updating breaks in place with no event history. You lose who did what.
  • Writing off to clear the queue. Ageing pressure leads to this; approval limits and review reports are the control.
  • Matching on amount alone with no date or reference. It creates false matches that hide real breaks.

Troubleshooting

SymptomLikely causeFix
Every line shows as a breakCurrency or reference formatting differs (case, padding)Normalise both sides in the canonical schema step
Phantom breaks after a re-runNon-idempotent break writesKey breaks by (business_date, reference, type) and upsert
ConditionalCheckFailedException in logsDuplicate file eventExpected: treat as "already processed", not an error
Workflow hits the 256 KiB state limitData passed between statesPass S3 paths, not lines
Athena full outer join gives inflated rowsDuplicate keys on one sideGroup by key first, as in the SQL above
Files arrive but nothing startsBucket EventBridge notifications off, or rule pattern mismatchEnable the setting; test the rule with a sample event

Cost and cleanup

Running the C# matcher is free. The optional CDK or Terraform deploy creates a bucket, a DynamoDB table (on-demand), an SQS queue and an EventBridge rule: pennies if destroyed the same day. A Transfer Family endpoint is not created in this lab because it bills hourly while it exists ⚠ VERIFY.

# CDK
cdk destroy --profile learn-sandbox
# Terraform
terraform destroy

# Confirm nothing tagged for the course remains.
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
  --profile learn-sandbox --region us-east-1

Exercises

  • 🟢 Guided: run the matcher. Add a sixth ledger entry that the partner reports with currency EUR instead of GBP and predict the break type before running.
  • 🟡 Challenge: add many-to-one matching: one partner line that equals the sum of two ledger entries sharing a batch reference. State which pass it belongs in and why.
  • 🔴 Live problem: operations reports 900 open breaks after a weekend. All are MISSING_AT_PARTNER with value dates on Friday. Work out three hypotheses, what you would query to separate them, and what the design should do automatically next time.

Assignment

Create architecture/reconciliation/ in your portfolio repository with the 15-section document, a break lifecycle state diagram, the file-ingestion idempotency design, a failure table and a golden-file test suite for the matcher using xUnit v3.

Solutions

Knowledge check

Interview questions

  • How do you make file-based reconciliation safe against duplicate delivery? Conditional write of a file key with a content hash before processing; duplicates stop, changed content alerts.
  • What do you do when control totals disagree? Quarantine the whole file, keep it as evidence, request a re-send, and do not partially load.
  • How do you stop write-offs from becoming routine? Approval limits, maker/checker, ageing reports to management and review by internal control.
  • Why keep the raw file? It is the evidence and the source for any re-run.
  • How would you scale to 100 million lines? Partitioned Parquet, set-based matching in Athena or Glue for the bulk, and code only for leftovers, chunked with Distributed Map ⚠ VERIFY limits.

Certification practice

Completion checklist

  • I can list the break types and their usual causes
  • I ran the matcher and explained each break
  • I designed idempotent ingestion with a file hash
  • My break model has an insert-only event history
  • I ran cleanup and confirmed nothing tagged Project=cea-lab remains

Key takeaways

  • Reconciliation produces cases (breaks) with owners and ageing, not just a pass/fail.
  • Ingest each file once, verify its hash and control totals, keep the raw file.
  • Match cheaply in bulk first, then apply precise code rules to the remainder.
  • Every break status change is an immutable event with maker/checker.
  • Accounting, safeguarding and write-off rules need finance and compliance review.

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