SQL for Data Profiling

In this lesson, you will learn how to build automated SQL scripts that proactively validate the health of your staging area by auditing data distribution, cardinality, and completeness.

The Objective of Automated Profiling

When ingesting raw data, "silent failures"—where data arrives but is malformed—are more dangerous than hard errors. We treat profiling as an automated diagnostic step in our pipeline, ensuring that every batch meets specific quality thresholds before it moves to the aggregation layer.

Auditing Core Metrics

To automate this, we create a profiling query that calculates three key health indicators for a table:

1. Completeness: The percentage of non-null records in critical columns.
2. Cardinality: The number of unique values versus the total row count to detect drift or unexpected duplication.
3. Distribution: Identifying if specific categories dominate the dataset, which can signal ingestion errors.

Here is a template you can integrate into a staging pipeline:

SELECT 
    'user_events' AS table_name,
    COUNT(*) AS total_rows,
    COUNT(user_id) * 100.0 / NULLIF(COUNT(*), 0) AS pct_completeness,
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(DISTINCT user_id) * 1.0 / NULLIF(COUNT(*), 0) AS cardinality_ratio,
    -- Distribution check: flagging if one category exceeds 90%
    MAX(category_count) * 1.0 / NULLIF(COUNT(*), 0) AS max_category_skew
FROM (
    SELECT user_id, 
           COUNT(*) OVER(PARTITION BY event_type) as category_count
    FROM staging_events
) t;

Building the Audit Loop

You can wrap this logic into a view or a stored procedure that executes immediately after the load process. By comparing the output of this query against historical norms (or predefined thresholds), you can trigger an alert if the pct_completeness drops below 99% or if the cardinality_ratio deviates significantly from the expected pattern for that specific ingestion source.

This approach transforms your staging area from a "black box" into a monitored environment, ensuring that the analytical layers downstream are only consuming verified, high-quality data.

Key Takeaway: Automated data profiling uses aggregation and distinct counts to establish a quantitative "health check" that detects data quality degradation before it impacts downstream analytical models.