ARData with Ale

Case study 03 / Data quality / Financial control

Turn movement mismatches into explainable review decisions.

Warehouse movement and marketplace activity did not always agree. I owned the design and implementation of a production reconciliation platform that validates reports, normalizes identifiers and gives Finance an actionable exception queue.

Outcome / Reduced recurring manual reconciliation effort, improved refresh reliability and identified a potential cost-saving opportunity.

My contribution
End-to-end owner · Platform and investigative analysis
Primary users
CFO · Finance AR/AP
Project status
Used in production

01 / The problem

Inventory Movement Reconciliation Platform

Manual comparisons could not reliably separate movement exceptions from late updates, duplicate reports, bundle representations or invalid inputs. An exact-SKU mismatch alone was insufficient evidence of stock loss.

Validate operational workbooks before processing, standardize marketplace and order identifiers, compare movement at order-line grain and classify differences for review. Historical backfills support deeper investigation.

  1. Validate intaken8n routes operational workbooks. Invalid or incomplete inputs are rejected.
  2. Normalize & retainS3 retains raw reports; Glue standardizes identifiers, quantities and logical report versions.
  3. Compare evidenceAthena and Python reconcile warehouse movement with upstream marketplace order lines.
  4. Route for reviewException queues, Excel workbooks, Parquet / catalog outputs, audit records and alerts support Finance.
Architecture redrawn for this case study with generic system labels. Technology: Python · SQL · AWS Glue · Athena · S3 · n8n · Parquet · Glue Catalog · Excel.

02 / See the work

A review queue with a reason to investigate

Synthetic review workbook concept. All identifiers, quantities and scenarios are fictional; the categories illustrate how a mismatch becomes a review question.

Inventory Movement Reconciliation Platform

Illustrative reporting view

Synthetic data · Not a live dashboard
Order missingDEMO-A · warehouse 4 units / marketplace unavailable

Check source freshness and identifier normalization before interpreting the gap.

SKU missingDEMO-B · BUNDLE-X / COMPONENT-Y

Review bundle representation and mapping coverage. Different labels do not prove stock loss.

Quantity mismatchDEMO-C · warehouse 6 units / marketplace 4 units

Compare line-level evidence and reporting timing before assigning a cause.

Unsupported sourceDEMO-D · coverage unavailable

Keep the coverage limitation visible and route it for review.

Recreated specifically for this portfolio. No employer screenshots, source records or financial results are shown.

Before / an unexplained mismatch

One source records a bundle while the other records its components. An exact-SKU comparison flags a difference.

After / evidence-led review

Keep the difference visible, check the bundle mapping and source freshness, then document the review decision. Do not automatically label it stock loss.

03 / Technical judgment

The decisions behind the result.

  1. Validate before replacing results

    Reject schema drift, blank keys and invalid quantities. Preserve the previous valid snapshot when new input fails.

  2. Normalize before joining

    Marketplace identifiers and report versions are standardized before comparing order-line movement.

  3. Keep exceptions explainable

    Separate missing orders, missing SKUs, quantity differences and unsupported sources instead of returning a single unexplained mismatch flag.

  4. Support investigation over time

    Separate historical backfills from incremental processing. Retain raw evidence and audit outputs for repeatable review.

View a simplified processing pattern
# Illustrative pseudocode — not production source
if not valid(incoming_report):
    retain_previous_valid_snapshot()
    record_failure_and_notify()
else:
    movement = normalize(incoming_report)
    exceptions = compare_order_lines(movement, marketplace)
    publish_review_queue(exceptions)

# A mismatch is a review candidate, not proof of loss.

04 / Ownership & impact

What I delivered, and what changed.

My work and collaboration

I owned ingestion, workbook validation, identifier normalization, reconciliation logic, historical backfills, cloud integration, tests, operational documentation and finance-facing review outputs.

A Data Engineer maintained the upstream e-commerce pipeline. CFO and Finance AR/AP stakeholders were the main users, with supporting operational and master-data stakeholders.

Outcome

The platform was used in production, reduced recurring manual reconciliation effort and improved refresh reliability. The analysis also identified a potential recurring cost-saving opportunity in the warehouse and third-party logistics operating model.

Qualitative outcomes and usage status reflect my project account. Illustrative values above are not impact metrics.