example sqlmesh macro

example.sql

MODEL (
  name tcloud_demo.example,
  cron '0 13 * * *',
  grain id,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column created_at
  )
);

@DEF(nested_props, [
  'cloud_tenant_id',
  'asdf',
  'xyz'
]);

WITH events AS (

SELECT 
    *
    FROM asdf.asdf.asdf
    WHERE timestamp BETWEEN @start_date AND @end_date
),

with events_left_joined AS (
    @left_join_unnested("select * from events", properties, @nested_props)
)


SELECT * FROM events_left_joined

left_join_unnested.py

from sqlmesh import macro
from sqlglot import exp
from typing import List

# We build it this way because we have to return a valid SQL expression. SQLMesh macros do NOT work like dbt macros where it substitutes strings. SQLMesh requires valid SQL to ensure correctness each time it renders the query or else it will waste time and money erroring out at the time of execution. If you build a macro that returns a LEFT JOIN by itself, SQLMesh will think you're trying to join a table to nothing and you missed including the select statement beforehand.
@macro()
def left_join_unnested(evaluator, select_query: exp.Select, column: exp.Column, nested_props: List[str]) -> str:
    # error handling for params
    if not isinstance(select_query, exp.Select):
        raise ValueError("Select query must be a valid SQL string.")
    if not isinstance(column, exp.Column):
        raise ValueError("Column must be a column object.")
    if not all(isinstance(field, str) for field in nested_props):
        raise ValueError("Nested props must be a list of strings.")
    
    # get the string representation of the select query
    sql = select_query.sql()
    
    # Append each LEFT JOIN
    for x in nested_props:
        sql += f" LEFT JOIN UNNEST({column}) as {x}_prop ON {x}_prop.key = '{x}'"
    
    return sql