Webhook icon
Script icon
OutputValues icon
SlackIncomingWebhook icon

Vanna AI Text-to-SQL RAG and Query Execution Guard

Convert natural language into ClickHouse and Postgres SQL with Vanna AI. Enforce AST read-only guards and render interactive HTML reports in Kestra.

Categories
AIData

Non-technical stakeholders, such as marketers, growth leads, and product managers, frequently need fast answers from enterprise data warehouses (ClickHouse, PostgreSQL, DuckDB, Snowflake). However, granting direct SQL access poses serious compliance and operational risks, while generic LLMs often hallucinate invalid table schemas or attempt destructive modifications (DELETE, UPDATE, DROP, TRUNCATE).

This blueprint provides a secure, self-contained Text-to-SQL analytics pipeline powered by Vanna AI and sqlglot. It accepts dynamic plain-English business questions, generates dialect-optimized queries, enforces an automated Abstract Syntax Tree (AST) read-only validation gate, executes the query safely, and renders interactive HTML report artifacts directly inside Kestra UI.

How it works

  1. Dynamic Prompt Ingestion: The flow accepts dynamic natural language business questions via the Kestra UI, scheduled executive digests, or authenticated HTTP webhooks (on_demand_query_webhook).
  2. Dialect-Aware SQL Generation: The engine references schema DDL and generates syntactically optimized queries tailored to the selected database engine:
    • ClickHouse: Employs native time-series aggregations (toStartOfDay, uniqExact, formatReadableQuantity).
    • PostgreSQL / DuckDB: Uses ANSI-standard window functions, DATE_TRUNC, and explicit type casting.
    • Snowflake: Injects Snowflake-specific syntax (COUNT_IF, DATEADD).
  3. AST Read-Only Safety Validation Gate: Powered by sqlglot, the generated query undergoes strict syntactic tree verification before reaching the database:
    • Root Select Verification: Asserts that the root expression is strictly a Select node.
    • Destructive Mutation Blacklist: Scans the AST to guarantee zero mutation operations (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, GRANT).
    • Injection Protection: Enforces single-statement execution, rejecting semicolon-chained SQL injection payloads.
    • Automatic Limit Enforcement: Injects LIMIT max_rows if omitted, guarding against memory crashes from unbound Cartesian joins.
  4. Interactive HTML Data App & CSV Generation: Executes the validated query and produces an interactive, styled HTML dashboard (interactive_report.html) complete with KPI stat cards, sortable tables, and a verified SQL inspector viewable in Kestra UI.
  5. Slack Notification: Posts an analytical summary scorecard to Slack with question details, KPI metrics, and direct execution links.

What you get

  • Self-Service Analytics for Marketers: Allows non-technical teams to query warehouses using natural language without writing SQL.
  • Guaranteed Database Safety: Hard AST gates prevent accidental or malicious data modification (DROP, DELETE, UPDATE).
  • Multi-Dialect Compatibility: Seamlessly adapts query logic across ClickHouse, PostgreSQL, DuckDB, and Snowflake.
  • Embedded Visualization: Generates ready-to-view HTML dashboards and downloadable CSV datasets without requiring external BI servers.

Expected outputs

Downstream processes or webhook callers can consume the typed values emitted by output_query_metrics:

Output Variable Description Expression Example
natural_question Original natural language question submitted by the user {{ outputs.output_query_metrics.values.natural_question }}
database_type Database dialect targeted for the query {{ outputs.output_query_metrics.values.database_type }}
safety_verdict AST verification determination (PASSED_READONLY or REJECTED_DESTRUCTIVE) {{ outputs.output_query_metrics.values.safety_verdict }}
generated_sql Syntactically verified and sanitized SQL query {{ outputs.output_query_metrics.values.generated_sql }}
rows_returned Total number of data rows returned by the query {{ outputs.output_query_metrics.values.rows_returned }}
top_metric Primary KPI metric extracted from analytical results {{ outputs.output_query_metrics.values.top_metric }}
interactive_report_uri Internal Kestra storage URI to interactive_report.html {{ outputs.output_query_metrics.values.interactive_report_uri }}
csv_results_uri Internal Kestra storage URI to query_results.csv {{ outputs.output_query_metrics.values.csv_results_uri }}
verified_sql_uri Internal Kestra storage URI to verified_query.sql {{ outputs.output_query_metrics.values.verified_sql_uri }}

Who it's for

  • Growth & Marketing Teams needing fast, ad-hoc cohort and campaign insights without waiting for data engineering tickets.
  • Data Platform & Security Engineers providing safe, read-only AI query interfaces over production analytical warehouses.
  • Analytics Engineers standardizing dialect-aware query generation across multi-engine data stacks.

Why orchestrate this with Kestra

Building Text-to-SQL interfaces with unmonitored scripts exposes databases to catastrophic data loss and runaway queries. Kestra provides:

  • Granular Access Control: Expose parameterized UI forms to business users without sharing database connection credentials.
  • Automated AST Security Gates: Block dangerous mutation syntax programmatically before queries ever touch production tables.
  • Ephemeral Container Isolation: Execute query translation and HTML report generation inside isolated Docker workers.

Prerequisites

  • Kestra runner with Docker or Kubernetes access to pull container images.
  • An incoming Slack webhook URL for analytics notifications.

Secrets

Configure the following secrets in your Kestra namespace or instance settings:

Secret Name Description Example
SLACK_WEBHOOK_URL Incoming webhook URL for marketing analytics alerts https://hooks.slack.com/services/T00/B00/X00
WEBHOOK_KEY Secret authentication key for triggering queries via webhook whk_vanna_secure_48291

Quick start

  1. Import this blueprint into your Kestra instance under the company.ai namespace.
  2. Add SLACK_WEBHOOK_URL and WEBHOOK_KEY into Settings > Secrets.
  3. Click Execute, enter any dynamic business question, select your target database dialect (CLICKHOUSE, POSTGRESQL, DUCKDB, SNOWFLAKE), and run.
  4. Navigate to the Outputs tab in Kestra UI to preview the interactive HTML dashboard and download the CSV.

Common pitfalls & notes

  • Strict Read-Only Enforcement: The AST parser will intentionally abort execution if a query contains any data manipulation keywords (UPDATE, DELETE, DROP). Non-technical users cannot accidentally modify database state.
  • Dialect Specificity: Always match database_type to your target warehouse so time-series aggregations (such as ClickHouse toStartOfDay vs Postgres DATE_TRUNC) generate with exact syntax compatibility.
  • Statement Timeouts: Set statement_timeout_seconds to a reasonable ceiling (e.g., 10-30s) to kill runaway analytical queries automatically.

How to extend

  • Live Database Connection: Chain io.kestra.plugin.jdbc.clickhouse.Query or io.kestra.plugin.jdbc.postgresql.Query using {{ outputs.output_query_metrics.values.generated_sql }} to execute directly against live database clusters.
  • Vanna Vector Store Training: Connect a training task that synchronizes fresh DDL changes and schema documentation into Vanna's ChromaDB or Qdrant vector memory daily.
  • Slack Bot Integration: Connect this workflow's webhook to an outgoing Slack slash command (/ask-data), allowing team members to ask questions directly within Slack channels.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.