Amazon Quick Chat Multi‑Dataset Topic Best Practices: Document Grain, Keys, Joins, RLS, and Tests

Multi-dataset Topic best practices for Amazon Quick Chat

TL;DR for BI leaders: If you need deterministic, auditable reports, choose defined-relationship Topics. If you want flexible natural-language exploration, use semantic (Chat) Topics. Hybrid Topics mix both. The single highest-impact action: document each dataset’s grain, primary/foreign keys, and business rules before you publish a Topic.

Why this matters

Quick Chat can either rely on a pre-wired join graph or build SQL at query time from semantic metadata. With crisp metadata the AI will correctly build unions, pick the right join, apply date truncation, and use NULL-safe math. Without it, you trade convenience for surprising results, like wrong joins, double-counting, or ambiguous dataset selection.

Two modes and the trade-offs

  • Defined-relationship Topics: the guidance states the relationship graph must be a directed acyclic graph (DAG), supports up to 12 datasets, and executes inner joins at query time. These are best when determinism, auditability, and strict governance matter.
  • Semantic (Chat) Topics: the generative engine composes SQL at query time from authored metadata. According to the guidance, it can produce inner, left/right/full outer joins, UNIONs, subqueries and conditional join logic, and imposes no structural relationship-graph constraint. Use this mode for natural-language access and exploratory analytics.

Security and performance constraints that change your choice

Two operational facts in the guidance should shape your architecture decisions and test plans:

  • Row-level security (RLS): the guidance states that RLS is enforced at the dataset level regardless of how the AI composes joins or unions: “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.” Confirm enforcement semantics and pushdown behavior with your QuickSight admin or AWS account team, since implementation nuances matter for compliance audits.
  • Performance (SPICE vs Direct Query): the guidance recommends SPICE-backed datasets for faster Chat responses in interactive workloads and advises keeping Topics focused by domain. Treat this as recommended practice. Test SPICE refresh cadence, size limits and cost trade-offs against your workload before committing to a large SPICE footprint.

The Semantic Guidance Stack, what the AI reads

Quick Chat generates SQL using a layered metadata stack attached to Topics. The guidance lists seven layers of metadata that the engine consults while producing SQL. The published table shows layers 1-4 and 6-7 while layer 5 is omitted in that table. That omission needs clarification from the source. The visible, actionable layers are:

  • Dataset-level instructions, grain, primary key, foreign keys, aggregation rules, known edge cases, and refresh cadence.
  • Topic-level instructions, cross-dataset disambiguation, preferred dataset for ambiguous terms, default join behaviors, and multi-fact resolution.
  • Synonyms, maps user vocabulary (executive terms, analyst shorthand, acronyms) to fields/filters.
  • Field descriptions / semantic types, definition, unit/format, nullability, valid values, and aggregation behavior.
  • Column exclusions, hide noisy technical or deprecated columns so the AI doesn’t misuse them.
  • Calculated fields & named filters, canonical metrics and segments the AI can reference (for example, Net Revenue = sales.amount, returns.refund_amount).
  • Grain alignment / rollup instructions, how to aggregate higher-granularity tables before joining to avoid inflated numbers.

Authoring rules that materially reduce errors

Treat Topic metadata as executable instructions for SQL composition. The guidance offers eight best practices, here they are with the practical why and an actionable note for each.

  1. Author dataset-level instructions like a data dictionary.

    Why: the AI needs a precise definition of grain, PKs/FKs, derived metrics, and rows to exclude to avoid double-counting or mis-aggregation. Action: attach a short JSON or markdown doc to each dataset with grain, PK, FK hints, and refresh cadence (owner: dataset steward).

  2. Author topic-level instructions for cross-dataset logic and disambiguation.

    Why: when multiple datasets could answer “sales, ” explicit preference rules prevent the AI from choosing the wrong source. Action: add disambiguation rules and default join guidance in the Topic metadata (owner: analytics product manager).

    “To answer questions about customer lifetime value, join ORDERS to CUSTOMERS on customer_id using a LEFT JOIN, so customers with zero orders still appear in the result.”

  3. Design synonyms that match how people actually talk.

    The guidance recommends aiming for 3-7 synonyms per commonly queried column and warns that more than 10 risks ambiguity. Why: synonyms reduce dataset-selection errors and help the model map intent to fields. Action: classify synonyms into executive, analyst, and domain jargon buckets (owner: BI content lead).

  4. Enrich field descriptions for the most-used columns.

    The guidance recommends enriching the top 15-20 queried fields across a Topic with unit/format, nullability, valid values and aggregation behavior. Why: clearer field semantics cut down on aggregation and unit mistakes. Action: prioritize the top 20 fields by query volume and populate full semantic descriptions (owner: data steward).

  5. Guide join behavior without wiring every relationship.

    Use explicit join hints, grain-alignment rules, and union instructions so the AI knows when to UNION ALL or when to aggregate before joining. Action: encode common join patterns in Topic-level instructions and test with sample queries (owner: analytics engineer).

    “ONLINE_ORDERS and IN_STORE_ORDERS have identical schemas. When the user asks about ‘all orders’, ‘total orders’, or ‘combined sales’, use UNION ALL of both tables before applying any filters or aggregations. Always include a channel_type column: ‘ONLINE’ for ONLINE_ORDERS, ‘IN_STORE’ for IN_STORE_ORDERS.”

  6. Handle complex patterns with explicit recipes.

    Many-to-many joins, role-playing dimensions, recursive hierarchies and cross-grain comparisons need one-line guidance: start from the bridge table for enrollments, specify which DATE_DIM role maps to “order date, ” or instruct the model to roll daily to month before join. Action: add a short pattern section per complex case (owner: analytics architect).

  7. Reduce noise by excluding irrelevant columns.

    Why: hiding surrogate keys, ETL audit columns and deprecated fields reduces the chance the AI will group by or filter on technical columns. Action: maintain a column-exclusion list per dataset (owner: ETL/data engineering).

  8. Test, validate and iterate with a question bank.

    The guidance recommends building 15-25 representative questions per major dataset before publishing a Topic. Why: this forces the model to exercise joins, aggregations and NULL logic and surfaces failure modes early. Action: run the question bank, compare outputs to canonical queries, log failures, and iterate on metadata fixes (owner: QA/analytics).

Concrete examples and small details that prevent big mistakes

  • Union-first semantics: If two fact tables share a schema (online vs in-store), instruct the engine to UNION ALL before filters and aggregations so channel attribution is correct.
  • Grain alignment: For DAILY_SALES and MONTHLY_FORECAST, tell the model to roll daily to month before joining so metrics compare at the same grain.
  • Role-playing dates: State which DATE_DIM role maps to “order date” vs “ship date” to avoid incorrect joins.
  • Null-safe math and functions: Example SQL in the guidance shows DATE_TRUNC(‘month’), COALESCE(…, 0) and NULLIF(…, 0). These are illustrative. Confirm function compatibility with your target SQL dialect before using generated SQL in production.

“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.”

Guidance excerpt attributed to the Quick Chat best-practices authors

Choosing defined relationships, semantic-only, or hybrid

  • Defined-relationship Topics, prefer when reports must be deterministic, auditable and governed.
  • Semantic-only (Chat) Topics, prefer for exploratory, natural-language access and when you want analysts to ask ad-hoc questions without engineering changes.
  • Hybrid Topics, define the core, high-confidence relationships and rely on semantic guidance for exploratory joins and edge cases.

Operational questions you should validate now

The published guidance is practical, but it leaves some operational details you must confirm with QuickSight docs or your AWS account team. Prioritize the items below and treat them as acceptance tests you must pass before rolling Chat Topics into production.

  1. Missing Semantic Guidance Stack layer: the published table omits layer 5. Action: request clarification from the QuickSight guide or authors.
  2. RLS semantics and auditability: confirm whether RLS predicates are pushed down pre-join, how conflicts across datasets are resolved, and how audit logs surface the effective predicate. Action: run a test case with divergent RLS rules and inspect query logs.
  3. Model/context/latency limits: as Topics grow in datasets and fields, measure Chat latency and any model-context truncation effects. Action: create two Topics (narrow vs wide) and run the same question bank while recording latency and SQL stability.
  4. SQL dialect and portability: confirm which SQL dialect(s) the Quick Chat engine targets and whether generated SQL needs post-processing for your warehouse. Action: capture generated SQL for representative queries and validate them against your target engine.
  5. SPICE vs Direct Query trade-offs: test SPICE performance, refresh windows and cost vs Direct Query latency and compute. Action: benchmark a set of Chat queries on SPICE and Direct Query under realistic loads.
  6. Logging and audit trail for generated SQL: confirm how and where generated SQL is stored, retention policies, and who can access logs for compliance reviews.
  7. Character and metadata limits: confirm any character limits for instructions, synonyms and descriptions to avoid truncated guidance.

Actionable checklist for BI teams (owner → output)

  • Dataset steward: Document grain, PK/FKs, refresh cadence and business rules; output = dataset-level markdown/JSON attached to the Topic.
  • Analytics product manager: Create topic-level disambiguation and default join rules; output = Topic instruction file/versioned metadata.
  • BI content lead: Build synonyms (start 3-7 per column) and maintain a column-exclusion list; output = synonym mappings and exclusion list.
  • Analytics engineer: Predefine calculated fields and named filters for canonical metrics; output = named metrics catalog integrated into Topics.
  • QA/analytics: Assemble 15-25 representative questions per major dataset, run them before publish, and compare results to canonical queries; output = validation report and remediation tickets.
  • Data engineering: Test SPICE vs Direct Query for representative Chat workloads and report cost/performance trade-offs; output = benchmarking summary and recommendation.
  • Security/Compliance: Validate RLS enforcement across multi-dataset queries and confirm audit log availability; output = compliance sign-off or remediation plan.

Key takeaways, questions you’ll want answers to

  • How should I write dataset instructions so Chat generates reliable SQL?

    Write them like a precise data dictionary: state the grain, primary key, foreign keys, refresh cadence, aggregation rules, derived-metric formulas, and rows to exclude. Attach this document to the dataset and version it.

  • How many synonyms and enriched field descriptions should I provide?

    The guidance recommends aiming for 3-7 synonyms per commonly queried column (more than 10 can cause ambiguity) and enriching the top 15-20 most-used fields across Topics. Start there and expand only when you measure missed mappings.

  • When should I define relationships versus rely on semantic guidance?

    Defined relationships (DAG + deterministic inner joins) are for governed, auditable reporting. Semantic-only Topics are for flexible, natural-language exploration. Use a hybrid approach when you need both strict governance for core data and flexible exploration around it.

  • Does RLS protect data when the AI generates cross-dataset SQL?

    The guidance states RLS is enforced at the dataset level regardless of how the AI composes joins or unions. Confirm the enforcement semantics and logs with your QuickSight admin to ensure the behavior meets your compliance needs.

  • How do I validate the AI’s SQL before I trust results?

    Build a question bank (15-25 representative queries per major dataset), run the bank regularly, compare AI-generated results to canonical queries or counts, log mismatches and iterate on metadata fixes. Capture generated SQL for audit and analysis.

Good metadata is the difference between liberating analysts and creating an accidental SQL sandbox. Start by documenting grain and keys, write clear topic rules for disambiguation and joins, hide noisy columns, and validate with a focused question bank. Before you publish: 1) run your 15-25 query bank, 2) confirm RLS behavior and log access with security, 3) test SPICE vs Direct Query on representative Chat queries.

0