Thomas BrochardDevelopment · Data · AI
Let’s talk
Back to selected work

Data engineering

Case study / 05

Sales Warehouse Audit

Trace the records behind a reported total.

Connect retail revenue reconciliation, warehouse integrity checks and SQL change-capture concepts.

Technologies & concepts

  • SQL
  • SQLite
  • Star schema
  • Audit triggers
  • Data quality

Technical demonstration / selected source

Private repository

01 / The context

The problem behind the design.

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

Choices made explicit.

Model receipt items as facts

Item-level sales facts link to product, customer, employee and calendar dimensions, making the analytical grain explicit.

Reconcile through two calculation paths

Direct item aggregation and receipt-level totals provide comparable revenue calculations. Reference, duplicate and receipt-consistency checks expose possible warehouse issues.

Capture changes for review

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

Follow the flow.

Sales audit design: retail dimensions and item facts feed revenue views, integrity checks, SQL change capture and audit review.

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 diagram
  1. Retail dimensions

  2. Sales item facts

  3. Revenue views

  4. Integrity checks

  5. Change capture

  6. Audit review

04 / Selected artifacts

What the repository contains.

  • sql/schema.sql
  • sql/validation.sql
  • sql/audit_triggers.sql
  • sql/audit_analysis.sql

05 / Scope & limitations

How to read this project.

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.

Explore the source with access.

The GitHub repository is private. Its link works for authorized collaborators; other visitors can contact me to discuss the project.

Next case study / 06

Building Energy Prediction

Analytics & ML

Explore the collection

The next conversation

Let’s talk about your project.

↗
A need, an idea, a technical question.
LinkedIn ↗GitHub ↗