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

  1. Write a SQL statement
  2. Does it change an object's structure? Yes: It is DDL: needs CREATE, ALTER or OWNERSHIP. No: It changes or reads rows.
  3. Does it change rows? Yes: It is DML: needs INSERT, UPDATE or DELETE privileges. No: It is a query: needs SELECT.
  4. Is it inside BEGIN ... COMMIT? Yes: DML can be rolled back; DDL commits immediately. No: Autocommit applies.
  5. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic