Enterprise Data Platform.
A containerized data ingestion and analytics platform that orchestrates incremental customer data synchronization across PostgreSQL and ClickHouse using Apache Airflow.
8
Airflow pipeline tasks
2
Database systems
SHA-256
Change detection
5
Synthetic test records
01 / OVERVIEW
Reliable ingestion. Consistent data. Analytical access.
The platform ingests customer data from CSV sources, validates records, and synchronizes changes into PostgreSQL. It then replicates the relevant records into ClickHouse for analytical queries.
Apache Airflow coordinates the workflow, handles task retries and maintains execution history. FastAPI exposes analytical results to the portfolio dashboard.
Engineering objectives
Avoid unnecessary database updates.
Make repeated ingestion safe.
Keep analytical data synchronized.
Verify data consistency across systems.
Maintain execution-level audit history.
02 / ARCHITECTURE
System architecture
Airflow orchestrates ingestion and synchronization. PostgreSQL acts as the operational source of truth, while ClickHouse provides the analytical read layer.
CSV data source
Customer records
Apache Airflow
Scheduling · Orchestration · Retries · Audit
Python + Pandas
Extraction · Validation · SHA-256 hashing
PostgreSQL
Operational customer data
Ingestion audit history
ClickHouse
Analytical customer data
Aggregations and reporting
FastAPI
Read-only customer analytics API
Next.js portfolio dashboard
Customer metrics and analytical visualizations
03 / ORCHESTRATION
Airflow workflow
The customer ingestion DAG separates processing into eight sequential tasks. Each task has a defined responsibility, and retries are configured for execution failures.
Start audit
Create an execution record in PostgreSQL.
Extract
Read customer records from the CSV source.
Validate
Check required fields, duplicates, emails and dates.
Load PostgreSQL
Detect and apply new or modified customer records.
Sync ClickHouse
Replicate changed records into analytical storage.
Verify PostgreSQL
Verify source records using their expected hashes.
Verify ClickHouse
Compare analytical records with PostgreSQL.
Complete audit
Store execution status and synchronization metrics.
04 / INCREMENTAL PROCESSING
Detect changes, not just records
Each customer record receives a deterministic SHA-256 hash calculated from its relevant fields. The hash is compared with the previously stored value to determine the required action.
New records
INSERT
Customers not yet present in the target database.
Changed records
UPDATE
Customers whose current record hash differs from the stored hash.
Unchanged records
SKIP
Customers with matching hashes and no required data update.
ClickHouse versioning
The analytical table uses ReplacingMergeTree with a version column. Queries use FINAL when the latest logical version of each customer is required.
05 / ENGINEERING
Engineering capabilities
Incremental ingestion
SHA-256 record hashes identify new, changed and unchanged customers. Unchanged records are skipped.
Idempotent synchronization
Repeated processing of the same dataset does not cause unnecessary updates or duplicate logical customer records.
Execution audit
Each Airflow run records its status, source record count, insertions, updates and unchanged record counts.
Cross-database verification
Record-level verification checks expected hashes in both PostgreSQL and ClickHouse.
06 / VALIDATION
Testing and verification
The implementation includes automated tests for customer validation, deterministic hashing, incremental database operations, record-level verification and audit status handling.
PostgreSQL and ClickHouse synchronization were tested with new, modified, unchanged and repeated customer records.
07 / ANALYTICS
Analytical access
ClickHouse powers customer summary metrics, city-wise distribution and monthly signup aggregations. These results are exposed through a FastAPI endpoint and displayed on the portfolio homepage.
View live analytics section ↗08 / TECHNOLOGY