Repository: TobikoData/sqlmesh
Pull request #3447 (2024-12-02 15:05:01)

Fix: use model dialect as fallback for audits
Fixes a regression [caused](https://github.com/TobikoData/sqlmesh/blob/main/sqlmesh/core/audit/definition.py#L216) by the recent [audit refactor](https://github.com/TobikoData/sqlmesh/commit/944283bd760f67d9c2c06a1c394ef18d97567ade). Additional context can be found in [this](https://tobiko-data.slack.com/archives/C044BRE5W4S/p1732365420003479) thread.

georgesittas: CI failures are unrelated and have been documented in https://github.com/TobikoData/sqlmesh/pull/3446.

Patch:
diff --git a/sqlmesh/core/macros.py b/sqlmesh/core/macros.py
index 63f0316e..b7fcdf0b 100644
--- a/sqlmesh/core/macros.py
+++ b/sqlmesh/core/macros.py
@@ -912,7 +912,7 @@ def generate_surrogate_key(
     *fields: exp.Expression,
     hash_function: exp.Literal = exp.Literal.string("MD5"),
 ) -> exp.Func:
-    """Generates a surrogate key for the given fields.
+    """Generates a surrogate key (string) for the given fields.
 
     Example:
         >>> from sqlglot import parse_one
@@ -920,7 +920,7 @@ def generate_surrogate_key(
         >>>
         >>> sql = "SELECT @GENERATE_SURROGATE_KEY(a, b, c) FROM foo"
         >>> MacroEvaluator(dialect="bigquery").transform(parse_one(sql, dialect="bigquery")).sql("bigquery")
-        "SELECT MD5(CONCAT(COALESCE(CAST(a AS STRING), '_sqlmesh_surrogate_key_null_'), '|', COALESCE(CAST(b AS STRING), '_sqlmesh_surrogate_key_null_'), '|', COALESCE(CAST(c AS STRING), '_sqlmesh_surrogate_key_null_'))) FROM foo"
+        "SELECT TO_HEX(MD5(CONCAT(COALESCE(CAST(a AS STRING), '_sqlmesh_surrogate_key_null_'), '|', COALESCE(CAST(b AS STRING), '_sqlmesh_surrogate_key_null_'), '|', COALESCE(CAST(c AS STRING), '_sqlmesh_surrogate_key_null_')))) FROM foo"
         >>>
         >>> sql = "SELECT @GENERATE_SURROGATE_KEY(a, b, c, hash_function := 'SHA256') FROM foo"
         >>> MacroEvaluator(dialect="bigquery").transform(parse_one(sql, dialect="bigquery")).sql("bigquery")
@@ -937,11 +937,16 @@ def generate_surrogate_key(
                 exp.Literal.string("_sqlmesh_surrogate_key_null_"),
             )
         )
-    return exp.func(
+
+    func = exp.func(
         hash_function.name,
         exp.func("CONCAT", *string_fields),
         dialect=evaluator.dialect,
     )
+    if isinstance(func, exp.MD5Digest):
+        func = exp.MD5(this=func.this)
+
+    return func
 
 
 @macro()
diff --git a/sqlmesh/core/model/definition.py b/sqlmesh/core/model/definition.py
index 15ff8805..ca13ccbb 100644
--- a/sqlmesh/core/model/definition.py
+++ b/sqlmesh/core/model/definition.py
@@ -464,7 +464,7 @@ class _Model(ModelMeta, frozen=True):
 
         query_renderer = QueryRenderer(
             audit.query,
-            audit.dialect,
+            audit.dialect or self.dialect,
             audit.macro_definitions,
             path=audit._path or Path(),
             jinja_macro_registry=audit.jinja_macros,


Test patch:
diff --git a/tests/core/test_audit.py b/tests/core/test_audit.py
index 4b313cff..90ee1603 100644
--- a/tests/core/test_audit.py
+++ b/tests/core/test_audit.py
@@ -12,6 +12,7 @@ from sqlmesh.core.audit import (
 )
 from sqlmesh.core.dialect import parse
 from sqlmesh.core.model import (
+    FullKind,
     IncrementalByTimeRangeKind,
     Model,
     SeedModel,
@@ -880,3 +881,20 @@ def test_model_inline_audits(sushi_context: Context):
     assert len(model.audit_definitions) == 3
     assert isinstance(model.audit_definitions["assert_valid_name"], ModelAudit)
     model.render_audit_query(model.audit_definitions["assert_positive_id"]).sql() == expected_query
+
+
+def test_audit_query_normalization():
+    model = create_sql_model(
+        "db.test_model",
+        parse_one("SELECT a, b, ds"),
+        kind=FullKind(),
+        dialect="snowflake",
+    )
+    rendered_audit_query = model.render_audit_query(
+        builtin.not_null_audit,
+        columns=[exp.to_column("a")],
+    )
+    assert (
+        rendered_audit_query.sql("snowflake")
+        == """SELECT * FROM (SELECT * FROM "DB"."TEST_MODEL" AS "TEST_MODEL") AS "_Q_0" WHERE "A" IS NULL AND TRUE"""
+    )