ETL pipeline

Problem

Operational data in MongoDB is nested and inconsistent, which makes it hard to report on. It needs to become clean, structured data that is ready for analytics.

Features

L1 Raw. A full load first, then incremental loads using timestamps or change streams. Data is stored as JSONB with load metadata (batch ID, load time, source collection) and never changed, so any later layer can be rebuilt from it.

L2 Cleaned.

• Nested documents flattened, arrays split into child tables.
• Type casting, deduplication and null handling.
• Validation rules, with bad records sent to quarantine.
• Standardization and schema drift handling.
• Upserts, so reruns are safe.

L3 Business. A star schema with fact and dimension tables, SCD Type 2 history on dimensions, enrichment, derived columns, and daily, weekly and monthly aggregates.

Segmentation. Runs after L3.

• Features from L3: RFM, average order value, categories, activity trend.
• K-Means assigns each customer a segment.
• Scheduled retraining with model versioning.
• Segments, segment history and aggregates written back to PostgreSQL.
• An alert if segment sizes shift suddenly.

Orchestration. One Airflow DAG runs L1, L2, L3 and segmentation in order, with retries, failure alerts, a run log and quality checks at each stage.

How it works

Data moves from MongoDB through the three layers, each one stricter than the last. Segmentation reads the business layer and writes its results back to PostgreSQL, ready for reporting.