Finance Data ETL to Benchmarking Platform

Summary

A scheduled ETL consolidating billing, credits, settlements, contracts, and aged balances from an Amazon Redshift warehouse and a SQL Server CRM, delivered to a third-party benchmarking platform’s API, fully in-memory on Azure Functions. Every extraction query runs behind parameterized builders with explicit column contracts, and per-source failure isolation means one unavailable store degrades the run rather than aborting it.

Architecture

• Every date filter uses a half open window, with an exclusive upper bound computed by advancing one day, rather than an inclusive range. The version history shows this replaced an inclusive range that silently dropped rows timestamped after midnight on the final day of the period, which is a real defect on a month end financial extract.
• Each query is paired with its column list as a tuple, and the list is applied at the point the result becomes a dataframe. That renames the physical source columns into one canonical vocabulary shared across both stores, so downstream code never sees warehouse naming or CRM naming.
• Failure isolation is finer than per source: each individual query is independently guarded, so one failed domain does not take down the other three that share its connection. Connection acquisition itself returns nothing rather than raising, which is what allows the orchestrator to skip a whole source cleanly.
• The retry decorator retries only transient network and timeout exception types and returns nothing on anything else, so a deterministic failure such as a malformed query fails immediately instead of consuming the full retry budget. It also detects and handles coroutine targets, so one decorator serves both call styles.

Highlights

• Every extraction query behind parameterized builders with explicit column contracts: schemas asserted, not inferred.
• Per-query and per-source failure isolation: one unavailable store degrades the run instead of aborting it.
• Historical backfill as a first-class operation via a month-slicing CLI.

Technologies

• Python
• Pandas
• Amazon Redshift
• SQL Server
• Azure Functions