Skip to main content

✍️ 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 MERGE Instead of Separate INSERT and UPDATE

    • 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 REPLACE Carefully

    • Avoid accidental loss of data. Always test in dev first.

πŸͺ› INSERT Best Practices​

  • βœ… Use INSERT ... SELECT instead of row-wise inserts for bulk data loading.
  • βœ… Validate data before inserting (e.g., filter duplicates or nulls).
  • βœ… Load data in batches using COPY INTO from staging areas (for large volumes).
  • βœ… Use SEQUENCE or AUTOINCREMENT for primary keys if needed.

❌ Avoid:​

  • Row-by-row insertions in loops.
  • Skipping NULL checks on mandatory fields.

πŸ› οΈ UPDATE Best Practices​

  • βœ… Use WHERE clause carefully to target specific rows.
  • βœ… For historical data changes, use soft updates (e.g., mark records inactive and insert a new version).
  • βœ… Use MERGE when 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_deleted or active_flag.
  • βœ… Use DELETE only when the row is no longer needed and not required for audit/time travel.
  • βœ… Always include a WHERE clause.

❌ Avoid:​

  • Full-table deletes unless absolutely necessary (use TRUNCATE instead).
  • 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 MERGE for upsert scenarios to simplify logic.
  • βœ… Add WHERE conditions inside WHEN MATCHED to 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 EXPLAIN plans or PROFILE tab 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​

AreaBest Practice Highlights
INSERTUse batch inserts, validate data, avoid row-by-row inserts
UPDATEUse filters, version data, prefer MERGE where suitable
DELETEUse soft deletes, avoid full deletes, always use WHERE
MERGESimplify upserts, optimize conditions
Warehouse UseRight-size warehouses, run off-hours
TransactionsUse 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_id assignment.
  • Uses CURRENT_DATE for 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 on customer_id.
  • WHEN MATCHED THEN UPDATE: Updates existing customers.
  • WHEN NOT MATCHED THEN INSERT: Inserts new customers.
  • Uses CURRENT_TIMESTAMP for 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, or updated_at fields β†’ hard to audit.
  • INSERT VALUES without 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.