Daily financial ETL that pulls core banking, customer master, partner files, and FX rates into a Snowflake warehouse with fraud scoring, SCD-2 dimensions and SOX controls applied along the way.
Financial institutions consolidate accounting and risk inputs from several upstream systems on a fixed schedule. The patterns are well known but tedious to get right: incremental extraction, multi-currency normalisation, rules-based fraud detection, slowly-changing dimension management, and data-quality gates ahead of the warehouse. This is the reference build a finance data team can lift wholesale.
Oracle (core banking) via CDC, Postgres (customer master) via Airbyte-style snapshot, SFTP partner files via inotify-on-arrival, FX rates from a REST API on a fixed schedule. All write to S3 as hive-partitioned Parquet.
Transactions are normalised to a reporting currency using the close-of-day FX snapshot. Customers are RFM-scored (recency, frequency, monetary) for downstream segmentation.
Four rules run per row โ velocity (transaction count per window), high-value flag (โฅ $10K), geo-velocity (impossible-travel), and z-score outlier (โฅ 3ฯ rolling). Hits land in a Redis fraud-alert stream for real-time consumers and a fact table for analytics.
Six SOX controls evaluated pre-load โ completeness, freshness, referential integrity, primary-key uniqueness, range, sum-check. SCD-2 MERGE writes versioned dimension history.
GE suite runs post-transform with fail-fast on critical expectations. A second pre-normalisation pass catches malformed currency codes before the cost of FX conversion is paid.
Versioned dimension history lets every downstream report be reproduced as-of any prior date. The MERGE pattern is durable across schema changes; delete-and-reload silently destroys history.
Simple, fast, no extra infra. Sufficient for single-worker setups and dev environments.
Multi-worker deployments must move the window to Redis sorted sets โ flagged in the README, swap-only change to the rule implementation.
Universal compatibility with partner SFTPs. inotify-on-arrival is wired where the partner supports a notification push; the rest poll on a 5-minute cadence.
A 5-minute floor on partner file latency. Acceptable for batch consumers.
Catching dirty currency codes after FX conversion wastes compute. The pre-pass is cheaper and prevents the failure mode that costs the most.