Data Cleaning Exercises

Module 17

SQL cleaning

A declared dialect, CTEs, CASE, trimming, typed casts, window functions and validation queries.

  1. Validate before you cast in SQL

    Check text against an explicit pattern before casting in DuckDB, and build a first-failing-reason column with one ordered CASE. · 10 min

  2. One reason column for clean and exceptions

    Compute a single reason column in rule order and split clean rows from exceptions with it. · 10 min

  3. TRY_CAST for quarantine

    Convert text to types in SQL without failing the query, and quarantine values that don't convert. · 7 min

  4. Profiling queries in SQL

    Write a few reusable queries that profile any column in DuckDB. · 7 min

Module 17 quiz

Practice questions: choose an answer and check it to see the explanation. Nothing here is timed or graded.

  1. 1. DuckDB: SELECT TRY_CAST('12a' AS INTEGER) returns?
  2. 2. Why put cleaning steps in CTEs (WITH clauses)?
  3. 3. What does COUNT(column) count compared with COUNT(*)?
  4. 4. SELECT trim(' a ') || '|' returns?
  5. 5. What does QUALIFY do in DuckDB?
  6. 6. Why prefer CAST with validation over string manipulation for dates in SQL?