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.
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
- Secure external integrations for the partner connectivity this lesson assumes
- SWIFT-style file integration and Transfer Family
- ISO 20022 messages
- Idempotency in .NET
- Amazon Athena
- Tools: .NET 10 SDK or Docker for the matcher
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:
| Result | Meaning | Typical cause |
|---|---|---|
| Matched | Same reference, amount, currency | Healthy |
| Amount mismatch | Same reference, different value | Keying error, fee netting, FX rounding |
| Missing at partner | We booked it, they did not | Not submitted, rejected, still in flight |
| Missing internally | They have it, we do not | Unsolicited credit, our booking failed |
| Duplicate at partner | Same reference twice | File loaded twice, partner retry |
| Timing difference | Same item, different value date | Cut-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
| Need | Service | Honest limits |
|---|---|---|
| Receive partner files | AWS Transfer Family (SFTP) into S3 | Per-endpoint hourly cost makes it a poor fit for a one-hour lab ⚠ VERIFY pricing |
| Detect arrival | S3 events to EventBridge | At-least-once delivery: the same event can arrive twice |
| Orchestrate steps | Step Functions Standard | 256 KiB state limit: pass S3 paths |
| Bulk matching | Athena over Parquet, or a Glue job | Set-based, strong for exact and key-based joins; clumsy for tolerance logic |
| Rule-based matching | .NET on Lambda or Fargate | You own the code and its tests |
| File and break state | DynamoDB (idempotency, status) or Aurora (relational case management) | DynamoDB needs access patterns planned up front |
| Case work | Your own UI or a case-management product | AWS has no reconciliation service; this is a build or buy decision |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SSIS file load plus SQL stored procedures | Transfer Family, S3 events, Step Functions, Athena or Glue | Event-driven and serverless; no scheduled polling job |
| Azure Data Factory pipelines | Step Functions plus Glue | Workflow state is visible per execution |
SQL Server MERGE / full outer join matching | Athena SQL full outer join | Same SQL idea; storage is Parquet in S3 |
| Service Bus duplicate detection | DynamoDB conditional put on file key | You implement the "seen" check yourself |
| Dynamics or SharePoint case lists | Aurora plus an internal app | Break 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
| Section | Content |
|---|---|
| 1 Business | Reconcile 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 Functional | F1 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-functional | Daily 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
| Choice | Why | Rejected alternative | Trade-off accepted |
|---|---|---|---|
| SQL tier (Athena) then code tier (C#) | Cheap, fast bulk matching, then precise rules on the leftovers | All-in-code | Two places for logic; document which pass owns which rule |
DynamoDB recon_files for idempotency | Conditional put gives atomic "first time" check | Check S3 for existing object only | Another table; far safer under duplicate events |
| Aurora PostgreSQL for breaks | Relational queries, joins, ageing reports | DynamoDB | Needs a database in a VPC; better for analyst-style queries |
| Step Functions per file | Visible failures, retries, redrive | One long Lambda | Per-transition cost, negligible at daily scale |
| Raw landing kept unmodified | Evidence and re-run source | Parse and discard | Storage 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. EveryPOSTuses anIdempotency-Keyheader (idempotency keys). - Database.
| Table | Key | Purpose |
|---|---|---|
recon_files (DynamoDB) | fileKey (partner + business date + sequence) | One row per accepted file: sha256, line count, control total, status |
breaks (Aurora) | break_id | reference, type, amounts, first_seen, status, owner, reason_code |
break_events (Aurora, insert-only) | event_id | break_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
| Failure | Detection | Automatic response | Impact | Manual step |
|---|---|---|---|---|
| Same file delivered twice | fileKey exists in recon_files | Second run stops, logs duplicate | None | None |
| Same name, different content | Hash differs for existing fileKey | Reject, raise security alert | Day delayed | Contact partner, investigate |
| Control total does not match lines | Validation state fails | Quarantine file, do not match | Day delayed | Request re-send |
| File missing at 07:00 | Scheduled check finds no fileKey for date | Alert operations | Late position | Chase partner |
| Out-of-sequence file | Sequence gap check | Hold later file until gap filled | Partial recon | Request missing file |
| Matcher crashes midway | Workflow failure | Redrive from failed state; writes are idempotent by (date, reference) | None | None |
| Break table write throttled or DB failover | Retries, alarm | Retry with backoff | Delay | None unless prolonged |
| Wrong adjustment approved | Audit review | None: process control | Financial misstatement | Reverse by a new, approved adjustment |
Sections 12 to 15
- Scalability. Volume grows with transaction count. Parquet partitioned by
business_datekeeps 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.
| Area | Where it touches | Reviewing team | Evidence |
|---|---|---|---|
| Books and records, safeguarding ⚠ VERIFY | Daily position, retention | Finance control, compliance | Signed daily summaries |
| Write-off and adjustment authority | Maker/checker rules | Finance, internal control | Delegation matrix, audit events |
| Third-party risk | Partner file contract and SLA | Procurement, risk | Contract and SLA terms |
| Data protection | Counterparty data in files | Data protection officer | Retention 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.s3and detail typeObject 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-1Infrastructure as Code
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.
// 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.00The 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
Entryis arecordwithdecimal Amount. Records give value equality andwithcopies; money asdecimalavoids binary floating-point errors.Findis a local function that scans partner lines not yetused. Marking a line used when it is consumed is what guarantees a partner line matches at most once.- Pass 1 consumes exact matches. Everything that fails falls into
pass2. - Pass 2 matches on reference only. A hit here is not a clean match, so it becomes an
AmountMismatchbreak carrying both amounts in its note. - 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.
- 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.
- The summary line prints a net difference. A reconciliation that reports counts but not value hides the money; finance reads both.
- 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
doublefor 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
| Symptom | Likely cause | Fix |
|---|---|---|
| Every line shows as a break | Currency or reference formatting differs (case, padding) | Normalise both sides in the canonical schema step |
| Phantom breaks after a re-run | Non-idempotent break writes | Key breaks by (business_date, reference, type) and upsert |
ConditionalCheckFailedException in logs | Duplicate file event | Expected: treat as "already processed", not an error |
| Workflow hits the 256 KiB state limit | Data passed between states | Pass S3 paths, not lines |
| Athena full outer join gives inflated rows | Duplicate keys on one side | Group by key first, as in the SQL above |
| Files arrive but nothing starts | Bucket EventBridge notifications off, or rule pattern mismatch | Enable 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-1Exercises
- 🟢 Guided: run the matcher. Add a sixth ledger entry that the partner reports with currency
EURinstead ofGBPand 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_PARTNERwith 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
The new entry has the same reference as the partner line but a different currency. Pass 1 fails because currency is part of the key. Pass 2 matches on reference alone, so the result is AmountMismatch with note ... GBP vs ... EUR. Why: a currency difference is a value difference and must never be treated as a match.
Group ledger entries by BatchReference, sum them, and run the exact pass against the partner lines using the batch sum. It belongs before Pass 2, otherwise the partner line would be reported as an amount mismatch against one of the components. Record the component IDs on the matched pair so the audit trail shows the one-to-many relationship.
Hypotheses: (1) Friday's partner file was never ingested (check recon_files); (2) the file arrived but was quarantined by a control-total failure (check workflow executions); (3) value-date cut-off: the partner books Friday-evening items as Monday (compare value dates; widen tolerance only with finance agreement). Automatic improvement: a scheduled "expected file" check that alerts when no file exists for a business date, and a rule that holds the reconciliation as INCOMPLETE instead of creating hundreds of false breaks.
Knowledge check
Answer: To tell a harmless duplicate delivery (same name, same hash) from a changed file with the same name (different hash), which is a security or data-quality event.
Answer: Equal counts can hide wrong values. A trailer total that differs from the sum of parsed lines shows corruption or truncation.
Answer: An insert-only event table recording who changed which break from which status to which, with timestamps and maker/checker identities.
Answer: For bulk, key-based exact joins over large volumes. Code is better for tolerances, many-to-one logic and rules that need unit tests.
Answer: No. It is an amount-mismatch break. Treating it as matched would hide a real money difference.
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
An S3 event can be delivered more than once to a reconciliation workflow. Loading the same statement twice would double-count items. Which design prevents this?
A. Rely on S3 never sending duplicate events. B. Use a DynamoDB conditional write keyed by file identity before processing. C. Increase the Lambda timeout. D. Delete the file after the first read.
Answer: B. Event delivery is at-least-once, so the consumer must be idempotent. A is false, C does not address duplicates, D destroys evidence and does not stop concurrent duplicates.
A bank needs daily reconciliation of 5 million lines with complex tolerance rules for a small fraction. Which approach is most cost-effective?
A. Run all matching in one large EC2 instance. B. Use Athena or Glue for exact bulk matching, then a code-based matcher only on the unmatched remainder. C. Load everything into a provisioned Redshift cluster permanently. D. Reconcile manually in spreadsheets.
Answer: B. Tiered matching limits expensive logic to the small leftover set. A and C add always-on cost; D does not scale and lacks controls.
Partners upload statements over SFTP. Which control best limits a compromised partner credential?
A. A shared SFTP user for all partners. B. One Transfer Family user per partner with an IAM role scoped to that partner's prefix and SSH-key authentication. C. Public write access to the bucket with an obscure name. D. Passwords rotated annually.
Answer: B. Per-partner identity and prefix scoping limit blast radius. A, C and D increase exposure.
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-labremains
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.
Reporting and regulatory reporting architecture
Design a governed reporting pipeline on S3, Glue and Athena where every submitted figure can be reproduced, traced to its sources, approved by a second person and retained immutably, and verify that with a small C# manifest tool.
Secure external integrations
Design safe connections to partner APIs, webhooks and file exchanges using egress allow-lists, mutual TLS, signed webhooks and third-party risk controls, and verify signed webhooks with a runnable C# verifier.