Persist Statements

Persist statements are used to materialize query results into a database table. They execute the select and write results to storage.

Syntax

persist_statement: auto_persist | full_persist
auto_persist: ("append" | "overwrite" | "persist") IDENTIFIER where?
full_persist: ("append" | "overwrite" | "persist") IDENTIFIER? "into" IDENTIFIER ("by" column_list)? "from" select_statement

Persist Modes

• persist: Default mode, creates or replaces the table
• overwrite: Replaces all data in the target table
• append: Adds new rows to an existing table

Auto Persist (Short Form)

The short form automatically persists a datasource:

persist my_datasource;

With a where clause to filter what gets persisted:

persist my_datasource where date > '2024-01-01';

Full Persist (Long Form)

The full form allows custom queries and explicit target naming:

persist into my_table from
select
    user.id,
    user.name,
    count(order.id) -> order_count
;

Partition By

For large datasets, use partition by to partition the output:

persist into sales_summary by year, month from
select
    order.year,
    order.month,
    sum(order.total) -> monthly_total
;

Example

import customer as customer;
import order as order;
persist into customer_order_summary from
select
    customer.id,
    customer.name,
    count(order.id) -> total_orders,
    sum(order.amount) -> total_spent
;