Agentic AI Analytics: Amazon QuickSight + Athena over a Metadata-First Lakehouse

Unleashing agentic AI analytics with Amazon QuickSight, Athena and a metadata‑first lakehouse

TL;DR — Combine a metadata‑first lakehouse (Amazon S3 + Apache Iceberg + AWS Glue Data Catalog) with Amazon Athena and Amazon QuickSight (with Amazon Q) to give business users conversational, agentic AI access to governed analytics and domain context. Prototype on a reproducible dataset like TPC‑H, validate UX and governance, then scale while watching costs, latency and model safety.

Think of your lakehouse as a locked library filled with valuable reports and hard‑won tables. This pattern builds the librarian that understands your business, speaks natural language, cites sources and hands users the right chart — without routing every request through a SQL expert.

Why agentic AI for analytics matters

Agentic AI and AI agents aren’t a gimmick when tied to reliable data and a clear metadata layer. For business leaders, the payoff is straightforward: fewer SQL tickets, faster answers for product and finance teams, and auditable responses that cite the source. The tradeoffs are real — cost, freshness and safety — but manageable with the right architecture and controls.

The architecture reframes analytics as a self‑service capability that lets business users query complex datasets via natural language rather than SQL.

Architecture overview — the components you’ll stitch together

  • Storage: Amazon S3 for raw CSVs and formatted files; Apache Iceberg (Parquet files) for ACID, time travel and schema evolution; Amazon S3 Tables (managed Iceberg) for easier operations.
  • Metadata: AWS Glue Data Catalog to register tables and present a consistent catalog to query engines and BI tools.
  • Query engine: Amazon Athena as a serverless SQL layer for exploration, transformation and CREATE TABLE AS SELECT (CTAS) jobs.
  • BI + agentic layer: Amazon QuickSight with Amazon Q to publish datasets into SPICE (in‑memory engine), build Topics, Spaces and Chat Agents that answer questions and render visuals.
  • Knowledge Base (KB): A focused, indexed set of documents (specs, runbooks, definitions) used by retrieval to ground answers and attach citations.

A simple flow: land or transform data into Iceberg/S3 Tables, register with Glue, query and pre-join with Athena (CTAS), publish curated datasets into SPICE for sub‑second dashboards, then expose conversational access through QuickSight Topics/Spaces and Chat Agents backed by a targeted KB.

Implementation details and practical snippets

Storage and table formats

Use Apache Iceberg when you need ACID semantics, time travel and safe schema evolution. Use Amazon S3 Tables (managed Iceberg) if you prefer AWS to manage the catalog and table lifecycle. Keep raw CSVs for provenance, but convert production datasets into Iceberg/Parquet for performance and reliability.

Metadata first: tell Glue about every table

Register each table with AWS Glue Data Catalog so Athena and QuickSight can discover them consistently. Glue becomes the single source of truth for table schemas and partitions across formats.

Athena for transforms and CTAS

Use Athena for ad‑hoc exploration and for CREATE TABLE AS SELECT (CTAS) jobs that produce cleaned, partitioned Iceberg tables. Example CTAS (annotated):

CREATE TABLE analytics_db.orders_iceberg
WITH (format = 'ICEBERG') AS
SELECT *
FROM raw_db.orders
WHERE o_orderdate BETWEEN DATE '1998-06-01' AND DATE '1998-12-31';

This CTAS creates an Iceberg table scoped to a specific date range — a pattern that reduces downstream query cost and improves SPICE ingestion predictability.

Amazon QuickSight, Amazon Q and SPICE

Publish curated tables to QuickSight datasets and load them into SPICE for sub‑second interaction. SPICE is an in‑memory engine that accelerates dashboards and agent responses; plan capacity as part of the UX. Amazon Q powers natural‑language-to-visual interactions inside QuickSight and the Chat Agents within QuickSight Spaces can surface visuals, cite KB passages and answer follow-ups.

Knowledge Base design (make KBs first‑class)

Focus KB documents by topic. Chunk long PDFs into small, labeled passages with metadata (title, doc id, section). Retrieval works best when it returns concise, high‑precision passages instead of a monolithic dump.

Agent trust and retrieval‑augmented generation (RAG) guardrails

Agents must be instructed and engineered to prefer grounded evidence over speculation. Good operational rules reduce hallucination risk and improve auditability.

  • Persona prompt (example): 
    You are a data assistant for the Sales Ops team. Always answer using only content available in the attached Space and Knowledge Base. When you use data, include table names and cite KB passages. If you cannot find a grounded answer, say "I don't have a source" and offer a recommended query for a human reviewer.
  • RAG tuning starting points: Top‑K = 5, similarity threshold ≈ 0.7 (tf‑idf/embedding similarity), and return up to 3 passages per retrieval. Treat these as starting points to tune for your KB and domain.
  • Human‑in‑the‑loop: Route high‑risk queries (financial closing totals, personal data lookups) to backline reviewers with SLAs and require manual approval before publishing sensitive answers.

Best practice: keep Knowledge Base documents focused and topic‑specific so retrieval returns precise, relevant passages.

Governance, security and access control

Design governance before opening conversational access. Two main models exist: Lake Formation centralized authorization and standard IAM/S3/Athena controls. Choose based on your security posture and scale.

  • Lake Formation (recommended for centralized control):
    • Pros: central permission catalog, fine‑grained grants (SELECT, DESCRIBE), easier auditing.
    • Cons: additional configuration and role mapping required for QuickSight authors and service roles.
    • Required QuickSight grants (example): grant SELECT and DESCRIBE on Glue tables to the QuickSight service principal or the QuickSight author role as of May 2026.
  • IAM + S3/Athena (simple setups):
    • Pros: familiar to teams already using IAM policies and S3 bucket controls.
    • Cons: harder to enforce consistent table‑level policies across many datasets.
  • PII and KB access: classify documents, redact sensitive passages before indexing, and maintain differential access to KBs per Space.
  • Audit logs to collect: Athena query logs, QuickSight usage and activity logs, Glue catalog events, S3 access logs and Lake Formation audit logs.

Observability and reliability

Monitor both data and agent behavior. Key signals:

  • Athena query latencies and costs (median, p95).
  • SPICE ingestion times and memory utilization.
  • Agent response times, percent of answers with KB citations, and % of “I don’t know” responses.
  • Hallucination hits: answers with no matching KB passages or low similarity scores.
  • User adoption: percentage of questions answered without escalation to BI or backline reviewers.

Run synthetic concurrency tests to validate Athena and agent throughput. Simulate KB outages and slow queries to verify graceful degradation and escalation paths.

Cost considerations (drivers, not fixed numbers)

Primary cost drivers are SPICE capacity, Athena query volume and storage/requests on S3. To estimate:

  • Baseline SPICE needs by the number of dashboards, cardinality of datasets and desired latency; SPICE is a UX cost, not optional for interactive use.
  • Estimate Athena costs by expected query concurrency from agents and ad‑hoc analysts; materialize expensive joins with CTAS to reduce repeated scan costs.
  • Use partitioning, compression and Iceberg features to lower scan costs and control cold‑data storage expenses.
  • As of May 2026, Athena and SPICE behaviors change; link to official docs and measure with a short pilot rather than assuming published numbers will match workload patterns.

POC playbook — 2–4 week timeline

  1. Day 0–3: Groundwork
    • Pick a reproducible sample (TPC‑H or a 1–10 GB production slice).
    • Create S3 buckets (same AWS Region), register tables in Glue Data Catalog.
  2. Day 4–7: Transform & catalog
    • Use Athena CTAS to build an Iceberg table and a materialized analytic table.
    • Confirm Glue catalog discovery and QuickSight dataset connectivity.
  3. Day 8–12: SPICE & visual work
    • Publish curated datasets to SPICE, build 3–5 dashboards representing common business questions.
  4. Day 13–18: KB and agent
    • Index a focused Knowledge Base (specs, definitions), create QuickSight Topics/Spaces and a Chat Agent with persona prompts and retrieval settings.
  5. Day 19–28: Test & iterate
    • Run user tests, measure citation rate, query latency and escalation rates. Tune RAG parameters and SPICE sizing.

What success looks like (POC metrics)

  • Median SPICE query latency: sub‑second for top dashboards.
  • ≥80% of sampled business questions answered with at least one KB citation.
  • Reduction in SQL ticket backlog for the selected domain by ≥50% during the pilot window.
  • Hallucination rate under target (e.g., <5% of answers flagged for manual review).

Runbook highlights & teardown

Keep a short runbook: where the CTAS tables live, SPICE datasets to delete, QuickSight Topics/Spaces/Agents and S3 buckets to remove. For cleanliness after a trial, drop materialized tables, delete SPICE datasets and remove QuickSight artifacts to avoid surprise costs.

Action checklist for executives

  1. Authorize a 2–4 week POC with a cross-functional team: analytics, security, and product.
  2. Choose a canonical dataset (TPC‑H or production slice) and an initial domain (sales, finance, ops).
  3. Define success metrics: latency, citation rate, ticket reduction, hallucination tolerance.
  4. Decide on governance model (Lake Formation vs IAM) and assign an owner to manage permissions and audits.
  5. Plan budget for SPICE capacity and expected Athena query volume during the pilot.

Key questions and answers

  • How do you query multiple lakehouse formats uniformly?

    Register CSV, Iceberg and S3 Tables in AWS Glue Data Catalog and use Amazon Athena as the unified serverless SQL layer to query across formats.

  • How do non‑technical users get natural‑language access to the data?

    Create Amazon QuickSight Topics and Spaces, publish curated datasets to SPICE for fast interaction, and attach a focused Knowledge Base so Chat Agents can cite sources while answering in natural language.

  • What governance controls are required?

    Use Lake Formation to centrally enforce SELECT/DESCRIBE permissions when enabled; otherwise rely on IAM, S3 and Athena access controls. Plan logging and audit of agent queries and KB retrievals.

  • When should joins be done before SPICE ingestion?

    If the combined secondary tables exceed ~1 GB, pre-join via Athena and materialize the result for SPICE to avoid runtime join costs and slowdowns inside QuickSight.

  • How do you limit agent hallucinations?

    Keep KB documents tight and topic‑specific, set retrieval thresholds, use persona prompts that demand citations, and route uncertain or high‑risk queries to human reviewers.

Final notes and next steps

Agentic AI analytics unlocks faster decisions and reduces dependency on a shrinking pool of SQL experts — but only if data is organized, governed and observable. Prototype quickly with a reproducible dataset, treat the Knowledge Base as a primary input, and bake governance into the design.

If a one‑page executive brief (costs, risks, benefits) or a production‑readiness checklist (security, monitoring, model safety) would help, say which you prefer and a tailored deliverable can be prepared.

Authors: Raj Balani (Solutions Architect, AWS), Praney Mahajan (Senior Technical Account Manager, AWS) and Rahul Sonawane (Principal Specialty Solutions Architect – GenAI/ML & Analytics, AWS).

0