2.3 SQL Data Cleaning & Transformation Techniques

2.3 SQL Data Cleaning & Transformation Techniques

In the Databricks Data Intelligence Platform, raw data landing in Bronze tables frequently contains unformatted strings, invalid timestamps, missing values, duplicate records, and nested semi-structured payloads. Data analysts operating within Databricks SQL must master robust, high-performance data cleaning and transformation techniques. By leveraging ANSI SQL standards, safe type coercion, regular expressions, windowed deduplication, and complex array transformations, analysts can reliably refine raw Bronze landing data into production-ready Silver and Gold datasets.

---

Safe Type Conversion and ANSI SQL Standards

Databricks SQL operates under ANSI SQL compliance by default. Under ANSI mode, invalid data operations—such as casting the string 'INVALID' to an integer or dividing by zero—throw runtime exceptions that abort query execution. Analysts must write resilient type conversion code to handle dirty input data gracefully.

CAST vs. TRY_CAST in Databricks SQL

The standard CAST(expression AS data_type) function attempts to convert a value to the target data type. If the expression contains unparseable characters, CAST fails and throws an error.

To prevent query failures when processing raw datasets, Databricks SQL provides TRY_CAST(expression AS data_type). If conversion fails, TRY_CAST returns NULL instead of raising a runtime exception.

-- Unsafe conversion: Fails if raw_age contains non-numeric strings like 'N/A'
SELECT CAST(raw_age AS INT) FROM bronze_users;

-- Safe conversion: Returns NULL for invalid values without failing the query
SELECT 
  raw_age,
  TRY_CAST(raw_age AS INT) AS clean_age,
  TRY_CAST(raw_signup_date AS DATE) AS clean_signup_date
FROM bronze_users;

Preventing Runtime Numeric Overflow and Date Failures

Similarly, functions such as TRY_ADD(), TRY_MULTIPLY(), and TRY_TO_TIMESTAMP() safely handle arithmetic overflow or malformed timestamp strings. Combining TRY_CAST with COALESCE allows analysts to substitute fallback default values for invalid records:

SELECT 
  user_id,
  COALESCE(TRY_CAST(raw_score AS DECIMAL(10, 2)), 0.00) AS clean_score
FROM bronze_users;

---

String Sanitization and Pattern Matching

Raw data ingested from web forms or legacy CSV files often suffers from inconsistent spacing, mixed casing, and special characters. Databricks SQL provides powerful string manipulation and regular expression functions optimized for the Photon execution engine.

Regex Cleaning with REGEXP_REPLACE and REGEXP_EXTRACT

Regular expression functions allow analysts to strip unwanted noise or extract structured patterns from free-text fields.

-- Sanitizing phone numbers to extract exactly 10 digits
SELECT 
  raw_phone,
  REGEXP_REPLACE(raw_phone, '[^0-9]', '') AS sanitized_phone,
  -- Extracting domain names from email addresses
  REGEXP_EXTRACT(email, '^[^@]+@([^@]+)$', 1) AS email_domain
FROM bronze_contacts;

String Trimming, Formatting, and Splitting

• TRIM(str) / LTRIM() / RTRIM(): Removes leading and trailing whitespace.
• LOWER(str) / UPPER(str) / INITCAP(str): Standardizes text casing.
• SPLIT(str, regex): Converts a delimited string into a SQL ARRAY.

SELECT 
  LOWER(TRIM(customer_email)) AS clean_email,
  INITCAP(TRIM(full_name)) AS formatted_name,
  SPLIT(full_address, ',')[0] AS street_address
FROM bronze_customers;

---

Managing NULLs and Conditional Logic

Handling NULL values correctly is vital for preventing skewed analytics metrics. In SQL, NULL represents an unknown value, and any arithmetic operation involving NULL yields NULL.

COALESCE, NULLIF, and NVL2 Patterns

-- Replacing empty string sentinel values with NULL, then applying a fallback default
SELECT 
  customer_id,
  COALESCE(NULLIF(TRIM(phone_number), ''), 'UNPROVIDED') AS contact_phone
FROM bronze_customers;

Aggregations and NULL Propagation Rules

Understanding how aggregate functions process NULL values is critical for exam success:

• COUNT(*) counts all rows, including rows where all columns are NULL.
• COUNT(column_name) ignores NULL values, counting only non-NULL entries.
• AVG(column_name), SUM(column_name), and MIN() / MAX() ignore NULL values. If all rows in a group are NULL, the function returns NULL.

---

Advanced Deduplication with Window Functions and QUALIFY

Duplicate records frequently occur due to web-hook retries or repeated raw batch file ingestion. While SELECT DISTINCT removes identical rows across all columns, real-world data cleaning often requires deduplicating based on a business key while retaining the most recent record based on a timestamp.

Deduplication via ROW_NUMBER and QUALIFY Clause

Databricks SQL supports the QUALIFY clause, which filters the results of window functions directly without requiring nested subqueries or CTEs.

-- Modern Databricks SQL pattern for deduplicating records
SELECT 
  customer_id,
  email,
  account_status,
  updated_at
FROM bronze_customers
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id 
  ORDER BY updated_at DESC, ingestion_time DESC
) = 1;

In this pattern:

1. PARTITION BY customer_id groups rows sharing the same customer identifier.
2. ORDER BY updated_at DESC ranks the newest update as row number 1.
3. QUALIFY ROW_NUMBER() ... = 1 filters out all historical or duplicate rows in a single readable execution step.

---

Structural Data Reshaping with Higher-Order SQL Functions

Semi-structured JSON payloads in Delta Lake often contain nested arrays and structs. Databricks SQL provides higher-order functions to clean and transform array elements natively.

Exploding Arrays and Transforming Structs

• EXPLODE(array): Flattens an array into multiple rows.
• TRANSFORM(array, expr): Applies a lambda transformation to every element in an array.
• FILTER(array, expr): Filters array elements based on a logical predicate.

-- Higher-order SQL transformation example
SELECT 
  order_id,
  -- Uppercasing all item tags in a nested array
  TRANSFORM(item_tags, tag -> UPPER(TRIM(tag))) AS clean_tags,
  -- Filtering out items priced below $5.00
  FILTER(line_items, item -> item.price >= 5.00) AS premium_items
FROM bronze_orders;

---

Real-World Scenario: End-to-End Silver Layer Data Cleaning Pipeline

A data analyst is building a clean Silver layer view silver_clean_orders from raw web-clickstream data (bronze_web_clicks). The raw dataset contains unparsed strings, duplicate click events, dirty numeric prices, and empty string customer IDs.

CREATE OR REPLACE VIEW silver_clean_orders AS
SELECT 
  CAST(click_id AS STRING) AS clean_click_id,
  COALESCE(NULLIF(TRIM(customer_id), ''), 'ANONYMOUS') AS customer_id,
  TRY_CAST(raw_price AS DECIMAL(10, 2)) AS item_price,
  REGEXP_REPLACE(user_agent, '[^a-zA-Z0-9 ./]', '') AS clean_user_agent,
  COALESCE(TRY_TO_TIMESTAMP(event_time), CURRENT_TIMESTAMP()) AS event_timestamp
FROM bronze_web_clicks
WHERE click_id IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY click_id 
  ORDER BY event_time DESC
) = 1;

By combining TRY_CAST, COALESCE, NULLIF, REGEXP_REPLACE, and QUALIFY ROW_NUMBER(), the analyst produces an error-tolerant, fully sanitized Silver layer table that guarantees data quality for executive reporting.