03 — Analytics / OLAP

<aside> 📊

Буллеты про аналитические БД и OLAP: ClickHouse, BigQuery, Snowflake, Redshift, Druid, Pinot, lakehouse, real-time analytics и data governance.

</aside>

ClickHouse

Built a ClickHouse cluster ingesting X billion events/day and serving analytical queries over X TB at sub-second p95.
Cut insert overhead X% by replacing row-by-row writes with asynchronous batched inserts of X rows.
Designed MergeTree tables with an X-column ORDER BY and LowCardinality types, shrinking storage by X%.
Built X materialized views pre-aggregating metrics on ingest, cutting dashboard queries from Xs to Xms.
Introduced ReplacingMergeTree with version columns for idempotent CDC ingestion of X million rows/day.
Reduced storage cost X% through per-column codec tuning (Delta, DoubleDelta, ZSTD) across X columns.
Partitioned by month with X-day TTL and tiered moves to cold storage, keeping hot data under X TB.
Migrated an analytical workload from PostgreSQL to ClickHouse, cutting p95 report time from Xs to Xms.
Operated an X-shard by X-replica cluster with distributed tables, scaling read throughput to Xk QPS.
Optimized X slow queries using PREWHERE, projections and skip indexes, cutting scanned rows X%.
Built a Kafka-engine ingestion path consuming X topics at Xk msg/sec with dedup-key exactly-once semantics.
Eliminated memory-limit failures by tuning max_memory_usage and rewriting X aggregations to spill to disk.
Decreased insertion time into ClickHouse by X% using a low-level native-protocol client and materialized views.
Added a query cache and per-user quotas, cutting repeated dashboard load on the cluster by X%.

Cut BigQuery spend X%/month by partitioning and clustering X datasets and enforcing per-user query quotas.
Modelled an X-table warehouse in dbt with X automated tests, reducing analyst-reported data incidents X%.
Replaced full refreshes with incremental models, cutting the daily pipeline from Xh to Xmin.