RetailSync Data Platform

A reproducible local batch pipeline that generates synthetic retail orders, validates and stages them, refreshes a constrained SQLite analytical table, runs business SQL, and produces a monthly-revenue chart and operational log.

Verified results

A separately verified default-size local execution and immediate rerun produced the same reconciled snapshot:

Check	Result
Deterministic source orders	100,000
Accepted staging rows	100,000
Rejected baseline rows	0
SQLite rows	100,000
Distinct SQLite order IDs	100,000
Retained analytical queries	3
Automated tests	6 passed

The rerun regenerates the same seeded source and replaces the SQLite snapshot inside a transaction. It preserves one row per order_id rather than appending duplicates.

GitHub Actions runs the complete automated suite. Its full-pipeline test uses a 1,000-row fixture for fast hosted verification; it does not repeat the separate 100,000-row default-size execution.

Architecture

flowchart LR
    A["Seeded synthetic order generator"] --> B["Raw orders CSV"]
    B --> C["Pandas validation and cleaning"]
    C --> D["Accepted staging CSV"]
    C --> E["Rejected rows CSV"]
    C --> F["Data-quality report"]
    D --> G[("SQLite orders snapshot")]
    G --> H["Three analytical SQL queries"]
    H --> I["Monthly revenue chart"]
    J["Pipeline runner"] --> A
    J --> C
    J --> G
    J --> H
    J --> K["Application log"]

Loading

The pipeline is intentionally local and single-source. See docs/ARCHITECTURE.md for the execution and rerun contract.

Engineering decisions

• A fixed seed makes the 100,000-row source reproducible.
• Validation rejects missing required values, duplicate order IDs, non-numeric or non-positive quantities and revenue, and invalid dates.
• Accepted, rejected, and quality-summary outputs are written separately so record handling can be reconciled.
• SQLite uses a primary key, required columns, and positive-measure checks.
• Each run transactionally refreshes a complete analytical snapshot. This is defined snapshot idempotency, not incremental or streaming ingestion.
• Exceptions propagate to the caller and are logged as failures; the runner no longer reports success after a failed step.

Run locally

Requirements: Python 3.12+.

python -m pip install -r requirements.txt
python -m src.pipeline_runner
python -m unittest discover -s tests -v

Generated CSVs, logs, the SQLite database, and chart are intentionally ignored by Git. A clean clone reproduces them through the pipeline command.

Retained analytics

src/sql_analytics.py executes:

1. Top ten companies by revenue
2. Product order and unit counts
3. Monthly revenue for the 2025 synthetic dataset

Repository guide

• src/ — generator, validation, loading, analytics, chart, logging, runner
• sql/create_orders_table.sql — constrained SQLite schema
• tests/ — deterministic generation, rejection, full-run, and rerun checks
• docs/ — implemented architecture and data model

Limitations

RetailSync is a compact local portfolio project. It does not claim production use, cloud deployment, multiple independent sources, orchestration, streaming, incremental CDC, or measured performance. Customer and email values are fully synthetic. The project complements larger warehouse and distributed-data work; it is not intended to be a flagship system.