Skip to content
Visory LLC

Axfaltec Variance Validation

What it does

Section titled “What it does”

Reconciles fuel-volume mass balance and variance for Axfaltec by cross-checking three independent data sources against each other: the storage-almacenista report (“Reporte Almacenista”), import receipts (“Ingresos”), and end-of-day dispatch (“EOD”). All three describe the same three canonical products — Regular, Premium, and DUBA (Diésel de Ultra Bajo Azufre) — using different vocabularies, which products.py maps into a shared ProductKey.

Pipeline (DVC-orchestrated, dvc.yaml)

Section titled “Pipeline (DVC-orchestrated, dvc.yaml)”

A 10-stage DAG (dvc dag, rendered to docs/pipeline_dag.md by the dag_doc stage):

  1. ingest — loaders.py reads the raw Axfaltec/Pedimentos workbooks with fastexcel, dynamically locating header rows and canonicalizing free-form Spanish column names (text.normalize_str); outputs clean reporte_almacenista.parquet, ingresos.parquet, eod.parquet.
  2. mass_balance — daily mass-balance and process gain/loss on the Reporte Almacenista.
  3. dispatch — EOD vs. CNE report dispatch reconciliation, flags violations.
  4. receipts — Ingresos vs. CNE report receipts reconciliation (daily/monthly), flags violations.
  5. coverage — data coverage summary and gap detection across the three sources.
  6. tracker / pair_shift — continuity tracking and shift-pairing of anomalies over time.
  7. audit_workbook — assembles all EDA outputs into an Excel audit workbook (build_audit_workbook.py).
  8. exec_summary — generates a .docx executive summary (build_executive_summary*.py).
  9. technical_doc_md / technical_doc_pdf — generates a Markdown + PDF technical document with data schemas (weasyprint for PDF rendering).

Data & outputs

Section titled “Data & outputs”

Raw inputs under data/raw/{Pedimentos,Reporte_Historico}/; intermediate parquet under data/preprocessed/; EDA CSVs under data/out/eda/; final deliverables under data/out/audit/ (Audit Workbook.xlsx, Executive Summary.docx) and docs/ (technical document .md/.pdf). Supplementary docs (01_open_items.md, review meeting talking points, deliverables email) are DVC-tracked (.dvc pointers) rather than committed directly.

Stack

Section titled “Stack”

polars + fastexcel for ingestion, python-docx for the executive summary, xlsxwriter for the audit workbook, weasyprint for PDF generation, matplotlib/seaborn for charts embedded in reports.

Status

Section titled “Status”

The most mature, fully DVC-orchestrated pipeline of the 7 projects — end-to-end from raw workbook ingestion to client-ready audit workbook, executive summary, and technical documentation.