Dictionaries

A dictionary is a Warehouse lookup table that SQL queries on Lake tables fill, one query for each source system. It refreshes on a schedule, and you correct its values by hand in Data Mapping. Use it for a list that keeps itself up to date from your sources; if the values have no source, use a Static table instead.

The dictionary window

The window has four tabs: Dictionary, Preview, Dependants and Settings. The Dictionary tab holds six steps: General, Queries, Columns, Dependencies, RLS and Data Mapping.

Preview stays disabled until the dictionary is deployed; point at the tab to read Dictionary must be deployed to preview data. See Preview table data.
The footer carries Previous, Revert, Apply, Next and Finish. Apply and Revert appear when the step has changes, and the footer shows on the Dictionary tab only.
Moving on with a missing required field is blocked by Please complete all required fields before navigating, or Please complete all required fields before finishing on the last step, and a failed check shows a Validation Error toast with the reason.
Leaving a step or a tab with unsaved changes opens Unsaved Changes: "You have unsaved changes. How would you like to proceed?" Choose Save & Continue to save the step and move on, or Discard & Continue to drop the changes. A dictionary that is still being created can only save.
When a step fails, a bar above the content shows the message, and View details opens the full text.

Create a dictionary

Load the Lake tables your queries read first, and create a logical model and a source system if you do not have them: Lake, Semantic sources, source systems and logical models.
Click New in the tree toolbar and choose Dictionary, or pick Dictionary under Add New in the header.
On General, type a name and select at least one of Logical Models, then save. Saving this step creates the dictionary. The full settings are in Dictionary general settings.
On Queries, add one query for each source system and validate them. The rules are below, and the step is covered in Dictionary queries.
On Columns, add any columns you fill by hand; your queries fill the rest. See Define table columns.
Dependencies and RLS are optional.
Deploy the dictionary, then correct its values: Dictionary settings and Dictionary data mapping.

How the queries fill a dictionary

Each source system has its own query, and each source system can be used once. A dictionary always keeps at least one query.
Every query must return the same fields, with the same names. DataMind combines them into one list, and those fields become the dictionary's columns. To merge tables within one source system, use UNION DISTINCT in that source's query.
Every query must return a Source ID column: text, unique in each row, and no more than 15,000 rows per query.
Do not use -- comments in a dictionary query.
Validation counts the rows a query returns and the empty Source ID values in them. A repeated Source ID value or more than 15,000 rows fails the query.
Editing a query clears its Validated mark, so validate it again before you move on.
Saving changed queries rebuilds the dictionary's columns from the combined result. A column the queries no longer return is removed, together with the data in it. In a Warehouse Builder chat this change waits on an approval card that lists the columns at risk and shows the SQL before and after; see Change approval in a builder chat.

Messages you may see

Message	What it means	What to do
The Source ID field must be a String type. Use CAST or CONVERT	The Source ID column is not text.	Convert the value to text in the query.
"Source ID" must be unique for each row in the query result	Two rows share a Source ID value. Empty values count as one value.	Give each row its own value.
Too many records: the maximum allowed is 15,000	The query returns more rows than a dictionary accepts.	Narrow the query to 15,000 rows or fewer.
A Dictionary must have at least one query. Removing the last query is not allowed.	You tried to remove the only query.	Add another query first, then remove this one.
"SOURCE_ID" should be Unique for each "SOURCE_SYSTEM_ID"	The save found the same Source ID and Source System ID pair twice in the combined result.	Make each pair unique in the query, then save again.
Unknown dictionary source key(s): ... Each "Source ID" and "Source System ID" pair must exist in the dictionary main table.	A row was added with a Source ID and Source System ID pair that the dictionary does not hold.	Reuse a pair the dictionary already carries, then save again.

Who can do what

View Warehouse Objects lets you see dictionaries, their queries and their preview and statistics.
Add/Modify Warehouse Objects lets you create and change a dictionary, its queries, columns and mapping data, deploy, and control refreshes.

Without the right permission, the action is refused with a message that names the permission. See Warehouse permissions and row-level security.

Questions, answered

Do all my queries have to return the same fields?

Yes. Every query must return the same fields, with the same names, because DataMind combines them into one list and those fields become the dictionary's columns. To merge tables within one source system, use UNION DISTINCT in that source's query.

Why can't I remove the last query?

A dictionary always keeps at least one query; removing the only one is refused with "A Dictionary must have at least one query. Removing the last query is not allowed." Add another query first, then remove this one.

Why did a column disappear when I changed a query?

When you save changed queries, the dictionary rebuilds its columns from the combined result, and a column the new queries no longer return is removed, together with the data in it. Check which columns your SQL returns before you save. In a Warehouse Builder chat the change waits on an approval card that lists the columns at risk and shows the SQL before and after.

Why does every query need a Source ID column?

Source ID identifies each record, and transformations join on it later. The column must be text, and every row in a query needs its own value; empty values count as one value. If your value is a number or a date, convert it with CAST or CONVERT. A query may return at most 15,000 rows.

Which dictionaries cannot use Fill with AI?

Fill with AI, in the Data Mapping toolbar, is disabled for a table when any of its columns carries a PII domain; the request is refused with AI fill features are disabled for tables with PII columns. Fill those rows by hand instead. For how domains are set, see PII detection.

What happens to my changes if I leave a step without saving?

Unsaved Changes opens: "You have unsaved changes. How would you like to proceed?" Choose Save & Continue to save the step and move on, or Discard & Continue to drop the changes; while the dictionary is still being created, saving is the only way forward. If a required field is missing, the move is blocked with Please complete all required fields before navigating and you stay on the step.

What do the colours on the step bar mean?

The strip above the steps shows each step's state. A step that is saved and valid turns green; the step you are on is highlighted, and it shows an amber mark while it has unsaved changes and turns red when it still needs attention. Fix a red step before you continue.

Why can't I edit some cells in Data Mapping?

Columns that come from your queries are locked, because the queries refill them. The columns you added are editable, and the key column is editable only on rows you add. The full grid is in Dictionary data mapping.