βοΈ Best Practices in DML (Data Manipulation Language) in Snowflake
DML commands such as INSERT, UPDATE, DELETE, and MERGE are used to manipulate data in Snowflake tables. While these operations are straightforward, following best practices ensures optimal performance, maintainability, and cost efficiency.
β General Guidelinesβ
-
Use
MERGEInstead of SeparateINSERTandUPDATE- Consolidates logic, improves clarity and performance.
- Helps in SCD Type 2 implementations and upserts.
-
Avoid Large-Scale Deletes or Updates in a Single Transaction
- Snowflake maintains versioned data for time travel and fail-safe.
- Massive updates/deletes can create large storage overhead.
-
Use
CREATE OR REPLACECarefully- Avoid accidental loss of data. Always test in dev first.
πͺ INSERT Best Practicesβ
- β
Use
INSERT ... SELECTinstead of row-wise inserts for bulk data loading. - β Validate data before inserting (e.g., filter duplicates or nulls).
- β
Load data in batches using
COPY INTOfrom staging areas (for large volumes). - β
Use
SEQUENCEorAUTOINCREMENTfor primary keys if needed.
β Avoid:β
- Row-by-row insertions in loops.
- Skipping NULL checks on mandatory fields.
π οΈ UPDATE Best Practicesβ
- β
Use
WHEREclause carefully to target specific rows. - β For historical data changes, use soft updates (e.g., mark records inactive and insert a new version).
- β
Use
MERGEwhen updating and inserting together.
β Avoid:β
- Updating entire table without a filter.
- Blind updates without versioning in slowly changing data.
π§Ή DELETE Best Practicesβ
- β
Prefer soft deletes: add a column like
is_deletedoractive_flag. - β
Use
DELETEonly when the row is no longer needed and not required for audit/time travel. - β
Always include a
WHEREclause.
β Avoid:β
- Full-table deletes unless absolutely necessary (use
TRUNCATEinstead). - Deleting data in time-travel protected tables (as it still consumes storage).
π§© MERGE Best Practicesβ
The MERGE statement is powerful for handling insert/update/delete logic in one go.
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
- β
Prefer
MERGEfor upsert scenarios to simplify logic. - β
Add
WHEREconditions insideWHEN MATCHEDto control which records are updated.
π Transaction Managementβ
-
β Group DMLs in transactions only if needed:
BEGIN;
UPDATE ...
INSERT ...
COMMIT; -
β Avoid long transactions that lock resources and increase warehouse usage.
πΎ Warehouse Optimization Tipsβ
- β Resize your warehouse for large DML jobs (e.g., M or L size).
- β
Monitor credit usage with
WAREHOUSE_METERING_HISTORY. - β Run DMLs in off-peak hours to avoid queueing.
π§ͺ Testing & Validationβ
- β Always run in DEV or clone tables before DML on production.
- β
Use
EXPLAINplans orPROFILEtab to understand performance. - β Audit changes by enabling change tracking where applicable.
π Naming & Audit Practicesβ
-
β Log metadata in audit tables if your DML alters critical data.
-
β Tag queries using
QUERY_TAG:ALTER SESSION SET QUERY_TAG = 'monthly_sales_update';
Summary Tableβ
| Area | Best Practice Highlights |
|---|---|
| INSERT | Use batch inserts, validate data, avoid row-by-row inserts |
| UPDATE | Use filters, version data, prefer MERGE where suitable |
| DELETE | Use soft deletes, avoid full deletes, always use WHERE |
| MERGE | Simplify upserts, optimize conditions |
| Warehouse Use | Right-size warehouses, run off-hours |
| Transactions | Use when needed, keep short and clean |
β
Best Practice: Bulk Insert Using INSERT ... SELECTβ
-- Insert filtered data from staging into final table
INSERT INTO sales_summary_2024 (sale_id, sale_date, amount)
SELECT sale_id, sale_date, amount
FROM stg_sales_data
WHERE sale_date >= '2024-01-01';
π’ Why it's good:
- Efficient bulk insert.
- Uses a filter (
WHERE) to insert only relevant rows. - Good table naming (
stg_,sales_summary_2024).
β Best Practice: Insert with Explicit Column Namesβ
INSERT INTO customer_dim (customer_id, customer_name, status)
VALUES (101, 'Amit Kumar', 'active');
π’ Why it's good:
- Always specifies column names (avoids column order issues).
- Clean, minimal values.
β Bad Practice: Row-by-Row Insert Without Column Namesβ
-- β Avoid this style
INSERT INTO customer_dim
VALUES (102, 'Rajiv Menon', 'active');
π΄ Why it's bad:
- No column names β breaks easily if schema changes.
- Not scalable for large datasets.
β Best Practice: Insert with Auto-Increment / Sequence Keyβ
CREATE OR REPLACE TABLE orders (
order_id INT AUTOINCREMENT START 1000 INCREMENT 1,
customer_id INT,
order_date DATE,
total_amount NUMBER(10,2)
);
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (301, CURRENT_DATE, 1500.00);
π’ Why it's good:
- Avoids manual
order_idassignment. - Uses
CURRENT_DATEfor dynamic timestamping.
β
Best Practice: Using MERGE for Upsertsβ
MERGE INTO customer_dim AS target
USING stg_customer_data AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET
target.customer_name = source.customer_name,
target.status = source.status,
target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (customer_id, customer_name, status, created_at)
VALUES (source.customer_id, source.customer_name, source.status, CURRENT_TIMESTAMP);
π Explanation:β
MERGE INTO ... USING ... ON: Joins staging (source) and target tables based oncustomer_id.WHEN MATCHED THEN UPDATE: Updates existing customers.WHEN NOT MATCHED THEN INSERT: Inserts new customers.- Uses
CURRENT_TIMESTAMPfor audit columns (created_at,updated_at). - Table names follow good naming conventions:
stg_for staging,dim_for dimension. - Keeps logic idempotent β running it again won't result in duplicates or wrong data.
β Bad Practice: Not Using Audit Columns or Join Conditionsβ
MERGE INTO customer_dim AS target
USING stg_customer_data AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET
target.customer_name = source.customer_name
WHEN NOT MATCHED THEN
INSERT VALUES (source.customer_id, source.customer_name);
π΄ Issues:β
- No
status,created_at, orupdated_atfields β hard to audit. INSERT VALUESwithout column list β error-prone.- Not scalable or maintainable.
Great question!
Using INSERT ... VALUES without a column list is considered error-prone for the following reasons:
β οΈ Why It's Error-Proneβ
1. Column Order Dependencyβ
- When you omit the column list, Snowflake assumes you are providing values in the exact order of the tableβs columns.
- If the table structure changes (columns are reordered or new columns are added), your insert logic can break or insert incorrect data into the wrong columns.
Example:
-- Table structure
CREATE TABLE customer (
id INT,
name STRING,
email STRING
);
-- Insert without column list
INSERT INTO customer VALUES (1, 'Gopi', 'gopi@example.com'); -- β
Works
-- Later, someone adds a column at position 2
ALTER TABLE customer ADD COLUMN status STRING;
-- Now the same insert will go wrong
INSERT INTO customer VALUES (1, 'Gopi', 'gopi@example.com'); -- β 'Gopi' goes into 'name', 'gopi@example.com' into 'status', etc.
2. Harder to Read & Maintainβ
- Without explicitly stating which columns are being inserted into, the SQL becomes harder to understand and maintain, especially when table structures are large.
3. Increased Risk in Automation or Scriptingβ
- If your ETL/ELT pipelines auto-generate or modify table structures, omitting the column list increases the chance of bugs due to schema drift.
β Best Practiceβ
Always specify the columns you're inserting into:
INSERT INTO customer (id, name, email)
VALUES (1, 'Gopi', 'gopi@example.com');
This way, even if the table changes, your code is explicit and safe.