# 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](/recipes).

The template fields you read from the API - `origin`, `categories`, `build_mode` and
`output_columns` - are described in [Deploying recipes](/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](/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:

   ```sql
   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 JOIN`s 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](/recipes-at-scale).

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.