Databricks SQL: Aggregate Metrics at the Correct Grain

Databricks SQL Tutorial: Aggregate Metrics at the Correct Grain

This tutorial focuses on ensuring you aggregate metrics at the appropriate granularity within Databricks SQL. Incorrect grain can lead to misleading insights. We will progress through three scripts, each building upon the previous one.

Script 1: Basic Aggregation – Identifying Total Sales

This script demonstrates the fundamental principle of aggregation: summing values within a defined group. The challenge here is understanding the ‘grain’ – are we aggregating sales by day, by product, or something else?


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

-- Insert sample data
INSERT INTO sales_data (sale_id, product_id, sale_date, quantity, price) VALUES
(1, 101, '2024-01-01', 2, 25.00),
(2, 102, '2024-01-01', 1, 50.00),
(3, 101, '2024-01-02', 3, 25.00),
(4, 103, '2024-01-02', 1, 75.00),
(5, 102, '2024-01-03', 2, 50.00);

-- Aggregate sales by sale_date.  This is the 'grain' at which we're aggregating.
SELECT
  sale_date,
  SUM(quantity  price) AS total_sales
FROM
  sales_data
GROUP BY
  sale_date;


Explanation:

We create a temporary table `sales_data` to store our sample sales data. This ensures our demonstration is self-contained.
We populate this table with a small, but representative, dataset.
The core aggregation query groups the sales data by `sale_date`.  This defines our grain – we're summarizing sales for each individual date. The `SUM()` function then calculates the total sales for each date.

The query will return a result set with two columns: `sale_date` and `total_sales`.  This shows the total sales for each day in the sample data.
To run this, enter the SQL directly into the Databricks SQL query editor and execute.

Script 2: Refining the Grain - Aggregating by Product
Now, let's refine the grain. Instead of aggregating by date, let's aggregate sales by `product_id`.  This is a more common scenario – understanding sales performance for individual products.

-- Re-use the sales_data table from Script 1
-- (No need to re-create it)

-- Aggregate sales by product_id
SELECT
  product_id,
  SUM(quantity  price) AS total_sales
FROM
  sales_data
GROUP BY
  product_id;

Explanation:

We reuse the `sales_data` table created in the previous script.  This simplifies the process and avoids duplicate data creation.
This query aggregates sales by `product_id`.  The `SUM()` function calculates the total sales for each product.

The result set will now have two columns: `product_id` and `total_sales`. This shows the total sales generated by each product in the dataset.
To run this, enter the SQL directly into the Databricks SQL query editor and execute.

Script 3: Combining Grain Changes - Aggregating by Date and Product
This final script combines the previous two by aggregating sales data grouped by both `sale_date` and `product_id`. This demonstrates how to aggregate at different granularities.

-- Re-use the sales_data table from Script 1 and Script 2
-- (No need to re-create it)

-- Aggregate sales by sale_date and product_id
SELECT
  sale_date,
  product_id,
  SUM(quantity  price) AS total_sales
FROM
  sales_data
GROUP BY
  sale_date,
  product_id;

Explanation:

Again, we reuse the `sales_data` table from the previous scripts.
This query groups sales data by both `sale_date` and `product_id`.  The `SUM()` function then calculates the total sales for each combination of date and product.  This provides a more detailed view of sales performance.

The result set will have three columns: `sale_date`, `product_id`, and `total_sales`.  It shows the total sales for each product on each individual date.
To run this, enter the SQL directly into the Databricks SQL query editor and execute.
To validate the results, run the following query:

SELECT
  COUNT()
FROM
  sales_data;

This confirms the number of rows in the `sales_data` table.

Output


-- Script 1 Output:

sale_date | total_sales

----------|-------------

2024-01-01 | 125.00

2024-01-02 | 150.00

2024-01-03 | 100.00
-- Script 2 Output:

product_id | total_sales

-----------|-------------

101        | 75.00

102        | 150.00

103        | 75.00
-- Script 3 Output:

sale_date | product_id | total_sales

----------|-----------|-------------

2024-01-01 | 101        | 25.00

2024-01-01 | 102        | 50.00

2024-01-02 | 101        | 25.00

2024-01-02 | 103        | 75.00

2024-01-03 | 102        | 50.00
-- Validation Output:

1.  5