Feature request: support user-defined macros written as SQL

[plaflamme]

Currently, user-defined macros need to be defined as python. An alternative SQL-based syntax would be more consistent with MODEL and AUDIT. That is, having a MACRO function to define user-defines macros in .sql files:

MACRO(
  name forall,
  defaults (
    condition NULL
  )
);
SELECT *
FROM @this_model
WHERE
  @AND(
    @REDUCE(@criteria, (l,r) -> l AND r),
    @condition,
  )

For supporting named arguments, perhaps a more general arguments could work, i.e.: you'd have to define each argument and its default value (and maybe its type for coercion)?:

MACRO(
  name accepted_range,
  arguments (
    min_v NULL,
    max_v NULL,
    inclusive TRUE,
    condition NULL,
  )
);
...

[tobymao]

i believe you can do this already

https://sqlmesh.readthedocs.io/en/stable/concepts/macros/sqlmesh_macros/#macro-functions

[tobymao]

closing for now but happy to discuss alternatives

[plaflamme]

But those cannot be reused across models. This feature-request is about having something like this:

macros/
  `- hash_to_string.sql

With the following content

MACRO(
  name hash_to_string,
  arguments (columns),
)
TO_BASE64(
  MD5(
    @EACH(@columns, c -> c),
    "|",
    "",
  )
)

And then in any model, do something like

SELECT @HASH_TO_STRING([a,b,c]) AS my_hash

I realize now that my original example wasn't great since it referred to writing audits, but the ask is for writing reusable macros using SQL.

[treysp]

We do currently support reusable SQL macros written with jinja

https://sqlmesh.readthedocs.io/en/stable/concepts/macros/jinja_macros/#user-defined-macro-functions

[plaflamme]

Which, presumably, is an argument for having an equivalent in SQL directly. As a SQLMesh user that doesn't have to deal with dbt or jinja, it would be a more consistent experience to be able to write these as SQL. Writing them in python is okay, but less accessible to non-developer types of users (e.g.: "data analysts" or similar SQL-mostly roles).