DataFrame builder#

db.table(...) starts a lazy query you build up with methods instead of writing a SQL string. Nothing runs until a terminal method like .collect().

from h5i_db import col

(db.table("trades", as_of="2026-07-01T00:00:00Z")
   .filter(col("symbol").is_in(["AAPL", "MSFT"]))
   .group_by("symbol")
   .agg(col("price").mean().alias("px"))
   .collect())

The builder is a compiler, not a second engine. Every verb lowers to SQL run through Database.sql(), so a built query sees the same session, the same table functions and the same version pins as the equivalent string. .sql() shows exactly what it produced:

SELECT "symbol", avg("price") AS "px"
FROM h5i('trades', '2026-07-01T00:00:00Z')
WHERE "symbol" IN ('AAPL', 'MSFT')
GROUP BY "symbol"

Reading a table#

table(name, version=None, as_of=None, snapshot=None) -> LazyFrame

Verbs#

Every verb returns a new frame, so a partial pipeline is safe to reuse as a base for several queries.

Strings are column names wherever an expression is accepted, so .group_by("symbol") and .group_by(col("symbol")) are the same thing. Keyword arguments name the result: with_columns(ret=col("close") - 1).

with_columns adds. Naming a column that already exists is an error, because SELECT * would then carry two of it. Say so explicitly to overwrite one:

db.table("trades").with_columns(price=col("price") * 2, replace="price")

SELECT * EXCEPT ("price"), "price" * 2 AS "price"
FROM "trades"

The builder never reads the schema, which is what keeps it lazy, so a replace name that does not exist is caught by the engine rather than at build time.

Expressions#

col(name) references a column, lit(value) a constant. The name is one identifier and is never split on ., so a column really named a.b works; pass col("price", relation="l") to qualify a side of a join.

from h5i_db import col, lit, when

col("price") * col("size")              # arithmetic
(col("price") > 100) & (col("size") < 5)   # & | ~ , not and/or/not
col("symbol").is_in(["AAPL", "MSFT"])
col("px").is_null()
col("ts").between(t0, t1)
col("symbol").like("A%")
col("size").cast("DOUBLE")
when(col("price") > 100).then(lit(1)).otherwise(lit(0))

Identifiers and literals are quoted at a single site, and identifiers are always quoted so case survives (col("Symbol") finds a field named Symbol, which bare SQL would fold to lowercase). A value containing '; DROP TABLE trades; -- is a string, never syntax.

Aggregates are methods: .sum(), .mean(), .min(), .max(), .count(), .n_unique(), .std(), .var(), .median(), .quantile(q), .first(order_by=), .last(order_by=). So are scalars: .abs(), .log(), .log10(), .exp(), .sqrt(), .sign(), .round(n), .floor(), .ceil(), .coalesce(...), .greatest(...), .least(...). Plus count_star(), vwap(price, size), wavg(weight, value) and time_bucket(interval, ts) as functions.

A when(...).then(...) chain is already a complete expression (a CASE with no ELSE yields NULL), so it can be aliased without .otherwise().

The idiomatic OHLCV query, built:

from h5i_db import col, time_bucket, vwap

(db.table("trades")
   .group_by(time_bucket("5m", col("ts")).alias("bar"), "symbol")
   .agg(col("price").first("ts").alias("open"),
        col("price").max().alias("high"),
        col("price").min().alias("low"),
        col("price").last("ts").alias("close"),
        col("size").sum().alias("volume"),
        vwap(col("price"), col("size")).alias("vwap"))
   .sort("bar"))

SELECT time_bucket('5m', "ts") AS "bar", "symbol",
       first_value("price" ORDER BY "ts") AS "open", max("price") AS "high",
       min("price") AS "low", last_value("price" ORDER BY "ts") AS "close",
       sum("size") AS "volume", vwap("price", "size") AS "vwap"
FROM "trades"
GROUP BY "bar", "symbol"
ORDER BY "bar"

Rolling and cross-sectional operators#

Rolling methods take a window and an order_by, and optionally a partition_by. The window is either a row count or a duration string ('30s', '5m', '1.5h', '1d', '1w', '1mo', '1y').

col("close").rolling_mean(20, order_by="ts", partition_by="symbol")

avg("close") OVER (PARTITION BY "symbol" ORDER BY "ts"
                   ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)

Unlike the rolling_avg SQL sugar, these carry a PARTITION BY, so they do not mix symbols on a multi-symbol table.

Cross-sectional methods rank a value against its peers at the same instant, so they take the bucket to compare within:

For a raw window frame, .over(partition_by=, order_by=, rows=, duration=) applies to a single aggregate, where rows is a trailing count or a (preceding, following) pair with None for unbounded. SQL attaches OVER to one function call, so window each part of a compound expression separately: col("a").sum().over(...) / count_star().over(...), not (col("a").sum() / count_star()).over(...), which is rejected.

Joins#

.join() renders both sides as subqueries aliased l and r. Those aliases are part of the contract: reach a specific side with col("price", relation="l"), or an arbitrary condition with predicate=sql_expr('l."a" > r."b"').

how= is inner (default), left, right, full, cross, semi or anti. Keys come from on= (same name both sides) or left_on=/right_on=.

Comparing one table at two pinned versions, the "same query across N versions" pattern:

def mean_px(version):
    return (db.table("trades", version=version)
              .group_by("symbol")
              .agg(col("price").mean().alias("px")))

drift = (mean_px(1).join(mean_px(2), on="symbol")
         .select(col("symbol", relation="l").alias("symbol"),
                 (col("px", relation="r") - col("px", relation="l")).alias("drift")))

SELECT "l"."symbol" AS "symbol", "r"."px" - "l"."px" AS "drift"
FROM (
  SELECT "symbol", avg("price") AS "px"
  FROM h5i('trades', 1)
  GROUP BY "symbol"
) AS "l"
INNER JOIN (
  SELECT "symbol", avg("price") AS "px"
  FROM h5i('trades', 2)
  GROUP BY "symbol"
) AS "r"
  ON "l"."symbol" = "r"."symbol"

join_asof(other, on=None, by=None, direction="backward", tolerance=None,
          left_on=None, right_on=None) -> LazyFrame

db.table("trades").join_asof(db.table("quotes"), on="ts", by="symbol",
                             tolerance=5_000_000)

SELECT *
FROM asof_join('trades', 'quotes', 'ts', 'ts', 'symbol', 'backward', 5000000)

How pipelines become SQL#

Most pipelines compile to one flat SELECT. A stage that reads a column an earlier stage computed gets its own level, because SQL resolves WHERE and SELECT against the FROM, not against sibling projections:

(db.table("bars")
   .with_columns(ret=col("close") / col("open") - 1)
   .filter(col("ret") > 0)          # reads a computed column -> subquery
   .sort("ret", descending=True)
   .limit(10))

SELECT *
FROM (
  SELECT *, "close" / "open" - 1 AS "ret"
  FROM "bars"
) AS "_s1"
WHERE "ret" > 0
ORDER BY "ret" DESC
LIMIT 10

Filtering a base column instead stays flat, and independent with_columns calls coalesce into one projection. Aggregation, LIMIT and DISTINCT always close a level, since whatever follows acts on their output. So does a projection holding a window function or aggregate: SQL evaluates WHERE before the select list, so filtering in the same level would recompute the window over only the surviving rows.

Because levels close, a stage can only see what the stage before it emitted. Filtering or sorting by a column an earlier .select() dropped is an error naming that column, as it would be for a DataFrame.

The generated SQL is deterministic, so it is safe to snapshot-test or diff.

Escape hatch#

sql_expr() embeds a raw fragment anywhere an expression is accepted. Full SQL coverage through builder verbs is deliberately not a goal, so reach for this rather than waiting for a method:

from h5i_db import sql_expr

(db.table("trades")
   .group_by("symbol")
   .agg(sql_expr("approx_percentile_cont(price, 0.99)").alias("p99")))

Its text is inserted verbatim, so it is the one place quoting is yours to get right; never build it from untrusted input. Because its column references are opaque to the builder, it conservatively forces a subquery when the current stage defines any computed name.

When a pipeline outgrows the builder, .sql() gives you the query to paste into db.sql() and keep going from there.