Data Transformation with CTE

Let's first understand the basic concepts, advantages, and common use cases of using Common Table Expression (CTE) with Lakehouse SQL for data transformation.

Basic Concepts

A Common Table Expression (CTE) is an expression that defines a temporary result set that exists within the execution scope of a SQL query. It is typically used to simplify complex queries, perform recursive queries, or break down query steps. CTEs are usually introduced by the WITH keyword and can be used in subsequent SELECT, INSERT, UPDATE, and DELETE statements.

Basic syntax:

WITH cte_name AS ( SELECT column1, column2, ... FROM table_name WHERE condition ) SELECT * FROM cte_name;

Advantages

1. High Readability: CTE can make complex queries more readable and maintainable. By breaking down complex query logic into multiple understandable parts, CTE enhances the structure and clarity of the query. CTE makes testing complex queries simpler.

   • CTE is a SELECT statement that can be reused within a single query.
   • Complex SQL queries often involve multiple subqueries. Multiple subqueries can make the code difficult to read.
   • Using Common Table Expressions (CTE) can make your queries more readable.

2. Strong Reusability: CTE can be referenced multiple times within the same query, thus avoiding code duplication and improving query reusability.

3. Step-by-Step Data Transformation: CTE allows the data transformation process to be implemented step by step, with each step defined as an independent CTE, making it easier to debug and test.

4. Support for Recursive Queries: CTE supports recursive queries, making it very suitable for handling recursive data structures with hierarchical relationships, such as tree-structured data.

Usage Scenarios

Here are some common scenarios for data transformation using CTE:

1. Data Cleaning and Transformation

CTE can be used to clean and transform data step by step, such as removing duplicates and correcting data formats:

WITH cleaned_data AS ( SELECT DISTINCT column1, column2 FROM raw_table WHERE column1 IS NOT NULL ), transformed_data AS ( SELECT column1, UPPER(column2) AS transformed_column2 FROM cleaned_data ) SELECT * FROM transformed_data;

2. Grouping and Aggregation

CTE can be used for complex grouping and aggregation operations, and the aggregation results can be referenced in subsequent queries:

WITH total_sales AS ( SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id ) SELECT customer_id, total_spent FROM total_sales WHERE total_spent > 1000;

3. Data Merging and Joining

Using CTE can simplify multi-table joins and data merging operations:

WITH customer_orders AS ( SELECT c.customer_id, c.customer_name, o.order_id, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id ) SELECT * FROM customer_orders WHERE order_date > '2024-01-01';

Data Model

TPC-H data represents a data warehouse for an auto parts dealer, recording orders, items that make up the orders (lineitem), suppliers, customers, parts sold (part), regions, countries, and parts suppliers (partsupp).

Singdata Lakehouse has built-in shared TPC-H data, which each user can directly use by adding the data context, for example:

SELECT * FROM clickzetta_sample_data.tpch_100g.customer LIMIT 10;

Data Transformation Using Singdata Lakehouse SQL CTE

How to Define CTE

-- CTE Definition WITH supplier_nation_metrics AS ( -- Define CTE 1 using WITH keyword SELECT n.n_nationkey, SUM(l.l_QUANTITY) AS num_supplied_parts FROM clickzetta_sample_data.tpch_100g.lineitem l JOIN clickzetta_sample_data.tpch_100g.supplier s ON l.l_suppkey = s.s_suppkey JOIN clickzetta_sample_data.tpch_100g.nation n ON s.s_nationkey = n.n_nationkey GROUP BY n.n_nationkey ), buyer_nation_metrics AS ( -- Define CTE 2 SELECT n.n_nationkey, SUM(l.l_QUANTITY) AS num_purchased_parts FROM clickzetta_sample_data.tpch_100g.lineitem l JOIN clickzetta_sample_data.tpch_100g.orders o ON l.l_orderkey = o.o_orderkey JOIN clickzetta_sample_data.tpch_100g.customer c ON o.o_custkey = c.c_custkey JOIN clickzetta_sample_data.tpch_100g.nation n ON c.c_nationkey = n.n_nationkey GROUP BY n.n_nationkey ) SELECT -- No comma needed before the final SELECT statement n.n_name AS nation_name, s.num_supplied_parts, b.num_purchased_parts FROM clickzetta_sample_data.tpch_100g.nation n LEFT JOIN supplier_nation_metrics s ON n.n_nationkey = s.n_nationkey LEFT JOIN buyer_nation_metrics b ON n.n_nationkey = b.n_nationkey LIMIT 10;

Calculate the Amount Lost Due to Discounts

Use the lineitem table to get the price of items in the order (excluding discounts) and compare it with the order. First, determine the granularity of the comparison. Think step by step, first get the price of all items in the order excluding discounts, and then compare it with the order data of totalprice that has already calculated the discount.

WITH lineitem_agg AS ( SELECT l_orderkey, SUM(l_extendedprice) AS total_price_without_discount FROM clickzetta_sample_data.tpch_100g.lineitem GROUP BY l_orderkey ) SELECT o.o_orderkey, o.o_totalprice, l.total_price_without_discount - o.o_totalprice AS amount_lost_to_discount FROM clickzetta_sample_data.tpch_100g.orders o JOIN lineitem_agg l ON o.o_orderkey = l.l_orderkey ORDER BY o.o_orderkey;

Do not overuse CTE. Pay attention to code readability.

1. CTE can help improve the readability and reusability of queries.

2. Do not overuse CTE; pay attention to the size of the query.

   • SQL queries with multiple temporary tables are better than 1000-line SQL queries with numerous CTEs.
   • Keep the number of CTEs in each query small (depending on the size of the query, but usually < 5).

3. If you have doubts about the performance of CTE, check your query plan.

Resources

Common Table Expression (CTE)