Why does numeric conversion merge distinct customer IDs?

Prepare the diagnostic input

The source explicitly treats 001 and 1 as different customer identities. Casting to integer turns both into one, so grouping collapses them. This is an identity transformation defect, not a duplicate-customer finding. Keep the original text unless the approved target contract defines another reversible mapping.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE source_customers(external_id TEXT PRIMARY KEY,name TEXT);
INSERT INTO source_customers VALUES ('001','Demo A'),('1','Demo B');

Reproduce the misleading result and correct it

SELECT CAST(external_id AS INTEGER) AS target_key,COUNT(*) AS records FROM source_customers GROUP BY target_key ORDER BY target_key;

SELECT external_id AS target_key,COUNT(*) AS records FROM source_customers GROUP BY external_id ORDER BY target_key;

The wrong result has target_key=1 with two records. The correction keeps two text keys, each with one. A numeric-looking ID is not a quantity, and padding the converted value afterwards cannot reveal which original customer it represented.

Check the boundary before applying the fix

Add 0001 as a third distinct source ID. All three remain separate in the text mapping; they would all collide after the integer cast.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO source_customers VALUES ('0001','Demo C');

Uniqueness is explicitly case-sensitive text in this fixture. Blank IDs, whitespace policy and alphanumeric mappings need additional validation before import.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.