Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Hash Trim (Project)

Applies to
Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Store · Data Vault · Dimensional · Data Mart

Hash Trim (HashTrim) removes leading and trailing spaces from every term value of a hash input before it is hashed. Use it when values that differ only in surrounding spaces must give the same business key and must not create a new version. It applies to every key hash and row hash of the project.

Values and default

  • false (default): values are hashed as they are.
  • true: values are trimmed.

Set the value in the Hashing group of the Additional Settings in Project Settings, before the first load.

Effect on generated output

Each term value is wrapped in TRIM, after the conversion to text and before Hash Casing. The Hash Null Placeholder is not trimmed. With true, Microsoft SQL Server generates for the terms CUSTOMER and CUSTOMER_SINCE:

ISNULL(TRIM([CUSTOMER]), N'_NULL_')
    + N'_' + ISNULL(TRIM(CAST([CUSTOMER_SINCE] AS NVARCHAR(4000))),
        N'_NULL_')

With true, the values abc, abc and abc give the same hash, and a change between them creates no new version. With false, they give different hashes, also on Microsoft SQL Server, where a comparison with = ignores trailing spaces.

On Oracle, a value of spaces only becomes NULL after trimming and is then replaced by the Hash Null Placeholder.

Changing the value

A new value changes every hash whose input has leading or trailing spaces. Stored hash values are not recalculated, so the next load treats those rows as changed. The consequences for loaded objects are described in Changing the hash settings.

Available since Generator 2.2. Earlier releases did not trim, so false keeps hash values unchanged after an upgrade.

Related articles