For agents · the instructions your agent follows. The page for people: Keep a table filled from a website
Scheduled SQL: keep a table filled from a URL
Give LakehouseBox one DuckDB SELECT that reads http(s) URLs, and a schedule. At every run LakehouseBox runs the query, casts the rows to your table's types and commits them. "Read this feed every 20 minutes and keep this table populated" is one command, with no cron job, server or engine of your own to keep running.
The query runs on LakehouseBox's executor, not on your machine, and an agent can set the whole thing up with the CLI. A scheduled-SQL sink is a kind of ingest sink: it has no send key, and LakehouseBox is the producer.
A first sink, end to end
The US Geological Survey publishes the earthquakes of the last hour as a GeoJSON file. On engine 2.0 (the default), read_json reads a GeoJSON FeatureCollection as one row per feature, with each feature's properties as columns, so this query turns it into one row per earthquake (quakes.sql):
SELECT id AS quake_id,
make_timestamptz("time" * 1000) AS event_time,
mag AS magnitude,
place,
(geometry->'coordinates'->>0)::DOUBLE AS lon,
(geometry->'coordinates'->>1)::DOUBLE AS lat,
(geometry->'coordinates'->>2)::DOUBLE AS depth_km
FROM read_json('https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.geojson',
columns = {id: 'VARCHAR', "time": 'BIGINT', mag: 'DOUBLE', place: 'VARCHAR', geometry: 'JSON'})
Without columns, the same read gives every property and a geometry column of type GEOMETRY. Engine 1.5 reads the file as one row instead. For a query that runs on both engines, read the file whole with format = 'unstructured' and unnest its features (without that option, this form returns 0 rows on 2.0: see the engine):
SELECT f->>'id' AS quake_id,
make_timestamptz(((f->'properties'->>'time')::BIGINT) * 1000) AS event_time,
(f->'properties'->>'mag')::DOUBLE AS magnitude,
f->'properties'->>'place' AS place,
(f->'geometry'->'coordinates'->>0)::DOUBLE AS lon,
(f->'geometry'->'coordinates'->>1)::DOUBLE AS lat,
(f->'geometry'->'coordinates'->>2)::DOUBLE AS depth_km
FROM (SELECT unnest(features) AS f
FROM read_json('https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.geojson',
format = 'unstructured', columns = {features: 'JSON[]'}))
lhbox sink preview --catalog analytics --sql @quakes.sql --limit 3 # runs once, writes nothing
lhbox sink create --catalog analytics --name docs_quakes --table docs_example.quakes \
--sql @quakes.sql --every 1h --offset 7m --key quake_id --create-table --paused
lhbox sink run docs_quakes --wait # one run now, until committed
lhbox sink runs docs_quakes # state, rows, requests, bytes, errors
lhbox sink resume docs_quakes # start the schedule
The preview shows the rows and each column's Iceberg type. With --table, it also says whether they fit an existing table. It carries at most 50 rows and 50,000 characters per value (--limit only chooses how many of those to print); when it cut either, the answer says so (truncated, truncated_by, rows_shown, values_truncated). A run keeps every row and the whole value.
--create-table makes the table from the query's columns. --key quake_id keeps one row per quake (below); a key needs a format-version 2 table, so the table is made format version 2. Give the key here, at creation: a key added later with lhbox sink update is refused (409 key_unsupported) when the table was made format version 3, the catalog's default. --every 1h --offset 7m runs the query at seven minutes past every hour (UTC). --paused holds the schedule until you have seen one run.
This feed covers a whole hour, so a quake is read by several runs. With a key, each run replaces the rows that share it, and the latest one wins (see a key). Without a key, runs append, and a quake read by two runs is stored twice. Every row carries __ingest_ts, the time its run was committed. A reader can use it to keep the latest row per key:
SELECT * FROM analytics.docs_example.quakes
QUALIFY row_number() OVER (PARTITION BY quake_id ORDER BY __ingest_ts DESC) = 1
What each command printed in a real run is at the end of this page.
Who can use it
Scheduled SQL is live and on for every organisation on the free plan. The plan allows two scheduled-SQL sinks (the plan limit sql_sinks, on the pricing page). Every sink of the organisation that is not deleted counts, a paused one too, whichever catalog or session made it: a third is refused with 409 limit_exceeded. lhbox usage --full shows yours and the per-run budgets.
A catalog an agent made without an account (lhbox new) gets it when a person claims it. Until then, sink create, run and resume answer 403 claim_required.
Preview works before the claim too. The query runs once and nothing is written, so an agent can hand over a query it has seen run. Without the feature (before the claim, or where it was turned off), a preview is smaller:
- at most 25 requests and 16 MiB downloaded;
- one at a time, in a capacity kept for such previews: when it is busy, 429 with
retry_after_seconds; - 10 such previews an hour per organisation (the plan limit
sql_previews_per_hour); a preview that did not run is not counted.
Its answer carries sink_create, saying what creating the sink still needs (not_enabled or claim_required). No stored connection credential is sent.
What the query can do
It is one SELECT: CTEs, joins, UNPIVOT, LATERAL, window functions and QUALIFY are all fine. It reads http(s) URLs, public or with a connection's credential, or the tables of its own catalog (derived sinks, below), not both in one query. The readers are DuckDB 2.0.0's: read_json, read_ndjson, read_csv, read_parquet, read_text, read_blob, or FROM 'https://…', the excel extension's read_xlsx, and the yaml extension's read_yaml, read_yaml_objects and read_yaml_frontmatter.
One failing URL in a list: ignore_errors. By default read_text([...]), read_blob([...]) and the other readers given a list fail the whole query when one URL fails (a 404, a timeout, a refusal), with a source_error that names the failing URL and its HTTP status first (The source answered HTTP 404 Not Found. HTTP Error: HTTP HEAD error on 'https://…/missing.txt' (HTTP 404 Not Found)). To keep the rest of the batch, add ignore_errors := true to read_text or read_blob:
SELECT filename, content, error
FROM read_text(['https://example.org/a.html', 'https://example.org/gone.html'], ignore_errors := true)
Every URL is then a row, in the list's order, with one more column, error: NULL when the URL answered, and the reason when it did not (the same message a failing query gives, its HTTP status first), with content, size and last_modified NULL. WHERE error IS NULL keeps only the URLs that answered; a sink that stores filename and error keeps a record of the dead links. Each URL is fetched on its own, through the same proxy and budget as any read; one that answered 408, 429 or 5xx (or did not answer) is asked once more a few seconds later when the run has the time, a 404 or 403 once. A run that runs out of time or memory still fails as a whole. The URL argument is a URL or a list of URLs (ignore_errors = true works too; ignore_errors := false is the default, all or nothing). ignore_errors is LakehouseBox's option for these two readers only: read_csv's own ignore_errors skips bad rows, not failed URLs, and the other readers given a list stay all or nothing, so read one URL per sink, or fetch the files with read_text / read_blob and parse content (content::JSON, for instance). A URL inside read_text must still be a literal (a LATERAL call per row is refused at bind time).
Finding the links that answer, without ignore_errors. A query that reads only filename (or only count(*)) from read_text([...]) or read_blob([...]) does not fail on a URL that fails: it returns one row per URL that answered and leaves out the others, without saying why. Each URL is still downloaded once and counts against the run's budget. A sink whose query reads only filename therefore silently leaves out the URLs that failed; ignore_errors := true says which and why (DuckDB 2.0.0, measured 2026-10-29).
Excel workbooks. read_xlsx('https://…/register.xlsx') reads the first sheet of an .xlsx workbook at a URL, with the header row as column names and each column typed from its cells; sheet = 'Name' picks another sheet, range = 'A5:F200' a block of cells, header = false and all_varchar = true change how the first row and the types are read (FROM 'https://…/register.xlsx' works too, with the defaults). The workbook comes through the same proxy as any URL and counts against the run's budget (downloaded whole, once per read); the sheet itself is read as a stream, so a 1,000,000-row sheet (147 MB uncompressed, 20 MB file) read in about a second in 70 MB of memory (measured 2026-10-23). The workbook's text cells that the sheet uses are held in the run's memory: a workbook with more distinct text than the run's memory answers query_memory. Only .xlsx: the old binary .xls format is not read. On either engine; the extension is loaded only for a query that uses it.
The query may also call the reviewed DuckDB community extensions the query service has: h3 (H3 cells), splink_udfs (phonetic and accent-stripping string functions), urlpattern and yaml. Each is loaded only when the query calls one of its functions, on either engine, and the same functions are refused as on the query service (those that multiply their input, such as h3_grid_disk, and the ones that change settings for the whole process).
Protocol Buffers. read_blob of a protobuf URL and proto_to_json(content, '<message>') (or proto_decode, its alias) decode it, with proto_get(content, '<message>', '<path>') for one field and proto_describe('<message>') for its shape. The GTFS-Realtime schema is registered for you (transit_realtime.FeedMessage): a transit agency's live vehicle feed becomes rows in one query. A query cannot register other schemas; ask us for the one you need. Example, one row per vehicle with its position as a geometry:
WITH f AS (SELECT proto_to_json(content, 'transit_realtime.FeedMessage')::JSON AS j
FROM read_blob('https://gtfs.ovapi.nl/nl/vehiclePositions.pb')),
e AS (SELECT unnest(from_json(j -> 'entity', '["JSON"]')) AS e FROM f)
SELECT e ->> 'id' AS vehicle_id,
to_timestamp((e -> 'vehicle' ->> 'timestamp')::BIGINT) AS ts,
format('POINT ({} {})', (e -> 'vehicle' -> 'position' ->> 'longitude')::DOUBLE,
(e -> 'vehicle' -> 'position' ->> 'latitude')::DOUBLE)::GEOMETRY AS geometry
FROM e
Text that is not UTF-8. DuckDB's readers take UTF-8 text (only read_csv has an encoding option), and much public data is Latin-1 or a relative. Read the bytes with read_blob and decode them with LakehouseBox's decode_text(content, '<encoding>'), then parse the text with DuckDB's JSON or string functions. The encoding is the label in the source's Content-Type charset: utf-8, latin-1 (iso-8859-1), iso-8859-15, windows-1252, utf-16 and the other ISO-8859, Windows and common Asian encodings. Example, AEMET's hourly observations (text/plain;charset=ISO-8859-15), one row per station and hour with its accented name intact:
WITH t AS (SELECT decode_text(content, 'iso-8859-15') AS txt
FROM read_blob('https://opendata.aemet.es/opendata/sh/f9b006f6')),
o AS (SELECT unnest(from_json(txt, '[{"idema":"VARCHAR","ubi":"VARCHAR","fint":"VARCHAR","prec":"DOUBLE"}]'),
recursive := true) FROM t)
SELECT idema, ubi, fint::TIMESTAMPTZ AS fint, prec FROM o
A byte the encoding has no character for fails the run with a sql_error naming its offset (it is never replaced by � in your table). decode_text is on engine 2.0 only. The decoded text is held in the run's memory beside the bytes.
A URL from an earlier answer, and request headers: http_get. The readers take a URL written in the query. When the URL (or a token) comes from a first answer, or a source wants headers of its own, call LakehouseBox's http_get(url, headers := MAP {…}, encoding := '…'), which takes values, columns included. It answers one value per call, a struct: status (the HTTP status: a 404 is a row with status 404, not a failed run), headers (the response's, names in lower case; a repeated header's values joined by , , set-cookie's by a line break), body (the text, decoded by the Content-Type charset, else UTF-8; encoding names another) and url (the last one asked, after redirects). Example, AEMET's two steps (the first answer names the URL of the data), the key in a connection:
WITH s AS MATERIALIZED (
SELECT (http_get('https://opendata.aemet.es/opendata/api/observacion/convencional/todas')).body::JSON ->> 'datos' AS u),
d AS MATERIALIZED (SELECT (http_get(u)).body AS txt FROM s),
o AS (SELECT unnest(from_json(txt, '[{"idema":"VARCHAR","ubi":"VARCHAR","fint":"VARCHAR","prec":"DOUBLE"}]'),
recursive := true) FROM d)
SELECT idema, ubi, fint::TIMESTAMPTZ AS fint, prec FROM o
and a source that wants its own headers: http_get('https://…', headers := MAP {'Accept': 'text/html', 'Accept-Language': 'es'}). Each call is one request through the same proxy as every read, checked the same way (public addresses only, ports 80 and 443) and counted in the run's request and download budgets and the per-host rate; it follows up to 5 redirects, each a request of its own, checked again. A connection whose prefix the URL starts with adds its credential, as for the readers (the query must name the connection's host, as the readers' must); on a redirect to another host the Authorization and Cookie headers you set are dropped. Host, Content-Length, Connection, Accept-Encoding, User-Agent and Proxy-* are LakehouseBox's to set. What fails the run: the proxy's refusal (egress_refused, budget_exceeded, rate_limited), a connection that broke, a body over 64 MB, or a body that is not text in its encoding. Put a call in a MATERIALIZED CTE when its result is used more than once, so it is asked once. On engine 2.0 only.
APIs that answer a POST: http_post. http_post(url, body, headers := MAP {…}, encoding := '…') sends one request with a body (a form, or a small JSON document) and answers the same struct as http_get. It sends only to URLs under a prefix your catalog declared for POST; declare one with lhbox sink post-prefix add https://host/path/ --catalog <catalog> (write access to the catalog). Any other POST is refused with post_not_declared, whatever the query asks. The query must name the prefix's host, as for connections. The body is text sent as UTF-8, at most 64 KiB. Without a Content-Type header, a body that starts with { or [ goes as application/json and any other as application/x-www-form-urlencoded. Each POST counts in the run's request budget and the per-host rate, and the run records which hosts it sent bodies to (egress.post_hosts). A redirect after a POST is not followed: the 3xx is the answer. A POST may be sent more than once for one slot:
- the preview that checks a sink when it is created or its query changed sends it;
- so does each retry of a failed run (a run that POSTs is not retried within itself).
unnest(http_post(…)) (or http_get) is refused (http_call_repeated): it would send one request per field of the answer. Call it once in a subquery and expand that: SELECT unnest(r) FROM (SELECT http_post(…) AS r).
Declare only endpoints where a repeated request does no harm, such as a viewer's query sent as a form, never one that submits, books or changes something. Removing a prefix (lhbox sink post-prefix remove) pauses the catalog's SQL sinks that POST under it. Example, a form, then a token from a first POST used in a second:
-- lhbox sink post-prefix add https://saihweb.chsegura.es/apps/iVisor/ --catalog <catalog>
SELECT (http_post('https://saihweb.chsegura.es/apps/iVisor/obtener_datos.php', 'action=consultar_embalses')).body::JSON AS j
-- lhbox sink post-prefix add https://www.example.org/api/ --catalog <catalog>
WITH t AS MATERIALIZED (SELECT (http_post('https://www.example.org/api/login', '{"user": "public"}')).body::JSON ->> 'token' AS token)
SELECT (http_post('https://www.example.org/api/data', '{"id": "E1-01"}', headers := MAP {'Authorization': 'Bearer ' || token})).body
FROM t
Zip archives. A file inside a zip archive at a URL is read by naming the reader: read_csv('zip://https://gtfs.ovapi.nl/nl/gtfs-nl.zip/routes.txt'), and zip_contents('https://…/feed.zip') lists the archive (the zipfs extension, loaded only for such a query). The archive comes through the same proxy as any URL and counts against the run's budget: it is downloaded whole, once per read. A compressed file inside it is decompressed whole into the run's memory before DuckDB reads it, so its uncompressed size has to fit the run. Measured on the Dutch GTFS feed (218 MB zip, 2026-10-23): trips.txt (61 MB, 842,644 rows) runs in 1 s; stop_times.txt (968 MB, 16.3 million rows) does not fit a run at any budget an organisation can be given today. FROM 'zip://…' without a reader is not supported. Only zip: not archive:// or compressed:// (tar, 7z and the like), not a zip inside a zip, and only an http(s) URL inside zip://.
Several URLs may be read in one call: a list, or a list comprehension over page numbers. A URL may contain current_date. LakehouseBox writes the rows; the query never writes.
The query runs on an executor of its own, whose only way out is a proxy that allows:
- GET and HEAD, to ports 80 and 443 of public addresses, checked again on every redirect, and POST (
http_post) only under a prefix the catalog declared; - a budget per run: 60 s, 768 MB, 1,000 requests and 1 GiB downloaded by default; the time, the requests and the download are configurable per organisation;
- a rate per destination host shared by every LakehouseBox job.
Every request names its sender: User-Agent: LakehouseBox-scheduled-sql/<engine> (+https://lakehousebox.com/docs/scheduled-sql/; hello@lakehousebox.com), set by LakehouseBox (never your organisation's name), so a source's operator can see who fetches and write to us. Requests ask for no compression (DuckDB does not decode a gzip Content-Encoding) and are never conditional (no If-Modified-Since or If-None-Match): every run fetches the whole response again. A source that answers 429, 503 (or 408, 500, 502, 504) or drops the connection is asked once more, 3 seconds later, so a run sends at most two requests per URL; Retry-After is not honoured within a run, and a failed run is retried by the schedule (30 seconds later at first).
Not available:
- local files, settings, other extensions, and secrets other than the catalog's connections (below);
- a request body to a URL your catalog has not declared for POST;
- your catalog's own tables in the same query as a URL: a derived sink reads the tables, and reads no URL.
Pin the types of a JSON source (columns = {…}, or JSON and ->>): inference can change between fetches.
Reading the catalog's own tables: derived sinks
A sink whose query reads tables of its own catalog keeps a table derived from them: an hourly summary, a grid, the latest state per station. Name the tables as you would in any query (catalog.namespace.table, or namespace.table):
lhbox sink create --catalog embalses --name basin_summary --table now.basin_summary --create-table --format-version 2 \
--sql @sql/now/basin_summary.sql --every 1h --offset 37m --mode replace --min-rows 10
-- sql/now/basin_summary.sql
SELECT r.basin_id, now() AS as_of, sum(s.stored_volume_hm3) AS stored_volume_hm3, sum(s.capacity_hm3) AS capacity_hm3
FROM embalses."now".reservoir_status s JOIN embalses.ref.reservoirs r USING (reservoir_id)
GROUP BY r.basin_id
- It runs where your other server-side queries run, with the sink's catalog attached read-only, as the person who created the sink (or last set its query): the runs stop, and the sink pauses (
owner_lost_write), when that person no longer holds write on the catalog. Someone who writes the catalog takes a query over by setting it, the same text included (lhbox sink update <sink> --sql …), and resuming the sink. A preview (lhbox sink preview) runs it the same way, with the 20 seconds of a server-side query, and writes nothing; it needs scheduled SQL enabled for the organisation. - Its own catalog only. A query naming another catalog's table is refused at preview, create and change with
400 catalog_not_this_sinks, even when you can read that catalog. To combine two catalogs, copy what you need into this one first. - Tables or URLs, not both. The URL readers (
read_csv,read_json,http_get, …) do not run in a derived sink, and a URL sink cannot read catalog tables (catalog_tables_not_supported). Load the source into a table with one sink and derive from it with another. - The functions are those of a server-side query (
POST /v1/query): the h3 extension, the spatial extension (ST_Distance_Sphere,ST_Xon a geometry column) and the rest of DuckDB 2.0.0's. Engine 2.0 only. - A run has at most 60 seconds of compute (or your organisation's
sql_run_seconds, if lower) and the memory of a server-side query, and writes at most 1,000,000 rows. Each run is one batch, committed like any other sink's, so--mode replaceswaps the whole table at once and--keykeeps the latest row per key. - One table depends on another: give each sink its own offset in the same hour, the one it reads first. A run reads the tables as they are when it starts:
--every 1h --offset 28mfor the first,--offset 31mfor the next, and so on. A run that has to wait for the query service starts later in its slot, so leave a few minutes between them. lhbox sink getshows"reads": "catalog"for a derived sink,"urls"for the others.
The engine
A sink runs on one DuckDB engine and keeps it: 2.0 (DuckDB 2.0.0, the default) or 1.5 (DuckDB 1.5.6). The engine is stored when the sink is created (--engine, else the default of that day), so a later change of LakehouseBox's default never changes what an existing sink runs. Moving a sink (lhbox sink update <sink> --engine 1.5) previews its query on the new engine against the table first, and is refused if it does not run or fit there. Previews take --engine too. Every run in lhbox sink runs names its engine.
The engines read some sources differently. The one we know of: DuckDB 2.0's read_json reads a GeoJSON FeatureCollection as one row per feature (its properties as columns, plus a GEOMETRY column), where 1.5 reads the file as one row with a features list. A query written for 1.5, unnest(features) over read_json(url, columns = {features: 'JSON[]'}), runs on 2.0 without an error and returns 0 rows. Either write it for 2.0 (the first example above), or add format = 'unstructured' to its read_json, which reads the file as one row on both engines (measured on the USGS feed, 2026-10-24: 238 rows on 1.5.6, and on 2.0.0 with it; 0 on 2.0.0 without).
Because 0 rows fit any table, a change of a sink's query or engine whose preview returns 0 rows, while the sink's recent runs (its last 10 finished ones) returned rows, is refused with 409 preview_empty and changes nothing. When an empty answer is what you expect now, send the change again with allow_empty: true (lhbox sink update <sink> … --force).
Sources that want a credential: connections
An API key never goes in the SQL. Store it once as a connection of the catalog, bound to a URL prefix, and write the plain URL in the query. The executor adds the credential to every request under that prefix, and to nothing else:
lhbox sink connection create weather --catalog <catalog> --url-prefix https://api.example.org/v1/ --bearer --value-env WEATHER_TOKEN
lhbox sink connection create aemet --catalog <catalog> --url-prefix https://opendata.aemet.es/ --header api_key --value @-
lhbox sink connection create legacy --catalog <catalog> --url-prefix https://data.example.com/feed/ --basic --value @userpass.txt
lhbox sink connection list --catalog <catalog> # names, prefixes, kinds: never a value
lhbox sink connection replace weather --catalog <catalog> --value @new-token.txt
lhbox sink connection delete weather --catalog <catalog> --yes # pauses the SQL sinks that name its prefix or host
Kinds. bearer (Authorization: Bearer …), header (a header you name, e.g. X-API-Key), basic (user:password), and query (URL query parameters, below).
A key the source wants in the URL: query. Some APIs take the key, or a user and password, as query parameters (?api_key=…, ?user=…&password=…). A query connection holds one to eight NAME = value pairs and appends them to the query string of every request under its prefix:
lhbox sink connection create stations --catalog <catalog> --url-prefix https://api.example.org/v2/ \
--param user=env:STATIONS_USER --param password=@password.txt
lhbox sink connection create maps --catalog <catalog> --url-prefix https://maps.example.org/ --param api_key --value-env MAPS_KEY
lhbox sink connection replace stations --catalog <catalog> --param password=@new.txt # user is kept
Each --param takes its value from @FILE, @- (stdin, one parameter only) or env:VAR, never from the command line; a single bare --param NAME takes --value, --value-env or a hidden prompt. A replace changes the parameters it names and keeps the others; --replace-all makes the ones given the whole set.
- Only
http_getandhttp_postsend them. DuckDB's own readers (read_json('https://…')) cannot: fetch the URL withhttp_getand parse itsbody(from_json,read_csvof the text). A reader that meets a 401 under such a prefix answersauth_requirednaming the connection. - The query never sets them itself. A URL in the SQL that already names one of the parameters, in any case, is refused before any request (
query_param_conflict): remove it from the URL. - The values never come back. The
urlthathttp_getanswers is the URL as the query wrote it. Every error and run record has the values replaced by<redacted>, as written and in their encoded forms (percent, JSON, HTML). In the headers and body of an answer to a request that carried them, a value of 8 characters or more is replaced the same way; a shorter one (a user id, a language code) is left alone, since masking it would change ordinary data. - Redirects. A redirect is followed to its target without the parameters, and they are added again only when the target is under the prefix. A source whose
Locationechoes one of them has it removed first; aLocationthat still holds a value in another shape (a return URL nested in a parameter, the path) is not followed and fails the run. - An organisation web key sent as a query parameter (
key_param) cannot be attached with--integrationyet: store it as aqueryconnection.
A key your organisation already stored for agents. A web key (lhbox integration list, kind web: the key a hosted agent sends with fetch_url) can be a connection too, with no second copy of it:
lhbox sink connection create aemet --catalog <catalog> --integration aemet
The connection's prefix is the key's host (https://opendata.aemet.es/), and its kind and header are the key's.
- It stays the person's key. Only the person who stored the web key attaches it, in a catalog of the same organisation. It is then sent only in queries that person wrote: their previews, and the sinks whose query they last set. Another writer's query that names the host gets no key, and the source answers 401 (
auth_required). It is never sent once its owner leaves the organisation or is suspended. - One key, read at each run. Every run reads the key from the web key itself. To replace it, the owner deletes the web key and adds it again under the same name and host; every agent and connection that uses it follows. A web key deleted and not added again fails the queries that use it (
connection_unreadable, the sink paused), and they never run without it. - A web key sent as a query parameter (
key_param) cannot be used this way; make aqueryconnection instead.
The prefix is https://, a host name, and a path ending in /. Two connections of a catalog may not overlap. The credential is not sent on a redirect to another host. The host is the boundary: DuckDB compares URLs as text, so a path prefix does not keep the credential from other paths of the same host (/v1/../admin/).
A header connection keeps the query on its own origin (its https host and port), because DuckDB would send a custom header on to wherever a redirect points.
- A query that also uses a connection on another host or port is refused before it runs (
connection_conflict). Other paths of the same host are fine. - Another host, plain http on the same host, or a redirect to either, is refused while it runs (
egress_refused). - Read other sources in another SQL sink.
- A query that uses only
bearerorbasicconnections has no such limit.
A connection belongs to the catalog, not to the one who stored it. Every member and agent that can write to the catalog can run a scheduled query that sends it to its host. None can read the value back. Give the key the narrowest rights the source offers. A key that only some writers should use goes in a catalog of its own.
Who stores it is up to you. An agent with the CLI uses --value-env VAR, --value @FILE or @-. A person at a terminal with neither is asked for it, hidden. Or a person stores it on the account page: the catalog's Sinks tab, Connections, paste the key. The agent then only ever needs the connection's name. Never put the value on the command line itself.
The person: on the catalog's Sinks tab, under Connections, clicks Add a connection and pastes the key.
The value is write-only. No route, command or page shows it again, and it is stored encrypted.
When the source refuses. A source that answers 401 fails the run with auth_failed, naming the connection. It is not retried: replace the value. A 401 where no connection applies is auth_required. A 403 is retried like any source error and names the connection: it is as often a rate limit or a challenge as a bad key.
Keys in the query string (?api_key=…) and OAuth client credentials are not supported yet.
The table
The query's columns are matched to the table's by name and cast to its types before anything is stored. A column the table lacks, a required column missing, or a value that does not cast fails the run. The error names the column and a sample value. A struct field the table's struct lacks is a column the table lacks too (unknown_column, naming it as s.z, l[].z inside a list).
create_table makes the table from the query's columns, in the format version you choose: format_version 2 or 3 (--format-version 2). Without it, the table gets the catalog's default format version, as lhbox table create does (3 for a catalog created since 2026-11-15; lhbox catalog update --default-format-version 2|3 changes it). A sink with a key or that replaces its table needs format version 2: without format_version, its new unpartitioned table is made 2 (format_version_source: sink_needs_v2). The query's columns never choose the version. A query that does not fit it is refused before any table is made, and the error names field: "format_version", where the version came from (format_version_source: request, catalog_default or sink_needs_v2) and a remedy:
- a geometry column under format version 2 (
400 format_version_conflict): sendformat_version: 3, or return the geometry as WKT (ST_AsText(g)) or WKB (ST_AsWKB(g)); partitionunder format version 2 (409 partitioned_table_unsupported): a sink feeds a partitioned table only in format version 3;keyunder format version 3 (409 key_unsupported): a key needs format version 2;- any value other than 2 or 3 (
400 invalid_value).format_versionandpartitionapply only to a table the sink creates: with an existing table they are409 table_exists.
partition (--partition day(ts)) partitions the new table by the year, month, day or hour of a date or timestamp column. POST /v1/sql/preview takes format_version too: without table, its answer names create_table_format_version and lists a geometry that version cannot hold in problems. The create answer's docs_hint names what the table lacks (a description, column descriptions) and the command that adds them: lhbox table document <catalog>.<ns>.<table> --doc "…" --column name="…".
A sink feeds a format-version 2 table that is not partitioned, or a format-version 3 table, which may be partitioned by time. These are the same rules as any ingest sink. A table it cannot feed is refused when the sink is created, and a sink whose table changes into one pauses (paused_reason: table_unsupported):
- a
geographycolumn, ageometrywith a coordinate system of its own (a sink writes longitude/latitude,OGC:CRS84, the default), or a geometry inside a list, struct or map; - a partition other than time (
partitioned_table_unsupported), or any partition on a format-version 2 table; - a key on a format-version 3 table (
key_unsupported): a key needs format version 2. Read the latest row per key withQUALIFYinstead.
A geometry column takes a GEOMETRY, WKB in a BLOB, or WKT in a VARCHAR. Build a point from two numbers with format('POINT ({} {})', lon, lat)::GEOMETRY; ST_GeomFromWKB(…) and, on engine 2.0, ST_GeomFromGeoJSON(…) work too. A sink that reads URLs has no spatial extension, so ST_Point and ST_Transform are not available there (a derived sink has it). A value that does not parse fails the run with type_mismatch and a sample. A geometry labelled OGC:CRS84 or EPSG:4326 (x = longitude) is taken as longitude/latitude. One in another coordinate system fails the same way.
-- a v3 table of positions, partitioned by day: lhbox sink create ... --create-table --format-version 3 --partition 'day(ts)'
SELECT vehicle_id, to_timestamp(t) AS ts, format('POINT ({} {})', lon, lat)::GEOMETRY AS geometry, speed
FROM read_json('https://example.org/vehicles.json')
The schedule
every is at least 1 minute by default (5 minutes until 2026-10-23); an operator can set another floor per organisation. A run that takes longer than every makes the next slot skipped. offset places the runs inside the interval: every 20m offset 2m runs at :02, :22 and :42 UTC.
- Each slot runs once and becomes one batch, committed whole by the next roll, seconds later.
- A slot that comes while the previous run is still going is
skipped. Slots missed while LakehouseBox was down are counted, not run late. - A failure the next attempt may fix is retried within the slot after 30 s, 2 min and 8 min. That covers the source or the executor being unavailable, and a timeout. A run that needed more memory than a run has (
query_memory) is not retried: it fails at once, and the next run (a manual one too) can start straight away. - A sink whose runs all failed for 24 hours pauses itself (
paused_reason: failing). So does one whose table is gone (table_missing) or changed into one it cannot feed (table_unsupported), whose organisation lost the feature (not_enabled), or whose creator, or the person who last set its query or engine (sql_author), no longer holds write on the catalog (owner_lost_write). - Runs append, unless the sink has a key (
--key): then each run replaces the rows that share it. Without a key, a source that republishes rows lands them again at every run. Read the latest per key withQUALIFY row_number() OVER (PARTITION BY <key> ORDER BY __ingest_ts DESC) = 1. A sink can also replace the whole table at every run (below).
Replacing the table at every run
For a source that is republished whole (a timetable, a register, a price list), a sink can replace its table at every run instead of appending: --mode replace ("mode": "replace"). Each run's rows become the table's rows in one commit, an overwrite snapshot: a reader sees the previous version or the new one, never a mix of the two, and every version is a snapshot you can read later.
lhbox sink create --catalog transit --name gtfs_trips --table gtfs.trips --create-table --format-version 2 \
--sql "SELECT * FROM read_csv('zip://https://gtfs.ovapi.nl/nl/gtfs-nl.zip/trips.txt', quote='\"', escape='\"')" \
--every 1d --offset 5h --mode replace --min-rows 100000
- An empty run never empties the table. A run that returns fewer rows than
min_rows(1 unless you set it) fails withempty_replace_refused, stores nothing, and leaves the table as it is. Set--min-rowsnear the size you expect, so a source that suddenly returns a fraction of its rows does not replace the table either (at most 1,000,000, the most rows a run writes); change it later withlhbox sink update <sink> --min-rows N. - A run with a row that does not fit the table is not committed either: the run fails with
replace_refused, the table keeps its previous version and the run's rows go to the sink's rejects. - A table that already holds rows needs
--replace-existing("replace_existing": true): the first run replaces them. Without it the create is refused (409 replace_needs_confirmation) and says how many rows the table holds. - The table is format version 2 (
--format-version 2with--create-table;409 replace_unsupportedotherwise), not partitioned, with no key (400 mode_conflict). A sink whose table later becomes format version 3 pauses (table_unsupported). - A replace sink is alone on its table: no other sink can feed it (
409 sink_exists), and it cannot join a table that already has one. Appending sinks can share a table (up to 8): one scheduled query per source, each failing on its own. - The mode is fixed when the sink is created (
409 mode_fixed): to change it, delete the sink and create it again. The table and its rows stay. - Runs are committed one at a time, oldest first: if two are waiting, each becomes its own version.
Reading an older version. Every run is one snapshot whose summary names the run's batch (lakehousebox.sink.batches, the run's batch_id in lhbox sink runs); the run shows the snapshot id once it is committed. Read a version by snapshot or by time (DuckDB, the catalog attached as transit):
SELECT * FROM iceberg_snapshots('transit.gtfs.trips'); -- every version still kept
SELECT count(*) FROM transit.gtfs.trips AT (VERSION => <snapshot id>);
SELECT count(*) FROM transit.gtfs.trips AT (TIMESTAMP => TIMESTAMP '2026-10-20 06:00:00');
How many versions survive is the catalog's snapshot window (time travel): by default at most 20 snapshots and none older than 7 days, so a daily sink keeps about a week of versions; lhbox catalog update transit --snapshot-retention-days 30 keeps a month. Every kept version keeps its data files, and they count against your storage.
The commands
lhbox sink preview --catalog <catalog> --sql @madrid.sql --table air.madrid # runs once, writes nothing: rows, columns, fits?
lhbox sink create --catalog <catalog> --name madrid_air --table air.madrid --sql @madrid.sql --every 20m --offset 2m --create-table
lhbox sink run madrid_air --wait # one run now, until committed
lhbox sink runs madrid_air # state, rows, requests, bytes, errors
lhbox sink pause madrid_air # hold the schedule
lhbox sink resume madrid_air # start it again
lhbox sink update madrid_air --sql @v2.sql # a new query
lhbox sink update madrid_air --engine 1.5 # another DuckDB engine, previewed first
lhbox sink create ... --mode replace --min-rows 1000 # each run replaces the table (above)
lhbox sink update gtfs_trips --min-rows 50000 # a replace sink's guard
When it fails
Every error names what to change and carries a remedy.
| error | what it names |
|---|---|
egress_refused |
the host and the reason |
source_error |
what the source answered, its HTTP status first (http_status) when it sent one, and the URL; runs retry it |
invalid_url |
a URL with a % that is not an escape (url, the character); write a literal % as %25; not retried |
type_mismatch |
the column, the expected type, a sample |
sql_error |
DuckDB's message; when a reader trips on the first byte of a body, the remedy names compression='gzip' (some servers send gzip even when not asked) |
auth_failed |
the connection the source refused with 401: replace its value |
auth_required |
a 401 where no connection applies |
connection_conflict |
a header connection used with a connection on another host or port |
budget_exceeded, rate_limited, unknown_column, query_timeout, output_too_large |
what to change, in its remedy |
query_memory |
the memory that stopped the run (memory_limit for the query; process_limit when the result did not fit while it was written); not retried. Read fewer columns or rows, or smaller files; read_text and read_blob hold each file whole as one value, so read a CSV with read_csv and select its columns |
engine_unavailable |
the sink's engine could not run this time; retried like any transient failure |
empty_replace_refused |
a replace sink's run returned fewer rows than min_rows; the table was not changed |
409 preview_empty |
a new query or engine previewed 0 rows where the sink's recent runs had rows (recent_rows); nothing was changed; allow_empty: true accepts it |
replace_refused |
a replace sink's rows were stored but not committed (a row rejected); the table was not changed |
replace_unsupported, replace_needs_confirmation, mode_conflict, mode_fixed |
why a replace sink was refused, in its remedy |
post_not_declared |
an http_post to a URL under no prefix the catalog declared for POST (host); declare it with lhbox sink post-prefix add |
request_too_large |
a POST body over 64 KiB |
scheme_not_allowed |
a zip:// path that is not one zip archive at an http(s) URL, or archive:// / compressed:// |
403 claim_required |
an unclaimed catalog: a person claims it first |
Routes
The full contracts are in the API reference.
POST /v1/sql/preview {warehouse, sql, table?, engine?, format_version?} -> rows (<= 50), row_count,
rows_shown, truncated, truncated_by, max_rows_applied, max_chars_applied,
values_truncated[{row, column, length}], columns{name, duckdb_type, type}, problems, create_table_schema, create_table_format_version,
format_version_source, fits_table / table_error, egress{requests, bytes_down, hosts (reached), refused?[{host, reason}]},
engine, engine_version
POST /v1/sinks {name, warehouse, table, sql, every, offset?, paused?, create_table?, format_version?,
partition?, key?, engine?, mode?: append|replace, min_rows?, replace_existing?} write level
PATCH /v1/sinks/{id} {sql?, every?, offset?, paused?, key?, engine?, min_rows?, allow_empty?} a new query or
engine is previewed against the table first; 0 rows there after runs with rows: 409
preview_empty unless allow_empty
POST /v1/sinks/{id}/run one run now (a paused sink too); 409 run_already_queued
POST /v1/sql/connections {warehouse, name, url_prefix, kind: bearer|header|basic, header_name?, value}
or {warehouse, name, url_prefix, kind: query, params: {NAME: value, …}} (1 to 8)
or {warehouse, name, integration} an organisation web key, no copy of it
GET /v1/sql/connections ?warehouse= names, prefixes, kinds (param_names for query); never a value
PUT /v1/sql/connections/{name}?warehouse= {value, header_name?}, or {params, replace_all?} for kind query
DELETE /v1/sql/connections/{name}?warehouse= pauses the SQL sinks naming its prefix or host (connection_deleted)
POST /v1/sql/post-prefixes {warehouse, url_prefix} the URLs the catalog's scheduled SQL may POST to; write level
GET /v1/sql/post-prefixes ?warehouse= the catalog's POST prefixes
DELETE /v1/sql/post-prefixes ?warehouse=&url_prefix= the next POST there is refused (post_not_declared)
GET /v1/sinks/{id}/runs state (queued, running, retry_wait, materialised, empty, failed, skipped), committed
{snapshot_id}, rows, requests, bytes_down, elapsed_ms, engine, error{code, message, remedy, …}
On the account page
A catalog's Sinks tab lists its SQL sinks next to the other sinks: the engine (DuckDB 2.0 or 1.5), whether each run replaces the table (and its min_rows), the schedule, the last runs with the engine each ran on, Run now and Pause / Resume.
A person who can write to the catalog can also make and change a SQL sink there:
- New SQL sink: the name, the table (an existing one, or Create the table from the query, with an optional time partition such as
day(ts)), the query, Every, Offset, the engine, and Start paused. Preview runs the query once and writes nothing: the rows, each column with its Iceberg type, whether the rows fit the table, and what was fetched. Create makes the sink. - Edit query and schedule on a sink: the query in an editor, Every, Offset and the engine. Preview runs the edited query against the sink's table; Save changes the sink, and a new query gets the next version.
Every refusal is shown with its remedy, for example an every below your organisation's floor. A person who can only read the catalog sees the sinks and their queries without these controls.
The same tab holds the catalog's Connections (Add a connection). A person can paste an API key there, and an agent only ever uses the connection's name.
Measured: the first sink, 2026-09-29
A real run against the live service, on engine 1.5 with the unnest(features) form of the query (engines came later); ... marks lines left out. The sink and the table were deleted afterwards.
$ lhbox sink preview --catalog analytics --sql @quakes.sql --limit 3
Preview: 7 rows in 65 ms (1 request, 11108 bytes from earthquake.usgs.gov); nothing written.
column duckdb iceberg
quake_id VARCHAR string
event_time TIMESTAMP WITH TIME ZONE timestamptz
magnitude DOUBLE double
...
$ lhbox sink create --catalog analytics --name docs_quakes --table docs_example.quakes \
--sql @quakes.sql --every 1h --offset 7m --create-table --paused
SQL sink docs_quakes created: every 3600 s (offset 420 s) into table docs_example.quakes of catalog analytics (paused).
table: created, format version 2, from the query's columns
$ lhbox sink run docs_quakes --wait
Run 5dceb624: committed, 7 rows.
$ lhbox sink runs docs_quakes
run trigger slot state rows requests bytes ms error
5dceb624 manual 2026-09-29T16:50:12.974Z committed 7 1 11108 62