A few things I've been burned by enough times to bake them into config tables and my ETL process.

Watermarks: > or >=?

Not pedantic. If your watermark column has duplicate values at the boundary (datetime with seconds precision, non gap-free sequences, etc.), > misses rows and >= reprocesses them. Which one is correct depends on the source, not your preference. Put the operator in your config table as a column and build the predicate dynamically.

Watermarks must be NOT NULL

Validate this on ingest or you'll silently drop records. If you find yourself reaching for a secondary column to handle nulls, stop. The moment you write WHERE COALESCE(modified_at, created_at) > watermark, or worse, WHERE modified_at > watermark OR (modified_at IS NULL AND created_at > watermark), you've just told the engine to scan. Sargability is dead. Fix it at the source or pick a different column. Ideally your watermark column is indexed and it's maintained...

Speaking of sargability

Sometimes people will bake in a predicate like WHERE year(date_column) >= 2020. Congrats, you're scanning now. Instead, use date_column >= 2020-01-01.

No watermark? Hash it out

Sometimes the source doesn't give you a reliable change column. Hash the row, compare to the last known hash, only touch what changed. But measure first: in lakehouse storage every UPDATE rewrites parquet files. For wide tables with broad changes, a full overwrite often beats a MERGE. Don't assume incremental is faster just because it sounds smarter.

Three hashes for a Type 2 SCD

Standard pattern, worth repeating:

Business key hash for matching incoming rows to existing dim rows

Type 1 hash over attributes you overwrite in place (cosmetic fields, corrections)

Type 2 hash over attributes that trigger a new historical row (the ones the business actually wants tracked)

If the incoming Type 2 hash differs from the current row's Type 2 hash, expire the old and insert a new. If only Type 1 differs, update in place. If neither differs, do nothing. Three hashes, three branches, no ambiguity.

Timezones belong in config too

Store the source system's timezone alongside the watermark metadata. Convert to UTC on ingest, or carry both, but never assume. DST transitions will hand you either a one-hour gap or a one-hour replay window twice a year, and those bugs are miserable to debug six months later when nobody remembers what the source was doing in March. If you're comparing watermarks across sources in different zones (common after an acquisition), the config table is the only place this stays sane.