Writing tables
A FeatureQL query can write its result to a warehouse table. End the query with INSERT OVERWRITE TABLE schema.table or INSERT INTO TABLE schema.table. The compiler emits the SQL for the backend you query: DuckDB, Trino, serving, Postgres, Snowflake, Databricks, BigQuery, or ClickHouse. The same statement creates the same table everywhere.
Use it to seed data from inline rows (test fixtures, tutorials, benchmark datasets), or to store a computed result that other tools scan as an ordinary table.
Write a table
The query is an ordinary DEFINE / RETURN / FOR query. INLINE_COLUMNS holds the rows, BIND TO names the key, and FOR binds the key values:
DEFINE
ID := INPUT(BIGINT),
T := INLINE_COLUMNS(
id BIGINT BIND TO ID,
city VARCHAR,
opened DATE,
revenue DOUBLE
FROM TSV(
id city opened revenue
1 Barcelona 2024-03-01 1250.5
2 Madrid 2024-05-15 980.0
3 Valencia NULL NULL
)
)
RETURN
ID,
CITY := T[city],
OPENED := T[opened],
REVENUE := T[revenue]
FOR
ID := BIND_VALUES(SEQUENCE(1, 3))
INSERT OVERWRITE TABLE docs_tables.stores
;| TABLE_NAME VARCHAR | ROW_COUNT BIGINT |
|---|---|
| docs_tables.stores | 3 |
INSERT OVERWRITE TABLE creates the schema if it is missing, drops the table if it exists, and creates it from the query result. The statement returns one row: the table and the number of rows written. Running it again gives the same table and the same row.
What the table gets:
- Columns: the
RETURNnames in lower case.FROM TABLE(...)reads them with the same rule, so a table written here reads back on every backend. - Types: the physical types the compiler reads for the returned types. A
ROWisJSONBon Postgres andOBJECTon Snowflake. - Rows: one per bound key. A bound key with no data row writes a row of
NULLs. A data row whose key is not bound is not written. The row count shows both.
BIND TO declares one row per key, so a repeated key in the inline rows is a compile-time error.
Read it back
The table is an ordinary host table. Map it like any other:
DEFINE
ID := INPUT(BIGINT),
S := EXTERNAL_COLUMNS(
id BIGINT BIND TO ID,
city VARCHAR,
opened DATE,
revenue DOUBLE
FROM TABLE(docs_tables.stores)
)
RETURN
ID,
CITY := S[city],
OPENED := S[opened],
REVENUE := S[revenue]
FOR
ID := BIND_COLUMNS(id FROM TABLE(docs_tables.stores))
ORDER BY ID
;| ID BIGINT | CITY VARCHAR | OPENED DATE | REVENUE DOUBLE |
|---|---|---|---|
| 1 | Barcelona | 2024-03-01 | 1250.5 |
| 2 | Madrid | 2024-05-15 | 980 |
| 3 | Valencia | NULL | NULL |
Append rows
INSERT INTO TABLE appends to an existing table. The returned row count is the whole table:
DEFINE
ID := INPUT(BIGINT),
T := INLINE_COLUMNS(
id BIGINT BIND TO ID,
city VARCHAR,
opened DATE,
revenue DOUBLE
FROM TSV(
id city opened revenue
4 Seville 2024-09-01 410.25
)
)
RETURN
ID,
CITY := T[city],
OPENED := T[opened],
REVENUE := T[revenue]
FOR
ID := BIND_VALUES(ARRAY[4])
INSERT INTO TABLE docs_tables.stores
;| TABLE_NAME VARCHAR | ROW_COUNT BIGINT |
|---|---|
| docs_tables.stores | 4 |
The query's columns must match the table. A mismatch is the engine's error, not a compile-time one.
The target name
The name is host text, like FROM TABLE(...). It is kept as written: t, schema.t, or catalog.schema.t, each part plain or double-quoted. OVERWRITE creates the schema named before the table. It never creates a catalog.
Keep schema, table, and column names in lower case. BigQuery and ClickHouse match names exactly, and on ClickHouse two databases whose names differ only by case collide.
Limits
- One statement compiles to at most 500,000 characters of SQL (
UE/TABLE-SINK-QUERY-TOO-LARGE). BigQuery refuses a query near 1M characters, and ClickHouse limits the parsed query. Write a large table in parts:INSERT OVERWRITE TABLEwith the first key range, thenINSERT INTO TABLEwith the next ones. - The clause ends a top-level query. It cannot appear inside
CREATE FEATURES … AS, a view body, or a subquery. - The clause only generates SQL. The backend connection's own rights decide whether the write is allowed.
Next
Inline data formats and BIND TO are covered in Mapping data sources . To expose a query as a relation that recomputes on every scan instead of a stored table, use a persisted view . To run FeatureQL inside an SQL statement you write yourself, use hybrid queries .