DDL vs DML Commands
Snowflake · Data Types & SQL Fundamentals
Theory
SQL commands fall into two main groups. DDL (Data Definition Language) creates and changes objects: CREATE, ALTER, DROP, UNDROP and others that change structure. DML (Data Manipulation Language) changes the rows inside tables: INSERT, UPDATE, DELETE, MERGE and COPY INTO. SELECT only reads data and is often called DQL.
In Snowflake, each DDL statement runs in its own transaction: if you run DDL inside an open transaction, the transaction is committed first. DML can be grouped with BEGIN and COMMIT so several changes succeed or fail together. For example, CREATE TABLE products defines the table, and INSERT INTO products adds rows to it.
Why we use it
The difference affects permissions, transactions and recovery. DDL needs object-level privileges like CREATE or OWNERSHIP, DML needs privileges like INSERT or UPDATE, and only DML can be rolled back as part of a transaction.
When to use it
Use DDL when building or changing the schema, typically from deployment scripts. Use DML for loading and maintaining data. Keep DDL out of multi-statement transactions you expect to roll back, because it commits automatically.
DDL: CREATE, ALTER, DROP
CREATE TABLE, ALTER WAREHOUSE, and DROP DATABASE all change what an object looks like or whether it exists at all, not the rows inside it.
DML: INSERT, UPDATE, DELETE, MERGE
These four commands add, change, remove, or upsert rows; MERGE in particular is Snowflake's standard tool for combining an insert-or-update in a single statement, common in incremental loading.
Why the Distinction Matters for Permissions
RBAC grants DDL and DML privileges separately (e.g. CREATE TABLE vs INSERT), so a role can be allowed to load data into existing tables without being able to create or drop them.
How it works
- Write a SQL statement
- Does it change an object's structure? Yes: It is DDL: needs CREATE, ALTER or OWNERSHIP. No: It changes or reads rows.
- Does it change rows? Yes: It is DML: needs INSERT, UPDATE or DELETE privileges. No: It is a query: needs SELECT.
- Is it inside BEGIN ... COMMIT? Yes: DML can be rolled back; DDL commits immediately. No: Autocommit applies.
- Statement runs with the right privileges
Example
CREATE OR REPLACE TABLE products (
product_id INT AUTOINCREMENT, name VARCHAR(200) NOT NULL,
category VARCHAR(100), price DECIMAL(10,2), stock_qty INT DEFAULT 0);
INSERT INTO products (name, category, price, stock_qty) VALUES
('Laptop Pro','Electronics',1299.99,50), ('Wireless Mouse','Accessories',39.99,200);
UPDATE products SET price = price * 0.9 WHERE category = 'Accessories';
DELETE FROM products WHERE stock_qty = 0;
ALTER TABLE products ADD COLUMN brand VARCHAR(100);Key points
- DDL changes structure; DML changes data
- A DDL statement commits any open transaction
- UNDROP recovers dropped objects within Time Travel
- MERGE is the standard pattern for incremental upserts
- Permissions for DDL and DML can be granted independently