Data & analytics

Data preparation

Raw tables rarely answer a question on their own. Prep joins them, fixes the columns and saves the whole recipe so tomorrow's data goes through the same steps.

Find it under Data → Data Catalog → Data preparation. The output is a new prepared table that behaves like any other: chart it, query it, attach it to an agent.

Why it works this way

The point of prep is not the transformation — you could do that in SQL — it's repeatability. A saved flow re-runs the same joins and casts against refreshed source data, so a dashboard doesn't quietly rot when next month's file has a differently named column.

The canvas

The left palette lists what you can bring in, split by where it lives so you always know whether you're touching a local copy or a live system:

Local tables — uploads and previously prepared tables, already in the workspace.
External tables — connected warehouses and databases, expanded as schema.table so you can see exactly which object you're picking. Clicking one brings a capped snapshot onto the canvas to design against, rather than pulling millions of rows into the browser.

Drop a table to make it the base, then add more and connect them. Every step shows a live preview of the resulting rows, so you find a broken join immediately rather than at the end.

orders ──┐
         ├── join (customer_id) ──▶ filter ──▶ computed column ──▶ prepared table
customers┘                          status=       margin =
                                    'shipped'     revenue - cost

A flow is a base table plus an ordered list of steps.

Joins

Pick the two key columns and the join type. The four types, in plain terms:

Inner: Only rows that matched on both sides. Loses unmatched rows silently — check your row count after.
Left: Every row from the base table; missing right-hand values become null. The safe default when the base table is your unit of analysis.
Right: The mirror of left. Usually clearer to swap the tables and use a left join instead.
Full: Everything from both sides. Useful for reconciliation — finding what exists on one side only.

Watch the row count

If joining doubles your rows, the key isn't unique on one side and you're now double-counting every measure downstream. The preview's row count is the cheapest way to catch this.

Column types

Every column carries a type, set on import and changeable in the prep canvas. The type decides which filters, aggregates and charts are available downstream, so getting it right here saves debugging later.

Type	Use for
`text`	Free text
`integer`	Whole numbers
`decimal`	Fractional numbers
`date`	Dates — required for date filters and time-series charts
`boolean`	True/false
`location`	Place names or codes — enables map charts
`category`	A small set of repeating values; the natural grouping dimension
`currency`	Money — formats as currency downstream
`percent`	Rates and shares
`id`	Identifiers — excluded from aggregation suggestions, since summing an id is meaningless

The nine steps

Steps apply in order. Any one can be removed, and the preview updates as you go.

Step	What it does	Configure
Calculated field `calc`	Add a column from a formula	Name, expression, and the resulting column type
Filter rows `filter`	Keep only rows that match	One or more conditions, combined with `AND` or `OR`
Summarize `aggregate`	Group by and roll up	Group-by columns plus measures (Sum, Average, Count rows, Count distinct, Minimum, Maximum)
Append rows `append`	Union rows from another dataset	Source table, columns to keep, and mode `all` or `distinct`
Pivot `pivot`	Turn row values into columns	The column to spread, and the value to fill with
Unpivot `unpivot`	Turn columns into rows (wide → long)	Which columns to melt, and names for the key/value columns
Split column `split`	Split text into multiple columns	Source column and delimiter
Remove duplicates `dedupe`	Drop duplicate rows	Which columns define a duplicate
Find & replace `replace`	Replace values in a column	Column, match and replacement

Writing a calculated field

The expression is SQL, not a spreadsheet formula language. It is dropped into a SELECT as (your expression) AS your_column_name, so anything valid in a SQL select list works — arithmetic, functions, CASE, references to any column available at that point in the flow.

sql

-- arithmetic across columns
revenue - cost

-- a rate, guarding the divide-by-zero that would otherwise produce nulls
CASE WHEN visits > 0 THEN conversions * 1.0 / visits ELSE 0 END

-- bucketing, for grouping later in the flow
CASE
  WHEN amount >= 10000 THEN 'enterprise'
  WHEN amount >= 1000  THEN 'mid-market'
  ELSE 'smb'
END

-- text tidying
lower(trim(email))

-- a month key to summarise by
date_trunc('month', ordered_at)

The dialect is DuckDB, everywhere

Local datasets run on DuckDB both in the browser preview and on the server for scheduled refreshes — deliberately the same engine, so what you see in the preview is what the schedule produces. That means DuckDB's function library is available to you: date_trunc, strftime, regexp_extract, list_aggregate, window functions, and the rest.

Order matters, and so does naming

Steps run in sequence, so a calculated field can only reference columns that exist above it — including earlier calculated fields, which is the intended way to build something up in readable pieces. Give each one a name you would be happy to see on a chart axis: it becomes a real column that dashboards, metrics and agents all address by that name, and renaming it later breaks whatever already points at it.

Filter operators

Beyond the usual comparisons, the text and null operators are the ones people look for: contains, starts with, ends with, is empty and is not empty.

is empty is not the same as equals blank

A column can hold a genuine empty string or a null, and they filter differently. If a filter returns fewer rows than expected, check which of the two your data actually contains — the column profile in the catalog shows the null rate.

Saving and refreshing

1
Save the flow
The recipe is stored — sources, joins, steps — not just the output.
2
Run it
Produces (or replaces) the prepared table. It appears in the catalog marked as prepared.
3
Schedule it
Set a refresh cadence so the prepared table is rebuilt from current source data. Anything built on it — dashboards, agents, metrics — updates with it.

Lineage

A prepared table records what it was built from, visible in the catalog. Before deleting or restructuring a source table, check what depends on it there.

When not to use prep

A one-off answer — just write the query in the SQL workbench.
Logic your organisation must agree on — a shared definition of "active customer" belongs in the Semantic Layer, where it is defined once and reused, rather than baked into one prepared table.
Heavy transformation over very large tables — push that down to the warehouse and connect the result.

PreviousData Catalog & SQL Next Knowledge Base