Capstone, assessment and reusable daily checklist

Sources are a daily customer snapshot and transaction events. The current estate uses the legacy platform and its query interface. The proposed target uses cloud storage and a modern analytics platform. Consumers need the risk valid at transaction time and a reproducible explanation of missing matches.

Constraints for this exercise: synthetic data only; daily complete publication by 07:00 UTC; source-qualified customer keys; half-open history intervals; late corrections allowed. These are invented learning requirements, not commitments.

1. 01Business brief: purpose, consumers, scope and non-goals.
2. 02Conceptual model: parties, accounts, transactions and risk history.
3. 03Architecture: ingestion, raw data, validation, quarantine, history and governed consumption.
4. 04Data contract: grain, identifiers, hashes, valid time, recorded time and deletions.
5. 05Source-to-target mapping with conversions and NULL handling.
6. 06SQL evidence from the workbook.
7. 07Migration plan with backfill, catch-up, parallel run, cutover and rollback.
8. 08Quality and access test pack.
9. 09Operations runbook with ownership and recovery objectives.
10. 10Architecture decision record and unresolved questions.
11. 11Knowledge-agent evaluation using approved public sources.

For each case specify expected treatment, evidence and owner. Missing snapshot rows must not automatically become deletions.

• A repeated batch; a duplicate source key.
• A customer number shared across two systems.
• A NULL risk code; a customer merge.
• A transaction with an unknown account.
• Two updates at the same effective time.
• A correction received after its effective date.
• A deletion; an access denial; a partial source snapshot.

Proposed pass: 80 with no critical defect. Critical defects include history overlaps that multiply outputs, unstable entity identity, unauthorised access, unexplained missing populations or no workable recovery plan. A manager or experienced architect should review the result.

A good design preserves original events with lineage; resolves source-qualified keys; validates and versions canonical records; represents history with explicit intervals; uses an as-of join; separates invalid records from approved publication; reconciles both keys and material values; and exposes controlled views. It explains where correction history is stored and what data existed at a historical decision time. It includes a source and target checkpoint and a practical replay plan.

There is no single mandatory service list. Explain why the selected components meet the requirements with an acceptable operating burden.

• Why is this the correct grain?
• Which field is stable across versions?
• What does each hash compare?
• What happens at midnight on a boundary?
• How do you know the source snapshot is complete?
• What changes if an update arrives late?
• Which permissions govern each copy?
• How do you recover after cutover?
• What would cause you to reject publication?

1. 01Confirm environment, role, engine and data freshness.
2. 02State the business question and population.
3. 03Check source authority, grain and key uniqueness.
4. 04Check date, currency and NULL semantics.
5. 05Count unmatched and rejected records.
6. 06Save reproducible SQL and query or batch identifiers.
7. 07Record decisions, assumptions and unresolved issues.
8. 08Ask the right owner before treating a hypothesis as policy.

Prioritise version control and code review, basic Python for profiling, automated SQL testing, orchestration, infrastructure as code and performance analysis when the role actually requires them. Build depth in the deployed stack first.