A rebuildable pipeline: every step is SQL

There are forty operations in the workbench: filter, join, pivot, window function, regex capture, fill missing, and so on. None of them transform data. Each one emits a string of SQL, and the SQL is the only thing that ever touches a row.

That constraint is the whole design. It means a pipeline is a list of queries, a pipeline can be rebuilt from nothing, and any step can be read, edited or replaced by a person who would rather write the SQL themselves. It also means a specific set of problems, which is what this post is actually about.

The shape

A view in the interface has a source table and an ordered list of steps. Rebuilding turns that into a chain of database relations, each reading from the last:

orders                    -- the loaded file's table
v_<viewId>_step_0         -- SELECT ... FROM orders
v_<viewId>_step_1         -- SELECT ... FROM v_<viewId>_step_0
v_<viewId>_step_2         -- SELECT ... FROM v_<viewId>_step_1
v_<viewId>_output         -- SELECT * FROM v_<viewId>_step_2

The grid reads from _output, never from a step directly, so the rest of the application does not need to know how long the chain is or whether it changed.

Rebuilding is total. Drop the output view first to break the dependency chain, then drop every step in reverse order, then create them all again:

const outputViewName = `"${prefix}_output"`;
try { await executeQuery(`DROP VIEW IF EXISTS ${outputViewName}`); } catch { /* ignore */ }

for (let i = steps.length; i >= 0; i--) {
  const name = `"${prefix}_step_${i}"`;
  try { await executeQuery(`DROP TABLE IF EXISTS ${name}`); } catch { /* ignore */ }
  try { await executeQuery(`DROP VIEW IF EXISTS ${name}`); } catch { /* ignore */ }
}

Reverse order matters because a view cannot be dropped while another view depends on it. The loop starts at steps.length rather than length - 1 to clean up a step that existed before a deletion and would otherwise be orphaned in the catalog. Both drops are attempted for each name because a step may have been a view last time and a table this time.

Rebuilding everything on every change sounds wasteful. It is not, for a reason specific to this design: a view is a stored query, so creating ten views costs ten catalog entries and reads no data at all. Nothing is computed until the grid asks for rows.

Views, until they should be tables

Nesting views has a cost that shows up later than you expect. Ten nested views produce one query with ten levels of subquery, and the planner has to reason about all of it on every scroll of the grid.

So there is a threshold:

const MATERIALIZE_DEPTH = 5;
...
const shouldMaterialize = i >= MATERIALIZE_DEPTH;

Steps zero through four are views. Step five and beyond become real tables, which caps plan depth at five levels regardless of how long the pipeline gets. Five is not a measured optimum; it is a number that behaved well and has not caused a complaint. I would rather say that than pretend it came from a benchmark.

There is one operation that has no choice. DuckDB's PIVOT statement cannot be used inside a view, so a pivot step is always materialized:

forceTable: shouldMaterialize || /\bPIVOT\b/i.test(query),

The bug: stored SQL is a cache, not a source of truth

Every step keeps two things: the parameters the user configured, and the SQL that was generated from them. Keeping the SQL matters because the user can read it, and can edit it, and an edited step's SQL is the only record of what they wrote.

The mistake was treating that stored SQL as authoritative on rebuild. It names relations, and relations move. Rename a file, delete an upstream view, reorder steps so that step 3 becomes step 1: the persisted SQL now points at something that does not exist, the rebuild throws, and the pipeline is broken. Worse, it stays broken. The bad SQL is persisted to IndexedDB, so re-running does not help and neither does reloading the page.

The fix is to treat a missing-relation error as a signal to re-derive rather than a failure:

try {
  await materialize(sql);
} catch (err) {
  // Stored SQL is a cache of the generator's output. When it names a
  // relation that is gone, rebuild it from the step's params rather than
  // leaving the pipeline permanently broken — a state that otherwise
  // survives Rerun and a reload, because the bad SQL is persisted.
  const message = err instanceof Error ? err.message : String(err);
  if (!isMissingRelationError(message)) throw err;
  const ctx = await contextForSource(prevSourceName);
  const fromParams = regenerateStepSQL(step, ctx);
  if (!fromParams) throw err;
  const regenerated = resolveForeignTable(fromParams);
  await materialize(regenerated);
  sql = regenerated;
}

Note what the recovery is careful about. It only triggers on a missing-relation error, not on any error, because regenerating from parameters would silently discard a user's hand-edited SQL if it fired on a syntax error in that edit. And it only applies when regeneration is possible: a raw SQL step has no parameters to regenerate from, so regenerateStepSQL returns nothing and the original error is rethrown.

The general principle, which took me longer to arrive at than it should have: if a value can be derived and is also stored, decide explicitly which one wins, and make the stored copy recoverable. Otherwise you get states that persistence makes permanent.

Naming, and one relation called data

Writing SQL against a generated view name like v_8f3a1c_step_2 is hostile. So a raw SQL step can call its input data, and the alias is defined on the connection inside the same window in which the step is created, so no other chain can repoint it midway.

That creates a name to protect. If a user loads a file called data.csv, the derived table name would be data, and DuckDB refuses to put a view over a table of the same name. That one file would switch the alias off for every other open file, and quietly re-point any saved FROM data at itself. So the name is reserved:

// Lowercased, because DuckDB resolves unquoted identifiers case-insensitively
// — "Orders" and "orders" are one relation, and one would drop the other.
const taken = new Set();
for (const name of existingTableNames) taken.add(name.toLowerCase());
taken.add(DATA_ALIAS);

if (!taken.has(base.toLowerCase())) return base;
let suffix = 2;
while (taken.has(`${base}_${suffix}`.toLowerCase())) suffix++;
return `${base}_${suffix}`;

The case-insensitivity comment is there because we shipped the bug it describes. Loading Orders.csv and then orders.csv produced two file tabs and one table, and the second load silently replaced the first.

Cross-view dependencies, in both directions

A join step joins against another open file. That other file has its own pipeline, and its output changes. Two directions have to work.

Forwards is resolution at rebuild time. The step stores the other side's view id, not just a table name, and on every rebuild that id is resolved to whatever the other view's current output relation is. If the other view has no pipeline, it falls back to the base file table. The old right-hand table name is then substituted out of the query, with a replacement that skips string literals so that a table name appearing inside a quoted value is not mangled.

Backwards is a sweep. After a view finishes rebuilding, it looks for other views whose join or union steps reference it, and rebuilds those too:

const dependsOnThis = otherSteps.some(
  (s) =>
    (s.operationType === "join" || s.operationType === "union_all") &&
    s.params &&
    (s.params._joinViewId === viewId ||
      s.params._unionViewId === viewId ||
      thisViewSources.includes(s.params.rightTable)),
);

Which immediately raises the question of cycles. View A joins B, B joins A, and the sweep recurses forever. The guard is a set of view ids currently rebuilding, checked on entry and cleared in a finally:

if (_rebuildingViewIds.has(viewId)) return;
_rebuildingViewIds.add(viewId);
try {
  ...
} finally {
  _rebuildingViewIds.delete(viewId);
}

The dependent rebuilds are fired without awaiting them, so a chain of dependencies does not block the view the user is looking at. They are not fire and forget, though: the rejection handler logs which dependent failed and which view it depended on, because a silently failed dependent leaves a downstream view showing stale data, and stale data that looks current is the worst outcome available.

Failing one step without failing the pipeline

When a step throws, everything after it is unusable, but the steps before it are fine and the user needs to see where the break is. So a failure marks that step broken with a readable message, marks every subsequent step broken with "previous step failed", and stops.

The output view then falls back to the last step that did succeed, so the grid shows partial results rather than nothing:

const lastGood = [...steps].reverse().findIndex((s) => s.status === "applied");
if (lastGood >= 0) {
  const goodIdx = steps.length - 1 - lastGood;
  await executeQuery(
    `CREATE VIEW ${outputViewName} AS SELECT * FROM "${prefix}_step_${goodIdx}"`,
  );
}

Error messages get translated on the way out, because DuckDB's are written for someone debugging a query, not for someone who clicked a button. Generated view names are rewritten to step numbers, a missing scalar function becomes a sentence about that function, and a catalog error about a missing table becomes "previous step failed, this step could not find its input". Anything over 200 characters is truncated.

One more status worth mentioning: a step that runs successfully and returns zero rows is marked as a warning, not a success. Zero rows is almost always a filter that is too aggressive, and the user needs to know at the step rather than at the empty grid.

Serialization

Every pipeline operation goes through one queue, because two concurrent rebuilds would be dropping and creating the same view names:

let _pipelineQueue = Promise.resolve();
export function serializePipelineOp(fn) {
  const next = _pipelineQueue.then(fn, fn);
  _pipelineQueue = next.catch((err) => {
    console.error("Pipeline operation failed:", err);
  });
  return next;
}

The subtlety is .then(fn, fn) with the same function in both slots. The queue only cares about ordering, so a rejected predecessor must not prevent the next operation from running. Callers still get their own failures through the returned promise; the queue's own copy is caught and logged so that one bad operation does not poison every operation after it.

What this buys

Three things, and they are the reason the constraint is worth its awkwardness.

The pipeline is portable. It is a list of parameters and SQL strings, so it serializes to IndexedDB and comes back, and it can be read by a person who wants to know exactly what happened to their data.

There is no second implementation. Nothing filters rows in JavaScript. A filter is a WHERE clause, which means it behaves identically on a thousand rows and on ten million, and there is no in-memory path to drift from the SQL path.

The escape hatch is the same mechanism. A user who hits the limits of the visual operations writes a raw SQL step, and it sits in the chain like any other step, with the same input alias and the same output contract. The advanced path is not a different system; it is the system with the UI taken off.