Multi

Note: The topics referenced throughout this document refer to the new Topics experience (not legacy Topics). For details on the differences, see Build a unified semantic layer across datasets with multi-dataset Topics in Amazon Quick.

Most real-world business questions span multiple tables. A retailer who wants to understand net revenue by product category must draw from a sales fact table, a returns fact table, and a product dimension. Each of these lives in a separate dataset. Until recently, bridging those datasets required a data engineer to pre-join them and deliver a single dataset to Amazon Quick Sight before any analyst could ask a question.

Amazon Quick Sight’s Multi-Dataset Topics change that equation by letting analytics teams bring multiple datasets into a single Topic in one of two ways. You can define explicit relationship keys (covered in the companion post, Data modeling best practices for Amazon Quick Sight multi-dataset relationships , or you can equip the generative AI engine with enough semantic context to write SQL itself. This post focuses on the second path: Chat-powered, AI-generated SQL.

When you configure a Topic for Chat, you do not need to define relationships in advance. Instead, you author a semantic layer that includes dataset-level custom instructions, topic-level instructions, field synonyms, and field descriptions. The AI uses that context to generate context-aware SQL at query time. This puts outer joins, unions, subqueries, self-joins, cross-grain comparisons, and conditional join logic all within reach, with no structural constraint on the relationship graph.

This post is for data architects, business intelligence (BI) engineers, and analytics engineers building or optimizing Quick Sight Topics for natural-language Chat-based exploration. You will come away with:

How Chat differs from defined relationships

Before diving into best practices, it helps to understand the fundamental architectural distinction between Quick Sight’s two multi-dataset modes.

When you define explicit relationships in a Topic, Quick Sight builds a logical join graph and executes inner joins at query time. The graph must be a directed acyclic graph (DAG), supports up to 12 datasets, and produces deterministic results because every join path is pre-specified. This suits governed reporting scenarios where you need to enforce exactly how tables combine.

When a user asks a question through Chat, Quick Sight’s generative AI reads either the defined relationships or your Topic’s semantic layer (instructions, descriptions, and synonyms) and generates SQL to answer that question. The AI determines which datasets to query, which columns to use, what join type is appropriate, and how to aggregate the result. There is no pre-wired join graph. The AI operates on intent, not structure.

Defined relationships are guardrails: they prevent incorrect joins from ever being attempted. Semantic metadata is guidance: it steers the AI toward correct, contextually appropriate SQL. Both have value. The right choice depends on your scenario. See the Decision framework section later in this post.

Defined relationships and semantic guidance are not mutually exclusive. A hybrid Topic can define relationships for the core fact-to-dimension joins while relying on custom instruction for exploratory patterns that fall outside the pre-defined graph. The Decision framework section explores this further.

For the best practices of defined relationship, please refer to Data modeling best practices for Amazon Quick Sight multi-dataset relationships and Data modeling patterns for Amazon Quick Sight multi-dataset relationships

The AI engine that powers Quick Chat draws on seven layers of metadata when generating SQL, collectively forming the semantic guidance stack. Understanding each layer is the foundation for writing effective metadata.

Each layer reduces the AI’s uncertainty about your data. The more precisely you populate each layer, the narrower the space of plausible SQL interpretations, and the more accurate the generated results. A sparsely described Topic with many datasets will produce unreliable results. This is not because the AI is incapable, but because the information it needs to make correct choices is absent.

Best practice 1: Write dataset-level instructions as a data dictionary

Dataset-level custom instructions are the AI’s first point of contact with each table and set the context for every question that touches that dataset.

Consider the following contrast for a SALES_FACT dataset:

Good Instruction (precise and complete):

Best practice 2: Write topic-level instructions for cross-dataset logic

Dataset-level instructions describe individual tables. Topic-level instructions tell the AI how tables relate to each other, which dataset takes precedence when terms are ambiguous, and how to handle cross-dataset computations. Together they give the AI a complete picture of your domain.

The following example shows a production-ready topic-level instruction block for a retail analytics Topic containing five datasets:

If your SALES_FACT dataset-level instruction says “revenue = quantity * unit_price – discount_amount” and your topic-level instruction says “net revenue = total_amount”, the AI will receive contradictory definitions. Always make sure dataset-level and topic-level instructions are consistent. Topic-level instructions should ADD cross-dataset context, not redefine single-dataset semantics.

Best practice 3: Design synonyms for how users actually talk

Synonyms bridge the gap between how your users express questions and what your technical schema calls things. A business analyst says “revenue”, but your database column is total_amount. A marketing manager says “churn”, but your schema flags it as customer_status = 'churned'. Without synonyms, the AI must guess the mapping, and guesses produce inconsistent results.

Organize your synonyms into four vocabulary tiers:

Aim for 3 to 7 synonyms per commonly queried column. Fewer than 3 leaves common vocabulary gaps. More than 10 risks introducing ambiguous terms that match too many fields. For internal or rarely queried technical fields, 0–1 synonyms is appropriate, or exclude the field entirely.

Best practice 4: Enrich field descriptions and semantic types

Field descriptions give the AI a precise understanding of each column’s meaning, unit, constraints, and intended use. While synonyms handle vocabulary and instructions handle logic, descriptions handle data semantics: what the value actually represents.

Write descriptions for an AI consumer, not a human reader. The AI benefits from structured, unambiguous information rather than relying on context. Each description should include:

Best practice 5: Guide join behavior without defining relationships

Chat-driven Topics let you specify join semantics entirely through natural-language instructions. You need no formal relationship keys and no constraint on join types. Five techniques are available:

State the join condition and join type as a plain-English rule in your dataset or topic-level instructions. The AI will follow this rule when generating SQL.

When two fact tables operate at different granularities, instruct the AI to roll up before joining. This is the most common source of inflated measures in multi-dataset analytics.

When two tables share the same schema and represent the same entity type from different sources, instruct the AI to union them.

For negation-pattern questions, such as “show me customers who have never ordered” or “products with no sales this quarter,” instruct the AI to use NOT EXISTS or LEFT JOIN / IS NULL patterns.

Some datasets are only relevant when the user asks about specific topics. Instruct the AI to include them conditionally.

Best practice 6: Handle complex patterns via semantic instructions

Chat-driven Topics unlock several analytical patterns that are unsupported or require complex workarounds with defined relationships. The following subsections show how to handle each pattern through semantic instructions alone.

Outer joins are the most commonly needed feature that defined-relationship Topics lack.

Classic many-to-many relationships (students↔︎courses, orders↔︎promotions, employees↔︎projects) require a bridge table, which the AI can navigate when instructed.

Defined-relationship Topics cannot represent self-joins. Chat Topics have no such constraint. You instruct the AI to self-join.

A role-playing dimension is a single dimension table that serves multiple contextual roles in a fact table (for example, a DATE_DIM used for order date, ship date, and delivery date simultaneously). In defined-relationship Topics, each role requires a separate join definition. In Chat Topics, the AI handles this via instructions.

Cross-grain comparisons arise when two fact tables measure the same concept at different granularities (daily actuals vs. monthly targets, weekly shipments vs. quarterly commitments). The AI needs explicit instruction on the rollup direction.

Defined-relationship Topics reject circular (non-DAG) relationship graphs. Chat Topics have no such constraint. The AI navigates cyclic patterns when the question and instructions are clear about which path to traverse. If ORDERS references CUSTOMERS, CUSTOMERS references ACCOUNTS, and ACCOUNTS references ORDERS, the AI can follow whichever path is most appropriate for the question at hand.

Best practice 7: Reduce noise and improve answer precision

A counterintuitive finding when tuning Chat-driven Topics: fewer visible fields produce more accurate answers. The AI processes every column in scope when formulating SQL. Technical columns, surrogate keys, ETL timestamps, and deprecated fields add noise, increasing the chance of irrelevant column selection or incorrect inference.

Best practice 8: Test, validate, and iterate your semantic model

A semantic model is never finished on the first draft. Iterative test-refine cycles move you from “mostly correct” to “reliably accurate.” In traditional BI development, you test SQL queries directly. Here, you test the AI’s ability to translate business intent into correct SQL, which requires a structured approach.

Before publishing a Topic, create a question bank of 15–25 questions per major dataset, organized from basic to complex:

Decision framework: Choosing your approach

Amazon Quick Sight offers three approaches to multi-dataset Topics: defined relationships, semantic-only Chat guidance, and a hybrid of both. The right choice depends on your use case, user profile, and governance requirements.

Putting it all together: End-to-end walkthrough

This section walks through configuring a Chat-ready Topic for a retail analytics scenario with five datasets.

Q1: “What was total net revenue by product category last quarter?”

Q2: “Show me all products including those with zero sales this month”

Q3: “Which customer segments have the highest return rates?”

Q4: “Which stores are below 80% of their monthly target?”

Current considerations and practical tips

Quick Sight custom instructions have character limits, but conciseness is a virtue regardless. Prefer bullet-point rule lists over prose paragraphs. Each rule should be a single, independently parseable statement. For example, “join orders to customers on customer_id using LEFT JOIN to preserve customers with no orders” is more effective than a two-paragraph essay covering the same point.

When topic-level and dataset-level instructions conflict, topic-level takes precedence. Use this predictably: dataset-level instructions should define single-dataset facts (grain, keys, aggregation rules), while topic-level instructions should define multi-dataset logic. Never use topic-level instructions to redefine single-dataset semantics already specified at the dataset level.

More datasets in a Topic means more context for the AI to process at query time. To keep response latency acceptable:

Row-level security (RLS) is enforced at the dataset level, regardless of how the AI joins or unions datasets. Even if the AI generates SQL combining five datasets, each dataset’s RLS filters apply before data enters the join. A regional manager restricted to their region’s data in SALES_FACT will never see other regions’ data, regardless of the Chat question asked.

Moving from explicit relationship graphs to semantic guidance is a fundamentally different approach to multi-dataset analytics: instead of defining every join path up front, you describe your data’s grain, vocabulary, business rules, and edge cases, and the AI translates user intent into correct SQL at query time.

This unlocks capabilities that pre-defined relationships cannot support: outer joins that preserve unmatched records, unions that combine parallel tables, self-joins that traverse recursive hierarchies, and cross-grain comparisons that require runtime rollups. The constraint is semantic, not structural: the more precisely you describe your data, the more reliably the AI delivers correct results.