--- title: "ClickHouse machine learning pipelines" excerpt: "ClickHouse feeds ML pipelines with feature stores, batch exports, and online inference queries over streaming event data." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-06-25 00:00:00" publishedOn: "2026-06-25 00:00:00" updatedOn: "2026-06-25 00:00:00" status: "published" --- Machine learning pipelines have a data infrastructure problem that sits between training and serving. Training works on static snapshots. Inference needs features computed over live data, at low latency, for every prediction request. The gap between how features are computed for training and how they are served in production is the source of most ML feature skew and a large portion of ML infrastructure complexity. ClickHouse® fits this pipeline as the analytical layer that computes features over event streams fast enough for online serving. It stores the raw events your models depend on, materializes feature aggregations incrementally as data arrives, and serves them via parameterized SQL endpoints fast enough to use at inference time. This post covers the feature engineering patterns, online feature store architecture, training data extraction, model monitoring, and A/B experiment analytics that ML teams build with ClickHouse. ## Event log as feature source ML features for behavioral models are typically aggregations over time windows: how many purchases in the last 7 days, average session duration in the last 30 days, time since last login. These are computations over an event log, which is exactly what ClickHouse is designed for. The raw event table stores the full behavioral history: ```sql CREATE TABLE user_events ( event_time DateTime64(3), event_type LowCardinality(String), user_id String, session_id String, item_id Nullable(String), category LowCardinality(Nullable(String)), amount Nullable(Float64), duration_ms Nullable(UInt32), source LowCardinality(String), properties String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (user_id, event_time); ``` Feature computation over this table uses ClickHouse's aggregation functions with conditional logic, running across billions of rows in seconds: ```sql SELECT user_id, countIf(event_type = 'purchase') AS purchases_30d, countIf(event_type = 'view') AS views_30d, countIf(event_type = 'session_start') AS sessions_30d, sumIf(amount, event_type = 'purchase') AS spend_30d, avg(duration_ms) AS avg_session_ms_30d, uniq(category) AS distinct_categories_30d, dateDiff('day', max(event_time), now()) AS days_since_last_event, dateDiff('day', minIf(event_time, event_type = 'purchase'), now() ) AS days_since_first_purchase FROM user_events WHERE user_id = {{ String(user_id, required=True) }} AND event_time >= now() - INTERVAL 30 DAY GROUP BY user_id; ``` This query, running against a multi-billion row event table with `user_id` first in the sort key, returns in milliseconds. It is fast enough to call at inference time. ## Materialized feature store For features needed by many models at high request rates, pre-materializing them into an `AggregatingMergeTree` avoids recomputing the aggregation on every inference request. This is the [serverless online feature store](https://www.tinybird.co/blog/using-tinybird-as-a-serverless-online-feature-store) pattern: ```sql CREATE TABLE user_features_1d ( day Date, user_id String, purchases UInt32, views UInt32, sessions UInt32, spend Float64, avg_session_ms AggregateFunction(avg, UInt32), distinct_cats AggregateFunction(uniq, String) ) ENGINE = AggregatingMergeTree PARTITION BY toYYYYMM(day) ORDER BY (user_id, day); CREATE MATERIALIZED VIEW user_features_1d_mv TO user_features_1d AS SELECT toStartOfDay(event_time) AS day, user_id, countIf(event_type = 'purchase') AS purchases, countIf(event_type = 'view') AS views, countIf(event_type = 'session_start') AS sessions, sumIf(amount, event_type = 'purchase') AS spend, avgState(duration_ms) AS avg_session_ms, uniqState(category) AS distinct_cats FROM user_events GROUP BY day, user_id; ``` Querying the 30-day feature window from the pre-aggregated table reads 30 rows per user instead of scanning all their events: ```sql SELECT user_id, sum(purchases) AS purchases_30d, sum(views) AS views_30d, sum(sessions) AS sessions_30d, sum(spend) AS spend_30d, avgMerge(avg_session_ms) AS avg_session_ms_30d, uniqMerge(distinct_cats) AS distinct_cats_30d FROM user_features_1d WHERE user_id = {{ String(user_id, required=True) }} AND day >= today() - 30 GROUP BY user_id; ``` ## Training data extraction For batch training jobs, ClickHouse exports labeled training datasets efficiently by reading only the columns needed for the feature set: ```sql -- Point-in-time correct feature extraction for training -- Each row is a label event with features computed as of that event's time SELECT e.user_id, e.event_time AS label_time, e.amount AS label_amount, h.purchases_7d, h.views_7d, h.sessions_7d, h.spend_7d, h.days_since_last_session FROM user_events AS e INNER JOIN ( SELECT user_id, event_time AS as_of_time, countIf( event_type = 'purchase' AND event_time >= event_time - INTERVAL 7 DAY AND event_time < event_time ) AS purchases_7d, countIf( event_type = 'view' AND event_time >= event_time - INTERVAL 7 DAY ) AS views_7d, countIf(event_type = 'session_start') AS sessions_7d, sumIf(amount, event_type = 'purchase') AS spend_7d, dateDiff('day', max(event_time), event_time) AS days_since_last_session FROM user_events WHERE event_time >= today() - 90 GROUP BY user_id, event_time ) AS h ON e.user_id = h.user_id AND e.event_time = h.as_of_time WHERE e.event_type = 'purchase' AND e.event_time BETWEEN '2026-01-01' AND '2026-06-01' ORDER BY e.event_time; ``` ClickHouse exports to Parquet, CSV, or JSON natively. For Python-based training pipelines: ```python # Using clickhouse-connect for training data export import clickhouse_connect import pandas as pd client = clickhouse_connect.get_client(host='your-host', ...) df = client.query_df(""" SELECT user_id, purchases_30d, spend_30d, sessions_30d, label FROM training_features WHERE created_date >= '2026-01-01' """) ``` The [ClickHouse Python client](https://www.tinybird.co/blog/clickhouse-python-example) and [SQL and Python integration](https://www.tinybird.co/blog/sql-and-python) patterns cover the connection setup, query streaming for large datasets, and integration with pandas and Arrow for the ML toolchain. ## Model monitoring and drift detection Once a model is in production, monitoring feature distributions over time detects training-serving skew and data drift before model performance degrades: ```sql SELECT toStartOfWeek(event_time) AS week, avg( JSONExtractFloat(properties, 'feature_purchases_30d') ) AS avg_purchases_feature, stddevPop( JSONExtractFloat(properties, 'feature_purchases_30d') ) AS stddev_purchases_feature, quantile(0.5)( JSONExtractFloat(properties, 'feature_spend_30d') ) AS median_spend_feature, quantile(0.95)( JSONExtractFloat(properties, 'prediction_score') ) AS p95_score, avg(JSONExtractFloat(properties, 'prediction_score')) AS avg_score FROM inference_log WHERE model_name = {{ String(model_name, 'churn_v3') }} AND event_time >= today() - 90 GROUP BY week ORDER BY week; ``` Week-over-week drift in feature distributions, before model performance metrics are available, is an early warning signal. ClickHouse handles the volume of inference logs (millions of predictions per day) without requiring a separate monitoring database. ## Batch scoring and prediction pipelines For models that score all users nightly rather than scoring on demand, ClickHouse exports the full feature set for batch inference efficiently. Rather than scanning the raw event table for each user independently, the pre-aggregated feature table reads at most 30 rows per user: ```sql INSERT INTO batch_scoring_input SELECT user_id, now() AS scoring_time, sum(purchases) AS purchases_30d, sum(views) AS views_30d, sum(sessions) AS sessions_30d, sum(spend) AS spend_30d, avgMerge(avg_session_ms) AS avg_session_ms_30d, uniqMerge(distinct_cats) AS distinct_cats_30d FROM user_features_1d WHERE day >= today() - 30 GROUP BY user_id HAVING purchases_30d > 0 OR sessions_30d >= 3; ``` Predictions write back as events to Tinybird via the Events API, and a second materialized view tracks the score distribution over time for drift monitoring. The cycle from feature extraction to score write-back is fully SQL-driven and runs against the same datasource that serves online features, with no separate offline pipeline. ## Feature importance and correlation analysis ClickHouse supports statistical functions that are useful for quick feature evaluation before expensive model training runs. Computing pairwise correlations between candidate features and a target variable: ```sql SELECT corr(purchases_30d, label_converted) AS purchases_corr, corr(sessions_30d, label_converted) AS sessions_corr, corr(spend_30d, label_converted) AS spend_corr, corr(days_since_last_event, label_converted) AS recency_corr, corr(distinct_categories_30d, label_converted) AS diversity_corr FROM training_features WHERE training_split = 'train' AND created_date >= today() - 90; ``` For computing feature distributions by label class, useful for detecting features with good class separation: ```sql SELECT label_converted, avg(purchases_30d) AS avg_purchases, stddevPop(purchases_30d) AS stddev_purchases, quantile(0.5)(spend_30d) AS median_spend, quantile(0.95)(spend_30d) AS p95_spend, avg(sessions_30d) AS avg_sessions FROM training_features WHERE created_date >= today() - 30 GROUP BY label_converted; ``` These queries run against billions of rows in ClickHouse in seconds, making rapid feature iteration practical. Testing a new feature hypothesis, computing its correlation with the target across the full training set, costs a single SQL query rather than a full training run. ## A/B experiment analytics For measuring ML model improvements in production via [real-time personalization](https://www.tinybird.co/blog/real-time-personalization) experiments: ```sql SELECT experiment_id, variant, count() AS exposures, countIf(converted) AS conversions, round( countIf(converted) / count() * 100, 2 ) AS conversion_rate_pct, avg(revenue) AS avg_revenue_per_user, quantile(0.95)(revenue) AS p95_revenue FROM experiment_events WHERE experiment_id = {{ String(experiment_id, required=True) }} AND event_time >= today() - 14 GROUP BY experiment_id, variant ORDER BY variant; ``` ## Tinybird for ML pipelines ML engineering teams at product companies face a consistent infrastructure challenge: the feature computation that works in a Jupyter notebook does not serve at inference latency without significant engineering investment. Building a feature store from scratch, with online serving, offline training exports, and real-time ingestion, is a multi-month project. Tinybird is managed ClickHouse that acts as [the serverless online feature store](https://www.tinybird.co/blog/using-tinybird-as-a-serverless-online-feature-store) for ML teams who want feature serving without feature store infrastructure. The materialized view patterns in this post run inside Tinybird datasources, computing features incrementally as events arrive. The feature query becomes a Tinybird Pipe, published as an HTTP endpoint your inference service calls with a `user_id` parameter. For [vector search and embedding workloads](https://www.tinybird.co/blog/vector-search-improvements), Tinybird has added native vector similarity functions that make nearest-neighbor retrieval feasible inside SQL Pipes, enabling semantic retrieval and approximate matching alongside traditional analytical features in the same query. For [Python SDK](https://www.tinybird.co/blog/python-sdk) integration, the Tinybird Python client handles authentication, query parameterization, and response parsing in a few lines of code. Training data exports run as standard SQL against Tinybird datasources, with results streamed to pandas DataFrames or Arrow tables for direct use in PyTorch, scikit-learn, or XGBoost pipelines. The same datasource that serves online features serves offline training exports, eliminating the training-serving skew that comes from maintaining two separate feature computation paths. On model deployment, the feature endpoint and inference log datasource live in the same Tinybird workspace. The ML team defines features in SQL Pipes, the inference service calls those Pipes at prediction time, and the inference log writes back to Tinybird via the Events API. Monitoring queries run against the inference log. The entire ML data loop, from raw events through feature serving through model monitoring, stays in one platform. Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Feature endpoints for online inference run inside that latency envelope. Your model's feature fetch adds under 100ms to inference latency. Tinybird is SOC 2 Type II certified. The pipeline from raw events to a serving feature API takes hours to build rather than months. ## The same data, twice Most ML infrastructure complexity comes from having two paths through the same data: one for training, one for serving. Training reads from a data warehouse or object store. Serving reads from a Redis cache or a feature store. When the paths diverge, features drift. Debugging production model degradation means reconciling two separate computation environments. ClickHouse collapses those two paths. The same materialized view that computes training features computes serving features. The same SQL that generates training exports generates online feature responses. There is no synchronization problem because there is no synchronization. The data is in one place. The queries run in one place. The gap between what the model was trained on and what it receives at inference time narrows to the materialization latency of the view, typically seconds. {% cta title="Feature engineering at production scale" text="Tinybird is managed ClickHouse with a streaming Events API and real-time SQL endpoints. From raw events to feature APIs in hours." button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"} /%}