Recipes & Steps

A recipe is the definition of a prepared table: one primary input and an ordered list of steps. It compiles to a single SELECT statement, with each step becoming one stage of it.

WITH _prep_input AS (SELECT * FROM <your primary input>),
     s1 AS (<step 1>),
     s2 AS (<step 2>)
SELECT * FROM s2

The primary input

The primary input is the table a recipe is mainly about. It is either a raw table or another prepared table.

In the editor it is the Input control at the top of the Recipe step. It shows what the recipe reads today, and opening it offers three ways to change that: browse the warehouse tables, pick one of the workspace’s other prepared tables, or enter manually a database, schema and table. Prefer the browsers — identifier case belongs to the warehouse, and a name picked from a listing is always spelled the way the warehouse holds it. The manual path is there for a table your warehouse credential can select from but not list.

It is declared separately from the steps, rather than being just another thing a step reads, because it is the only relation Zeotap windows when a table is built incrementally. Everything else a recipe reads — a lookup you join, extra inputs you union — is a side input, and is read whole on every build.

That distinction matters when you write a lookup join: the primary input is windowed and the lookup is not. If it is the lookup that changes and you need that windowed, the lookup should be your primary input.

A primary input is optional for a table that rebuilds in full, and required for an incremental one.

Referring to other tables

Steps refer to other relations through tokens, which Zeotap resolves for you:

Tokens are also what give Zeotap the dependency graph between prepared tables — which is how a chain builds in the right order, how a cycle is refused, and how deleting a table that something else reads is refused rather than silently breaking it. Naming a table literally in a SQL step still works; it is just invisible to all of that.

Steps

Every step is a single SELECT. A step is either typed — you fill in a form and Zeotap writes the SQL for your warehouse — or a SQL step, where you write the SELECT yourself.

A recipe can mix the two freely.

The editor lists the steps in the order they run. Each card carries a one-line summary of what that step does; opening one shows its form, and Preview through this step runs the recipe as far as it — in the panel beside the steps, so the rows a step produces stay next to the controls that produced them.

Inside a SQL step, Insert table or column opens the same browser the Input control uses and splices a {{source(...)}} or {{ref(...)}} token at the cursor.

Why typed steps are worth preferring

A typed step is a set of fields Zeotap understands. A SQL step is a string it can only pass through. Three things follow, and one enabled SQL step anywhere in the recipe costs you all three:

1. Exact column lineage, which is what makes personal-data handling a fact rather than a guess. Zeotap knows full_name came from first_name and last_name, so it knows full_name inherits their sensitivity. A column whose path crosses a SQL step is reported as unverified, and an unverified column is masked rather than assumed clean — losing the lineage never loses the protection. (A preview adds a measured backstop on top of that; see Permissions & Grants.)
2. A real incremental-safety check instead of a warning. See Incremental Builds.
3. Portability. The same recipe runs on every supported warehouse, which is what makes blueprints and moving recipes between workspaces possible.

Good reasons to reach for a SQL step anyway: a window function that is not “latest per key”, a CASE across several columns, a set operation other than append, a correlated subquery, or a function your warehouse has and no other does.

Column names and case

Three rules, and they are not the same rule: what a recipe reads, what the built table is called, and how you name a raw table.

Columns that already exist are referred to by the spelling your warehouse reports for them. Everywhere a recipe or its build settings name a column, the control is a picker over the columns Zeotap knows that step or table has, with the warehouse’s own spelling and the column’s type beside it. You can always type a name instead — a column downstream of a SQL step is one Zeotap cannot see, and a value the list does not contain is kept and marked not in this step’s input rather than replaced. A name you type is matched against the real column list case-insensitively, so you can write it in whichever case you have to hand. Two columns differing only in case are refused by name rather than guessed between.

The lists fill in on their own. The input’s columns are known as soon as you choose an input, and a later step’s as soon as the step above it compiles — you do not have to press Validate to make a picker work.

The columns of the table you build take the case your warehouse gives a new column, whether you aliased them or passed them straight through. On Snowflake a prepared table’s columns are always upper case — CONTACT_ID — even when the table it reads holds contact_id; on BigQuery, Databricks and ClickHouse they keep exactly the names your recipe produced. That is what the built table will really be called, so it is what you will see in the model, in an audience filter, and in your own warehouse.

Step reference

Types are always drawn from the same list: integer, float, decimal, boolean, string, date, timestamp.

select_columns — project or drop

Use one or the other, never both. Dropping every column is refused.

{ "kind": "select_columns", "params": {
  "columns": [{ "column": "id", "as": "contact_id" }, { "column": "email" }, { "column": "_ss_loaded_at" }] } }

cast — type a column

casts is a list of {column, to, format?, on_error?, as?, precision?, scale?}.

Naming the same column twice in one casts list is refused. A cast replaces its column in place, so one input column can only leave the step as one output column — the second rule would silently win and the first one’s output would never exist.

epoch_seconds and epoch_millis are only meaningful for timestamp and date.

{ "kind": "cast", "params": { "casts": [
  { "column": "id", "to": "integer", "as": "contact_id" },
  { "column": "lifetime_value", "to": "decimal", "precision": 18, "scale": 2 },
  { "column": "last_seen_ms", "to": "timestamp", "format": "epoch_millis", "as": "last_seen_at" } ] } }

flatten_json — pull values out of a payload column

A path containing [] is refused, with a pointer to explode_array: crossing an array means many values per row, which flattening cannot produce.

explode_array — one row per element

A scalar string element reads the same on every warehouse — unquoted, as the value itself rather than as the JSON that carried it.

normalize — string hygiene

ops is a list of {column, op, values?, country_code?, as?}, where op is one of trim, lower, upper, trim_lower, email, phone_e164, null_if.

Every operation is a string function, so a non-text column is refused with a note to cast it first. values belongs to null_if alone and is compared case-insensitively against the trimmed value, so N/A, n/a and N/A are one rule. country_code belongs to phone_e164 alone — digits only, no +; without it a bare national number comes back as digits with no + rather than asserting a country nobody named.

derive — an expression the other kinds cannot hold

columns is a list of {as, expression, type?}. The expression is a scalar SQL fragment over the step’s own columns, checked conservatively: no statement separators, no comments and no statement keywords. If you need a subquery, you want a join step.

Declare type whenever you can. Without it the column’s family is unknown, so a later normalize or aggregate over it cannot be validated and its lineage is weaker.

derive is the escape hatch inside the typed world: it keeps a recipe fully typed, so the incremental-safety checks stay checks rather than warnings. Prefer it to a whole SQL step.

filter — keep rows

Either a filter tree or a boolean expression, never both.

The tree uses the same grammar and the same operators as the audience builder, compiled by the same code, so an operator behaves identically in both places — but property conditions only. An event, relation, computed-attribute or audience condition resolves against the modelling layer, which does not exist at this point in the pipeline (the models are usually defined over the table you are building), so those are refused by name with a pointer to a join step.

Prefer the tree: it is data Zeotap can read, where the expression is a fragment it can only check for the obviously dangerous.

dedupe_latest — keep the latest row per key

Both are required: without order_by there is no defensible “latest” and the step would keep an arbitrary row. There is no tiebreak — if two rows tie on every order_by column, the surviving one is whichever the warehouse picked, so order by something that discriminates, usually a business timestamp plus _ss_loaded_at.

join — a lookup

left is the default because an inner join whose lookup is missing a row removes that row from the output, which reads as data loss rather than as a join choice. Right and full joins do not exist here: both can produce a row with no input row behind it, which breaks lineage back to the primary input and makes an incremental window meaningless.

Omitting select projects everything from both sides and is refused on a name collision rather than resolved by a rule, because every such rule is wrong half the time.

union — append more inputs

By name, always: a positional union silently swaps two columns of the same type the day somebody reorders a projection upstream, which is exactly the change a prepared table exists to absorb.

aggregate — group and measure

A grand total is a legitimate query and a terrible prepared table — one row, no key to merge on and nothing downstream can join to it — so group_by cannot be empty. Every function but count needs a column. first and last need an order_by (that is what they are first by), and nothing else may carry one.

pivot — values into columns

keys is explicit and cannot be discovered: a prepared table’s schema is a fact Zeotap records, not something that changes with the data on every build. prefix exists because a key called id would otherwise pivot into a column that collides with a group_by column of the same name.

sql — the escape hatch

One SELECT (it may start with WITH), reading {{input}}. Everything above about lineage, incremental safety and portability is the cost.

SELECT *, SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_time) AS session_no
FROM {{input}}

Statement separators are refused, and so is anything but a SELECT — a step can never write to your warehouse.

Validating and previewing

Validate compiles the recipe and tells you what it will produce: the output columns and their types, the lineage of each one, a one-line summary of each step, per-step errors with the field that caused them, any warnings, and — on BigQuery, which is the one supported warehouse that can price a query before running it — how many bytes a build would scan.

Preview runs the compiled query with a small limit and shows the rows, with masked columns masked exactly as a model preview masks them. You can preview through any step, which is how you find the step that changed something unexpectedly.

Next steps

• Incremental Builds
• Data Tests & Reconcile
• Profiling & Suggestions