Publish / database · All operations

Replace a table with the result

A variant of Write to a database

Create the table from the result, or empty and refill it, so it holds only the new rows.

Status

In arcform

Not built yet

arcform does not do this yet. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Alteryx Designer (2)

Calgary Loaderread 2026-09-27
Create an indexed Calgary database (.cydb) from any input
Write Data In-DBread 2026-09-27
Create or update a table directly in the database from an in-DB stream

Apache DataFusion (3)

CREATE TABLEread 2026-09-27
Create an in-memory table from a query or values list
Catalogs: Tables (CREATE / DROP TABLE, CREATE TABLE AS SELECT)read 2026-09-27
Create and drop tables, including from a query
SELECT INTOread 2026-09-27
Create an in-memory table from the query result

Bruin (3)

create+replaceread 2026-09-27
Create the table if absent and overwrite its contents with the query result on every run
tableread 2026-09-27
Materialize the asset query result as a table
truncate+insertread 2026-09-27
Truncate the table, keeping its structure, and insert the full query result

dbt-utils (1)

tableread 2026-09-27
Rebuild the model as a table on each run via create table as

KNIME Analytics Platform (1)

DB Writer (DB Data)read 2026-09-27
Materialise the result of a DB query as a new table inside the database

RapidMiner Studio (1)

Store (In Database)read 2026-09-27
Store the result of an in-database chain as a database table

SQLMesh (2)

FULLread 2026-09-27
Rewrite the whole table on every model evaluation
MANAGEDread 2026-09-27
Create a table whose data the database engine itself keeps up to date

Background

In DuckDB, CREATE OR REPLACE TABLE creates a new table or overwrites an existing one, as shorthand for dropping the table and creating it anew.^[1] In DuckDB, CREATE TABLE ... AS SELECT (CTAS) creates a table from a query's result, and works with OR REPLACE.^[1] In Bruin, the truncate+insert strategy empties the existing table and then inserts the query result.^[2]

When to use it

Bruin's create+replace strategy suits a table cheap enough to recompute every time, and gives the simplest, always-correct result.^[2] With Bruin's create+replace strategy, cost and run time grow with the size of the full result, because nothing is reused between runs.^[2] Replace a table when each run computes the whole result, and change only part of it when a run computes only the new rows. (Meridian's view)

In Bruin, recreating a table can reset grants and other table-level settings on some platforms, and the truncate+insert strategy keeps them.^[2] In DuckDB, a table made with CREATE TABLE ... AS SELECT cannot carry constraints such as primary keys or check constraints.^[1]

Bruin wraps the truncate and the insert in a transaction where the platform allows it, but on engines where TRUNCATE commits implicitly, such as MySQL, Redshift and Oracle, a failed insert can leave the table empty.^[2] In PostgreSQL, TRUNCATE takes an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table.^[3] Make sure a failed run cannot leave readers looking at an empty table. (Meridian's view)

The method

Bruin's create+replace strategy runs CREATE OR REPLACE TABLE ... AS with the query, or the platform's equivalent drop and create.^[2] Bruin's truncate+insert strategy runs TRUNCATE TABLE and then INSERT INTO with the query, and on Athena and Trino it uses DELETE FROM instead of TRUNCATE.^[2] On Doris and StarRocks, Bruin's truncate+insert builds a new table from the query and swaps it in, so the original table object is not kept.^[2]

Compared with the alternatives

TRUNCATE TABLE removes all rows but keeps the table's structure, columns, constraints and indexes, while DROP TABLE also removes the table definition.^[4] In DuckDB, CREATE TABLE IF NOT EXISTS leaves an existing table in place and takes no action, where CREATE OR REPLACE overwrites it.^[1] In Bruin, full rebuilds recreate or reset the table on each run, while incremental loads, such as append, delete+insert and merge, change only part of an existing table.^[2]

Sources

[1]CREATE TABLE Statement – DuckDBdocumentation
[2]Materialization | Bruin CLIdocumentation
[3]PostgreSQL: Documentation: 18: TRUNCATEdocumentation
[4]Truncate (SQL) - Wikipediasecondary source