transforms

name

transforms

description

Authoring transforms with transform_write — the query source (definition vs query_handle), the target table and what patching it renames, why the target database is never yours to choose, the two shapes the tool refuses (python, incremental), tags and folders. Read before your first transform_write. Triggers — "make a transform", "materialize this query into a table", "build a table other questions can query", "rename a transform's output table", "why won't it let me edit this transform".

Transforms

A transform is a saved query Metabase runs to materialize its results into a real warehouse table, which questions, other transforms, and anything reaching the database then query like any table. (A model is resolved at read time; a transform is a table that exists between runs.)

transform_write {"method": "create", "name": "Daily revenue",
                 "query_handle": "qh_…",
                 "target": {"name": "daily_revenue", "schema": "analytics"}}

Query source — exactly one of query_handle | definition

Preferred: author and run with execute_query (or execute_sql for native — fine here), then pass the returned query_handle; it saves exactly what ran. The alternative, inline definition, is the transform's source map, not a bare query — the shape get_content's "definition" include returns, so read-modify-write round-trips; the inner query is the execute_query dialect (learn("query-dialect")), numeric ids:

"definition": {"type": "query", "query": {"lib/type": "mbql/query", "stages": [...]}}

A bare query in definition is the common miss (older transforms stored it that way) and a teaching error.

Target table

target is {name, schema?} — the table the transform writes, recreated in full on every run.

• schema is required on databases that have schemas.
• On update target is patched, not replaced: passing only name renames the output table and keeps the schema — the supported rename.
• A new transform whose target table already exists is refused — it creates its table, never adopts one. Pick another name or schema.
• Never set target.database: the target follows the database the query reads; naming another is an error.
• target.type is always "table"; incremental types are configured in Metabase.

Refused, not degraded

Two shapes are rejected outright, because rewriting them would silently change how the transform loads: Python transforms (an update that would rewrite one is refused) and incremental loading — checkpoint (definition.source-incremental-strategy) and append/merge (target.target-incremental-strategy). Configure those in Metabase; the query of an incremental transform is still editable here as long as those keys stay out.

transform_write only authors: no run (saving doesn't execute), no archive, no delete (transforms have no trash — remove in Metabase). get_content on a transform reports source type, target, and latest run.

Folders, tags, permissions

• collection_id files it in a transform folder; omit for the top of the transforms tree.
• Transform folders are their own hierarchy — make one with collection_write(method: "create", namespace: "transforms"), and browse them with browse_collection(namespace: "transforms").
• tag_ids — jobs select transforms by tag, so tags are how a transform gets scheduled. Replaced wholesale: pass the full set, or [] to clear.
• Needs transforms permission on the source database plus the transforms feature, enforced exactly as the REST API and UI enforce them.

Don't

• Don't expect saving to run the transform — nothing executes until a job does.
• Don't pass a partial tag_ids — the list is replaced wholesale and the rest are dropped.
• Don't pass a partial target expecting a replacement — it is patched, so the old schema stays.
• Don't rely on rows surviving between runs — the table is recreated in full each time.