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

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.

intermediate⏱ 1 h 50 min🟢 FreeDEA-C01

Assumes: .NET 10 LTS · Python 3.11 / PySpark 3.5 · AWS Glue 5.0 Data Quality (DQDL) · xUnit v3 4.0.3 · pytest 8.4.2 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Classify data-quality problems (schema, completeness, validity, uniqueness, consistency, timeliness) and decide for each whether to reject, repair or warn.
  • Write row-level validation rules that emit reason codes, and keep a Python and a C# implementation in step by grading both against the same answer key.
  • Write a Glue Data Quality ruleset in DQDL, evaluate it in a Glue job, and split passing and failing rows.
  • Design a quarantine zone and a reject report that operations can act on.
  • Prove completeness with control totals and reconciliation, and record each run in an audit trail.

Prerequisites

Why this matters in banking

A partner payroll file arrives with 40 salary payments. Line 17 has an IBAN with a typo in the check digits. Line 23 says 1,433.12 with a thousands separator. Line 31 is a copy of line 30. If the pipeline loads the file as is, a salary goes to a non-existent account, an amount becomes 1 or NULL, and a person is paid twice. If the pipeline rejects the whole file, 39 people are not paid on time.

Banks need a third path: accept what is valid, quarantine what is not with a precise reason, report the rejects to someone who can fix them, and prove — with numbers — that every row and every penny is accounted for. That proof is what auditors and finance mean by reconciliation.

The idea in plain language

Data quality is "is this data fit for its purpose?". In practice it is a list of rules and what to do when a rule fails:

DimensionExample rule (transactions)Typical action
SchemaHeader has the 15 expected columnsReject the file
Completenesstransaction_id, account_id, idempotency_key not emptyReject the row
Validitybooking_date is a real ISO date; currency in GBP/EUR; IBAN passes mod-97Reject the row
Consistencytransfer_in amounts positive; card payments negativeReject the row
UniquenessNo repeated transaction_id or idempotency_key in a fileReject the duplicate
Repairable formatUTF-8 text read as Latin-1 (Café)Repair, keep a warning
TimelinessCreated more than a day after the value dateAccept, warn (LATE_ARRIVING)

A reason code is a short, stable code (INVALID_IBAN, DUPLICATE) attached to every rejected row. Codes can be counted, alerted on, trended and translated into partner-facing messages; free-text error messages cannot.

Control totals are numbers both sides agree on: row counts and sums per currency. If the sender says "1,036 rows, £97,784.91" and you have 969 accepted plus 67 quarantined rows whose amounts add up to the same total, nothing was lost. If not, you have a break.

Analogy: a bank's cash-in-transit check. Every bag is counted at the branch and again at the centre. Bags with torn seals go to a separate cage (quarantine) with a form saying why, but they are still counted. The centre signs off only when "good bags + caged bags = bags sent".

What problem the AWS service solves

AWS Glue Data Quality evaluates rules written in DQDL (Data Quality Definition Language) against Glue Data Catalog tables or inside Glue ETL jobs. It can:

  • compute a score and per-rule outcomes, and publish them to the Glue console and CloudWatch metrics;
  • in ETL jobs, return row-level outcomes: each row gets the lists of rules it passed and failed, so you can route failures to quarantine;
  • recommend a starter ruleset from data statistics, and detect anomalies over time with analyzers.

It is built on the open-source library Deequ (Amazon's "unit tests for data" on Spark). Its limits: DQDL rules are generic — it cannot express an IBAN mod-97 check or "duplicate by idempotency key within this file but not across files" without custom SQL, and custom logic is harder to unit test than code. That is why this module uses two lines of defence: tested code for business rules with reason codes, and DQDL as an independent check on the output.

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SSIS conditional split + error outputGlue job splitting rows by rule outcomeRow-level outcomes come from EvaluateDataQuality or your own code
ADF data flow assert transformationGlue Data Quality rules in an ETL jobDQDL is a declarative rule language
Microsoft Purview data qualityGlue Data Quality (catalog rulesets, scores) + SageMaker Catalog / DataZone quality display ⚠ VERIFY integration namingRulesets attach to Glue tables
Great Expectations / custom C# validatorsDeequ, PyDeequ, or your own codeSame concepts: checks, constraints, metrics
SQL Server CHECK constraintsValidation in the pipelineThe lake has no constraints: files accept anything

Architecture

Security-boundary view. The quarantine bucket holds raw values (including clear IBANs) because operators need them to fix and replay rows. It is therefore protected like the raw zone: separate bucket, KMS, versioning, access only for the pipeline role and a small operations group, short retention. Curated never contains the clear IBAN.

The rules in code

The lab's rules live in labs/m07-transaction-pipeline/glue/src/harbourline_etl/transactions.py, with an identical C# implementation in src/Harbourline.DataPipeline.Core/Validation/TransactionValidator.cs. Both are graded against transactions_messy_defects.csv, the generator's answer key.

Reason codeSeverityRule
SCHEMA_COLUMN_COUNTrejectRow does not have 15 fields
MISSING_REQUIREDrejecttransaction_id, account_id, idempotency_key, type, channel, status present
INVALID_IDrejecttransaction_id is a lower-case UUID; account_id is ACC- + 10 digits
DUPLICATErejecttransaction_id or idempotency_key seen earlier in the file
INVALID_DATErejectbooking_date, value_date match yyyy-MM-dd and are real dates
INVALID_TIMESTAMPrejectcreated_at is yyyy-MM-ddTHH:mm:ssZ and real
INVALID_AMOUNTrejectDecimal with up to 2 places that fits DECIMAL(18,2)
INVALID_CURRENCYrejectGBP or EUR exactly
INVALID_CODErejecttype, channel, status in their enums; MCC is 4 digits
AMOUNT_SIGNrejecttransfer_in > 0; debit types < 0
INVALID_IBANrejectCounterparty IBAN passes ISO 13616 mod-97 when present
ENCODING_INVALIDrejectText looks double-encoded and cannot be repaired
ENCODING_REPAIREDwarnA Latin-1 line or mojibake was repaired
LATE_ARRIVINGwarncreated_at more than 1 day after value_date

Notice the choices: duplicates are rejected (paying twice is worse than paying late), encoding is repaired (the data is right, only the bytes are wrong), late data is accepted (it is real, but reports for past dates must be re-run).

Step-by-step implementation

Grade the rules against the answer key (free)

Run the pytest suite and the xUnit suite. The key test asserts that every injected defect is found and that no clean row is flagged.

Read the reject report

hl-pipeline validate data/samples/transactions_messy.csv --quarantine rejects.csv prints counts by reason code and writes one line per rejected row.

Write the DQDL ruleset

glue/dq/transactions_raw.dqdl checks completeness, uniqueness, formats and allowed values on raw strings. It is the Spark-native gate used in Lab 7.4.

Evaluate in Glue and split rows

In the job, EvaluateDataQuality().process_rows(...) returns ruleOutcomes and rowLevelOutcomes; rows with DataQualityEvaluationResult = 'Passed' go to curated, others to quarantine with the failed rule names.

Reconcile and audit

Compute counts and sums per currency for accepted and quarantined rows, compare with the sender's control figures, write gold.reconciliation, and write the audit record last.

AWS Console instructions

  1. AWS Glue → Data Catalog → Tables → hl_m07_curated.transactions → Data quality → Create data quality rules. Choose Recommend rules to see what Glue proposes from the data, then compare with your ruleset; paste your DQDL and Save ruleset.
  2. Run the ruleset against the table with the lab's IAM role. Open the run to see each rule's outcome and the overall score.
  3. In Glue Studio, a visual job can add the Evaluate Data Quality transform; under its options choose to output original data with row-level results to route failures. The lab's script does the same in code.
  4. CloudWatch → Metrics → Glue Data Quality to chart pass rates over time; create an alarm when the rule pass rate drops below 100%.

AWS CLI

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

# Create a ruleset attached to the curated table (a compact version of the job's curated checks).
aws glue create-data-quality-ruleset --name hl-m07-curated-transactions \
  --ruleset 'Rules = [ RowCount > 0, IsComplete "transaction_id", IsUnique "transaction_id", ColumnValues "currency" in ["GBP","EUR"] ]' \
  --target-table TableName=transactions,DatabaseName=hl_m07_curated \
  --tags Project=cea-lab,Lab=m07-l4,Environment=learn,Owner=learner

# Run it against the catalog table.
RUN=$(aws glue start-data-quality-ruleset-evaluation-run \
  --data-source '{"GlueTable":{"DatabaseName":"hl_m07_curated","TableName":"transactions"}}' \
  --role arn:aws:iam::111122223333:role/hl-m07-glue-job-role \
  --ruleset-names hl-m07-curated-transactions --query RunId --output text)

aws glue get-data-quality-ruleset-evaluation-run --run-id "$RUN" --query '{Status:Status,Results:ResultIds}'
aws glue get-data-quality-result --result-id <result-id> --query 'RuleResults[].{Rule:Name,Result:Result}'

Infrastructure as Code

using Amazon.CDK.AWS.Glue;

_ = new CfnDataQualityRuleset(this, "CuratedRuleset", new CfnDataQualityRulesetProps
{
    Name = "hl-m07-curated-transactions",
    Description = "Second-line checks on curated transactions",
    Ruleset = File.ReadAllText(Path.Combine(labRoot, "glue", "dq", "transactions_raw.dqdl")),
    TargetTable = new CfnDataQualityRuleset.DataQualityTargetTableProperty
    {
        DatabaseName = "hl_m07_curated",
        TableName = "transactions",
    },
});

Complete code

The DQDL ruleset

glue/dq/transactions_raw.dqdl
Rules = [
    RowCount > 0,
    IsComplete "transaction_id",
    IsComplete "account_id",
    IsComplete "idempotency_key",
    IsUnique "transaction_id",
    ColumnValues "account_id" matches "ACC-[0-9]{10}",
    ColumnValues "booking_date" matches "[0-9]{4}-[0-9]{2}-[0-9]{2}",
    ColumnValues "value_date" matches "[0-9]{4}-[0-9]{2}-[0-9]{2}",
    ColumnValues "created_at" matches "[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}Z",
    ColumnValues "amount" matches "-?[0-9]{1,16}(\.[0-9]{1,2})?",
    ColumnValues "currency" in ["GBP", "EUR"],
    ColumnValues "type" in ["card_payment", "transfer_in", "transfer_out", "direct_debit", "standing_order", "atm_withdrawal", "fee", "interest"],
    ColumnValues "channel" in ["mobile", "web", "branch", "card", "api", "batch"],
    ColumnValues "status" in ["booked", "pending", "reversed", "rejected"]
]

Splitting rows by DQ outcome in a Glue job (from glue/jobs/transactions_dq_bookmarked.py)

results = EvaluateDataQuality().process_rows(
    frame=raw_with_file,
    ruleset=ruleset,
    publishing_options={
        "dataQualityEvaluationContext": "hl_m07_raw_check",
        "enableDataQualityCloudWatchMetrics": True,
        "enableDataQualityResultsPublishing": True,
    },
    additional_options={"performanceTuning.caching": "CACHE_NOTHING"},
)
rows = SelectFromCollection.apply(dfc=results, key="rowLevelOutcomes", transformation_ctx="rows").toDF()

dq_cols = ["DataQualityRulesPass", "DataQualityRulesFail", "DataQualityRulesSkip", "DataQualityEvaluationResult"]
passed = rows.filter(F.col("DataQualityEvaluationResult") == "Passed").drop(*dq_cols)
failed = rows.filter(F.col("DataQualityEvaluationResult") != "Passed")
failed.withColumn("reason_codes", F.concat_ws(",", "DataQualityRulesFail")) \
      .write.mode("append").json(f"s3://{ARGS['quarantine_bucket']}/dq_rejects/")

The C# test that grades the validator against the answer key

TransactionValidatorTests.cs (excerpt)
[Fact]
public void Every_injected_defect_is_found_and_nothing_else()
{
    var rows = Samples.Validate("transactions_messy.csv");

    // Answer key: row_number,key,defect,column,original_value,messy_value,note
    var expected = File.ReadAllLines(Samples.Path("transactions_messy_defects.csv")).Skip(1)
        .Select(CsvLine.Parse)
        .GroupBy(f => int.Parse(f[0], CultureInfo.InvariantCulture))
        .ToDictionary(g => g.Key, g => g.Select(f => f[2]).ToHashSet(StringComparer.Ordinal));
    var found = rows.Where(r => r.Issues.Count > 0)
        .ToDictionary(r => r.RowNumber, r => r.Issues.Select(i => i.Defect).ToHashSet(StringComparer.Ordinal));

    var missed = expected.Where(e => !found.TryGetValue(e.Key, out var f) || !e.Value.IsSubsetOf(f)).Select(e => e.Key).ToList();
    var unexpected = found.Keys.Except(expected.Keys).ToList();
    Assert.Empty(missed);      // recall: every planted defect is caught
    Assert.Empty(unexpected);  // precision: no clean row is flagged
}

[Fact]
public void Messy_file_control_totals_match_the_python_job()
{
    var rows = Samples.Validate("transactions_messy.csv");
    Assert.Equal(1036, rows.Count);
    Assert.Equal(969, rows.Count(r => r.Accepted));
    Assert.Equal(67, rows.Count(r => !r.Accepted));
    Assert.Equal(124187.38m, rows.Where(r => r.Curated?.Currency == "GBP").Sum(r => r.Curated!.Amount));
}

Reconciliation (from glue/src/harbourline_etl/reports.py)

def reconcile(rows, source_file, run_id, control=None, currencies=("EUR", "GBP")):
    accepted_count = sum(1 for r in rows if r.accepted)
    quarantined_count = len(rows) - accepted_count
    source_count = control.record_count if control else len(rows)
    counts_ok = source_count == accepted_count + quarantined_count

    lines = []
    for cur in currencies:
        accepted_total = sum((Decimal(str(r.curated["amount"])) for r in rows
                              if r.accepted and r.curated and r.curated["currency"] == cur), Decimal("0.00"))
        quarantined_total = sum((_raw_amount(r) for r in rows
                                 if not r.accepted and r.raw.get("currency") == cur), Decimal("0.00"))
        source_total = (control.control_sums.get(cur, Decimal("0.00")) if control else
                        sum((_raw_amount(r) for r in rows if r.raw.get("currency") == cur), Decimal("0.00")))
        difference = source_total - accepted_total - quarantined_total
        lines.append({"currency": cur, "source_row_count": source_count,
                      "accepted_row_count": accepted_count, "quarantined_row_count": quarantined_count,
                      "source_control_total": source_total, "accepted_amount_total": accepted_total,
                      "quarantined_amount_total": quarantined_total, "difference": difference,
                      "status": "MATCHED" if counts_ok and difference == 0 else "BREAK"})
    return lines

Code explained line by line

DQDL

  1. Rules = [ ... ] — a ruleset is a list of rules; each rule has a type, a target column and a condition.
  2. RowCount > 0 — dataset-level: an empty file is suspicious (did the partner send the header only?).
  3. IsComplete "transaction_id" — no nulls/empties in the column. Row-level outcomes mark the offending rows.
  4. IsUnique "transaction_id" — no repeated values. It cannot see duplicates by idempotency_key with a new id — the Python rule can.
  5. ColumnValues "booking_date" matches "..." — regular expression on a string column. It cannot tell that 2026-02-30 is not a real date; the code rule does.
  6. ColumnValues "currency" in [...] — allowed values. Lower-case gbp fails, which is what we want.

Row split in Glue

  1. process_rows — evaluates the ruleset and returns a collection with ruleOutcomes (one row per rule) and rowLevelOutcomes (every input row with outcome columns).
  2. publishing_options — results appear in the Glue console under the evaluation context name and as CloudWatch metrics for alarms.
  3. DataQualityEvaluationResult — Passed or Failed for each row; DataQualityRulesFail lists the rules that row broke, which we turn into its reason codes.
  4. Dataset-level rules such as RowCount do not mark individual rows; they appear only in ruleOutcomes.

C# grading test

  1. Reads the answer key, groups by row_number, and builds the set of expected defects per row.
  2. found maps each flagged row to the defect names derived from its reason codes (ReasonCodes.ToDefect).
  3. missed checks recall: every planted defect is found. unexpected checks precision: no clean row is flagged. Both must be empty — a validator that rejects everything would pass a recall-only test.
  4. The control-total test uses the same numbers as the Python test, so the two implementations cannot drift apart silently.

Reconciliation

  1. counts_ok — every row read must be either accepted or quarantined.
  2. Totals use Decimal, never float: 0.1 + 0.2 must be 0.3 in a bank.
  3. quarantined_total uses the raw amount when it parses, so a row rejected for a bad IBAN still contributes its money to the reconciliation.
  4. With a control (from the sender), the source figures come from outside the pipeline — the strong check. Without one, the job reconciles against its own reading of the file, which proves only that nothing was lost inside the job.
  5. A row with currency gbp is rejected and belongs to no allowed currency, so a sender's GBP control total will show a break of exactly that amount — correct behaviour: someone must look at it.

Security considerations

⚠ Learning shortcut — not for production

The lab quarantine stores the full raw record as JSON so you can see what failed. In production, consider storing only the row number, reason codes and a pointer to the raw object version (which is already retained in the raw zone), so PII is not copied a second time; restrict who can read quarantine and expire it quickly once rows are resolved.

  • Reject reports go to people outside the data team (operations, partners). Send reason codes and row numbers, not full records, and never e-mail clear IBANs.
  • Do not log rejected values in CloudWatch. Log counts by reason code.
  • DQ results are evidence. Keep the published results and the audit records for the period your audit policy requires.
  • Rules are code: review them, version them, test them; a silently weakened rule is a control failure.

Banking use case

Every file that lands in Harbourline's raw zone produces: a curated partition with accepted rows; a quarantine object with rejected rows and reason codes; a reconciliation line per currency; and an audit record. At 06:00 the operations team receives the list of breaks and the count of rejects by reason and partner. Saltmarsh Payroll gets an automated reply listing line numbers with INVALID_IBAN, and corrected lines come back as a new file with a new batch id. Month-end, finance signs off only runs with status MATCHED or with a documented explanation for each break.

Common mistakes

  • Silently dropping bad rows → numbers do not add up and nobody knows why. Quarantine with reason codes and reconcile.
  • Rejecting a whole file for one bad row → valid payments are delayed. Reject rows; reject files only for schema problems.
  • Validation rules only in DQDL → no unit tests and no business-specific checks. Keep business rules in tested code; use DQDL as a second line.
  • Floating-point money in checks → off-by-a-penny breaks. Use decimal/Decimal.
  • No precision check in tests → a validator that rejects everything "passes". Test both directions.
  • Treating late data as bad data → real transactions lost. Accept, flag, recompute affected reports.

Troubleshooting

SymptomLikely causeFix
Recall test fails for bad_date rowsRegex accepts 2026-02-30Parse the date after the regex (date.fromisoformat / DateOnly.TryParseExact)
Precision test flags clean rows with ENCODING_REPAIREDMojibake regex too broadMatch only Ã/Â followed by U+0080–U+00BF
C# and Python totals differ by penniesOne side used double/floatUse decimal and Decimal end to end
rowLevelOutcomes missing in GlueUsing apply (rule outcomes only) instead of process_rowsCall process_rows and select rowLevelOutcomes
DQ run fails with AccessDeniedRole lacks permissions to publish results or read the tableAdd glue:PublishDataQuality and table/S3 read
Reconciliation BREAK with difference equal to one row's amountThat row's currency code is invalid (for example gbp)Expected; resolve via the reject report

Cost and cleanup

Everything in this lesson except running rulesets in AWS is free: tests run in Docker. Glue Data Quality runs bill as Glue DPU time (USD 0.44 per DPU-hour in us-east-1, with a minimum per run) ⚠ VERIFY the current minimum billing for data quality runs on the Glue pricing page. A single evaluation on the lab table costs cents.

aws glue delete-data-quality-ruleset --name hl-m07-curated-transactions --profile learn-sandbox

Exercises

  • 🟢 Guided: Run both test suites. Then break one rule on purpose (for example allow gbp) and watch which test fails and why.
  • 🟡 Challenge: Add a rule FUTURE_DATED: reject rows whose booking_date is more than 5 days after created_at. Implement it in Python and C#, add the code to both reason-code tables, and extend both tests with a hand-made row.
  • 🔴 Live problem: The partner says "we sent 40 payments totalling £89,281.75" (the trailer of HLPG_PTN0042_PAYMENTS_20261116_001_messy.csv). Your pipeline accepted 37 and quarantined 2 — and the totals do not match. Explain the possible causes and how you would demonstrate the reconciliation to the partner.

Assignment

Write a data-quality specification for the payments dataset: dimensions, rules, reason codes, severities and actions, DQDL for what DQDL can express, and the tests you would write. Use payments_messy_defects.csv as your answer key.

Solutions

Knowledge check

Interview questions

  • "How do you handle bad data in a pipeline?" — Classify by dimension; reject rows with reason codes to a quarantine zone; repair only when safe and record a warning; reconcile counts and totals; alert on breaks; report rejects to the owner.
  • "How do you know your validation is right?" — Graded tests against a dataset with known, planted defects; both recall and precision; the same tests across implementations.
  • "What is Deequ / Glue Data Quality?" — Declarative data checks on Spark; Glue Data Quality uses DQDL, publishes scores and can mark rows for routing.
  • "What is reconciliation?" — Proving with independent control figures that the output equals the input, minus documented exceptions.

Certification practice

Completion checklist

  • I can name the data-quality dimensions and the action for each
  • I ran both test suites and understand recall and precision
  • I can read and write a DQDL ruleset and know its limits
  • I can explain a reconciliation line, including a break
  • I deleted any rulesets I created in AWS

Key takeaways

  • Validate rows, not files; reject with stable reason codes; repair only when safe and warn.
  • Grade validators against a dataset with planted defects — both recall and precision — and keep twin implementations in step with shared tests.
  • Use Glue Data Quality (DQDL) as an independent second line and for metrics and alarms.
  • Quarantine is sensitive: protect it like raw.
  • Reconcile counts and totals with independent control figures; write the audit record last.

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