Intuit MailchimpCustomer data platform

Joining on the Fly: How Intuit Mailchimp Rebuilt Its Real-Time Customer Data Platform on StarRocks

Intuit Mailchimp's Customer Data Platform holds about 3.6 trillion rows and answers audience segmentation queries that join several large, changing tables. After testing engines on live production queries, the team moved the platform from a batch data warehouse to StarRocks. It then replaced pre-aggregation and denormalization pipelines with aggregates and joins computed at query time, opened the platform to product teams and AI agents through a guarded SQL API, and gave each use case its own warehouse on shared data.

87×

faster P99 segmentation queries in the evaluation

191 s → 2.2 s on shadowed production queries

<10s

ingestion lag in the evaluation

Over 30 minutes on the previous warehouse

3.6T

rows in StarRocks

8 billion new rows a day from Kafka

CompanyIntuit Mailchimp
Use caseCustomer data platform
Previous solutionBatch data warehouse

Based on Jake Hilborn, Mohan Pandiyan and Caique Costa's session at NextQuery 2026, PhoenixAI's Data + AI Infrastructure Summit. All three are engineers on the Customer Data Platform team at Intuit Mailchimp. Watch the session

About Intuit Mailchimp

Mailchimp, part of Intuit, is a marketing platform where businesses build audiences of contacts and send them campaigns. Its Customer Data Platform (CDP) is the unified customer data layer behind audience segmentation, in-app analytics and context for AI agents.

Mailchimp's microservices, third-party integrations and monolith publish domain events to Kafka. The CDP ingests them into StarRocks and OpenSearch, and APIs on top of both serve the Mailchimp web product and backend services. The platform is petabyte scale. StarRocks holds about 3.6 trillion rows and ingests 8 billion rows a day, with peaks above 750,000 rows per second. Product teams and AI agents query the CDP through a guarded SQL API.

Reference architecture: email, ecommerce and other domain services (microservices, third-party integrations, monolith) publish domain events to Kafka. The Customer Data Platform ingests them into StarRocks and OpenSearch. StarRocks handles cross-entity joins, aggregates and counts, with one warehouse per use case (live clients, incident triage, experiments, AI Insights Agent) on shared data in S3. OpenSearch handles full-text search, point lookups and paginated lists. A SQL API on StarRocks serves product teams and agents, and the existing Query API reads from both stores and serves the Mailchimp web product and services
The Mailchimp Customer Data Platform on StarRocks

The challenge

Segments that took minutes on stale data

Before StarRocks, Mailchimp's Customer Data Platform (CDP) ran on a traditional data warehouse built for batch business intelligence. The CDP team had tuned and scaled it as far as it would go, and it still did not fit an interactive, customer-facing product.

Segmentation is how a marketer chooses who receives a campaign: for example, contacts subscribed to marketing emails who live in Chicago, have spent over $100 and opened at least one email in the past 30 days. Each segment is a SQL query that joins several large tables.

On the warehouse, these queries had a P75 of 20 seconds and a P99 of over 3 minutes. Marketers refine a segment step by step, so each step meant waiting tens of seconds or minutes. Queries timed out after 5 minutes to protect shared resources, and some segments could not be calculated at all.

Contact updates took over 30 minutes to reach the warehouse. A customer who updated their audience and sent a campaign right away could get a segment calculated on stale data. The campaign could then reach the wrong people.

Workarounds built around the warehouse

Mailchimp's previous batch warehouse was not built for thousands of concurrent user-facing queries, so the CDP team had compensated for it one fix at a time:

  • Pre-aggregation. For in-app reports, scheduled jobs computed an aggregate for every account and pushed it to OpenSearch. Each job fixed the dimension, time grain and freshness, so clicks by device instead of by day meant a new job, a new index and a backfill.
  • Denormalization. Joining orders to contacts for lifetime value was too slow on the warehouse, so a pipeline joined them upstream and wrote a denormalized contact. Similar pipelines were queuing behind it, one for each cross-entity question.
  • A predefined query API. Every join and aggregation the API could serve was defined in code first, so every new question meant a schema change, a deploy and a wait.

The solution

Testing StarRocks and ClickHouse on production traffic

Mailchimp's CDP team looked for a real-time, user-facing analytical database and compared its previous warehouse with StarRocks and ClickHouse on real production data and queries. Similarly sized StarRocks and ClickHouse clusters received live data from Kafka and history from the warehouse through S3. The production API kept returning warehouse results to callers, while also sending each query to both engines and logging execution times for all three.

At P99, segmentation queries ran 87 times faster on StarRocks than on the warehouse, 2.2 seconds against 191 seconds, and 6 times faster than ClickHouse. In the same test, both real-time engines kept ingestion lag under 10 seconds, against 30 minutes on the warehouse. StarRocks merges on write. ClickHouse ingested slightly faster, but it merges on read, so it pays that cost at query time.

Slide with a log-scale chart of segmentation query latency by percentile from P50 to P99 for the data warehouse, StarRocks and ClickHouse. At P99 the warehouse reaches 191 seconds and StarRocks 2.2 seconds, labeled 87x faster. A table below shows ingestion lag at P95 and P99: 30 and 31 minutes on the warehouse, 5,883 and 8,511 ms on StarRocks, and 3,763 and 5,424 ms on ClickHouse
Segmentation latency and ingestion lag in the production-traffic evaluation. Source: Intuit Mailchimp, NextQuery 2026

Why StarRocks handled the joins

Segmentation in Mailchimp's CDP is join-heavy, and the CDP team credits StarRocks' lead in the evaluation to how it distributes joins across nodes. One heavy segment from a large customer joins four tables on contact ID, each with 25 million to 700 million rows. StarRocks ran it in about 4 seconds, and in 1.5 seconds once its data cache was warm. ClickHouse took a little over 21 seconds.

With parallel replicas, ClickHouse splits the largest table across nodes, but every replica builds and holds hash tables for the three smaller tables. StarRocks uses a bucket shuffle on contact ID: it knows which backend node holds which contact IDs, so each node builds hash tables for only its share of the rows. The shuffle adds network traffic, but memory per node falls as nodes are added. In Mailchimp's experience, heavier queries on ClickHouse could use hundreds of gigabytes of RAM per node, which created a risk of one query starving the others.

Slide comparing the four-table join on three-node clusters. ClickHouse parallel replicas split the email_open probe side but copy the contact, contact_merge_fields and segment_membership hash tables to every replica, about 450 million rows per replica. StarRocks bucket shuffle splits both the probe side and the hash tables by contact ID, about 306 million rows per backend node. A side table lists row counts from 25 million to 700 million and first and second run times on each engine
How each engine distributes the four-table segmentation join. Source: Intuit Mailchimp, NextQuery 2026

Colocated joins in StarRocks can avoid the shuffle when tables are modeled for it, and ClickHouse has strategies that would speed up this query too. Mailchimp's CDP team found that StarRocks' shuffle join algorithms and cost-based optimizer kept queries fast even on tables that were not modeled optimally. Heavy joins and time-series aggregates both run well on the same tables, with no second version of each table.

Queries instead of pipelines

With segmentation in production on StarRocks, Mailchimp's CDP team revisited each workaround built for the previous warehouse, starting with the pre-aggregation jobs and the denormalization pipelines. Each domain is now ingested once into its own StarRocks tables, and answers are computed when the question is asked:

  • In-app reports are a GROUP BY over click events ingested as they happen. By day, by device or by link, the answer comes from the same table, and freshness equals ingestion latency.
  • Live subscriber counts were about to get their own Apache Flink job, with its own state and cluster. Because a contact is queryable in StarRocks shortly after it changes, the count became a COUNT with a WHERE clause, and the job was never built.
  • Lifetime value is a SUM over a join of orders and contacts, computed at query time, with no denormalization pipeline to maintain or backfill.

OpenSearch still serves full-text search, point lookups and paginated lists. Only the pieces that existed to work around Mailchimp's previous warehouse were removed.

A guarded SQL API for product teams and agents

Once StarRocks removed the database bottleneck, Mailchimp's predefined query API became the limit for the CDP. Product teams could live with it because they plan from a roadmap, but an AI agent asks the questions nobody wrote down in advance. It needs a query language, and for Mailchimp that is SQL.

Open SQL on a multi-tenant platform risks cross-tenant reads, soft-deleted rows, full-table scans, queries that break when a physical table changes, and injection from model-written text. Mailchimp's SQL API sits between clients and StarRocks and handles each risk by design:

  1. It parses every query into a syntax tree and validates it before it runs: only SELECT, only tables on an allow list, and nothing that reads a whole table.
  2. It rewrites the tree to add the caller's account scope and a soft-delete filter to every table reference, including subqueries. Clients never write a tenant filter and cannot write one that widens their scope.
  3. It maps the logical table names clients see to physical tables, so the tables underneath can change.
  4. It sends values as bind variables, never as SQL text.
  5. StarRocks enforces its own limits on time, memory and rows as the last line of defense.

Product teams and Mailchimp's AI agents now query through the same interface. Shipping a new data product on Mailchimp's CDP used to mean agreeing on a pre-aggregate, building a pipeline and coordinating a backfill, which took months. Now a product team writes a query, which takes days.

From shared compute to one warehouse per use case

Opening Mailchimp's CDP to product teams and an AI agent raised the risk of noisy neighbors, and demand rose as coding agents improved. With coding agents and automation tools, onboarding a new entity dropped from 1 to 2 weeks to days. The CDP reached its limits in weeks, sometimes days, instead of about a month. Balancing high-throughput ingestion with highly concurrent queries on one shared cluster grew harder.

The CDP team first added three layers of guardrails, all still in use. The application layer throttles queries and limits connections. In StarRocks, user-defined limits set maximum connections per user, concurrency limits and query timeouts per use case. StarRocks resource groups limit rows scanned, set hard memory limits on expensive queries and assign CPU weights for when use cases compete for the same hardware.

Noisy neighbors still appeared, because every CDP client shared the same physical compute and one heavy workload could affect ingestion and slow everyone else down.

Mailchimp then moved the CDP to the multi-warehouse feature in StarRocks, which runs several warehouses on the same shared data. Each use case now has its own compute, with the guardrails kept in place. A spike in one use case stays in its own warehouse, ingestion spikes are taken out of the equation, and each warehouse is tuned and scaled for its own workload. Before switching, the CDP team shadowed a share of production traffic, asynchronously, to a new warehouse and measured it against the original.

Tuning each warehouse

Running several warehouses for Mailchimp's CDP led to four tuning practices. On each new warehouse, the CDP team experiments with pipeline DOP, the StarRocks setting for query pipeline parallelism, whose best value depends on node count and query shape. For cache-sensitive use cases, it pre-warms a new warehouse's cache with the StarRocks warm-up feature and its own tooling. Problems and fixes are scoped to one use case across the stack, instead of one shared solution for every client team. When a use case is onboarded, its query profiles show the true cost of each query, so the CDP team and client teams can optimize on their own. Mailchimp relies mostly on off-the-shelf StarRocks features and optimization techniques.

StarRocksApache KafkaOpenSearchAmazon S3

The results

Results for Mailchimp's CDP on StarRocks:

  • Faster segments on fresher data in the evaluation: on shadowed production queries, P99 segmentation latency was 2.2 seconds on StarRocks against 191 seconds on the previous warehouse. Ingestion lag stayed under 10 seconds, against 30 minutes.
  • Fewer pipelines: reports and lifetime value are computed at query time instead of by scheduled aggregation jobs and a denormalization pipeline, and a planned Flink job for live counts was never built.
  • Days instead of months: one client team reported 90% less development time and 95% less debugging time.
  • Isolated workloads within SLA: in the shadow test, the isolated warehouse stayed within the SLAs defined for client teams. Its P50 was 109 ms and its P99 977 ms, against 264 ms and 1,616 ms on the shared original. During a production rollout, the original warehouse degraded while the isolated one held steady.
  • Zero-downtime upgrades: upgrades roll through one warehouse at a time.
  • Safer room to experiment: experiments and data exploration in production run on dedicated compute, and engineers have their own warehouse for incident triage and ad hoc queries, so tooling once too risky for production can now be built.
Slide titled Shadowing results. A table compares query latency on the original and isolated warehouses: P50 264 ms and 109 ms, P99 1,616 ms and 977 ms, P100 5,099 ms and 4,263 ms. Two callouts say the isolated workload fit within client SLAs and held steady while a production rollout degraded the original warehouse
Query latency on the original and isolated warehouses during production shadowing. Source: Intuit Mailchimp, NextQuery 2026

Next, Mailchimp plans more internal and external agents and tooling on the same CDP architecture, isolated federated queries against other data stores across the company, and stronger disaster recovery: emergency warehouse swapping, quick snapshot recovery and multi-region availability.