technical-setupanalyticsdeal-scanner

Designing Deal Scanners That Scale: Using OLAP Tech (ClickHouse) for Real-Time Offers

UUnknown

2026-02-18

10 min read

Architect a ClickHouse-backed deal scanner to deliver sub-second pricing and personalized offers for landing pages. Practical steps and SQL patterns included.

Hook: If your deal scanner is slow, your landing pages are leaking revenue

Marketing teams and product owners constantly face the same pain: pricing, inventory and offer pages that lag behind real-time data. That delay costs conversions, especially during flash sales or high-traffic launches. This article shows how to architect a scalable deal scanner using an OLAP backend—specifically ClickHouse—to power real-time offers and dynamic landing pages that refresh instantly without blowing up costs.

Executive summary — the bottom line first

Use ClickHouse as the analytical backbone for deal scanning and personalization by combining high-throughput streaming ingestion, pre-aggregated materialized views, lightweight per-request joins, and a thin serving layer (Redis/CDN/Edge functions). The result: sub-100ms rule evaluation for offers, continuous pricing updates, accurate personalization, and a system that scales to millions of scans per minute.

Why OLAP (ClickHouse) for web apps and deal scanners in 2026?

By late 2025 ClickHouse's growth and funding signaled broad enterprise acceptance of OLAP for operational workloads. In 2026 the trend is clear: analytics systems must be both fast and transactional enough to drive user-facing decisions. ClickHouse sits in the sweet spot—columnar performance for aggregations plus integration with streaming systems like Kafka and ingestion engines for near-real-time freshness. For deal scanners this means:

High-throughput ingestion of events (views, clicks, inventory, price changes).
Fast aggregations and cohort computations for personalization.
Cost-effective storage of detailed event history for audit and rollback.

Core architecture: components and dataflow

At a high level, a ClickHouse-powered deal scanner has five layers:

Event & Change Ingestion: Price updates, inventory changes, user events streamed into the system (Kafka, Pulsar, or cloud streaming).
OLAP Store (ClickHouse): Raw events are stored in append-only tables; materialized views create rollups and features.
Feature & Dictionary Layer: Fast lookup tables (Redis or ClickHouse dictionaries) for product metadata, cached price lists, and TTL-based overrides.
Serving & Personalization Layer: API layer that composes offers per request using pre-aggregates and small joins, and caches results at the edge (CDN).
Observability & Control: Metrics, alerts, golden datasets for testing, and A/B experiments stored back into ClickHouse.

Dataflow in practice

Example sequence:

Point-of-truth system publishes a price change to Kafka.
ClickHouse consumes via Kafka engine into a raw events table with millisecond timestamps.
Materialized view updates a distributed table that holds latest prices per sku and precomputed offer eligibility buckets.
The landing page API queries ClickHouse for the item, looks up personalization signals from a Redis cache (fed by ClickHouse aggregations), and returns the latest price and qualifying offers.
CDN caches the landing page variant with a short TTL; on invalidation the API triggers a cache purge or pushes a new ETag.

ClickHouse specifics: tables, materialized views, and dictionaries

The design choices in ClickHouse determine freshness, query latency, and cost. Use these patterns:

Raw event tables — append-only MergeTree with partitioning by day and a timestamp column. Retain raw events for audits and backfills.
Latest-state tables — create materialized views that aggregate raw events into "latest" rows (e.g., latest_price_by_sku) using the argMax aggregator. Querying a small table is much faster than scanning histories.
Distributed tables — use distributed tables to route queries across shards in production clusters. This provides horizontal scale for scans and ensures availability; consider careful sharding by sku or customer segment for even distribution.
Dictionaries — external dictionaries (file, MySQL, or ClickHouse) are ideal for sub-ms lookups for product metadata and feature flags. Use dictionaries for joins where freshness tolerance is seconds to minutes.

Example SQL patterns

Materialize latest price per sku using argMax. This snippet shows the pattern; adapt column names to your schema.

CREATE MATERIALIZED VIEW latest_price_mv TO latest_price AS SELECT sku, argMax(price, event_ts) AS price, argMax(change_id, event_ts) AS change_id, max(event_ts) AS ts FROM price_changes_raw GROUP BY sku;

For aggregated feature generation (e.g., user purchase frequency):

CREATE MATERIALIZED VIEW user_purchase_agg TO user_features AS SELECT user_id, countIf(event_type='purchase') AS purchases_7d FROM events_raw WHERE event_ts >= now() - INTERVAL 7 DAY GROUP BY user_id;

Real-time personalization engine: composing offers per request

Real-time offer composition must balance latency and accuracy. Use ClickHouse for heavy aggregation and a fast cache-based layer for per-request lookups:

Precompute heavy features in ClickHouse (cohorts, recency, conversion propensity) with minutes-level freshness.
Store lightweight, per-user features in Redis for sub-ms access—these are populated and refreshed from ClickHouse materialized views or via streaming updates.
Policy engine applies business rules. Simple rules can be evaluated in the API; complex ML scoring can be served from a model server or inlined via vector similarity if using embeddings.

This hybrid approach keeps per-request latency low while preserving the analytical horsepower of ClickHouse.

Low-latency patterns and join strategies

Joins across wide tables are a common cause of slow queries. Use these strategies:

Denormalize critical attributes into latest-state tables so a request touches one small table.
Use dictionaries for high-throughput, low-latency lookups instead of runtime joins when possible.
Pre-join in materialized views for common access patterns (e.g., sku + campaign eligibility).
Limit result cardinality and use LIMIT and sample() for exploratory analytics rather than user-facing queries.

Streaming ingestion & freshness targets

Freshness is a spectrum. For pricing updates you often need sub-second to second latency. For historical personalization features, minutes might be acceptable. Design SLAs:

Sub-second — use Kafka / ClickHouse Kafka engine + Buffer to land changes; push critical price updates directly into the latest_price table via a lightweight API when atomicity is required.
1–30 seconds — materialized views consuming Kafka provide near-real-time aggregations suitable for most personalization signals.
Minutes — nightly or hourly batch rollups for expensive features and cohort recalculations.

Serving layer and APIs

Keep the serving layer thin and deterministic:

API server performs three fast operations per request: get product + price, fetch user features, run rule engine.
Edge caching saves dozens of milliseconds and reduces cluster load. Use cache keys that encode user segment and experiment bucket.
Graceful degradation — when ClickHouse is under heavy load, fall back to last-known-good price from Redis or CDN-stored variants.

Integrations and telemetry

Integrate with tools and processes you already use:

Kafka/Pulsar for event ingress.
Prometheus + Grafana for metrics; ClickHouse exposes system tables for disk, merge queue, query latency.
Tracing (OpenTelemetry) to measure end-to-end latency from event to visible offer.
Audit & GDPR controls — use TTL on raw event tables and anonymization functions where required.

Scaling, replication, and high availability

Design for shard-level scaling and replication:

Sharding by sku or customer segment ensures even distribution for high-cardinality scans.
Replicated MergeTree tables give data redundancy and allow maintenance with minimal downtime.
Load isolation — separate analytic clusters from the serving cluster or use quotas and query timeouts to protect latency-sensitive queries.

Operational best practices

Make ClickHouse predictable in production:

Use query profiling and limit long-running queries in the serving path.
Monitor merge and mutation queues; avoid large mutations during peak traffic.
Automate schema migrations and data backfills with idempotent jobs.
Maintain small partitions for fast pruning; daily or hourly partitions depending on write volume.

Cost and performance tradeoffs

Columnar storage is efficient for aggregations but less so for tiny per-row updates. Strategies to control cost:

Store only change events in ClickHouse; compute latest state in materialized views rather than rewriting rows.
Use hybrid storage: hot ClickHouse cluster for recent data, cheaper cold storage for history.
Keep per-request work minimal and cache aggressively at the edge.

2026 trends and future-proofing your design

Design with modern trends in mind:

Edge personalization — more personalization will run at CDN edge via serverless functions. Make precomputed features available via fast APIs or edge-replicated caches.
Privacy-first personalization — expect increasing reliance on first-party data and on-device signals; ClickHouse can be the analytics store for aggregated, privacy-compliant features. Consider Hybrid Sovereign Cloud designs for highly regulated datasets.
Multimodal scoring — integrating embeddings for product similarity or session intent is becoming common; pair ClickHouse features with a vector store for nearest-neighbor lookup.
Real-time cost-awareness — dynamic routing of queries between hot and warm clusters to reduce cloud bills without sacrificing SLAs.

Common pitfalls and how to avoid them

Trying to run million-row transactional updates in ClickHouse — instead, model changes as events and materialize state.
Over-joining in user-facing queries — denormalize and use dictionaries.
Ignoring merge queue behavior — large merges during peak times can spike I/O; schedule heavy mutations off-peak.
Insufficient observability — monitor both ClickHouse internals and end-to-end offer latency; keep post-incident playbooks and incident comms ready.

Step-by-step implementation plan (90-day runway)

Week 1–2: Map data sources (price feeds, inventory, events), define SLAs for freshness and latency.
Week 3–4: Stand up a ClickHouse dev cluster, create raw event tables, and ingest sample streams from Kafka.
Week 5–6: Build materialized views for latest_state and core features; populate a Redis cache for per-request lookups.
Week 7–8: Implement the offer composition API that queries latest_state and Redis, return offer payloads, and integrate edge caching.
Week 9–12: Load test with synthetic traffic, tune partitioning/sharding, add observability dashboards, and run a soft rollout on a subset of traffic.

Real-world example: flash sale use case

During a flash sale the critical needs are sub-second price updates, per-user eligibility checks, and rapid cache invalidation. Implementation sketch:

Price updates go through Kafka and are also shadow-written to a low-latency update API that writes to a small "hot" table optimized for immediate reads.
Materialized views merge that hot table with the price history into latest_price, available for APIs.
Landing pages hit the API for price and offer eligibility, which returns an ETag. CDN caches per-segment variants for 5–15 seconds.
When a price update arrives, the system publishes an event to the CDN invalidation queue based on affected skus.

Measuring success

Track these KPIs to validate the architecture:

End-to-end offer latency (goal: <100ms for API composition).
Time from price update to visible change on landing pages (goal: <2s for critical updates).
Conversion uplift and revenue captured during launches.
System cost per million scans compared to alternative designs.

Final notes and 2026 perspective

ClickHouse has become a mainstream option for powering operational analytics and real-time decisioning. With proper schema design, streaming ingestion, and a hybrid serving layer that leverages both ClickHouse and fast caches, you can build a deal scanner that scales to high traffic while keeping latency low. The key is to treat ClickHouse as the analytical engine—fast at aggregations and rollups—while using caches and dictionaries for per-request speed.

Architect your system so the slow, heavy work is precomputed. Real-time decisions should only do what’s necessary at request time.

Get started checklist

Identify event sources and define freshness SLAs.
Design raw event schema and partitioning strategy for ClickHouse.
Create materialized views for latest state and key features.
Set up Redis/dictionaries for super-fast lookups.
Implement a thin API and edge cache strategy with short TTLs and invalidation hooks.
Deploy observability and load testing before rollout.

Call to action

If you want a practical blueprint tailored to your stack, we can map a 90-day implementation plan and provide sample ClickHouse schemas and materialized view templates that match your product catalog and traffic profile. Start a free architecture review or download our ClickHouse deal scanner playbook to accelerate your next launch.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.