Databricks SQL Tutorial: Reduce Data Scans with Selective Predicates

This tutorial focuses on reducing data scans in Databricks SQL by strategically using selective predicates. We’ll explore how to filter data as early as possible in your queries to minimize the amount of data processed.

Script 1: Basic Filtering

The core idea is to select only the necessary columns and apply predicates to filter rows before joining or aggregating. This significantly reduces the volume of data scanned.

-- Create a table named 'orders' with sample order data.
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- Insert some sample order data.
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 101, '2023-01-15', 100.00),
(2, 102, '2023-02-20', 250.50),
(3, 101, '2023-03-10', 75.25),
(4, 103, '2023-04-05', 120.00),
(5, 102, '2023-05-12', 300.75);

-- Select only the order_id and total_amount from the orders table, filtering for orders with a total amount greater than 100.
SELECT order_id, total_amount
FROM orders
WHERE total_amount > 100.00;

This script creates a sample `orders` table and then selects only the `order_id` and `total_amount` columns, filtering for orders where `total_amount` is greater than 100. This is a simple example of reducing a data scan by selecting only the relevant columns.

The final SELECT statement returns the `order_id` and `total_amount` for orders with a total amount greater than 100.00.

-- Output:
-- order_id  total_amount
-- 2         250.50
-- 4         120.00
-- 5         300.75

Script 2: Filtering on Multiple Columns

Let’s expand on the previous example to filter on multiple columns. This demonstrates how to further reduce the amount of data scanned by combining filters.

-- Create a table named 'customers' with sample customer data.
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255),
    city VARCHAR(255),
    country VARCHAR(255)
);

-- Insert some sample customer data.
INSERT INTO customers (customer_id, customer_name, city, country) VALUES
(101, 'Alice Smith', 'New York', 'USA'),
(102, 'Bob Johnson', 'London', 'UK'),
(103, 'Charlie Brown', 'Paris', 'France'),
(101, 'David Lee', 'New York', 'USA'),
(102, 'Emily Davis', 'London', 'UK');

-- Select the customer_name and country from the customers table, filtering for customers in New York or London.
SELECT customer_name, country
FROM customers
WHERE city IN ('New York', 'London');

This script creates a `customers` table and inserts sample data. The query filters for customers located in ‘New York’ or ‘London’, using the `IN` operator to efficiently filter based on multiple values. Again, we filter the data early to reduce the scan.

-- Output:
-- customer_name   country
-- Alice Smith      New York
-- Bob Johnson      London
-- David Lee        New York

Script 3: Filtering on Date Ranges

Now, let’s introduce date filtering. Filtering on dates early in the query can significantly reduce the data scanned, especially in large tables with time-series data. This is more complex than simple equality filtering, and the SQL engine can often optimize this more efficiently.

-- Create a table named 'sales' with sample sales data.
CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

-- Insert some sample sales data.
INSERT INTO sales (sale_id, product_id, sale_date, quantity, price) VALUES
(1, 1, '2023-01-01', 10, 20.00),
(2, 2, '2023-01-15', 5, 30.00),
(3, 1, '2023-02-10', 8, 22.00),
(4, 3, '2023-03-01', 12, 15.00),
(5, 2, '2023-03-15', 7, 28.00),
(6, 1, '2023-04-01', 15, 18.00);

-- Select the sale_id and quantity from the sales table, filtering for sales made between '2023-02-01' and '2023-03-31' (inclusive).
SELECT sale_id, quantity
FROM sales
WHERE sale_date BETWEEN '2023-02-01' AND '2023-03-31';

This script creates a `sales` table and inserts sample data. The query filters sales records based on a date range, again highlighting the effectiveness of filtering early in the query to reduce the scanned data. Using `BETWEEN` is a concise way to define a date range, but consider using `>=` and `<` operators for performance tuning in some cases.

-- Output:
-- sale_id  quantity
-- 3         8
-- 5         7
-- 6         15