This project is a portfolio prototype that simulates how reporting problems emerge across enterprise dataflows and how they can be investigated in a structured, business-aware way.
It is built around a simple but realistic scenario: a daily cash movement reporting process takes records from a source system, passes them through a transformation / ETL layer, and publishes them into a downstream reporting layer. The goal of the prototype is to show what happens when that flow breaks and how a systems-oriented analyst can detect, document, validate, and remediate those issues.
In short, this is not just a data project. It is a system dataflow investigation project designed to reflect the kind of work involved in technical business analysis, reporting workflow analysis, and exception investigation roles.
In enterprise environments, reporting errors are often not caused by one obvious system failure. A report can become unreliable because problems are introduced at different points in the workflow, such as:
- a record never reaching the downstream layer
- the same record being published twice
- an amount being transformed incorrectly
- a category being mapped inconsistently
- a record arriving too late for the expected reporting cutoff
When this happens, the real task is not just to “find the bad record.” The real task is to understand:
- where the record moved through the workflow
- where the logic broke down
- how to prove the issue with repeatable checks
- how to turn findings into remediation and control improvements
That is the problem this prototype is built to simulate.
This prototype models a closed-loop investigation process across three system layers:
-
Source Layer
Raw cash movement records enter the workflow with original account IDs, dates, amounts, movement types, and statuses. -
Transformation / ETL Layer
Records are standardized, mapped, normalized, validated, and prepared for publication. -
Downstream Reporting Layer
Final reporting outputs are produced for business review and monitoring.
The project then introduces seeded exceptions into that workflow and investigates them using:
- SQL-based exception checks
- Python + DuckDB execution
- structured issue logging
- QA / UAT-style test cases
- findings summaries
- remediation and control recommendations
The result is a prototype that shows not only how exceptions are detected, but also how investigation findings feed back into workflow improvement.
If someone asked, “What is this project actually doing?”, the shortest answer would be:
This project simulates a reporting workflow, intentionally introduces dataflow errors, detects them with SQL checks, documents the findings, validates the results, and closes the loop with remediation and control recommendations.
That narrative is the backbone of the entire repository:
workflow → exception → investigation → validation → findings → remediation → workflow improvement
To make the investigation realistic, the prototype includes five intentionally seeded exception types.
| Transaction ID | Exception Type | What It Means |
|---|---|---|
| TXN1005 | Missing Record | The record exists upstream but never appears in downstream reporting |
| TXN1007 | Duplicate Record | The same record appears more than once in downstream reporting |
| TXN1008 | Classification Mismatch | The downstream category does not match the transformed mapping |
| TXN1009 | Amount Mismatch | The downstream amount differs from the transformed normalized amount |
| TXN1010 | Timing Mismatch | The record is processed or reported later than its original business date |
These exceptions were chosen because they reflect common reporting integrity problems in enterprise workflows.
The prototype starts by defining the business scenario, the dataflow layers, and the field mappings between source, transformation, and downstream reporting.
Relevant files:
docs/business-context.mddocs/field-mapping.md
The data is intentionally structured so that specific transaction records contain known issues that should be detectable later.
Relevant files:
data/source_cash_movements.csvdata/transformed_cash_movements.csvdata/downstream_cash_report.csv
The prototype uses targeted SQL checks to identify missing records, duplicates, amount mismatches, classification mismatches, and timing issues.
Relevant files:
sql/01_missing_records.sqlsql/02_duplicate_records.sqlsql/03_amount_mismatch.sqlsql/04_classification_mismatch.sqlsql/05_timing_mismatch.sql
A Python runner uses DuckDB to execute the SQL checks directly against the CSV data and save the outputs.
Relevant file:
python/run_sql_checks.py
Generated outputs:
data/query_results/
The prototype records each issue in a structured issue log, then validates the expected vs observed outcomes through test cases.
Relevant files:
docs/issue-log.mddocs/test-cases.md
The final stage translates investigation results into findings, root cause themes, and remediation/control recommendations.
Relevant file:
docs/final-findings.md
This creates a closed-loop investigation model rather than a one-time detection script.
system-dataflow-investigation-prototype/
├── README.md
├── data/
│ ├── source_cash_movements.csv
│ ├── transformed_cash_movements.csv
│ ├── downstream_cash_report.csv
│ └── query_results/
├── docs/
│ ├── business-context.md
│ ├── field-mapping.md
│ ├── issue-log.md
│ ├── test-cases.md
│ ├── final-findings.md
│ └── prototype-workflow.png
├── python/
│ └── run_sql_checks.py
└── sql/
├── 01_missing_records.sql
├── 02_duplicate_records.sql
├── 03_amount_mismatch.sql
├── 04_classification_mismatch.sql
└── 05_timing_mismatch.sql