Handling Nulls And Defaults

Why this matters

In real ETL pipelines, source data is often incomplete. Nulls and blanks can break joins, distort aggregates, and confuse downstream analytics. Clean, predictable defaults keep your jobs stable and your metrics trustworthy.

• Prevent broken foreign keys by assigning safe "Unknown" members.
• Keep aggregations accurate (count, sum, avg) by understanding how nulls behave.
• Stabilize transformations so schema changes and missing fields don’t crash jobs.
• Communicate data quality clearly by logging defaulting decisions.

Concept explained simply

Null means "missing/unknown". It is not zero, not an empty string, and not false. Defaults are fallback values used when data is missing or invalid, so your pipeline can still produce consistent output.

Mental model

Think of each field as a pipe with gates:

• Gate 1 Validate: Is the value present and in range?
• Gate 2 Standardize: Trim strings, parse dates, normalize cases.
• Gate 3 Default: If still missing, supply a safe fallback.
• Gate 4 Derive: Compute new fields from the now-stable inputs.

Applying defaults before derivations prevents cascaded nulls and brittle logic.

Core rules and patterns

• Treat blanks as nulls before defaulting: use TRIM + NULLIF or equivalent.
• Use COALESCE (SQL) / NVL / IFNULL to pick the first non-null value.
• Be explicit with strings vs numbers: "0" is not missing; 0 is a real value.
• Handle dates safely: if parsing fails, set a known fallback date and log.
• Use an Unknown dimension member for foreign keys (e.g., -1).
• Don’t hide data quality issues: default, but also log counts and examples.

-- Normalize blanks to null, then default
COALESCE(NULLIF(TRIM(col), ''), 'UNKNOWN')

-- Numeric default
effective_qty = COALESCE(qty, 0)

-- Date parsing with fallback
to_date_str = COALESCE(TRY_TO_DATE(raw_date, 'YYYY-MM-DD'), DATE '1970-01-01')

-- Foreign key with Unknown member
COALESCE(fk_id, -1) AS fk_id

Worked examples

Example 1: Strings (blanks vs null)

Input:
name        | city
------------+-----------
"Ada"       | ""
""         | null
"Grace"     | "London"

Transform:
clean_city = COALESCE(NULLIF(TRIM(city), ''), 'UNKNOWN')
clean_name = COALESCE(NULLIF(TRIM(name), ''), 'UNKNOWN')

Output:
clean_name  | clean_city
------------+-----------
"Ada"       | "UNKNOWN"
"UNKNOWN"   | "UNKNOWN"
"Grace"     | "London"

Example 2: Foreign keys (Unknown member)

Facts may arrive before dimensions are populated.

Input Orders:
order_id | customer_id
---------+------------
1        | 101
2        | null
3        | 9999   -- not found in dimension

Rule:
- Dimension has Unknown member (-1, 'Unknown Customer').
- After join, default missing to -1.

SQL sketch:
SELECT o.order_id,
       COALESCE(d.customer_key, -1) AS customer_key
FROM orders o
LEFT JOIN dim_customer d
  ON o.customer_id = d.customer_id;

Example 3: Aggregations with nulls

Input:
qty
----
null
2
0
5

COUNT(qty) = 3  -- ignores nulls
COUNT(*)   = 4  -- counts rows
SUM(qty)   = 7  -- nulls ignored
AVG(qty)   = 7/3

Be explicit about desired behavior (e.g., default qty to 0 before sum only if that is correct for your domain).

Example 4: Dates

Input:
raw_date
---------
"2022-04-01"
"04/01/2022"  -- invalid under strict ISO parser
null

Rule:
parsed_date = COALESCE(TRY_TO_DATE(raw_date, 'YYYY-MM-DD'), DATE '1970-01-01')

Output:
parsed_date
------------
2022-04-01
1970-01-01
1970-01-01

Production checklist

• Have you normalized blanks to null before defaulting?
• Are defaults documented and approved by data consumers?
• Do you use an Unknown member for every critical foreign key?
• Are you logging counts of defaulted records per field per run?
• Do aggregates explicitly handle nulls the way the business expects?
• Do unit tests cover null, blank, and malformed inputs?

Exercises

Complete both exercises below. Everyone can take the exercises and the quick test; only logged-in users will have progress saved.

Exercise 1: SQL — Clean blanks and set defaults

Goal: Normalize blanks to null and apply defaults using COALESCE.

1. Create a small table with columns: order_id INT, promo_code TEXT, shipping_city TEXT, quantity INT.
2. Insert rows including nulls and blanks.
3. Write a SELECT that outputs: clean_promo (default 'NOPROMO'), clean_city (default 'UNKNOWN'), clean_qty (default 1).

Exercise 2: DataFrame — Fill numeric, date, and string defaults

Goal: In a DataFrame, trim strings, convert blanks to null, then fill defaults: numeric to 0, dates to 1970-01-01, strings to 'UNKNOWN'.

1. Create a DataFrame with columns: amount (float), event_date (string), note (string) containing nulls and blanks.
2. Transform: trim note, set blanks to null, parse event_date, fill defaults.
3. Show the final DataFrame.

Common mistakes (and self-checks)

• Confusing empty string with null: Always NULLIF(TRIM(col), '').
• Defaulting 0 for missing numeric when 0 is meaningful: Confirm domain semantics first.
• Forgetting Unknown member for FKs: Leads to orphan facts. Ensure a single, consistent Unknown key per dimension.
• Defaulting before validation: Validate, standardize, then default.
• Breaking aggregates: COUNT(col) ignores nulls; COUNT(*) counts rows. Choose intentionally.

Practical projects

• Build a null-handling library of SQL macros/UDFs: blank_to_null, safe_date, fk_unknown.
• Create a data quality dashboard tracking default counts by column and day.
• Retrofit a fact table to support Unknown members and backfill existing rows.

Who this is for

• ETL/ELT developers who transform data into analytics-ready models.
• Data engineers maintaining pipelines with imperfect source data.
• Analytics engineers aligning semantics with business rules.

Prerequisites

• Basic SQL (SELECT, JOIN, CASE/COALESCE).
• Familiarity with a DataFrame API (e.g., Spark/Pandas) is helpful.
• Understanding of data types (string, numeric, date).

Learning path

• Start: Null semantics, blanks vs null, COALESCE/NULLIF.
• Next: Default strategies for strings, numbers, dates, and foreign keys.
• Then: Aggregations with nulls, testing strategies, and logging.
• Finally: Apply to a pipeline and add Unknown members to dimensions.

Next steps

• Implement the checklist in one of your staging models.
• Add counters for defaulted fields and alert when thresholds spike.
• Proceed to the quick test to verify understanding.

Mini challenge

You receive a daily orders file with nullable customer_id and product_id. The dimensions dim_customer and dim_product each include an Unknown member (-1). Write the transformation steps to:

• Normalize blanks to null.
• Left join to both dimensions.
• Default missing dimension keys to -1.
• Produce metrics: count of orders defaulted for each dimension.