Multi-dataset Topic best practices for Amazon Quick Chat

extract

Artificial Intelligence Multi-dataset Topic best practices for Amazon Quick Chat 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: - A clear understanding of how Chat-driven SQL generation differs from defined-relationship Topics. - A layered framework, the Semantic Guidance Stack, for structuring all the metadata that guides the AI. - Eight concrete best practices, each with examples, anti-patterns, and sample instructions. - Techniques for handling complex patterns: outer joins, many-to-many, recursive hierarchies, role-playing dimensions, and cross-grain comparisons. - A decision framework for choosing between defined relationships, semantic-only guidance, and hybrid approaches. - A complete end-to-end walkthrough using a retail analytics Topic with five datasets. 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 semantic guidance stack 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. What to include - Table purpose and grain: State plainly what the table represents and what one row means. This prevents the AI from double-counting or aggregating at the wrong level. - Primary key: Identify the column(s) that uniquely identify each row. - Foreign key hints: Tell the AI which column in this dataset matches which column in related datasets. Use natural-language phrasing: “This dataset joins to PRODUCT_DIM on product_id.” - Business rules: Express derived calculations in plain English. “Revenue equals quantity multiplied by unit_price minus discount_amount.” This reduces the chance the AI invents an incorrect formula. - Known edge cases: Flag nullable columns, sentinel values, or special codes. “order_status = ‘VOID’ records should be excluded from revenue calculations.” - Aggregation rules: Specify the correct aggregation function for each measure. “Always SUM revenue. Do not use AVERAGE or COUNT.” Good vs. bad instructions: examples Consider the following contrast for a SALES_FACT dataset: Bad Instruction (too generic): "This is the sales data table. It contains sales information." Good Instruction (precise and complete): "SALES_FACT contains one row per order line item. Primary key: order_line_id (integer, never null). Grain: one line item = one product on one order. Key columns: - order_id: links to ORDER_HEADER_DIM.order_id - product_id: links to PRODUCT_DIM.product_id - customer_id: links to CUSTOMER_DIM.customer_id - order_date_key: links to DATE_DIM.date_key (integer, YYYYMMDD format) Revenue = quantity * unit_price - discount_amount. Always SUM revenue. Exclude rows where order_status = 'VOID' from all revenue calculations. The table is refreshed nightly at 02:00 UTC." Anti-patterns to avoid - Overly generic instructions: “This table has sales data.” gives the AI no actionable context. - Overly prescriptive SQL fragments: Embedding raw SQL snippets in instructions (for example, “always add WHERE is_deleted = 0”) can conflict with the AI’s query construction. Use business-language rules instead, and apply permanent filters at the dataset level in Quick Sight’s dataset transform editor. - Contradictory rules: If you state that revenue should be SUM at the dataset level and then define an AVERAGE aggregation at the field level, the AI will receive conflicting signals. Be consistent across all layers. 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. What to include in topic-level instructions - Conceptual relationships: Describe how datasets connect even when you have not defined formal relationship keys. “SALES_FACT and RETURNS_FACT both link to CUSTOMER_DIM on customer_id and to PRODUCT_DIM on product_id.” - Disambiguation rules: When the same business term could map to multiple datasets or fields, tell the AI which one to prefer. “When the user asks about ‘sales’, use SALES_FACT. When the user asks about ‘returns’ or ‘refunds’, use RETURNS_FACT. For ‘net sales’, join both.” - Default join behavior: Specify the join direction that preserves intended semantics. “Prefer LEFT JOIN from fact tables to dimension tables so that facts without matching dimension records are not silently dropped.” - Multi-fact resolution: Explain how to combine multiple fact tables. “To compare actuals versus forecast, join DAILY_SALES to MONTHLY_FORECAST by rolling up DAILY_SALES to the month level first, then join on month_key and store_id.” - Spanning business definitions: “Net revenue = SALES_FACT.total_amount minus RETURNS_FACT.refund_amount. Always join on order_id to avoid double-counting.” - Hierarchy navigation: “Product hierarchy: PRODUCT_DIM.product_id → PRODUCT_DIM.subcategory_id → PRODUCT_DIM.category_id. Roll up in this order for drill-down analysis.” Example: Full topic-level instruction block (retail analytics) The following example shows a production-ready topic-level instruction block for a retail analytics Topic containing five datasets: Topic: Retail Analytics Datasets in this Topic: - SALES_FACT: daily order line items (grain: one line item per order) - RETURNS_FACT: returned order line items (grain: one return per order line) - CUSTOMER_DIM: customer master (grain: one row per customer) - PRODUCT_DIM: product catalog (grain: one row per product SKU) - DATE_DIM: date attributes (grain: one row per calendar day) Disambiguation rules: - "sales", "revenue", "orders" -> SALES_FACT - "returns", "refunds", "credits" -> RETURNS_FACT - "net sales", "net revenue" -> join SALES_FACT LEFT JOIN RETURNS_FACT on order_line_id - "customer", "buyer", "shopper" -> CUSTOMER_DIM - "product", "item", "SKU", "category" -> PRODUCT_DIM Default join directions: - SALES_FACT LEFT JOIN CUSTOMER_DIM on customer_id - SALES_FACT LEFT JOIN PRODUCT_DIM on product_id - SALES_FACT LEFT JOIN DATE_DIM on order_date_key = date_key Cross-dataset definitions: - Net Revenue = SUM(SALES_FACT.total_amount) - SUM(RETURNS_FACT.refund_amount) - Return Rate = COUNT(RETURNS_FACT.order_line_id) / COUNT(SALES_FACT.order_line_id) Grain alignment note: - SALES_FACT and RETURNS_FACT are daily grain. - For monthly aggregations, GROUP BY DATE_DIM.year_month_key. Row-Level Security note: - Each dataset enforces its own RLS. Do not attempt to circumvent RLS filters. Anti-pattern: Contradicting dataset-level instructions 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. Synonym coverage strategy Organize your synonyms into four vocabulary tiers: - Executive language (KPI names): The terms leadership uses in board decks. “Revenue”, “EBITDA”, “Market Share”, “NPS”. - Analyst language (metric definitions): The terms your BI team uses. “Gross Margin”, “AOV” (average order value), “CAC” (customer acquisition cost). - Domain jargon: Industry-specific terminology. For retail: “SKU”, “planogram”, “shrinkage”. For SaaS: “MRR”, “ARR”, “seats”. - Abbreviations and acronyms: “YTD”, “QTD”, “MTD”, “LTM”, “TTM”. Sample synonym mapping table Guidelines for synonym quantity 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. Writing effective field descriptions 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: - Definition: One precise sentence stating what the column contains. - Unit or format: USD, percentage (0–1 scale), YYYY-MM-DD, or integer. - Nullability: “Never null” vs. “Nullable; null indicates the customer has not placed an order.” - Valid range or enumerated values: “Values: PENDING, PROCESSING, SHIPPED, DELIVERED, CANCELLED, VOID.” - Aggregation behavior: “SUM for totals; do not AVERAGE this field.” Field description examples 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: Technique 1: Implicit join hints 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. Technique 2: Grain alignment instructions 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. Technique 3: Union instructions When two tables share the same schema and represent the same entity type from different sources, instruct the AI to union them. Technique 4: Subquery instructions 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. Technique 5: Conditional join instructions Some datasets are only relevant when the user asks about specific topics. Instruct the AI to include them conditionally. -- Topic instruction: "Include the PROMOTIONS dataset only when the user's question involves discounts, campaigns, promo codes, or promotional pricing. Join on order_id when PROMOTIONS is needed." 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: preserving unmatched records Outer joins are the most commonly needed feature that defined-relationship Topics lack. Many-to-many relationships: bridge tables Classic many-to-many relationships (students↔︎courses, orders↔︎promotions, employees↔︎projects) require a bridge table, which the AI can navigate when instructed. Recursive and self-referencing hierarchies Defined-relationship Topics cannot represent self-joins. Chat Topics have no such constraint. You instruct the AI to self-join. Role-playing dimensions 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 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. Circular relationships 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. Column exclusion strategy - Exclude surrogate keys: Integer surrogate keys (for example, customer_sk, product_sk) are used for joins but are meaningless as analytical values. Exclude them unless users would realistically filter on them. - Exclude ETL metadata: etl_load_timestamp, is_deleted, batch_id, source_system_code. These fields exist to support data engineering, not analytics. - Exclude deprecated fields: If you have both legacy_region_code and region_name, hide the deprecated one. Offering both invites the AI to pick the wrong one. - Exclude highly cardinal low-value fields: Free-text comments, internal notes, and UUID strings rarely contribute to meaningful analytics. Exclude them. 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. Building a question bank Before publishing a Topic, create a question bank of 15–25 questions per major dataset, organized from basic to complex: - Tier 1 – Single dataset, single metric: “What was total revenue last month?” Tests basic SUM aggregation and date filtering. - Tier 2 – Single dataset, multi-dimension: “Show me revenue by product category and region for Q3.” Tests GROUP BY with multiple dimensions. - Tier 3 – Cross-dataset, matching grain: “What is the return rate by product category?” Tests cross-dataset join and division calculation. - Tier 4 – Cross-dataset, mismatched grain: “Which stores are above their monthly sales target?” Tests grain alignment, rollup, and join logic. - Tier 5 – Complex patterns: “Show me all customers, including those with no orders, and their lifetime spend.” Tests LEFT JOIN and NULL-safe aggregation. What to validate - Join correctness: Did the AI choose the right join type (LEFT vs. INNER)? Did it join on the correct key? - Aggregation correctness: Did the AI aggregate at the correct grain? Are measures summed rather than averaged where specified? - Dataset selection: When terms are ambiguous, did the AI pick the intended dataset? - Filter logic: Are filters applying correctly? Is the date range what the user intended? - NULL handling: Are NULL values handled appropriately (COALESCE, IS NULL checks)? Common failure modes and fixes 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. Scenario: Retail analytics Topic Step 1: Dataset-level instructions (SALES_FACT) "SALES_FACT contains one row per order line item. Primary key: order_line_id. Grain: one line item = one product on one order. Revenue = quantity * unit_price - discount_amount. Always SUM revenue. Exclude rows where order_status = VOID. Joins: product_id -> PRODUCT_DIM, customer_id -> CUSTOMER_DIM, store_id -> STORE_DIM, order_date_key -> DATE_DIM.date_key." Step 2: Dataset-level instructions (RETURNS_FACT) "RETURNS_FACT: one row per returned order line. Primary key: return_id. order_line_id is a foreign key back to SALES_FACT. Use this dataset when the user asks about returns, refunds, or credits. refund_amount is always a negative number (credit to customer). LEFT JOIN from SALES_FACT to RETURNS_FACT to preserve unmatched sales." Step 3: Topic-level instructions Topic: Retail Analytics - Disambiguation and Cross-Dataset Rules Disambiguation: "sales", "revenue", "orders" -> use SALES_FACT "returns", "refunds" -> use RETURNS_FACT "net sales", "net revenue" -> join SALES_FACT LEFT JOIN RETURNS_FACT on order_line_id Cross-dataset metrics: Net Revenue = SUM(SALES_FACT.total_amount) + SUM(RETURNS_FACT.refund_amount) Return Rate = COUNT(RETURNS_FACT.return_id) / COUNT(SALES_FACT.order_line_id) Default joins: SALES_FACT LEFT JOIN CUSTOMER_DIM on customer_id SALES_FACT LEFT JOIN PRODUCT_DIM on product_id SALES_FACT LEFT JOIN DATE_DIM on order_date_key = date_key Date handling: YTD: current year up to and including today This quarter: DATE_TRUNC('quarter', today()) to today Step 4: Key synonyms Steps 5-7: Enrichment, exclusions, and validation - Enrich field descriptions: Add descriptions to the 15-20 most queried columns across all five datasets using the format: definition + unit + nullability + aggregation rule. - Tag semantic types: Tag all date fields as Date, monetary fields as Currency, percentage fields as Percent, and geographic fields with their respective geo types. - Exclude noise: Hide etl_load_ts, batch_id, is_deleted, source_system, and surrogate key columns not used in user-facing questions. Step 8: Sample questions and expected SQL 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 Instruction length and conciseness 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. Order of precedence 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. Performance considerations More datasets in a Topic means more context for the AI to process at query time. To keep response latency acceptable: - Keep Topics focused on a domain: retail analytics, HR analytics, financial reporting. Avoid creating one Topic for an entire enterprise data warehouse. - Split topics when datasets serve different user communities. A single all-data Topic is harder to tune and harder to govern than three focused Topics. - SPICE-backed datasets generally produce faster query responses than Direct Query sources for Chat workloads. Row-level security 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. When to split into multiple Topics Conclusion 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.