ASK() — natural-language SQL for DuckDB, grounded in your dbt Semantic Layer
Installing and Loading
INSTALL duckthink FROM community;
LOAD duckthink;
Example
CREATE SECRET oai (TYPE openai, API_KEY 'sk-...');
CREATE MODEL('gpt', 'gpt-4o-mini', 'openai');
SET ask_model = 'gpt';
-- Ground ASK in your dbt Semantic Layer: metrics, entities and tags come from the
-- manifest, and a tag scopes the question to a business domain — no ON (...) needed.
SET ask_dbt_semantic_manifest = 'target/manifest.json';
SET ask_dbt_scope_tags = 'support';
ASK('average ticket resolution time by team this month')
RETURN (team VARCHAR, avg_hours DECIMAL);
About duckthink
duckthink brings ASK() — natural-language-to-SQL — to DuckDB, grounded in your dbt Semantic Layer. Point it at a dbt manifest (SET ask_dbt_semantic_manifest) and generation uses your real semantic models, metrics, entities and tags — so "gross margin" or "attrition rate" resolve to their exact definitions instead of the model's best guess — and a dbt tag (SET ask_dbt_scope_tags) scopes a question to a business domain. ASK writes exactly one read-only SELECT over the tables in scope, executes it, and returns typed rows. It also runs over the plain DuckDB catalog with no dbt, and optional embedding-based table retrieval (SET ask_embed_model) scales it to large schemas. Works with OpenAI, Ollama or Anthropic.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| ask | table | NULL | NULL | |
| ask_sql | table | NULL | NULL | |
| create_model | scalar | NULL | NULL | |
| drop_model | scalar | NULL | NULL | |
| duckthink_metrics | table | NULL | NULL | |
| duckthink_metrics_reset | scalar | NULL | NULL | |
| llm_models | table | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| ask_dbt_scope_tags | Comma-separated dbt tags. When set (with ask_dbt_semantic_manifest), ASK() scopes to every model/source carrying any of these tags — so a service names its DOMAIN ('taxi') instead of listing tables in ON (…). Unioned with any explicit ON tables. Empty = off. | VARCHAR | GLOBAL | [] |
| ask_dbt_semantic_manifest | Path to a dbt Semantic Layer manifest (target/semantic_manifest.json) and/or a target/manifest.json. When set, ASK() grounds the prompt in the semantic models (entities, dimensions, measures), metric definitions, and column descriptions for the tables in scope — closing the text-to-SQL accuracy gap. Empty = off. | VARCHAR | GLOBAL | [] |
| ask_embed_model | Registered embedding model (CREATE MODEL, provider openai/ollama). When set, table selection runs in two stages: a cheap EMBEDDING recall ranks all candidates by cosine to the question (summaries embedded once and cached) down to ask_retrieve_k, then the LLM reranks. Scales to very large domains. Empty = LLM routing only. | VARCHAR | GLOBAL | [] |
| ask_max_retries | ASK() self-correction: how many times to feed a failing generated query's error back to the model before giving up (0 = one shot). Default 2. | BIGINT | GLOBAL | [] |
| ask_model | Registered model ASK uses to generate SQL (see CREATE MODEL) | VARCHAR | GLOBAL | [] |
| ask_retrieve_k | How many candidate tables the embedding recall keeps before the LLM rerank. Only used when ask_embed_model is set. Default 30. | BIGINT | GLOBAL | [] |
| ask_scope_max_tables | When a scope resolves to more than this many tables (e.g. a whole tagged domain), ASK() narrows it to the tables relevant to the question before building the prompt. 0 disables the step. Default 12. | BIGINT | GLOBAL | [] |
| duckthink_log | Emit a one-line stderr trace per LLM call (provider:model, tokens, ms). Off by default; metrics are always collected — see duckthink_metrics(). | BOOLEAN | GLOBAL | [] |
| llm_max_tokens | Max response tokens per LLM call (required by Anthropic; ignored by OpenAI/Ollama). | BIGINT | GLOBAL | [] |
| llm_rate_limit_wait | Seconds to keep waiting out HTTP 429 rate limits (honoring the server's Retry-After) before failing a call. 0 = fail on the first 429. | BIGINT | GLOBAL | [] |