dlimbachiya7/ifta-aws-pipeline

1. Overall shape

This repo implements an AWS medallion-style data pipeline for IFTA fuel-tax evidence. The core boundary is between immutable raw evidence in S3 and curated analytical tables in downstream S3-backed Glue/Athena datasets.

2. Infrastructure layer

terraform/ provisions the cloud foundation: S3 buckets, KMS encryption, IAM roles/policies, Glue Data Catalog resources, and Athena-related setup. This is the deployment boundary for the whole system; the application logic assumes those resources already exist.

3. Raw zone

The raw zone is the legal/audit record of submitted evidence. It stores original files as-is in S3 and is treated as immutable/version-protected. Inputs include heterogeneous source formats: Excel trip logs, scanned PDFs for distance logs, and fuel receipts.

4. Bronze extraction subsystem

glue_jobs/glue_extract.py is the main code artifact in the repo. It runs as an AWS Glue Python Shell job and converts raw evidence into typed Parquet records in the bronze layer, while preserving source lineage and extraction metadata on every row.

5. Extraction technologies and source-specific handling

The extraction job branches by file type:

• Textract start_expense_analysis for receipt PDFs

• Textract start_document_analysis with TABLES for scanned log PDFs

• pandas read_excel for Excel trip logs

This is the primary transformation boundary where unstructured or semi-structured evidence becomes tabular data.

6. Bronze schema contract

Bronze records carry both business fields and lineage fields such as src_uri, src_sha256, src_page_or_row, extraction_method, extraction_confidence, ingested_at, and processed_at. This makes bronze the canonical typed extraction layer and the handoff point for all downstream cleaning.

7. Silver and gold transformation subsystem

A separate Glue Studio Visual ETL job consumes bronze and produces both silver and gold outputs. Silver normalizes records into curated entities like silver_juris_km and silver_fuel, while gold aggregates by IFTA period and jurisdiction into gold_ifta_reconciliation.

8. Business reconciliation logic

The gold layer is the audit-facing analytics boundary. It performs a full outer join between kilometers and fuel totals to expose mismatches, then derives audit flags such as “no fuel in jurisdiction” or “fuel but no travel.” This layer is optimized for filing/reconciliation, not raw analysis.

9. Catalog and query access

All curated datasets are registered in the AWS Glue Data Catalog under the ifta_medallion database and queried through Athena. The catalog is the metadata boundary that makes bronze/silver/gold discoverable and queryable without embedding storage details into consumers.

10. Data quality and lineage model

docs/data-quality-codes.md and docs/data-model.md describe a design choice: quality issues are recorded explicitly as data rather than silently corrected. Combined with the lineage columns, this allows an auditor to trace any gold record back to its source byte and inspect why a record was accepted, corrected, or flagged.

11. Operational flow

The intended runtime sequence is: deploy infra with Terraform, upload the Glue extraction script, land files in raw S3, run the extraction job to populate bronze, run a crawler to register tables, run the Glue Studio ETL to produce silver/gold, then query results in Athena. Each stage can be rerun independently, with recovery costs increasing as you move from raw to gold.

12. Repository structure as architecture

The repo is intentionally split by concern: terraform/ for cloud provisioning, glue_jobs/ for executable extraction logic, and docs/ for schema/rationale/quality rules. That separation mirrors the runtime architecture: infrastructure, ingestion, transformation, and governance.