Carbon Arc builds data infrastructure for the AI economy. We take in third-party datasets, such as card transactions, point-of-sale data, clickstream, foot traffic and building permits, and map every record to a proprietary ontology of companies, brands, tickers and places. Clients reach that data in two ways: through our web platform, and through the Carbon Arc MCP server, which AI agents call to pull insight data straight into an analysis.
Both paths end in the same place: a query against a StarRocks cluster that reads Apache Iceberg tables on S3. As MCP traffic grew, a small set of expensive queries came to dominate that cluster. We moved them onto 28 precomputed StarRocks materialized views that now serve 928 Explorer views. Two weeks after the October 20, 2026 production release, the slowest 1% of MCP queries on those views take 5.0 seconds instead of 24.7, and MCP data calls slower than 20 seconds fell from 8.4% to 0.9%.
This post explains why the hot queries were slow, how we chose what to precompute, the design that lets every existing query use the precomputed data with no application change, and what we learned running it on S3 and Iceberg.
The challenge
Every Explorer dataset that clients query is a logical view. Our dbt project defines 17,344 of them, and each one aggregates a certified Iceberg table at query time. That keeps the data fresh and storage cheap, but it means the cluster redoes the same heavy rollup every time someone asks.
The worst case was our point-of-sale dataset. A brand-by-day view read a 7.0-billion-row, 188 GB Iceberg table across 2,835 files to return about 2.5 million rows, a 2,800-to-1 reduction repeated on every query. A single brand over the last 90 days took 82 seconds with a cold cache and 0.24 seconds warm. The pipeline rewrites the trailing 92 days every morning, so the cache was cold exactly where users look.
The cost was concentrated. Over one week in October, the StarRocks audit log showed that MCP queries on the point-of-sale views used 68.9% of all MCP query CPU, about 15 CPU-minutes per query. Clickstream views took another 13.6%, and the top clickstream queries each spent 1,600 to 1,860 CPU-seconds. Weighted by how often each view was queried, four datasets accounted for 67% of all MCP time spent waiting past five seconds.
Those few queries also slowed everything else. They shared compute with every other client request, so one heavy rollup pushed ordinary queries into the queue. At the same time, we were planning for MCP traffic to grow by orders of magnitude. MCP traffic used about 1.6 StarRocks cores on average in October, so 1,000 times the traffic would need roughly 1,600 cores against a production peak of about 105. Adding hardware would not close that gap; doing less work per query would.
Solution overview
Our data lake is Iceberg on S3. Apache Spark jobs on EMR write each dataset’s certified tables, an Apache Polaris catalog tracks them, and Apache Airflow orchestrates the pipeline. StarRocks reads those tables in place through an external catalog: our production cluster runs on CelerData Cloud in our AWS account on Graviton instances, and our development cluster runs open source StarRocks on EKS.
We added one layer between the Iceberg tables and the views clients query. For each expensive query family, a StarRocks asynchronous materialized view stores the rollup once, at the grain the views actually group by (for example, entity by state by day). The Explorer views keep their names and columns, but now read the materialized view instead of the raw table. The pipeline refreshes each materialized view right after the Iceberg commit it depends on, on compute that is kept apart from client traffic.
Four design decisions
Each decision starts with a problem we hit, then what we built and what we measured.
1. Precompute where the CPU goes, not where the views are
With 17,344 views, a materialized view per view was never an option. It is also more expensive than it looks: StarRocks keeps per-partition refresh state for every materialized view in frontend memory, so the cost grows with materialized views times base-table partitions. Some of our base tables have more than 100,000 partitions.
So we ranked by measured cost. The StarRocks frontend audit log, which we ship to Datadog, records CPU time, scanned bytes and the relations each query touched. We grouped a week of production MCP queries by view and ranked datasets by CPU share, and separately by time spent past five seconds weighted by how often each view was queried. Two datasets we had planned to cover fell off the list at 0.1% and 0.04% of CPU.
Then we found the smallest set of rollups that the hot views share. Most Explorer views for a dataset differ only in the entity and the geography they group by, so one rollup at entity by state by day can serve many of them. One point-of-sale materialized view of 71.7 million rows now serves 20 views that used to scan 7 billion rows each time. In total, 28 materialized views serve 928 views across six datasets.
| Dataset | Materialized views | What each one stores |
|---|---|---|
| Building permits | 10 | Company or location by geography by day |
| Clickstream | 6 | Entity by state by day |
| Card transactions (dataset A) | 6 | Entity by state by day |
| Card transactions (dataset B) | 3 | Entity by state by day |
| Foot traffic | 2 | Entity by state by day, and nationally by day |
| Point of sale | 1 | Entity by state by day, for 20 views |
The gain is not always from fewer rows. Our clickstream company rollup holds 746 million rows against 1.76 billion in the base table, only 2.4 times smaller. Its top production queries still went from 1,600 to 1,860 CPU-seconds down to 0.3 to 0.45 seconds, because the materialized view is distributed by company ID. A single-company query now reads 1.3 million rows instead of 3.1 billion. Choosing the distribution key mattered as much as choosing the grain.
2. Repoint the views, don’t route the queries
We first tried two standard approaches. StarRocks can rewrite a query to use a materialized view automatically, but only when the view’s query is a simple select-project-join-group-by. Our views use common table expressions and joins to aggregates, and the optimizer trace showed it would not rewrite them. We also tried routing in our application, mapping each view to its materialized view in the MCP server. That missed the web platform, which stores generated SQL for saved analyses and replays it later.
What works for every caller is to change the view itself. Each Explorer view now selects from its materialized view, with the same name, columns and results. The MCP server, the web platform, saved queries and internal tools all pick it up with no code change. Because the views reach the materialized views directly, we turned automatic rewrite off on them. A rewrite candidate that never matches still added about 110 ms of planning to every query on the base table.
The rollups have to give exactly the same answers as the views they replace. They keep every column the views filter, join or group on, and store integer sums, so aggregates above them stay exact. Before the production release, we compared all 928 view definitions and row counts against the development clusters, and all 928 matched.
Before production, we also benchmarked each dataset on our development cluster: the same full-history query against the materialized view and against the base tables, 30 pairs in all, every pair with identical results.
3. Refresh in the pipeline, and make it all or nothing
A materialized view is only useful if it is never wrong or half-built. dbt defines each one with manual refresh. Our pipeline command refreshes them in a fixed order after every Iceberg commit: it refreshes external table metadata on each cluster, refreshes the materialized views and waits for them, fails the run if any refresh failed, and only then publishes the new date ranges that the platform shows. A user can never see a date range that the rollup doesn’t hold yet.
We tested what a reader sees during a refresh by polling every 50 ms while it ran. With the default settings, StarRocks commits one partition per task run, and 340 of 370 reads saw a mix of old and new months. Setting the strict partition-refresh strategy with no partition limit makes the whole refresh one commit, and the same test showed zero mixed reads in full, partial and forced refreshes. When the base table has not changed, StarRocks compares per-partition statistics and skips the refresh in under a second.
ALTER MATERIALIZED VIEW explorer_mv.entity_state_day SET (
"partition_refresh_strategy" = "strict",
"partition_refresh_number" = "-1"
);
We also learned not to hold a connection open for a long refresh. The synchronous refresh mode waits on the client connection, and our load balancer silently dropped idle connections after 200 to 400 seconds, so the client hung while the refresh succeeded. We submit refreshes asynchronously and poll the StarRocks task-run table by query ID instead.
Monthly full rebuilds, for example after an ontology change, are the hardest case. Rebuilding in place would show readers empty or partial rollups for the whole fill. Instead, the pipeline clones each materialized view as a shadow copy, fills it in chunks of 12 partitions, and swaps every materialized view on a cluster at once. If any fill fails, nothing is swapped on that cluster.
4. Give refreshes their own compute
Refreshes are large INSERT statements, and they do not pass through StarRocks’ query queue. Early on, two concurrent fills saturated a development cluster and slowed client queries. On CelerData, production and development, we now run every refresh on a dedicated warehouse, mv_refresh, of three Graviton r7g.4xlarge nodes, cap fills at two per cluster, and limit them with a resource group.
The warehouse layout mattered more than its size. We first deployed it across three Availability Zones, which creates one compute group per zone, and StarRocks runs each query on one group. Every refresh therefore ran on a single node. Moving the warehouse to one Availability Zone put all three nodes behind each refresh, and our largest forced fill went from 3,866 seconds to 1,155 seconds, 3.35 times faster, with all three nodes near 80% CPU.
The refresh cost stays small. In production, the first fill of all 28 materialized views took 90 minutes in 142 refresh runs on October 19. In the week after release, the 73 incremental refreshes averaged 38 seconds, none failed, and none ran on the warehouse that serves clients.
Results
We compare the week before the October 20 release with the second full week after it, October 28 to November 4, 2026, and skip the first week, which had compute-node errors and queue spikes. As a control, we track Explorer views that did not change. These numbers reflect our data and our traffic, so your results might vary.
On the views that moved to materialized views, the slowest 1% of queries got about five times faster for every caller, and the median fell from between 0.75 and 0.99 seconds to between 0.04 and 0.22 seconds. The control views barely moved, so the gain comes from the materialized views rather than from lighter traffic. On release night, a sample point-of-sale query went from 42 seconds to 0.5 seconds, and a card-transaction query from 27 seconds to 0.3 seconds.
The MCP server’s tool calls, which include model, metering and authentication time as well as the query, show the same change at the tail.
| MCP measure | Before | After |
|---|---|---|
| Data tool calls over 20 seconds | 8.35% | 0.91% |
| Data tool p99 | 40.8 s | 19.4 s |
| All tool calls over 20 seconds | 6.19% | 2.84% |
| All tool calls p99 | 49.8 s | 29.0 s |
| Data fetch p99 | 16.2 s | 8.56 s |
MCP call volume rose about 2.5 times between the two windows, and the median tool call got slower, which we attribute to the change in workload mix. We report the tail, where the materialized views remove the long queries.
The cluster also does less work for the same answers. In the week after release, a query on a materialized-view-backed view averaged 6.5 CPU-seconds and a median of 43 ms, against 24.6 CPU-seconds and 399 ms for the views we did not change. CPU used by the web platform’s resource groups fell from 0.71 to 0.27 cores on average while it ran more queries. Across the cluster, total queries rose 17%, yet the average query queue fell from 0.85 to 0.16 waiting queries and the peak from 20 to 5.
Refreshing the materialized views has a cost: in the after window, refreshes used about 17% of the query CPU recorded in the StarRocks audit log. That work runs once per data update on the separate refresh warehouse, instead of on every client query on the warehouse that serves them.
Lessons learned
- Rank by measured cost, then by popularity. The query audit log told us where CPU went, and a small share of query families held most of it. Measuring first kept the program to 28 materialized views instead of hundreds, and it dropped two datasets we would otherwise have built for.
- Prove the results are identical, every time. Before any view moved, we compared results from the materialized view against the base tables in 30 cases, and every one matched. That habit also caught a real optimizer bug: on StarRocks 4.0.11, bucket-aware execution on lake tables split one brand into four rows that summed to the correct total. The fix shipped in 4.0.14, and we now require that version or later.
- Test with the production session settings. One refresh failure surfaced only in production, because our test harness set a longer planner timeout than the pipeline client did. Under fill load, planning the catalog reads took 35 to 92 seconds against a 30-second default.
- Check that infrastructure changes did what they said. A warehouse grant script reported success while it ran before the warehouse existed, and granted nothing. We now check grants after every apply.
- Keep refresh compute apart and fully usable. Refreshes bypass the query queue, so they need their own warehouse and a concurrency cap. Its layout matters too: a warehouse spread across Availability Zones ran each refresh on one node.
What’s next
We are moving more datasets onto a unified long-format panel layout on Iceberg, where one rollup per panel can serve every view of that dataset. The same ranking will decide what gets precomputed next. We also want to add a scheduled scaling policy to the refresh warehouse, so it grows during the monthly rebuild window and shrinks after.
Conclusion
We kept our data lake as Iceberg on S3 and added one precomputed layer where measurement showed the cost was: 28 StarRocks materialized views behind 928 existing views. Every client, including AI agents on our MCP server, now gets the same answers faster with no change to how they query, and the cluster does far less repeated work. The refresh path runs inside the pipeline on its own compute, and commits all at once.
If you serve many query shapes from a lake on S3, start the same way: rank query families by measured CPU, find the few rollups they share, repoint the views rather than the callers, and give refreshes their own compute.
- starrocks
- materialized-views
- iceberg
- mcp
- performance
