Finance ETL Pipeline

Spreadsheet-based processes can create incompatible layouts and weak traceability when repeated scripts write directly to reporting surfaces.

Problem

A financial-data workflow needs a database source of truth, repeatable parsing, explicit validation tiers and a recoverable audit trail.

Operational impact

Creates a repeatable path from statement input to validated, traceable records, reducing ambiguity when financial-data loads are retried or reviewed.

Constraints

• Current parser scope is KBank PDF statements plus a basic generic fallback.
• Statements, transaction data, email content and error payloads are sensitive and are never displayed on this portfolio.

A Python pipeline design for extracting KBank e-statements, validating transactions, loading PostgreSQL idempotently and preserving operational audit records.

Inputs

• Gmail IMAP statement email
• KBank PDF attachment
• keyword categorisation rules

Implementation

• Select statement attachment, extract PDF text, normalize Thai/Buddhist-era dates and parse transactions.
• Validate PASS/WARNING/REJECT tiers before idempotent PostgreSQL loading.
• Preserve source-message, source-document, ETL-run and parser-error audit records; optional Sheets export reads downstream from PostgreSQL.
• Python orchestration, parsing, normalization and validation layers.
• PostgreSQL migrations, audit entities and idempotent upsert behavior.
• Unit/integration test files including database-gated integration coverage.

• Use PostgreSQL as source of truthReporting sheets remain downstream output rather than becoming the system of record.
• Use Decimal and database numeric values for moneyFinancial values should not depend on floating-point arithmetic.
• Make repeated loads idempotentDeterministic identity plus conflict-safe inserts prevent duplicate transactions during retries.

Evidence classes distinguish what can be inspected directly from what is documented, simulated or reconstructed.

• VERIFIED CODE
  Pipeline orchestrator

  src/pipeline/orchestrator.py

• VERIFIED CODE
  PostgreSQL migration and audit schema

  db/migrations/0001_init.sql

• VERIFIED TEST
  Unit and PostgreSQL integration test suite

  tests/

• KBank-specific parser scope; do not imply broad multi-bank production support.
• No public evidence of a successful finance ETL workflow run with live credentials.
• No claim of encryption-at-rest, retention/deletion, RLS, masking or production access-control policy.