• Burst syncs. One user offline a week → ~15K rows in seconds. Pipeline must absorb spikes without lag bleed.
• 5-min freshness. Rules out hourly batch. Rules in CDC + streaming.
• Updates & deletes propagate. Can't poll on updated_at (column doesn't exist; also misses deletes). Binlog CDC is mandatory.
• Scale 150M → 1B+. Columnar OLAP, not OLTP. Compression matters a lot here.
• Multi-tenant (230 apps). Tenant pruning needs to be in the storage layout.
• Likely regulated. Health data (GDPR — sample user is in Berlin). Deletion must be real, not soft.

# Debezium MySQL connector — the settings that matter
connector.class: io.debezium.connector.mysql.MySqlConnector
database.include.list: thryve
table.include.list: thryve.app, thryve.end_user, thryve.epoch_data, ...

# Snapshot once, then tail binlog (no table locks, uses InnoDB MVCC)
snapshot.mode: initial
snapshot.locking.mode: none

# Performance for the bursty epoch_data table
max.batch.size: 8192
max.queue.size: 32768
poll.interval.ms: 500

# Topic per table, keyed by PK → in-row ordering preserved
topic.prefix: cdc.thryve
message.key.columns: thryve.epoch_data:id

# Flatten Debezium envelope; mark deletes via __deleted=true
transforms: unwrap
transforms.unwrap.type: io.debezium.transforms.ExtractNewRecordState
transforms.unwrap.delete.handling.mode: rewrite

-- Fact mirror (denormalized app_id for tenant pruning)
CREATE TABLE epoch_data (
  id              UInt64,
  end_user_id     UInt32,
  app_id          UInt16,    -- denormalized
  data_type_id    UInt32,
  data_source_id  UInt16,
  start_ts        DateTime64(3, 'UTC'),
  end_ts          Nullable(DateTime64(3, 'UTC')),
  created_at      DateTime64(3, 'UTC'),
  double_value    Nullable(Float64),
  long_value      Nullable(Int64),
  boolean_value   Nullable(UInt8),
  string_value    Nullable(String),
  trustworthiness_id UInt8,
  _version        UInt64,       -- binlog pos
  _deleted        UInt8
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(start_ts)
ORDER BY (app_id, end_user_id, data_type_id, start_ts, id)
TTL toDate(start_ts) + INTERVAL 2 YEAR DELETE;

-- Incremental MV: daily resting HR per user
CREATE MATERIALIZED VIEW mv_daily_resting_hr
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (app_id, end_user_id, date)
AS SELECT
  app_id, end_user_id,
  toDate(start_ts) AS date,
  avgState(long_value) AS avg_hr,
  countState()         AS samples
FROM epoch_data
WHERE data_type_id = 3001   -- HeartRateResting
  AND _deleted = 0
GROUP BY app_id, end_user_id, date;

-- 90-day trend query — sub-second on billions of rows
SELECT date, avgMerge(avg_hr) AS resting_hr
FROM mv_daily_resting_hr
WHERE end_user_id = 42817 AND date >= today()-90
GROUP BY date ORDER BY date;

-- Recommended pattern: a view that hides the dedup logic
CREATE VIEW epoch_data_current AS
SELECT
  id, end_user_id, app_id, data_type_id, data_source_id,
  start_ts, end_ts, created_at,
  argMax(double_value, _version)   AS double_value,
  argMax(long_value, _version)     AS long_value,
  argMax(boolean_value, _version)  AS boolean_value,
  argMax(_deleted, _version)       AS is_deleted
FROM epoch_data
GROUP BY id, end_user_id, app_id, data_type_id,
         data_source_id, start_ts, end_ts, created_at
HAVING is_deleted = 0;

• Schema registry (Apicurio). Today: schemas are inline in Kafka messages. Becomes painful when 2+ consumers depend on stable shape. Add in 6 months.
• dbt project on top of ClickHouse. Today: MVs encode the transformations. dbt becomes essential when the data team starts proposing models — adds versioning, tests, docs.
• Data quality framework (Great Expectations or Soda). Today: handful of CH queries on a Grafana dashboard. Promote when we have >10 metrics worth tracking.
• Flink for real-time anomaly detection. Today: CH can do windowed queries fast enough for <5-min alerts. Flink wins at sub-second.
• Iceberg lakehouse for AI training. Today: archive Kafka → S3 Parquet hourly. Promote to Iceberg + Trino when ML needs reproducible point-in-time snapshots.

• Multi-region HA. Today: single region. Add when we have customers requiring data residency or when uptime SLA needs >99.5%.
• Fine-grained RBAC. Today: 2 roles (read, write). Add when data team has >5 people with different access scopes.
• PII tokenization layer. Today: PII columns flow as-is to ClickHouse (encrypted at rest). For HIPAA/stricter compliance, tokenize at Debezium SMT layer.
• Self-serve schema evolution. Today: data engineer reviews each MySQL DDL. Acceptable since the backend team is small. Automate when schema changes > weekly.
• Cost telemetry. Today: monthly invoice review. Add query cost attribution per team when we have >3 consumers competing for warehouse time.