Medical Data Warehouse (Telegram ELT)
Scalable Telegram ELT pipeline, Star Schema data warehouse, and Dagster orchestration for medical channel intelligence
Problem & Architectural Solution
Core business challenges, domain requirements, and technical strategy
The Problem & Bottlenecks
Medical businesses and pharmacies in Ethiopia frequently use public Telegram channels to share inventory, product updates, and business communications. However, this data is completely unstructured, distributed across thousands of message feeds, and mixed with image attachments. Without an automated ELT pipeline, tracking trends, auditing product availability, analyzing engagement metrics, and deriving actionable healthcare insights at scale is virtually impossible.
The Implemented Solution
Architected and built an end-to-end ELT system that extracts raw messages and images using Python and Telethon, stores payloads in a partitioned raw data lake, ingests them into PostgreSQL via a robust data loader, transforms the raw data into an analytical Star Schema (fct_messages, dim_channels, dim_dates) using dbt, enriches images with YOLO object detection, and orchestrates the entire lifecycle with Dagster for automated scheduling, retries, and monitoring.
Detailed Scope & Objectives
The Medical Data Warehouse project is a robust, production-grade ELT (Extract, Load, Transform) pipeline designed to extract, ingest, transform, and analyze public Telegram data from Ethiopian medical business channels. By combining Telethon for automated scraping, local partitioned raw data lakes, PostgreSQL in Docker, dbt for Star Schema dimensional modeling, Dagster for workflow orchestration, and YOLO for media enrichment, the platform turns unstructured social messaging feeds into structured analytical intelligence.
System Architecture & Data Flow
Component boundaries, async pipeline execution, and transaction lifecycle
Telegram Data Extraction
Python and Telethon scrape message text, metadata, view counts, and image assets from targeted Ethiopian medical channels.
Raw Data Lake Storage
Saves original API responses as raw JSON files partitioned by date and downloads raw images locally to preserve original payload fidelity.
PostgreSQL Ingestion
A dedicated loader script validates and ingests raw JSON data into the raw staging tables of a containerized PostgreSQL 15 database.
dbt Star Schema Modeling
dbt compiles and executes SQL transformations converting raw records into analytical fact (fct_messages) and dimension (dim_channels, dim_dates) tables.
YOLO Media Enrichment
Computer vision model inspects medical channel images for detected objects and enriches fact tables with visual intelligence.
Dagster Orchestration
Dagster manages dependency graphs, providing retries, logs, and a unified execution dashboard.
graph TD
A[Telegram Medical Channels] -->|Telethon Scraper| B[Local Raw Data Lake]
B -->|Partitioned JSON & Images| C[Database Loader]
C -->|Raw Ingestion| D[(PostgreSQL 15 Data Warehouse)]
D -->|dbt Staging Models| E[dbt Transformations]
E -->|Star Schema Marts| F[Fact & Dimension Tables\nfct_messages, dim_channels, dim_dates]
F -->|Image Payloads| G[YOLO Image Enrichment]
G -->|Enriched Detections| F
H[Dagster Orchestrator] -.->|Manages & Schedules| A
H -.->|Triggers| C
H -.->|Executes| E
H -.->|Coordinates| GTechnology Stack Breakdown
Explicit technical responsibilities and tooling justification per layer
Backend & Systems
Asynchronous Telegram client library for extracting messages and media.
Core scripting language for scraping, ingestion, and pipeline logic.
Database & Caching
Relational data warehouse hosting raw schemas, staging models, and dimensional marts.
Modular SQL transformations, Star Schema modeling, automated testing, and documentation.
Infrastructure & Security
Orchestrator for managing ELT dependency graphs, scheduling, monitoring, and automated retries.
Containerized database environment running on custom isolated ports.
Key Technical Capabilities
Production-grade features, system subsystems, and upcoming roadmap items
Telegram Channel Scraping
CompletedAutomated ingestion of messages, view counts, forwards, replies, and image media across multiple medical business channels.
Partitioned Raw Data Lake
CompletedOrganizes raw JSON payloads and image downloads into partitioned directory structures before relational loading.
dbt Star Schema Warehouse
CompletedData warehouse models structured into staging views, dimension tables (dim_channels, dim_dates), and fact tables (fct_messages).
YOLO Object Detection Enrichment
CompletedDeep learning object detection annotates channel imagery and links detected visual classes back to message fact records.
Dagster Pipeline Orchestration
CompletedComplete asset-oriented orchestration with automated schedules, materializations, error handling, and visual lineage graphs.
Architecture Decisions & Trade-Offs
Technical context, decision rationales, and verified system outcomes
1ELT over ETL Pattern
Raw Telegram payloads can have varying structures across channels and change over time as new metadata is scraped.
Adopted an ELT approach: extract and load raw payloads into PostgreSQL first, then transform using dbt SQL models.
Ensured no loss of historical raw data fidelity and allowed transforming data repeatedly without rescraping Telegram APIs.
2dbt for Dimensional Modeling
Analytical querying of millions of messages required clean separation between raw ingestion and dimensional reporting.
Implemented dbt to manage staging views, dimensional models (dim_channels, dim_dates), and fact tables (fct_messages).
Created modular, testable, and documented SQL transformations with version control and data quality assertions.
Engineering Challenges & Solutions
Real architectural bottlenecks encountered and the engineering rationale behind their resolution
#1Telegram Rate Limits & API Floods
Scraping extensive channel histories triggers strict Telegram API rate limits (FloodWait errors).
Implemented exponential backoff, rate-limiting guards, and incremental scraping tracking the highest scraped message ID.
Security, Integrity & Reliability
Production safeguards, boundary enforcement, and fault-tolerance patterns
Isolated Environment Configuration
Telegram credentials, API hashes, and database passwords are kept strictly isolated in environment variables.
dbt Data Quality Assertions
Automated dbt test suite verifying uniqueness, non-negative view counts, and future date constraints.
Verified Results & Status
Factual metrics, operational milestones, and current production state
- Automated scraping and transformation pipeline processing Ethiopian medical Telegram feeds.
- Production-ready Star Schema data warehouse with automated fact and dimension models via dbt.
- End-to-end Dagster orchestration with full pipeline visualization, retries, and scheduled execution.
- Integrated YOLO computer vision enrichment for extracted medical media and product imagery.
- Comprehensive dbt test suite and CI workflow enforcing 100% data integrity on every build.
Lessons Learned & Retrospective
Key technical takeaways that inform future platform architecture decisions
Maintaining raw payload data lakes before relational ingestion is crucial for ELT flexibility and pipeline idempotency.
dbt transforms complex raw messaging logs into clean, analyst-friendly dimensional models while maintaining lineage.
Orchestrators like Dagster provide critical observability and retry resilience that standalone scripts cannot match.
Swift Addis Car Detailing Platform
Backend & Admin System
ETBooking Solutions
Full-Stack Web Platform
Related Case Studies
Swift Addis Car Detailing Platform
A production car detailing platform for appointment booking, service management, staff scheduling, availability, and business operations, backed by a Node.js/Express API and a React administrative dashboard.
ETBooking Solutions
A modern, bilingual enterprise website for ETBooking Solutions, showcasing custom booking and appointment management solutions for service-based businesses across Ethiopia.