Skip to main content
All projects
Data & Analytics
/projects/data-pipeline

Real-Time Data Pipeline

  • Minutes → sub-100ms queries
  • Real-time dashboards
  • ELT with Dagster + DLT
project.data-pipeline.local
Real-Time Data Pipeline project screenshot

Problem: Analytics queries took minutes to return, blocking decision-making.

Solution: Architected a real-time ELT pipeline using Dagster, DLT, and ClickHouse.

Impact: Query latency reduced from 5–10 minutes to under 100ms, enabling real-time dashboards.

Overview

From batch pain to near-real-time analytics by orchestrating assets in Dagster and serving ClickHouse for interactive use.

Before / after

Query latency

Before: 5–10 minute waits

After: Sub-100ms for core dashboards

Stack

Dagster
DLT
ClickHouse
Python

Decisions

Key trade-offs and design calls that shaped the final delivery.

Sling for MySQL, DLT for MongoDB

Context: Different data patterns—MySQL needed incremental sync, MongoDB full replace

Decision: Best-of-breed tools per source rather than one-size-fits-all: Sling for incremental MySQL, DLT for MongoDB collections

Dagster over Airflow

Context: Needed modern orchestration with asset-based lineage and better observability

Decision: Dagster Cloud for serverless deployment—explicit dependency graphs and software-defined assets

ClickHouse over PostgreSQL

Context: Analytical queries on large datasets were slow in row-based stores

Decision: Columnar ClickHouse Cloud for 10x query performance—sub-100ms dashboards instead of 5-10 minute waits

Architecture

The primary system boundaries, runtime pieces, and how the project was structured in production.

Sling + DLT

Extract Layer

Sling pulls MySQL tables incrementally every 6 hours (only new/changed rows), DLT pulls MongoDB collections (full sync).

Dagster Cloud

Orchestration

Manages job dependencies, schedules, and asset-based lineage—runs in serverless mode with proper dependency graphs.

DBT Core

Transform

Creates denormalized views (orders_data, product_data) with business logic and data quality rules applied after raw loads.

ClickHouse + Metabase

Warehouse + Consumption

Raw data lands in ClickHouse (tables prefixed: buyogo_mysql_*, dlt_new_mongo_*), business users query via Metabase dashboards.

Mermaid source. Paste into mermaid.live to visualize the diagram.

flowchart LR
  SRC[Sources] --> DLT[DLT ingest]
  DLT --> CH[(ClickHouse)]
  D[Dagster orchestration] --> DLT
  D --> CH
  CH --> BI[Dashboards]

Pipeline

How changes moved from development through validation and deployment.

1

Extract

Sling + DLT

MySQL incremental every 6h via Sling, MongoDB full sync via DLT

2

Load

ClickHouse

Raw data lands in prefixed tables: buyogo_mysql_*, dlt_new_mongo_*

3

Transform

DBT Core

Creates orders_data and product_data views with business logic

4

Orchestrate

Dagster Cloud

Manages dependencies, retries, scheduling via GitHub Actions deployment

5

Deploy

GitHub Actions

Branch-based: main→prod, staging→staging, test→testing environments

6

Monitor

Metabase

Dashboards for processed data with better visuals

Incidents

Operational failures, rehearsals, or recovery moments that changed how the system was run.

DBT temp table conflicts in shared ClickHouse

P2

Resolution: Added cleanup query to pipeline—DBT temp tables now cleaned between runs

Lesson: Shared ClickHouse schema needs explicit cleanup between runs; don't assume automatic cleanup