How to Connect Snowflake and Google BigQuery to Dust for Natural Language SQL Queries

Enterprise analytics teams frequently hit an operational bottleneck: business stakeholders submit ad-hoc tickets for standard metrics, while self-serve BI tools remain too complex for non-technical users. Standard generative AI knowledge bases and Retrieval-Augmented Generation (RAG) tools cannot solve this problem. Semantic search relies on text vector similarity, making it impossible to aggregate numeric values, calculate conversion rates, or group metrics across millions of records.

Dust bridges this gap through structured data integrations and its Table queries feature. Instead of moving or duplicating data warehouse storage, Dust connects directly to Snowflake and Google BigQuery, indexes schema metadata, and equips AI agents to translate natural language questions into governed, read-only SQL queries.

This guide details the technical architecture, security configurations, and operational workflows required to connect Snowflake and BigQuery to Dust, map table schemas, and deploy natural language querying agents across enterprise warehouses.

Architecture: How Dust Queries Structured Warehouses Without Storing Raw Data

A common security and governance concern when connecting AI systems to enterprise data warehouses is whether raw transactional records will be duplicated or stored in third-party vector databases. Dust addresses this with a metadata-only architecture:

1. Zero Data Synchronization: Dust does not synchronize, mirror, or retain customer records from Snowflake or BigQuery. It only inspects and indexes metadata: database names, dataset identifiers, schema structures, table names, column names, data types, and native schema descriptions.
2. Schema Metadata Injection: When a user asks an analytical question (e.g., "What was our average order value by region last quarter?"), the agent references the indexed table schemas to understand table relationships and metrics definitions.
3. Controlled SQL Synthesis: The model generates a dialect-specific SQL query grounded strictly in the available tables.
4. Direct Read-Only Execution: The query is dispatched to the target warehouse over an encrypted connection using designated read-only credentials.
5. Synthesis and Transparency: Dust parses the returned query results, generates a natural language business response or data visualization, and displays the underlying SQL and execution logs behind a Tools inspection drawer for full auditability.

Option 1: Connecting Snowflake to Dust

Dust provides two distinct integration patterns for Snowflake: the Snowflake Connection (Spaces > Connections, optimized for curated administrative table sharing) and the Snowflake Tool (OAuth-driven runtime schema exploration and view querying). Most analytics teams deploying governed self-service agents use the Snowflake Connection.

1. Snowflake Security & Infrastructure Provisioning

Follow the principle of least privilege by creating a dedicated compute warehouse, user account, and role within Snowflake.

-- Step 1: Create a dedicated role
CREATE ROLE dust_role;

-- Step 2: Create a dedicated compute warehouse with auto-suspend
CREATE WAREHOUSE dust_warehouse
WITH WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;

GRANT USAGE ON WAREHOUSE dust_warehouse TO ROLE dust_role;

Authentication Choice: Key Pair vs. Password

Snowflake recommends Key Pair Authentication over basic passwords, as password-only programmatic access is subject to stricter deprecation policies.

Setting up Key Pair Authentication (Recommended):

1. Generate a 2048-bit RSA private key and public key:

   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out dust_rsa_key.p8 -nocrypt
   openssl rsa -in dust_rsa_key.p8 -pubout -out dust_rsa_key.pub
   

2. Extract the public key string (excluding header, footer, and line breaks):

   cat dust_rsa_key.pub | grep -v "BEGIN PUBLIC KEY" | grep -v "END PUBLIC KEY" | tr -d '\n'
   

3. Create the Snowflake user bound to the public key:

   CREATE USER dust_user
     DEFAULT_ROLE = dust_role
     RSA_PUBLIC_KEY = '<PASTE_CLEAN_PUBLIC_KEY_STRING>';
   
   GRANT ROLE dust_role TO USER dust_user;
   

Alternative (Password Authentication):

CREATE USER dust_user
  PASSWORD = 'YourSecurePasswordHere'
  DEFAULT_ROLE = dust_role;

GRANT ROLE dust_role TO USER dust_user;

2. Network Egress and Granting Read-Only Privileges

If your Snowflake account restricts ingress via Network Policies, whitelist Dust's static egress IP addresses:

• US Workspaces (https://dust.tt): 34.46.9.232
• EU Workspaces (https://eu.dust.tt): 35.195.191.222

Next, grant metadata access and table reading privileges. Dust strictly rejects any role configured with mutation grants (INSERT, UPDATE, DELETE, MERGE):

-- Grant USAGE on the container database and schema
GRANT USAGE ON DATABASE analytics_dw TO ROLE dust_role;
GRANT USAGE ON SCHEMA analytics_dw.marts TO ROLE dust_role;

-- Grant SELECT on tables (Individual tables or all tables in schema)
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_dw.marts TO ROLE dust_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_dw.marts TO ROLE dust_role;

Important: The managed Snowflake Connection only exposes physical tables to agent Table Queries; standard views and materialized views are ignored in this mode. If your architecture requires querying views, use the OAuth-based Snowflake Tool.

3. Activating the Connection in Dust

1. In Dust, navigate to Spaces > Connections > Add Connections.
2. Select Snowflake.
3. Choose Key Pair Authentication (or Username & Password).
4. Enter the required parameters:
   • Account Identifier: Format org-account or account_locator.region (e.g., xy12345.us-east-1).
   • Role: dust_role.
   • Warehouse: dust_warehouse.
   • Username: dust_user.
   • Private Key: Paste the complete PEM content including header and footer.
5. Select the specific databases, schemas, and tables to expose to Dust agents.

Option 2: Connecting Google BigQuery to Dust

Google BigQuery connects to Dust via Google Cloud Platform (GCP) IAM service account keys, supporting location-based dataset isolation and native column description harvesting.

1. GCP Service Account Configuration

1. In Google Cloud Console, open IAM & Admin > Service Accounts.
2. Click Create Service Account and name it dust-warehouse-sa.
3. Assign the two required minimum IAM roles:
   • roles/bigquery.user: Grants permission to run query jobs, list datasets, and consume query compute.
   • roles/bigquery.dataViewer: Grants permission to inspect dataset schemas and read table records.
4. Navigate to the Keys tab for the newly created service account.
5. Select Add Key > Create new key > JSON, and save the downloaded credentials file securely.

2. Registering BigQuery in Dust

1. In Dust, go to Spaces > Connections > Add Connections and select BigQuery.
2. Paste the complete contents of the downloaded service account JSON key.
3. Select BigQuery Location: BigQuery strictly forbids cross-region queries (e.g., joining an EU dataset with a US dataset in a single query). Choose the target cloud region (e.g., US, EU, us-central1). All datasets queried through this connection must reside within this designated location.
4. Select the datasets and tables you want to make queryable.
5. Enable BigQuery Descriptions: Check the option "Use BigQuery descriptions". This tells Dust to automatically ingest table-level and column-level descriptions directly from BigQuery schema metadata, giving the agent built-in contextual definitions.

Snowflake Connection vs. Snowflake Tool: Which Should You Use?

Data engineering teams often ask whether to configure the managed connection or the agent tool for Snowflake. The table below outlines the trade-offs:

Configuring AI Agents to Execute Governed Table Queries

Once Snowflake or BigQuery tables are available in your Space, analytics teams can configure custom agents (or use the system @deep-dive agent) to serve end users.

1. Agent Builder Tool Selection

When creating a custom agent in Dust:

1. Navigate to Agent Builder > Instructions & Tools.
2. Under Action & Data sources, click Add a tool and select Query Tables.
3. Select the target warehouse tables (e.g., marts.fct_daily_revenue from Snowflake, or reporting.monthly_churn from BigQuery).

2. Instructing the Agent on Table Joins & Metrics Logic

While models infer schema types from table metadata, explicit domain logic is necessary to prevent hallucinations on complex joins and temporal metrics. Include guidance like the following in the agent's instructions:

You have access to structured warehouse tables:
- fct_orders: Contains historical transactional volume and revenue metrics.
- dim_customers: Contains customer tier, sign-up dates, and company demographics.

Guidelines for query generation:
1. Join fct_orders and dim_customers using dim_customers.customer_id = fct_orders.customer_id.
2. When answering revenue questions, filter by `order_status = 'COMPLETED'` unless specifically asked for gross pipeline.
3. Prefer explicitly named columns over SELECT *.
4. Always convert raw UTC timestamps to standard quarters or calendar months when aggregating trends.
5. Accompany tabular figures with a concise natural language explanation highlighting the core operational insight.

3. Query Execution and Auditability (Tools Inspection)

When a user submits a prompt, Dust's runtime executes the query lifecycle:

• Schema context is loaded into the prompt context.
• The model generates SQL conforming to Snowflake or GoogleSQL syntax.
• The SQL runs against the warehouse, and tabular records return to the model context.
• Dust formats the response with text explanations and dynamic charts.
• Team members can click Tools inspection on the response card to review the exact SQL syntax, run latency, and raw returned payload, verifying the calculation methodology.

Best Practices for Analytics and Operations Teams

1. Expose Curated Data Marts, Not Raw Data Lakes: Point Dust agents toward normalized dimensional models (e.g., dbt marts or gold-tier reporting tables) rather than bronze ingestion layers. Clean schemas drastically improve SQL generation accuracy.
2. Document Column Comments in the Warehouse: Dust leverages table and column descriptions (especially with BigQuery's native description toggle). Maintaining descriptive metadata in warehouse DDL directly improves query generation quality without requiring prompt edits.
3. Control Compute Costs:
   • On Snowflake, set dust_warehouse to XSMALL with an AUTO_SUSPEND window of 300 seconds or lower.
   • On BigQuery, implement project-level byte-scan quotas on the dedicated service account to prevent expensive full-table scans.
4. Combine Unstructured and Structured Context: Agents can pair the Query Tables tool with the Search Data Source (RAG) tool. For example, an agent can query BigQuery for actual quarterly revenue variance and simultaneously search Notion or Google Drive post-mortems to explain the business context behind the figures.

References

• Snowflake - Multiplayer AI
• BigQuery - Multiplayer AI
• Table queries - Multiplayer AI
• Snowflake - Multiplayer AI
• AI Data Analytics Agents: Turn Data Into Business Decisions | Dust
• Integrations | Dust AI Agents