Data modeling patterns for Amazon QuickSight multi-dataset relationships
Historically many BI teams flattened schemas into large denormalized tables so dashboards could GROUP BY and filter without joins. That made queries simpler, and ETL painfully heavy, refresh windows long, and maintenance brittle.
Amazon QuickSight’s Multi‑Dataset Relationships lets you model facts and dimensions separately and declare relationships in a Topic so QuickSight performs joins at query time. That shifts complexity away from endless denormalization and into modeling, refresh strategy, and governance.
Why this matters
- ETL design: keep single‑purpose datasets instead of generating and maintaining giant flattened extracts.
- Refresh cadence: refresh high‑velocity facts more often without reloading large, slow dimensions every time.
- Security & governance: row‑level security remains dataset‑attached and affects final results; model it deliberately.
“Multi-dataset runtime joins move Quick Sight from ‘flatten everything first’ to ‘model once, combine at query time.’”
Common schema patterns that map well to runtime joins
QuickSight supports several standard dimensional approaches when you relate datasets inside a Topic. These are the practical patterns teams will reach for:
- Star schema: one fact table with single‑hop joins to denormalized dimension datasets, simple and performant for aggregations.
- Snowflake schema: normalized dimensions with chained joins. AWS guidance suggests flattening the chain into a single dimension unless normalization materially reduces storage or maintenance cost (teams often treat ~1M rows as a rough heuristic to test, benchmark for your workload).
- Galaxy / constellation: multiple fact tables share conformed dimensions (e.g., PRODUCT_DIM used by SALES_FACT and RETURNS_FACT) so you can analyze across facts without duplicating business logic.
- Role‑playing dimensions: use the same physical dimension multiple ways (OrderDate, ShipDate) by creating aliases or separate dataset instances that map to the same table.
- Multi‑fact with different grain: datasets with different grains (hourly orders vs monthly forecasts) can be combined at query time, but behavior depends on how measures and aggregations are defined, so validate with your queries.
Row‑level security (move this to the top of gotchas)
“RLS is applied before the join, not after. This means users see the intersection of their permitted rows from each dataset, which is a stricter and more secure model than post-join filtering.”
That intersection model is secure by default, but it can surprise analysts. If Dataset A restricts users to Region X and Dataset B restricts the same user to Store Group Y with no overlap, the query returns nothing. Test RLS rules early and document which dataset enforces which filters so you can debug empty result sets.
Operational patterns: refresh cadence and SPICE
Separate datasets by purpose and refresh them on cadences that match their volatility. A practical starting point:
- High‑velocity transaction facts (orders, clicks): hourly
- Aggregated/summary facts (daily summaries): daily
- Dimensions (customer, product): weekly or on change
- Reference/lookups (regions, categories): monthly or ad‑hoc
Use incremental refresh for large facts where supported to reduce SPICE ingestion cost and shorten refresh windows. Keep a small set of denormalized SPICE extracts for dashboards that require lowest latency and highest concurrency.
What QuickSight does (and what to verify)
Two platform behaviors are particularly important to confirm in your environment. As of June 2024, QuickSight guidance includes explicit notes you should validate in your account and documentation before committing a design.
-
“Note: All Multi-Dataset relationships in the current release use inner join. Only rows with matching keys in both datasets appear in query results. Design your data model accordingly.”
Inner joins exclude unmatched rows. If your analysis needs to retain all rows from one side (e.g., products with zero sales), plan to precompute those cases in ETL or keep a denormalized extract.
-
Query mode and source support vary by release and feature. QuickSight supports SPICE (in‑memory) and Direct Query modes; examples of Direct Query sources cited in QuickSight announcements include Amazon Redshift, Amazon Athena, Amazon S3 Tables and Amazon Aurora PostgreSQL. Other sources such as Snowflake or Databricks may be supported in specific QuickSight releases or regions, confirm the current list in the QuickSight documentation for your account and region.
-
Composite keys and some advanced relationship setups may require JSON upload rather than the UI in some releases, verify the current UI behavior before you design multi‑column keys into your Topics.
When you see statements like these in QuickSight guidance, date‑stamp them in your design notes and revalidate periodically; QuickSight adds capabilities regularly.
Unsupported patterns and pragmatic workarounds
Not every relational shape is supported out of the box. If you encounter these limitations, you can often resolve them in the data layer.
- Circular / loop joins: QuickSight requires an acyclic relationship graph. Break cycles by denormalizing one leg into the fact or removing a relationship and rebuilding the dependency in ETL.
- Self‑relationships / recursive hierarchies: QuickSight won’t model a dataset relating to itself. Precompute a flattened hierarchy in your database (for example, with a recursive CTE exposed as a view) and expose that as a separate dataset. Note: recursive CTE syntax is engine‑specific (Redshift, Snowflake, etc.).
- Ragged hierarchies: pad or repeat parent values so every path has uniform depth, or pivot the hierarchy into level columns during ETL.
- Split/parallel hierarchies: model each hierarchy as its own dimension and relate each independently to the fact.
Practical modeling rules of thumb
- Create one dataset per fact and one per logical dimension. Declare relationships in a Topic using stable keys.
- Favor single‑hop joins from fact to dimension in star schemas for predictable performance.
- Denormalize when heavy GROUP BY activity dominates and you need fastest queries; normalize when storage or maintenance benefits outweigh the extra join cost, benchmark to decide.
- Use SPICE for high concurrency and low latency; use Direct Query when you need live, up‑to‑the‑second data (expect higher latency and dependency on source concurrency).
- For many‑to‑many relationships, prepare bridge tables in ETL and expose them as standalone datasets to relate to facts.
Small, concrete example: inner joins and RLS can drop rows
Imagine two datasets in a Topic:
- SALES_FACT, 100, 000 hourly rows for the last 30 days
- PRODUCT_DIM, 10, 000 products refreshed weekly
If SALES_FACT contains sales for 9, 000 product SKUs and PRODUCT_DIM contains 10, 000 SKUs, an inner join returns only sales for the 9, 000 matching SKUs. Any product in PRODUCT_DIM with zero sales simply won’t appear in a sales‑by‑product query unless you precompute a left‑join result in ETL or keep a denormalized extract.
Now add RLS: if PRODUCT_DIM has RLS restricting a user to Region A (filtering out some SKUs) and SALES_FACT has RLS restricting that same user to a particular channel, the final result is the intersection of both filters, possibly empty. That combination is secure but must be anticipated in dashboard design and testing.
Test plan: what to validate in your account
Before production rollout, run these concrete checks in your QuickSight environment and document results:
- Create two tiny datasets: A (5 keys), B (3 keys with 2 keys overlapping). Build a Topic joining A.key = B.key. Run a query and confirm row counts reflect an inner join (only matching keys returned).
- Apply distinct RLS policies to A and B for a test user and rerun the query; confirm the visible rows are the intersection of permitted rows from both datasets.
- Attempt to create a Topic mixing a SPICE dataset and a Direct Query dataset; confirm whether the UI permits it in your account and region.
- Try to create a self‑relationship (dataset to itself) and a circular relationship to see if the UI blocks them.
- Benchmark latency: compare the same dashboard built from (a) a pre‑joined SPICE extract and (b) runtime multi‑dataset joins. Record query latency and SPICE ingest time/cost.
Acceptance criteria examples: if pre‑joined SPICE latency is under your SLA (for example, < 2s for the dashboard) and can support target concurrency, keep the extract; otherwise prefer runtime joins for flexibility.
Example business questions this enables
- Total sales by customer segment and region, without producing a giant flattened table.
- Actual versus forecast by store, combine daily sales facts with monthly forecasts at query time.
- Which promotions drive the most returns, join RETURNS_FACT to PROMOTION_DIM and RETURNS_REASONS without duplicating dimensions.
- Average ship lag by product category, combine ORDERS_FACT with PRODUCT_DIM updated on its own cadence.
Practical next steps and checklist for leaders
- Document the datasets, keys, grains, and RLS rules you plan to include in each Topic.
- Run the validation test plan above in a sandbox QuickSight account and capture the results and query latencies.
- Decide which dashboards need denormalized SPICE extracts for latency/scale reasons; keep them small and focused.
- Schedule periodic revalidation of QuickSight behavior against the official docs (features and limits evolve rapidly).
Key takeaways, questions a curious leader will ask
- Can Amazon QuickSight combine normalized datasets at query time so I don’t have to denormalize everything?
Yes. Multi‑Dataset Relationships let you define dataset relationships in a Topic and QuickSight performs runtime joins so you can retain normalized schemas and combine at query time. Validate performance versus SPICE extracts for high‑value dashboards.
- Will I lose rows because of the join type?
Possibly. As of June 2024 QuickSight guidance notes runtime relationships use inner joins by default, so unmatched rows are excluded. If you need outer‑join semantics, precompute the result in ETL or keep a denormalized extract.
- How does row‑level security behave when multiple datasets are joined?
QuickSight guidance states RLS is applied before the join; users see the intersection of permitted rows across datasets. Test RLS combinations early to avoid surprising empty results.
- When should I pre‑join in ETL rather than rely on runtime joins?
Pre‑join when you need outer joins, when denormalization significantly improves aggregation performance for mission‑critical dashboards, or when SPICE latency under load is required. Otherwise, use runtime joins for flexibility and smaller ETL footprints, benchmark to decide.
- Can I mix SPICE and Direct Query datasets in the same Topic?
Behavior can vary by release and region. QuickSight supports SPICE and Direct Query modes; confirm whether a Topic in your account must choose one mode by testing and checking the QuickSight documentation for your region.
Named contributors cited in the original guidance
Ying Wang, “senior worldwide specialist solutions architect in the Generative AI organization at AWS. She brings 17 years of experience in data analytics and data science…”
Emily Zhu, “Senior Product Manager at Amazon Quick, responsible for the full structured data stack…”
Salim Khan, “Specialist Solutions Architect for Amazon Quick Sight. Salim has over 16 years of experience implementing enterprise business intelligence (BI) solutions…”
Neeraj Kumar, “Senior Worldwide Solutions Architect at AWS… With over two decades of experience in data and analytics…”
Srikanth Baheti, “Senior Manager for Amazon Quick Sight…”
Parting note
Multi‑Dataset Relationships move some effort out of ETL and into modeling. That tradeoff can reduce extract churn and storage, but it also requires rigorous testing of joins, RLS, and refresh patterns. Run the short validation plan above, document the behaviors you depend on (join semantics, RLS ordering, Direct Query sources), and keep a small set of denormalized SPICE extracts for the dashboards that demand them.