Introduction
Building an enterprise-grade data platform has historically implied tying your architecture to a specific cloud hyperscaler. Between Kinesis, S3 bucket policies, Redshift clusters, and proprietary IAM roles, teams frequently find themselves trapped in complex configurations and soaring monthly bills.
Modern data engineering has evolved. With open protocols, container standards, and specialized serverless cloud SaaS providers, you can build a resilient, real-time data platform that is cloud-agnostic, blazingly fast, and completely free of proprietary hyperscaler lock-in.
In this guide, we will design, build, test, and automatically deploy ByteFlow: a complete, end-to-end modern data engineering platform featuring:
- High-throughput Event Streaming: Apache Kafka (running on Upstash Serverless or Confluent Cloud).
- Workflow Orchestration & Scheduling: Apache Airflow 2.8+ containerized with automated DAG workflows.
- Data Warehousing & Lakehouse Engine: MotherDuck (Serverless DuckDB Cloud) and PostgreSQL.
- Data Modeling & Testing: dbt Core 1.8+ (Staging, Intermediate, and Mart layers with dimensional Star Schema modeling).
- Automated CI/CD Deployment: GitHub Actions with Docker image automation and zero-downtime rolling deploys to public cloud PaaS (Render / Fly.io).
All source code, Docker configs, dbt models, and CI/CD pipelines are available in our public repository. Let's dive into the architecture!
ποΈ System Architecture & Data Flow
Here is how data flows from producer to executive analytics:
βββββββββββββββββββββββββββββββββββ
β Event Producer Service β
β (Python / Simulated Streaming) β
ββββββββββββββββββ¬βββββββββββββββββ
β
βΌ (JSON / SASL_SSL)
βββββββββββββββββββββββββββββββββββ
β Apache Kafka Cloud Topic β <-- Upstash / Confluent (Serverless)
β `ecommerce.orders` β
ββββββββββββββββββ¬βββββββββββββββββ
β
βΌ
βββββββββββββββββββββββββββββββββββ
β Streaming Micro-batch Loader β <-- Idempotent Ingestion Service
ββββββββββββββββββ¬βββββββββββββββββ
β
βΌ (Streaming Buffer)
βββββββββββββββββββββββββββββββββββ
β Raw Lakehouse / Warehouse β <-- MotherDuck / DuckDB Cloud
β `raw_orders_stream` β
ββββββββββββββββββ¬βββββββββββββββββ
β
β Scheduled Trigger (Cron / DAG)
βΌ
βββββββββββββββββββββββββββββββββββ
β Apache Airflow β
β (Orchestrator & Healthcheck) β
ββββββββββββββββββ¬βββββββββββββββββ
β
βΌ Executes
ββββββββββββββββββββββββββββββββββββββββββββββββββββ
β dbt Core Transformation β
β β
β βββ Staging: `stg_ecommerce__orders` β
β βββ Intermediate: `int_customer_orders` β
β βββ Marts: `fct_daily_sales` β
β `dim_customers` β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ
Key Architectural Strengths
- Decoupled Compute & Storage: The ingestion layer streams into an append-only raw buffer without blocking transformation jobs.
- Strict Data Contracts: Event structures are validated with Pydantic schemas before serialization.
- Idempotency by Design: Deduplication handles at-least-once Kafka guarantees seamlessly within dbt models using window functions.
- Zero Heavy Infrastructure Footprint: DuckDB running on MotherDuck provides analytical response times under 50ms without provisioning persistent multi-node compute clusters.
π¦ Repository Structure
The complete project follows a standardized production structure:
byteflow-data-platform/
βββ .github/
β βββ workflows/
β βββ deploy.yml # Automated CI/CD pipeline
βββ airflow/
β βββ dags/
β β βββ byteflow_analytics_dag.py
β βββ Dockerfile # Custom Airflow + dbt container
βββ dbt_project/
β βββ models/
β β βββ staging/ # Bronze: Clean & cast
β β βββ intermediate/ # Silver: Windowing & rollups
β β βββ marts/ # Gold: Star schema dimensional models
β βββ seeds/
β βββ dbt_project.yml
β βββ profiles.yml
βββ infra/
β βββ render.yaml # Infrastructure as Code for Cloud PaaS
β βββ terraform/ # Terraform specs for Kafka SaaS
βββ src/
β βββ config.py # Pydantic environment configurations
β βββ models.py # Event schema definitions
β βββ producers/
β β βββ order_stream_producer.py
β βββ consumers/
β βββ order_stream_loader.py
βββ tests/
β βββ test_models.py
β βββ test_consumer.py
βββ docker-compose.yml # Local development orchestration
βββ requirements.txt
βββ Makefile
β‘ Step 1: Real-Time Event Streaming with Apache Kafka
To avoid managing ZooKeeper or complex cloud brokers, we use Upstash Serverless Kafka or Confluent Cloud. Both provide standard Kafka TCP endpoints with SASL_SSL authentication.
1. Defining Event Schemas with Pydantic
We define strong typing for order items and transactions:
# src/models.py
import uuid
from datetime import datetime
from enum import Enum
from typing import List, Optional
from pydantic import BaseModel, Field
class OrderStatus(str, Enum):
PENDING = "PENDING"
COMPLETED = "COMPLETED"
FAILED = "FAILED"
CANCELLED = "CANCELLED"
class OrderItem(BaseModel):
item_id: str = Field(default_factory=lambda: str(uuid.uuid4()))
sku: str
product_name: str
category: str
unit_price: float
quantity: int
total_amount: float
class OrderEvent(BaseModel):
event_id: str = Field(default_factory=lambda: str(uuid.uuid4()))
order_id: str = Field(default_factory=lambda: f"ORD-{uuid.uuid4().hex[:10].upper()}")
customer_id: str
customer_email: str
customer_country: str
customer_city: str
order_timestamp: datetime = Field(default_factory=datetime.utcnow)
status: OrderStatus
payment_method: str
items: List[OrderItem]
total_order_value: float
currency: str = "USD"
device_type: Optional[str] = None
2. The Resilient Kafka Producer
The producer connects securely via SASL_SSL and publishes orders partitioned by customer_id to guarantee ordering per customer:
# src/producers/order_stream_producer.py
from kafka import KafkaProducer
from src.config import settings
def create_kafka_producer() -> KafkaProducer:
kafka_kwargs = {
"bootstrap_servers": settings.kafka_bootstrap_servers.split(","),
"value_serializer": lambda v: json.dumps(v, default=str).encode("utf-8"),
"key_serializer": lambda k: k.encode("utf-8") if k else None,
"acks": "all",
"retries": 5,
"max_in_flight_requests_per_connection": 1,
}
if settings.kafka_sasl_username and settings.kafka_sasl_password:
kafka_kwargs.update({
"security_protocol": "SASL_SSL",
"sasl_mechanism": "SCRAM-SHA-256",
"sasl_plain_username": settings.kafka_sasl_username,
"sasl_plain_password": settings.kafka_sasl_password,
})
return KafkaProducer(**kafka_kwargs)
π₯ Step 2: Micro-Batch Ingestion into Cloud MotherDuck
Rather than writing custom file parsers, our consumer pools Kafka events and writes them directly into MotherDuck/DuckDB tables using batch transactions.
# src/consumers/order_stream_loader.py
def flush_batch_to_warehouse(con, records: List[Dict[str, Any]]) -> int:
if not records:
return 0
inserted = 0
for r in records:
con.execute("""
INSERT OR IGNORE INTO raw_orders_stream (
event_id, order_id, customer_id, customer_email, customer_country,
customer_city, order_timestamp, status, payment_method,
total_order_value, currency, device_type, raw_payload
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (
r.get("event_id"), r.get("order_id"), r.get("customer_id"),
r.get("customer_email"), r.get("customer_country"), r.get("customer_city"),
r.get("order_timestamp"), r.get("status"), r.get("payment_method"),
r.get("total_order_value"), r.get("currency"), r.get("device_type"),
json.dumps(r)
))
inserted += 1
return inserted
INSERT OR IGNORE ensures that if a consumer crashes and replays an offset, duplicate events are silently dropped at the database primary key layer.
π Step 3: Transformation & Dimensional Modeling with dbt
We implement a clean 3-tier medallion architecture with dbt:
1. Staging (stg_ecommerce__orders.sql)
Extracts, casts, and handles deduplication via row_number():
with source_data as (
select
event_id,
order_id,
customer_id,
lower(trim(customer_email)) as customer_email,
trim(customer_country) as customer_country,
cast(order_timestamp as timestamp) as order_timestamp,
upper(trim(status)) as order_status,
upper(trim(payment_method)) as payment_method,
cast(total_order_value as decimal(12, 2)) as total_order_value_usd,
ingested_at,
row_number() over (
partition by order_id
order by order_timestamp desc, ingested_at desc
) as deduplication_rank
from {{ source('raw_staging', 'raw_orders_stream') }}
)
select
order_id,
customer_id,
customer_email,
customer_country,
order_timestamp,
order_status,
payment_method,
total_order_value_usd,
case when order_status = 'COMPLETED' then true else false end as is_successful_order
from source_data
where deduplication_rank = 1
2. Dimensional Marts (dim_customers.sql & fct_daily_sales.sql)
Creates analytics-ready facts and customer dimensions with segmentation logic:
with customer_metrics as (
select * from {{ ref('int_customer_orders') }}
)
select
customer_id,
customer_email,
customer_country,
total_orders_placed,
successful_orders_count,
lifetime_spend_usd,
round(coalesce(average_order_value_usd, 0), 2) as average_order_value_usd,
case
when lifetime_spend_usd >= 2000 then 'VIP / High Value'
when lifetime_spend_usd >= 500 then 'Regular / Medium Value'
when lifetime_spend_usd > 0 then 'Occasional Buyer'
else 'Prospect / Inactive'
end as customer_tier
from customer_metrics
3. Automated Data Quality Tests
In schema.yml, we define assertions to safeguard data integrity:
-
not_nullanduniqueon primary keys (order_id,customer_id). -
accepted_valuestesting for allowed transaction statuses and customer tiers.
β±οΈ Step 4: Orchestration with Apache Airflow
Airflow runs on a schedule (every 30 minutes), validates that ingestion SLAs are met, and triggers dbt pipeline runs sequentially.
# airflow/dags/byteflow_analytics_dag.py
with DAG(
dag_id="byteflow_analytics_pipeline",
default_args=default_args,
schedule_interval="*/30 * * * *",
start_date=datetime(2025, 1, 1),
catchup=False,
max_active_runs=1,
) as dag:
raw_healthcheck = PythonOperator(
task_id="verify_raw_stream_health",
python_callable=check_raw_data_health,
)
dbt_seed = BashOperator(
task_id="dbt_seed",
bash_command="cd /opt/airflow/dbt_project && dbt seed --profiles-dir .",
)
dbt_run_staging = BashOperator(
task_id="dbt_run_staging",
bash_command="cd /opt/airflow/dbt_project && dbt run --select staging --profiles-dir .",
)
dbt_test_staging = BashOperator(
task_id="dbt_test_staging",
bash_command="cd /opt/airflow/dbt_project && dbt test --select staging --profiles-dir .",
)
dbt_run_marts = BashOperator(
task_id="dbt_run_marts",
bash_command="cd /opt/airflow/dbt_project && dbt run --select marts --profiles-dir .",
)
dbt_test_marts = BashOperator(
task_id="dbt_test_marts",
bash_command="cd /opt/airflow/dbt_project && dbt test --select marts --profiles-dir .",
)
raw_healthcheck >> dbt_seed >> dbt_run_staging >> dbt_test_staging >> dbt_run_marts >> dbt_test_marts
π Step 5: Automated Deployment & CI/CD Pipeline
To ensure the entire stack deploys autonomously, we set up GitHub Actions and Infrastructure-as-Code.
GitHub Actions Workflow (.github/workflows/deploy.yml)
Every push to main:
-
Lints Python & SQL: Runs
black,flake8, andsqlfluffto ensure styling and SQL dialect compliance. -
Executes Test Suite: Runs
pytestwith coverage and validates dbt models against an isolated in-memory test database. -
Builds & Pushes Containers: Packages the custom Airflow and ingestion microservices to the GitHub Container Registry (
ghcr.io). - Triggers Cloud Rollout: Calls a webhook to deploy the updated containers to public cloud PaaS (such as Render or Fly.io) with zero downtime.
-
Runs Production dbt Build: Executes
dbt run --target prodanddbt test --target prodagainst MotherDuck.
# Snippet from .github/workflows/deploy.yml
deploy-public-cloud:
name: π Deploy to Public Cloud / SaaS
needs: [build-and-publish]
if: github.ref == 'refs/heads/main' && github.event_name == 'push'
runs-on: ubuntu-latest
steps:
- name: Trigger Automated Deployment Webhook
env:
RENDER_DEPLOY_HOOK: ${{ secrets.RENDER_DEPLOY_HOOK_URL }}
run: |
curl -s -X POST "$RENDER_DEPLOY_HOOK"
- name: Automated Production dbt Cloud Run
env:
MOTHERDUCK_TOKEN: ${{ secrets.MOTHERDUCK_TOKEN }}
run: |
pip install dbt-core dbt-duckdb
cd dbt_project
dbt seed --target prod --profiles-dir .
dbt run --target prod --profiles-dir .
dbt test --target prod --profiles-dir .
Infrastructure as Code with Render Blueprint (infra/render.yaml)
Using Render's Infrastructure-as-Code blueprint, Airflow Webserver, Scheduler, and Kafka Loader are provisioned automatically:
services:
- type: web
name: byteflow-airflow-webserver
env: docker
dockerfilePath: airflow/Dockerfile
plan: starter
startCommand: "airflow webserver --port $PORT"
healthCheckPath: /health
- type: worker
name: byteflow-airflow-scheduler
env: docker
dockerfilePath: airflow/Dockerfile
plan: starter
startCommand: "airflow scheduler"
- type: worker
name: byteflow-kafka-loader
env: docker
dockerfilePath: airflow/Dockerfile
startCommand: "python -m src.consumers.order_stream_loader"
π οΈ Running the Platform Locally
To spin up the entire platform locally with zero cloud dependencies:
# 1. Clone the repository
git clone https://github.com/your-username/byteflow-data-platform.git
cd byteflow-data-platform
# 2. Configure environment
cp .env.example .env
# 3. Start containers
docker compose up -d
# 4. Open Airflow UI
open http://localhost:8080 # User: admin / admin
To run the streaming producer and test dbt locally:
# In terminal 1: Stream events
make stream
# In terminal 2: Run transformations
make dbt-run
make dbt-test
π Summary & Results
By combining Apache Kafka, Apache Airflow, dbt Core, and MotherDuck, we have constructed a modern data platform that delivers:
| Metric / Aspect | Traditional Cloud Setup | ByteFlow Architecture |
|---|---|---|
| Cloud Dependency | Tied to AWS (Kinesis, Redshift, S3, IAM) | 100% Cloud-Agnostic / Open Standard |
| Setup & Provisioning Time | Days / Complex IAM policies | Minutes (Docker + Blueprint IaC) |
| Streaming Latency | Minutes | Sub-second Kafka event delivery |
| Warehouse Query Speed | 500ms - 3s (Cold starts) | <50ms with DuckDB / MotherDuck |
| Deployment Automation | Custom scripts | Full GitHub Actions CI/CD |
This modern architecture proves you don't need AWS to build an enterprise-ready, automated data engineering powerhouse.
Resources & Links
- GitHub Repository: byteflow-data-platform
- Documentation: dbt-duckdb Docs
- Airflow Documentation: Apache Airflow
- Upstash Kafka: Upstash Serverless Kafka
- MotherDuck: MotherDuck Cloud Analytics
Have questions or built a similar architecture? Drop a comment below and let's discuss!
Top comments (0)