NLQueries GitHub

Blog  / 

Ask Snowflake questions in plain English: setup, permissions and cost

Published . Written for nlqueries-core 0.4.

snowflake security query-history cost

A Snowflake natural language query is two problems at once. One is the usual text-to-SQL question: will the model write the SQL you meant? The other belongs to Snowflake: every statement it runs, right or wrong, runs on a warehouse you pay for. This post covers both for nlqueries-core 0.4. It goes through the role to create, the warehouse to give it, how query history becomes context, and how to see the SQL before it costs anything. The Snowflake page has the short version.

Connect as a role built for it

Install the extra and register the connection:

pip install "nlqueries-core[snowflake]"
nlqueries connect snowflake --account myorg.us-east-1 --database ANALYTICS \
  --schema PUBLIC --warehouse NLQ_WH --user NLQ_SVC --alias snow

Leave out --password and connect prompts for it without echoing, then stores it in your OS keychain rather than in ~/.nlqueries/connectors.yaml. Where no keychain is available, it falls back to the stored connection URL and warns you. --password-env VAR reads it from an environment variable instead. In 0.4 the connector signs in with a user and password only; key-pair authentication is not supported yet. Snowflake is moving accounts away from single-factor passwords, so check that your account's authentication policy still allows one for this user before you start. An account pasted from a browser address bar is fine: the connector trims https:// and the .snowflakecomputing.com suffix.

The connector does not pick a role. The session runs as the user's default role, so give the NLQueries user one role, make it the default, and grant that role only what you would put in an answer. In Snowflake's SQL:

CREATE ROLE NLQ_READER;
GRANT USAGE ON WAREHOUSE NLQ_WH TO ROLE NLQ_READER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE NLQ_READER;
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE NLQ_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE NLQ_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE NLQ_READER;

CREATE USER NLQ_SVC PASSWORD = '<a long random secret>'
  DEFAULT_ROLE = NLQ_READER DEFAULT_WAREHOUSE = NLQ_WH;
GRANT ROLE NLQ_READER TO USER NLQ_SVC;

Why the role is the boundary on Snowflake

Snowflake has no read-only transaction to ask for. Instead, the connector runs every generated statement between BEGIN and ROLLBACK. An INSERT or UPDATE that got past the SQL policy is undone, on success as well as on failure. DDL is not transactional on Snowflake, though, so a CREATE TABLE or DROP that ran stays. The SQL policy refuses DDL before it gets that far, but the policy is application code. On the database side, the grant is the only control for DDL. What each connector enforces has the table for every engine.

A Snowflake transaction belongs to the session, not the cursor, and the connector holds one session. So queries on one connector run one at a time: two people asking at once wait their turn instead of sharing a transaction.

nlqueries health --connector snow checks the role for you. The first time it runs, it reads SHOW GRANTS TO ROLE for the current role and every role it inherits, up to 50. It reports:

  • any of ACCOUNTADMIN, ORGADMIN, SECURITYADMIN, SYSADMIN or USERADMIN within reach;
  • any privilege beyond USAGE, SELECT, REFERENCES, MONITOR, OPERATE, READ and IMPORTED PRIVILEGES.

A role that holds nothing more reads least privilege. Secondary roles are not followed. Like the PostgreSQL check, it reports and does not refuse the connection.

Query history as context

A schema says which tables exist. Your team's query history says which ones they actually join, and on what. extract-schema reads the tables, process-history turns the frequent queries into the knowledge base the model sees, and export-kb writes it out:

nlqueries extract-schema snow
nlqueries process-history snow --days 30
nlqueries export-kb snow

On Snowflake the history comes first from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY, grouped by query text, most frequent first. Reading that view needs IMPORTED PRIVILEGES on the SNOWFLAKE database. If the role cannot read it, the connector falls back to the INFORMATION_SCHEMA.QUERY_HISTORY() table function, which covers the last 7 days only, whatever --days says. If neither can be read, process-history stops with an error that names the grant.

Decide that grant on purpose:

  • IMPORTED PRIVILEGES opens the whole ACCOUNT_USAGE schema to the role, and its QUERY_HISTORY is account-wide. The connector's history query does not filter by database, so statements run against other databases in the account are read too.
  • What is kept is narrower than what is read. The pipeline drops anything that is not a SELECT, statements touching information_schema, commands such as SHOW and SET, and queries run fewer than three times (--min-executions). Every literal becomes a typed placeholder, so a value in a WHERE clause does not reach the knowledge base as written.
  • Without the grant you get at most a week, and only the queries Snowflake lets this role see through the table function. That is a Snowflake rule, not ours: usually the user's own queries and those on warehouses the role monitors or operates. For a service user that asks nothing else, that can be very little.

The connector table lists the history source for every engine, and process-history has the flags.

Preview the SQL before it spends anything

The SQL is generated and checked in Snowflake's dialect, taken from the connector's type. Then there are two ways to ask:

  • nlqueries ask snow "..." generates and validates the SQL and never runs it.
  • nlqueries query snow "..." generates it, runs it, and prints the rows. With --no-execute it stops before running.

"Never runs it" is about the generated statement. With literal grounding on, which is the default, ask can still open the connector read-only to check the statement's string literals against the values a column stores. That is how status = 'shipped' becomes 'Shipped' when that is how it is stored. The lookups are bounded: at most 8 literals a pass, 1 second each and 3 seconds in all. A table the knowledge base records as larger than NLQ_LITERAL_GROUNDING_MAX_ROWS (a million rows by default) is not queried. On Snowflake a lookup is still a query, though. It needs a running warehouse, and an auto-resuming one will start for it. For a preview that touches nothing, set NLQ_LITERAL_GROUNDING=false. See ask.

Cost: bound the query, then the warehouse

Time per query. CONNECTOR_STATEMENT_TIMEOUT_SECONDS (120 by default) goes with each statement as Snowflake's own statement parameters, so the server cancels it, not the client. The budget is split in two:

  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS bounds the wait in the warehouse queue. It is set by SNOWFLAKE_QUEUE_TIMEOUT_SECONDS: 30 by default, never more than half the budget.
  • STATEMENT_TIMEOUT_IN_SECONDS bounds the run, with the rest of the budget.

With the defaults, that is 30 seconds queued and 90 running. Raise the queue share for a warehouse that queues under load or is slow to resume. The total stays the same.

Size per query. A connector stops reading a result at 10,000 rows (CONNECTOR_MAX_FETCH_ROWS) or about 64 MiB (CONNECTOR_MAX_RESULT_BYTES), whichever comes first. That bounds memory, not credits: by then the warehouse has done the work.

Building the knowledge base. export-kb stores sample values, so the model sees how values are spelled. That costs one sample query per table and one SELECT DISTINCT per text column. --values-max-rows skips the largest tables, and --no-include-samples runs none of these queries. See export-kb.

What caps spend for good is on Snowflake's side. These are Snowflake features, not NLQueries ones:

  • A dedicated, small warehouse. Every generated query runs on the warehouse named at connect. Give NLQueries its own, start at X-Small, and set a short AUTO_SUSPEND with AUTO_RESUME on, so it costs nothing between questions. Snowflake bills at least 60 seconds each time a warehouse resumes.
  • A resource monitor on that warehouse, which suspends it at a credit quota. Only an account administrator can create one.
CREATE WAREHOUSE NLQ_WH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

CREATE RESOURCE MONITOR NLQ_MONTHLY WITH CREDIT_QUOTA = 20
  FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 90 PERCENT DO SUSPEND
           ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE NLQ_WH SET RESOURCE_MONITOR = NLQ_MONTHLY;

A timeout bounds how long one statement runs. A resource monitor bounds what all of them cost together. You want both.

A checklist

  1. A user whose default role is a read-only role, and a dedicated X-Small warehouse.
  2. nlqueries health --connector snow reports least privilege.
  3. Decide on IMPORTED PRIVILEGES: account-wide history, or a week of it.
  4. Run process-history, then export-kb.
  5. Use ask while you build trust in the agent. Turn literal grounding off if even its lookups are too much.
  6. Put a resource monitor on the warehouse.

To try it, pip install "nlqueries-core[snowflake]" and follow getting started, or read the code on GitHub. For how query history becomes context on every engine, see How to build a text-to-SQL knowledge base from query history.

All posts · RSS feed · Documentation