๐ ๏ธ 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?โ
| Category | Purpose | Common Commands |
|---|---|---|
| ๐๏ธ Create Objects | Define new structures | CREATE |
| โ๏ธ Modify Objects | Change structure or properties | ALTER |
| โ Remove Objects | Delete existing objects | DROP |
| ๐ฆ Duplicate Objects | Clone existing objects (zero-copy) | CREATE ... CLONE |
| ๐ Inspect Objects | View definition of an object | DESCRIBE, SHOW, LIST |
๐งฑ Common DDL Objects in Snowflakeโ
- Database
- Schema
- Table (Permanent, Transient, Temporary, External, Iceberg)
- View (Standard, Secure, Materialized)
- Stage (Internal, External)
- File Format
- Sequence
- Function / Procedure
- Stream (for CDC)
- 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.
- ๐ซ
ROLLBACKdoes not undo DDL changes. - ๐ All DDL activity is audited and can be tracked using QUERY_HISTORY.
๐ Summary of DDL Actionsโ
| Action | Keyword | Example |
|---|---|---|
| Create Object | CREATE | CREATE TABLE emp (...); |
| Modify Object | ALTER | ALTER TABLE emp ADD COLUMN hire_date; |
| Delete Object | DROP | DROP TABLE emp; |
| Copy Object | CLONE | CREATE TABLE emp_copy CLONE emp; |
| Explore Object | SHOW, DESC | SHOW TABLES;, DESCRIBE TABLE emp; |