Simplifying ETL Pipelines with SQL: Three Tips for Data Processing

Recently, I have been asked how to simplify our ETL pipeline so that customers can quickly visualize the data processing steps before building complicated ML models. Here are three tips you could immediately implement into your workflow to make your data more transparent.

• Generate input tables using SQL queries without modifying the database.
• Implement simple calculation steps by utilizing SQL functions.
• Set variables for the ETL calculation steps.

The benefit is obvious:

• Simplify workflow: Eliminate the need to transfer data between SQL, Python, or JavaScript for data quality checks.
• Reduce cycle time: Identify potential data issues using SQL-based data monitoring dashboards within your pipeline.
• Facilitate easier pipeline maintenance: Seamlessly integrate user data (CSV or Excel) into your workflow, saving time and cost.

Tip 1: Generating Input Tables

This method proves to be highly useful in various user scenarios, including:

• Testing query syntax without retrieving data from the actual database: You can validate and fine-tune your queries without impacting the live database.
• Combining customer data tables from CSV or Excel with tables from databases: By merging data from different sources, you can perform comprehensive analysis and gain insights.
• Establishing an input parameters table for subsequent queries: Create a dedicated table to store input parameters, facilitating easier execution of subsequent queries.

-- 1. create meta data table
WITH devmap AS (
  SELECT *
  FROM (VALUES
('John', 'New York', '10001'),
('Emma', 'California', '90001'),
('Michael', 'Texas', '75001'),
('Sophia', 'Florida', '32003'),
('James', 'Illinois', '60601'),
('Olivia', 'Ohio', '44101'),
('William', 'Georgia', '30301'),
('Ava', 'Washington', '98101')
) AS t(first_name, state, zip_code)
)
SELECT first_name, state, zip_code
FROM devmap;

** tip**: you can ask chatGPT to reformat csv table into query table like this, or generate mock tables for testing your queries.

Tip 2: Implementing ETL Steps in SQL

Although SQL is not primarily designed for complex scientific calculations, it can still be used effectively for many ETL (Extract, Transform, Load) tasks. Implementing ETL steps using SQL queries offers several advantages.

To demonstrate the ideas, we use Newton’s law of universal gravitation: the gravitational force between two objects is defined by

F = (G * m1 * m2) / r²

where: F is the gravitational force between the objects; G is the gravitational constant (approximately 6.67430e-11 N(m/kg)²); m1 and m2 are the masses of the two objects; r is the distance between the centers of the two objects.
Let’s assume, the input data table is following

SELECT m1, m2, distance FROM  objects

Perform the gravity calculation and wrap the results using a CTE (Common Table Expression). The reason for wrapping the calculation with a CTE is that it allows you to encapsulate all the calculation steps within a single query, referred to as the “gravity_calculation” query. This approach enables you to easily select and pick the desired end columns to present to the end users.

WITH gravity_calculation AS (
  SELECT
    m1,
    m2,
    distance,
    (6.67430e-11 * m1 * m2) / POWER(distance, 2) AS gravity
  FROM
    objects
)
SELECT
  m1,
  m2,
  distance,
  gravity
FROM
  gravity_calculation;

Tip 3: Parameterize Variables using Subqueries

To enhance clarity during debugging or experimentation, it is helpful to set variables for calculation steps. For instance, you can define the gravitational constant as ‘g_coeff’ within a CTE. This approach allows you to manage a longer list of variables as needed.

WITH vars AS (
  6.67430e-11 AS g_coeff 
),

and these variables are used as subquery in the subsequent main function

((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity

When putting everything together, the calculation steps can be summarized in the following query steps.

- set up variable Gravitational coefficient
WITH vars AS (
  6.67430e-11 AS g_coeff 
),
-- pull m1, m2,. distance from tables
gravity_calculation AS (
  SELECT
    m1,
    m2,
    distance,
    POWER(distance, 2), dist_square
    ((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity
-- pull m1, m2,. distance from tables
SELECT
  m1,
  m2,
  distance,
  dist_square
  gravity
FROM
  gravity_calculation;