Null Handling

A null usually represents a missing, unknown, or inapplicable value rather than an ordinary value. It is not automatically equivalent to zero, false, an empty string, whitespace, or the literal text 'NULL'. These distinctions matter when checking data quality during processing. An order amount of zero can be valid, while a null amount may indicate an incomplete transaction. An optional delivery instruction can legitimately be absent, while a missing order identifier should normally prevent publication of the record.

Begin with a data contract that defines which fields are required, what absence means for each field, and which representations are accepted. Incoming CSV files might use blank fields or sentinel strings such as 'N/A', while JSON distinguishes an absent property from a property explicitly assigned null. Parsers and serialization settings can change how these representations become processing-engine values. Normalize only documented representations, and preserve the original input when auditability or reprocessing matters.

Run checks within the processing pipeline, before incomplete records reach trusted datasets. For a required string identifier, test both nullness and whether the trimmed value is empty. A null-only check does not necessarily catch empty strings or whitespace. For numeric and timestamp fields, track parsing failures separately from source omissions: a failed conversion that produces null is a different quality issue from a value that was never supplied.

Measure completeness as the proportion of records containing an acceptable value for a field. Define the denominator, treatment of empty strings, and handling of empty datasets explicitly. Critical identifiers might require complete coverage, whereas an optional attribute can have a lower threshold. Evaluate these rules for each batch or streaming window, and consider checks by source or partition so that an overall average does not conceal a failing producer.

SQL null semantics require particular care. Use IS NULL or IS NOT NULL rather than equality comparisons with NULL. Ordinary comparisons involving null generally produce unknown, and WHERE retains only rows whose predicate evaluates to true. Consequently, a filter such as status <> 'cancelled' also excludes rows with null status unless they are explicitly included. COUNT(*) counts rows, while COUNT(column) counts non-null values. AVG(column) normally excludes null inputs, so replacing missing measurements with zero changes the result rather than merely cleaning the data.

Choose remediation according to business meaning. Reject or quarantine records missing essential keys, retaining a reason code and source reference for correction. Preserve nulls when absence is valid. Use COALESCE for an approved fallback, or NULLIF(TRIM(field), '') to normalize blank strings where appropriate. Imputation can keep analytical datasets usable, but it introduces assumptions and should be accompanied by an indicator showing which values were substituted. Silently defaulting every missing amount to zero can conceal upstream defects and bias financial totals.

Nulls also affect joins and operational reliability. Standard equality joins do not match null keys to each other, and outer joins introduce nulls for unmatched rows. Null-safe equality should be used only when matching absent keys is actually meaningful. Publish completeness metrics, rejected-record counts, and threshold breaches to monitoring systems. A failed quality rule must have an explicit response, such as stopping publication, quarantining affected records, or raising an alert; merely calculating a metric does not enforce data quality.