Transaction Data Health Check

/**
 * Fintech Math Bootcamp · Module 03 demo · Transaction Data Health Check
 * A finance team wants March card revenue from three small extracts: customer profiles, card payments
 * and EUR→USD rates. Before anyone trusts the total, the tool runs one check per lesson and fills in a
 * data quality report card. Lessons 021–030. Synthetic example; not financial advice.
 */

export type Grade = "low" | "medium" | "high";
export type Customer = {customer: string; snapshot: string; validFrom: string; segment: string; riskGrade: Grade};
export type Payment = {id: string; customer: string; amount: number | null; currency: "USD" | "EUR"; at: string; settledHours: number | null; openFor: number | null};
export type FxVintage = {pair: string; rateDate: string; rate: number; availableAt: string};
export type Lineage = {table: string; source: string; owner: string; licence: string; transform: string};
export type Status = "pass" | "warn" | "fixed" | "fail";

export const customers: Customer[] = [
  {customer: "C1", snapshot: "Jan", validFrom: "2026-01-01T00:00:00Z", segment: "retail", riskGrade: "low"},
  {customer: "C2", snapshot: "Jan", validFrom: "2026-01-01T00:00:00Z", segment: "retail", riskGrade: "medium"},
  {customer: "C3", snapshot: "Jan", validFrom: "2026-01-01T00:00:00Z", segment: "small business", riskGrade: "low"},
  {customer: "C4", snapshot: "Jan", validFrom: "2026-01-01T00:00:00Z", segment: "small business", riskGrade: "high"},
  {customer: "C1", snapshot: "Mar", validFrom: "2026-03-01T00:00:00Z", segment: "retail", riskGrade: "low"},
  {customer: "C2", snapshot: "Mar", validFrom: "2026-03-01T00:00:00Z", segment: "retail", riskGrade: "high"},
  {customer: "C3", snapshot: "Mar", validFrom: "2026-03-01T00:00:00Z", segment: "small business", riskGrade: "medium"},
  {customer: "C4", snapshot: "Mar", validFrom: "2026-03-01T00:00:00Z", segment: "small business", riskGrade: "high"},
];

// openFor: a payment still unsettled when the extract was taken (censored settlement time)
export const payments: Payment[] = [
  {id: "P01", customer: "C1", amount: 120, currency: "USD", at: "2026-03-02T09:15:00Z", settledHours: 20, openFor: null},
  {id: "P02", customer: "C1", amount: 80, currency: "USD", at: "2026-03-02T14:40:00Z", settledHours: 26, openFor: null},
  {id: "P03", customer: "C2", amount: 250, currency: "EUR", at: "2026-03-04T11:00:00+01:00", settledHours: 30, openFor: null},
  {id: "P04", customer: "C2", amount: 40, currency: "USD", at: "2026-03-09T18:05:00Z", settledHours: 22, openFor: null},
  {id: "P05", customer: "C3", amount: 60, currency: "USD", at: "2026-03-12T08:30:00Z", settledHours: 18, openFor: null},
  {id: "P06", customer: "C3", amount: null, currency: "USD", at: "2026-03-15T16:20:00Z", settledHours: 24, openFor: null},
  {id: "P07", customer: "C4", amount: 95, currency: "USD", at: "2026-03-20T12:00:00Z", settledHours: 40, openFor: null},
  {id: "P08", customer: "C4", amount: Infinity, currency: "USD", at: "2026-03-22T10:45:00Z", settledHours: 21, openFor: null},
  {id: "P09", customer: "C1", amount: 150, currency: "USD", at: "2026-03-27T19:10:00Z", settledHours: 25, openFor: null},
  {id: "P10", customer: "C2", amount: 55, currency: "USD", at: "2026-03-31T22:30:00-04:00", settledHours: null, openFor: 48},
];

// the EUR→USD rate for 3 March was first published, then revised two days later
export const fx: FxVintage[] = [
  {pair: "EURUSD", rateDate: "2026-03-03", rate: 1.08, availableAt: "2026-03-03T16:00:00Z"},
  {pair: "EURUSD", rateDate: "2026-03-03", rate: 1.0842, availableAt: "2026-03-06T09:00:00Z"},
];

export const lineage: Lineage[] = [
  {table: "customers", source: "synthetic-crm-v2", owner: "crm-team", licence: "internal", transform: "monthly-snapshot"},
  {table: "payments", source: "synthetic-card-processor-v1", owner: "payments-ops", licence: "internal", transform: "march-extract"},
  {table: "fx rates", source: "synthetic-fx-feed", owner: "", licence: "", transform: "daily-close"},
];

const ms = (s: string) => Date.parse(s);
const round2 = (x: number) => Math.round(x * 100) / 100;
const usd = (x: number) => "$" + x.toLocaleString("en-US", {minimumFractionDigits: 2, maximumFractionDigits: 2});

// 021 · rows are observations; entities are counted by their key
export const entityCount = <T>(rows: T[], key: (r: T) => string) => new Set(rows.map(key)).size;

// 022 · an ordinal variable has order but no measured distance
export const GRADE_ORDER: Grade[] = ["low", "medium", "high"];
export function gradeSummary(grades: Grade[]) {
  const counts = GRADE_ORDER.map(g => grades.filter(x => x === g).length);
  const max = Math.max(...counts);
  const codes = grades.map(g => GRADE_ORDER.indexOf(g) + 1);
  return {counts, modes: GRADE_ORDER.filter((_, i) => counts[i] === max), meaninglessMeanCode: codes.reduce((s, x) => s + x, 0) / codes.length};
}

// 024 · a declared grain is a key that must be unique
export const isUnique = <T>(rows: T[], key: (r: T) => string) => entityCount(rows, key) === rows.length;

// 027 · keep only finite numbers; never turn unknown into zero
export const usable = (x: number | null): x is number => typeof x === "number" && Number.isFinite(x);

// 029 · point-in-time: the latest vintage already available at the decision time
export function pointInTime(vintages: FxVintage[], decisionAt: string): FxVintage | null {
  return vintages.filter(v => ms(v.availableAt) <= ms(decisionAt))
    .reduce<FxVintage | null>((best, v) => best === null || ms(v.availableAt) > ms(best.availableAt) ? v : best, null);
}
export const latestVintage = (vintages: FxVintage[]) => vintages.reduce((a, b) => (ms(b.availableAt) > ms(a.availableAt) ? b : a));

// 025 · a join on customer alone versus a join at the declared grain (customer × snapshot in force)
export function naiveJoin(ps: Payment[], cs: Customer[]) {
  return ps.flatMap(p => cs.filter(c => c.customer === p.customer).map(c => ({p, c})));
}
export function grainJoin(ps: Payment[], cs: Customer[]) {
  return ps.map(p => {
    const inForce = cs.filter(c => c.customer === p.customer && ms(c.validFrom) <= ms(p.at));
    const c = inForce.reduce((a, b) => (ms(b.validFrom) > ms(a.validFrom) ? b : a));
    return {p, c};
  });
}

// 026 · compare instants, not clock strings; the reporting calendar is UTC
export const utcDate = (s: string) => new Date(ms(s)).toISOString().slice(0, 10);
export const localDate = (s: string) => s.slice(0, 10);

// 028 · error = measured − reference; bias is the mean error, spread is the range
export function clockCheck(measured: number[], reference: number) {
  const errors = measured.map(x => x - reference);
  return {errors, bias: errors.reduce((s, x) => s + x, 0) / errors.length, spread: Math.max(...errors) - Math.min(...errors)};
}

// 030 · lineage is complete only when every required field is present
export const lineageComplete = (l: Lineage) => (["source", "owner", "licence", "transform"] as const).every(k => l[k].length > 0);

export function runDemo() {
  // 021 · entities versus rows
  const entities = {
    paymentRows: payments.length, paymentCustomers: entityCount(payments, p => p.customer),
    customerRows: customers.length, customerEntities: entityCount(customers, c => c.customer),
    fxRows: fx.length,
  };

  // 022 · variable types, with the risk grade as an ordinal
  const current = customers.filter(c => c.snapshot === "Mar");
  const grades = gradeSummary(current.map(c => c.riskGrade));
  const types = [
    {column: "amount", type: "numeric"}, {column: "currency", type: "categorical"},
    {column: "riskGrade", type: "ordinal"}, {column: "isSettled", type: "binary"}, {column: "customer", type: "identifier"},
  ];

  // 023 · the extract is a sampling frame, not the whole population
  const population = {allMarchPayments: 13, cardPaymentsInFrame: payments.length, bankTransfersNotCovered: 13 - payments.length};

  // 024 · declared grain of each table, checked for uniqueness
  const grain = [
    {table: "payments", key: "payment id", kind: "event", unique: isUnique(payments, p => p.id)},
    {table: "customers", key: "customer", kind: "panel", unique: isUnique(customers, c => c.customer)},
    {table: "customers", key: "customer × snapshot", kind: "panel", unique: isUnique(customers, c => c.customer + "|" + c.snapshot)},
    {table: "fx rates", key: "pair × date × vintage", kind: "time series", unique: isUnique(fx, v => v.pair + v.rateDate + v.availableAt)},
  ];

  // 029 · the EUR payment is converted at the decision time, the next morning
  const eur = payments.find(p => p.currency === "EUR")!;
  const decisionAt = "2026-03-05T09:00:00Z";
  const pit = pointInTime(fx, decisionAt)!;
  const revised = latestVintage(fx);
  const toUsd = (p: Payment, rate: number) => (p.currency === "EUR" ? round2((p.amount as number) * rate) : (p.amount as number));

  // 027 · missing, nonfinite, censored
  const valid = payments.filter(p => usable(p.amount));
  const missing = payments.filter(p => p.amount === null).map(p => p.id);
  const nonfinite = payments.filter(p => typeof p.amount === "number" && !Number.isFinite(p.amount)).map(p => p.id);
  const censored = payments.filter(p => p.openFor !== null).map(p => ({id: p.id, atLeastHours: p.openFor}));

  // 025 · the join that doubles the money
  const naive = naiveJoin(valid, customers);
  const fixed = grainJoin(valid, customers);
  const sumUsd = (rows: {p: Payment}[], rate: number) => round2(rows.reduce((s, r) => s + toUsd(r.p, rate), 0));
  const revenue = {
    naiveRows: naive.length, naiveTotal: sumUsd(naive, pit.rate),
    fixedRows: fixed.length, fixedTotal: sumUsd(fixed, pit.rate),
    withRevisedRate: sumUsd(fixed, revised.rate),
    perCustomerFixed: ["C1", "C2", "C3", "C4"].map(id => round2(fixed.filter(r => r.p.customer === id).reduce((s, r) => s + toUsd(r.p, pit.rate), 0))),
  };

  // 026 · timestamps: local strings versus UTC instants
  const times = payments.map(p => ({id: p.id, raw: p.at, utc: new Date(ms(p.at)).toISOString().replace(".000Z", "Z"), localDate: localDate(p.at), utcDate: utcDate(p.at)}));
  const crossesMonth = times.filter(x => x.localDate.slice(0, 7) !== x.utcDate.slice(0, 7)).map(x => x.id);

  // 028 · a terminal clock checked against a reference: four test pings, seconds past the reference minute
  const clock = {reference: 0, measured: [5, 5, 6, 4], ...clockCheck([5, 5, 6, 4], 0)};

  // 030 · provenance
  const provenance = lineage.map(l => ({table: l.table, source: l.source, owner: l.owner || "—", licence: l.licence || "—", transform: l.transform, complete: lineageComplete(l)}));

  const card: {lesson: number; check: string; status: Status; finding: string}[] = [
    {lesson: 21, check: "Entities vs rows", status: "pass", finding: `${entities.paymentCustomers} customers, not ${entities.paymentRows}`},
    {lesson: 22, check: "Variable types", status: "pass", finding: "risk grade is ordinal"},
    {lesson: 23, check: "Sample vs population", status: "warn", finding: `card only: ${population.cardPaymentsInFrame} of ${population.allMarchPayments}`},
    {lesson: 24, check: "Declared grain", status: "warn", finding: "customers: customer × snapshot"},
    {lesson: 25, check: "Join cardinality", status: "fixed", finding: `${usd(revenue.naiveTotal)} → ${usd(revenue.fixedTotal)}`},
    {lesson: 26, check: "Time zones", status: "warn", finding: `${crossesMonth.join(", ")} is April in UTC`},
    {lesson: 27, check: "Missing & nonfinite", status: "warn", finding: `${missing.length + nonfinite.length} excluded, ${censored.length} censored`},
    {lesson: 28, check: "Measurement", status: "warn", finding: `terminal clock +${clock.bias} s`},
    {lesson: 29, check: "Point-in-time FX", status: "fixed", finding: `use ${pit.rate.toFixed(4)}, not ${revised.rate.toFixed(4)}`},
    {lesson: 30, check: "Provenance & licence", status: "fail", finding: "FX licence missing"},
  ];
  const tally = (s: Status) => card.filter(c => c.status === s).length;

  return {
    tables: {
      customers: customers.map(c => ({customer: c.customer, snapshot: c.snapshot, segment: c.segment, riskGrade: c.riskGrade})),
      payments: payments.map(p => ({id: p.id, customer: p.customer, amount: p.amount === null ? "null" : Number.isFinite(p.amount) ? p.amount.toFixed(2) : "Infinity", currency: p.currency, at: p.at, settled: p.openFor !== null ? `≥ ${p.openFor} h (open)` : `${p.settledHours} h`})),
      fx: fx.map(v => ({...v})),
    },
    entities, types, grades, population, grain,
    revenue, validRows: valid.length, missing, nonfinite, censored,
    times, crossesMonth, clock,
    fxCheck: {eurPayment: eur.id, eurAmount: eur.amount, eurAt: eur.at, decisionAt, pitRate: pit.rate, pitAvailableAt: pit.availableAt, revisedRate: revised.rate, revisedAvailableAt: revised.availableAt,
      usdPit: toUsd(eur, pit.rate), usdRevised: toUsd(eur, revised.rate), lookAheadError: round2(toUsd(eur, revised.rate) - toUsd(eur, pit.rate))},
    provenance, card,
    summary: {pass: tally("pass"), warn: tally("warn"), fixed: tally("fixed"), fail: tally("fail"), trustedRevenue: revenue.fixedTotal},
  };
}

export const checkedResult = {"tables":{"customers":[{"customer":"C1","snapshot":"Jan","segment":"retail","riskGrade":"low"},{"customer":"C2","snapshot":"Jan","segment":"retail","riskGrade":"medium"},{"customer":"C3","snapshot":"Jan","segment":"small business","riskGrade":"low"},{"customer":"C4","snapshot":"Jan","segment":"small business","riskGrade":"high"},{"customer":"C1","snapshot":"Mar","segment":"retail","riskGrade":"low"},{"customer":"C2","snapshot":"Mar","segment":"retail","riskGrade":"high"},{"customer":"C3","snapshot":"Mar","segment":"small business","riskGrade":"medium"},{"customer":"C4","snapshot":"Mar","segment":"small business","riskGrade":"high"}],"payments":[{"id":"P01","customer":"C1","amount":"120.00","currency":"USD","at":"2026-03-02T09:15:00Z","settled":"20 h"},{"id":"P02","customer":"C1","amount":"80.00","currency":"USD","at":"2026-03-02T14:40:00Z","settled":"26 h"},{"id":"P03","customer":"C2","amount":"250.00","currency":"EUR","at":"2026-03-04T11:00:00+01:00","settled":"30 h"},{"id":"P04","customer":"C2","amount":"40.00","currency":"USD","at":"2026-03-09T18:05:00Z","settled":"22 h"},{"id":"P05","customer":"C3","amount":"60.00","currency":"USD","at":"2026-03-12T08:30:00Z","settled":"18 h"},{"id":"P06","customer":"C3","amount":"null","currency":"USD","at":"2026-03-15T16:20:00Z","settled":"24 h"},{"id":"P07","customer":"C4","amount":"95.00","currency":"USD","at":"2026-03-20T12:00:00Z","settled":"40 h"},{"id":"P08","customer":"C4","amount":"Infinity","currency":"USD","at":"2026-03-22T10:45:00Z","settled":"21 h"},{"id":"P09","customer":"C1","amount":"150.00","currency":"USD","at":"2026-03-27T19:10:00Z","settled":"25 h"},{"id":"P10","customer":"C2","amount":"55.00","currency":"USD","at":"2026-03-31T22:30:00-04:00","settled":"≥ 48 h (open)"}],"fx":[{"pair":"EURUSD","rateDate":"2026-03-03","rate":1.08,"availableAt":"2026-03-03T16:00:00Z"},{"pair":"EURUSD","rateDate":"2026-03-03","rate":1.0842,"availableAt":"2026-03-06T09:00:00Z"}]},"entities":{"paymentRows":10,"paymentCustomers":4,"customerRows":8,"customerEntities":4,"fxRows":2},"types":[{"column":"amount","type":"numeric"},{"column":"currency","type":"categorical"},{"column":"riskGrade","type":"ordinal"},{"column":"isSettled","type":"binary"},{"column":"customer","type":"identifier"}],"grades":{"counts":[1,1,2],"modes":["high"],"meaninglessMeanCode":2.25},"population":{"allMarchPayments":13,"cardPaymentsInFrame":10,"bankTransfersNotCovered":3},"grain":[{"table":"payments","key":"payment id","kind":"event","unique":true},{"table":"customers","key":"customer","kind":"panel","unique":false},{"table":"customers","key":"customer × snapshot","kind":"panel","unique":true},{"table":"fx rates","key":"pair × date × vintage","kind":"time series","unique":true}],"revenue":{"naiveRows":16,"naiveTotal":1740,"fixedRows":8,"fixedTotal":870,"withRevisedRate":871.05,"perCustomerFixed":[350,365,60,95]},"validRows":8,"missing":["P06"],"nonfinite":["P08"],"censored":[{"id":"P10","atLeastHours":48}],"times":[{"id":"P01","raw":"2026-03-02T09:15:00Z","utc":"2026-03-02T09:15:00Z","localDate":"2026-03-02","utcDate":"2026-03-02"},{"id":"P02","raw":"2026-03-02T14:40:00Z","utc":"2026-03-02T14:40:00Z","localDate":"2026-03-02","utcDate":"2026-03-02"},{"id":"P03","raw":"2026-03-04T11:00:00+01:00","utc":"2026-03-04T10:00:00Z","localDate":"2026-03-04","utcDate":"2026-03-04"},{"id":"P04","raw":"2026-03-09T18:05:00Z","utc":"2026-03-09T18:05:00Z","localDate":"2026-03-09","utcDate":"2026-03-09"},{"id":"P05","raw":"2026-03-12T08:30:00Z","utc":"2026-03-12T08:30:00Z","localDate":"2026-03-12","utcDate":"2026-03-12"},{"id":"P06","raw":"2026-03-15T16:20:00Z","utc":"2026-03-15T16:20:00Z","localDate":"2026-03-15","utcDate":"2026-03-15"},{"id":"P07","raw":"2026-03-20T12:00:00Z","utc":"2026-03-20T12:00:00Z","localDate":"2026-03-20","utcDate":"2026-03-20"},{"id":"P08","raw":"2026-03-22T10:45:00Z","utc":"2026-03-22T10:45:00Z","localDate":"2026-03-22","utcDate":"2026-03-22"},{"id":"P09","raw":"2026-03-27T19:10:00Z","utc":"2026-03-27T19:10:00Z","localDate":"2026-03-27","utcDate":"2026-03-27"},{"id":"P10","raw":"2026-03-31T22:30:00-04:00","utc":"2026-04-01T02:30:00Z","localDate":"2026-03-31","utcDate":"2026-04-01"}],"crossesMonth":["P10"],"clock":{"reference":0,"measured":[5,5,6,4],"errors":[5,5,6,4],"bias":5,"spread":2},"fxCheck":{"eurPayment":"P03","eurAmount":250,"eurAt":"2026-03-04T11:00:00+01:00","decisionAt":"2026-03-05T09:00:00Z","pitRate":1.08,"pitAvailableAt":"2026-03-03T16:00:00Z","revisedRate":1.0842,"revisedAvailableAt":"2026-03-06T09:00:00Z","usdPit":270,"usdRevised":271.05,"lookAheadError":1.05},"provenance":[{"table":"customers","source":"synthetic-crm-v2","owner":"crm-team","licence":"internal","transform":"monthly-snapshot","complete":true},{"table":"payments","source":"synthetic-card-processor-v1","owner":"payments-ops","licence":"internal","transform":"march-extract","complete":true},{"table":"fx rates","source":"synthetic-fx-feed","owner":"—","licence":"—","transform":"daily-close","complete":false}],"card":[{"lesson":21,"check":"Entities vs rows","status":"pass","finding":"4 customers, not 10"},{"lesson":22,"check":"Variable types","status":"pass","finding":"risk grade is ordinal"},{"lesson":23,"check":"Sample vs population","status":"warn","finding":"card only: 10 of 13"},{"lesson":24,"check":"Declared grain","status":"warn","finding":"customers: customer × snapshot"},{"lesson":25,"check":"Join cardinality","status":"fixed","finding":"$1,740.00 → $870.00"},{"lesson":26,"check":"Time zones","status":"warn","finding":"P10 is April in UTC"},{"lesson":27,"check":"Missing & nonfinite","status":"warn","finding":"2 excluded, 1 censored"},{"lesson":28,"check":"Measurement","status":"warn","finding":"terminal clock +5 s"},{"lesson":29,"check":"Point-in-time FX","status":"fixed","finding":"use 1.0800, not 1.0842"},{"lesson":30,"check":"Provenance & licence","status":"fail","finding":"FX licence missing"}],"summary":{"pass":2,"warn":5,"fixed":2,"fail":1,"trustedRevenue":870}};

// Run this file directly: npx tsx lessons/03-data-variables-samples-and-measurement/demo-transaction-data-health-check.ts
if (process.argv[1] && import.meta.url.endsWith(process.argv[1].replace(/\\/g, "/").split("/").pop()!)) {
  console.log(JSON.stringify(runDemo(), null, 2));
}