Refusing to Convert Quantities When Nobody Knows the Unit

A number without a unit is not a quantity. Imported data often carries a value such as 12 under a column called size or weight. A conversion that guesses the unit can produce plausible numbers that are wrong. This article proposes a conditional design. Adapt it to your data and test it yourself.

The primary artifact: unit provenance and blocked conversion table

Keep one row for each imported field value, or for each field when a source is uniform:

source_name
field
raw_value, stored exactly as received
unit_text, exactly as received, possibly empty
unit_origin: one of declared_per_record, declared_per_source, inferred, none
unit_status: confirmed, unconfirmed, or unknown
conversion_status: allowed, blocked, or pending_review
reason, in plain words

Conversion is allowed only when unit_status is confirmed. Inferred units are unconfirmed until a person who knows the source confirms them in a recorded decision. Never convert and then discard the raw value.

Decision branches

Unit declared on the record and matches a known unit in your list: confirmed, convert.
Unit declared once for the whole source in documentation or a contract the team has seen: confirmed for that source, convert, and record the origin as declared_per_source.
Unit text is present but not in your list, or is ambiguous (for example an abbreviation with two meanings): unknown, block, send to review.
Unit absent: unknown, block. Do not borrow the unit of the previous row or of a neighbouring column.
Unit differs between rows of one source, with no explanation: block the affected rows and flag the source.

Worked hypothetical example

A hypothetical supplier file has 6 rows with a width field.

Rows 1 and 2 say 30 cm and 45 cm: branch 1, convert.
Row 3 says 20 in: branch 1, convert.
Row 4 says 50 with no unit: branch 4, blocked.
Row 5 says 12 with the text 'ft?': branch 3, blocked.
Row 6 says 60 mm, although rows 1 and 2 use cm: this is a declared unit, so branch 1 applies and it converts. The mix of cm and mm across rows is a prompt to check, but each row has its own declaration.

Result: 4 converted, 2 blocked, 6 total. The table shows 4 allowed and 2 blocked, and the counts add up. If someone later confirms that the whole supplier file is in cm, rows 4 and 5 move to pending_review. Row 5 still needs a decision, because its text contradicts that statement. Rows are unblocked only by a recorded confirmation.

Stop conditions

Stop the import step that needs converted values if any required row is blocked, or proceed with an explicit partial result that lists the blocked rows.
Stop and escalate if more than a threshold you choose in advance is blocked, since this suggests the source format changed.
Never convert in order to fill an empty required field.

Proposed acceptance tests (not performed)

None of these has been run.

A row with no unit should end in conversion_status blocked and keep its raw value unchanged.
A row with an unrecognised unit string should be blocked, not matched to the closest known unit.
A row with a declared unit should convert, and the table should show unit_origin.
Counts of allowed, blocked and pending_review should equal the row count.
Reviewing a blocked row should require a stored reason and a person's decision. Check that the decision is visible later.
Reimporting the same file should not silently unblock rows.

The purpose of the design is not to avoid every error. It makes unknown units visible and keeps a guess from passing as data.