Near-real-time analytics at 50M events/day near-real-time

When the analytics team asks "how many users activated this week?" and the answer is eight hours old, every decision downstream is a guess. The company's clickstream pipeline ran as a nightly batch — a cron job pulling the previous day's events from S3, transforming them in a monolithic PySpark script, and loading them into a provisioned Redshift cluster. One missed window meant waiting another 24 hours.

The pipeline processed 50M+ events per day at peak. The data wasn't the problem; the architecture was. We needed the same data, fresher, without blowing up the infrastructure budget.

Pipeline architecture

The replacement pipeline followed a five-stage flow: ingest, land, transform, serve, orchestrate.

Kinesis Data Streams captured events from the application SDK with two shards delivering 1 MB/s per shard. A Lambda consumer batched records and wrote them to S3 in Hive-partitioned prefixes (dt=YYYY-MM-DD/hr=HH/), giving us partition pruning for free downstream.

An AWS Glue PySpark job ran every five minutes, reading only the latest partitions. The transform layer handled deduplication, timestamp normalization, null filtering, and session ID hashing — all in a single pass.

from awsglue.context import GlueContext
from pyspark.context import SparkContext
from pyspark.sql import functions as F
 
sc = SparkContext()
glue_context = GlueContext(sc)
 
raw = glue_context.create_dynamic_frame.from_catalog(
    database="raw_events",
    table_name="clickstream",
    push_down_predicate="partition_dt >= '2024-01-01'",
)
 
cleaned = (
    raw.toDF()
    .filter(F.col("event_type").isNotNull())
    .withColumn("event_ts", F.to_timestamp("event_ts", "yyyy-MM-dd'T'HH:mm:ss"))
    .withColumn("session_id", F.sha2(F.col("user_id").cast("string"), 256))
    .dropDuplicates(["event_id"])
)

Cleaned data landed in Redshift Serverless at 128 RPU base capacity. dbt incremental models handled the business logic layer — session aggregation, funnel metrics, cohort definitions — and ran as part of the same Airflow DAG that triggered the Glue job.

MWAA Airflow orchestrated the entire flow on a five-minute cadence: trigger Glue job, wait for completion, run dbt models, publish metrics. The DAG was the single source of truth for pipeline state.

Observability

We instrumented the pipeline at two layers. Infrastructure metrics — Kinesis iterator age, Glue DPU utilization, Redshift query queue depth — fed into CloudWatch dashboards that the on-call engineer monitored. Business SLOs were tracked via custom CloudWatch metrics published directly from the Glue job at the end of each run.

import boto3
 
cloudwatch = boto3.client("cloudwatch", region_name="us-east-1")
 
 
def publish_pipeline_metrics(
    rows_processed: int,
    dedup_count: int,
    job_run_id: str,
) -> None:
    """Publish per-run metrics to CloudWatch for SLO tracking."""
    cloudwatch.put_metric_data(
        Namespace="DataPipeline/Clickstream",
        MetricData=[
            {
                "MetricName": "RowsProcessed",
                "Value": rows_processed,
                "Unit": "Count",
                "Dimensions": [{"Name": "JobRunId", "Value": job_run_id}],
            },
            {
                "MetricName": "DedupRate",
                "Value": dedup_count / max(rows_processed, 1) * 100,
                "Unit": "Percent",
                "Dimensions": [{"Name": "JobRunId", "Value": job_run_id}],
            },
        ],
    )

A dead-letter queue captured events that failed parsing. A CloudWatch alarm on queue depth greater than zero triggered a PagerDuty page immediately — no more silent data loss.

Lessons learned

Partition pruning pays for itself. Hive-partitioned S3 prefixes reduced Glue scan cost by roughly 80%. The job reads only the current and previous hour's partitions on each run, which kept the five-minute cadence feasible without scaling DPU capacity.

Dead-letter queues prevent silent failures. The original batch pipeline silently skipped malformed events. Adding a Kinesis DLQ surfaced data quality issues that had gone undetected for months — broken SDK integrations, timezone mismatches, and truncated payloads.

Redshift Serverless auto-pause needs a cache layer. Without materialized views, auto-pause resume latency of 8–12 seconds was visible to dashboard users. A scheduled dbt model that pre-warmed the most queried aggregations eliminated cold-start pain while keeping costs low during off-hours.