Real-Time Clickstream Analytics: Ingesting 20,000 Events/Sec into S3 & Athena
Streaming massive client telemetry with Amazon Kinesis Data Firehose, automated Parquet conversion, Glue Catalog schema evolution, and serverless Athena SQL.
1. Business Problem & Context
An online streaming portal generates 20,000 user clickstream events every second (page views, video scrubs, clicks). Storing raw JSON files directly on S3 resulted in millions of tiny 2KB files. Running a query across 1 day of data required scanning 1.7 Billion files, costing thousands of dollars per query and timing out Athena.
2. Requirements & Constraints
- Near Real-Time Availability: Telemetry must be queryable via SQL within 60 seconds of emission.
- Optimized Storage Format: Convert unstructured JSON to compressed columnar Apache Parquet.
- Partition Pruning: Enable queries to scan only targeted date partitions (
year=YYYY/month=MM/day=DD/hour=HH).
3. Architecture Overview & Data Flow
Interactive Architecture Diagram (Use controls to zoom & pan)
4. AWS Services Used & Rationales
AWS Services Architecture Rationale
Concrete reasons why these specific services were chosen over alternatives
| Service | Category | Architectural Rationale ("Why this service?") |
|---|---|---|
| Amazon Kinesis Data Firehose | Analytics | Performs automated buffer aggregation and inline JSON-to-Parquet conversion without provisioning ETL servers. |
| Amazon Athena | Analytics | Serverless SQL engine charging $5.00 per Terabyte scanned. Parquet reduces scanned bytes by ~90%. |
| AWS Glue Data Catalog | Analytics | Centralized Hive-compatible schema catalog shared between Athena, EMR, and Redshift. |
5. Key Design Trade-offs
Architecture Decision & Trade-Off Matrix
Evaluating alternative approaches under real-world constraints
Raw JSON Files in S3
- + Zero transformation latency
- − Scans 100% of data (all columns)
- − Millions of small file I/O penalties
- − Athena query bills skyrocket
Firehose Snappy Parquet + Partitioning (Chosen)
✓ Chosen Design- + 90%+ storage compression
- + Columnar projection scans only requested fields
- + Instant partition pruning
- − 60-second micro-batch latency
6. Implementation Highlights
Configuration Dynamic S3 Prefix Partitioning in Firehose
# Dynamic Partitioning S3 Prefix Expression:
events/year=!{timestamp:yyyy}/month=!{timestamp:MM}/day=!{timestamp:dd}/hour=!{timestamp:HH}/
# Error Output Prefix:
errors/!{firehose:error-output-type}/!{timestamp:yyyy}/!{timestamp:MM}/7. Results & Key Metrics
- Query Scan Volume: Reduced from 1.2 TB per analytical query to 98 GB.
- Query Cost: Dropped from $6.00/query to $0.49/query.
8. Key Architectural Takeaways
Data Lake Law: Athena charges purely based on the number of bytes scanned from S3. Using Apache Parquet + Date Partitioning is the single most effective way to cut big data query costs by 90%.
