Python · SQL · Systems analysis · Synthetic data
A practical Python and SQL workflow for turning inconsistent synthetic intake into validated, traceable results.
Project proof
Why I built it
I built this project to show how I approach inconsistent operational data: define the business rules, preserve uncertainty, separate records that are ready for use from those that require review, and produce results that can be traced back to their source.
I wanted the project to show that I can translate an ambiguous business problem into clear requirements, validation logic, SQL-backed analysis, tested technical outputs, and documentation that both technical and business stakeholders can use.
The problem
Inconsistent intake data can create false confidence. An unknown value may be silently converted into an affirmative fact, while records that still require context may be mixed into the same dataset as records that are ready for reporting.
One issue that stood out to me was how an unknown boolean value was handled. Converting an unclear value to true may make the record easier to process, but it also invents a fact that was not present in the source. I decided that the uncertainty needed to remain visible and the record should be routed for review instead.
That reflects how I approach analysis more broadly: I would rather preserve an honest uncertainty than create a cleaner result that cannot be supported.
Design decisions
Two decisions shaped the rest of the data model, reporting, and test strategy.
01
I designed distinct surfaces so questionable input remains visible without weakening what “validated” means. All 30 records stay available; 21 meet the defined contract and 9 remain ready for human review.
02
I kept the source value next to its normalized value, rule, finding, and output identity. A reviewer can see what changed, why it changed, and where the result went.
Together, these choices prevent the workflow from manufacturing certainty. They make reliable records usable while preserving the records that still need judgment or context.
Workflow and implementation
I implemented the batch pipeline and bounded local API over the same normalization and validation rules so the behavior stays consistent.
Thirty synthetic source records pass through shared normalization and validation rules. Known values are normalized while unknown values remain visible. Records that meet the final contract are separated from those requiring review. SQLite and named SQL make both groups queryable, while reports, tests, and field-level traceability reconcile the reported result to the source.
I defined the source fields, controlled values, conditional rules, cross-record checks, and acceptance behavior before treating the output as valid.
Python and JSON Schema enforce the same contract across batch processing and the bounded local API.
SQLite keeps validated and review-required records distinct while retaining source locators and findings.
Named SQL exports and generated summaries reconcile against the controlled data instead of relying on copied totals.
Validated evidence
The figures describe the controlled synthetic sample and its reconciled outputs; they are not business-impact claims.
I tested required fields, controlled values, dates, ownership and closure rules, duplicate identities and references, schema behavior, SQLite integrity, SQL execution, report totals, and API contracts. Deterministic checks also guard against stale, missing, or unexpected generated outputs.
The traceability surface connects source location, original value, normalized value, rule, finding, and output identity. That lets a reviewer move from a reported result back to the exact data and reasoning behind it.
Skills demonstrated
Framed the workflow, stakeholders, constraints, decisions, and failure modes.
Converted the problem into testable business rules and expected behavior.
Handled missing, unknown, inconsistent, conditional, and cross-record values explicitly.
Created queryable surfaces and checked every reported count against controlled data.
Connected business intent to rules, code, findings, SQL, tests, and readable documentation.
How I used AI
I used AI-assisted tools to brainstorm edge cases, draft and refactor parts of the implementation, suggest tests, and check the code and documentation for consistency. I defined the goals and business rules, reviewed the changes and results, checked the generated outputs, and made the final decisions.
The technical claims are supported by reproducible code, schemas, SQL, automated tests, and reconciled results.
Scope
This project uses synthetic data and runs locally. It demonstrates analysis, validation, traceability, and documentation practices; it is not a live production deployment and does not claim production integrations, enterprise scale, or measured business outcomes.
Records that need context remain in review because the available data alone cannot resolve every decision.
What I learned
A workflow becomes more trustworthy when uncertainty stays visible and every reported result can be traced back to its source and rule.
What I would add next
I would add configurable rule profiles, reviewer dispositions, and testing over larger synthetic batches before developing a separate production-oriented version.