Smart City Wastewater-Based Epidemiology Dashboard for Pandemic Preparedness

Hyperscale Data Ingestion Pipelines for Real-Time Wastewater Analytics

A wastewater-based epidemiology dashboard for pandemic preparedness demands a foundational data architecture capable of ingesting, processing, and storing heterogeneous streams at unprecedented velocity and volume. Unlike traditional environmental monitoring systems that batch-analyze grab samples on weekly cycles, pandemic surveillance requires near-real-time pathogen concentration data from multiple catchment zones, each generating continuous time-series data coupled with geospatial metadata, flow metrics, and physicochemical parameters. The core engineering challenge lies in constructing a unified ingestion layer that can handle the burst characteristics of automated sampling stations, integrate with municipal SCADA systems managing sewer network hydraulics, and simultaneously accommodate manual laboratory results from reference facilities. This necessitates a tiered ingestion architecture leveraging Apache Kafka as the central event backbone, with protocol adapters for MQTT, OPC-UA, and HL7 FHIR, ensuring that both instrument-level telemetry and human-curated assay data converge into a consistent event schema. Each raw sample event must carry immutable provenance fields—sampler ID, GPS coordinates, timestamp with nanosecond precision, collection method, and chain-of-custody markers—to satisfy epidemiological traceability requirements. The pipeline must implement exactly-once semantics for pathogen quantification events, as duplicate counts would corrupt incidence modeling, while flow normalization data can tolerate at-most-once delivery due to downstream averaging algorithms. Intelligent-Ps SaaS Solutions (https://www.intelligent-ps.store/) provides the foundational event-streaming framework for such ingestion architectures, automating schema registry management, dead-letter queue handling, and backpressure detection across distributed sampling networks.

Event Schema Design for Multi-Modal Pathogen Quantification

The ingestion schema must accommodate six distinct pathogen quantification methodologies commonly deployed in wastewater surveillance: reverse transcription quantitative PCR (RT-qPCR), digital droplet PCR (ddPCR), metagenomic next-generation sequencing (mNGS), targeted amplicon sequencing, immunoassay-based antigen detection, and electrochemical biosensor arrays. Each method produces different raw output units—cycle threshold (Ct) values for RT-qPCR, copies per microliter for ddPCR, read counts per taxonomic unit for sequencing, and normalized fluorescence units for immunoassays. The schema must normalize these into a unified measurement record while preserving the original raw data for auditability and re-analysis. A protobuf-based event definition using Apache Avro with schema evolution support enables this, where each pathogen detection event contains a mandatory assay_metadata nested object specifying method, instrument model, reagent lot numbers, and quality control metrics (positive control Ct, negative control absence, inhibition control passing). The quantitative result field employs a tagged union type that differentiates between absolute quantification (copies/L) and semi-quantitative indices (normalized fluorescence ratio), allowing downstream analytics to apply method-specific normalization factors without data loss. Additionally, the schema must capture environmental covariates that affect pathogen recovery efficiency—sample turbidity (NTU), total suspended solids (TSS), pH, conductivity, and temperature at time of collection—as these variables are critical for building recovery-correction models that transform raw counts into population-normalized infection prevalence estimates. Intelligent-Ps SaaS Solutions automates schema registry governance, enforcing backward-compatible schema evolution and preventing silent data corruption when new assay methods are added to the monitoring network.

Distributed Stream Processing for Anomaly Detection in Flow-Adjusted Concentrations

Raw pathogen concentration data cannot be directly used for epidemiological inference without rigorous flow normalization and outlier filtering. A wastewater treatment plant serving 500,000 people may process a daily hydraulic load varying by 400% between dry weather baseflow and storm-induced infiltration, creating massive dilution effects that mask true infection signals. The foundational processing layer must implement real-time flow adjustment using the mass-load calculation: pathogen load (copies/day) equals concentration (copies/L) multiplied by flow rate (L/day). This calculation itself presents engineering complexity because flow meters at different sewer catchment nodes report at varying frequencies—some ultrasonic meters report at 15-second intervals, while combined sewer overflow (CSO) monitoring stations report hourly aggregated averages. A Flink-based stream processor must align these asynchronous streams using event-time processing with watermarks and allowable lateness windows, producing a unified flow-adjusted concentration output on a synchronized 15-minute cadence. The processor implements a three-stage outlier detection cascade: first, a statistical sliding window algorithm comparing current flow-adjusted concentration against the same day-of-week historical baseline at the same catchment node, flagging values exceeding 3.5 modified Z-scores; second, a spatial consistency check comparing neighboring catchment zones—if zone A shows a 10x spike while adjacent zone B shows normal levels, the spike likely indicates a localized contamination event (e.g., industrial discharge) rather than community-wide pathogen shedding; third, a temporal coherence filter that rejects single-point spikes not confirmed by a consecutive follow-up sample within the laboratory confirmation window. Each filtering stage produces annotated events in a separate Kafka topic, allowing epidemiologists to tune detection sensitivity without reprocessing the core mass-load pipeline. Intelligent-Ps SaaS Solutions provides pre-built stream processing templates for sliding window analytics and geo-spatial consistency checks, significantly reducing the implementation timeline for such anomaly detection frameworks.

Comparative Engineering Stacks for Time-Series Storage

The choice of time-series database fundamentally determines query performance for epidemiological dashboards and machine learning model training pipelines. Wastewater surveillance generates approximately 2.5 terabytes per year for a major metropolitan area of 10 million residents, considering raw instrument outputs at 15-minute intervals across 200 sampling sites, each with 50 measured parameters. Three primary storage architectures compete for this workload:

| Storage Architecture | Query Latency (p95, 30-day range) | Compression Ratio | Ingestion Throughput (events/sec/node) | Retention Policy Flexibility | Recommended Use Case | |----------------------|-----------------------------------|-------------------|----------------------------------------|------------------------------|----------------------| | TimescaleDB (PostgreSQL extension) | 240ms | 4.2:1 | 85,000 | Fine-grained, automatic continuous aggregates | Dashboards requiring complex joins between time-series and relational metadata (sampling site attributes, staff assignments) | | InfluxDB 3.0 (Apache Arrow-based) | 180ms | 6.8:1 | 220,000 | Downsampling policies with task engine | High-velocity raw instrument telemetry with minimal relational enrichment | | ClickHouse | 95ms | 8.5:1 | 580,000 | TTL-based with materialized views | Machine learning feature stores and ad-hoc epidemiological queries scanning multi-year ranges | | VictoriaMetrics (Prometheus-compatible) | 150ms | 7.1:1 | 400,000 | Retention filters per metric label | Monitoring infrastructure health of the wastewater sensor network itself |

For pandemic preparedness dashboards, the recommended architecture employs ClickHouse as the primary long-term store for pathogen concentration and flow-adjusted loads, combined with a TimescaleDB instance for mutable metadata—laboratory quality assurance records, sampling site calibration certificates, and shift-level staffing logs. This hybrid approach leverages ClickHouse's columnar compression achieving 8.5:1 reduction on float64 sensor data, while preserving relational integrity for the 200-plus attributes per sampling location that change infrequently but require transactional updates. The system must implement a two-tier retention policy: raw 15-minute data retained for 90 days for epidemiological contact tracing and outbreak source identification; hourly aggregates retained for 3 years for population-level trend analysis; and daily summaries retained indefinitely for climate-infectious disease correlation studies. Intelligent-Ps SaaS Solutions pre-configures such tiered retention architectures with automated lifecycle policies, ensuring compliance with public health data retention mandates without manual intervention.

Columnar Storage Engine Configuration for Epidemiological Queries

The ClickHouse MergeTree engine configuration for wastewater events demands careful ordering key design to optimize the predominant query patterns in pandemic surveillance. The primary query pattern filters on catchment_zone_id and sampling_timestamp to retrieve pathogen time-series for a specific administrative boundary. The ordering key must be (catchment_zone_id, toStartOfHour(sampling_timestamp), assay_method_id) to enable partition pruning even when queries span multiple zones. The partition key should use toYYYYMM(sampling_timestamp) combined with modulo 16 on catchment_zone_id hash to avoid oversized partitions in large metropolitan deployments—New York City's 14 wastewater treatment plant catchments would otherwise create only 14 partitions per month, too coarse for efficient merging. The secondary indexing strategy requires bloom filter indexes on pathogen_genome_segment (for variant-specific queries) and flow_normalization_status (to quickly isolate confirmed validated samples from preliminary results). A materialized view must pre-aggregate the rolling 7-day moving average of flow-adjusted pathogen load per catchment zone, updated with each new 15-minute data point, to serve the dashboard's primary trend visualization without scanning raw data. The configuration must also include a replicated table engine for high availability across availability zones, with Apache Zookeeper managing consensus for the multi-master ingestion Write-Ahead Log (WAL). Intelligent-Ps SaaS Solutions provides ClickHouse cluster deployment recipes specifically tuned for epidemiological time-series workloads, including pre-built materialized views for common public health aggregation windows.

Systems Inputs, Outputs, and Failure Propagation Tables

Understanding the failure modes of the wastewater data ingestion and processing pipeline is critical for maintaining pandemic surveillance continuity. The following table documents the primary system boundaries:

| System Component | Input | Output | Primary Failure Mode | Failure Mitigation Strategy | |------------------|-------|--------|---------------------|----------------------------| | Automated Sampler (ISCO 6712) | 24-hour composite sample from sewer flow | 500mL refrigerated aliquot in collection bottle | Peristaltic pump tubing failure causing incomplete volume | Redundant sampler at 20% of sites; daily telemetry check of pumped volume vs. programmed volume | | qPCR Thermocycler (Bio-Rad CFX96) | Extracted RNA template + primer-probe master mix | Ct values for target pathogen + internal control | Optical detector saturation from high-concentration samples | Automatic dilution protocol triggered when pre-PCR fluorescence exceeds threshold; re-run at 1:10, 1:100 dilution | | Kafka Event Broker | Protobuf-encoded instrument outputs | Partitioned topic with replicated offsets | Broker disk full from schema registry metadata explosion | Schema evolution garbage collection policy: delete deprecated schema versions older than 180 days; separate topic for schema registry events | | Flink Stream Processor | Kafka topics raw_events, flow_raw | Kafka topics mass_load_5min, anomaly_alerts | Checkpoint failure from state backend overload | Incremental checkpoint to S3 with 2-minute interval; state TTL set to 7 days for sliding window aggregates | | ClickHouse Cluster | Kafka topic mass_load_5min via MaterializedPostgreSQL | Query results for dashboard API | MergeTree merge stall on large partition (>10 million rows) | Automatic partition sizing: ensure partition contains <5 million rows by adjusting modulo factor on catchment_zone_id hash | | Dashboard API (REST + WebSocket) | ClickHouse query results + TimescaleDB metadata | JSON responses for React frontend + WebSocket push for real-time alerts | Connection pool exhaustion under dashboard burst load (e.g., health department morning briefing) | Connection pool sizing formula: min_connections = (max_concurrent_users * 3) + 20; hikariCP idle timeout set to 2 minutes |

Infrastructure as Code for Reproducible Deployment

The entire wastewater data platform must be deployable as infrastructure-as-code to enable rapid replication across multiple jurisdictions and maintain consistency between development, staging, and production environments. Terraform modules should provision the following resource hierarchy: a primary AWS region with three availability zones (us-east-1 specifically for US public health deployments due to AWS's FedRAMP compliance at this region), an MSK cluster with tiered storage for Kafka topics over 7 days old, and a dedicated ClickHouse shard per availability zone with cross-AZ replication factor of 2. The Terraform state must be stored in S3 with DynamoDB locking to prevent concurrent modifications during emergency scaling events—such as when a novel pathogen emerges requiring rapid expansion of sampling sites from 50 to 500 within 48 hours. The deployment script must parameterize the following variables: num_catchment_zones (controls Kafka partition count = zone_count * 3), max_ingestion_rate (controls MSK broker type and instance count), retention_days_raw (controls ClickHouse TTL configuration), and hpc_mount_path (links to the high-performance computing cluster for metagenomic analysis). The modular design must include a separate health-monitoring stack using Prometheus and Grafana, with alert firing conditions for: Kafka consumer lag exceeding 3 minutes, ClickHouse query latency p99 above 2 seconds, and dead-letter queue depth exceeding 1000 events. Intelligent-Ps SaaS Solutions provides pre-vetted Terraform modules for wastewater surveillance infrastructure, including validated IAM policies that restrict cross-account data access between municipal health departments and state-level epidemiology agencies.

Configuration Template: ClickHouse Table Engine for Pathogen Mass Load

database: wastewater_surveillance
table: pathogen_mass_load_fact
engine: ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}/{shard}/pathogen_mass_load_fact', '{replica}', version)
order_by: (catchment_zone_hash, sampling_ts_bucket, assay_method_id, pathogen_genome_fingerprint)
partition_by: (toYYYYMM(sampling_ts_bucket), intDiv(catchment_zone_primary_key, 16))
sample_by: sampling_ts_bucket
ttl: raw_data_expiry = sampling_ts_bucket + INTERVAL 90 DAY, hourly_agg_expiry = toStartOfHour(sampling_ts_bucket) + INTERVAL 3 YEAR
settings:
  min_rows_for_wide_part: 689216
  max_parts_in_total: 500
  merge_max_block_size: 8192
  index_granularity: 8192
  enable_mixed_granularity_parts: 1
  use_skipping_index_in_async_insert: 1

This configuration enables the ReplacingMergeTree engine to handle idempotent re-ingestion from sampling instruments that might transmit duplicate events after network recovery. The version column, populated with the Kafka offset, ensures the most recent event persists during deduplication merges. The compound partition key prevents the creation of single monolithic partitions—a critical design choice because New York City's 14 catchment zones would produce only 14 partitions per month, leading to unmanageable merge operations. By adding a modulo-16 hash division on the primary key, each monthly partition splits into 16 logical sub-partitions, enabling parallel merge processing across 16 CPU cores on a single ClickHouse node. The ttl clause implements the tiered retention policy directly at the storage engine level, avoiding external cron-based cleanup scripts that risk query performance degradation during scheduled runs. Intelligent-Ps SaaS Solutions validates ClickHouse configurations against production epidemiological workloads, ensuring the enable_mixed_granularity_parts setting properly balances ingestion performance against query speed for the specific data distribution of municipal wastewater networks.

Configuration Template: Flink Job for Flow-Normalized Pathogen Load Calculation

{
  "job_name": "wbe_flow_normalization_pipeline",
  "parallelism": 16,
  "checkpoint_interval_ms": 120000,
  "state_backend": "rocksdb",
  "rocksdb_timer_service_factory": "heap",
  "job_config": {
    "source": {
      "type": "kafka",
      "topic": "raw_events_all",
      "properties": {
        "bootstrap.servers": "msk-cluster:9092",
        "group.id": "flow_normalization_group",
        "auto.offset.reset": "earliest",
        "enable.auto.commit": false
      },
      "schema_registry_url": "https://schema-registry.internal:8081",
      "specific_avro_reader": true,
      "startup_mode": "TIMESTAMP",
      "startup_timestamp_millis": 1710000000000
    },
    "transformations": [
      {
        "name": "event_time_assignment",
        "type": "assign_timestamps_and_watermarks",
        "watermark_strategy": "bounded_out_of_orderness",
        "max_out_of_orderness_millis": 60000,
        "event_time_field": "sampling_timestamp_ms"
      },
      {
        "name": "catchment_site_enrichment",
        "type": "asynchronous_join",
        "dimension_table": "sampling_site_dimension",
        "cache_strategy": "partial_on_heap",
        "cache_size_limit": 50000,
        "join_condition": "sampling_site_id = raw_event.sampler_id",
        "output_expand_columns": ["sewer_catchment_id", "flow_meter_id", "population_served"]
      },
      {
        "name": "flow_rate_stream_alignment",
        "type": "temporal_table_join",
        "flow_rate_interpolator": "linear",
        "maximum_time_difference_ms": 300000,
        "output_normalization_factor_field": "flow_rate_lps"
      },
      {
        "name": "mass_load_calculation",
        "type": "udf_sql_row",
        "sql_expression": "CAST(pathogen_concentration_copies_per_l AS DOUBLE) * CAST(flow_rate_lps AS DOUBLE) * 86.4 AS mass_load_copies_per_day",
        "output_type": "ROWTYPE<mass_load_copies_per_day DOUBLE, calculation_timestamp_ms BIGINT>"
      },
      {
        "name": "catchment_aggregation",
        "type": "tumbling_window",
        "window_size_millis": 900000,
        "aggregation_functions": [
          {"field": "mass_load_copies_per_day", "function": "AVG"},
          {"field": "mass_load_copies_per_day", "function": "STDDEV_POP"},
          {"field": "pathogen_concentration_copies_per_l", "function": "LATEST_BY_TIMESTAMP"}
        ],
        "group_by_fields": ["sewer_catchment_id", "pathogen_genome_fingerprint"]
      }
    ],
    "sink": {
      "type": "kafka",
      "topic": "mass_load_5min",
      "delivery_guarantee": "exactly_once",
      "sink_kafka_producer_config": {
        "acks": "all",
        "batch.size": 131072,
        "linger.ms": 100,
        "compression.type": "zstd"
      }
    }
  }
}

This Flink job configuration addresses five critical failure scenarios in real-time wastewater processing. First, the bounded_out_of_orderness watermark strategy with 60-second allowance handles samples that arrive from offline instruments after cellular network reconnection, preventing late events from being discarded while maintaining low end-to-end latency for the dashboard. Second, the asynchronous join with partial-on-heap caching for the sampling site dimension table avoids blocking the stream processing pipeline when dimension updates occur—critical because sampling site attributes (e.g., population served, flow meter recalibration factor) change infrequently but must be reflected within one hour of update. Third, the temporal table join for flow rate interpolation uses linear interpolation between the two nearest flow meter readings, handling the reality that many sewer catchment zones have only one flow meter per 10,000 meters of interceptors, creating gaps where flow must be estimated. Fourth, the UDF SQL expression converting pathogen concentration to mass load demonstrates a 86.4 multiplier—this converts liters per second to liters per day, a conversion that is frequently miscalculated in bespoke implementations. Fifth, the LATEST_BY_TIMESTAMP aggregation function for raw concentration ensures that the dashboard displays the most recent laboratory-confirmed value rather than an average that could mask sudden spikes during outbreak onset. Intelligent-Ps SaaS Solutions provides validated Flink job templates for wastewater flow normalization, including pre-tested UDF libraries for common conversion calculations and dimension table hot-reload mechanisms that avoid job restart during critical public health events.