Text-to-SQL: automatic SQL generation from text

Text-to-SQL: Where Data Access Breaks Down

In a typical company, an analyst handles dozens of ad hoc questions per week: "how many customers returned in this segment," "what is the conversion rate by channel for the quarter." Each such question means manual SQL queries: find the right table, remember how activity is calculated, remember to exclude test accounts. The queue grows faster than the team, and decisions based on numbers are delayed by days.

Even more costly than delays is disagreement in numbers. Two analysts describe the same metric differently: one filters by payment date, the second by order creation date, the third does not remove internal accounts.

In a meeting, it turns out that the marketing report and the finance export have mismatched values, and half the meeting is spent determining whose query is "correct."

This is exactly where the analytics bottleneck lies: data access is limited not by storage capacity but by the manual translation of a question into query language.

As long as a person does this, speed is limited by their workload, and correctness by whether they remember all metric agreements.

Text-to-SQL removes this layer: a natural-language question is turned into a query against a pre-described data model, where metrics, table relationships, and mandatory filters are fixed once.

Generating SQL from text does not replace the analyst — it removes queue waiting for typical questions and eliminates inconsistency in definitions.

We look at how this is addressed engineering-wise: what we describe in the semantic layer, how we verify the correctness of generated queries, and at which points we keep a human in the loop.

What Changes in the Analytics Team's Work

Previously, the cycle looked like this: a data question went into the analyst's queue, the analyst clarified the wording, wrote the query, and returned the result — that is hours of work, and when the team was busy, the answer arrived the next day.

Text-to-SQL changes the scale: the question is phrased in words in a familiar interface, the query is assembled from the schema description, and numbers are returned immediately.

The main metric is the share of questions resolved without an engineer's involvement.

Typical slices (revenue by channel, activity by cohort, conversion by funnel step) stop occupying the queue, and only tasks with complex logic remain for the team: non-standard table joins, window functions, calculations across multiple sources.

Time to answer a question moves from the "hours and days" range into the "minutes" range — this is what analytics acceleration means: it is about waiting disappearing, not about typing speed. Reducing the load on analysts does not mean their work has been handed over to a machine.

A separate effect is that an entire class of manual query errors disappears: a forgotten period filter, an incorrectly chosen join type, row duplicates on a many-to-many relationship, and mismatched conditions between reports.

Generation is based on a single schema description, so two people who ask the same question in different words get comparable numbers.

Self-service analytics becomes the norm for adjacent teams, and the analyst works on the data model rather than rewriting similar queries for each requester.

Text-to-SQL: Implementation Options and Their Limitations

The choice of option is limited not by model quality but by schema design and access requirements. The same query generation behaves differently on a dozen tables with clear names versus several hundred with legacy names, views, and partitions.

In practice, the choice comes down to four alternatives — from a ready-made service to an in-house model on the data schema.

Limitations Common to All Options

Generation checks syntax and schema compliance but not the meaning of the question — therefore any environment retains read-only access, a row limit, and a preview of the query before execution.

If the schema changes more often than once a quarter, choose an option with automatic metadata reconciliation: otherwise queries will start failing on renamed fields. Let's look at which option fits your schema.

Implementation Stages: From Schema Audit to Handover

Implementing text-to-sql starts not with the model but with the data schema. Until the metric dictionary is described — which indicators are calculated and from which tables — query generation produces plausible but incorrect results. Below is the sequence in which risks are removed before production operation.

1. Data schema audit: tables, relationships, types, duplicates, and "junk" legacy fields, views from reporting. The output is an entity map and a list of places requiring renames or computed columns.
2. Metric dictionary: definitions of revenue, active customer, conversion — from which tables and with which filters. Synonyms ("client," "customer," "account") are reduced to one concept.
3. Generation setup: the schema and dictionary become context for the model, plus constraints — read-only, mandatory period filter, query size limit.
4. Acceptance on historical questions: we take real questions from analysts and managers, compare generated queries with reference answers. Discrepancies refine the dictionary, not the wording of the model request.
5. Access separation: roles inherit source permissions, and the user does not see data they cannot access. Every query is logged — what was asked and what was returned.
6. Handover: dictionary documentation, deployment diagram, set of reference questions, and update procedure for changes in the source.

Implementation Artifacts: Schemas, Access Policies, Procedures

After implementation, the customer retains a set of artifacts that allows the system to be operated and developed without the author. Without it, further development starts with reverse engineering, and handing tasks to another contractor costs weeks of downtime.

• Data schema description. Tables, relationships, keys, field purposes, and notes about those that cannot be used in generation: legacy duplicates, technical and computed columns.
• Business terms layer. Mapping "what the customer calls an entity" → specific fields and tables. This is what enables text-to-SQL, so it is updated with every schema change.
• Configurations and environments. Connection parameters, limits on query size and execution time, access separation between environments, logging settings.
• Access policies. Which roles see which schemas and columns, row-level restrictions, personal data masking rules. Permissions are checked before generation, not after.
• Reference set of questions with verified SQL. It is used to run regression after every change: the share of correct queries is known before rollout, not from user complaints.
• Query handling procedure. How to phrase a question, who verifies the result, what to do when generation is incorrect, and how feedback returns to the terms layer.
• Support procedures. Update order during schema migration, regression run frequency, configuration versioning, and rollback to the previous version.
• Audit log and instructions. Query history with author and result, guides for users and administrators.

Case Study: Relieving Analysts and Increasing Trust in Data

A retail chain with a team of five analysts: product managers and business unit heads requested revenue by category, funnel, inventory, and churn every day. The queue of similar requests grew faster than the team — a typical answer took one to two days.

What It Was Like

Worse still: the same metrics were calculated differently — some used order date, some shipment date, some subtracted returns. A manager saw two numbers for one indicator and stopped trusting both. The cause was not storage quality but the absence of a single metric definition.

What We Changed

We described the semantic layer in dbt: each metric got one definition, an owner, and a filtering rule. Generating SQL from text works on top of this layer — the model sees only described entities and cannot invent a field or bypass a filter.

The business resolves simple questions itself, while disputed ones go to the analyst along with a query draft. The generated query is additionally run against the reference set — a discrepancy is visible before the number reaches a report.

Analysts switched to modeling and root-cause analysis instead of exports, and the numbers in reports began to reconcile. Relieving the team is not the goal here: as long as a metric is defined once, the answer to a question is reproducible on any day and by any employee.

What to Do If Some Data Already Lives in Ready-Made Marts

Ready-made marts and query generation solve different tasks, and one should not be substituted for the other.

A mart answers an already known question predictably and quickly, while the generator covers what cannot be listed in advance: arbitrary slices, composite filters, one-off research. The question is not "either-or" but where to draw the boundary between them.

The boundary runs through the data catalog. We describe each mart: which entities and metrics it covers, for what period, and how often it is updated.

The generator receives the question text, first looks for a suitable mart in the catalog, and only on a miss builds a query against the primary warehouse tables. This way, typical questions go into a predictable read plan and do not touch heavy sources.

Metric discrepancies arise where the definition of an indicator lives in two places.

This is treated with a single layer of definitions: revenue, active customer, and return are calculated once in transformations — we maintain them in dbt — and marts are assembled from the same models that the generator accesses. Then "the same number from a different query" stops being a surprise.

We set up control via regression checks: a set of reference questions is run through the generator, and a discrepancy with the mart beyond tolerance fails the build.

We also record freshness separately: the mart is updated on a schedule, while a query on top of the warehouse sees data at the moment of access — the user must understand exactly what they are looking at.

Technical Doubts: Accuracy, Security, Support

Engineering doubts around translating a question into a query are tested before the pilot, not after: accuracy, data permissions, and the deployment environment are tested on the real schema. Below are answers to what technical leaders most often ask about.

What happens if the query is still incorrect?

The query runs in read-only mode, with a row and execution time limit, so an error does not reach data changes.

With ambiguous wording, the system asks a clarifying question instead of guessing, and the "question — query — result" pair goes into the log and becomes material for refining the descriptions.

How is access to data separated?

The query runs under the user's identity, not a service account with full access, so table- and row-level restrictions continue to apply. A business unit head and an analyst will get different slices for the same question.

Does the solution work in a closed environment?

Yes: the model, database, and logs remain inside your perimeter; neither the question text nor the schema description leaves. Updating table and relationship descriptions follows a procedure — new entities enter the layer before anyone starts asking about them.

Let's Discuss the Task and Come Back with an Estimate and Plan

To make the estimate for implementing text-to-sql concrete rather than market average, it is worth gathering several inputs for the conversation.

The team working with the database usually already has all of this — access to this data and half an hour to collect it will be needed.

• Database schema: tables, relationships, and views that queries should target. It immediately shows where synonyms and a business terms dictionary are needed.
• 20–50 real data questions — in the wording used by analysts and managers, together with reference answers.
• Complex cases: period aggregates, comparison with a previous interval, calculation of shares and cumulative totals.
• Access permissions: which roles and departments should see which rows and columns.
• Load limits and the mode of working with the production database: read-only, a separate copy, a window for heavy queries.
• Where the result goes — report, chat, export — and in what form users expect it.
• How we verify accuracy: the share of questions the system must answer correctly.

From these inputs, a work plan comes together: a terms dictionary, an access perimeter, a set of checks, and implementation stages with checkpoints. Describe the task — we will come back with solution options, an estimate, and a work plan.