✍️ Snowflake DML (Data Manipulation Language)
DML (Data Manipulation Language) is used to interact with data stored in database tables — inserting new data, modifying existing data, or deleting data.
Snowflake supports standard SQL DML operations, and because of its cloud-native architecture, these operations are highly parallelized, transactional, and performant.
📚 What Can You Do with DML?
| Operation | Purpose | Example Command |
|---|---|---|
| INSERT | Add new data to a table | INSERT INTO |
| UPDATE | Modify existing rows | UPDATE |
| DELETE | Remove rows from a table | DELETE |
| MERGE | Conditionally INSERT, UPDATE, DELETE | MERGE INTO |
| TRUNCATE | Remove all rows quickly | TRUNCATE TABLE |
1. 📥 INSERT – Add Data to Tables
-- Insert one row
INSERT INTO employees (emp_id, name, department, salary)
VALUES (101, 'Alice', 'Sales', 55000);
-- Insert multiple rows
INSERT INTO employees (emp_id, name, department, salary)
VALUES
(102, 'Bob', 'HR', 60000),
(103, 'Charlie', 'IT', 72000);
✅ You must specify the same number of columns and values unless inserting from a SELECT.
2. ✏️ UPDATE – Modify Existing Data
-- Update salary for one employee
UPDATE employees
SET salary = 58000
WHERE emp_id = 101;
-- Update multiple rows
UPDATE employees
SET department = 'Tech Support'
WHERE department = 'IT';
⚠️ Always use a
WHEREclause to avoid updating all rows.
3. ❌ DELETE – Remove Rows from Table
-- Delete one employee
DELETE FROM employees
WHERE emp_id = 103;
-- Delete all employees in a department
DELETE FROM employees
WHERE department = 'HR';
⚠️ Like
UPDATE, useWHEREcarefully to avoid mass deletion.
4. 🔁 MERGE – UPSERT (Insert or Update Based on Condition)
MERGE INTO employees tgt
USING (SELECT 104 AS emp_id, 'David' AS name, 'Finance' AS department, 75000 AS salary) src
ON tgt.emp_id = src.emp_id
WHEN MATCHED THEN
UPDATE SET
name = src.name,
department = src.department,
salary = src.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, name, department, salary)
VALUES (src.emp_id, src.name, src.department, src.salary);
🧠
MERGEis ideal for incremental data loading, CDC (Change Data Capture), and SCD (Slowly Changing Dimensions).
5. 🧹 TRUNCATE – Delete All Rows (Fast and Non-Recoverable)
TRUNCATE TABLE employees;
- Very fast and optimized.
- Cannot be rolled back.
- Metadata remains (i.e., structure of the table is not affected).
🧠 DML Behavior in Snowflake
- ✅ Fully ACID-compliant – ensures atomicity and consistency.
- ✅ Supports multi-statement transactions using
BEGIN,COMMIT, andROLLBACK. - ✅ Time Travel and Fail-safe allow recovery of data even after deletion or update.
- ✅ Automatic optimization and scaling during execution.
🔐 Transaction Example
BEGIN;
UPDATE employees
SET salary = salary + 1000
WHERE department = 'Sales';
DELETE FROM employees
WHERE salary < 40000;
COMMIT;
Use
ROLLBACK;instead ofCOMMIT;to undo the transaction.
🔍 Best Practices for DML
- Always use
WHEREclause inUPDATEandDELETEto avoid full table changes. - For large inserts, prefer
COPY INTOfrom stage for better performance. - Use
MERGEfor upsert scenarios in ETL/ELT pipelines. - Leverage
TRUNCATEfor cleaning temporary or staging tables. - Use
VALIDATE()on your DML logic before running it on production tables.
🧾 Summary of DML Operations
| Operation | Purpose | Rollback Possible | Supports WHERE | Time Travel Safe |
|---|---|---|---|---|
| INSERT | Add data | ✅ Yes | ❌ N/A | ✅ Yes |
| UPDATE | Modify rows | ✅ Yes | ✅ Yes | ✅ Yes |
| DELETE | Remove specific rows | ✅ Yes | ✅ Yes | ✅ Yes |
| MERGE | Conditional insert/update/delete | ✅ Yes | ✅ Yes | ✅ Yes |
| TRUNCATE | Remove all rows | ❌ No | ❌ N/A | ✅ (via Time Travel) |
🧪 DML with Semi-Structured Data (VARIANT)
JSON data is stored in columns of type VARIANT, OBJECT, or ARRAY. Since these are semi-structured, you can't partially update individual keys inside a JSON object
Instead, you typically:
🔁 1. Replace the entire JSON object in an UPDATE
🗑️ 2. Filter by JSON keys in a DELETE
✅ Example Setup
CREATE OR REPLACE TABLE json_table (
emp_id INT,
data VARIANT
);
INSERT INTO json_table VALUES
(1, PARSE_JSON('{ "name": "Alice", "dept": "HR" }')),
(2, PARSE_JSON('{ "name": "Bob", "dept": "IT" }'));
🔁 Update: Replace JSON Entirely or with Modified Values
💡 Option 1: Full replace with new JSON
UPDATE json_table
SET data = PARSE_JSON('{ "name": "Alice", "dept": "Finance" }')
WHERE emp_id = 1;
💡 Option 2: Update one key while preserving others
To modify just one key, extract the existing JSON, update the key, and rebuild the object:
UPDATE json_table
SET data = OBJECT_INSERT(data, 'dept', 'Marketing')
WHERE emp_id = 1;
✅
OBJECT_INSERT(<object>, <key>, <value>)adds or replaces a key in a JSON object.
🗑️ Delete: Filter by JSON Key Value
❌ Delete based on JSON field
DELETE FROM json_table
WHERE data:"dept" = 'IT';
🧠 Bonus: Remove a Key from a JSON Object
You can't do this inline, but you can rebuild the object excluding the key:
UPDATE json_table
SET data = OBJECT_DELETE(data, 'dept')
WHERE emp_id = 1;
✅ Summary
| Task | Snowflake JSON Support |
|---|---|
| Modify a key | OBJECT_INSERT() |
| Delete a key | OBJECT_DELETE() |
| Replace whole object | SET data = PARSE_JSON(...) |
| Delete rows by JSON key | Use WHERE data:"key" = value |
Snowflake’s VARIANT data type allows DML on nested JSON-like documents too!