Databricks SQL: Validate Production Tables Before Publication

This tutorial guides you through a crucial data engineering task: validating production tables before publishing them to downstream consumers. We’ll use Databricks SQL to ensure data quality and consistency, building a robust data pipeline. This approach aligns with Data Platform Engineering best practices focused on Governance, Reliability, and Operational considerations. We’ll be leveraging Delta Lake for reliable and versioned data storage and Unity Catalog for governance and access control.

Scenario

Let’s assume you’ve developed a new reporting dashboard that relies on sales data. Before making this data available to business users, you need to rigorously validate its accuracy and completeness. This tutorial will demonstrate how to use Databricks SQL to achieve this.

Phase 1: Initial Data Validation – Simple COUNT Checks

The first step is to verify basic counts. We’ll create a sample table, and then check the counts of key columns. This serves as a foundational sanity check. We’ll use a Delta Lake table for persistence and governance.

CREATE OR REPLACE TABLE sales_validation_counts (
  table_name STRING,
  column_name STRING,
  expected_count BIGINT
) WITH LOCATION '/delta/validation_counts'
AS
SELECT
  'sales' AS table_name,
  'customer_id' AS column_name,
  (SELECT COUNT() FROM sales) AS expected_count,
  'sales' AS table_name,
  'product_id' AS column_name,
  (SELECT COUNT() FROM sales) AS expected_count,
  'sales' AS table_name,
  'order_date' AS column_name,
  (SELECT COUNT() FROM sales) AS expected_count;
-- Final Validation SELECT
SELECT
  table_name,
  column_name,
  expected_count
FROM sales_validation_counts
WHERE table_name = 'sales';

Explanation:

• `CREATE OR REPLACE TABLE` ensures a new table is created if it doesn’t exist, and updates it if it does.
• `WITH LOCATION` specifies the Delta Lake table location.
• The `SELECT` statement within the `AS` clause populates the table with the expected counts for the ‘sales’ table.
• The final `SELECT` statement retrieves the validation counts.

Potential Beginner Mistake: Forgetting to specify the `table_name` in the final `SELECT` query if you’re running the entire process as a single query. This can lead to the validation data being lost.

Output:

+------------+----------------+--------------+
| table_name | column_name    | expected_count|
+------------+----------------+--------------+
| sales      | customer_id    | 1000          |
| sales      | product_id     | 500           |
| sales      | order_date     | 1000          |
+------------+----------------+--------------+

Phase 2: Data Type Validation

Now, let’s validate that the data types of key columns are as expected. This is crucial for preventing downstream data corruption. We’ll perform basic type checks using SQL.

-- Sample Data (simulated)
CREATE OR REPLACE TABLE sales (
  customer_id INT,
  product_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

-- Validate data types
SELECT
  COUNT()
FROM sales
WHERE customer_id IS NOT NULL AND customer_id::STRING != customer_id;  -- Check integer type

SELECT
  COUNT()
FROM sales
WHERE product_id IS NOT NULL AND product_id::STRING != product_id; -- Check integer type

SELECT
  COUNT()
FROM sales
WHERE order_date IS NOT NULL AND order_date::STRING != order_date; -- Check date type

SELECT
  COUNT()
FROM sales
WHERE order_amount IS NOT NULL AND order_amount::STRING != order_amount; -- Check decimal type

-- Final Validation SELECT
SELECT
  COUNT()
FROM sales
WHERE order_amount IS NULL; -- Check for null values in order_amount

Explanation:

• We create a sample `sales` table with common data types.
• The `SELECT` statements attempt to cast the columns to strings and compare them to their original data types. A non-zero count indicates a type mismatch.
• The final `SELECT` statement checks for null values.

Potential Beginner Mistake: Incorrectly casting data types. Databricks SQL is generally good at type inference, but explicit casting can be helpful for catching subtle issues.

Output:

+--------------------+
| COUNT()           |
+--------------------+
| 0                  |
| 0                  |
| 0                  |
| 0                  |
+--------------------+

Phase 3: Null Value and Range Validation

Finally, let’s verify that critical columns contain valid data within expected ranges. This ensures data integrity. This example looks for nulls and values outside the reasonable range for order_amount.

-- Sample Data (extended)
CREATE OR REPLACE TABLE sales (
  customer_id INT,
  product_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

-- Insert some sample data
INSERT INTO sales (customer_id, product_id, order_date, order_amount) VALUES
(1, 101, '2023-10-26', 100.00),
(2, 102, '2023-10-27', 50.50),
(3, 101, '2023-10-28', 200.00),
(4, 103, '2023-10-29', 75.25),
(5, 102, '2023-10-30', 120.75);


-- Null Value Check
SELECT
  COUNT()
FROM sales
WHERE order_amount IS NULL;

-- Range Check (order_amount within a reasonable range)
SELECT
  COUNT()
FROM sales
WHERE order_amount  1000;

-- Final Validation SELECT
SELECT
  COUNT() FROM sales;

Explanation:

• We extend the sample data to include a wider range of values.
• The `SELECT` statements check for null values in `order_amount`.
• The `SELECT` statement checks for `order_amount` values outside the typical range (0 – 1000 in this example). Adjust the range as needed for your data.
• The final `SELECT` statement verifies that the table has the expected number of rows.

Potential Beginner Mistake: Choosing an inappropriate range for value validation. The range needs to be aligned with your business rules.

Output:

+--------------------+
| COUNT()           |
+--------------------+
| 0                  |
| 0                  |
+--------------------+