Derive a table with Python

Who can do this

Authoring the workflow needs Build mode access (enabled per account by an administrator), or the Ronja CLI signed in to your organization. In your own private feature you create the Dynamic table, publish the workflow and create the automation yourself. In a shared feature, creating the table, committing the workflow and creating the automation all require Admin, or a maintainer of that feature.

A Derived table is defined by a query over its inputs, and Ronja rebuilds it whenever one of those inputs changes. That covers most transformations, and when it does, use it — see Create tables with Ronja.

Some calculations don’t fit in a query. This guide is the pattern for those: a Workflow does the work in Python and writes its result into a Dynamic table, and an Automation re-runs the workflow whenever the table it reads is rebuilt. The result behaves like a derived table — a table that keeps itself up to date — computed in code you control.

When to reach for this

The test is whether the calculation can be expressed as one query over the inputs.

A worked example that can’t: grouping companies that are the same company spelled differently. Acme AB looks like ACME AB., and ACME AB. looks like Acme Aktiebolag — but Acme AB and Acme Aktiebolag are not close enough to pair directly. All three are one company only once you follow the chain of similarities through, which means comparing every pair and then joining up the groups that overlap. Python does that in a few lines; a single query can’t say it at all.

Others of the same shape: a prediction from a model, a score that needs a library, a column enriched from an external API.

1. Create the table the workflow writes

A workflow writes into a Dynamic table — that is the kind a workflow’s output is. Ask Ronja to create it in the feature the data belongs to, or create it over the API.

A Dynamic table has no columns until something is written into it. That is fine for the table the workflow writes — its first run gives it its columns. It matters for a table the workflow reads: a Dynamic table nothing has been written to yet has no columns for a query to name. Either write the first rows into it before you point a workflow at it, or declare its columns on the create call over the API, where fields names the columns a query sees before the first write.

2. Write the workflow

The workflow reads the table you already have — the raw one — and writes the table you just created. You declare neither: Ronja binds both from the code, where the table being read is a {{ ref }} inside the query and the table being written is a {{ write }}. The workflow’s page then shows the raw table under Inputs and the new one under Writes to.

Ask Ronja for it in a Build-mode exploration (Create a workflow), or keep the Python in your own editor with the ronja wf loop (Use the Ronja CLI). From the terminal, ronja wf push reports what it bound, for example “Bindings: 1 input table, 1 output table” — that line is how you check the markers landed.

A test run writes to the real table

There is no test copy of a table. ronja wf test refuses to run a draft that writes one — “this test run writes to 1 live table … Re-run with --write-live if that is what you mean.” The run didn’t fail; it hasn’t started. Re-run it with --write-live when you mean to write, and read the output table afterwards to check the result.

When the table changes

The workflow’s write doesn’t change the table at the moment the code makes it. The table is written when the run finishes, or when the run pauses as Waiting, so:

• A run that fails before its tables are written changes nothing. The table keeps its previous rows. A run that pauses more than once writes at each pause, and a failure after a pause doesn’t undo what was written at that pause.
• Until then, a read in the same run doesn’t see what the run wrote. If the code needs the new rows again, it should keep them rather than read the table back.
• When a workflow writes several tables, they are written one after another. If one of them is refused part-way, the ones written before it stay written, and the run shows Failed with the reason.
• A table built from several of them normally rebuilds once, after the last one is written, rather than once per table.

Run it once, then publish it. A new workflow stays a draft until you do, so publish before you point an automation at it — and publishing is also what puts the workflow on the Data map, which draws published workflows.

With rows in it, the new table’s page carries the line “Built by workflow · inputs unchanged”, with the workflow’s name as a link, and turns amber when the table it read has moved on since. See When a workflow’s inputs move on.

3. Keep it fresh

Ask Ronja for an automation with a Table trigger, whose Watched tables is the raw table and whose action is the workflow. From then on, every time that raw table finishes rebuilding — including a push of rows into a Dynamic table — the workflow runs and rewrites its output. Nothing else has to be scheduled.

Watch the table the workflow reads, never the one it writes

An automation watching the output table would fire on the workflow’s own write, which writes it again, which fires the automation again. Always pick the input.

Ronja refuses to pair a Table trigger with a workflow that writes one of the tables that automation watches — but that check can only see the tables the workflow declares it writes, so treat it as a backstop, not as the rule. The rule is yours to follow.

A table trigger is capped at 50 runs per rolling 24 hours per automation, which is the loop-safety bound rather than a quota to plan around: an output that needs refreshing more often than that is a sign the chain is firing on something it shouldn’t. Field-by-field detail is in Automation triggers.

4. See it on the Data map

Open the Data map — from the sidebar’s Advanced → Data map if you’re an admin, or from Show in data map on either table.

The map opens grouped by feature, with every card closed, so nothing is on the canvas yet: click the card your tables live in to open it, or switch Group to Table to flatten everything into one table-level flow. (Show in data map on either table opens the right card for you.)

The workflow is then drawn as a chip between the two tables: a solid arrow from the raw table into it, a dotted arrow out of it into the table it writes. Hover the chip for its full name and how many tables it reads and writes; click its name to open the workflow. Before you build this, the new table sits on the map with nothing pointing at it — where its rows come from is exactly what the chip adds.

Selecting either table dims the map to that table’s pipeline, and the pipeline now runs through the workflow, so the raw table and the derived one are one chain rather than two unconnected nodes.

Related

• Create tables with Ronja — the ordinary path, for a calculation a query can express.
• Create a workflow — authoring, testing and committing the job itself.
• Schedule an automation — every trigger kind, and who hears when one breaks.
• Tables — the kinds, builds, lineage and the “Built by” line.
• Data map — the whole estate on one page.