Retail Data Systems & Automation
Batch ETL, streaming reconciliation, and operational reporting.
Three bodies of data work across two employers. At Korner Enterprise Services, I automated recurring operational data processing with Python ETL across more than ten retail locations, and worked on a Kafka-based pipeline for fuel inventory reconciliation: aligning fuel tank-gauge measurements with periodic fuel receipt records by event time to identify inventory discrepancies. PostgreSQL and scheduled workflows with Airflow supported the pipelines, which also fed downstream Power BI reporting. Separately, at OBLS International LLC, I worked on TypeScript/React reporting tools backed by PostgreSQL and Python/PostgreSQL historical reconciliation.
- Role
- ETL, streaming reconciliation & reporting
- Category
- Data Engineering / Automation
- Status
- Korner Enterprise Services · OBLS International LLC
Operational problems
At Korner, recurring data processing for more than ten retail locations was handled manually, and fuel inventory had to be reconciled between two sources that arrived on different schedules.
Fuel tank gauges generated ongoing inventory measurements, while fuel delivery and receipt records became available periodically. Comparing the two was necessary to identify inventory discrepancies and surface potential anomalies.
At OBLS International LLC, a separate employer, the problem was different: Operations and Finance reporting relied heavily on Excel-based workflows.
Python ETL automation
Korner Enterprise Services
Automating recurring operational data processing
Operational data for more than ten locations came from platforms including Titan Cloud and CStore. Making it usable meant recurring manual processing: pulling data, cleaning and combining it, and checking the results, repeated for every location.
I started by understanding how that work was done, then automated it with Python ETL: extracting from the operational platforms, transforming data into a consistent shape across locations, and producing outputs without the repeated manual steps.
Before and after
Before · manual (Accounting department)
- Retrieve data from multiple operational systems
- Collect records covering 10+ retail locations
- Manually clean, consolidate, and organize the data
- Identify and investigate discrepancies
- Prepare reports and operational outputs
Recurring data processing and reconciliation were handled manually by the Accounting department across more than ten retail locations.
After · automated with Python
- Extract from Titan Cloud and CStore
- Transform to a consistent format across locations
- Reconcile across sources
- Produce outputs
Fuel inventory reconciliation
Korner Enterprise Services
Fuel inventory reconciliation with Kafka
Fuel tank gauges produce ongoing inventory measurements. Those measurements were ingested as events through Apache Kafka, while fuel delivery and receipt records became available periodically. The pipeline aligned the two sources and reconciled them to identify fuel inventory discrepancies and surface potential anomalies as alerts.
Ingestion and reconciliation run on different clocks. Gauge measurements arrive continuously, but reconciliation depends on receipt records that only become available periodically, so continuous ingestion doesn’t make the results real-time. PostgreSQL is part of the broader pipeline, and Airflow and cron are used for scheduled processing where applicable.
The pipelines also supported downstream Power BI reporting, making processed operational and fuel inventory data available for visualization and review. This connected the underlying data processing and reconciliation work with the reporting tools used to understand activity and investigate discrepancies across locations.
What the pipeline involved
- Fuel tank-gauge measurements as the continuous data source.
- Kafka-based ingestion of gauge measurements as events.
- Periodic fuel delivery and receipt records as the second source.
- Event-time alignment of the two sources, then reconciliation.
- Identification of fuel inventory discrepancies.
- Anomaly detection and alerts.
- PostgreSQL within the broader pipeline.
- Scheduled processing with Airflow, and cron where applicable.
- Downstream reporting in Microsoft Power BI.
Pipeline architecture
Inputs
Reconciliation
- Event-time alignment
- Reconciliation
- Discrepancy detection
Outputs
Downstream reporting
Event-time processing & anomaly detection
Korner Enterprise Services
Putting each fuel reading in the right reconciliation period
Fuel tank-gauge measurements and fuel receipt records describe overlapping periods, but they arrive at different times and on different cadences. Reconciling them correctly means grouping each measurement by when it was taken (its event time), not when it arrived, before comparing it with the receipts for that period and flagging discrepancies.
Why event time matters
Measurement
Fuel tank-gauge reading
Event time
Sun 21:40
Arrival time
Mon 07:10
Reconciliation period
Week 1 · Mon to Sun
Fuel receipt records · reconciliation period
Fuel tank-gauge readings · grouped by event time
Grouped by event time, the Sunday 21:40 reading stays in week 1 and is reconciled against week 1’s fuel receipts.
Same readings · grouped by arrival time
Grouped by arrival time, the same reading lands in week 2 because it arrived Monday 07:10. Week 1 is missing a reading and week 2 gains one, so both periods can show a false inventory discrepancy.
Engineering considerations
Event time vs. arrival time
Fuel tank-gauge readings and fuel receipts describe when something happened, not when the data arrived. Grouping by arrival time compares the wrong periods, so records are aligned by event time before reconciling.
Late and out-of-order data
Measurements can arrive late or out of order, and a periodic receipt covers a span of time rather than a moment. Any temporal join has to decide how long to wait for data and what to do with records that arrive after a period has been reconciled.
What counts as a discrepancy
An inventory discrepancy is a mismatch between what the fuel tank gauges show and what the delivery and receipt records imply for the same period. How large a mismatch must be before it’s flagged is a business decision as much as a technical one.
Continuous ingestion, scheduled reconciliation
Kafka ingestion is continuous, but reconciliation depends on receipt data that arrives periodically. A discrepancy can only be confirmed once the receipts covering that period are available, not the moment a gauge reading arrives.
Reporting & historical reconciliation
Separate employer · OBLS International LLC
Replacing Excel workflows with application reporting
This work was at OBLS International LLC, a different employer, and is not part of the Korner pipelines above.
Operations and Finance relied on Excel workflows for recurring reporting. My work there involved translating those workflows into application-based reporting and reconciling historical data.
What the work involved
- TypeScript and React reporting tools.
- PostgreSQL-backed business reporting.
- Operations and Finance Excel workflows translated into application-based reports.
- Python/PostgreSQL reconciliation of historical data.
Decisions & tradeoffs
The first two describe the Kafka pipeline’s design, not decisions I made alone. The third reflects my ETL automation work.
Ingestion separate from reconciliation
Decision
Fuel tank-gauge data is ingested continuously through Kafka, while reconciliation works against fuel receipt records that arrive periodically.
Tradeoff
Discrepancies can only be confirmed once receipts for a period are available, in exchange for comparing like-for-like periods.
Joining on event time, not arrival time
Decision
Fuel tank-gauge measurements and fuel receipts are aligned by when events occurred.
Tradeoff
Late and out-of-order data has to be accounted for, in exchange for comparisons that reflect what happened in each period.
Automate the routine, review the exceptions
Decision
Python handles repetitive ETL processing, with mismatches left for human review where applicable.
Tradeoff
Someone still owns exception review, but their time goes to the cases that need judgment.
Stack
- Batch ETL
- PythonPandas
- Streaming
- Apache Kafka
- Orchestration
- Apache Airflowcron
- Data
- PostgreSQL
- Sources (Korner)
- Titan CloudCStore
- Reporting (Korner)
- Microsoft Power BI
- Reporting (OBLS)
- TypeScriptReactPostgreSQL
Outcomes & lessons
Outcomes
- Korner: Python ETL automation of the recurring data processing and reconciliation handled by the centralized Accounting department across 10+ retail locations.
- Korner: A fuel inventory reconciliation pipeline that identifies discrepancies between fuel tank-gauge data and periodic fuel receipts.
- Korner: Processed operational and fuel inventory data made available for downstream Power BI reporting.
- OBLS International LLC: Operations and Finance Excel workflows translated into application-based reporting.
Lessons
- Understand why each manual step exists before automating it.
- Getting the reconciliation right depended as much on when each record was taken as on its value.