Copy page

Recipes

Recipe templates

A recipe template is created in the interface, by promoting a recipe that already works. The API only reads templates: you cannot create, change or delete a template through it. What you can do through the API is deploy a template into a workspace - see Deploying recipes.

The template fields you read from the API - origin, categories, build_mode and output_columns - are described in Deploying recipes and are not repeated here. This page is about the other half: authoring a template so that it deploys cleanly, and writing step SQL that the engine accepts.

Authoring a template so that it deploys cleanly

Authoring is done in the interface, but the constraints are what determine whether the API path works later, so they belong here.

Preconditions and the silent failure to avoid

A template is created by promoting an existing working recipe. Three preconditions:

• the source recipe must have been run at least once; a recipe assembled from raw SQL and never run cannot be promoted;
• it must resolve to exactly one template lineage;
• the person promoting it needs edit rights on it. Promoting a recipe requires edit rights on that recipe, and a recipe deployed through the API does not grant those rights to everyone - check in the interface who can edit it before you plan on promoting it.

The silent one: when the template is created, the source recipe's dependencies are resolved by table-name shape. Sources that look like extracts or like other recipes are captured. Anything else - a manually uploaded table, an external view, a hand-written table reference - is dropped without warning. The template is created successfully, the copied SQL still names that table literally, and the table does not exist in any other workspace. Before promoting, confirm every source of the recipe is an extract or another recipe.

Because the memorized set is what gets provisioned, a template is only as portable as its sources.

Naming and lifecycle

Template content is immutable: the API only reads templates, and the transformation logic of an existing template is not edited in place. Consequences worth designing around from the start:

• the output table name of a recipe is fixed when that recipe is deployed. The title and the description of a template can be edited in the interface, but renaming a template changes the output table name only for recipes deployed after the rename - already deployed recipes keep the name they were given. Choose the title as if it were final (see Deploying recipes on how the name is derived and resolved);
• a new version of a report is a new template, so the catalog only grows;
• deprecation can only be expressed by convention.

Suggested convention: one template per (data source, report type, version), an explicit version marker in the title, a deprecation prefix that sorts predictably, and an owner-side registry that maps your product's concepts to template ids. The platform will not maintain this for you.

Writing recipe SQL that the engine accepts

Five constructs the step compiler rejects. Every one is reported as a missing table or column, so the message will point you at the wrong thing.

Never debug against the error message. Reduce the step to SELECT one_column FROM one_table, confirm it runs, then add one construct at a time.

1. Do not alias an aggregate onto its own source column. any(client) AS client shadows the source. Alias the source in an inner subquery first, then aggregate:

   Code
   SELECT key, any(client_src) AS client_name
   FROM (SELECT key, client AS client_src FROM source)
   GROUP BY key

2. Do not GROUP BY an alias of a computed expression. Compute the key in an inner subquery and group by the plain column.

3. Do not combine UNION ALL with an arrayJoin subquery. Each works alone; together they fail. Write the branches out explicitly.

4. Do not chain two LEFT JOINs in one step. One join per step, or nest the first in a subquery.

5. VALUES is not available as a table function in a step. For a static lookup, pack it into a delimited literal and expand it with arrayJoin and splitByChar.

There is also a complexity ceiling, and it reports identically. If every construct passes in isolation but the assembled step still fails, it is doing too much at once - split it across steps rather than hunting a syntax error that is not there.

Cross joins are not caught by anything. Nothing in the build path validates join keys, and a cross join without proper keys can exhaust resources. Assert your join cardinality yourself before promoting a recipe to a template that will be deployed across many workspaces - see Recipes across many workspaces.

One more, not a SQL rule: a step's saved SQL and its last executed SQL are tracked separately, so after a failed run the interface can show a failure while the stored SQL is correct.