The modern BI analyst typically begins their day in a state of fragmented chaos. Sales transactions live in one table, customer demographics in another, and product attributes in a third. To answer a single business question, the standard operating procedure has long been the creation of a massive, denormalized dataset. This process requires analysts to flatten multiple tables into one giant spreadsheet-like structure before a single visualization can be built. It is a rigid, labor-intensive workflow where a simple change in reporting requirements often means scrapping the entire dataset and starting the join process from scratch.
The Architecture of Runtime Joins
Amazon QuickSight is fundamentally changing this workflow with the introduction of Multi-Dataset Relationships. Instead of forcing analysts to flatten data upfront, this feature allows for the definition of logical relationships between datasets that are only executed at the moment of query. This mechanism, known as a runtime join, ensures that tables remain independent until the exact second a user creates a visual, applies a filter, or asks a question via natural language.
This system operates on a strict two-layer modeling structure. The first is the Dataset Layer, which serves as the physical foundation. Here, each table is maintained as an independent QuickSight dataset, preserving its native level of detail or grain. The second is the Topic Layer, a semantic layer that acts as a logical map. The Topic Layer does not store data; instead, it defines how the underlying datasets relate to one another. When a query is triggered, the QuickSight engine references the Topic Layer to determine which tables are necessary and constructs the join on the fly.
In its current release, these relationships utilize inner join semantics. This means that for a record to appear in the final output, a matching key must exist in both related datasets. To optimize this structure, AWS recommends a star schema, where a central fact table is surrounded by radial dimension tables. For more complex organizational needs, analysts can implement snowflake or galaxy schema patterns to manage join complexity without sacrificing the benefits of the runtime approach.
From Physical Flattening to Semantic Intelligence
The shift from pre-joining to runtime joins solves a critical problem in data integrity: the duplication of measures. In a denormalized dataset, when a high-level metric is joined with a lower-grain table, the metric is often repeated across multiple rows. This leads to the common and costly error of double-counting totals unless the analyst writes complex calculated fields to handle the aggregation. By keeping datasets separate and joining them at runtime, QuickSight ensures that measures are calculated at their native grain, eliminating redundancy and optimizing computing resources.
This architectural change provides a significant boost to the intelligence of Amazon Q, the natural language interface for QuickSight. The accuracy of an AI's response is only as good as the metadata it can access. Because the Topic Layer provides a clean, semantic map of business terms and their corresponding fields, Amazon Q can navigate the data more effectively. When the underlying model follows a clear star schema with well-defined join keys, the AI can identify the most efficient query path, resulting in faster and more accurate answers to complex business questions.
For teams deciding between this new approach and traditional pre-joining, AWS suggests a decision framework based on volatility and performance. Runtime relationships are the superior choice for environments where reporting scenarios evolve rapidly and operational overhead must be minimized. However, for massive, static aggregations where extreme query performance is the only priority, pre-joined datasets may still hold an advantage.
To assist with advanced implementations, AWS has released the Data Modeling Patterns for Amazon QuickSight Multi-Dataset Relationships guide. This resource provides specific SQL examples and workarounds for complex scenarios, including role-playing dimensions, multi-fact models with varying grains, circular joins, and recursive or ragged hierarchies. Detailed documentation and implementation tools are available at https://aws.amazon.com/quicksight/.
The move toward runtime joins signals a broader transition in BI from static data preparation toward dynamic, semantic-driven exploration.
