Cleaning Snowflake Data: Duplicates, NULLs, Type Conversion, Semi-Structured Data and Clones

1.Removing duplicates and matching keys that contain NULLs

The most common way to remove duplicate rows is to number the rows within each business key and keep only the first. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) numbers the rows, and Snowflake's QUALIFY clause filters on that window result without needing a nested subquery:

SELECT i, p, o
  FROM qt
  QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1;

SELECT DISTINCT is the other way to drop duplicate rows. It is equivalent to GROUP BY ALL, so it removes only rows that are identical in every selected column. It cannot say which of several near-duplicate rows to keep. The QUALIFY ROW_NUMBER() version is explicit about which duplicate survives, because you choose the ORDER BY. To find out how many duplicates you have before you start, the system data metric function DUPLICATE_COUNT counts duplicate values in a column, including NULL values. If a dedup step removes the wrong rows, Time Travel (covered below) lets you query or clone the table as it was before the change.

Duplicates are also created by NULLs. With the = operator, NULL is an unknown value, so NULL = NULL evaluates to NULL rather than TRUE. If a join or MERGE condition compares a column that can be NULL, those rows never match and get inserted again on every run. EQUAL_NULL(a, b) and its equivalent a IS NOT DISTINCT FROM b are NULL-safe: they treat two NULLs as equal. For non-NULL inputs, EQUAL_NULL behaves exactly like =.

SELECT x1.i x1_i, x2.i x2_i
  FROM x x1, x x2
  WHERE EQUAL_NULL(x1.i, x2.i);

NULLs also affect aggregates. Aggregate functions work on non-NULL records: AVG returns the average of non-NULL records, and SUM likewise sums only non-NULL values. A NULL is therefore skipped rather than counted as 0. If you replace NULLs with a default such as 0 using COALESCE before averaging, those rows now take part in the average and the result changes.

Sources12345

2.Filling NULLs and converting to native data types

Missing values are handled with Snowflake's conditional expression functions. That family includes COALESCE, IFNULL, NVL, NVL2, NULLIF and ZEROIFNULL. COALESCE returns its first non-NULL argument, or NULL if every argument is NULL, which lets you put a fallback chain such as COALESCE(mobile, landline, work_phone) in a single expression.

The others cover narrower cases. NVL(expr1, expr2) returns expr2 if expr1 is NULL, otherwise expr1, and IFNULL is an alias of NVL. NULLIF(expr1, expr2) works in the opposite direction: it returns NULL if the two are equal, otherwise expr1, which turns a placeholder such as an empty marker into a real NULL. ZEROIFNULL(x) returns 0 if its argument is NULL, otherwise the argument. NVL2 returns values depending on whether its first input is NULL.

COALESCE quietly converts types. If one argument is numeric, Snowflake converts the others to a number: COALESCE('17', 1) works, but COALESCE('foo', 1) raises an error, and a non-numeric value converted this way becomes NUMBER(18,5). Snowflake recommends passing arguments of the same type, or converting them explicitly.

To convert explicitly, use CAST (or ::). It raises an error as soon as a value cannot be converted, which aborts a cleaning job over one bad row. TRY_CAST performs the same conversion but returns NULL for values that cannot be converted. You can then count those NULLs, investigate them, or default them with COALESCE. TRY_CAST has two limits: the input must be a string expression, and the target must be one of the native scalar types it supports.

Besides CAST and TRY_CAST, Snowflake has type-specific conversion functions such as TO_DATE, TO_NUMBER and TO_BOOLEAN, and each has an error-handling TRY_ version that returns NULL instead of raising an error. The date, time and timestamp functions accept an optional format argument, for example YYYY, MM, DD, so you can parse strings that are not in the default format. TRY_TO_DATE is the safe choice for a column of dirty date strings.

SELECT
  TRY_TO_DATE('2024-05-10') AS valid_date,
  TRY_TO_DATE('Invalid') AS invalid_date;

Choosing a native type follows from the data. Scalar types such as VARCHAR, NUMBER, DOUBLE, BOOLEAN, DATE, TIME and the TIMESTAMP family hold single values, and they are what you convert dirty strings into. VARIANT, OBJECT and ARRAY are the semi-structured types: they hold nested or repeating data, and FLATTEN accepts exactly these three. TO_VARIANT, TO_OBJECT and TO_ARRAY convert a value to them. Once a value is extracted from a VARIANT, cast it to a scalar type so that it can be compared, summed or sorted properly.

SELECT  ? ('05/16' AS TIMESTAMP);

Sources678910511

3.Traversing and flattening semi-structured data

JSON, Avro, ORC and Parquet data usually arrives in a VARIANT column holding nested OBJECTs and ARRAYs. You reach into it with path notation: a colon after the column name, then dots between levels, as in src:salesperson.name. Bracket notation, src['salesperson']['name'], returns the same values.

SELECT src:salesperson.name
    FROM car_sales
    ORDER BY 1;

If a key is not a valid SQL identifier, for example "company name" or "94987", you must wrap it in double quotes in the path. To pick one element of a repeating array, add a numbered predicate that starts from 0, as in src:vehicle[0] for the first vehicle or src:customer[0].name for the first customer's name. To get all the instances of a child element in a repeating array, you must flatten the array. When one row holds an array, such as several vehicles per sale, you turn it into rows with FLATTEN. FLATTEN is a table function that takes a VARIANT, OBJECT or ARRAY and produces a lateral view. In other words, it can refer to tables earlier in the FROM clause and emits one row per element, which converts semi-structured data into a relational shape.

The output of FLATTEN has the fixed columns SEQ, KEY, PATH, INDEX, VALUE and THIS. In practice you mostly use VALUE (the element), INDEX (its position, for arrays) and KEY (the key, for objects). Columns of the original table remain available, and their values are repeated for every row that FLATTEN produces from the same input row. In the example below, emp.project_names is an ARRAY column. LATERAL FLATTEN(INPUT => emp.project_names) runs once for each employee row, so each employee appears once per project, with the project in VALUE:

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
  FROM employees AS emp,
    LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
  ORDER BY employee_ID;

For a single-level array, TABLE(FLATTEN(...)) and LATERAL FLATTEN(...) give the same result. For nested structures, where one FLATTEN must read the output of an earlier one (for example f.value:business), use LATERAL so that each later FLATTEN can reference the previous one. With RECURSIVE => TRUE you can explore unfamiliar data: flattening every nested element lets you list the paths and types present.

Values extracted from a VARIANT keep their VARIANT form, so strings, dates and times appear in double quotes. Cast them explicitly to the type you need with ::, which also lets you do arithmetic on them:

SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
    FROM car_sales
    ORDER BY tax;

Casting to ::VARCHAR likewise removes the double quotes from a string value. PARSE_JSON interprets a string as a JSON document and produces a VARIANT, which is how JSON text is loaded into a VARIANT column. To build nested data from relational columns, use the nesting functions: OBJECT_CONSTRUCT returns an OBJECT constructed from its arguments, ARRAY_CONSTRUCT builds an ARRAY from its inputs, ARRAY_AGG pivots input values into an array, and OBJECT_AGG returns one OBJECT per group.

Sources1213514

4.Cleaning safely with zero-copy clones and Time Travel

Deduplication and type fixes are destructive, so test them on a clone first. A clone shares the underlying storage of its source, so CREATE TABLE ... CLONE gives you a full copy to rehearse on without duplicating storage up front. Once you run DML on the clone, Snowflake generates new data files for it, so the clone and the source diverge and changes to one do not affect the other. Several things carry over to the clone: object parameters set on the source, its DMF bindings, and its masking and row access policies. Grants are generally *not* copied unless you use COPY GRANTS. Automatic Clustering on a cloned table is suspended. The clone's own Time Travel history starts at the moment it was created.

Time Travel gives you the undo. Within the retention period you can query data as it was before an update, clone a table at or before a past point, and UNDROP a dropped table, schema or database. The AT | BEFORE clause specifies the point by TIMESTAMP, OFFSET (seconds back from now) or STATEMENT (a query ID). So if a cleaning UPDATE went wrong, you can clone the table BEFORE that statement's query ID.

CREATE SCHEMA S2 CLONE S1 AT(TIMESTAMP => '2025-04-01 12:00:00');

How far back you can go is set by the DATA_RETENTION_TIME_IN_DAYS parameter, which can be set on the account, database, schema or table. Setting it to 0 turns Time Travel off for that object. One detail matters for this example: user tasks are not cloned into a schema cloned with a timestamp. When the retention period ends, the historical data moves into Fail-safe: it can no longer be queried, past objects can no longer be cloned, and dropped objects can no longer be restored.

Sources151617

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A join or MERGE condition written with = will match rows where both sides are NULL.Why is that wrong?

   = treats NULL as unknown, so NULL = NULL is never TRUE. EQUAL_NULL and IS NOT DISTINCT FROM treat two NULLs as equal.

   Covered in Removing duplicates and matching keys that contain NULLs

2. 2.TRY_CAST can safely convert any column type, for example a VARIANT or a NUMBER, without raising errors.Why is that wrong?

   TRY_CAST accepts only string expressions as input, and only a fixed set of target types.

   Covered in Filling NULLs and converting to native data types

3. 3.A cloned table carries the source table's Time Travel history, so you can query the clone as it was last week.Why is that wrong?

   A table clone's history starts when the clone is created. To reach older states, use AT | BEFORE on the source table.

   Covered in Cleaning safely with zero-copy clones and Time Travel

4. 4.Because Snowflake identifiers are case-insensitive, src:Salesperson.Name finds the key salesperson.name.Why is that wrong?

   Only the column name is case-insensitive. JSON element names in the path must match their case exactly.

   Covered in Traversing and flattening semi-structured data

Practise it for real

See in your own account how NULL-safe matching, TRY_CAST and QUALIFY behave on small, dirty data

1. 1.Run CREATE OR REPLACE TABLE x (i NUMBER); then INSERT INTO x VALUES (1), (2), (NULL);

   Why: A key column containing a NULL is the setup that causes MERGE duplicates.

   You should see: Three rows in x.

2. 2.Self-join with SELECT x1.i x1_i, x2.i x2_i FROM x x1, x x2 WHERE x1.i = x2.i;

   Why: Shows how = handles NULL keys.

   You should see: Two rows: (1, 1) and (2, 2). The NULL row matches nothing.

3. 3.Repeat the join with WHERE EQUAL_NULL(x1.i, x2.i);

   Why: Shows the NULL-safe comparison you would use in a MERGE ON clause.

   You should see: Three rows, including (NULL, NULL).

4. 4.Run SELECT TRY_CAST('05-Mar-2016' AS TIMESTAMP); and SELECT TRY_CAST('05/16' AS TIMESTAMP);

   Why: Compares a parseable string with an unparseable one.

   You should see: 2016-03-05 00:00:00.000 for the first and NULL for the second. Neither raises an error.

5. 5.Create table qt (i INTEGER, p CHAR(1), o INTEGER) with rows (1,'A',1), (2,'A',2), (3,'B',1), (4,'B',2), then run SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1;

   Why: This is the deduplication pattern: one surviving row per key.

   You should see: Two rows: (1, A, 1) and (3, B, 1).