End-to-end data engineering project that simulates a fintech transaction analytics platform.
The project ingests synthetic transaction events, processes them through a layered data lake, loads business metrics into PostgreSQL, transforms them with dbt, orchestrates the analytical workflow with Airflow, and exposes the data to Metabase for reporting.
The goal is to present a realistic, reproducible data platform with clear ingestion, processing, modeling, orchestration, and reporting layers.
- Overview
- Running the Project
- Operations
- Technical Details
- Portfolio Notes
Fintech companies process large volumes of payment transactions. Business teams need reliable access to metrics such as:
- daily revenue,
- transaction volume,
- average transaction value,
- merchant performance,
- fraud indicators,
- platform-level KPIs.
Raw event data is not suitable for direct reporting. This project transforms raw transaction events into clean, business-ready analytical tables.
flowchart LR
A[Python transaction producer] --> B[Kafka topic: transactions]
B --> C[Spark Structured Streaming]
C --> D[Bronze Parquet data lake]
D --> E[Spark Silver job]
E --> F[Silver Parquet data lake]
F --> G[Spark Gold job]
G --> H[Gold Parquet data lake]
H --> I[PostgreSQL warehouse]
I --> J[dbt staging and marts]
J --> K[Metabase dashboard]
L[Airflow]
L --> C
L --> E
L --> G
L --> I
L --> J
producer/producer.pygenerates synthetic fintech transactions.- Kafka stores events in the
transactionstopic. - Spark reads Kafka events and stores raw JSON in the Bronze layer.
- Spark parses, validates, and standardizes data into the Silver layer.
- Spark creates Gold business aggregates.
- Gold datasets are loaded into PostgreSQL through JDBC.
- dbt builds staging models and analytical marts.
- Metabase connects to PostgreSQL for dashboarding.
- Airflow orchestrates the analytical part of the pipeline.
| Area | Technology | Purpose |
|---|---|---|
| Event generation | Python | Synthetic fintech transaction producer |
| Streaming | Apache Kafka | Event transport and decoupling |
| Processing | Apache Spark | Streaming ingestion, validation, aggregation |
| Data lake | Parquet | Bronze, Silver, Gold storage |
| Warehouse | PostgreSQL | Serving layer for analytics |
| Transformations | dbt | SQL models, tests, documentation |
| Orchestration | Apache Airflow | Pipeline execution and dependency management |
| BI | Metabase | Dashboard and data exploration |
| Runtime | Docker Compose | Local reproducible environment |
.
+-- airflow/
| +-- dags/
| +-- Dockerfile
| +-- entrypoint.sh
| +-- wait-for-postgres.sh
+-- dbt/
| +-- models/
| | +-- staging/
| | +-- marts/
| +-- dbt_project.yml
| +-- profiles.yml
+-- docs/
| +-- images/
+-- metabase/
| +-- backup/
+-- scripts/
| +-- export_metabase.ps1
| +-- import_metabase.ps1
| +-- reset_pipeline_data.ps1
+-- producer/
| +-- Dockerfile
| +-- producer.py
| +-- requirements.txt
+-- spark/
| +-- app/
| | +-- bronze_available_now_job.py
| | +-- bronze_stream.py
| | +-- silver_job.py
| | +-- gold_job.py
| | +-- load_gold_to_postgres.py
| | +-- schemas.py
| +-- conf/
| +-- Dockerfile
+-- postgres/
| +-- initdb/
+-- docker-compose.yml
+-- README.md
+-- ARCHITECTURE.md
The final analytics layer is exposed in Metabase through a dashboard built on top of dbt marts and PostgreSQL warehouse tables.
For a quick technical review:
- Start the stack with Docker Compose.
- Generate transaction events for 1-2 minutes.
- Stop the producer to keep the dataset stable.
- Trigger the Airflow DAG
daily_fintech_pipeline. - Check that dbt tests pass.
- Open the Metabase dashboard and review platform KPIs, revenue trends, fraud rate, and merchant performance.
This walkthrough demonstrates the complete path from generated Kafka events to business-facing analytics.
The repository includes a lightweight GitHub Actions workflow for the dbt layer.
The workflow starts a temporary PostgreSQL service, creates minimal source tables, loads sample records, and runs:
dbt debug --project-dir dbt
sqlfluff lint dbt/models
dbt build --project-dir dbtThis validates dbt connection configuration, SQL style, model compilation, model dependencies, and dbt data tests.
The CI workflow does not run the full Kafka/Spark/Airflow/Metabase stack. The full platform is intended to be run locally with Docker Compose.
Install:
- Docker Desktop,
- Docker Compose,
- Git.
Copy the example environment file:
cp .env.example .envOn Windows PowerShell:
Copy-Item .env.example .envThe file contains:
KAFKA_BOOTSTRAP_SERVERS=kafka:9092
KAFKA_TOPIC=transactionsdocker compose up --build -dNote: after the platform starts, the producer container continuously sends transaction events to Kafka. For controlled runs, you can stop only this container after enough data has been generated:
docker compose stop producerdocker compose psExpected core services:
postgreskafkakafka-uiproducersparkdbtairflowmetabase
| Service | URL |
|---|---|
| Kafka UI | http://localhost:8080 |
| dbt docs server | http://localhost:8081 after running dbt docs serve |
| Airflow UI | http://localhost:8088 |
| Metabase | http://localhost:3000 |
| PostgreSQL | localhost:5432 |
Airflow local credentials:
user: admin
password: admin
These credentials are intended only for local development.
PostgreSQL local credentials:
user: fintech
password: fintech
Local PostgreSQL databases:
fintech -> warehouse tables and dbt models
airflow -> Airflow metadata
metabase -> Metabase metadata, dashboards, questions, users, and settings
Use this section when you want to run the whole platform from scratch and verify that data moved through every important layer.
Make sure Docker Desktop is running before using Docker Compose.
On Windows PowerShell, you can verify Docker with:
docker versionIf this is your first run, create .env from the provided example:
Copy-Item .env.example .envThe default values are enough for local development.
From the project root directory, run:
docker compose up --build -dThis starts Kafka, Kafka UI, the transaction producer, Spark, PostgreSQL, dbt, Airflow, and Metabase.
The first run can take a few minutes because Docker needs to build images and initialize services.
Run:
docker compose psExpected result: the main services should be running or healthy.
Important containers:
kafkakafka-uiproducersparkpostgresdbtairflowmetabase
The producer container continuously sends transaction events to Kafka.
For a controlled local run, let it run for 1-2 minutes so Kafka has data available for the pipeline.
Open Kafka UI:
http://localhost:8080
Check that the transactions topic exists and contains messages.
This confirms:
Python Producer -> Kafka
After Kafka contains messages, stop only the producer so it does not keep generating data forever:
docker compose stop producerThis keeps the rest of the platform running.
Open:
http://localhost:8088
Use the local development credentials:
user: admin
password: admin
In Airflow:
- Open the
Dagspage. - Find
daily_fintech_pipeline. - Enable the DAG if it is paused.
- Click
Trigger. - Wait until all tasks finish with
success.
Expected task order:
ingest_bronze_layer_for_demo -> build_silver_layer -> build_gold_layer -> load_gold_to_postgres -> dbt_run -> dbt_test
This confirms:
Kafka -> Bronze Parquet -> Silver Parquet -> Gold Parquet -> PostgreSQL -> dbt marts
The first task, ingest_bronze_layer_for_demo, uses Spark Structured Streaming with availableNow=True. It reads the currently available Kafka messages, writes them to Bronze Parquet, and then finishes. This makes it suitable for a finite Airflow task in a local environment.
Check that Bronze files were created:
docker exec spark ls -R /opt/spark-data/bronze/transactionsThis confirms:
Kafka -> Bronze
Check when the warehouse tables were loaded:
docker exec postgres psql -U fintech -d fintech -c "select max(warehouse_loaded_at) from daily_transaction_metrics;"docker exec postgres psql -U fintech -d fintech -c "select max(warehouse_loaded_at) from merchant_metrics;"Check row counts:
docker exec postgres psql -U fintech -d fintech -c "select count(*) from daily_transaction_metrics;"docker exec postgres psql -U fintech -d fintech -c "select count(*) from merchant_metrics;"If rows exist and warehouse_loaded_at is filled, the Gold-to-PostgreSQL load worked.
Check one of the final dbt models:
docker exec postgres psql -U fintech -d fintech -c "select * from mart_platform_summary;"Run dbt tests manually if needed:
docker exec dbt dbt testIf dbt tests pass, the warehouse models meet the declared data quality rules.
Open PySpark inside the Spark container:
docker exec -it spark /opt/spark/bin/pysparkCheck Silver records:
silver_df = spark.read.parquet("/opt/spark-data/silver/transactions")
silver_df.show(10, truncate=False)
silver_df.printSchema()Check Gold records:
gold_df = spark.read.parquet("/opt/spark-data/gold/daily_transaction_metrics")
gold_df.show(10, truncate=False)Exit PySpark:
exit()This confirms that the data lake layers were created correctly.
Open:
http://localhost:3000
Use Metabase to inspect the PostgreSQL warehouse tables and dashboard.
Main tables to check:
daily_transaction_metricsmerchant_metricsmart_daily_kpismart_merchant_performancemart_platform_summarymart_top_merchants
When you are done:
docker compose downIf you only want to stop data generation but keep the platform running:
docker compose stop producerUse this section when you want to generate a fresh dataset and rerun the pipeline without deleting Metabase dashboards.
This is the recommended reset option for controlled local runs.
It deletes:
- Bronze, Silver, and Gold Parquet files from
spark/data, - Spark streaming checkpoints from
spark/data/checkpoints, - warehouse tables and dbt-created models stored in PostgreSQL,
- Kafka topic data from the
transactionstopic.
It does not delete:
- Metabase dashboards,
- Metabase questions,
- Metabase database connection setup,
- source code,
- Docker images.
docker compose up -dFrom the project root:
.\scripts\reset_pipeline_data.ps1The script:
- stops the producer,
- removes Spark lake files and checkpoints,
- drops warehouse and dbt tables from the
fintechdatabase, - deletes and recreates the Kafka topic
transactions, - keeps Metabase metadata untouched.
If you want to keep existing Kafka messages, run:
.\scripts\reset_pipeline_data.ps1 -SkipKafkaResetStart the producer:
docker compose start producerLet the producer run for 1-2 minutes.
Then stop only the producer:
docker compose stop producerOpen Airflow:
http://localhost:8088
Use:
user: admin
password: admin
Trigger:
daily_fintech_pipeline
Expected task order:
ingest_bronze_layer_for_demo -> build_silver_layer -> build_gold_layer -> load_gold_to_postgres -> dbt_run -> dbt_test
Check warehouse load timestamps:
docker exec postgres psql -U fintech -d fintech -c "select max(warehouse_loaded_at) from daily_transaction_metrics;"Check row counts:
docker exec postgres psql -U fintech -d fintech -c "select count(*) from daily_transaction_metrics;"Check Bronze files:
docker exec spark ls -R /opt/spark-data/bronze/transactionsIf these checks return data, the pipeline was rebuilt from a clean local state.
Use this only if you also want to delete Metabase dashboards and all local PostgreSQL data.
Warning: this removes the PostgreSQL Docker volume postgres_data. In this project, that volume stores both:
database fintech -> warehouse and dbt tables
database airflow -> Airflow metadata
database metabase -> Metabase dashboards, questions, users, and settings
Full reset command:
docker compose down -vAfter this, Metabase setup and dashboards need to be recreated.
Metabase stores dashboards, questions, collections, users, and connection metadata in its application database. In this project, that application database is the local PostgreSQL database named metabase.
To avoid losing dashboard work during local resets, back up the Metabase metadata database after creating or updating dashboards.
From the project root:
.\scripts\export_metabase.ps1The backup is written to:
metabase/backup/metabase_metadata.dump
This uses PostgreSQL pg_dump against the local metabase database. It works with the open-source Metabase Docker image and stores dashboards, questions, collections, users, and Metabase database connections.
Recommended workflow:
- Create or update dashboards in Metabase.
- Run
.\scripts\export_metabase.ps1. - Commit the backup together with the project.
Restoring replaces the local Metabase metadata database, so the script requires the explicit -Force flag.
Run:
.\scripts\import_metabase.ps1 -ForceThen open Metabase:
http://localhost:3000
If the warehouse connection is missing or invalid, reconnect Metabase to the local PostgreSQL warehouse manually:
host: postgres
port: 5432
database: fintech
user: fintech
password: fintech
docker compose up --build -ddocker compose downdocker compose logs -f producer
docker compose logs -f spark
docker compose logs -f airflowThe producer service runs continuously and keeps generating Kafka events. For controlled local testing, stop it when you have enough data. This prevents the Bronze layer from growing too quickly and keeps Spark jobs faster.
Stop only the producer:
docker compose stop producerStart it again:
docker compose start producerdocker exec spark /opt/spark/bin/spark-submit /opt/spark-apps/app/bronze_available_now_job.pyUse this version for finite Airflow runs.
It processes currently available Kafka messages, writes them to Bronze Parquet, and then finishes automatically. This behavior allows the Airflow task to complete successfully and the DAG can continue to Silver, Gold, PostgreSQL, and dbt.
docker exec spark /opt/spark/bin/spark-submit /opt/spark-apps/app/bronze_stream.pyThis is the production-style version of Bronze ingestion.
It keeps running until stopped with Ctrl + C, continuously reading new Kafka messages and appending them to the Bronze layer. In a real production setup, this type of job would usually be managed as a separate long-running streaming process.
For normal local runs, use the Airflow DAG. Do not run bronze_stream.py while the Airflow DAG is running, because both jobs write to the same Bronze folder:
/opt/spark-data/bronze/transactions
docker exec dbt dbt rundocker exec dbt dbt testdocker exec dbt dbt docs generatedocker exec dbt dbt docs serve --host 0.0.0.0 --port 8081Before triggering the Airflow DAG, make sure Kafka contains messages. The DAG will run the finite Bronze ingestion task automatically.
After triggering daily_fintech_pipeline in Airflow, all tasks should finish with success:
ingest_bronze_layer_for_demo -> build_silver_layer -> build_gold_layer -> load_gold_to_postgres -> dbt_run -> dbt_test
Check when warehouse tables were last loaded:
docker exec postgres psql -U fintech -d fintech -c "select max(warehouse_loaded_at) from daily_transaction_metrics;"docker exec postgres psql -U fintech -d fintech -c "select max(warehouse_loaded_at) from merchant_metrics;"Check row counts:
docker exec postgres psql -U fintech -d fintech -c "select count(*) from daily_transaction_metrics;"docker exec postgres psql -U fintech -d fintech -c "select count(*) from merchant_metrics;"Check a dbt mart:
docker exec postgres psql -U fintech -d fintech -c "select * from mart_platform_summary;"Note: PostgreSQL timestamps may be shown in UTC. For example, 19:24 UTC equals 21:24 in Poland during summer time.
The normal producer generates valid transactions. To test Silver-layer rejection logic, send five intentionally invalid events:
For a controlled test, stop the normal producer first so it does not keep generating valid events in the background:
docker compose stop producerIf the stack was already running before this script was added, rebuild the producer image first:
docker compose build producerdocker compose run --rm producer python send_invalid_transactions.pyTrigger daily_fintech_pipeline in Airflow. The first DAG task will ingest the invalid Kafka events into Bronze, and the Silver task will write invalid rows to the rejected-records path.
After the test, start the normal producer again only if you want to generate more live data:
docker compose start producerAfter the DAG finishes, inspect rejected records:
docker exec -it spark /opt/spark/bin/pysparkInside PySpark:
df = spark.read.parquet("/opt/spark-data/silver/rejected_transactions")
df.groupBy("rejection_reason").count().show(truncate=False)
df.select("rejection_reason", "raw_event_json").show(20, truncate=False)Expected rejection reasons include:
missing_transaction_idnon_positive_amountmissing_merchantinvalid_event_timestampmissing_customer_idmissing_amountmissing_currencymissing_payment_methodmissing_fraud_flag
Expected grouped result:
+-------------------------------------------------------------------------------------------------+-----+
|rejection_reason |count|
+-------------------------------------------------------------------------------------------------+-----+
|missing_transaction_id |1 |
|non_positive_amount |1 |
|missing_merchant |1 |
|invalid_event_timestamp |1 |
|missing_customer_id, missing_amount, missing_currency, missing_payment_method, missing_fraud_flag|1 |
+-------------------------------------------------------------------------------------------------+-----+
Stores raw Kafka events as JSON strings with Kafka metadata.
Purpose:
- preserve raw input,
- support auditability,
- allow reprocessing.
Stores parsed and validated transaction records.
Main transformations:
- JSON parsing,
- type casting,
- timestamp normalization,
- required field validation,
- positive amount validation,
- rejected-record capture with rejection reasons,
- ingestion metadata.
Rejected records are written separately to:
/opt/spark-data/silver/rejected_transactions
This keeps invalid records auditable without allowing them into analytical datasets.
Stores business-ready aggregates.
Current Gold datasets:
daily_transaction_metricsmerchant_metrics
Spark loads Gold datasets into PostgreSQL.
Base warehouse tables:
daily_transaction_metricsmerchant_metrics
Both warehouse tables include warehouse_loaded_at, a technical timestamp showing when the current Gold dataset was loaded into PostgreSQL.
The current loading strategy is full refresh: each DAG run truncates and reloads the warehouse tables from the current Gold Parquet datasets. This keeps the local pipeline reproducible and easy to debug while preserving dbt dependencies.
dbt then builds analytical models:
mart_daily_kpismart_merchant_performancemart_merchant_tiermart_platform_summarymart_top_merchants
dbt tests validate model assumptions such as not-null fields, accepted values, uniqueness, and relationships.
The project contains an Airflow DAG:
daily_fintech_pipeline
Current DAG sequence:
ingest Bronze layer -> build Silver layer -> build Gold layer -> load Gold data to PostgreSQL -> dbt run -> dbt test
The first DAG task uses spark/app/bronze_available_now_job.py. This is a finite Spark Structured Streaming job using availableNow=True: it processes Kafka messages that are currently available, writes them to Bronze Parquet, and then exits.
The project also keeps spark/app/bronze_stream.py as the production-style long-running streaming variant:
docker exec spark /opt/spark/bin/spark-submit /opt/spark-apps/app/bronze_stream.pyIn a production setup, the long-running Bronze stream would usually run as a separate continuously managed process. For local development, the finite Airflow task is easier to run and verify end-to-end.
Summary:
bronze_available_now_job.py -> finite Airflow ingestion
bronze_stream.py -> production-style long-running stream
The platform supports reporting for:
- daily transaction KPIs,
- total revenue,
- average transaction amount,
- fraud rate,
- merchant revenue,
- merchant transaction volume,
- top merchants,
- merchant tier classification,
- platform-level summary metrics.
These outputs are consumed by Metabase dashboards.
Kafka simulates event-driven ingestion. It decouples the transaction producer from downstream processing and reflects how real transactional systems often publish events.
Spark demonstrates distributed processing concepts and supports both streaming ingestion and batch-style transformations over data lake files.
Parquet is a columnar storage format commonly used in data lakes. It is efficient for analytical workloads and works well with Spark.
PostgreSQL acts as a lightweight local serving warehouse. It keeps the project easy to run while still supporting realistic SQL analytics and BI integration.
dbt separates analytical SQL transformations from processing code. It adds tests, documentation, and a clear modeling layer.
Airflow shows workflow orchestration, dependency management, and repeatable execution of the analytical pipeline.
The project is designed for local reproducibility. Docker Compose is enough to run the data platform architecture without additional orchestration infrastructure.
This project demonstrates practical Data Engineering skills across the full data platform lifecycle:
- Event-driven ingestion using Python, Kafka, and Spark Structured Streaming.
- Medallion architecture with Bronze, Silver, and Gold Parquet layers.
- Data validation and rejected-record handling in the Silver layer.
- Business aggregation design for transaction, merchant, fraud, and platform KPIs.
- Warehouse loading from Spark to PostgreSQL through JDBC.
- Analytics engineering with dbt staging models, marts, tests, and documentation.
- Workflow orchestration with Airflow DAG dependencies.
- BI integration with Metabase on top of warehouse marts.
- Local reproducibility with Docker Compose and documented operational scripts.
- Clear separation between analytical data, Airflow metadata, and Metabase metadata.
This project intentionally uses a compact local architecture:
- Docker Compose is used instead of Kubernetes to keep the project easy to run locally.
- PostgreSQL is used as a lightweight warehouse instead of a cloud warehouse.
- Parquet is used as a local data lake format for Bronze, Silver, and Gold layers.
- Full refresh loading is used for deterministic local runs instead of incremental warehouse loading.
- The Airflow DAG uses an available-now Bronze ingestion job, while
bronze_stream.pyshows the production-style long-running streaming variant. - Metabase metadata is backed up separately from pipeline data so dashboards can survive local data resets.
Potential high-value improvement:
- add a small scripted project walkthrough.
Lower-priority improvements:
- cloud deployment,
- Snowflake or BigQuery warehouse,
- Great Expectations,
- monitoring stack,
- Kubernetes.
These are intentionally outside the current scope to keep the project focused and reproducible.