SQL macros#

Call a shared macro like any other SQL function. Supernova injects a macro definition only when the query references its name, which keeps unrelated queries unchanged.

select
  id,
  initcap(lower(property_company)) as company_name
from titan.hubspot.contacts
where not _deleted

The shared library#

Shared macros live in src/queries/macros/. The filename is the callable name. The current initcap macro splits text into words, uppercases each first character, lowercases its remainder, and joins the pieces.

Its definition uses ordinary dialect syntax:

create macro initcap(text) as
  list_reduce(
    list_transform(
      regexp_extract_all(text, '(\w+\W*)'),
      word -> upper(word[1]) || lower(word[2:])
    ),
    (left_value, right_value) -> left_value || right_value,
    ''
  );

Resolution#

Macro detection happens before the query runs. A reference to initcap( loads that definition for the session. Macro names do not become lake tables, and their definitions cannot access a different organization.

Use typed functions when callers need parameter and return contracts. A SQL macro remains useful for established untyped helpers and compatibility with DuckDB syntax.