Blog

In modern retail and digital banking, data infrastructure is far more than a reporting tool: it represents the operational and analytical backbone for real-time fraud prevention, credit underwriting and risk scoring, regulatory reporting (BACEN/LGPD), and hyper-personalized financial services.

However, building an enterprise-grade banking data platform introduces mission-critical technical challenges that break traditional data warehousing paradigms:

• Massive volume & temporal granularity: Millions of transactions per day spanning asynchronous channels (PIX, wire transfers, debit/credit cards, ATM operations).
• Strict relational integrity constraints: Payment and settlement records cannot point to non-existent accounts, phantom cards, or unregistered merchants.
• Non-blocking Data Quality assurance: Corrupted or malformed incoming payloads must never crash long-running production pipelines, nor should they quietly pollute downstream analytical marts.
• Regulatory ACID guarantees and Time Travel: The regulatory mandate to inspect, verify, and audit historical balance and ledger snapshots with total transactional consistency.

To solve these engineering challenges under production conditions, I designed and implemented the Banking Data Lakehouse (Banking Pipeline) — a robust, containerized, and fully reproducible end-to-end data platform.

1. End-to-End Architectural Blueprint

The platform implements the Medallion Architecture (Bronze -> Silver -> Gold) combined with a Modern Open Lakehouse pattern powered by Apache Iceberg and S3-compatible object storage (MinIO):

                         DATA SOURCES
                Synthetic Digital Banking Engine
                               │
                               ▼
                      STREAMING INGESTION
                  PyArrow Chunked Batch Writer
                               │
                               ▼
                         🥉 BRONZE
                 Raw Parquet (year=YYYY/month=MM)
                               │
                               ▼
                   APACHE SPARK ENGINE
             Distributed Validation & Cleansing
                               │
                     ┌─────────┴─────────┐
                     │                   │
                     ▼                   ▼
                  SILVER            QUARANTINE
            Validated Parquet      Invalid Records / DLQ
                     │
                     ▼
                  🥇 GOLD
             Analytical Marts & Aggregations
                     │
         ┌───────────┴───────────┐
         ▼                       ▼
   APACHE ICEBERG            DBT CORE
  ACID Lakehouse (MinIO)   DuckDB Semantic Layer
         │                       │
         └───────────┬───────────┘
                     ▼
             APACHE AIRFLOW
       CeleryExecutor Orchestration & Circuit Breakers

Multi-Tiered Technology Stack

• Synthetic Domain Generator: Python, Faker, NumPy, Pandas (deterministic generation with relational integrity and power-law distribution skew).
• Streaming Ingestion: PyArrow (zero-copy memory-mapped record batches writing snappy-compressed columnar Parquet).
• Distributed Processing Engine: Apache Spark 3.5 / PySpark (distributed joins, anti-join quarantine filtering, and window-based deduplication).
• Storage & Lakehouse Table Format: Apache Iceberg + MinIO (S3 API compatibility, snapshot isolation, hidden partitioning, metadata-driven schema evolution).
• Semantic Modeling & Contracts: dbt Core + DuckDB (modular Staging/Intermediate/Marts layers, automated relational integrity and data contract tests).
• Workflow Orchestration: Apache Airflow 2.10 (CeleryExecutor, automated retry policies, Docker container execution, and automated Quality Gate circuit breakers).
• Containerized Infrastructure: Docker & Docker Compose orchestrating 7 isolated microservices.

2. Relational Domain Modeling & Synthetic Data Synthesis

To mirror the real-world complexity of digital neo-banks, the domain was modeled across 7 interconnected relational entities with strict cardinality rules:

                       ┌──────────────┐
                       │   CUSTOMER   │ (100,000 records)
                       └──────┬───────┘
                              │
                    ┌─────────┴─────────┐
                    ▼                   ▼
             ┌─────────────┐     ┌─────────────┐
             │   ACCOUNT   │     │    LOAN     │ (30,000 contracts)
             └──────┬──────┘     └──────┬──────┘
                    │                   │
          ┌─────────┴─────────┐         ▼
          ▼                   ▼   ┌──────────────┐
   ┌─────────────┐     ┌──────────┴──┐│ LOAN_PAYMENT │ (~740k installments)
   │    CARD     │     │ TRANSACTION │└──────────────┘
   └─────────────┘     └──────┬──────┘ (5,000,000 events)
  (120k records)              │
                              ▼
                       ┌──────────────┐
                       │   MERCHANT   │ (10,000 records)
                       └──────────────┘

Volumetric Scale & Realistic Data Distributions

• Customers (100k): Partitioned into realistic socioeconomic tiers (BASIC, STANDARD, PREMIUM) governing credit limits and transaction velocity.
• Accounts (~150k): Checking (CHECKING) and interest-bearing savings (SAVINGS) accounts with active/blocked lifecycle states.
• Transactions (5,000,000 events): Multi-channel events covering PIX, TED, debit cards, credit purchases, cash deposits, and ATM withdrawals over multiple fiscal periods.
• Data Synthesis Realism: Instead of pure random noise, transactions follow a log-normal / Pareto distribution simulating real consumer behavior: high-frequency small-value transactions (coffee, groceries) contrasted with low-frequency high-ticket transactions (rent, electronics, investments).

3. Bronze Layer: High-Throughput Streaming Ingestion Without OOM

A classic failure mode in data engineering is attempting to materialize multi-million row datasets into memory all at once using standard Pandas read_csv or to_parquet, which inevitably triggers Out-Of-Memory (OOM) fatal crashes in memory-constrained environments.

To achieve maximum throughput with a minimal memory footprint (under 300MB RAM), ingestion was implemented using PyArrow Chunked Streaming RecordBatches:

# Streaming batch writer pattern
import pyarrow as pa
import pyarrow.parquet as pq

def stream_records_to_bronze(generator, batch_size=100_000, output_path='data/bronze/transactions'):
    schema = get_transaction_pyarrow_schema()
    writer = None
    
    for batch in generator.iter_batches(batch_size):
        arrow_table = pa.Table.from_batches([batch], schema=schema)
        if writer is None:
            writer = pq.ParquetWriter(f'{output_path}/data.parquet', schema, compression='SNAPPY')
        writer.write_table(arrow_table)
        
    if writer:
        writer.close()

Key Storage Optimizations:

• Columnar Parquet Storage: Snappy compression provides a ~4.5x disk space reduction compared to raw CSV/JSON payloads.
• Temporal Partitioning (year=YYYY/month=MM): Queries filtering for specific fiscal quarters leverage partition pruning to skip 90%+ of irrelevant data files on disk during Spark scans.

4. Silver Layer: Distributed Cleansing & The Quarantine Pattern

The Silver layer transforms raw, untrusted Bronze files into cleansed, validated, and normalized datasets using Apache Spark.

The Data Quarantine Architecture (Dead Letter Queue)

In enterprise data engineering, encountering records with broken foreign keys or invalid business states presents an architectural dilemma:

1. Drop invalid rows silently: Destroys auditability, obfuscates data loss, and violates regulatory compliance.
2. Fail the entire batch job: A single bad payload halts analytics delivery for the whole organization.

The Banking Pipeline solves this with a Dual-Sink Quarantine Routing Pattern:

# PySpark dual-sink validation pattern
from pyspark.sql import functions as F

# 1. Foreign Key Verification via Left Anti Join
df_valid_tx = df_raw_tx.join(df_accounts, 'account_id', 'inner')
df_invalid_tx = df_raw_tx.join(df_accounts, 'account_id', 'left_anti') \
                        .withColumn('rejection_reason', F.lit('FOREIGN_KEY_NOT_FOUND_ACCOUNT'))

# 2. Business Domain Rules Validation
df_business_valid = df_valid_tx.filter(
    (F.col('amount') > 0) &
    (F.col('currency') == 'BRL') &
    (F.col('status').isin('COMPLETED', 'PENDING', 'REVERSED'))
)

# 3. Write clean records to Silver and quarantined records to DLQ
df_business_valid.write.mode('overwrite').parquet('data/silver/transactions')
df_invalid_tx.write.mode('append').parquet('data/quarantine/transactions')

Core Invariants Enforced in Silver:

• Referential Integrity: Account existence verified on every transaction; merchant existence validated on card purchases.
• Business Invariants: Savings accounts (SAVINGS) are restricted from credit card issuance; credit card credit limits must be strictly positive (credit_limit > 0); transaction amounts must be non-zero and positive.
• Idempotent Deduplication: Deduplication using window functions ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY ingested_at DESC) ensures replayable, idempotent pipeline runs.

5. Gold Layer: Dimensional Modeling & 360° Analytical Marts

With reliable data in the Silver tier, Spark calculates dimensional star-schema marts and pre-aggregated analytics tables:

1. customer_360: Unified customer profile integrating account balances, active card count, lifetime transactional volume, credit loan exposure, and an RFM (Recency, Frequency, Monetary) engagement score.
2. daily_transaction_summary: High-performance daily aggregations tracking payment channel volume (PIX vs. TED vs. Card), average ticket size, and transaction authorization rates.
3. customer_transaction_metrics: Granular behavioral metrics including peak spending hours, single maximum ticket size, and preferred merchant categories.
4. merchant_performance: Commercial partner analytics displaying total turnover, chargeback rates, and transaction velocity.
5. loan_portfolio: Credit portfolio risk health calculating delinquency rates (30/60/90+ days past due), total disbursed capital, and recovery ratio.

6. Modern Lakehouse with Apache Iceberg & MinIO Object Storage

While raw Parquet files are efficient for basic reads, they fall short of modern enterprise requirements: they lack transaction isolation, cannot perform concurrent writes, and require expensive directory crawls to discover partition files.

We upgraded the platform to a true Open Table Format Lakehouse using Apache Iceberg backed by MinIO S3:

# Building an ACID Iceberg table with hidden partitioning in Spark
df_gold_summary.writeTo('iceberg.banking.daily_transaction_summary') \
    .tableProperty('write.format.default', 'parquet') \
    .tableProperty('write.parquet.compression-codec', 'snappy') \
    .partitionedBy(F.days('transaction_date')) \
    .createOrReplace()

┌────────────────────────────────────────────────────────┐
│               ICEBERG METADATA TREE                   │
├────────────────────────────────────────────────────────┤
│ 1. Catalog Pointer (Points to current metadata JSON)   │
│    └── v3.metadata.json                                │
│        ├── Schema & Partition Spec                     │
│        └── Manifest List (Snapshot #104928)            │
│            ├── snap-manifest-1.avro                    │
│            │   ├── data_file_a.parquet (min/max stats) │
│            │   └── data_file_b.parquet (min/max stats) │
│            └── snap-manifest-2.avro                    │
│                └── data_file_c.parquet                 │
└────────────────────────────────────────────────────────┘

Why Apache Iceberg Outperforms Traditional Lake Formats:

• ACID Transactions via Optimistic Concurrency Control (OCC): Guaranteed atomic commits prevent partial or dirty reads while jobs write new data.
• Snapshot Isolation & Time Travel: Reproduce historical financial reports at any given snapshot (SELECT * FROM table FOR SYSTEM_TIME AS OF '2026-06-01').
• Hidden Partitioning: Partition pruning is handled automatically via column transformations without requiring artificial synthetic partition columns in SQL queries.
• Column-Level Min/Max Statistics: Iceberg manifests store column bounds, skipping entire Parquet files during query planning without reading the file headers.

7. Semantic Transformations & Data Contracts with dbt + DuckDB

To separate raw data manipulation from business logic modeling, we integrated dbt (data build tool) running on top of DuckDB as a fast, in-process analytical SQL engine:

dbt Project Structure:
├── models/
│   ├── staging/        # Type casting, column renaming, null sanitization
│   │   ├── stg_accounts.sql
│   │   └── stg_transactions.sql
│   ├── intermediate/   # Business logic joins & feature enrichment
│   │   └── int_customer_transaction_summary.sql
│   └── marts/          # Dimension & Fact tables for BI consumption
│       ├── fct_daily_transactions.sql
│       └── dim_customers_360.sql

Data Contracts & Automated Testing

Every dbt model is protected by declarative data contract tests in schema.yml:

• Primary Key Uniqueness & Not-Null: Validating identity guarantees across accounts and transactions.
• Referential Integrity Relationships: Enforcing that fct_daily_transactions.account_id strictly exists in dim_accounts.
• Accepted Values: Validating transaction status enums (['COMPLETED', 'PENDING', 'REVERSED']).

8. Enterprise Orchestration with Apache Airflow & Circuit Breakers

The end-to-end pipeline is automated via a production-ready DAG in Apache Airflow leveraging CeleryExecutor:

[generate_data] ──> [build_bronze] ──> [build_silver] ──> [silver_qa]
                                                              │
                                                              ▼
[build_iceberg] <── [gold_qa] <── [build_gold] <──────────────┘

Orchestration Highlights:

• Containerized Spark Dispatching: The Airflow Celery worker communicates with the Docker socket to dispatch execution commands inside the dedicated Spark cluster container (spark_exec).
• Quality Gate Circuit Breakers (silver_qa, gold_qa): Custom validation tasks inspect row count thresholds, unexpected null ratios, and schema drift. If anomalies are detected, the pipeline halts immediately, preventing faulty data from reaching the Iceberg Lakehouse.
• Resilient Retry Policies: Exponential backoff configurations ensure automatic recovery from transient network or I/O hiccups.

9. Key Engineering Takeaways & Hard Lessons Learned

1. Tuning Spark Shuffle Partitions (spark.sql.shuffle.partitions): In local development with 4GB heap allocations, Spark's default of 200 shuffle partitions created massive task scheduling overhead and tiny I/O buffers for smaller dimension tables. Adjusting partitions dynamically (down to 8–16 partitions for staging tables) slashed pipeline runtime by 64%.
2. MinIO S3A Connector Compatibility: Connecting Spark 3.5 to MinIO required explicitly setting fs.s3a.path.style.access = true, disabling fs.s3a.fast.upload.buffer=bytebuffer, and leveraging in-memory catalog references to avoid heavy Hive Metastore container overhead.
3. Defensive Pipeline Design: The quarantine pattern completely eliminated unhandled pipeline crashes while providing the data engineering team with a structured, queryable dead-letter repository for anomaly analysis.

10. Conclusion & Roadmap

The Banking Data Lakehouse project proves that modern data engineering is about building resilient, auditable, and automated data software architectures rather than just writing SQL queries.

Future Evolution Roadmap:

• [ ] Real-time transaction ingestion via Apache Kafka and Spark Structured Streaming.
• [ ] Self-service analytical dashboards integrated via Metabase / Superset.
• [ ] Machine Learning feature store integration for real-time credit scoring with XGBoost / Feast.

Explore the complete source code, Docker configs, Airflow DAGs, and dbt models on GitHub!