Databricks SQL: Build Idempotent Recovery and Backfill Queries

This tutorial demonstrates building idempotent recovery and backfill queries within Databricks SQL. We will focus on the mechanics of these operations, using a simplified scenario and progressively complex queries. Idempotent recovery ensures that a query that is executed multiple times produces the same result, and backfill queries recreate data to address data loss or inconsistencies.

Script 1: Initial Data and Recovery

First, let’s create some sample data and then demonstrate basic recovery.

CREATE TABLE IF NOT EXISTS `orders` (
  order_id STRING,
  customer_id STRING,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O1', 'C1', '2024-01-01', 100.00),
('O2', 'C2', '2024-01-02', 250.50),
('O3', 'C1', '2024-01-03', 75.25);

-- Simulate data loss: Drop the table
DROP TABLE IF EXISTS `orders`;

Now, let’s re-create the table and data. This recovery process is idempotent—running it multiple times results in the same state.

CREATE TABLE IF NOT EXISTS `orders` (
  order_id STRING,
  customer_id STRING,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O1', 'C1', '2024-01-01', 100.00),
('O2', 'C2', '2024-01-02', 250.50),
('O3', 'C1', '2024-01-03', 75.25);

SELECT  FROM `orders`;


+------------------+---------------+------------+--------------------+
| order_id | customer_id | order_date | total_amount |
+------------------+---------------+------------+--------------------+
| O1 | C1 | 2024-01-01 | 100.00 |
| O2 | C2 | 2024-01-02 | 250.50 |
| O3 | C1 | 2024-01-03 | 75.25 |
+------------------+---------------+------------+--------------------+
1 rows


Script 2: Backfilling Data

Now, let’s explore backfilling data. We will add a new order, and then demonstrate how to re-apply this change if it’s lost.

-- Add a new order
INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O4', 'C3', '2024-01-04', 120.75);

-- Simulate data loss: Drop the table
DROP TABLE IF EXISTS `orders`;

CREATE TABLE IF NOT EXISTS `orders` (
  order_id STRING,
  customer_id STRING,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O1', 'C1', '2024-01-01', 100.00),
('O2', 'C2', '2024-01-02', 250.50),
('O3', 'C1', '2024-01-03', 75.25),
('O4', 'C3', '2024-01-04', 120.75);

SELECT  FROM `orders`;


+------------------+---------------+------------+--------------------+
| order_id | customer_id | order_date | total_amount |
+------------------+---------------+------------+--------------------+
| O1 | C1 | 2024-01-01 | 100.00 |
| O2 | C2 | 2024-01-02 | 250.50 |
| O3 | C1 | 2024-01-03 | 75.25 |
| O4 | C3 | 2024-01-04 | 120.75 |
+------------------+---------------+------------+--------------------+
4 rows


Script 3: Idempotent Recovery with Backfill

Let’s combine the recovery and backfill concepts. We’ll drop the table, add the new order, and then drop the table again. This simulates a situation where the backfill was lost, and we need to re-apply it.

-- Add a new order
INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O4', 'C3', '2024-01-04', 120.75);

-- Simulate data loss: Drop the table
DROP TABLE IF EXISTS `orders`;

CREATE TABLE IF NOT EXISTS `orders` (
  order_id STRING,
  customer_id STRING,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

INSERT INTO `orders` (order_id, customer_id, order_date, total_amount) VALUES
('O1', 'C1', '2024-01-01', 100.00),
('O2', 'C2', '2024-01-02', 250.50),
('O3', 'C1', '2024-01-03', 75.25),
('O4', 'C3', '2024-01-04', 120.75);

SELECT  FROM `orders`;


+------------------+---------------+------------+--------------------+
| order_id | customer_id | order_date | total_amount |
+------------------+---------------+------------+--------------------+
| O1 | C1 | 2024-01-01 | 100.00 |
| O2 | C2 | 2024-01-02 | 250.50 |
| O3 | C1 | 2024-01-03 | 75.25 |
| O4 | C3 | 2024-01-04 | 120.75 |
+------------------+---------------+------------+--------------------+
4 rows