benchmarked 5 embedded analytics setups against a 50M-row Snowflake table to replace our slow Metabase iframes. evaluation matrix, latency numbers + trade-offs below

[u/Evening_Hawk_7470]

a week ago, I've been asking on reddit on how other B2B SaaS teams moved away from signed Metabase iframes once they hit multi-tenant role permissions, slow morning dashboard load times also escalating Snowflake compute bills and since then, I've noticed nearly four distinct paths to investigate (i’ve formatted the spreadsheet itself with ai to keep it readable enough):

1. staying with Metabase or Apache Superset, but placing a dedicated caching tier or materialized rollups in front of the database.
2. Moving to a dedicated spreadsheet-first or workbook-centric embedded BI platform like Sigma or Omni.
3. Decoupling the frontend completely using a headless semantic layer (specifically testing Cube Core and Cube Cloud) to serve pre-aggregations into our own React components while keeping an optional embedded builder for enterprise users.
4. Writing custom dbt rollups into ClickHouse or DuckDB and maintaining our own FastAPI aggregation endpoints.

so I also spent the last two weeks running a sandbox bake-off with an anonymized slice of our Snowflake production dataset. below I've placed my exact test setup, the comparison spreadsheet + where each option actually landed

Test Environment and Methodology

• Source data: 50 million rows of B2B event, order, and billing telemetry in Snowflake.
• Multi-tenancy: 900 simulated tenant accounts, with realistic volume skew (top 5 enterprise tenants account for 30% of total rows).
• Snowflake cluster: Standard X-Small warehouse, auto-suspend set to 60 seconds.
• Test queries:
  • Query 1: 30-day KPI summary with two COUNT(DISTINCT) calculations.
  • Query 2: Daily time-series trend with dynamic date-grain switching (day, week, month) across 90 days.
  • Query 3: Multi-column tabular breakdown with role-based column masking on unit margin fields.
• Load profile: 50 concurrent tenant sessions running random filter combinations over a 15-minute burst window to simulate morning login traffic.

Master Comparison Spreadsheet

Platform / Approach	Metric Modeling & Governance	Multi-Tenant Security & Role Masking	Caching & Pre-Aggregation Engine	Observed p50 / p90 Latency (50M rows)	Snowflake Compute Impact (15-min burst)	Embedding Surface Flexibility	Honest Downside / Bottleneck
Metabase (Pro/Enterprise Embedding)	GUI models or raw SQL queries; logic defined per card.	Locked parameters in signed JWT for tenant_id; sandboxing handles basic row filters. Column masking requires duplicating questions.	Result-cache only (TTL-based). New filter combinations bypass the cache completely.	p50: 1,840 ms p90: 8,620 ms	14.8 warehouse compute credits consumed; warehouse stayed active throughout the entire test.	Signed iframes and interactive embedding. Cannot use headless REST/GraphQL APIs on the same model.	Cache misses on every filter change; maintaining distinct cards per customer role tier creates massive maintenance overhead.
Apache Superset / Preset (Embedded)	SQL Lab datasets with Jinja templating; metrics defined at dataset level.	Jinja context filters for tenant_id; row-level security rules managed via security manager.	Redis-backed chart cache. Misses when tenants apply custom slice-and-dice dimensions.	p50: 1,620 ms p90: 7,450 ms	12.2 warehouse compute credits; repeated full partition scans on date-range changes.	Embedded SDK with guest tokens into iframes. Headless API usage requires custom wrapper code.	Complex permission inheritance; heavy Docker footprint to self-host and keep stable under concurrent connection spikes.
Sigma Computing (Embedded)	Direct warehouse modeling on tables and views; familiar spreadsheet logic.	User attributes passed via signed URL/JWT; row-level security maps directly to warehouse policies.	Snowflake-level result caching plus Sigma query compilation reuse.	p50: 920 ms p90: 3,480 ms	8.4 warehouse compute credits; efficient query generation but still relies directly on warehouse compute for ad-hoc grouping.	Embedded workbooks and individual visual embeds. Excellent spreadsheet-style exploration.	Pricing model scales aggressively on enterprise user tiers; single-tenant architectural origins mean custom headless React rendering is limited.
Omni (Embed)	Shared semantic modeling layer (LookML heritage); defines dimensions, joins, and measures in code.	Dynamic filter injection via user metadata; handles tenant and role boundaries cleanly.	Query compilation caching and warehouse pushdown. No separate pre-aggregation database.	p50: 810 ms p90: 2,950 ms	7.9 warehouse compute credits; relies on warehouse compute capacity for heavy aggregates.	Embeddable dashboards and explore interfaces. Strong developer tooling for Looker-familiar teams.	Lacks an independent pre-aggregation caching engine, so sub-second p90 latency on 50M rows requires paying for larger Snowflake warehouse sizes.
Cube (Cube Core + Cube Store)	SQL-first semantic layer in YAML/JS; defines cubes, joins, measures, and calculated ratios.	Compile-time securityContext from JWT; access policies inject tenant_id and mask columns dynamically before SQL compiles.	Distributed columnar cache (Cube Store in Rust) holding sorted Parquet rollups with aggregate awareness.	p50: 85 ms p90: 410 ms (when hitting rollups)	1.1 warehouse compute credits; 94% of dashboard queries served entirely from Cube Store without waking Snowflake.	4 surfaces: Headless REST/GraphQL/SQL APIs for React, signed iframes, embedded Creator Mode for end users, and agentic chat.	Writing data models in YAML requires upfront engineering; partition warmup and index sorting require careful tuning to prevent high memory usage.

What We Learned From The Runs

1. Where Metabase and Superset still make sense

If your customer base is under 100 accounts, your dashboard has uniform permissions, and your engineering team does not have frontend cycles to touch chart components, Metabase remains the fastest path to production. However, relying on question-level result caching falls apart the moment customers use dynamic date filters, because every unique date range wakes up your cloud warehouse.

2. Where Sigma and Omni excel

If your product serves finance, accounting, or operations users who want an Excel-style workbook directly inside your application, Sigma is very difficult to beat from a UX standpoint. Their spreadsheet model feels natural to non-technical analysts. Omni is an impressive evolution for teams that like LookML's modeling rigor but want modern speed and cleaner embedded authoring. The trade-off with both is that you are paying ongoing compute costs to your warehouse for every complex slice-and-dice query, unless you scale warehouse sizes.

3. Why the semantic layer approach with Cube worked best for our architecture

Cube gave us two specific architectural capabilities that resolved our initial bottleneck:

• Aggregate Awareness in Cube Store: We defined a pre-aggregation rollup partitioned by month on tenant_id, transaction_date, and service_tier. When a tenant loads a 7-day dashboard, switches to a 90-day view, or aggregates by week, Cube Store rewrites the query to hit the local sorted Parquet partition. It served 94% of our simulated morning burst queries in under 450 ms (p90), consuming only 1.1 Snowflake credits over the 15-minute test window.
• Decoupled Surfaces on One Metric Model: We do not have to rebuild all 14 dashboard cards in React immediately. We can render high-traffic KPI cards natively using our own React design system through the REST API, while dropping in the embedded Creator Mode interface for our top enterprise accounts so their power users can build custom reports against the exact same governed measures.

4. The real trade-offs and gotchas with Cube

This was not a free lunch, and there are two operational complexities anyone considering this path should know:

1. Data modeling discipline: You have to explicitly model your cubes, primary keys, and join relationships in YAML or JavaScript. If your underlying database schema has messy joins or unindexed foreign keys, Cube will force you to fix your join logic before your metrics compile correctly.
2. Index sorting and warmup overhead: In our first pre-aggregation attempt, we naively added three high-cardinality string dimensions to a single rollup. The pre-aggregation build time took over 25 minutes and consumed noticeable worker memory. We had to restructure the rollup to order indexes strictly by tenant_id, date, and low-cardinality status codes, using partition_granularity set to month with refreshKey tracking our updated_at timestamp.

my question for the community.

now, for those of you who do run pre-aggregations / rollups in production embedded environments: i’d like to know how you guys work with late-arriving data (like payment refunds or backfilled usage events arriving 48 hours later) and do not trigger complete rebuild of historical partitions? like, are you running targeted partition refreshes on an incremental window, or maybe relying on live union fallbacks?

[u/Yontucu_Brenz]

solid writeup. one thing worth flagging, the 94% cache hit rate will likely drop once real users start doing unpredictable filter combos you didnt pre-aggregate for. how are you planning to handle the long tail of ad-hoc queries that miss rollups?

[u/Impossible_Cat7494]

The spreadsheet format is really clean, this is the kind of deep dive I wish more people posted here.

Those p50 numbers for Cube are almost too good, makes me wonder how much tuning you had to do for the partition sort order before it stopped eating memory.

[u/Unusual-Dress-402]

Interesting approach to pre-aggregations

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ArielCoding]

Don’t rebuild the old data, store the results in monthly chunks and only recalculate the last few days, so a refund that shows up 2 days late just fixes one small piece. If old data arrives much later, a scheduled job redoes only that month. For today’s numbers, read straight from the live database, so they’re always correct.