Run files as jobs

The lake’s own files are a workspace. A .sql file, a .py file or a notebook kept there (the console’s Workspace, or PUT /files/…) runs as a job: with parameters, from any client, on a schedule, and each run is logged. That is an ETL pipeline with nothing else to install.

A file with parameters

Save a SQL file in the lake. $day and $region are its parameters:

curl -s -X PUT http://localhost:8080/files/etl/orders.sql --data-binary @- <<'EOF'
INSERT INTO orders VALUES ($day, $region, $amount);
SELECT region, count(*) AS n, sum(amount) AS total FROM orders WHERE day = $day GROUP BY region;
EOF

Run it with CALL run, naming each value:

CALL run('etl/orders.sql', day => DATE '2026-09-29', region => 'west', amount => 10.5);

 region | n | total
--------+---+-------
 west   | 1 |  10.5

• The values are bound, never pasted into the text: $day is a DATE here, and a string with a quote in it stays one string.
• The answer is the last statement’s.
• The statements run in order, and a failure stops the file: its error names the statement.
• The path is under the lake’s files, with or without files/.

Parameters declared

A DECLARE PARAMETER says what a parameter is: its type, its default, and, in the comment above it, what it means. A run’s value takes the default’s place, cast to the type; a parameter with no default must be given. A plain DECLARE is a variable of the file’s own: it never shows as a parameter, and a run given a value for it is refused. Tools and people read the parameters with pondra.parameters:

curl -s -X PUT http://localhost:8080/files/etl/by_region.sql --data-binary @- <<'EOF'
-- The day to load
DECLARE PARAMETER $day DATE = current_date - 1;
-- Where the orders come from
DECLARE PARAMETER $region VARCHAR;
DECLARE $since = $day - 7;   -- the file's own: not a parameter
SELECT count(*) AS n FROM orders WHERE day BETWEEN $since AND $day AND region = $region;
EOF

SELECT name, type, "default", required, description FROM pondra.parameters('etl/by_region.sql');

 name   | type    | default          | required | description
--------+---------+------------------+----------+---------------------------
 day    | DATE    | current_date - 1 | false    | The day to load
 region | VARCHAR |                  | true     | Where the orders come from

CALL run('etl/by_region.sql', region => 'west');
CALL run('etl/by_region.sql', region => 'west', day => '2026-09-29');

• A $name used and never set is a parameter too, required, with no type.
• The run’s variables are its own: a DECLARE in a file doesn’t change the caller’s $day, and $day = $day + 1 in it changes only the run’s (variables).
• In Python (a .py file, a notebook’s cells), pondra.vars.day is the same value.

Python files and notebooks

A .py file gets its parameters as variables. What it prints comes back as notices (psql shows them as NOTICE), and its last expression is the answer, as in a notebook cell. A cell marked # %% tags=["parameters"] (jupytext’s and papermill’s way) holds its parameters and their defaults: it runs first, the values given replace its names, then the rest runs; a name it doesn’t set is refused. Its annotations and comments are what pondra.parameters says:

curl -s -X PUT http://localhost:8080/files/etl/score.py --data-binary @- <<'EOF'
# %% tags=["parameters"]
region: str = "east"  # where the orders come from

# %%
print("scoring", region)
n = db.sql(f"SELECT count(*) AS n FROM orders WHERE region = '{region}'").item()
{"region": region, "orders": n}
EOF

CALL run('etl/score.py', region => 'west');

A notebook (.ipynb) runs its cells in order: %%sql cells as SQL, the others as Python, in one namespace for the run. Its cell tagged parameters (as papermill’s) holds the defaults, and the values given replace them; its SQL cells’ DECLARE PARAMETERs are parameters too. SQL cells take the values as $name.

CALL run('reports/weekly.ipynb', region => 'west');
CALL run('notebooks/weekly');           -- notebooks/weekly.ipynb (or, saved before versions, its newest save)

Note

Python files and notebooks with Python cells run code on the node, so they need an admin token, as DO does. A writer runs SQL files, with its own rights for each statement. The node needs Python for them: see Functions and procedures.

From every client

Python

import pondra, datetime
db = pondra.connect("http://localhost:8080")
db.run("etl/orders.sql", day=datetime.date(2026, 9, 29), region="south", amount=2.0)

db.run runs a .sql file on your machine if there is one at that path, and otherwise the lake’s file, on the node. wait=False starts it there and gives back its run.

JavaScript

import { connect } from "pondra";
const db = connect("http://localhost:8080");
await db.run("etl/orders.sql", { day: "2026-09-29", region: "south", amount: 2 });

Postgres

CALL run('etl/orders.sql', day => DATE '2026-09-29', region => 'south', amount => 2.0);

MCP clients run a file through the write tool, with the same CALL.

Files that run files

A file may run another with the same CALL, and a Python file with pondra.run, so SQL and Python chain freely:

curl -s -X PUT http://localhost:8080/files/etl/daily.sql --data-binary @- <<'EOF'
CALL run('etl/orders.sql', day => $day, region => 'east', amount => 1.0);
CALL run('etl/orders.sql', day => $day, region => 'north', amount => 2.0);
EOF

CALL run('etl/daily.sql', day => DATE '2026-09-30');

pondra.run("etl/orders.sql", day=datetime.date(2026, 9, 29), region="chain", amount=3.0)  # in a .py file

Runs inside runs stop 16 deep, so a file that runs itself ends with an error, not a loop.

Jobs, schedules and the run log

pondra.start('run', …) starts a file on the node and answers at once with its run’s id. A task runs it on a schedule:

SELECT pondra.start('run', 'etl/orders.sql', day => DATE '2026-09-28', region => 'later', amount => 8.0) AS run;
CREATE TASK nightly SCHEDULE 'cron 0 2 * * * UTC' AS CALL run('etl/daily.sql', day => current_date - 1);

Every run is a row of pondra.runs, named files/<path>@<version>: the file, and the version of it that ran. Its arguments, who ran it (a task’s runs say task:nightly), when, and how it went are there too:

SELECT routine, args, caller, status, error FROM pondra.runs WHERE routine LIKE 'files/%' ORDER BY started DESC LIMIT 5;

• Retried with a job, a run writes once. Over HTTP, POST /sql?job=…; a task’s tick has its own. Each statement of the file (and of the files it runs) gets its part of the job.
• The version is the file’s etag. A file is replaced in place when saved, so the log names which version ran; keep the files in git (below) for their history.

DROP TASK nightly;

In git

pondra workspace pull DIR writes the lake’s files into a folder of your own, and pondra workspace push DIR sends back what changed there: edit them in your editor, commit them with git, review them in a pull request, then push them to the lake that runs them.

pondra workspace pull ws --url http://localhost:8080
printf 'SELECT count(*) AS orders FROM orders;\n' > ws/etl/count.sql
pondra workspace push ws --url http://localhost:8080

pulled 4, removed 0 here
pushed 1, deleted 0 in the lake

• Each side’s changes reach the other. A pull writes the files the lake changed since the last sync and removes the ones it deleted; a push sends the files changed here, new ones too, and deletes there the ones deleted here.
• A file changed on both sides is a conflict. It is listed, left as it is on both sides, and the command exits 1. Make the two the same (or take one side’s), and the next sync goes on.
• What the folder last agreed with the lake is DIR/.pondra/workspace.json: each file’s version in the lake and its checksum here. A push replaces a file only if the lake still has that version (If-Match), so nothing anyone saved since is lost. Commit it with the files, or leave it out with .gitignore; a folder without it pulls everything again.
• Names starting with . (.git, .gitignore, .pondra, the lake’s kept versions) stay on their own side.
• Without --url it starts a node on the lake you name (pondra workspace pull ws ./lake, or s3://bucket/lake), as pondra run does. With tokens, --token (or PONDRA_TOKEN) is a writer’s for a push.

In the console

• A SQL file’s $names each get an input above the editor. Run sends their values with the statements, bound on the node.
• The file’s ⋯ (and a notebook’s) has Run as a job, which saves the file and starts it on the node, and Schedule…, which makes a task that runs it (15 minutes, 1 day, or a cron line).
• History (the right pane), at Runs, lists the node’s runs, newest first, and the schedules, each with a button to drop it. A running job’s row updates until it ends. Click a file’s run to open the file.