Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Nulls and semi-structured data require policy, validation, and flexible schemas. **Nulls**: COALESCE/IFNULL for defaults; define semantics (missing vs not applicable). Use sentinel values (e.g., -1, 'Unknown') for dimensions; document policy....
Architectural Logic: Nulls and semi-structured data require policy, validation, and flexible schemas. Nulls: COALESCE/IFNULL for defaults; define semantics (missing vs not applicable). Use sentinel values (e.g., -1, 'Unknown') for dimensions; document policy. Unstructured: Schema-on-read (Parquet, JSON); JSON_EXTRACT, from_json for extraction. Validate and handle malformed; optional chaining. Data Quality: Null checks in pipelines; dbt/great_expectations tests; log anomalies. Scalability: Separate columns for 'missing' vs 'N/A'; use struct/variant for flexible fields. Cost: Validate early to avoid propagating bad data.
Red Flag: Treating all nulls the same—loses semantic meaning. Pro-Move: Document null policy per column; use data quality framework; distinguish 'no match' vs 'not collected' vs 'N/A'.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.