Control / ddl · All operations

Create or change tables

Create, alter or maintain tables and indexes.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
not a data operation
Level
one clause of SQL

The DuckDB step

Create and alter a table

SQL
CREATE TABLE t (id INTEGER PRIMARY KEY, v VARCHAR); ALTER TABLE t ADD COLUMN w DOUBLE DEFAULT 0;
INSERT INTO t (id, v) VALUES (1, 'a');
CREATE TEMP TABLE result AS
SELECT * FROM t;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 1 AND max(w) = 0 FROM result)) THEN 'as expected' ELSE error('the added column did not take its default') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the added column did not take its default”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Change a table's columns

Add, drop, rename or retype the columns of an existing table or view.

Manage buckets, queues and topics

Create, delete, list or purge the storage buckets, containers, queues and topics that pipelines write to.

Create a table

Create a table, schema or catalogue, empty or from a query, in a chosen database or table format.

Maintain a lake table's files

Compact files, vacuum old versions and expire snapshots of a Delta Lake or Iceberg table.

Empty a table

Delete every row of a table and keep the table itself.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache Hop (5)
  • DDL Generatorread 2026-09-27

    Generate, and optionally run, CREATE TABLE statements from stream field metadata

  • Neo4j constraintread 2026-09-27

    Create or drop constraints on node or relationship properties in Neo4j

  • Neo4j indexread 2026-09-27

    Create or drop indexes on node or relationship properties in Neo4j

  • Spark lake table maintenanceread 2026-09-27

    Run OPTIMIZE, VACUUM and snapshot expiry on Delta Lake or Iceberg tables

  • Truncate tablesread 2026-09-27

    Truncate one or more database tables

DuckDB (6)
  • ALTER TABLEread 2026-09-27

    Add, drop, rename or retype columns of a table

  • ALTER VIEWread 2026-09-27

    Rename a view or change its schema

  • CREATE INDEXread 2026-09-27

    Build an index on a table

  • CREATE SCHEMAread 2026-09-27

    Create a schema

  • CREATE TABLEread 2026-09-27

    Create a table, empty or from a query

  • DROPread 2026-09-27

    Remove a table, view or other catalog entry

Talend (11)

Background

In SQL, data definition language (DDL) is a syntax for creating and modifying database objects such as tables, indices and users.[1] Common examples of DDL statements include CREATE, ALTER and DROP.[1] SQL's DDL statements can be freely mixed with other SQL statements, so DDL is not a separate language.[1]

When to use it

Reach for a DDL step when a pipeline needs a table to exist, with the right columns, before a later step writes rows into it. (Meridian's view) In DuckDB, CREATE TABLE ... AS SELECT, also known as CTAS, creates a table from the result of a query.[2] In DuckDB, a CTAS statement can read a CSV file directly, detecting the column names and types automatically.[2] In DuckDB, a table cannot be created by a CTAS statement with constraints such as primary keys or check constraints.[2] In DuckDB, copying a table's schema with CREATE TABLE ... AS FROM another table with LIMIT 0 copies only the column names and types, not indexes, constraints or default values.[2] Where a table needs its keys and checks, declare it with CREATE TABLE first and fill it with INSERT afterwards, rather than building it with CTAS. (Meridian's view)

In DuckDB, CREATE TABLE IF NOT EXISTS creates the table only if it does not already exist, and otherwise takes no action and leaves the existing table in place.[2] In DuckDB, a temporary table can be reached only by the connection that created it, and is dropped automatically when that connection to DuckDB is closed.[2] In a step that runs on every pipeline run, decide on purpose whether the table should start afresh or keep what the last run left. (Meridian's view)

In DuckDB, a column added with ALTER TABLE ... ADD COLUMN is filled with the specified default value, or NULL if none is specified.[3] In DuckDB, changing a column's type converts the existing data with the expression in the USING clause, or casts it to the new type if there is no USING clause.[3] In DuckDB, setting or dropping a column's default does not modify any data already in the column.[3] In DuckDB, changing a column's type fails if values of a conflicting type have occurred in the table at any point, even if they have since been deleted.[3]

The method

The concept of a data definition language, and its name, were first introduced with the Codasyl database model, where a database's schema was written in a syntax describing records, fields and sets.[1] Later the term DDL was used for the subset of Structured Query Language (SQL) that declares tables, columns, data types and constraints.[1] SQL-92 introduced a schema manipulation language and schema information tables to query schemas.[1] In the SQL 1992 specification, the components a CREATE statement can make are schemas, tables, views, domains, character sets, collations, translations and assertions.[1]

Many data description languages define columns and data types declaratively, whereas SQL uses imperative verbs that modify the schema by adding, changing or deleting definitions of tables and other elements.[1] An ALTER statement in SQL changes the properties of an object inside a relational database management system (RDBMS), and which objects can be altered depends on the RDBMS.[1]

Compared with the alternatives

TRUNCATE TABLE is logically, though not physically, equivalent to a DELETE FROM the table without a WHERE clause.[4] The SQL standard classifies TRUNCATE as a data change statement, but some documents describe it as a DDL operation, since it may be seen as a combined DROP and CREATE.[4]

Sources

  1. [1]Data definition language - Wikipediasecondary source
  2. [2]CREATE TABLE Statement – DuckDBdocumentation
  3. [3]ALTER TABLE Statement – DuckDBdocumentation
  4. [4]Truncate (SQL) - Wikipediasecondary source