SQL for Data Engineers: Foundations, from DDL to the NULL Traps

In Snowflake:

CREATE TABLE customers (
    customer_sk   NUMBER        AUTOINCREMENT PRIMARY KEY,   -- Postgres: INT GENERATED ALWAYS AS IDENTITY
    customer_code VARCHAR(50)   NOT NULL UNIQUE,
    email         VARCHAR(255)  NOT NULL,
    country_code  CHAR(2),
    credit_limit  NUMBER(12,2)  NOT NULL DEFAULT 0,
    created_at    TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    CONSTRAINT chk_credit_nonneg CHECK (credit_limit >= 0)
);

The same logic in portable SQL, as run on DuckDB to produce the output above:

CREATE SEQUENCE customer_seq START 1;

CREATE TABLE customers (
    customer_sk   INTEGER       PRIMARY KEY DEFAULT nextval('customer_seq'),
    customer_code VARCHAR(50)   NOT NULL UNIQUE,
    email         VARCHAR(255)  NOT NULL,
    country_code  CHAR(2),
    credit_limit  DECIMAL(12,2) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP     NOT NULL DEFAULT current_timestamp,
    CONSTRAINT chk_credit_nonneg CHECK (credit_limit >= 0)
);

• customer_sk … DEFAULT nextval('customer_seq') makes the database fill the surrogate key. Snowflake spells it AUTOINCREMENT (or IDENTITY), Postgres GENERATED ALWAYS AS IDENTITY. A PRIMARY KEY implies NOT NULL and UNIQUE.
• customer_code … NOT NULL UNIQUE declares the business key as an alternate key, which later loads use for lookups and dedup.
• DECIMAL(12,2) stores money exactly. Never use FLOAT for currency: binary floating point can't represent 0.10 exactly, and sums drift.
• DEFAULT 0 and DEFAULT current_timestamp fill columns an insert leaves out.
• CONSTRAINT chk_credit_nonneg CHECK (…) names the rule, so the error message tells you which rule broke.

Which rules are enforced depends on the engine. DuckDB and Postgres enforce all of them, as the step-through shows. Snowflake does not enforce PRIMARY KEY, UNIQUE or FOREIGN KEY on standard tables: they are metadata for modeling and the optimizer (Snowflake docs). It does enforce NOT NULL, and since the April 2026 release it also enforces CHECK constraints (release notes). So on Snowflake a duplicate customer_code loads silently, and your pipeline has to test for it (Q14 and post 02's quality gate).

Engineer check. Grain: one row per customer, guaranteed by the unique business key only where the engine enforces it. Run twice: CREATE TABLE fails the second time, so deployment scripts use CREATE TABLE IF NOT EXISTS or versioned migrations. NULL: NOT NULL is the one rule every engine enforces, so it's the cheapest quality check you have.

TRUNCATE TABLE stg_orders;                               -- DDL: empty it, keep it

DELETE FROM app_log WHERE log_date = DATE '2025-03-26';  -- DML: filtered row removal

DROP TABLE deprecated_table;                             -- DDL: rows and definition gone

• TRUNCATE TABLE removes every row in one operation and keeps the table, its columns and grants. It can't take a WHERE. It's classed as DDL because it works on the table's storage, not row by row.
• DELETE … WHERE is DML: it removes only the rows that match, and it can be rolled back inside a transaction.
• DROP TABLE removes the rows and the definition. Snowflake keeps dropped tables for its Time Travel period, so UNDROP TABLE can bring one back (docs); most engines can't.

The trap is reaching for DELETE FROM stg_orders (no WHERE) to empty a staging table. It works, but it is the slow, logged path, and on a large table TRUNCATE is the right tool.

Engineer check. Run twice: TRUNCATE and the DELETE are harmless the second time, but DROP TABLE errors, so scripts write DROP TABLE IF EXISTS. NULL: a DELETE … WHERE log_date = … never removes rows whose log_date is NULL, which is usually what you want, but worth knowing.

ALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_tier VARCHAR(10) DEFAULT 'BRONZE';
ALTER TABLE customers RENAME COLUMN phone TO phone_e164;
ALTER TABLE customers DROP COLUMN IF EXISTS fax;

• ADD COLUMN … DEFAULT 'BRONZE' adds the column, and existing rows read the default, as the output shows. On Postgres 11 and later, adding a column with a constant default doesn't rewrite the table (docs).
• IF NOT EXISTS / IF EXISTS make the add and the drop safe to run again: a second run is a no-op instead of an error. Snowflake, Postgres and DuckDB all accept these forms.
• RENAME COLUMN moves no data, but it breaks every downstream query that uses the old name, so it's a coordinated change, not a silent one.

Engineer check. Run twice: the add and drop are idempotent thanks to the guards; the rename is not. NULL: rows that exist when the column is added get the default; a later insert that explicitly writes NULL gets NULL, not the default.

CREATE TABLE stg_orders AS
SELECT
    CAST(order_id AS INT)                     AS order_id,
    cust_id,
    TRY_CAST(amount_str AS DECIMAL(12,2))     AS amount,
    TRY_CAST(ts_str     AS TIMESTAMP)         AS order_ts,
    (amount_str IS NOT NULL AND TRY_CAST(amount_str AS DECIMAL(12,2)) IS NULL)
      OR (ts_str IS NOT NULL AND TRY_CAST(ts_str AS TIMESTAMP) IS NULL)  AS has_parse_error
FROM raw_orders;

• CREATE TABLE … AS SELECT (CTAS) creates and fills the table in one statement; its column types come from the query.
• TRY_CAST(…) returns NULL instead of raising an error when a value doesn't parse (Snowflake docs). '1,250.00' (thousands separator), 'n/a' and the impossible date 2025-02-30 all become NULL.
• has_parse_error is the part most answers forget. After TRY_CAST, a NULL amount could mean "the source had no amount" (order 1005, fine) or "the source had garbage" (orders 1002 and 1003, a problem). The flag is true only when the source had a value and the cast failed, so the bad rows can be counted, quarantined or alerted on.

Order 1002 is the dangerous one: 1,250.00 is a real amount that a stricter parser rejected. A silent TRY_CAST without the flag would drop $1,250 of revenue from every report.

Engineer check. Run twice: CTAS fails if the table exists; use CREATE OR REPLACE TABLE for a staging table that is rebuilt every run. NULL: distinguish a NULL from the source from a NULL produced by a failed cast, or you'll quietly lose data.

In Snowflake:

-- 1. empty target with the same column definitions, defaults and constraints
CREATE TABLE dim_product_load LIKE dim_product;

-- 2. zero-copy clone: instant, shares storage until either side changes
CREATE TABLE dim_product_dev CLONE dim_product;

The same logic in portable SQL, as run on DuckDB to produce the output above:

-- 1. empty target: a query that returns no rows
CREATE TABLE dim_product_load AS SELECT * FROM dim_product WHERE FALSE;

-- 2. full copy for testing
CREATE TABLE dim_product_dev AS SELECT * FROM dim_product;

• CREATE TABLE … AS SELECT … WHERE FALSE gives the right columns and types with zero rows. It does not copy defaults or constraints, because CTAS only knows the query's result shape.
• Snowflake's CREATE TABLE … LIKE makes an empty table with the column definitions, defaults and constraints (docs). That's the better empty target.
• CLONE copies the data without physically copying it: the clone shares the original's micro-partitions, and the two diverge only as either is changed (docs). It's the right tool for the dev copy, and the wrong one for an empty target, because it brings every row along.

Engineer check. Run twice: both statements fail if the target exists, so use CREATE OR REPLACE for throwaway copies. Watch out: a clone of a production table in a dev schema is still production data, so the same access rules apply.

INSERT INTO dim_product (product_sk, product_code, name, category, list_price)
VALUES (1004, 'SKU-004', 'Webcam', 'Video', 49.00);

• A positional insert must supply a value for every column, in the table's order. The table now has six columns and the insert gives five, so the engine rejects it, as the step-through shows.
• Naming the columns makes the insert independent of column count and order. Columns you don't name, like is_active, get their default.
• The failure is the good outcome. The bad one is when two columns of the same type are added or reordered: a positional insert then loads cleanly into the wrong columns, and nothing errors.

Engineer check. Run twice: this insert adds a second SKU-004 row, because a plain INSERT is never idempotent. Q10 makes it safe with MERGE.

In Snowflake:

CREATE SEQUENCE store_seq START = 1 INCREMENT = 1;

CREATE TABLE dim_store (
    store_sk   NUMBER DEFAULT store_seq.NEXTVAL,   -- or: store_sk NUMBER AUTOINCREMENT
    store_code VARCHAR(20) NOT NULL,
    store_name VARCHAR(100)
);

INSERT INTO dim_store (store_code, store_name)
VALUES ('ST-01', 'Downtown'), ('ST-02', 'Airport');

The same logic in portable SQL, as run on DuckDB to produce the output above:

CREATE SEQUENCE store_seq START 1;

CREATE TABLE dim_store (
    store_sk   INTEGER PRIMARY KEY DEFAULT nextval('store_seq'),
    store_code VARCHAR(20) NOT NULL,
    store_name VARCHAR(100)
);

INSERT INTO dim_store (store_code, store_name)
VALUES ('ST-01', 'Downtown'), ('ST-02', 'Airport');

• CREATE SEQUENCE makes a standalone number generator, and DEFAULT nextval(…) pulls the next value whenever an insert leaves store_sk out.
• Surrogate keys are meaningless integers on purpose: they insulate the warehouse from source keys that change format, get reused or collide across systems.
• Sequences guarantee unique, increasing values, not gap-free ones: a rolled-back insert or a cached block skips numbers (Snowflake docs). Never use them as a row count.

The re-run is the real lesson. The sequence knows nothing about store_code, so running the load again inserts ST-01 and ST-02 a second time under new keys 3 and 4. Surrogate keys make rows unique, not entities. The natural key needs its own guard, and that's the job of MERGE (Q10).

Engineer check. Grain: unique per row, not per store, until a natural-key check exists. Run twice: duplicates. NULL: store_code NOT NULL is worth having, because a NULL business key can never be matched or deduplicated later.

In BigQuery and Snowflake:

-- BigQuery: one partition per day of event_date
CREATE TABLE analytics.events (
    event_id   INT64,
    user_id    STRING,
    event_date DATE
)
PARTITION BY event_date;

-- Snowflake: no declared partitions; micro-partitions are automatic,
-- and a clustering key keeps rows with nearby dates together
CREATE TABLE events (event_id NUMBER, user_id STRING, event_date DATE)
CLUSTER BY (event_date);

-- The rewrite (both engines): a range on the bare column
SELECT count(*) FROM events
WHERE event_date >= DATE '2025-03-01' AND event_date < DATE '2025-04-01';

The same logic in portable SQL, as run on DuckDB to produce the output above:

SELECT count(*) AS march_events
FROM events
WHERE event_date >= DATE '2025-03-01'
  AND event_date <  DATE '2025-04-01';

• PARTITION BY event_date stores each day separately, so a filter on event_date reads only the matching days. That's partition pruning.
• Snowflake has no declared partitions. It keeps min/max metadata for every micro-partition and skips the ones a filter rules out; CLUSTER BY keeps nearby dates in the same micro-partitions so that skipping works well on very large tables (docs).
• YEAR(event_date) = 2025 asks the engine about a computed value, so it can't compare the filter with a partition's date range and reads everything. Google's guidance is the same: isolate the partition column on one side of the comparison (BigQuery docs).
• A half-open range >= '2025-03-01' AND < '2025-04-01' returns the same rows, as the output shows, but prunes. It also handles month ends without knowing whether March has 31 days, and works for timestamps without a 23:59:59.999 hack.

Engineer check. Grain is unchanged; this is purely about cost and speed. Correctness: the half-open range is also the safe way to filter timestamps, because BETWEEN '2025-03-01' AND '2025-03-31' misses everything after midnight on the 31st.

CREATE VIEW v_active_products AS
SELECT product_sk, name
FROM dim_product
WHERE is_active;

• A view stores only the query, so every read runs it against the current table. It's always fresh, and you pay for the query on every read.
• A table snapshot (the teammate's copy) stores rows. It's fast to read, but it's only as fresh as its last rebuild, as the step-through shows.
• A materialized view stores rows and keeps them in sync. How it stays in sync is the difference that matters:
  • Snowflake maintains it automatically in the background and always returns current results; the cost is the background maintenance compute. It's limited to a single table, with no joins or window functions (docs).
  • Postgres materialized views are stale until you run REFRESH MATERIALIZED VIEW (docs).
  • For multi-table pipelines in Snowflake, dynamic tables fill the gap: you set a target lag and Snowflake refreshes them, incrementally when it can (docs).

Promote a view to something materialized when it is expensive and read far more often than its inputs change.

Engineer check. Freshness is the trade-off: a view is always current, a snapshot is current only as of its last build, and a materialized view's freshness depends on the engine.

MERGE INTO dim_currency t
USING src_currency s
   ON t.currency_code = s.currency_code
WHEN MATCHED THEN UPDATE SET
    currency_name = s.currency_name,
    symbol        = s.symbol
WHEN NOT MATCHED THEN INSERT (currency_code, currency_name, symbol)
    VALUES (s.currency_code, s.currency_name, s.symbol);

• MERGE INTO … USING … ON joins the target to the source on the key; each source row either matches a target row or doesn't.
• WHEN MATCHED THEN UPDATE refreshes USD's name. WHEN NOT MATCHED THEN INSERT adds EUR. GBP isn't in the source, so it's left alone.
• Running it again finds every source row matched and writes the same values, so the table doesn't change. The build ran it twice to check.

Postgres's older form does the same with INSERT … ON CONFLICT (currency_code) DO UPDATE SET currency_name = EXCLUDED.currency_name (docs); EXCLUDED is the row that would have been inserted.

Engineer check. Grain: one row per currency code. Run twice: no change, which the build verified. NULL: a source row with a NULL currency_code never matches anything (NULL = NULL is not true), so it is inserted every run. Filter NULL keys out of the source.

SELECT customer_code, email, updated_at
FROM stg_customer
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_code
    ORDER BY updated_at DESC NULLS LAST, email DESC
) = 1
ORDER BY customer_code;

• ROW_NUMBER() OVER (PARTITION BY customer_code ORDER BY …) numbers the rows within each customer, starting at 1 for the winner.
• QUALIFY … = 1 filters on that number in the same query. It's to window functions what HAVING is to aggregates. Postgres has no QUALIFY, so you wrap the query in a subquery and filter WHERE rn = 1 (Snowflake docs).
• email DESC is the tiebreaker that makes C-002's winner deterministic. Without it, the two rows with the same timestamp could swap places between runs.
• ROW_NUMBER, not RANK: RANK gives both tied rows rank 1 and you'd keep both.

NULLS LAST is the part most answers miss. Snowflake and Postgres sort NULL as larger than any value, so in a descending sort NULLs come first (Snowflake, Postgres). Without NULLS LAST, C-001's winner is the row with no timestamp, ana@lost.com. DuckDB happens to sort NULLs last by default (docs), which is exactly how this bug hides in testing and appears in production. Write the null ordering explicitly.

Engineer check. Grain: exactly one row per customer_code, guaranteed by ROW_NUMBER. Run twice: deterministic only because of the full tiebreak. NULL: always state NULLS LAST in a newest-first dedup.

SELECT *
FROM orders
WHERE coupon_code IS DISTINCT FROM 'PROMO10'
ORDER BY order_id;

• SQL logic has three values: TRUE, FALSE and UNKNOWN. Comparing NULL with anything gives UNKNOWN, and WHERE keeps only TRUE rows, so coupon_code <> 'PROMO10' silently drops orders 3 and 5. The step-through shows the UNKNOWN (NULL) result on those rows.
• IS DISTINCT FROM treats NULL as an ordinary value: NULL is distinct from 'PROMO10', so those rows are kept.
• The portable spelling is WHERE coupon_code <> 'PROMO10' OR coupon_code IS NULL.

Here the naive filter loses $115 of no-coupon revenue, and nothing errors.

Engineer check. NULL: any <>, NOT IN or NOT LIKE filter on a nullable column needs a decision about NULLs. Make it explicit.

SELECT
    method,
    COALESCE(SUM(amount), 0)                                          AS total_amount,
    COALESCE(SUM(CASE WHEN status = 'CAPTURED' THEN amount END), 0)   AS captured_amount,
    COUNT(CASE WHEN status = 'FAILED' THEN 1 END)                     AS failed_count
FROM payments
GROUP BY method
ORDER BY method;

• SUM(CASE WHEN status = 'CAPTURED' THEN amount END): rows that aren't captured become NULL, and SUM ignores NULLs, so one scan produces a filtered total.
• COUNT(CASE WHEN status = 'FAILED' THEN 1 END): COUNT of an expression counts only non-NULL values, so only failures are counted.
• COALESCE(…, 0): SUM over only NULLs returns NULL, not 0. That's the wallet's captured amount (nothing captured) and the bank's total (its one amount is NULL). COALESCE turns those into 0.00 so a dashboard shows a number.
• One pass replaces three queries with three WHERE clauses joined back together.

Engineer check. Grain: one row per method. NULL: SUM of nothing is NULL and COUNT of nothing is 0, so wrap sums in COALESCE when a 0 is the honest answer.

SELECT DISTINCT f.customer_sk
FROM fact_sales f
WHERE NOT EXISTS (
    SELECT 1 FROM dim_customer d WHERE d.customer_sk = f.customer_sk
)
ORDER BY f.customer_sk;

• NOT EXISTS (…) keeps a fact row when the subquery finds no dimension row with its key. Keys 7 and 9 have none, so they're the orphans.
• NOT IN breaks on the dimension's bad row with a NULL key: 7 NOT IN (1, 2, 3, NULL) expands to … AND 7 <> NULL, which is UNKNOWN, so the whole condition is never TRUE and every row is filtered out. One NULL in the subquery and the check silently reports no problems.
• LEFT JOIN dim_customer d ON … WHERE d.customer_sk IS NULL is the other safe form (an anti-join).

A referential check that can't fail is worse than no check, because it tells you the data is clean.

Engineer check. NULL: never use NOT IN with a subquery that can return NULL. A fact row whose own key is NULL is also returned by NOT EXISTS, which is right: it's an orphan too.

In Snowflake:

WITH spine AS (
    SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, DATE '2025-03-01') AS full_date
    FROM TABLE(GENERATOR(ROWCOUNT => 7))
)
SELECT
    TO_NUMBER(TO_CHAR(s.full_date, 'YYYYMMDD')) AS date_key,
    s.full_date,
    DAYNAME(s.full_date)                        AS day_name,
    DAYOFWEEKISO(s.full_date) IN (6, 7)         AS is_weekend,
    COALESCE(o.orders, 0)                       AS orders
FROM spine s
LEFT JOIN daily_orders o ON o.order_date = s.full_date
ORDER BY s.full_date;

The same logic in portable SQL, as run on DuckDB to produce the output above:

WITH spine AS (
    SELECT CAST(d AS DATE) AS full_date
    FROM generate_series(DATE '2025-03-01', DATE '2025-03-07', INTERVAL 1 DAY) AS t(d)
)
SELECT
    CAST(strftime(s.full_date, '%Y%m%d') AS INT) AS date_key,
    s.full_date,
    dayname(s.full_date)                         AS day_name,
    isodow(s.full_date) IN (6, 7)                AS is_weekend,
    COALESCE(o.orders, 0)                        AS orders
FROM spine s
LEFT JOIN daily_orders o ON o.order_date = s.full_date
ORDER BY s.full_date;

• The spine is a gapless list of days that doesn't depend on the data: Snowflake builds it from GENERATOR, Postgres and DuckDB from generate_series.
• LEFT JOIN daily_orders keeps every calendar day; days with no orders get NULL, and COALESCE(…, 0) reports them as 0. Grouping the orders directly would simply omit March 3, 5 and 6, and a chart would draw a straight line across the gap.
• date_key as a YYYYMMDD integer is the classic warehouse date key: compact, sortable and readable.
• isodow / DAYOFWEEKISO returns 6 and 7 for Saturday and Sunday under ISO rules, which avoids locale differences over which day is day 1.

Why ROW_NUMBER() and not SEQ4() alone in Snowflake. SEQ4() gives unique, increasing numbers but doesn't guarantee they're gap-free, especially at scale, so DATEADD(day, SEQ4(), …) can silently skip dates. Snowflake's own docs recommend ROW_NUMBER() OVER (ORDER BY SEQ4()) for a gap-free sequence (docs).

Engineer check. Grain: one row per calendar day, guaranteed by the spine. NULL: missing days come back as NULL from the LEFT JOIN and must be turned into 0 explicitly.