Skip to content

Derive a table with Python

A Derived table is defined by a query over its inputs, and Ronja rebuilds it whenever an input changes. When a query can express the calculation, use one: see Create tables with Ronja.

For a calculation a query can’t express, a Workflow does the work in Python and writes the result into a Dynamic table, and an Automation re-runs it whenever the table it reads is rebuilt. The result keeps itself up to date like a Derived table, computed in code you control.

Use it when the calculation can’t be written as one query over the inputs. For example, grouping companies that are one company spelled differently: Acme AB looks like ACME AB., and ACME AB. like Acme Aktiebolag, but Acme AB and Acme Aktiebolag are too far apart to pair directly. Finding the group means comparing every pair and joining the groups that overlap, a few lines of Python that a single query can’t say.

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

A workflow writes into a Dynamic table. 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, which gets its columns on the first run. For a Dynamic table the workflow reads, write the first rows before you point a workflow at it, or name its columns when you create it over the API (Push rows).

The workflow reads the raw table and writes the table you just created. Ronja attaches both from the code: the table read is a {{ ref }} inside the query, and the table written is a {{ write }}. The workflow’s page then shows the raw table under Inputs and the new one under Table outputs.

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 (Work on Ronja from your editor). ronja wf push reports what it attached, for example “Bindings: 1 input table, 1 output table”, so you can check the markers landed.

The table changes the moment the workflow’s code writes it, not when the run ends:

  • The rest of the run sees the write. A later step that reads the table gets the new rows, and a workflow can write the same table more than once in a run.
  • A run that fails or is cancelled keeps what it wrote before it stopped. Its page lists the tables it wrote under Produces. Running the workflow again writes them again, so rows it adds are added a second time; to undo a write, restore an earlier version of the table.
  • A run that waits and then carries on doesn’t write twice. A run that paused as Waiting, or a failed run resumed from where it stopped, skips a write it already made.
  • Tables built from it rebuild once per stretch of the run, when the run finishes, fails or starts waiting, over everything it wrote. Its Automations and health checks hear about it then too.
  • Changing a few rows doesn’t need the whole table. Ronja can write a workflow that changes rows in place with SQL, which rewrites only the data that changed.
  • Keeping a table in step with another system is one write. A workflow can pass the rows it fetched into that SQL write, which adds, updates and deletes rows by key in one go. It keeps the table’s column types, refusing a value that doesn’t fit (When a column changes type).
  • A write can wait for nobody else to have changed the table. When two runs, or a run and a person, read the same table and write it back, Ronja can make the workflow’s write check that the table is still as it read it, and refuse it otherwise rather than lose the other change.

Run it once, then publish it. A new workflow stays a draft until you do, so publish before you point an automation at it; publishing also puts it on the Data map.

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

Ask Ronja for an automation with a Table trigger whose Watched tables is the raw table and whose action is the workflow. Every time the raw table finishes rebuilding, including a push of rows into a Dynamic table, the workflow runs and rewrites its output.

A table trigger runs at most 50 times per rolling 24 hours per automation, a loop-safety bound: an output that needs refreshing more often is firing on something it shouldn’t. Details are in Automation triggers.

  1. Open the Data map with Show in data map on either table, which opens the right feature card, or, as an admin, from Advanced → Data map in the sidebar.
  2. If you came from the sidebar, click the card your tables live in, or switch Group to Table. The map opens grouped by feature with every card closed.

The workflow is a chip between the two tables: a solid arrow in from the raw table, a dotted arrow out to the table it writes. Hover the chip for its name and how many tables it reads and writes; click its name to open the workflow. Selecting either table dims the map to its pipeline, which runs through the workflow, so the two tables are one chain.