Docs · Concepts

Normalise

The eleven transforms that make two systems read the same way, where each one applies, and why dates are handled so carefully.

Two systems rarely spell things the same way. One returns S014, the other 014. One dates a sale when it rang, the other when it settled. One keeps amounts in cents.

Normalising fixes that before anything is compared. It changes how a value is read. The system on the other end is never written to.

#Two places, one vocabulary

On a data source, the Normalise tab fixes how that source's columns are read, for every run and every aggregation built on it. Set it here when the source is simply wrong about something — a column that is always padded, a clock that is always an hour out.

On an aggregation input, the Normalise tab makes one source read like the others on the way in, for this comparison only.

Prefer the data source when the fix is a property of the system. Prefer the aggregation when it is a property of the comparison.

#The transforms

Label	What it changes
As it is	Nothing
Trim spaces	Leading and trailing whitespace, strings only
UPPERCASE	Strings only
lowercase	Strings only
Digits only	Removes everything that is not a digit
Drop leading zeroes	`000123` → `123`; `000` → `0`, one zero kept
Pad with leading zeroes to	`42` → `000042` at the default 6; a longer code is left as it is
Drop first characters	`IT-1710102` with 3 → `1710102`. Default 1
Drop last characters	`10500110` with 1 → `1050011`, for a check digit on the end. Default 1
Keep first characters	`ST014-T3` with 5 → `ST014`. Default 4
Keep last characters	`REG-0042` with 4 → `0042`. Default 4
Replace text	Every occurrence of Find becomes Replace with; leave Replace with empty to remove it. `ST-0042` → `0042`
Shift by hours (timezone)	Adds hours; fractional allowed; negative to go back
Date only, drop the time	A timestamp becomes `YYYY-MM-DD`
Round to decimals	Default 2 places
Multiply by	Default 1. Use `0.01` for cents to units, or `-1` to flip a sign
Absolute value	Magnitude only

Shift by hours, Round to decimals, Multiply by, the four drop and keep rules and Pad with leading zeroes take an amount. Replace text takes two pieces of text instead.

The drop, keep and pad rules work on text and on whole numbers, so a store id that one system sends as 10500110 and another as "10500110" is cut the same way. They count from the code itself, ignoring the spaces a fixed-width column pads it with. An amount with decimals, or a date, is left alone.

Replace text matches the text exactly as you type it. It is not a regular expression: a pattern runs on every row of every scheduled run, and one that never finishes would stop every run on the machine. The drop, keep and replace rules together cover the codes reconciliations meet.

#A value it cannot act on is left alone

A text transform meeting a number returns the number untouched, rather than turning it into a blank. This matters: a mis-pointed mapping shows itself as obviously wrong data rather than as a column of nulls that looks almost plausible.

#Dates, and why they are fussy

Mosaic refuses to read anything as a date unless it is shaped like one, with a four-digit year. The reason is concrete: ST-0042 is a store code, and a permissive date parser reads it as the first of January 2042. A bare number is rejected for the same reason.

Date only reads the day off the text rather than computing it, so an unzoned 2026-09-07T00:30:00 does not quietly become the 6th for a reader in Paris. A zoned timestamp is read in UTC.

One ambiguity is left standing rather than guessed at: 09/07/2026 could be July or September depending on where it was written.

To move a timestamp between zones on purpose, apply Shift by hours first and Date only second. The order is the whole point — shifting after truncating does nothing useful.

#Where each layer applies

Three layers, always in this order:

Source normalise — after the driver, after the collection fan-out, before anything reads the rows.
Source row filters — after the transforms, because a filter is written against the value you can see in the grid, and the grid shows the normalised value.
Aggregation transforms, then the key's own rule — inside the comparison.

That last pair is load-bearing. The input's transforms make this source's value mean what the others mean. The key's rule then says how the comparison reads whatever came out of that.

All of it happens before grouping. Rows are grouped on the normalised key.

#Key rules apply to every side

A key's rule is set once and applied to every input, so it cannot be set on one side and forgotten on the other — which would match worse than not setting it at all.

The panel is How each key is read, and a rule is a chain applied in order, because the real ones chain: trim, then uppercase, then digits only. ABC-123 and abc123 become one article.

When a rule is inherited from the data source, the builder says so and tells you where to change it: "POS reads its "store" column as: trim spaces, uppercase. Set on the data source, so every check on it reads the same. Change it there."