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.

Dimension

Defined Relationships

AI-Generated SQL

Join definition

Explicit, pre-defined in Topic

Inferred by generative AI at query time

Join types supported

Inner join only for dashboards

Inner, left, right, full outer, union, subquery

Relationship graph constraint

Must be directed acyclic graph (DAG): no circular relationship

No structural constraint

Multi-fact handling

Requires conformed dimension keys

AI bridges via instructions

Guidance mechanism

Relationship keys + dataset metadata

Custom instructions + synonyms + descriptions

Flexibility

Schema-bound

Intent-bound

Best for

Governed dashboards, regulated environments

Exploratory analytics, ad-hoc questions, power users

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.

Layer

Source

Definition

Purpose

Example

1

Dataset Output; Dataset

Dataset-Level Instructions

Define the grain, purpose, keys, and business rules of each individual dataset

“Each row in SALES_FACT represents one line item on one order.”

2

Topic

Topic-Level Instructions

Define cross-dataset logic, disambiguation rules, and default join behaviors

“When the user says ‘sales’, use SALES_FACT, not RETURNS_FACT.”

3

Dataset Output; Field

Synonyms

Map business vocabulary to technical field names

“Revenue”, “Top line”, “Income” → total_amount

4

Dataset Output; Field

Field Descriptions

Explain column semantics, units, nullability, and valid ranges

“order_date: UTC date customer placed order, never null, format YYYY-MM-DD”

6

Dataset Transform

Column Exclusions

Remove noise such as internal keys, ETL timestamps, and deprecated fields

Hide etl_load_timestamp, surrogate_key, is_deleted_flag

7

Dataset Transform

Calculated Fields & Named Filters

Pre-build common business metrics and segment definitions the AI can reference directly

Field: “Net Revenue” = sales.amount – returns.refund_amount

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

User Expression

Technical Field / Filter

Notes

Revenue, Sales, Income, Top Line

SALES_FACT.total_amount

Map all four terms to same column

Churn, Attrition, Lost Customers

CUSTOMER_DIM.status = ‘churned’

Synonym maps to filtered condition, not just column

AOV, Average Order Value, Avg Basket

SUM(total_amount)/COUNT(DISTINCT order_id)

Document the formula in field description too

Category, Department, Aisle

PRODUCT_DIM.category_name

Retail domain jargon mapping

Store, Location, Branch, Shop

STORE_DIM.store_name

Multiple business terms for same concept

YTD, Year to Date

DATE_DIM filter: year = current year AND date

Time-intelligence synonym

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