2.3 SQL Data Cleaning & Transformation Techniques

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

2.3 SQL Data Cleaning & Transformation Techniques

Key Takeaways Explain This Topic AI

ANSI SQL compliance in Databricks SQL enforces strict type coercion by default, throwing runtime errors for invalid casts unless explicit functions like TRY_CAST() or COALESCE() are used.

High-performance string cleanup in Databricks SQL utilizes regular expressions via REGEXP_REPLACE(), REGEXP_EXTRACT(), and TRIM() to standardize unformatted strings across millions of records.

Handling missing data with COALESCE(col1, col2, 'Default') and NVL2(expr, if_not_null, if_null) prevents silent NULL propagation in aggregations and calculated metrics.

Deduplication in Databricks SQL is efficiently executed using QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) = 1 without requiring complex subqueries or self-joins.

The CASE WHEN ... THEN ... ELSE construct and array/higher-order functions (TRANSFORM, FILTER) enable complex data reshaping directly within SQL Warehouses.

Last updated: July 2026

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

• Function Signature Behavior
• COALESCE COALESCE(val1, val2, ...) Returns the first non-NULL value in the argument list
• NULLIF NULLIF(expr1, expr2) Returns NULL if expr1 = expr2 ; otherwise returns expr1

NVL2 NVL2(expr, if_not_null, if_null) Returns if_not_null if expr is not NULL; otherwise if_null -- 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:

PARTITION BY customer_id groups rows sharing the same customer identifier.

ORDER BY updated_at DESC ranks the newest update as row number 1.

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.