The diagnostic:

Profile the column. Group by data type / format and count:

SELECT 
  CASE 
    WHEN user_id ~ '^[0-9]+$' THEN 'integer'
    WHEN user_id LIKE 'u_%' THEN 'prefixed'
    WHEN user_id ~ '^[a-f0-9-]{36}$' THEN 'uuid'
    ELSE 'other'
  END AS format,
  COUNT(*)
FROM logs GROUP BY 1

This tells you the scope — is 'other' 0.01% (ignore) or 15% (fix).

Find the source. Is the variation from different applications writing to the same table? Different pipeline versions? A migration in progress? Fixing the source is usually easier than downstream normalization.

Normalization strategies:

Cast-and-strip. If user_id is 'u_12345', '12345', and 12345, normalize to integer:
```
CAST(REGEXP_REPLACE(CAST(user_id AS VARCHAR), '^u_', '') AS INTEGER)
```
Add a derived column. Store the normalized version alongside the original. Prevents re-cleaning in every query.
Create a mapping table. If conversions are complex (some UUIDs, some integers, some prefixed strings corresponding to different systems), a mapping table source_id → canonical_id is cleaner than one-liner casts.

Caveats:

Information loss. If 'u_12345' and 12345 really are different entities (one is Users table, one is an anonymous tracking ID), collapsing them causes bugs.
Null propagation. CAST('abc' AS INTEGER) throws errors in strict databases. Wrap with TRY_CAST (Snowflake, BigQuery) or CASE WHEN ... ELSE NULL.
Collation effects. Some databases treat '01' vs. '1' as the same string, others don't. Profile your specific database.

Reporting to stakeholders:

"The user_id column has three formats representing two different entity types. 84% are integer user IDs; 12% are UUID session IDs (different entity); 4% are legacy strings from the pre-2020 mobile app. Joining them requires: (1) normalizing integer vs. prefix-integer, (2) explicitly excluding session IDs (they're not users), (3) mapping legacy IDs via the migration table. Silent joins without normalization drop ~16% of users."

Prevention at the source:

Enforce a single format in the ingestion layer.
Add a schema validation step that rejects malformed IDs.
Consistent naming: if a column is user_id, it represents users — not sessions, not devices. Name things precisely.

What senior candidates add:

Mention data contracts — formal specs between producer and consumer teams about format. Prevents drift.
Acknowledge that 'clean once in SQL' is a losing battle. The fix belongs upstream.

What do you do when a column's data type is inconsistent?

Draft your answer

Stuck? Peek at a hint

More data cleaning questions