Skip to main content

๐Ÿ› ๏ธ Snowflake DDL (Data Definition Language)

DDL (Data Definition Language) is a subset of SQL used to define and manage database objects such as databases, schemas, tables, views, and more.

In Snowflake, DDL commands are executed in real time and changes are immediately reflected due to its cloud-native architecture.


๐Ÿ“š What Can You Do with DDL in Snowflake?โ€‹

CategoryPurposeCommon Commands
๐Ÿ—๏ธ Create ObjectsDefine new structuresCREATE
โœ๏ธ Modify ObjectsChange structure or propertiesALTER
โŒ Remove ObjectsDelete existing objectsDROP
๐Ÿ“ฆ Duplicate ObjectsClone existing objects (zero-copy)CREATE ... CLONE
๐Ÿ“‹ Inspect ObjectsView definition of an objectDESCRIBE, SHOW, LIST

๐Ÿงฑ Common DDL Objects in Snowflakeโ€‹

  1. Database
  2. Schema
  3. Table (Permanent, Transient, Temporary, External, Iceberg)
  4. View (Standard, Secure, Materialized)
  5. Stage (Internal, External)
  6. File Format
  7. Sequence
  8. Function / Procedure
  9. Stream (for CDC)
  10. Task (for orchestration)

๐Ÿงพ Key DDL Commands with Examplesโ€‹

1. ๐Ÿ“ฆ CREATE โ€“ To define new objectsโ€‹

-- Create a database
CREATE DATABASE my_database;

-- Create a schema
CREATE SCHEMA my_schema;

-- Create a table
CREATE TABLE emp (
emp_id INT,
name STRING,
salary NUMBER(10,2)
);

-- Create a secure view
CREATE SECURE VIEW emp_view AS
SELECT emp_id, name FROM emp;

2. โœ๏ธ ALTER โ€“ To modify existing objectsโ€‹

-- Add a column to a table
ALTER TABLE emp ADD COLUMN hire_date DATE;

-- Rename a column
ALTER TABLE emp RENAME COLUMN name TO full_name;

-- Change data type of a column
ALTER TABLE emp ALTER COLUMN salary SET DATA TYPE FLOAT;

3. โŒ DROP โ€“ To delete objectsโ€‹

-- Drop a table
DROP TABLE emp;

-- Drop a schema (and all its contents)
DROP SCHEMA my_schema CASCADE;

-- Drop a view
DROP VIEW emp_view;

4. ๐Ÿงฌ CREATE ... CLONE โ€“ Zero-copy cloningโ€‹

-- Clone a table
CREATE TABLE emp_clone CLONE emp;

-- Clone a schema
CREATE SCHEMA my_schema_clone CLONE my_schema;

๐Ÿง  Snowflake's zero-copy cloning allows you to copy metadata without duplicating storage.


5. ๐Ÿ” Inspecting Metadataโ€‹

-- Show all tables in a schema
SHOW TABLES IN SCHEMA my_schema;

-- Describe a table's structure
DESCRIBE TABLE emp;

-- List all databases
SHOW DATABASES;

๐Ÿง  DDL Behavior in Snowflakeโ€‹

  • โœ… DDLs are auto-committed: No need for COMMIT.
  • โœ… Changes are reflected immediately.
  • โœ… Most objects support Time Travel and Cloning.
  • ๐Ÿšซ ROLLBACK does not undo DDL changes.
  • ๐Ÿ“œ All DDL activity is audited and can be tracked using QUERY_HISTORY.

๐Ÿ“š Summary of DDL Actionsโ€‹

ActionKeywordExample
Create ObjectCREATECREATE TABLE emp (...);
Modify ObjectALTERALTER TABLE emp ADD COLUMN hire_date;
Delete ObjectDROPDROP TABLE emp;
Copy ObjectCLONECREATE TABLE emp_copy CLONE emp;
Explore ObjectSHOW, DESCSHOW TABLES;, DESCRIBE TABLE emp;