Query Testing

Focus on logical and systematic approaches to identifying data quality issues in BigQuery. Instead of reviewing records one by one, you learn how to detect common problems using targeted SQL queries and repeatable validation patterns.

BigQuery is often used with large, heterogeneous datasets from domains such as finance, CRM, and marketing. These datasets frequently contain issues that are not immediately visible without structured analysis.

Rather than manual inspection, data issues can be identified by querying for common error patterns, including:

• Missing identifiers using IS NULL;
• Invalid numeric values, such as negative amounts;
• Outdated records based on a specific date threshold;
• Duplicate records detected with aggregation logic.

A typical validation workflow starts by establishing a baseline:

• Use SELECT COUNT(*) to understand the total number of rows;
• Apply filters like WHERE customer_id IS NULL or WHERE total_amount < 0 to isolate problematic entries;
• Detect duplicates by grouping on a key field and applying HAVING COUNT(...) > 1.

The distinction between WHERE and HAVING is critical. WHERE filters individual rows before aggregation, while HAVING filters aggregated results produced by GROUP BY, such as counts or sums.

Best practices include:

• Writing queries that proactively surface data quality issues;
• Using DISTINCT when appropriate to avoid duplicate-driven distortions;
• Approaching data validation as a logical diagnosis process rather than a reactive cleanup task.

Complete the chapter with a practical challenge that applies these techniques to investigate inconsistencies between order quantity, order amount, and total values, reinforcing analytical thinking in query design.