Patch Gaps

When managing real-world data, we often run into incomplete information, missing values, skipped dates, or NULL fields that break our reports. Imagine we’re building a daily sales report. What happens if no orders were placed on a given day?

The date disappears from our chart, making trends hard to read. Or let’s say we’re tracking customer activity, but some users are missing their LastLogin or LastPurchaseDate. These gaps can mislead our analysis.

That's where the Patch gaps pattern comes in. In this lesson, we will learn how to handle missing or NULL data by filling in the blanks or generating the missing records. We will explore different types of gaps: missing rows, missing dates, and NULL fields, and learn strategies to patch them effectively.

By the end of this lesson, we’ll be able to:

• Detect and handle NULL values using standard SQL functions.

• Generate sequences to fill in missing time intervals or identifiers.

• Join data with generated ranges to ensure we never miss a data point.

• Use this pattern to build reliable reports and consistent datasets.

Pattern overview

Category:

• Filtering Patterns

Intent:

To fill in missing values or generate absent records so that the data is complete for accurate analysis, reporting, or visualization.

Motivation:

Real-world data often contains gaps, NULL values, missing dates, or absent records. If left unpatched, these gaps can distort analytics and lead to incorrect conclusions. For instance, a sales chart might suggest a dip in performance when a date is simply missing from the dataset.

This pattern helps us detect and fill those holes, making our data analysis more truthful and actionable.

Also known as:

• Data imputation

• Gap filling

• NULL handling

• Time series completion

Structure

There are two main structures used in this pattern:

1. Patch NULLs using COALESCE or CASE