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:

FeatureQL
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
;
Result
TABLE_NAME VARCHARROW_COUNT BIGINT
docs_tables.stores3

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 RETURN names 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 ROW is JSONB on Postgres and OBJECT on 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:

FeatureQL
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
;
Result
ID BIGINTCITY VARCHAROPENED DATEREVENUE DOUBLE
1Barcelona2024-03-011250.5
2Madrid2024-05-15980
3ValenciaNULLNULL

Append rows

INSERT INTO TABLE appends to an existing table. The returned row count is the whole table:

FeatureQL
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
;
Result
TABLE_NAME VARCHARROW_COUNT BIGINT
docs_tables.stores4

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 TABLE with the first key range, then INSERT INTO TABLE with 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 .