Model receipt items as facts
Item-level sales facts link to product, customer, employee and calendar dimensions, making the analytical grain explicit.
Data engineering
Case study / 05
Trace the records behind a reported total.
Connect retail revenue reconciliation, warehouse integrity checks and SQL change-capture concepts.
Technologies & concepts
Technical demonstration / selected source
Private repository
01 / The context
Revenue reports can change when source records are edited, references break or product prices are overwritten. The case study uses a retail model to make calculation paths and subsequent changes inspectable.
02 / Engineering decisions
Item-level sales facts link to product, customer, employee and calendar dimensions, making the analytical grain explicit.
Direct item aggregation and receipt-level totals provide comparable revenue calculations. Reference, duplicate and receipt-consistency checks expose possible warehouse issues.
SQL triggers record inserts, updates, deletes and product-price changes in a shared audit-event model. Queries make past-sale edits and price changes visible as review signals.
03 / System view
Illustrative architecture from the project presentation. It describes the selected design; it does not imply a deployed service or a measured operational result.
View the full-size diagramRetail dimensions
Sales item facts
Revenue views
Integrity checks
Change capture
Audit review
04 / Selected artifacts
sql/schema.sqlsql/validation.sqlsql/audit_triggers.sqlsql/audit_analysis.sql05 / Scope & limitations
Demonstration and source showcase without a retail extract, database or operational log. Audit events use a placeholder actor identity. The simplified revenue calculation uses current product prices, so edits can affect past totals. Historical prices, authenticated actors, tamper-resistant logs and operational alerts require additional design. A retrospective edit is a review signal, not evidence of misconduct.
The GitHub repository is private. Its link works for authorized collaborators; other visitors can contact me to discuss the project.
Next case study / 06
The next conversation