Databricks SQL Tutorial: Reconcile Source and Target Row Counts

This tutorial guides you through reconciling source and target row counts using Databricks SQL. We’ll focus on the core principles and techniques, providing practical examples with runnable SQL scripts. The primary goal is to verify the accuracy of data transformations by comparing row counts before and after the transformation.

Scenario 1: Simple Count Reconciliation

Let’s start with a simple scenario where we have a source table and a target table. We’ll count rows in both and ensure they match. This is the fundamental step in verifying data transformations.

-- Create a source table
CREATE TABLE source_table (
    id INT
);

-- Insert some sample data
INSERT INTO source_table (id) VALUES (1), (2), (3), (4);

-- Create a target table
CREATE TABLE target_table (
    id INT
);

-- Insert the same data into the target table
INSERT INTO target_table (id) VALUES (1), (2), (3), (4);

-- Count rows in the source table
SELECT COUNT() AS source_count FROM source_table;

-- Count rows in the target table
SELECT COUNT() AS target_count FROM target_table;

Execute these scripts in Databricks SQL. The expected output is:

4

This confirms that the row counts match before any transformation happens. This is our baseline for further reconciliation.

Scenario 2: Reconciliation with a Simple Transformation

Now, let’s introduce a simple transformation. We’ll add a new column to the source table and then reconcile the row counts again. This demonstrates how row count differences can arise during data modification.

-- Add a new column to the source table
ALTER TABLE source_table ADD COLUMN value INT;

-- Update the new column with some values
UPDATE source_table SET value = id  2;

-- Count rows in the source table after the transformation
SELECT COUNT() AS source_count_transformed FROM source_table;

-- Count rows in the target table
SELECT COUNT() AS target_count FROM target_table;

After executing this, the `source_count_transformed` will likely be different from `target_count`. This is because the `UPDATE` statement modifies the data, increasing the row count in the source table.

4

The `target_count` remains at 4, as the target table hasn’t been updated. This clearly shows the reconciliation process in action.

Scenario 3: Reconciliation with a Filtering Transformation

Let’s consider a more complex transformation: filtering. We’ll filter rows from the source table based on a condition and then reconcile the row counts.

-- Create a source table with more diverse data
CREATE TABLE source_table (
    id INT,
    value INT
);

-- Insert sample data
INSERT INTO source_table (id, value) VALUES
(1, 10), (2, 20), (3, 30), (4, 40), (5, 50);

-- Create a target table
CREATE TABLE target_table (
    id INT,
    value INT
);

-- Insert data into the target table, filtering
INSERT INTO target_table (id, value)
SELECT id, value
FROM source_table
WHERE value > 20;

-- Count rows in the source table after filtering
SELECT COUNT() AS source_count_filtered FROM source_table;

-- Count rows in the target table
SELECT COUNT() AS target_count FROM target_table;

This time, the `source_count_filtered` will be less than the original `source_count` because of the `WHERE` clause. The target table only contains rows where `value > 20`.

2

This illustrates how filtering significantly impacts row counts and the importance of accurately reflecting those changes during reconciliation.

Conclusion

This tutorial demonstrated the fundamental techniques for reconciling source and target row counts in Databricks SQL. By systematically counting rows before and after transformations, you can effectively verify data accuracy and identify potential issues in your data pipelines. Remember to start with simple transformations and gradually increase the complexity to gain a solid understanding of the process.