DE//AGENT
Explore
Tutorial Lesson 8 of 10 · The Python & SQL Series community intermediate

Production gotchas — NULL traps, implicit casts, silent drift

The bugs that survive testing compare wrong instead of failing loud: NOT IN over a nullable subquery, a comparison across types left to an implicit cast, and a schema change that silently turns a column to NULLs. You walk the review rules that ban each pattern, then use the schema-drift runbook to tell a hard failure from a silent one and to decide between an explicit cast and a quarantine.

At a glance

When Before merging SQL with NOT IN, cross-type comparisons, or a changed source schema; A pipeline went quiet instead of failing — checking for silent nulls
Where SQL
Why NULL traps and implicit casts compare wrong without erroring — the only defense is a rule you apply before the query runs.

Learning objectives

  • Spot NULL-unsafe `NOT IN` and `=` predicates and rewrite them with NOT EXISTS
  • Make every cross-type comparison an explicit cast, not an implicit one
  • Classify a schema drift failure as hard (job error) or silent (nulls)

What you need

  • [object Object]
  • A query with a cross-type comparison or a NOT IN to audit
  • The schema-drift runbook for the silent-null cases

Steps

  1. `NOT IN (subquery)` is banned where the subquery can return NULL — use `NOT EXISTS`. (from skill-sql-review )
  2. Spot NULL-unsafe `NOT IN` and `=` predicates. (from skill-sql-review )
  3. Comparisons across types must be explicit casts, not implicit. (from skill-sql-review )
  4. A drift can be silent — determine whether the failure is hard (job error) or silent (nulls). (from runbook-schema-drift )
  5. A type change gets cast explicitly, or reject and quarantine. (from runbook-schema-drift )
  6. Audit the quiet ones together — non-deterministic ordering is in the same review pass as the NULL and cast traps. (from skill-sql-review )

Check yourself

  • Every NOT IN in the query has a nullable-column story — or is rewritten as NOT EXISTS.
  • Every cross-type comparison shows an explicit CAST.
  • For the last schema change on the source, you can say whether it failed hard or silently.

Worked example

The two review rules this lesson enforces — from skill-sql-review 

2. NOT IN (subquery) is banned where the subquery can return NULL — use NOT EXISTS.
3. Comparisons across types must be explicit casts, not implicit.

What the schema-drift runbook does with a type change — from runbook-schema-drift 

Type change → cast explicitly, or reject and quarantine.

Sourced from

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

  • NOT IN with a nullable column never compares as you think; use NOT EXISTS.
  • An implicit cast that works in dev can flip semantics when the source type changes.
  • Silent nulls pass green pipelines — diff the source schema, not just the row counts.

Production considerations

  • Quarantine type-change rejections instead of letting the cast decide.
  • Run the sql-review rules in CI so the NULL and cast traps are caught before merge.

Diagram

production gotchas
   |
   |- NOT IN vs NULL --> never compares as expected --> NOT EXISTS
   |
   |- cross-type comparison --> explicit cast, not implicit
   |
   |- schema drift --> hard (job error) vs silent (nulls)
   |
   |- type change --> cast explicitly, or reject and quarantine

Registry stats

Verification community · Oct 23, 2026
Completeness 81
Trust multiplier ×1.25
Composite 34.15 · adequate
Freshness 1 days since last source check
Difficulty intermediate
Version 0.1.0
License CC-BY-4.0
Security flags none