Glosarium
Contents

Data Engineering › Transformation & Analytics SQL

Standardizing Values

Making country codes, casing, units and currencies consistent.

Data Engineer · Junior

Also known as: standardizing values, data standardization, value normalization

Standardizing values means making the same thing look the same everywhere, so that grouping, joining and counting work. If one system says US, another USA and a third United States, a report grouped by country shows three countries.

(Not to be confused with “normalizing” a numeric column to a 0 to 1 range for machine learning, or with database normalization. Here it just means consistent representation.)

Common cases:

IssueExampleFix
Casing and whitespace" Berlin", "BERLIN"trim and lower-case
Codes vs namesUS, USA, United Statesmap to one standard such as ISO country codes
Units5 kg vs 5000 g, miles vs kmconvert to one unit
Currency19.99 USD vs 19,99 EURstore amount and currency; convert with a dated rate
Dates and time zones03/04/26, local timesparse to ISO 8601, store in UTC
BooleansY, yes, 1, truemap to a real boolean
select
    lower(trim(email))                                  as email,
    case upper(trim(country))
        when 'US' then 'US' when 'USA' then 'US'
        when 'UNITED STATES' then 'US'
        else upper(trim(country))
    end                                                 as country_code
from stg_signups

Practical advice

  • Do it in one place, early (see staging layers), so every downstream model gets the clean value.
  • Keep the original value next to the cleaned one when you can, so you can audit a mapping.
  • Use a mapping table (see reference data) instead of long case statements.
  • Handle unknowns visibly. Map unrecognized values to something like other or flag them, instead of silently dropping them.
  • Check ambiguous dates such as 03/04/26: it is March 4th or April 3rd depending on the source’s locale.
  • Profile first. See what values exist before deciding on rules. See data profiling.