Macro library

Macros are reusable SQL fragments you reference from column transformations and workflow steps as @macro:name(...). DeltaVault ships a default macro library of 36 macros covering the cleansing, masking, and Data Vault patterns most teams rebuild by hand: null handling, whitespace cleanup, date sentinels, boolean standardization, PII masking and encryption, and business-key and hash-key treatment. Every organization can invoke them immediately; nothing needs to be created or imported first.

This page covers the shipped library. For the jinja helper macros used inside templates themselves, see Customizing templates.

The library at a glance

Open Toolkit > Macros. The library shows one list grouped by folder: Strings, Numbers, Dates, Booleans, PII, Data Vault, and Validation, with your own macros grouped alongside them and unfoldered macros in an Ungrouped section at the end. Shipped macros carry a Default badge and are read-only; your organization’s macros are editable as always.

One further entry sits in the Data Vault folder and is not one of the 36: tobk, carrying a Built-in badge rather than a Default one. It is built into the product rather than shipped as a library row, which is why it has no detail page to open and no row actions. It derives a business key expression from ordered columns, with an optional leading {{rs}} record source token, and it takes one argument or more, shown as 1+. The business key machinery expands it per platform, so it is never expanded by the ordinary macro pass the rest of this page describes.

All default macro bodies are Databricks (Spark) SQL. The encrypt and encrypt_date macros additionally require a Databricks secret scope holding the encryption key.

Invoking a macro

Reference a macro from any column transformation, a workflow SQL step, or a View Designer column transformation or derived expression:

@macro:trim_to_empty()

{{this}} inside the macro body is bound automatically to the host column name, so the example above expands to COALESCE(TRIM(customer_name), '') on a column named customer_name.

Parameterized macros take positional arguments, and quoted arguments keep their quotes:

@macro:null_to_default('Unknown')
@macro:round_to(4)
@macro:clamp(0, 100)
@macro:left_pad_zeros(10)
@macro:encrypt('my_scope', 'my_key')

Argument counts are validated as you type and again by the catalog lint rules, so a wrong arity is flagged before anything renders.

Composition

Macro bodies can reference other macros. The shipped hash_key demonstrates this: its body is SHA2(@macro:business_key(), 256), so business_key expands first and the result is hashed. Expansion is recursive with cycle protection.

Overriding a default

Creating an organization macro with the same name as a default overrides the default everywhere it is used, including inside other macros that reference it: override business_key and the default hash_key picks up your version automatically. On the library page, an organization macro that hides a default shows an Overrides default hint.

The fastest way to customize a default is Duplicate to my macros (on the library row or the macro’s detail page). It opens the create form prefilled with the default’s name, folder, comment, parameters, and body; save it unchanged under the same name and edit from there.

Deleting a macro

Deleting a macro soft-deletes it: it disappears from the library and can no longer be invoked, but any @macro:name(...) references already written into column transformations, workflow SQL steps, or view expressions are left exactly as written, they just stop expanding. Before you confirm, the delete dialog previews how many such references exist on this branch.

Read that count as a floor rather than a total. It covers column transformations and workflow SQL steps, and nothing on a view. It does not reach a view’s stored SELECT, a View Designer derived-column expression, or a designer WHERE clause, all three of which really are expanded when the view renders. So a macro used only inside a view can show a count of zero and still be genuinely in use. Check your views yourself before deleting a macro you expect something to be referencing.

Reference: the 36 defaults

Macro	Folder	Description
`null_to_empty()`	Strings	Replace null strings with empty string.
`null_to_unknown()`	Strings	Replace null values with default of Unknown.
`trim_to_empty()`	Strings	Trim and replace null strings with empty string.
`blank_to_null()`	Strings	Convert blank or whitespace-only strings to null (the inverse of trim_to_empty; feeds COALESCE chains).
`clean_whitespace()`	Strings	Trim and collapse runs of internal whitespace to a single space.
`proper_case()`	Strings	Trim and convert to proper case (first letter of each word capitalized), for names and titles.
`upper_trim()`	Strings	Trim and uppercase, for case-insensitive matching and code columns.
`digits_only()`	Strings	Strip everything except digits, for phone numbers, postal codes, account numbers.
`left_pad_zeros(length)`	Strings	Left-pad with zeros to a fixed width. Invoke: @macro:left_pad_zeros(10).
`null_to_zero()`	Numbers	Replace null integers with 0.
`null_to_minus_one()`	Numbers	Replace null numbers with -1.
`round_to_2_decimals()`	Numbers	Round null or decimal values to 2 decimal places.
`null_to_default(fallback)`	Numbers	Generic null replacement with a caller-supplied default. Invoke: @macro:null_to_default(‘Unknown’) or @macro:null_to_default(0).
`negative_to_zero()`	Numbers	Floor negative or null numbers at zero, for quantities and amounts that cannot go negative.
`round_to(places)`	Numbers	Round null or decimal values to a caller-supplied precision. Invoke: @macro:round_to(4).
`clamp(min, max)`	Numbers	Constrain a value to a range. Invoke: @macro:clamp(0, 100).
`percent_to_ratio()`	Numbers	Convert a percentage (0 to 100) to a ratio (0 to 1).
`null_to_start_date()`	Dates	Replace null date with 1900-01-01.
`null_to_today()`	Dates	Replace null date with current system date.
`null_to_end_date()`	Dates	Replace null date with the high date 9999-12-31 (pairs with null_to_start_date for effective-dating).
`date_key()`	Dates	Convert a date to an integer surrogate key in yyyyMMdd form (null stays null).
`end_of_month()`	Dates	Return the last day of the value’s month, for period-end snapshots.
`age_in_years()`	Dates	Whole years elapsed since the date, for age from date of birth.
`future_date_to_null()`	Dates	Plausibility rule, nulls out dates in the future (typos, sentinel values).
`null_to_false()`	Booleans	Replace null boolean with false.
`null_to_true()`	Booleans	Replace null boolean with true.
`yes_no_to_boolean()`	Booleans	Standardize free-text flag values (Y/N, Yes/No, True/False, 1/0) to a boolean; unrecognized values become null.
`boolean_to_yes_no()`	Booleans	Render a boolean as Yes/No for presentation layers.
`encrypt(scope, key)`	PII	Encrypts a value using AES-GCM with a secret-scope key and returns the Base64-encoded ciphertext as a string. Invoke: @macro:encrypt(‘my_scope’, ‘my_key’).
`encrypt_date(scope, key)`	PII	Formats a date or timestamp to a canonical string, then encrypts it with AES-GCM and returns the Base64-encoded ciphertext as a string. Invoke: @macro:encrypt_date(‘my_scope’, ‘my_key’).
`hash_sha256()`	PII	Irreversible SHA-256 hash, for pseudonymization where the raw value is never needed back.
`mask_all_but_last_4()`	PII	Mask all characters except the last four, for card and account numbers.
`mask_email()`	PII	Mask the local part of an email address, keeping the first character and the domain.
`business_key()`	Data Vault	Standard business-key treatment: cast, trim, uppercase, with null or blank collapsed to the -1 sentinel.
`hash_key()`	Data Vault	Hash key over the standardized business key. Demonstrates macro composition: business_key expands first, then the result is hashed.
`valid_email_or_null()`	Validation	Keep only syntactically valid email addresses, null out the rest.