# The Gaps Between

> The data is not as clean as it looks.

Canonical URL: <https://datadriven.io/problems/the_gaps_between>

Domain: Python · Difficulty: hard · Seniority: L4

## Problem

A data-quality job profiles raw record exports before they load into the warehouse, and the records are dicts that don't all carry the same columns. For every column that appears in any record, report how many records hold a real value versus how many are blank, where blank means the value is `None` or the column is absent from that record entirely. Each column maps to its own `null_count` and `non_null_count`, and an empty batch produces an empty result.

## Worked solution and explanation

### What this really tests

This is a union-of-keys problem wearing a data-quality costume. Adding up the counts per column is the easy part; what separates candidates is realizing the column set is never handed to you. The records are ragged: a column can debut in record 50 and appear nowhere else, and an absent key is itself a null signal. Derive your columns from the first record and you will silently drop every column that shows up later, reporting a clean bill of health on data you never actually inspected.

---

### Break it into three decisions

#### Step 1: Discover the full column set first

Sweep every record and union their keys. This is the move people skip, and it is the whole problem: the first record is not authoritative about which columns exist.

#### Step 2: Count against the full column set

For each record and each known column, read the value with `record.get(col)` so a missing key resolves to None instead of raising. Compare with `is None`, not a truthiness check, so 0 and the empty string stay on the non-null side.

#### Step 3: Let missing keys fall out as nulls

Because you iterate the known columns rather than the keys each record happens to have, a record that lacks a column automatically scores a null for it. No special case needed.

---

### The solution

**Per-column null profiling over the unioned column set**

```python
def column_stats(records):
    columns = set()
    for record in records:
        columns.update(record)
    result = {col: {'null_count': 0, 'non_null_count': 0} for col in columns}
    for record in records:
        for col in columns:
            if record.get(col) is None:
                result[col]['null_count'] += 1
            else:
                result[col]['non_null_count'] += 1
    return result
```

> **Time and Space Complexity**
>
> **Time:** O(n * c) where n is the number of records and c is the number of unique columns. The first pass unions the keys, the second visits each (record, column) cell once.
> 
> **Space:** O(c) for the result dict, one entry per column.

> **Interviewers Watch For**
>
> The two tells: using `record.get(col)` so a missing key becomes None instead of a KeyError, and using `is None` rather than a falsy check so a legitimate 0 or '' is not miscounted as missing data.

> **Common Pitfall**
>
> Reading the column names off the first record only. If later records introduce new columns, those columns never make it into the report, and the profile looks complete when it is not.

---

## Common follow-up questions

- How would you add a type distribution per column? _(Tests extending the profiler to track int/str/float counts alongside null counts.)_
- What if the dataset has millions of records and hundreds of columns? _(Tests awareness of columnar profiling strategies and memory efficiency at scale.)_
- How would you flag columns that are entirely null? _(Tests post-processing: filter the report for columns where non_null_count is 0.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_gaps_between)
- [Python Interview Questions](https://datadriven.io/python-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.