Data Normalization: One Term, Three Different Meanings

A ticket says: normalize the customer table before Friday's load. Three engineers could pick it up and hand back three unrelated things. The first splits the table in two and adds a foreign key. The second rewrites every phone number into +14155551234 shape. The third divides each numeric column by its standard deviation and saves the divisors. All three did what the ticket said. One did what the person who wrote it wanted.

The word carries three technical traditions that never merged. Each has its own literature, its own tooling and its own way of failing. Asking which one is meant is the first debugging step, and skipping it is how a data-quality problem gets handed to a database administrator who correctly reports that the schema is already in third normal form.

Everything here was re-read against primary specifications and vendor documentation in August 2026: the Unicode standard, ISO 8601, ITU-T E.164, the IANA time zone database, the SQL standard, PostgreSQL 18 and the scikit-learn documentation. Where a number is measured rather than quoted, the method sits next to it.

The three jobs:

1. Normal forms. Designing table structure so each fact is stored in exactly one place. Relational theory, functional dependencies, JOINs. Owned by whoever owns the schema.
2. Canonicalization, usually called standardization. Making values that mean the same thing look the same: dates, phone numbers, addresses, casing, encodings, currency amounts, company names. This is the meaning that dominates data cleaning, record matching and data enrichment, and the largest of the three in day-to-day work. It has no elegant theory behind it. It has reference data, and that data carries a version number which changes several times a year.
3. Feature scaling. Putting numeric columns on a comparable scale so distance and gradient calculations are not dominated by whichever column happens to be measured in dollars.

Why guessing wrong is expensive

The three jobs fail in shapes that look nothing like each other.

A normal-form mistake surfaces as contradiction. Two rows disagree about the same fact, and nothing in the system can say which is right. A human notices, usually months later.

A canonicalization mistake surfaces as a number that is too low. Match rate, join yield, dedupe count. Nothing errors. Records fail to find their counterparts and land in the pile marked "new", where they become duplicates. On a 500,000-row enrichment run, one percentage point of match rate is 5,000 records, compounding every time the job runs.

A feature-scaling mistake surfaces as a model that was excellent in the notebook and mediocre in production. The gap between those two numbers is the whole symptom.

One figure turns up in almost every article on this subject: bad data costs the United States about $3.1 trillion a year. It traces to a September 2016 Harvard Business Review piece by Thomas C. Redman, which attributes it to IBM and sources its figures through links to trade press rather than a published methodology. Nobody has replicated it. Keep it out of a business case; the first person who chases the citation hits the same dead end you would have.

Count the records that failed to join last month instead.

Meaning 1. Normal forms

The gist

Normalization in the relational sense is decomposition driven by functional dependencies. Edgar Codd published the relational model in 1970 and the first three normal forms over the following two years; Boyce-Codd normal form arrived in 1974, Ronald Fagin added the fourth in 1977, the fifth in 1979 and domain-key normal form in 1981. Sixth normal form came out of the temporal-database work of Date, Darwen and Lorentzos in 2002.

The clearest statement of the goal is William Kent's, from A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM 26(2), February 1983: "Under second and third normal forms, a non-key field must provide a fact about the key, use the whole key, and nothing but the key." The version circulating with "so help me Codd" attached is a later embellishment. Kent did not write it.

The three anomalies

Store the same fact in several rows and three problems follow.

Update anomaly. This is the one that bites. A duplicated fact changes, you edit some copies, and the table contradicts itself with no way to tell which copy is authoritative.

Insertion anomaly. You cannot record one fact without inventing another. No employee can be added until they are attached to a project, because both live in the same row.

Deletion anomaly. Removing a row destroys a fact stored nowhere else. The last project ends and the employee goes with it.

The ladder

First normal form. One value per cell, no repeating groups. See below, because this one is not what people think.

Second normal form. No partial dependencies on a composite key. If the key is (order, product) and the product name depends only on product, the name belongs in its own table. Tables with single-column keys are in 2NF by construction, which is why this form rarely comes up.

Third normal form. No transitive dependencies. An employees table holding department and department head has the head depending on the department, not on the employee. Split the department out. This is where most production schemas stop, and stopping here is an engineering decision rather than laziness.

Boyce-Codd normal form. Every determinant must be a candidate key. It catches what 3NF misses when candidate keys overlap.

Fourth, fifth, sixth, domain-key. Multi-valued dependencies, join dependencies, irreducible temporal components, and a theoretical ceiling with no general algorithm to reach it. You will probably never decompose this far on purpose.

What first normal form actually says

Here is a correction that applies to the earlier version of this page as much as to anyone else's. The definition "all values must be atomic" has no rigorous content, because nothing in the relational model defines atomic. C. J. Date has made this argument for two decades, most directly in the essay What First Normal Form Really Means. A date divides into year, month and day. A string divides into characters. Atomicity is a decision about the attribute's declared type, not a property you can read off the data.

So the folk rule "1NF forbids arrays and JSON columns" is not something the standard says. The SQL standard's current edition is ISO/IEC 9075:2023, published in June 2023, and it includes SQL/JSON. PostgreSQL ships json and jsonb and recommends jsonb for most uses, staying measured about the trade: "even for applications where maximal flexibility is desired, it is still recommended that JSON documents have a somewhat fixed structure."

A better test than atomicity: if you query inside a value with LIKE, split it. If you only ever read it whole, leave it alone.

Where normal forms stop helping

Normalization optimizes for write integrity and costs reads, because every decomposition adds a JOIN. Analytical systems run the other way on purpose: star and snowflake schemas duplicate attributes into dimension tables, materialized views precompute aggregates, document stores keep related data together so one read returns a whole entity.

Kent said this in the same 1983 paper: "There is no obligation to fully normalize all records when actual performance requirements are taken into account." That sentence is forty-three years old and still gets treated as heresy in code review. Columnar storage has tilted the argument further: a wide denormalized table in Parquet costs far less than the same table in a row store.

Scraped data lands nowhere near any of this. It arrives as strings in a shape decided by someone else's markup, so land it raw and immutable, then model downstream. Normalizing on ingest makes every schema change a re-scrape. Normal forms are for the system of record, not for the landing zone.

Meaning 2. Standardization

The gist

This is canonicalization: choosing a single representation for each value and rewriting everything into it, so things that are the same in substance are the same in bytes. It is the precondition for matching, deduplication and enrichment. You cannot merge "Acme LLC" and "Acme, L.L.C." until both sides have been through the same rewriting.

What separates this meaning from the other two is that it is not an algorithm. It is an algorithm plus a table of facts about the world, and the table changes underneath you. Everything below has a version number.

Dates and times

The target is ISO 8601, which is two documents: ISO 8601-1:2019 for basic rules and ISO 8601-2:2019 for extensions. On the wire most systems use the RFC 3339 profile. Since April 2024 there is also RFC 9557, which adds bracketed suffixes carrying the IANA zone: 1996-12-19T16:39:57-08:00[America/Los_Angeles]. It exists because an offset is not a time zone. -08:00 tells you nothing about what that clock reads in July.

Zone rules are data, and they move. The IANA time zone database is at release 2026c of 8 July 2026, which alone records Alberta moving to permanent UTC-06:00 on 18 June 2026 and Morocco to permanent UTC+00:00 on 20 September 2026. A pipeline on two-year-old tzdata converts Alberta timestamps wrongly and never says so.

Parsing is where the damage happens. The default Python tool for loose date strings is dateutil, whose own documentation describes the parser as "forgiving with regards to unlikely input formats." Forgiving is the wrong disposition for a scraper. Run this against python-dateutil 2.9.0 and read the output carefully:

from dateutil import parser
parser.parse("01/02/2003").date()   # 2003-01-02  -> January 2, not 1 February
parser.parse("10.08.2026").date()   # 2026-10-08  -> October 8, not 10 August
parser.parse("March 2026")          # 2026-03-10  -> the day came from today's date
parser.parse("2026")                # 2026-08-10  -> month and day came from today

The last two lines are the dangerous ones. Missing components are filled from the current date, so the same input parses to a different value tomorrow. Nothing warns you, and both results look like clean data downstream. Pass an explicit format, or pass default=datetime(1,1,1) and reject whatever comes back with the sentinel intact.

In JavaScript the fix has been coming for years and has not landed: the Temporal API is documented on MDN as limited availability and not Baseline in August 2026, because it still does not work in some widely used browsers. The 2025 posts announcing that you can drop your date library were early.

Text, casing and Unicode

Two strings that render identically can be different sequences of code points, and comparison sees the bytes. The fix is a Unicode normalization form, defined in UAX #15, revision 57 dated 30 July 2025. There are four. NFD and NFC use canonical decomposition; NFKD and NFKC use compatibility decomposition.

import unicodedata as ud
a = "café"                 # NFC: e-acute as one code point
b = "café"                # NFD: plain e plus a combining acute
a == b                          # False
len(a), len(b)                  # 4, 5
ud.normalize("NFC", a) == ud.normalize("NFC", b)   # True

NFC is the right default for storage and interchange, because it is what the web already produces. PostgreSQL exposes a function and a predicate for it: normalize(text, NFC) and IS NFC NORMALIZED, both needing a UTF8 server encoding, with the docs noting that checking is often faster than normalizing text already normalized.

The compatibility forms are not a stronger version of the same thing. They are lossy, and the standard says so: "Normalization Forms KC and KD must not be blindly applied to arbitrary text. Because they erase many formatting distinctions, they will prevent round-trip conversion to and from many legacy character sets." What erasure means in practice:

ud.normalize("NFKC", "file")   # 'file'   ligature split
ud.normalize("NFKC", "①")      # '1'      circled digit flattened
ud.normalize("NFKC", "ＡＢＣ")  # 'ABC'    full-width forms folded
ud.normalize("NFKC", "㎞")     # 'km'     unit symbol expanded

For matching keys that is often exactly what you want. For storing what the page said, it is data loss you cannot undo.

Case folding has its own traps. "straße".upper() returns 'STRASSE', so uppercasing and lowercasing a German word does not round-trip. "İ".lower() returns two code points, 'i' plus a combining dot above. Use str.casefold() rather than str.lower() when the purpose is comparison.

Trimming whitespace does less than you think. Python's str.strip() removes characters Unicode classifies as separators and leaves the invisible formatting characters alone:

"x ".strip() == "x"   # True   no-break space, category Zs
"x​".strip() == "x"   # False  zero-width space, category Cf
"x­".strip() == "x"   # False  soft hyphen, Cf
"x⁠".strip() == "x"   # False  word joiner, Cf

Soft hyphens are the ones that come off scraped pages, inserted by publishing systems for line breaking and invisible in the browser and in your terminal. len("Acme­LLC") is 8. Your key comparison fails and your diff tool shows two identical strings.

Phone numbers

The target is E.164, and the current edition of the recommendation is E.164 (02/26), approved in February 2026. Most write-ups still cite the 2010 edition. The plan caps an international number at fifteen digits.

The advice you will read everywhere, including in the earlier version of this article, is to strip spaces, parentheses and dashes and add the country code. The second half is not implementable. A bare ten-digit string carries no evidence of which country it belongs to, so the country has to come from elsewhere in the record, and if it comes from a default you have invented data.

Use libphonenumber and pass the region explicitly. Read its FAQ first, because validity is not reachability: "Do not rely on libphonenumber to determine whether numbers are currently assigned to a specific user and reachable." Short codes are out of scope for PhoneNumberUtil entirely.

Note the release cadence. Tags land every two to three weeks, the notes say each "contains mostly metadata changes", and the Python port phonenumbers was at 9.0.36 on 1 August 2026. The library is a snapshot of national numbering plans, so the same input can normalize differently in two builds three weeks apart. Your normalized output has a version.

Addresses

Addresses are the hardest of the common fields, because the canonical form is set by a postal authority and mostly lives in licensed reference data. USPS publishes an Addresses API, currently v3.3.1, which verifies and standardizes to USPS specification; the legacy Web Tools interfaces are slated for replacement without a published date. Other countries have equivalents on their own terms.

The open option is libpostal, a statistical parser trained, per its README, on over a billion addresses from OpenStreetMap and OpenAddresses. Check its release history first: the last tagged release is 1.1, "Walla Walla", from 9 May 2018. The repository is not archived and issues still move. Nothing is broken and nothing is shipping.

A parser splits a string into components. It cannot tell you whether the address exists, and expanding "St." to "Street" becomes a lookup the moment the street is called St. Anne's.

Company names and reference codes

The example everyone reaches for is "Acme LLC" against "Acme, L.L.C.", and the usual advice is to strip legal-form suffixes. Almost nobody says which list to strip against.

There is a published one. The ISO 20275 Entity Legal Forms code list, maintained by GLEIF alongside the LEI system, assigns a four-character code to each legal form and covers more than 3,400 forms across more than 185 jurisdictions in their native languages. That is your suffix dictionary, and a far better start than a hand-written pattern that knows about Inc, Ltd and GmbH. Like country and currency codes, it is versioned data with a retrieval date.

Prices and numbers

For scraped commerce data this is the section that pays. Schema.org states the rules bluntly for its price property: "Use '.' (Unicode 'FULL STOP' (U+002E)) rather than ',' to indicate a decimal point. Avoid using these symbols as a readability separator", and use priceCurrency with an ISO 4217 code "instead of including ambiguous symbols such as '$' in the value".

The failure the naive cleanup produces:

float("1,234.56".replace(",", ""))   # 1234.56   correct
float("1.234,56".replace(",", ""))   # 1.23456   wrong by a factor of 1000
float("1 234,56".replace(",", ""))   # ValueError

The German price does not raise. It returns a plausible small number and continues. A price feed that silently divides European listings by a thousand passes every schema check you have, and the first person to notice is a customer.

Parse with locale rules, not with replace. Unicode CLDR is the reference data behind every serious implementation, currently at release 48.2 dated 17 March 2026. Indian grouping (1,23,456.78) breaks fixed-width assumptions too.

The pipeline

1. Profile. Find out which formats occur and how often, before writing any rules. Rules written from imagination cover the cases you imagined.
2. Define the canonical form. Write it down: one shape per field, with the reference standard named.
3. Parse into components. An address into street and city, a timestamp into instant and zone, a price into amount and currency.
4. Transform, then map to reference data. Apply the rules, then resolve against country codes, currencies, legal forms, industry classifications.
5. Validate. Digit counts, existence of the date, currency in the list.
6. Keep the original and stamp the versions. Store the raw value beside the canonical one, with the tzdata, libphonenumber, CLDR and ruleset revisions used. Without the raw value a rule change means a re-scrape. Without the versions, two rows normalized six months apart are not comparable.

Some of this is regular expressions and lookup tables. Some needs real libraries: phone parsers, geocoders, the platform's own Unicode functions. For interactive work on a few hundred thousand rows, OpenRefine is still worth knowing, at version 3.10.0 released 26 February 2026; its clustering features justify the install rather than the spreadsheet-like editing.

Where standardization breaks

Normalization is not reversible, so the aggressive version destroys evidence. NFKC folding, suffix stripping and casing all discard distinctions that were sometimes signal.

Both sides need the same treatment. A canonical form only helps if the thing you compare against went through the identical function. Two teams each running "their" normalizer produce two canonical forms and zero matches. One implementation, versioned, with tests.

Rules must be idempotent. Jobs get replayed after failures, so running a normalizer twice must produce what running it once produced. That sounds obvious until a rule that trims a trailing period meets a company name ending in "Inc.". Assert the property in a test.

Failure is silent by default. A normalizer that returns its input unchanged when it cannot parse looks exactly like one that succeeded. Return a status, count the rejects, alert when the rate moves.

Some fields have no canonical form. Free-text job titles, product descriptions and category names have no authority to appeal to. Cluster them instead, and accept that clustering is a judgement call needing review rather than a rule.

Meaning 3. Feature scaling

The gist

Numeric features arrive in incomparable units. Age runs 0 to 100, income runs 0 to 10,000,000, and any method computing a distance or a gradient lets income dominate because its numbers are bigger. Scaling removes the unit so the algorithm weighs columns on their information rather than their magnitude.

The methods

The reference implementation is scikit-learn's preprocessing module, at version 1.9.0 released 2 June 2026.

StandardScaler subtracts the mean and divides by the standard deviation, giving mean 0 and variance 1. It is the default, and the one people mean when they say "normalization", though the technique is standardization.

MinMaxScaler squeezes into [0, 1] with (x - min) / (max - min). It preserves shape and is hostage to outliers, since one extreme value compresses everything else into a narrow band. It has no defined behaviour for values outside the training range, which production data will supply.

RobustScaler centres on the median and scales by the interquartile range, the answer when the tails are long and real. Normalizer does something else entirely: it scales each row to unit norm rather than each column, which suits text vectors and cosine similarity and confuses everybody because of the name.

QuantileTransformer and PowerTransformer change the distribution rather than the scale. Yeo-Johnson accepts negatives; Box-Cox requires strictly positive input. A plain log transform does the same job crudely and is often enough.

When you need it

Scale-sensitive methods need it: distance-based algorithms such as kNN, SVM and k-means clustering, gradient descent in neural nets and linear models, regularized fits, and PCA.

Tree-based models do not. Decision trees, random forests and gradient boosting split on thresholds per feature, and monotonic rescaling leaves the splits identical. The qualification textbooks skip: PCA in front of a tree model reintroduces the requirement, because PCA is scale-sensitive even when the model after it is not.

The leakage rule, and what it is actually protecting

The rule is real. Fit the scaler on the training data, then transform test and production data with those saved parameters. Scikit-learn's common pitfalls page states it without hedging: "The general rule is to never call fit on the test data." The mechanism is Pipeline, which pairs each step with the right subset during cross-validation.

Whether breaking that rule inflates your score is a separate question, and we measured it rather than repeating the folklore. Setup: 400 trials, each with 60 rows and 400 columns of standard-normal noise, random binary labels, a 25% test split and a RidgeClassifier. No signal is present, so honest accuracy should sit at 0.5.

# leaky: the step is fitted on train and test together, then the split is used
X_all = StandardScaler().fit_transform(np.vstack([X_train, X_test]))
# honest: the same step inside a pipeline, fitted on train only
make_pipeline(StandardScaler(), RidgeClassifier()).fit(X_train, y_train)

Fitting StandardScaler, MinMaxScaler or QuantileTransformer on the full matrix before splitting moved mean test accuracy by less than half a percentage point, in the direction of worse. Swapping the scaler for a supervised step changed everything: SelectKBest(f_classif, k=10) fitted on all 60 rows scored 0.797, against 0.494 for the same selector inside a pipeline. Thirty points of accuracy conjured out of noise.

Measured on one machine with scikit-learn 1.8.0, NumPy 2.4.4 and Python 3.11, on synthetic Gaussian data with no signal in it. Your numbers will differ; the shape will not. Unsupervised scaling leaks little, and steps that see the labels leak enormously.

So the reason to keep the scaler inside the pipeline is not that it would otherwise flatter your score. The fitted scaler is a model artifact, and production has to apply the same means and standard deviations. A scaler fitted outside the pipeline is a set of numbers nobody saved.

Leakage of the second kind is not a rare mistake. Sayash Kapoor and Arvind Narayanan of Princeton, writing in 2022, catalogued eight types of leakage and found affected work in 329 studies across 17 scientific fields, with a case study in civil-war prediction where every claimed improvement over logistic regression evaporated once the leak was closed.

The other normalization, one layer down

Say "normalization" to someone who trains transformers and they will not think of StandardScaler. They will think of the normalization layer inside the network: LayerNorm, or more likely RMSNorm, proposed by Biao Zhang and Rico Sennrich in October 2019. RMSNorm drops LayerNorm's re-centering step on the argument that "re-centering invariance in LayerNorm is dispensable", and the authors report cutting running time by 7% to 64% depending on the model. It is the default in most current large language models. A fourth sense of the word, nested inside the third.

While here, retire the standard explanation for why these layers help. Batch normalization was introduced as a fix for internal covariate shift, and a 2018 MIT paper by Santurkar, Tsipras, Ilyas and Madry tested that claim and found that "such distributional stability of layer inputs has little to do with the success of BatchNorm". The effect they identified is a smoother optimization surface. The covariate-shift story survives in course notes because it is easier to draw.

Where feature scaling breaks

Production values leave the training range. MinMaxScaler maps anything above the training maximum past 1.0, and downstream code that assumed a bounded input breaks.

Sparse matrices. Centering a sparse matrix fills in every zero. Scikit-learn refuses rather than doing it: StandardScaler accepts sparse input only with with_mean=False, because "silently centering would break the sparsity and would often crash the execution by allocating excessive amounts of memory unintentionally".

Drift. Parameters fitted eighteen months ago describe eighteen-month-old data, and refitting them is a model change.

Telling them apart in one question

Ask what the goal is. The goal identifies the meaning faster than any vocabulary cue, because the vocabulary is shared.

Four rows, and they cover everything the word gets used for.

What this looks like on scraped data

Extraction hands you strings. Every field is text until somebody decides otherwise, and that decision is the normalization step whether anyone names it or not.

Each source has its own dialect, so rules are per-source. One site writes prices with a currency symbol and a comma decimal, another puts them in a data-price attribute in cents, a third splits pounds and pence across two elements. A single global cleanup function is silently wrong for one of them. Keep the rules next to the extractor they belong to, and the raw string in the record.

Normalize at the boundary, not on the way in. Land what the page said, byte for byte, and canonicalize in a step that can be rerun. When the suffix list changes, you reprocess rather than re-crawl.

An API changes the balance without removing the work. Reading a site's JSON endpoints gives you typed numbers and usually an ISO timestamp, which removes the parsing risk. It gives you no canonical company names, no canonical categories, and no guarantee that a field means the same thing on two sites.

Joining to anything external is where the strictness pays. Keys have to be identical on both sides: domains lowercased with www dropped, phones in E.164 with a known region, company names with legal forms stripped against a real list. The working order is profile, canonicalize, match and dedupe, then enrich. Getting it wrong produces an enrichment step with a low hit rate and no explanation.

Sites that change their markup under you are where a managed extraction service earns its keep, because normalization rules need a permanent owner rather than a one-off script. Buying the output as a data-as-a-service feed moves the version-pinning problem onto somebody else's build, and the question to ask them is which reference-data versions they pinned.

What breaks at ten thousand pages a night

A normalizer that works on a notebook full of examples behaves differently as a nightly job.

The reference data has a release schedule and your runtime does not follow it. Unicode 17.0.0 shipped on 9 September 2025 with 4,803 new characters and a total of 159,801. Python 3.11 reports unicodedata.unidata_version as 14.0.0. Your database, language runtime and ICU build each carry their own Unicode version, so text that normalizes one way in Postgres can normalize another way in the application that wrote it. The same split applies to tzdata, CLDR and libphonenumber metadata.

Wasted work multiplies. Compiling a regular expression inside a per-row function costs microseconds. Ten thousand pages with forty fields each is 400,000 calls, and a millisecond apiece is close to seven minutes of wall clock for nothing.

The reject pile is the monitoring signal. Count what failed to parse, per field per source, and chart it. A site changing its date format shows up as a spike in one field the night it happens, rather than in a quarterly review. A pipeline with no reject counter reports the same success rate whether it is working or returning empty strings. Keep the ugliest hundred real values per field as a CI fixture with their expected outputs, so a tightened rule tells you which other case it broke.

The word tells you nothing

Three traditions, four if you count the layer inside the network, one word, no shared theory. Nobody is going to fix the vocabulary.

What you can do is refuse to act on the word alone. Ask what the output is supposed to enable: a schema that cannot contradict itself, two records that can find each other, or a model whose columns carry equal weight. The answer names the job, and the job names the method. Then write down which version of the world you normalized against.