Skip to main content

04.9 PySpark Nulls, Types & Data Quality

PySpark null and data-quality architecture

Architect goal: Treat NULLs, types, casting, and malformed records as explicit data-contract decisions rather than incidental cleanup.

1. NULL is a business meaning, not just an empty value​

A NULL can mean missing, unknown, not applicable, failed derivation, or unavailable source data. These meanings can require different downstream behavior.

Do not automatically convert every NULL to UNKNOWN, 0, or an empty string.

2. Spark NULL semantics​

Spark SQL uses three-valued logic: an expression can evaluate to TRUE, FALSE, or UNKNOWN.

Use explicit predicates:

df.filter(F.col("country").isNull())
df.filter(F.col("country").isNotNull())

3. coalesce()​

coalesce() returns the first non-NULL expression:

df = df.withColumn(
    "country_final",
    F.coalesce("country", "billing_country", F.lit("UNKNOWN"))
)

Use it for intentional fallback logic, not blanket data-quality masking.

4. Conditional logic​

df = df.withColumn(
    "status_group",
    F.when(F.col("status") == "A", "ACTIVE")
     .when(F.col("status") == "I", "INACTIVE")
     .otherwise("UNKNOWN")
)

Decide explicitly whether otherwise() should produce a business value, NULL, or a rejected record.

5. fillna() and dropna()​

df.fillna({"country": "UNKNOWN", "quantity": 0})
df.dropna(subset=["customer_id"])

0 is not automatically equivalent to NULL. Drop only when the business contract says the row is unusable.

6. NULL and joins​

Normal equality joins do not treat NULL as an ordinary matching value. If NULL-safe equality is actually required:

condition = left.customer_id.eqNullSafe(right.customer_id)

Use this only when the business semantics explicitly require NULL = NULL behavior.

7. NULL and aggregations​

df.select(
    F.count("amount").alias("non_null_count"),
    F.count("*").alias("row_count"),
    F.sum("amount").alias("total_amount")
)

count(column) counts non-NULL values; count(*) counts rows. Interpret aggregates using their actual semantic grain.

8. Type discipline​

Inspect schemas before transformation:

df.printSchema()
print(df.schema.simpleString())

Production contracts should distinguish identifiers, measures, dates, timestamps, booleans, and text. Avoid STRING-for-everything.

9. Casting and decimals​

df = df.withColumn(
    "amount",
    F.col("amount_raw").cast("decimal(18,2)")
)

For financial data, Decimal is generally preferable when exact decimal semantics are required. Precision is total digits; scale is digits after the decimal point.

10. Safe type conversion​

Malformed casts must be tested against the target Spark/Databricks runtime. Use tolerant/safe conversion functions where supported and validate the resulting NULLs instead of assuming a cast succeeded.

11. Dates, timestamps and time zones​

Do not treat date, timestamp, and timestamp-without-time-zone semantics as interchangeable. Define source timezone, target timezone, format, precision, and business-day rules explicitly.

Common defects include UTC/local conversion errors, midnight boundary issues, daylight-saving transitions, and inconsistent source formats.

12. Parsing dates and timestamps​

df = df.withColumn(
    "order_date",
    F.to_date("order_date_raw", "yyyy-MM-dd")
)

df = df.withColumn(
    "event_ts",
    F.to_timestamp("event_ts_raw", "yyyy-MM-dd HH:mm:ss")
)

Malformed input should become a measured data-quality outcome rather than silently disappearing.

13. Identifier pitfalls​

Do not casually convert identifiers to numeric types. 001234 may be a business identifier rather than integer 1234. SAP-style document numbers and account codes often require STRING semantics.

Standardize boolean-like values such as Y/N, X/blank, 1/0, and true/false explicitly.

14. Enterprise data-quality architecture​

Bronze / Raw
    ↓
Schema validation
    ↓
Type normalization
    ↓
Null / domain / range checks
    ↓
 ┌───────────────┐
 │               │
Accepted       Rejected
 │               │
Silver          Quarantine
 ↓               ↓
Gold        Error metrics / audit

The rejected path is important: production quality systems should make bad data measurable and actionable.

15. Data-quality rules​

RuleExample
Required fieldcustomer_id IS NOT NULL
Rangequantity >= 0
Domainstatus IN ('A','I')
Formatvalid date / identifier
Referentialcustomer exists
Uniquenessbusiness key not duplicated
Freshnesssource meets SLA

16. Quarantine pattern​

Instead of silently dropping invalid rows, produce accepted and rejected datasets:

accepted = df.filter(F.col("customer_id").isNotNull())
rejected = df.filter(F.col("customer_id").isNull())

Useful rejection metadata includes _rejection_reason, _source_file, _ingestion_timestamp, and _pipeline_run_id.

17. Schema drift​

Monitor new/missing columns, type changes, precision/scale changes, nested-structure changes, and new domain values. Do not silently accept every schema change in critical pipelines.

18. SAP/BW-style example​

DOC_ID       STRING
COMPANY_CODE  STRING
AMOUNT_RAW    STRING
POSTING_DATE  STRING
CURRENCY      STRING

Normalize explicitly:

normalized = (
    raw
    .withColumn("amount", F.col("AMOUNT_RAW").cast("decimal(18,2)"))
    .withColumn("posting_date", F.to_date("POSTING_DATE", "yyyyMMdd"))
    .withColumn("company_code", F.trim("COMPANY_CODE"))
)

Then validate required fields and route malformed records to quarantine.

19. Data-quality metrics​

Track null rate, invalid-cast count, rejected-record count, unknown-domain count, duplicate-key count, schema-drift events, and freshness/SLA violations. Persist metrics with a pipeline run identifier.

20. Common anti-patterns​

  • Convert every NULL to zero.
  • Drop invalid rows silently.
  • Cast everything to STRING.
  • Ignore timezone assumptions.
  • Trust schema inference blindly for critical datasets.
  • Ignore malformed values.

21. Hands-on lab — NULL semantics​

Create rows containing NULL, empty string, zero, and unknown code. Compare isNull, coalesce, fillna, and when. Document the business meaning of every output.

22. Hands-on lab — type normalization​

Build valid and invalid decimal, date, timestamp, and identifier values. Implement accepted and rejected paths and measure rejection rate.

23. Hands-on lab — data-quality metrics​

Calculate row count, required-field NULL rate, invalid-date count, invalid-amount count, duplicate business keys, and rejected-record percentage. Persist the metrics with the pipeline run ID.

24. Troubleshooting workflow​

When a report suddenly shows NULLs:

  1. Check source schema and raw values.
  2. Check casting/parsing.
  3. Check NULL propagation.
  4. Check joins.
  5. Check when / otherwise.
  6. Check timezone conversion.
  7. Compare accepted and rejected records.

Do not start by changing the dashboard.

25. Architect decision matrix​

RequirementStarting approach
Required fieldExplicit isNotNull validation
Fallback valuecoalesce
Conditional standardizationwhen / otherwise
Remove unusable rowsdropna only when contract permits
Preserve invalid dataQuarantine/reject dataset
Financial amountDecimal
Business identifierUsually STRING
TimestampExplicit format + timezone contract
Production qualityRules + metrics + audit

26. Interview questions​

  1. What is Spark three-valued logic?
  2. Why use isNull() instead of equality with NULL?
  3. coalesce() vs fillna()?
  4. How do NULLs affect joins and aggregates?
  5. Why use Decimal for financial data?
  6. What are precision and scale?
  7. How do you handle malformed dates?
  8. Why are identifiers often strings?
  9. How would you design a quarantine pattern?
  10. How do timezone assumptions affect timestamps?
  11. How would you debug unexpected NULLs?
  12. How would you architect a Bronze-to-Silver data-quality contract?

27. Cheat sheet​

from pyspark.sql import functions as F

df.filter(F.col("x").isNull())
df.filter(F.col("x").isNotNull())
df.withColumn("x2", F.coalesce("x", F.lit("UNKNOWN")))
df.withColumn("status2", F.when(F.col("status") == "A", "ACTIVE").otherwise("UNKNOWN"))
df.fillna({"quantity": 0})
df.withColumn("amount", F.col("amount_raw").cast("decimal(18,2)"))
df.withColumn("d", F.to_date("raw_date", "yyyy-MM-dd"))
df.withColumn("ts", F.to_timestamp("raw_ts", "yyyy-MM-dd HH:mm:ss"))
condition = left.key.eqNullSafe(right.key)

28. Key takeaways​

  1. NULL is a semantic state, not automatically zero or empty.
  2. Spark SQL uses three-valued logic.
  3. Use explicit NULL predicates.
  4. Types are part of the data contract.
  5. Decimal is important for exact financial values.
  6. Identifiers should not be numerically cast without business justification.
  7. Dates and timestamps require explicit parsing and timezone rules.
  8. Malformed data should be observable, not silently discarded.
  9. Enterprise data quality needs accepted data, rejected data, metrics, and auditability.

Next​

04.10 Advanced PySpark — Catalyst-aware transformations, execution plans, caching, checkpointing, broadcast patterns, advanced functions, complex nested data, and production engineering.

Official references​