Building a Production Data Pipeline for On Demand Super Apps: An Engineering Blueprint

On-demand super apps platforms integrating food delivery, grocery marts, express courier logistics, and digital banking into a single interface present unique architectural challenges for data teams. Unlike standard e-commerce models where orders follow a linear, static checkout funnel, an on-demand ecosystem like Cambodia's Nham24 runs on hyper-local geographic boundaries, dynamic dispatching latencies, multi-party settlements, and dual-currency transactions. To transform high-velocity, deeply nested operational payloads into boardroom-ready analytics, data engineers must move beyond basic CRUD scripts toward resilient, dimensional data warehousing. Here is an architectural walkthrough of designing, transforming, and operationalizing an end-to-end data pipeline for an on demand delivery super app.

1. The Operational Reality: Why Nested JSON Breaks Traditional Analytics

Operational systems (MongoDB, DynamoDB, or transactional PostgreSQL stores) prioritize low-latency writes and flexible document schemas. A single customer profile often encapsulates nested arrays for:

Identity & Preferences: Multi-lingual flags (Khmer km and English en), notification tokens, and dietary restrictions.
Geographic Topologies: Multiple saved locations with spatial coordinates, informal alley notes, and localized administrative subdivisions (Khan and Sangkat).
Financial Rails: Multi-currency wallets balancing US Dollars (USD) and Khmer Riel (KHR), tokenized direct-clearing accounts (Bakong KHQR, ABA PayWay), and Cash on Delivery (COD) flags.

Querying this document directly with analytical tools (like Metabase, PowerBI, or Streamlit) creates massive compute bottlenecks. Running a simple question such as "What is the average order frequency for customers living in Boeung Keng Kang 1 who use Bakong KHQR?" requires costly JSON unnesting operations and sequential array scans on unindexed documents.

The solution requires dimensional normalization.

2. Deconstructing Documents into Star Schemas

A robust data pipeline transitions data through the Medallion Architecture:

Bronze Layer (Raw Landing): Exact byte-for-byte JSON extracts from API endpoints stored in an object store or local volume.
Silver Layer (Cleaned & Conformed): Deconstructed relational tables where 1:N arrays are normalized into distinct entities.
Gold Layer (Aggregated Marts): Pre-aggregated Customer 360 tables and spatial logistics views.

Relational Normalization

By separating the root profile from child collections, data teams achieve referential integrity:

dim_customers: Contains scalar attributes (tier, lifetime orders, registration channel) and audit metadata (processed_at).
customer_addresses: Normalizes latitude/longitude pins and administrative boundaries to allow standard spatial SQL aggregations (GROUP BY khan, sangkat).
customer_payment_methods: Isolates banking tokens and transaction channels, making it trivial to track digital payment adoption curves against cash.

3. Data Transformation Patterns: Functional Purity

In data engineering pipelines, transformation logic should be pure, deterministic, and idempotent. A functional transformation approach—separating single-record mapping from collection batching guarantees testability:

<- Python Code ->

def transform_customer(customer: dict) -> dict:

"""Transforms raw nested customer JSON into a clean, warehouse-ready row."""

identity = customer["identity"]

prefs = customer["preferences"]

loyalty = customer["loyalty"]

meta = customer["metadata"]

return {

"customer_id": str(customer["customer_id"]),

"full_name": identity["name"].strip(),

"phone_number": identity["phone"].strip(),

"email": identity["email"].strip().lower() if identity.get("email") else None,

"preferred_currency": prefs.get("currency", "USD").upper(),

"loyalty_tier": loyalty.get("tier", "Bronze").title(),

"lifetime_orders_count": int(loyalty.get("lifetime_orders_count", 0)),

"processed_at": datetime.now(timezone.utc),

}

<- ---------- ->

By decoupling transformations from database drivers:

Unit tests can run in milliseconds using standard mock payloads without provisioning test databases.
Transforming 1,000 or 1,000,000 records scales uniformly across multi-threaded workers, Spark nodes, or dbt models.

4. Idempotent Database Ingestion (Upserting with PostgreSQL/NEON)

Network interruptions, scheduled DAG retries, and API re-polls inevitably re-ingest duplicate records. Without idempotency, primary key constraints fail, or worse, analytics count the same customer multiple times.

Using psycopg (v3) with PostgreSQL's ON CONFLICT (...) DO UPDATE guarantees that pipelines can be re-run indefinitely without data corruption:

<- SQL ->

INSERT INTO customers ( customer_id, full_name, phone_number, email, loyalty_tier, lifetime_orders_count, processed_at ) VALUES (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (customer_id) DO UPDATE SET full_name = EXCLUDED.full_name, phone_number = EXCLUDED.phone_number, loyalty_tier = EXCLUDED.loyalty_tier, lifetime_orders_count = EXCLUDED.lifetime_orders_count, processed_at = EXCLUDED.processed_at;

<-- -->

5. Trust but Verify: Automated Data Quality with Soda Core

A common failure mode in delivery data pipelines is silent bad data: invalid coordinates (e.g., longitude set to 0.0 placing deliveries in the Atlantic Ocean), negative loyalty balances, or missing phone numbers.

Implementing Data Quality as Code catches anomalies before data hits executive dashboards. Using Soda Core, validation checks run as programmatic assertions:

<- YML FILE ->

checks for customer_addresses: - row_count > 0: name: Address registry cannot be empty # Cambodia geographic bounding box validation - invalid_count(latitude) = 0: valid min: 10.0 valid max: 15.0 name: Latitude must be within Cambodian territory - invalid_count(longitude) = 0: valid min: 102.0 valid max: 108.0 name: Longitude must be within Cambodian territory - missing_count(khan) = 0: name: All delivery points must belong to a recognized district

<-- -->

If a breaking bug in an app release drops GPS telemetry, the Soda scan exits with a non-zero status code, halting downstream Airflow DAG execution and notifying on-call engineers via Slack or PagerDuty.

6. Closing the Loop: Executive Business Intelligence

When data is transformed and validated, business questions that once took hours of manual parsing can be served interactively using lightweight frameworks like Streamlit.

Architectural Takeaway

Building modern data engineering pipelines is not just about moving records from an API into a database. It is about translating operational noise into business signal.

By applying strict dimensional modeling, functional transformations, idempotent PostgreSQL upserts, and automated quality gates, engineering teams build a data foundation capable of scaling alongside rapid consumer growth.

nham24_dashboard_url : https://data-engineering-nham24-ebdamfdmnyndqxcubbwjbj.streamlit.app/
github : https://github.com/kikoyummy115-tech/data-engineering-nham24

Building a Production Data Pipeline for On Demand Super Apps: An Engineering Blueprint

Relational Normalization

6. Closing the Loop: Executive Business Intelligence

Architectural Takeaway

មតិយោបល់ 0