DataMosaicDocs · 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 eleven 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 |
| 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 |
Only Shift by hours, Round to decimals and Multiply by take an amount.
#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."