Snowflake Data Operations Study Guide

Treat the five areas as one operation

The five areas are connected. Identify who may change which object, control the change boundary, load or rerun work, and validate the resulting data.

A. Confirm role and object scope

Snowflake users operate through roles, and privileges granted to those roles authorize object access. Correct SQL can still fail for lack of privilege. Checking the current role, database, and schema first also reduces the chance of changing the wrong environment or a same-named table.

SELECT
  CURRENT_ROLE(),
  CURRENT_DATABASE(),
  CURRENT_SCHEMA();

Schema objects can be referenced as database.schema.object. For data-changing statements, a fully qualified name such as SALES_DB.CORE.ORDERS reduces reliance on the session's current database and schema.

SELECT *
FROM SALES_DB.CORE.ORDERS;

For read-only work, a typical least-privilege design grants only what is required, such as USAGE on the database and schema and SELECT on the table. Check the required operation before switching to a stronger role.

B. Change data safely

INSERT adds rows, UPDATE changes existing rows, and DELETE removes rows. UPDATE and DELETE without a WHERE clause target every row, so previewing the same predicate with SELECT helps verify the row count and keys first.

SELECT ORDER_ID, CUSTOMER_ID, STATUS
FROM SALES_DB.CORE.ORDERS
WHERE CUSTOMER_ID = 1001;

UPDATE SALES_DB.CORE.ORDERS
SET STATUS = 'CANCELLED'
WHERE CUSTOMER_ID = 1001;

MERGE can combine UPDATE, INSERT, and DELETE according to source-target matches. It does not make a duplicated source unique by itself. Choose a business key and verify source uniqueness before merging.

MERGE INTO SALES_DB.CORE.ORDERS AS t
USING LANDING_DB.RAW.ORDERS_STAGE AS s
  ON t.ORDER_ID = s.ORDER_ID
WHEN MATCHED THEN
  UPDATE SET t.STATUS = s.STATUS, t.AMOUNT = s.AMOUNT
WHEN NOT MATCHED THEN
  INSERT (ORDER_ID, CUSTOMER_ID, AMOUNT, STATUS)
  VALUES (s.ORDER_ID, s.CUSTOMER_ID, s.AMOUNT, s.STATUS);

NULL is different from zero and an empty string. Use IS NULL / IS NOT NULL to test it. For reruns, check idempotency: running the same input again should not create unintended duplicates or additional changes.

C. Separate transactions from recovery

BEGIN starts an explicit transaction, COMMIT makes it durable, and ROLLBACK cancels changes in that transaction. Use an explicit transaction when several DML statements belong to one success-or-failure unit.

BEGIN TRANSACTION;

UPDATE SALES_DB.CORE.ORDERS
SET STATUS = 'CLOSED'
WHERE ORDER_DATE < '2026-01-01';

COMMIT;

In Snowflake, executing DDL implicitly commits an active transaction, and the DDL runs as its own transaction. DDL cannot be rolled back. Do not mix DML and DDL while assuming one later ROLLBACK will undo everything.

ROLLBACK is for the current uncommitted transaction. Recovery after committed changes or a dropped object is different. Within the applicable retention period, Time Travel can expose historical data, and UNDROP can restore supported dropped objects.

D. Connect file loading and automation

A useful loading model is Stage → File Format → COPY INTO → Table. A stage identifies the file location, a file format defines how CSV, JSON, and other files are interpreted, and COPY INTO loads the table.

COPY INTO SALES_DB.CORE.ORDERS
FROM @LANDING_DB.RAW.ORDER_STAGE
FILE_FORMAT = (
  FORMAT_NAME = 'LANDING_DB.RAW.ORDER_CSV'
);

For automation, a Task answers when work runs, while a Stored Procedure can package what the work does. A task may call a stored procedure that performs a MERGE and validation steps.

E. Validate counts, keys, and values

A larger row count after a JOIN is not automatically an error. If ORDERS has one row per order and ORDER_ITEMS has one row per item, a one-to-many join naturally repeats an order. Confirm what one row means in each table before judging the result.

COUNT(*) counts rows, while COUNT(column) excludes rows where that column is NULL. Equal source and target row counts are still not complete validation because keys or values may differ.

SELECT COUNT(*) AS row_count,
       COUNT(AMOUNT) AS amount_count
FROM SALES_DB.CORE.ORDERS;

SELECT ORDER_ID, COUNT(*) AS duplicate_count
FROM SALES_DB.CORE.ORDERS
GROUP BY ORDER_ID
HAVING COUNT(*) > 1;

For late-arriving data, separate the business/event time from the ingestion time. If processing windows overlap to catch late records, combine that overlap with rerun-safe deduplication or merging.

Knowledge check

Before opening an answer, reason in this order: scope, transaction boundary, rerun behavior, and validation.

Official references

The behavior described here is checked against Snowflake documentation. Account-specific settings, including retention and grants, still need to be verified in the actual environment.

• Access control
• Object name resolution
• CURRENT_ROLE
• UPDATE
• DELETE
• MERGE
• Transactions
• Time Travel
• UNDROP TABLE
• COPY INTO <table>
• Internal stages
• Tasks
• Stored procedures
• COUNT
• IS NULL