Transformations

Transformation projects let users manage a folder of SQL models, persist the model graph on the server, inspect lineage, and execute the models through the existing automation runner.

This is intentionally dbt-inspired, not dbt-compatible. There is no Jinja, package manager, dbt manifest support, tests, or dbt data snapshots. Models can materialize as a table, a view, or incrementally (dbt-style materialized: incremental; see below). A Definite "run SQL snapshot" means the exact SQL statements captured for one transformation run; it is not a dbt snapshot model.

Project layout

transformations/
  staging/
    stg_orders.sql
  marts/
    orders.sql

Each .sql file is one model. The model name defaults to the file stem.

-- @definite:
--   schema_name: marts
--   materialized: table
--   compute_profile: large
--   description: Clean order facts for reporting.

select *
from {{ ref("stg_orders") }}

Supported header fields:

The tiny {{ ref("model_name") }} helper is supported for project-internal dependencies. It is not a general Jinja renderer.

Incremental models

Large fact models should not be rebuilt from scratch on every run. With materialized: incremental the engine compiles a delta plan instead of CREATE OR REPLACE TABLE:

-- @definite:
--   schema_name: analytics
--   materialized: incremental
--   incremental_strategy: delete_insert
--   unique_key: [participantid]
--   watermark: updated_at
--   lookback: 6 hours

select * from {{ ref("stg_participants") }}

Two strategies are supported in v1:

• delete_insert (default): stage the rows whose watermark is newer than the target's current maximum (minus the optional lookback) in a session temp table, delete the matching unique_key rows from the target, and insert the staged rows. This is replace-by-key — late updates inside the lookback window converge with no duplicates.
• insert_overwrite: stage the distinct partitions (partition_key) that contain new/changed rows by the same watermark window, delete those whole partitions from the target, and re-insert them from the model output. Use this when changed rows must replace whole days/months, e.g. late status flips on old days.

Advanced: when the rows to rebuild are keyed by something the model output's watermark cannot see (fan-out joins), provide delta_keys_sql — a single SELECT returning the changed keys, with columns in unique_key order. The engine deletes those keys and re-inserts the model output rows that match them. With delta_keys_sql you own delta detection; the first run of such a model should be a --full-refresh (see below) so the target starts complete.

Semantics worth knowing:

• First run is pure SQL: an empty (or just-created) target makes the high-water mark NULL, so the very first incremental run loads everything. Note the delta path stages the full model output in a temp table on that first run — for very large first builds, prefer an initial run with --full-refresh.
• Materialization flips converge: incremental models materialize a table. Flipping table → incremental adopts the existing table without a rebuild; view → incremental drops the stale view first.
• Schema changes need a full refresh: incremental runs INSERT into the existing target, so a model that adds/renames output columns fails until a full_refresh run rebuilds the table (on_schema_change handling is future work).

Full refresh runs

Every incremental model also compiles a full-rebuild variant (the exact CREATE OR REPLACE TABLE a materialized: table model would run). A run queued with full_refresh: true executes those variants instead of the incremental plans — non-incremental steps are unaffected, and the stored pipeline is untouched (scheduled runs stay incremental):

definite transform run core-models --full-refresh

or POST /api/v1/transformations/projects/{id}/runs with {"full_refresh": true}.

A weekly scheduled-off-hours full-refresh run is the recommended backstop for hourly incrementals: CREATE OR REPLACE TABLE rewrites the table compactly, which also clears the DuckLake delete files that key-scoped DELETEs accumulate (compaction and correctness backstop in one).

CLI workflow

# Create a starter project.
definite transform init transformations

# Parse the local project, infer edges, and show the execution plan.
definite transform plan transformations

# Persist the graph and create/update the linked automation pipeline.
definite transform apply transformations --name core-models --compute-profile large

# List persisted projects.
definite transform list

# Inspect the persisted project, models, edges, and execution order.
definite transform get core-models --format json

# List immutable applied versions for a project.
definite transform versions core-models --format json

# Inspect a historical version; add --include-ast only for parser debugging.
definite transform get core-models --version 3 --format json
definite transform get core-models --version 3 --include-ast --format json

# Show source/output lineage, optionally filtered to one project or table.
definite transform lineage --project core-models --format json
definite transform lineage --source-table orders --format json

# Queue a run through the linked automation pipeline.
definite transform run core-models --format json

# Rebuild incremental models from scratch for this one run.
definite transform run core-models --full-refresh --format json

apply sends the manifest to /api/v1/transformations/projects/{slug}/manifest. The API stores the project, models, and edges in Postgres, then compiles the models into ordered automation sql steps. The linked automation owns schedule, execution, logs, retries, and sharing.

apply treats pipeline state as preserve-unless-specified. Without --cron, the stored cron and timezone are untouched, so re-applying model changes cannot silently stop scheduled runs; --cron sets the schedule (--cron-timezone optionally changes the timezone, otherwise the stored one is kept) and --clear-cron explicitly removes it. The enabled flag works the same way: apply leaves a paused pipeline paused unless --enable or --disabled is passed. Table output ends with a schedule: line stating the resulting schedule and whether it was set, preserved, or cleared; JSON/CSV consumers should read data.project.automation_pipeline.cron_schedule instead. Preservation is enforced by the API server — applying against a server older than this release still clears an unspecified schedule.

The same preserve-unless-specified rule covers the rest of the project's settable state. Without --compute-profile, the project's stored profile is kept (--compute-profile default resets it explicitly). Without --description, the stored description is kept; --description "" clears it. A pipeline renamed on the automations page keeps its custom name permanently — a project rename only updates pipelines still carrying the derived Transforms: … name. Note that --name defaults to the local directory name, so applying from a renamed directory renames the project. Two caveats: --default-schema (default main) is resolved into headerless models by the CLI at build time, so changing it re-points where those models materialize — the apply response carries a default_schema_changed warning when this happens; and visibility grants are seeded only when the apply creates the pipeline, so sharing changes made in the product are never re-asserted by a re-apply.

plan warns when a model reads a table in a schema the project writes to (the default schema or any model's schema_name) that no model in the project produces — typically a stale leftover table or a missing model that would otherwise fail mid-run. The warning lists each table with the models that read it; in --format json it is carried as a warnings array, and in --format csv it goes to stderr so stdout stays machine-parseable. It is a warning, not an error: reads from a shared schema that another project populates can be intentional. apply reruns the same check server-side with DuckDB's parser and returns it as warnings in the response, persisted with the version's manifest.

Use --format json when handing results to another tool, to Fi, or to an external review workflow. The JSON payload preserves project ids, linked automation ids, version ids, run ids, lineage edges, source refs, parser status, compute-profile choices, AST summaries, and exact run SQL snapshots when those fields are returned by the deployed API.

The version and lineage CLI commands require a deployment API that exposes the matching version/lineage endpoints. Older API builds can still plan, apply, get, list, and run transformation projects, but may not return version history, source lineage, or full AST payloads.

Versioning and run snapshots

A transformation project has a current definition and may have multiple applied versions over time. Treat each applied version as the reviewable definition that was current at the moment of apply: model SQL, model metadata, dependency edges, compute-profile routing, parsed source lineage, and parser metadata all belong to that version.

When a run is queued, the automation layer snapshots the exact definition it will execute into the automation run record, along with a definition hash. For a transformation-generated pipeline, that definition contains the ordered sql steps: the fully compiled CREATE OR REPLACE TABLE or CREATE OR REPLACE VIEW statement for each model, plus the compute_profile each step will use. Debug past runs from this run SQL snapshot, not from whatever the latest local files or latest project version say now. When the definition carries transformation_project_id and transformation_version_id, those ids identify which transformation definition produced the run.

This distinction matters:

• Editable source is the SQL model text a user should modify in a project folder or source editor.
• Applied version SQL is the server-side definition saved by an apply.
• Run SQL snapshot is the immutable SQL that a specific run attempted.

If a model is edited after a failed run, the latest project SQL may no longer match the SQL that failed. Use the run SQL snapshot for incident review, audit, and exact reproduction.

Lineage

Transformation lineage has two layers:

• Model lineage is project-internal: depends_on, ref(), and inferred model-to-model edges such as stg_orders -> orders.
• Source lineage is external: lakehouse source tables that a model reads, such as raw.orders -> stg_orders.

Source lineage is parsed server-side at apply time with DuckDB's own parser (json_serialize_sql) — the same engine that runs the SQL, so the lineage is authoritative. Each source ref records the raw reference, resolved source schema and table, reference kind, and whether the reference matched another internal model. If DuckDB cannot serialize a model's SQL (a non-SELECT body, or syntax it rejects), the server falls back to a token-scan and marks the model degraded; those refs are lower confidence. Dynamic table names or unusual table functions may still produce partial lineage or a degraded parse.

Use explicit depends_on for model-to-model edges that the parser could miss. Source lineage is for graphing and impact analysis; table-level query permissions are still enforced by the query execution path.

The UI should make the distinction visible. Source tables and transformation models are different node types, and a run detail should be able to show lineage for the version and SQL snapshot that actually ran.

AST summary and full AST

DuckDB's parser produces both a compact AST summary and, when needed, the full serialized AST for the DuckDB dialect.

The AST summary is the default product surface. It includes stable facts such as parser name/version/dialect (duckdb / the DuckDB library version / duckdb), parse status, statement count, referenced tables (with the internal/external flag), CTE names, fallback mode, and any parse error. The summary is small enough for lists, model drawers, lineage graph sidebars, and Fi context.

The full AST is a debugging aid. It can be large, parser-version-specific, and awkward to read. Use this when a historical version needs full parser detail:

definite transform get <project> --version <n> --include-ast --format json

UI flows should lazy-load full AST only when a user asks to inspect parser details, and Fi should summarize relevant nodes instead of pasting the whole object into chat.

Server graph

The server persists:

• one transformation project row;
• one model row per SQL file in the current graph;
• one edge row per project-internal dependency;
• a linked automation pipeline containing the compiled execution steps;
• automation run definition snapshots and hashes for exact run reproduction;
• version, run-snapshot, source-lineage, and AST metadata where enabled by the deployed API version.

The graph can be read with:

definite transform get core-models --format json

or via:

GET /api/v1/transformations/projects/{id_or_slug}

UI concepts

The editor-only /transformations page uses the same server graph and run records as the CLI. The page is organized around:

• Project filters: text search plus status, source schema/table, and output schema/table filters.
• Projects list: project name, slug, model count, edge count, last run, and source/output hints.
• Project detail: models, model dependency edges, selected-model SQL, source/output table refs, versions, and linked automation metadata.
• Actions: refresh, queue a transformation run, open the linked automation, and open the latest automation run.

Exact run SQL snapshots and full AST payloads are detail/debug payloads. They should stay out of default lists and load only when a user explicitly opens the relevant run, version, or parser-debug view.

Where transform SQL executes & sizing the runner

Transform projects compile to one automation sql step per model. The API pod never executes transform models — it parses, plans, and serves interactive queries. The job runner orchestrates the model steps, and the SQL execution surface depends on the compiled compute_profile values:

• Default profile: the step executes on the job-runner pod's embedded DuckDB via AutomationExecutor.execute_sql and lakehouse.query.
• Uniform non-default profile: if every compiled SQL step in the transform run declares the same non-default profile (the normal project-level compute_profile case), the runner leases one BurstSqlSession for the whole run. Every model statement runs through that warm burst sandbox's persistent /sql-session worker, so the DuckDB connection and lake attach are reused across the DAG.
• Mixed profiles: per-model overrides that produce different profiles keep the existing per-step burst path. Each profile-bound step leases, runs one statement, and releases its own sandbox.

Everything below is about the default in-runner path. For a uniform profile-bound DAG, size the selected compute profile instead; the job-runner pod remains the orchestrator and does not carry the heavy DuckDB plan.

What bounds a heavy model

DuckDB executes each compiled CREATE OR REPLACE TABLE with a memory budget of ~40% of the job-runner container's memory limit and threads equal to the CPU limit (rounded up). Both are handed down through the pod environment and can be overridden for the job-runner alone. Hash joins / sorts / large aggregates that exceed the budget spill to the pod's /scratch volume. These Helm values decide whether a heavy model completes:

The spill values are per-component: jobRunner.spill.* overrides the shared lakehouse.spill.* for the runner alone, so the runner can carry several times the API pod's spill without resizing both. When maxTempDirectorySize is left empty the chart derives it from the scratch PVC size (floor of 90%, as a binary GiB quantity), so the cap can no longer drift past the volume it bounds.

Two defaults keep large writes in these limits:

• DuckDB preserve_insertion_order is off by default on the job-runner engine (jobRunner.duckdb.preserveInsertionOrder), on the definite_lakehouse SDK engines for attach_lake python steps, burst sandboxes, and connector loaders, and on the fi-sandbox burst SQL runner (the last three through lakehouse.duckdb.preserveInsertionOrder). Ordered INSERT/CTAS buffers cannot spill, and the memory budget does not count them. The api pod's query engine is the one exception: it keeps the DuckDB default (true) and has no configuration value. To get the DuckDB default on the other engines, set the applicable value to true, or use SET preserve_insertion_order = true in a step. When the setting is off, CREATE TABLE ... AS SELECT ... ORDER BY sorts the query stream but does not sort the stored table — parallel insert sinks can write blocks out of sequence. A read that must have an order must use ORDER BY.
• attach_lake python steps get the DuckDB limits of their container from the DUCKDB_* variables (memory fraction and bytes, threads, and spill-to-disk settings). The steps do not calculate their own limits. The one DuckDB limit setting that the job-runner does not forward to each step is the absolute DUCKDB_MEMORY_LIMIT value: that value is a memory budget for the entire engine, and each step would get the full value again if it were forwarded. To give one step an exact budget, put DUCKDB_MEMORY_LIMIT in the config.env block of that step; the config.env block can override each of the other values too (see helm/values.yaml).

Worked example (real 100M+-row migration)

A 41-model DAG over 134M / 92M / 36M-row source tables:

• Chart defaults (3Gi runner, 50Gi scratch, 45GB temp cap): OOM'd mid-DAG, hours in — model 9/40 on one attempt, 18/40 on another (could not allocate block at 28.7GiB).

• Completed config — 41/41 models in 131 minutes:

  resources:
    jobRunner:
      replicas: 1
      resources:
        requests: { cpu: "2", memory: 8Gi }
        limits:   { cpu: "8", memory: 48Gi }
  
  jobRunner:
    duckdb:
      # Runner-only: does not change API queries or Fi sandboxes.
      memoryFraction: "0.4"
      # Fewer threads than CPUs: each concurrent operator pipeline holds
      # its own working set, so 4 threads at 48Gi left each join more
      # memory than 8 did.
      threads: 4
    spill:
      size: 250Gi   # derived max_temp_directory_size: 225GiB
  

  The heaviest step — a 42M x 134M x 92M three-way join — ran 103.7 minutes pinned at 47.8Gi of the 48Gi limit while fully spilling. At this scale the spill volume is doing real work; the defaults could never absorb it.

Rules of thumb from that run:

• Budget runner memory against the largest single join, not the DAG as a whole — models execute serially through the automation.
• If a model OOMs before /scratch fills, raise the runner memory limit. If it dies on spill exhaustion (an error naming max_temp_directory_size, or No space left on device under /scratch), raise jobRunner.spill.size — the temp cap follows automatically unless you pinned it.
• Threads default to the CPU limit. Lowering jobRunner.duckdb.threads trades speed for per-operator memory headroom and is often the cheapest fix for a single giant join. jobRunner.duckdb.memoryFraction similarly scopes a different memory fraction to the runner without changing API or Fi limits.

For new large DAGs, prefer a project-level non-default compute_profile so the whole SQL run uses one sized, warm burst sandbox. The runner sizing knobs above remain the supported controls for default-profile runs and deployments that intentionally keep transform SQL in-process.

Current limits

• The runner still executes models as a linear topological order through Automations; DAG-native parallel execution is not implemented.
• Dependency inference is intentionally conservative. Use ref() or depends_on for reliable project-internal edges.
• Source lineage and AST summaries are parser-derived and best-effort. Dynamic SQL or unsupported syntax may produce partial lineage or a parse error.
• Full AST output is for debugging, not a stable public data model.
• Versioning captures server-side transformation definitions. Use git or another source-control workflow for branching, code review, and local author history.
• Incremental v1 supports delete_insert and insert_overwrite. merge and automatic on_schema_change handling are planned follow-ups; an output schema change today requires a --full-refresh run.
• There is no dbt compatibility layer.