Validating SQL Results for Duplicates, Nulls, and Row Counts

The First Sanity Check: Unexpected Row Counts

The most basic test you can run on any result set is to count the number of rows. Does the output have the number of records you expect? If you perform a LEFT JOIN and the row count suddenly explodes, it might indicate an incorrect join key. If you aggregate daily data over a month, do you get the expected number of days? A simple COUNT(*) can often be your first clue that something is wrong.

There are several ways to think about row count validation:

• Full Count: Is the total number of rows in a table what you expect?
• Partial Count: For a specific subset of data (e.g., records from a certain region), is the count correct?
• Relative Count: Does the row count in one table have a sensible relationship to the count in another? (e.g., "I expect to have many more order_items than orders").
• Recent Count: Are we still receiving new data? A check on rows created in the last 24 hours can be a vital health check for a data pipeline.

The following video provides a practical demonstration of how to implement these different row count checks in SQL.

Handling the Unknown: Finding and Counting Nulls

A NULL value in SQL represents missing or unknown information. It is not the same as zero or an empty string. For example, a shipping_date column might be NULL because an order hasn't shipped yet, which is valid. However, a NULL value in a customer_id column might indicate a serious data quality problem that could break downstream analytics. Learning to identify, count, and handle nulls is essential.

First, let's solidify our understanding of what a null value is and why it appears in datasets.

Identifying and Filtering Nulls

SQL provides the IS NULL and IS NOT NULL operators to specifically test for the presence or absence of nulls. You cannot use = NULL, as NULL is not considered equal to anything, including itself.

The same video demonstrates how to use these operators to explore and filter your data.

Counting Nulls

Once you know how to find nulls, you'll often need to count how many there are in a column. There are two common methods for this.

Method 1: The COUNT Function Trick

The COUNT aggregate function behaves differently depending on what you put inside the parentheses.

• COUNT(*) counts all rows.
• COUNT(column_name) counts all rows where column_name is not null.

You can use this difference to easily find the number of nulls.

Method 2: The CASE Statement Pattern

A more general and powerful method is to use a CASE statement to create a "flag" for null values (1 if null, 0 otherwise) and then sum those flags. This pattern is extremely useful and can be adapted to count rows that meet any condition, not just being null.

Ensuring Uniqueness: Detecting Duplicates

Duplicate records can wreak havoc on data analysis. They can cause inflated sums, incorrect counts, and skewed averages. A common data engineering task is to identify and often remove duplicate entries based on a "business key" or "natural key"—the set of columns that should uniquely identify a record (e.g., user_id, or the combination of product_id and order_date).

The standard SQL pattern to find duplicates is to group by the columns that should be unique and then use a HAVING clause to find groups with more than one record.

The general pattern is:

SELECT
  natural_key_col_1,
  natural_key_col_2,
  COUNT(*) AS num_duplicates
FROM
  your_table
GROUP BY
  natural_key_col_1,
  natural_key_col_2
HAVING
  COUNT(*) > 1;

Any rows returned by this query represent duplicate values in your key columns. If this query returns nothing, your data is unique with respect to those columns.

A Practical Workflow: Combining CTEs and Quality Checks

Now, let's connect this lesson with the previous one. Data quality checks are not just for source tables; they are essential for validating the output of your complex transformations. The CTEs you learned to write are the perfect place to apply these checks.

Imagine you've written a complex query to calculate monthly user activity. Your workflow could look like this:

1. Transform: Use a CTE to perform your main transformation logic.
2. Validate: Write a final SELECT statement that queries your CTE to perform data quality checks.

Here is an example of what that might look like:

WITH monthly_user_activity AS (
  -- A complex query from the previous lesson that joins, filters, and aggregates
  -- to produce a table with user_id, month, and activity_count
  SELECT
    user_id,
    DATE_TRUNC(event_timestamp, MONTH) AS activity_month,
    COUNT(*) AS activity_count
  FROM raw_events
  WHERE user_id IS NOT NULL
  GROUP BY 1, 2
)

-- Now, run quality checks on the result of the transformation
SELECT
  -- Check 1: Do we have any completely null rows?
  COUNT(*) AS total_rows,

  -- Check 2: Are there any nulls in our key columns?
  COUNTIF(user_id IS NULL) AS null_user_ids,
  COUNTIF(activity_month IS NULL) AS null_months,

  -- Check 3: Are there any duplicate user/month combinations?
  (
    SELECT COUNT(*)
    FROM (
      SELECT user_id, activity_month
      FROM monthly_user_activity
      GROUP BY user_id, activity_month
      HAVING COUNT(*) > 1
    )
  ) AS duplicate_records
FROM monthly_user_activity;

(Note: COUNTIF is a convenient BigQuery function equivalent to SUM(CASE WHEN ... THEN 1 ELSE 0 END)).

This single query both performs the transformation and reports on its quality. In real data pipelines, you might separate these steps, but the principle is the same: transform, then validate.

Conclusion

In this lesson, you've learned the fundamental SQL techniques for data quality validation, a cornerstone of the data engineering profession.

Here are your key takeaways:

• Data quality is crucial: Never assume a query that runs is a query that is correct. Always validate your results.
• Check Row Counts: Use COUNT(*) as a basic sanity check to ensure your transformations are not producing an unexpected number of rows.
• Investigate Nulls: Use IS NULL to find missing data and functions like COUNT(*) and COUNT(column) or SUM(CASE WHEN ...) to quantify the problem.
• Detect Duplicates: Use the GROUP BY ... HAVING COUNT(*) > 1 pattern to find rows that violate uniqueness constraints.
• Validate Transformations: Apply these checks to the output of your CTEs to build reliable, trustworthy data pipelines.

This lesson concludes the "Data Engineering and SQL Foundations" module. You have built a solid base in the language of data. In the next module, we will expand your toolkit by introducing the command line for file system navigation and the Python programming language for more complex data manipulation.