Real-Time Twitch Analytics Pipeline
End-to-end ELT streaming pipeline that ingests real-time Twitch viewership data through Apache Kafka, warehouses in Snowflake, and transforms with dbt for Looker Studio dashboards.
Architecture
Twitch API → Python Producer → Apache Kafka → Snowflake (Raw) → dbt (Transformation) → Looker Studio
This pipeline demonstrates a modern data stack architecture capable of handling real-time data ingestion, cloud warehousing, and automated transformation. The system monitors live streams for Age of Empires II, processing events through Apache Kafka, loading them into Snowflake via a custom Python consumer, and transforming the raw JSON data using dbt for analytics.
Pipeline Stages
1. Ingestion (Producer)
Python script polls Twitch API every 60 seconds for live Age of Empires II streams. Extracts metadata (streamer_name, viewer_count, started_at, language), serializes to JSON, and pushes to Kafka topic.
2. Storage (Consumer)
Python consumer listens to Kafka topic, batches messages for efficiency, and loads raw JSON directly into Snowflake VARIANT column (RAW_DATA) in the STREAM_LOGS table.
3. Transformation (dbt)
dbt models clean and normalize data. Parses JSON fields into structured columns, calculates stream duration, and filters out streams with 0 viewers to prevent visualization skew.
Key Features
Real-Time Processing
60-second polling interval with Kafka buffering ensures near real-time data availability while handling API rate limits and network variability.
Schema-on-Read with VARIANT
Raw JSON stored in Snowflake VARIANT column allows flexible schema evolution without pipeline changes. dbt handles transformation logic separately.
Automated Visualization
Looker Studio dashboard automatically reflects transformed data, showing concurrent viewers, top streamers by popularity, and peak viewership metrics.
Technology Stack
Source: Twitch API (Helix)
Official Twitch API providing real-time stream metadata for Age of Empires II category.
Streaming: Apache Kafka (Confluent Cloud)
Managed Kafka service handling message buffering, partitioning, and consumer group management with free tier support.
Warehouse: Snowflake (Standard Edition)
Cloud data warehouse with VARIANT column support for semi-structured data, automatic scaling, and separation of storage and compute.
Transformation: dbt (data build tool)
SQL-based transformation framework with testing, documentation, and lineage tracking. Models parse JSON and calculate derived metrics.
Dashboard Metrics
Concurrent Viewers
Real-time viewer count across all tracked streams, updated every 60 seconds.
Top Streamers
Ranked list of streamers by current viewership, showing audience distribution.
Peak Viewership
Historical maximum viewer counts with timestamps for trend analysis.
Future Enhancements
Containerization & Orchestration
Dockerize producer and consumer scripts, schedule dbt runs with Apache Airflow for production-grade orchestration.
Alerting System
Slack notifications when viewership spikes (e.g., > 10k viewers) for real-time monitoring.
CI/CD Pipeline
Automate dbt testing on GitHub merge, ensuring data quality and model reliability before deployment.
Related Projects
Distributed Medallion Lakehouse integrating cross-ministry CSR, groundwater, and education datasets using Apache Spark and Iceberg for panel regression analysis of Indian state-level policy outcomes.
Longitudinal analysis of IPUMS USA microdata to understand how wage trends influence shifts in U.S. college major popularity from 2009–2023.
Research project using Synthetic Control Method to analyze the causal impact of World Trade Organization accession on national economies.