ClickPy at 2 Trillion rows: Scaling ingestion and fixing the past

Key Actionable Insights

1

Use a parallel staging environment when migrating data pipelines at scale. Clone table schemas and materialized views into an isolated database, run both old and new pipelines simultaneously, and compare outputs before switching over. This approach ensures zero risk to production data during the transition.

This pattern is especially critical when dealing with datasets too large to re-ingest from scratch, where corruption or data loss during migration would be catastrophic.

2

Leverage the Null engine with materialized views as a transformation layer to decouple ingestion from data transformation. By having ClickPipes write to a Null engine table and using a materialized view to transform and route data to the final table, you get a clean separation of concerns that is easier to evolve and debug.

This pattern centralizes transformation logic inside ClickHouse rather than in external scripts, making it version-controlled and inspectable as part of the database schema.

3

Distinguish between daily and non-daily aggregation tables when performing historical data corrections. Daily-aggregated tables allow targeted deletes and re-ingestion via materialized views, while non-daily aggregations require dropping and recreating materialized views to prevent duplicate data during backfills.

Failing to account for this distinction can lead to silently duplicated data in aggregation tables, producing incorrect analytics results that are difficult to detect.

4

Regularly validate source data against your analytical database to catch ingestion gaps early. At trillion-row scale, missing data is easy to overlook because queries still work and dashboards still render, but results are quietly incorrect. Implement daily row count comparisons between source and destination systems.

The ClickPy team only discovered historical discrepancies when they compared BigQuery and ClickHouse data during the pipeline migration, highlighting the need for ongoing data quality monitoring.

5

Replace custom cron-based ingestion scripts with managed ingestion services to reduce operational overhead. Hand-rolled retry logic, failure handling, and progress tracking are error-prone and require ongoing maintenance, whereas managed services provide these capabilities out of the box.

The original ClickPy pipeline required significant scripting because managed ingestion (ClickPipes) didn't exist yet. Once available, the migration eliminated several categories of operational risk.

6

When migrating to a continuous ingestion service that doesn't support arbitrary start points, redirect new source data to a fresh storage location. Configure the new pipeline to read from this fresh bucket, avoiding the need to reprocess historical data while ensuring no gap in new data ingestion.

This approach was used for ClickPipes, which tracks processed files but cannot start from a specific timestamp, making a fresh GCS bucket the pragmatic solution.