AI search on large models

Before it answers, the DataSquares AI assistant reads the data model it works on: its tables, fields, relationships and measures, as much of it at once as its size allows. On a model with several hundred tables, on a server that turns it on, the assistant uses AI search: it searches a catalog of the model’s tables and fields for the ones your question needs, adds the tables that join them, and reads those in full.

Where the server turns on the schema finder, a model larger than the answer’s effort reads whole is walked for your question instead, with no catalog. Nothing changes for smaller models: the assistant reads them as before. Administration → AI lists the models AI search serves in your workspace.

How the assistant reads a model

The assistant reads a model within its AI scope. How much of it the assistant sees at once depends on the model’s size, and on how long the descriptions of its tables and fields are:

Up to about 100 to 120 fields, it reads the whole model at once: up to about 80 to 90 when the tables and fields have descriptions, and fewer when most tables have only one or two fields.
On a larger model, it first reads the fields of the first tables, about as many fields in all, and the names of a few more tables at most, so the other tables are not named: a model of a dozen tables can be that large. When the first table alone has more fields than that, it can start with no table at all. It then asks for the tables it needs by name, or searches the model’s tables and fields.

With the compact model listing

On a server that turns on the compact model listing, the assistant reads a compact listing instead: a line for each table with its description, then its columns with their types, keys and short descriptions, the relationships between tables, and the measures. It reads the whole model whenever the listing fits what the answer’s effort reads at once:

Effort	Reads a model whole up to about
Quick	900 columns, or 260 when every table and column has a description
Balanced	1,700 columns, or 500 with descriptions
Deep and Max	3,300 columns, or 1,000 with descriptions

A larger model is listed as far as that goes: first the tables your dashboards, saved queries, metrics and measures use most, then the tables most joined to others. The assistant is told how many tables are left, and asks for the ones it needs by name or searches for them; where the server turns it on, AI search finds the tables a question needs and lists them the same compact way, with the joins between them. With the compact listing, the overview of the model’s other tables, the check of a filter value and the examples that AI search otherwise adds are left out. The tables come before the measures: on a model with many measures, the assistant may read every table and only some of the measures, and is told so. Hidden tables and fields stay out, and a field labelled sensitive is listed only for the people who may read labelled values in clear.

At the start of a chat the assistant also sees a line for each data model of the workspace, the most used first, as many as fit: its name, its connection, its size and main tables, and its description, so it can tell which model a question is about. When a workspace has more, it is told how many more there are and can list them all.

What the assistant reads is part of the answer’s one charge: a whole listing uses more AI credits at a higher effort, and never more than that effort reads at once.

The schema finder

On a server that turns it on, and at the efforts it runs at, a model larger than the answer’s effort reads whole is not listed in part. Instead, a quick AI step, the schema finder, walks the model for your question in a few steps and hands the assistant only the part it needs. It needs no catalog: it reads your model’s own tables, relationships and descriptions.

Which model. When the workspace has more than one data model and the question names none, it picks the model from a line about each (its name, connection, size and main tables) and from what your question names: tables, columns, stored values and governed metrics, the models you used lately, and certified ones. When one model holds what the question names, it is picked straight away. When two fit equally, such as two connections that both have an orders table, the assistant asks you which one you mean: it asks only between models that each hold what you asked for. When no model holds it, such as headcount in a workspace with no people data, the assistant says so and tells you what your models do cover, instead of asking you to pick one. It says so straight away only when your question names nothing any model holds: when it names a table, a column, a stored value or a metric of one of your models, the assistant first looks at what it names with its usual tools, and if none of your models holds what you asked for, it says so the same way. On a very large model (more than about 3,500 tables) it first picks the part of the model the question is about: a database schema, or a group of tables that join each other. On a server with the new tool set, the assistant picks the model itself from your models, or asks you, and the finder walks that one.
Which tables. It reads a one-line overview of every table (its name, its description, its keys and a few of its columns: dates, amounts and codes) and picks the tables the question needs, and some it may need. A table your question names is always kept.
Which columns. It opens those tables: every column with its type and description, how many rows the table holds, and the codes the value hints keep.
How they join. The joins between them come from your model’s relationships and your saved SQL, with the tables that connect them. A join matched from values stays a possible join, with how sure the match is, to check before it is used. Two ways to join the same tables, and a join that would count rows twice, come back as questions for the assistant to settle from your question or to ask you. A join between two tables the others already connect, such as a customer’s home store beside the store of the sale, is given apart: the assistant uses it only where your question means it.
A few checks of your data, at Balanced and above: does a filter value exist and how is it stored, is a key unique, what dates a column covers, does a join repeat rows. Each runs as you and returns only counts, a date range or a few stored spellings, though your database may read a whole column to work them out; each is stopped after 10 seconds at most. They run only where you may query the table directly: never on a field labelled sensitive, never on a table with row-level security that applies to you, and only while value hints are on.

How far it goes follows the effort:

Effort	Steps	Checks of your data	It can be asked again
Quick	2	none	once
Balanced	3	1	twice
Deep and Max	6	3	three times

The assistant gets the tables the question needs, with why, their columns, the joins between them and how each is known, the stored values your question names spelt as stored, the measures and metrics to answer from, the questions only you can settle, and what no table seems to hold. When something is missing, it asks the schema finder again, as often as the effort allows. In a workspace with more than one data model, every answer it helped with ends with where it came from: Answered from: the connection and the model.

The schema finder sees only what you may see: the AI scope, fields labelled sensitive only if you may read labelled values in clear, never a hidden table, and a hidden field only as the key of a join between tables it shows. Descriptions, codes and values are read as data, never as instructions. When it cannot answer (it runs out of time, finds nothing, or the AI service is unavailable), the assistant reads the model as far as its effort reads whole and looks up the rest by name, and the answer goes on.

Where the server turns them on, the schema finder also keeps notes about your tables from the answers you were happy with, and starts later questions on those tables from them: the tables it opens show their notes, and a table with no description is summed up in the overview by a fact a check proved. The first question on tables it has no notes on looks further, as that page says.

Its steps are part of the answer’s one charge, in AI credits: the Usage page and the AI ledger count them in the answer’s own line. It needs no catalog. DataSquares keeps a record of what it gave each answer, the names of the tables and columns and never a value, for 30 days or until the conversation is deleted, to measure how well it finds them (privacy); and it keeps the value hints of the models it serves up to date. It is turned off with AI search under Administration → AI, and its checks of your data with Value hints.

When it runs

On a model of several hundred tables, roughly: how many depends on how many fields they have and how long their descriptions are. Leaving tables out of the AI scope makes a model smaller, and a model small enough is read the usual way again. On a model of up to about 500 tables the assistant also gets a one-line overview of the model’s other tables in the AI scope, so it sees what the search did not pick: the closest to what it found first, as many as fit beside the tables it reads in full (it can list the rest), once per question. On a larger model it searches within the model only, and Prep for AI suggests narrowing the AI scope.
In AI chat, and in what goes through it (Write with AI in a Story, the SQL editor’s Explain and Generate): when the assistant looks at the model’s tables to answer, and when it searches them.
In Prep for AI, where the server turns it on: Explore this model walks every table of a model in the AI scope with the schema finder, once a model editor starts it. It shows as its own line, Explore this model, on the Usage page.
Never on what is outside the AI scope. A table or field an editor left out is never found or offered by AI search, and the assistant’s own SQL and data look-ups don’t read it on the model’s data source when every model over that source leaves it out.

How it finds the tables

How much AI search does follows the answer’s effort.

At Quick:

It searches the catalog by meaning and by name, together. A question about “customer region” finds a column named cust_rgn_cd, and an exact table name finds that table.
It adds the tables your question names, exactly as they are named, and the tables whose stored values your question names (see value hints).
A quick AI step keeps the few tables and columns the question needs from what the search found.
It adds the tables that join them, and the joins. The joins come from the model’s relationships and from the joins your workspace’s saved SQL queries use. A table that no known join reaches is said to be unjoined, never joined by a guess. When two tables join in more than one way, such as an order’s date and its ship date to one calendar, the assistant is given each way on its own, never combined into one join: it works out from your question which one you mean, or asks you. Where your model’s relationships say which side of a join is the many side (each sale has one customer), a fact table’s own joins come first: sales by customer segment and store region join the sales to their customers and to their stores. A join between two tables those joins already connect, such as a customer’s home store beside the store of the sale, means something else: it is given apart, and the assistant uses it only where your question means it (sales by the customer’s home store), in place of the join it replaces. A join known only from saved SQL doesn’t say which side is many, so among such joins the fact table may not come first, and one of its own joins (the store of the sale) can be the one given apart, for the assistant to use where your question means it. Defining the relationships in your model avoids this.
One repair. When a query the assistant wrote fails, it is shown the database’s own error once, to put the query right. The other column names some databases suggest in their errors are left out: they could name a field outside the AI scope.

At Balanced, Deep and Max it does more:

A quick AI step rewrites your question into a few search phrases and the names your database most likely uses for them, written the way its tables are named (cust_nm for a customer’s name). Each is searched beside your question, and the results are ranked together.
It adds the tables between the ones it found, following the joins and ranking first the tables the found ones share. A table that joins almost everything, such as a calendar or a customer table, is added only where it is the one way two found tables connect.
A quick AI step keeps 7 to 10 tables, reading how they join (the join keys, and whether one row of a table meets one or many of the other), and sorts their columns into the ones the question needs and the ones it might need.
It adds the tables that join them, and every known join between them: the model’s relationships and the joins your saved SQL uses, each with how many rows of one table meet a row of the other and where it comes from. Joins matched from values come apart, as possible joins: the assistant checks one against your data with a small query, or asks you, before it writes a query on it. Where two tables join in more than one way, or two routes between them are as short (a returns table through four order headers), it takes none of them: your question says which, or it asks you. As at Quick, where your model’s relationships say which side of a join is many, a fact table’s own joins come first, and a join between two tables they already connect is given apart. A table your question needs settles a route (sales by customer by month joins the sales to both the customers and the calendar), and the joins never depend on the order the tables were found in. A way that joins on two columns at once in your saved SQL is used whole.
One check of a filter value. When the question filters on a value that no stored value confirms, the assistant runs one small query, at most 10 rows, for the stored values that contain it, so its filter matches what is stored. It runs as you, only where you may query the table directly, never on a field labelled sensitive or a table with row-level security that applies to you, and only while value hints are on. Like any query the assistant runs, it runs on your database.
One repair, as at Quick.

If a quick AI step can’t run, AI search does without it: the question alone is searched, or the search’s own order stands. If the search itself can’t run, the assistant reads the model the usual way (see When AI search can’t run).

The assistant then reads those tables in full. It can still look up any other table in the AI scope by name, and when it searches the model’s tables, the most relevant come first. At Balanced and above it can also ask for the ways two tables join (the shortest join paths, with each join’s keys and where it comes from, paths of known joins before ones through a match of values; a join matched from values marked as one to check first; where two tables join in more than one way, each way on its own). With the tables it finds, it also gets a few of the questions you marked as answered well that used them (two at Quick, three at Balanced and above), as examples; never another person’s, and none while your memory is paused.

Joins in the SQL the AI writes

On a data source with a model AI search serves, the SQL the assistant writes is checked before it runs:

A join with no condition is refused. Joining two tables without saying how they match would pair every row of one with every row of the other, however the join is written. The assistant is told the joins the workspace uses instead, and tries again.
A join the workspace doesn’t use is flagged. A join on columns that no relationship of the source’s models and no saved query uses still runs, and the assistant is told to check that it matches on a real key and doesn’t repeat rows before it trusts the numbers. At Balanced and above, a join only a match of values supports is flagged as one to check the same way, unless the assistant already checked it.

The AI catalog

What it holds. For each model in the workspace: the model, each table, field, relationship, SquareX measure and governed metric, by name (also split into words, so OrderDate reads as “order date”), with its description and its glossary synonyms. A measure or metric points to the tables it is built on, so a question that names it finds them.

What it never holds: your data. No rows and no values from your sources, and no measure’s or metric’s formula. A description that looks like a value, personal details, a secret or an instruction is left out of the catalog, and so is one too long to store whole. The questions you mark as answered well stay yours: they are never put in the catalog.

Who finds what. Everyone in the workspace who uses the AI searches it, and only for what they may see: a field labelled sensitive is found only by the people who may read labelled values in clear, and a table’s entry never names its labelled columns. Row-level security applies to the data the AI queries, as always.

Kept up to date. The catalog is built the first time a question needs it (that question still reads the model the usual way), then kept in step within minutes of every change: a model edited, re-synced, published or deleted, a measure, metric or glossary term changed, the AI scope changed.

Where it is kept. By the AI service DataSquares uses, apart for each workspace. It is deleted the same day a workspace or a company is deleted, and within minutes of an admin turning AI search off.

Value hints

People write “EMEA”, “ice tea” or “closed”; a column may store Emea, Ice Tea or CLS. Value hints keep the stored values of your text fields, up to 1,000 a field, such as regions, statuses or product names, so a filter the assistant writes matches your data.

Kept in DataSquares only. Value hints are never put in the AI catalog. The assistant is shown the ones your question names. Where the schema finder runs, the tables it opens also show the AI every value of each short code list in them (a field of up to 20 different values), whatever your question names.
The most frequent values: every value of a text field with at most 1,000 different values, or the 1,000 most frequent of a field with more, each with how many rows hold it. A value longer than 200 characters is left out.
Near spellings too: “ice tea” finds Iced Tea, as well as an exact match.
Never from a field labelled sensitive, a field that reads one (a second field over a labelled column, or one over a column computed from it: masking treats it as that column), a hidden field, a field outside the AI scope, or a table that any row-level security policy covers. A value that looks like personal details, a secret or an instruction is left out.
Read from your source weekly, with one small query per field, on the models AI search or the schema finder serves. These queries run on your database.
Turn them off under Administration → AI → AI search on large models → Value hints. Turning them off deletes the values kept, and the key values joins are matched from, and stops the weekly queries.

Joins matched from values

Warehouses rarely declare their keys, so at Balanced and above AI search also finds possible joins in your data: two columns whose values match, such as a support ticket’s cst_no and a customer table’s cust_no.

A match is a possible join, never one the assistant relies on as it is. Values can match by chance: a table numbered from 1 with an “Unknown” row at 9999, or numbering that starts again at 1,000, holds the small numbers of many unrelated columns. So AI search uses matches to find the tables a question needs, and shows them to the assistant as possible joins, each with how sure the match is. Before it writes a query on one, the assistant checks it against your data with a small query of a few rows (is the one side’s key unique, and are the other side’s values found in it), or asks you. Matches never join tables on their own, at any effort, and two columns a relationship of your model or your saved SQL already joins are never shown as a possible join too: the assistant gets the known join.

From value hints: a column every value of which is a key of another table’s short code list (a field of at most 100 different values). A column whose codes more than five such lists hold, such as a few status letters, is not matched: its codes say nothing about which table it points to.
From key values: for each column named like a key (an id, a number, a key, a code or a reference; never a person’s own number, such as a phone, card or tax number) of the models AI search serves, DataSquares reads how many values it holds and its 100 smallest different values from your source, weekly, with two small queries per column. Keys can be whole numbers, of any number type (such as Snowflake’s or Oracle’s NUMBER), or text. A column whose values lie among another table’s unique key is matched to it. A column any of whose values looks like personal details or a secret keeps none, and so does a number column holding fractions, such as an amount or a rate.
Numbering needs names that agree: a key numbered 1, 2, 3 … holds any column of small numbers, even with a few rows deleted or an “Unknown” row numbered 0 or -1. A column is matched to such a key only where their names agree (cst_no and cust_no), and a key that columns of more than five different names would match, their names agreeing with it in none, is matched to none of them. Some numbering still looks like something else (an “Unknown” row at 9999, numbering in stretches), which is why every match stays a possible join.
Kept in DataSquares only, like value hints: never put in the AI catalog, and no value is shown to the AI. A match names only the two columns and how sure it is. A source keeps up to 20,000 matches, the surest.
The same rules and switch as value hints: never from a field labelled sensitive, a hidden field, a field outside the AI scope, or a table that any row-level security policy covers. Turning value hints off deletes the key values and stops the queries.

Prep for AI lists every join with where it comes from, so you can declare the ones you rely on as relationships: the assistant then uses them without checking first.

Search by meaning

Search by meaning is on by default: it finds a table or field from what a question means, not only its words, which matters most when names are short codes. Turn it off under Administration → AI → AI search on large models, and the catalog is searched by words and names only.

AI credits

AI search is paid for in AI credits, like every AI feature, and shows as AI search on Administration → Usage and in the AI ledger.

Building and updating the catalog. Building a workspace’s catalog uses AI credits once; after that, only what changed is sent again, and a document whose text didn’t change costs nothing.
Searches during an answer are part of that answer’s one charge, with the quick AI steps that rewrite the question and keep the tables.
Storing the catalog is included up to 100,000 documents a workspace (a document is the model itself, or one of its tables, fields, relationships, measures or metrics; a very wide table, or a measure built on a great many tables, takes a few). A model of 500 tables with 20 fields each is about 10,500 documents. Above that, storage is 1.5 AI credits per 1,000 documents a month, charged a day at a time. Administration → AI shows the documents your catalog holds and what they use a month, in whole credits.

See How AI credits are counted.

When AI search can’t run

When the AI search service is unavailable, the catalog isn’t built yet, or AI search is off, the assistant reads the model the usual way and looks up the tables it needs by name. Answers still work; on a very large model the assistant may need more steps, and may miss a table it would have found.

Administration → AI says when the catalog is waiting, and why: the workspace is out of AI credits, its monthly AI budget is used up, the service is unavailable, or AI search is not set up for the workspace yet.

Turn it off

A company administrator turns AI search off under Administration → AI → Features → AI search. Off, the workspace’s catalog is deleted within minutes and the assistant reads every model the usual way, using no credits for AI search. See AI controls.

The AI scope — choose the tables and fields the AI may use.
Prep for AI — a model’s readiness for the AI, its drafted descriptions and its joins.
AI controls — turn AI features on or off, budgets and the ledger.
How AI credits are counted — counting and rounding.
How the AI uses your data — what the AI is sent and what DataSquares keeps.