Skip to content

Run a model with DataFusion and append to its table #10

Open

Description

@codecapo

Run one model's SQL with DataFusion against Iceberg tables and write the result to its target table.

Crate: crates/engine

Done when

  • Registers upstream Iceberg tables with DataFusion under their layer names (raw.orders and bronze.orders both work)
  • Executes the model's query and streams Arrow batches to the target
  • Creates the target table on first run with the query's schema
  • Rows are appended to the target
  • Tests for append and a schema created from the query
  • Writes use create then INSERT INTO; CREATE TABLE AS in model SQL is rejected with a clear error (it silently drops rows upstream)
  • Timestamps in model output are stored as microseconds

Merging on @unique_key is #35 and @partition_by is #36.

Depends on: #6, #7
Blocks: #12, #13, #35, #36

Activity

  1. added this to the 0.1.0 milestone on Oct 29, 2026
  2. changed the title [-]Run a model with DataFusion and write its table[/-] [+]Run a model with DataFusion and append to its table[/+] on Oct 29, 2026
  3. codecapo commented on Oct 30, 2026

    @codecapo
    OwnerAuthor

    Spike findings (engine)

    A spike proved this issue end to end, including a 1B-row run (raw to clean to ready in 17.3 min, 5.7 GB peak RSS, every count and sum exact).

    • Layer names: a CatalogProvider that wraps IcebergCatalogProvider and maps names through Layer::from_namespace makes raw.orders and bronze.orders resolve to one table. Look tables up lazily rather than snapshotting the list.
    • Write path: plan the SQL, create the target with its schema, then INSERT INTO. That streams through IcebergWriteExec and makes one commit. Don't go through Warehouse::append, which needs every batch in memory.
    • Never use CREATE TABLE AS. It silently drops rows: the upstream ensure_table_is_empty only checks partition 0. Reject CTAS in model SQL. To be reported upstream.
    • Timestamps: cast(x as timestamp) gives nanoseconds, which needs Iceberg format v3. Normalise outputs to microseconds.
    • If the insert fails after the create, an empty table is left behind; the runner (In-process runner: stage, run tasks, commit once #13) handles that.
    • Always use a multi-thread Tokio runtime.

    Points: 8 -> 5. Added two criteria below.

  4. codecapo commented on Nov 6, 2026

    @codecapo
    OwnerAuthor

    The design review of 2026-11-01 touches this issue. It asks the engine to plan every model under one start time per job and never to depend on the order rows arrive in, while reading pinned input ranges, the new _source_row column and the RFC text move to new tickets.

    Suggested additions to the Done-when:

    • The engine plans and runs each model under a start time its caller supplies, on DataFusion 55.2 (Cargo.toml:28 at a0b5e6c): it sets query_execution_start_time through SessionState::execution_props_mut(), plans with SessionState::create_logical_plan, wraps the plan in DataFrame::new on that same state and executes that DataFrame, never through SessionContext::sql or SessionContext::execute_logical_plan, which both take a fresh copy of the state, with its start time reset to the wall clock, on every call. now(), current_timestamp and current_date in @check expressions (Row checks and quarantine tables #12) then fold to values taken from the job's start time, which a rerun needs to repeat the split between target and quarantine (docs/adr/0006-ordered-job-commits.md:71-72); storing that time with the job id and supplying it again on a rerun belongs with the runner (In-process runner: stage, run tasks, commit once #13), under the rule docs: revise RFC 0001 with the incremental rules (decision D2) #73 adds to RFC 0001.
    • Test: a query selecting now() and current_date, planned twice under the same supplied start time with a pause in between, returns identical values both times, and now() equals the supplied start time.
    • Nothing in the engine relies on the order rows arrive in or on _loaded_at: scan_at hands every data file to one iceberg reader at its default concurrency (crates/storage/src/lib.rs:644-647), so rows from different files can arrive in a different order on each read, and _loaded_at is one wall-clock reading per read batch (crates/connectors/src/csv.rs:207-211, :228). Tests compare sorted rows, and a step that needs an order (Row checks and quarantine tables #12, Merge rows on @unique_key #35) uses the RFC 0001 ordering key from docs: revise RFC 0001 with the incremental rules (decision D2) #73: Raw snapshot sequence number, then _source_file, then _source_row.

    Work that moved to new tickets:

    Source: design review of 2026-11-01, findings C15 and C17, with section 2.5.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions