Skip to main content

✍️ 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?​

OperationPurposeExample Command
INSERTAdd new data to a tableINSERT INTO
UPDATEModify existing rowsUPDATE
DELETERemove rows from a tableDELETE
MERGEConditionally INSERT, UPDATE, DELETEMERGE INTO
TRUNCATERemove all rows quicklyTRUNCATE 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 WHERE clause 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, use WHERE carefully 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);

🧠 MERGE is 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, and ROLLBACK.
  • ✅ 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 of COMMIT; to undo the transaction.


🔍 Best Practices for DML​

  • Always use WHERE clause in UPDATE and DELETE to avoid full table changes.
  • For large inserts, prefer COPY INTO from stage for better performance.
  • Use MERGE for upsert scenarios in ETL/ELT pipelines.
  • Leverage TRUNCATE for cleaning temporary or staging tables.
  • Use VALIDATE() on your DML logic before running it on production tables.

🧾 Summary of DML Operations​

OperationPurposeRollback PossibleSupports WHERETime Travel Safe
INSERTAdd data✅ Yes❌ N/A✅ Yes
UPDATEModify rows✅ Yes✅ Yes✅ Yes
DELETERemove specific rows✅ Yes✅ Yes✅ Yes
MERGEConditional insert/update/delete✅ Yes✅ Yes✅ Yes
TRUNCATERemove 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​

TaskSnowflake JSON Support
Modify a keyOBJECT_INSERT()
Delete a keyOBJECT_DELETE()
Replace whole objectSET data = PARSE_JSON(...)
Delete rows by JSON keyUse WHERE data:"key" = value

Snowflake’s VARIANT data type allows DML on nested JSON-like documents too!