Clean Nulls and Duplicates Before Joins

Context

Large operational datasets often contain missing join keys, partially populated attributes, and duplicate records. If you join them as-is, you can inflate row counts, lose valid matches, or produce misleading metrics in Google Cloud reporting workflows.

Question

Explain how you would prepare a large dataset before performing a join when the source tables contain both NULL values and duplicate records. Your answer should cover how you would identify which NULLs can be imputed versus filtered, how duplicate rows should be detected and resolved, and how those decisions affect join correctness.

Scope guidance

Focus on practical SQL reasoning in PostgreSQL rather than generic data-cleaning advice. The interviewer expects you to discuss join keys, COALESCE, LEFT JOIN versus INNER JOIN, deduplication with ROW_NUMBER() in a CTE, and how you would validate that the cleaned data does not introduce row multiplication or unintended data loss.

NULL handling in join keys

NULLs in join columns do not match each other in standard SQL joins, so rows with missing keys are usually excluded from INNER JOIN results. Before joining, you should decide whether those NULLs represent bad data to filter out, values that can be safely imputed, or records that should be preserved with a LEFT JOIN for downstream review.

SELECT *
FROM google_ads_clicks c
LEFT JOIN campaign_dim d
  ON c.campaign_id = d.campaign_id
WHERE c.campaign_id IS NULL;

COALESCE for nullable attributes

COALESCE is useful for filling nullable descriptive fields or standardizing fallback values, but it should be used carefully on join keys. Replacing a missing key with a placeholder can simplify reporting, but it can also create false matches if used directly in the join condition.

SELECT
  user_id,
  COALESCE(country_code, 'UNKNOWN') AS country_code
FROM user_profile_staging;

Deduplication with ROW_NUMBER()

When duplicate business keys exist, ROW_NUMBER() helps you keep the single best record per key based on recency, completeness, or source priority. This is often safer than SELECT DISTINCT because DISTINCT removes exact duplicates only and does not encode which record should win.

WITH ranked AS (
  SELECT
    customer_id,
    email,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customer_staging
)
SELECT customer_id, email, updated_at
FROM ranked
WHERE rn = 1;

CTEs for staged cleaning

CTEs make the cleaning flow explicit: one step for null analysis, one for deduplication, and one for the final join. This improves readability and reduces the risk of mixing business rules directly into a complex join query.

WITH cleaned_orders AS (
  SELECT *
  FROM orders_staging
  WHERE order_id IS NOT NULL
)
SELECT *
FROM cleaned_orders;

Join validation after cleaning

After cleaning, you should validate row counts, unmatched rates, and duplicate key counts to confirm the join behaves as expected. This is critical because a technically valid join can still be analytically wrong if one side remains many-to-many unexpectedly.

SELECT customer_id, COUNT(*)
FROM cleaned_customers
GROUP BY customer_id
HAVING COUNT(*) > 1;