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. There is no cron job, server or engine of your own to keep running: "read this feed every 20 minutes and keep this table populated" is one command. 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.

Availability. Scheduled SQL is live, and it is switched on per organisation by LakehouseBox (the plan limit sql_sinks, 0 until then; lhbox usage --full shows yours and the per-run budgets). To have it switched on, write to hello@lakehousebox.com with your organisation's handle. Until then the commands below answer 403 not_enabled.

A first sink, end to end

The US Geological Survey publishes the earthquakes of the last hour as a GeoJSON file. This query turns it into one row per earthquake (quakes.sql):

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',
                     columns = {features: 'JSON[]'}))

1. Preview it. The query runs once on the executor and nothing is written: you see the rows, each column's Iceberg type, and (with --table) whether they fit an existing table.

$ 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
...

2. Create the sink. --create-table makes the table from the query's columns; --every 1h --offset 7m runs it at seven minutes past every hour (UTC). --paused holds the schedule until you have seen one run.

$ 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

3. Run it once, now, and look at the run.

$ 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

4. Start the schedule: lhbox sink resume docs_quakes.

5. Read the table without duplicates. Runs append, and this feed covers a whole hour, so a quake read by two runs is stored twice. Every row carries __ingest_ts, the time its run was committed; 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

(The output above is a real run on 2026-09-29 against the live service; ... marks lines left out. The sink and the table were deleted afterwards.)

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) with DuckDB 1.5.5's readers: read_json, read_ndjson, read_csv, read_parquet, read_text, read_blob, or FROM 'https://…'. Several URLs may be read in one call (a list, or a list comprehension over page numbers), and a URL may contain current_date. LakehouseBox writes the rows; the query never writes. It 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;
• a budget per run: 60 s, 768 MB, 1,000 requests and 1 GiB downloaded by default, each configurable per organisation;
• a rate per destination host shared by every LakehouseBox job.

Not available:

• local files, settings, extensions, and secrets other than the catalog's connections (below);
• request bodies (POST APIs);
• a URL computed from another source's rows;
• your catalog's own tables. Joining with them is planned.

Pin the types of a JSON source (columns = {…}, or JSON and ->>): inference can change between fetches.

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).
• The prefix is https://, a host name, 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/).
• Who stores it is up to you: an agent with the CLI (--value-env VAR, --value @FILE or @-; a person at a terminal with neither is asked, hidden), or a person on the account page (the catalog's Sinks tab, Connections: paste the key, and the agent only ever needs the connection's name). Never on the command line itself.
• The value is write-only (no route, command or page shows it again) and is stored encrypted.
• A source that answers 401 fails the run with auth_failed, naming the connection (not retried: replace the value); a 401 where no connection applies is auth_required. A 403 is retried like any source error (it is as often a rate limit or a challenge as a bad key) and names the connection.
• 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 with the column and a sample value named. create_table makes the table from the query's columns (format version 2). A sink feeds an unpartitioned format-version-2 table.

The schedule

every is at least 5 minutes by default, and 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 (the source or the executor unavailable, a timeout) is retried within the slot after 30 s, 2 min and 8 min.
• A sink whose runs all failed for 24 hours pauses itself (paused_reason: failing). So does one whose table is gone (table_missing), or whose organisation lost the feature (not_enabled).
• Runs append. A source that republishes rows lands them again at every run: read the latest per key with QUALIFY row_number() OVER (PARTITION BY <key> ORDER BY __ingest_ts DESC) = 1.

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   /   lhbox sink resume madrid_air   /   lhbox sink update madrid_air --sql @v2.sql

Errors

Every error names what to change: egress_refused (the host and the reason), budget_exceeded, rate_limited, type_mismatch (column, expected type, a sample), unknown_column, sql_error (DuckDB's message), query_timeout, output_too_large, auth_failed (the connection), auth_required, each with a remedy.

Routes

The full contracts are in the API reference.

POST  /v1/sql/preview          {warehouse, sql, table?} -> rows (<= 50), row_count, columns{name, duckdb_type, type},
                               problems, create_table_schema, fits_table / table_error, egress{requests, bytes_down, hosts}
POST  /v1/sinks                {name, warehouse, table, sql, every, offset?, paused?, create_table?}   write level
PATCH /v1/sinks/{id}           {sql?, every?, offset?, paused?}   a new query is previewed against the table first
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}
GET   /v1/sql/connections     ?warehouse=  names, prefixes, kinds; never a value
PUT   /v1/sql/connections/{name}?warehouse=   {value, header_name?}
DELETE /v1/sql/connections/{name}?warehouse=  pauses the SQL sinks naming its prefix or host (connection_deleted)
GET   /v1/sinks/{id}/runs      state (queued, running, retry_wait, materialised, empty, failed, skipped), committed
                               {snapshot_id}, rows, requests, bytes_down, elapsed_ms, error{code, message, remedy, …}

On the account page

A catalog's Sinks tab lists its SQL sinks next to the other sinks: the schedule, the last runs, and Run now and Pause / Resume. The same tab holds the catalog's Connections (Add a connection), so a person can paste an API key there and an agent only ever uses the connection's name. Creating the sink itself is done with the CLI or the API.