Data modeling patterns for Amazon Quick Sight multi

In Part 1 of this series, we introduced Amazon Quick Sight Multi-Dataset Relationships and covered the foundational concepts of dimensional modeling, best practices for designing clean data models, and a decision framework for when to use runtime joins versus pre-joined datasets. If you haven’t read Part 1 yet, we recommend starting there.

In this post, we shift from concepts to patterns. For each schema, you’ll find a table structure, use cases, implementation steps, and sample SQL queries. We also cover workarounds for advanced scenarios that require extra modeling steps, and close with a summary of current limitations.

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.

The following seven scenarios are natively supported by Quick Sight Multi-Dataset Relationships. Each scenario maps to a common data modeling pattern, with concrete implementation guidance and sample SQL.

The most common and recommended pattern. A central fact dataset is related to multiple dimension datasets.

Total sales by customer segment and region:

A snowflake schema extends the star by normalizing dimension tables into chains. For example, a Customer dimension might link to a Geography table, which links to a Region table. Each table stays at its own grain.

Dimension tables are normalized into multi-level chains.

The multi-hop join (fact → customer → geography) increases query complexity slightly. Pre-join snowflake chains into a single flat dimension dataset unless the dimension is very large (>1M rows) and storage reduction justifies the added join hop.

Multiple fact tables share common (conformed) dimension tables. This supports cross-process analytics. For example, you can compare sales versus returns using shared product and customer dimensions.

Multiple fact tables share common conformed dimensions.

Conformed dimensions must use identical grain and keys across both fact tables. Querying across facts uses shared dimensions as the “bridge” for the join.

Which promotions drive the most returns?

A single dimension table (for example, Date) is referenced multiple times by the same fact table, each time in a different analytical role. In Quick Sight, create three separate datasets all based on the same underlying DATE_DIM source table.

A single date dimension serves multiple analytical roles.

The role mapping is defined in the following table.

Average ship lag by product category:

Two or more fact tables at different levels of detail (grain) share common dimension tables. Quick Sight Multi-Dataset runtime joins automatically aggregate the finer-grained fact up to the coarser grain before joining. This eliminates the need for manual pre-aggregation in extract, transform, and load (ETL) pipelines.

Daily sales and monthly forecasts share dimensions at different grain levels.

Actual versus forecast by store (monthly):

The preceding scenarios demonstrate how different schema patterns map to Multi-Dataset Relationships. The next two scenarios shift focus from data modeling structure to operational capabilities of the multi-dataset architecture: independent refresh schedules and runtime row-level security.

Because each dataset in a Multi-Dataset Topic is an independent entity, datasets can be refreshed on separate schedules tailored to their data volatility. High-velocity fact tables can refresh hourly, and slowly changing dimensions can refresh daily or weekly.

Multi-Dataset Relationships enforce row-level security (RLS) rules during runtime joins. Each dataset’s RLS policies are respected independently, so users see only the data they are authorized to access, even when queries span multiple datasets. This is a key advantage over composite datasets, which cannot enforce the parent dataset’s RLS.

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.

The following patterns are not natively supported but can be addressed with data modeling workarounds applied in the dataset preparation layer.

A circular relationship exists when two paths lead from the same fact to the same dimension (for example, Order → Branch AND Order → Sales Staff → Branch). Circular relationships are not supported. The solution is to remove one leg and denormalize the redundant path.

A triangular join cycle that must be broken to avoid ambiguity.

Order → Branch (direct FK) AND Order → Sales Staff → Branch (indirect path via Staff). This creates a triangular loop. If your model creates a cycle (A → B → C → A), you must break it before defining relationships in the Topic. Quick Sight will reject relationship definitions that form a loop.

The new table structure after the workaround is applied.

An employee table with a MANAGER_ID column referencing back to the same table (org chart). Self-joins are not supported across datasets.

Create a copy of the employee table as a “Manager” dimension dataset. Good for single-level hierarchy (employee → direct manager only).

Pre-compute a flattened hierarchy view with explicit level columns (Level1_VP, Level2_Director, Level3_Manager, Employee). Import as a single Quick Sight dataset.

Geographic hierarchy where some countries have states and cities, but others go directly from country to city. Missing levels cause gaps in drill-downs.

In the ETL/dataset layer, pad missing levels by repeating the parent value so each path has uniform depth:

When an entity belongs to two or more independent hierarchies simultaneously (for example, a Product has both a Brand hierarchy and a Category hierarchy), model each as a separate dimension connected to the fact table independently.

Brand and category exist as independent dimension hierarchies.

A product belongs to both a Brand hierarchy (Nike Inc. > Nike > Air Max) and a Category hierarchy (Footwear > Running > Road). These hierarchies are independent. Combining them into one dimension creates a false dependency.

Cross-analysis: Brand x Category matrix

Although the preceding workarounds address many advanced modeling needs, there are constraints in the current release that cannot be resolved through data modeling alone.

Multi-dataset runtime joins move Quick Sight from “flatten everything first” to “model once, combine at query time.” Keep each table as a dataset, declare relationships in a Topic, and let Quick Sight assemble inner joins on demand across visuals, calculations, filters, and Chat.

Star and snowflake schemas, single-conformed-dimension multi-fact analysis, and role-playing dimensions are supported directly. Loops, many-to-many, deep hierarchies, and outer-join retention are reachable through bridge tables, flattening, conforming to a spine, and pre-built joins. A small number of cases remain out of scope for now: true cycles and full outer-join semantics.

Start by modeling one fact and its dimensions, enrich the Topic metadata, validate with Chat, and grow the model outward. The same set of related datasets will then answer far more questions than any single flattened dataset could.