Repository: TobikoData/sqlmesh
Pull request #2556 (2024-05-02 22:08:35)

Fix: Include catalog if available when calling USE SCHEMA in snowflake
USE SCHEMA fails when a seed is in a different db than the main project.

izeigerman: Test please

Patch:
diff --git a/docs/concepts/audits.md b/docs/concepts/audits.md
index 365e7ef3..34c3cfbb 100644
--- a/docs/concepts/audits.md
+++ b/docs/concepts/audits.md
@@ -65,8 +65,8 @@ Apply the generic audit to a model by referencing it in the `MODEL` statement:
 MODEL (
   name sushi.items,
   audits (
-    does_not_exceed_threshold(column=id, threshold=1000),
-    does_not_exceed_threshold(column=price, threshold=100)
+    does_not_exceed_threshold(column := id, threshold := 1000),
+    does_not_exceed_threshold(column := price, threshold := 100)
   )
 );
 ```
@@ -84,7 +84,7 @@ For example, if an audit `my_audit` uses a `values` parameter, invoking it will
 MODEL (
   name sushi.items,
   audits (
-    my_audit(column=a, "values"=(1,2,3))
+    my_audit(column := a, "values" := (1,2,3))
   )
 )
 ```
@@ -107,7 +107,7 @@ This example asserts that all rows have a `price` greater than 0 and a `name` va
 MODEL (
   name sushi.items,
   audits (
-    forall(criteria=(
+    forall(criteria := (
       price > 0,
       LENGTH(name) > 0
     ))
@@ -128,7 +128,7 @@ This example asserts that the model has more than 10 rows:
 MODEL (
   name sushi.orders,
   audits (
-    number_of_rows(threshold=10)
+    number_of_rows(threshold := 10)
   )
 );
 ```
@@ -142,7 +142,7 @@ This example asserts that none of the `id`, `customer_id`, or `waiter_id` column
 MODEL (
   name sushi.orders,
   audits (
-    not_null(columns=(id, customer_id, waiter_id))
+    not_null(columns := (id, customer_id, waiter_id))
   )
 );
 ```
@@ -156,7 +156,7 @@ This example asserts that the `zip` column contains at least one non-NULL value:
 MODEL (
   name sushi.customers,
   audits (
-    at_least_one(column=zip)
+    at_least_one(column := zip)
     )
 );
 ```
@@ -170,7 +170,7 @@ This example asserts that the `zip` column has no more than 80% `NULL` values:
 MODEL (
   name sushi.customers,
   audits (
-    not_null_proportion(column=zip, threshold=0.8)
+    not_null_proportion(column := zip, threshold := 0.8)
     )
 );
 ```
@@ -188,7 +188,7 @@ This example asserts that the column `customer_id` has at least two non-NULL val
 MODEL (
   name sushi.customer_revenue_by_day,
   audits (
-    not_constant(column=customer_id)
+    not_constant(column := customer_id)
     )
 );
 ```
@@ -202,7 +202,7 @@ This example asserts that the `id` and `item_id` columns have unique values:
 MODEL (
   name sushi.orders,
   audits (
-    unique_values(columns=(id, item_id))
+    unique_values(columns := (id, item_id))
   )
 );
 ```
@@ -216,7 +216,7 @@ This example asserts that the combination of `id` and `ds` columns has no duplic
 MODEL (
   name sushi.orders,
   audits (
-    unique_combination_of_columns(columns=(id, ds))
+    unique_combination_of_columns(columns := (id, ds))
   )
 );
 ```
@@ -232,7 +232,7 @@ This example asserts that column `name` has a value of 'Hamachi', 'Unagi', or 'S
 MODEL (
   name sushi.items,
   audits (
-    accepted_values(column=name, is_in=('Hamachi', 'Unagi', 'Sake'))
+    accepted_values(column := name, is_in=('Hamachi', 'Unagi', 'Sake'))
   )
 );
 ```
@@ -248,7 +248,7 @@ This example asserts that column `name` is not one of 'Hamburger' or 'French fri
 MODEL (
   name sushi.items,
   audits (
-    not_accepted_values(column=name, is_in=('Hamburger', 'French fries'))
+    not_accepted_values(column := name, is_in := ('Hamburger', 'French fries'))
   )
 );
 ```
@@ -260,7 +260,7 @@ These audits concern the distribution of values in numeric columns.
 #### sequential_values, sequential_values_non_blocking
 Ensures that each of an ordered numeric column's values contains the previous row's value plus `interval`.
 
-For example, with a column having minimum value 1 and maximum value 4 and `interval=1`, it ensures that the rows contain values `[1, 2, 3, 4]`.
+For example, with a column having minimum value 1 and maximum value 4 and `interval := 1`, it ensures that the rows contain values `[1, 2, 3, 4]`.
 
 This example asserts that column `item_id` contains sequential values that differ by `1`:
 
@@ -268,7 +268,7 @@ This example asserts that column `item_id` contains sequential values that diffe
 MODEL (
   name sushi.items,
   audits (
-    sequential_values(column=item_id, interval=1)
+    sequential_values(column := item_id, interval := 1)
   )
 );
 ```
@@ -282,18 +282,18 @@ This example asserts that all rows have a `price` greater than or equal 1 and le
 MODEL (
   name sushi.items,
   audits (
-    accepted_range(column=price, min_v=1, max_v=100)
+    accepted_range(column := price, min_v := 1, max_v := 100)
   )
 );
 ```
 
-This example specifies the `inclusive=false` argument to assert that all rows have a `price` greater than 0 and less than 100:
+This example specifies the `inclusive := false` argument to assert that all rows have a `price` greater than 0 and less than 100:
 
 ```sql linenums="1"
 MODEL (
   name sushi.items,
   audits (
-    accepted_range(column=price, min_v=0, max_v=100, inclusive=false)
+    accepted_range(column := price, min_v := 0, max_v := 100, inclusive := false)
   )
 );
 ```
@@ -306,7 +306,7 @@ This example asserts that each row's range [min_price, max_price] does not overl
 ```sql linenums="1"
 MODEL (
   audits (
-    mutually_exclusive_ranges(lower_bound_column=min_price, upper_bound_column=max_price)
+    mutually_exclusive_ranges(lower_bound_column := min_price, upper_bound_column := max_price)
   )
 );
 ```
@@ -326,7 +326,7 @@ This example asserts that no `name` is an empty string:
 MODEL (
   name sushi.items,
   audits (
-    not_empty_string(column=name)
+    not_empty_string(column := name)
   )
 );
 ```
@@ -340,7 +340,7 @@ This example asserts that all `zip` values are 5 characters long:
 MODEL (
   name sushi.customers,
   audits (
-    string_length_equal_audit(column=zip, v=5)
+    string_length_equal_audit(column := zip, v := 5)
     )
 );
 ```
@@ -354,18 +354,18 @@ This example asserts that all `name` values have 5 or more and 50 or fewer chara
 MODEL (
   name sushi.customers,
   audits (
-    string_length_between_audit(column=name, min_v=5, max_v=50)
+    string_length_between_audit(column := name, min_v := 5, max_v := 50)
     )
 );
 ```
 
-This example specifies the `inclusive=false` argument to assert that all rows have a `name` with 5 or more and 59 or fewer characters:
+This example specifies the `inclusive := false` argument to assert that all rows have a `name` with 5 or more and 59 or fewer characters:
 
 ```sql linenums="1"
 MODEL (
   name sushi.customers,
   audits (
-    string_length_between_audit(column=zip, min_v=4, max_v=60, inclusive=false)
+    string_length_between_audit(column := zip, min_v := 4, max_v := 60, inclusive := false)
     )
 );
 ```
@@ -380,7 +380,7 @@ This example asserts that all `uuid` values have the UUID structure:
 ```sql linenums="1"
 MODEL (
   audits (
-    valid_uuid(column=uuid)
+    valid_uuid(column := uuid)
     )
 );
 ```
@@ -395,7 +395,7 @@ This example asserts that all `email` values have the email address structure:
 ```sql linenums="1"
 MODEL (
   audits (
-    valid_email(column=email)
+    valid_email(column := email)
     )
 );
 ```
@@ -410,7 +410,7 @@ This example asserts that all `url` values have the URL structure:
 ```sql linenums="1"
 MODEL (
   audits (
-    valid_url(column=url)
+    valid_url(column := url)
     )
 );
 ```
@@ -425,7 +425,7 @@ This example asserts that all `http_method` values are valid HTTP methods:
 ```sql linenums="1"
 MODEL (
   audits (
-    valid_http_method(column=http_method)
+    valid_http_method(column := http_method)
     )
 );
 ```
@@ -438,7 +438,7 @@ This example asserts that all `todo` values match one of `'^\d.*'` (string start
 ```sql linenums="1"
 MODEL (
   audits (
-    match_regex_pattern_list(column=todo, patterns=('^\d.*', '.*!$'))
+    match_regex_pattern_list(column := todo, patterns := ('^\d.*', '.*!$'))
   )
 );
 ```
@@ -451,7 +451,7 @@ This example asserts that no `todo` values match one of `'^!.*'` (string starts
 ```sql linenums="1"
 MODEL (
   audits (
-    match_regex_pattern_list(column=todo, patterns=('^!.*', '.*\d$'))
+    match_regex_pattern_list(column := todo, patterns := ('^!.*', '.*\d$'))
   )
 );
 ```
@@ -464,7 +464,7 @@ This example asserts that all `name` values are `LIKE` one of `'jim%'` or `'pam%
 ```sql linenums="1"
 MODEL (
   audits (
-    match_like_pattern_list(column=name, patterns=('jim%', 'pam%'))
+    match_like_pattern_list(column := name, patterns := ('jim%', 'pam%'))
   )
 );
 ```
@@ -477,7 +477,7 @@ This example asserts that no `name` values are `LIKE` `'%doe'` or `'%smith'`:
 ```sql linenums="1"
 MODEL (
   audits (
-    not_match_like_pattern_list(column=name, patterns=('%doe', '%smith'))
+    not_match_like_pattern_list(column := name, patterns := ('%doe', '%smith'))
   )
 );
 ```
@@ -497,17 +497,17 @@ This example asserts that the `age` column has a mean of at least 21 and at most
 ```sql linenums="1"
 MODEL (
   audits (
-    mean_in_range(column=age, min_v=21, max_v=50)
+    mean_in_range(column := age, min_v := 21, max_v := 50)
     )
 );
 ```
 
-This example specifies the `inclusive=false` argument to assert that `age` has a mean greater than 18 and less than 65:
+This example specifies the `inclusive := false` argument to assert that `age` has a mean greater than 18 and less than 65:
 
 ```sql linenums="1"
 MODEL (
   audits (
-    mean_in_range(column=age, min_v=18, max_v=65, inclusive=false)
+    mean_in_range(column := age, min_v := 18, max_v := 65, inclusive := false)
     )
 );
 ```
@@ -520,17 +520,17 @@ This example asserts that the `age` column has a standard deviation of at least
 ```sql linenums="1"
 MODEL (
   audits (
-    stddev_in_range(column=age, min_v=2, max_v=5)
+    stddev_in_range(column := age, min_v := 2, max_v := 5)
     )
 );
 ```
 
-This example specifies the `inclusive=false` argument to assert that `age` has a standard deviation greater than 3 and less than 6:
+This example specifies the `inclusive := false` argument to assert that `age` has a standard deviation greater than 3 and less than 6:
 
 ```sql linenums="1"
 MODEL (
   audits (
-    mean_in_range(column=age, min_v=3, max_v=6, inclusive=false)
+    mean_in_range(column := age, min_v := 3, max_v := 6, inclusive := false)
     )
 );
 ```
@@ -545,7 +545,7 @@ This example asserts that the `age` column contains no rows with z-scores greate
 ```sql linenums="1"
 MODEL (
   audits (
-    z_score(column=age, threshold=3)
+    z_score(column := age, threshold := 3)
     )
 );
 ```
@@ -558,7 +558,7 @@ This example asserts that the symmetrised KL Divergence between columns `age` an
 ```sql linenums="1"
 MODEL (
   audits (
-    kl_divergence(column=age, target_column=reference_age, threshold=0.1)
+    kl_divergence(column := age, target_column := reference_age, threshold := 0.1)
     )
 );
 ```
@@ -571,7 +571,7 @@ You can look up the critical value corresponding to a p-value with a table (such
 ```python linenums="1"
 from scipy.stats import chi2
 
-# critical value for p-value = 0.95 and degrees of freedom = 1
+# critical value for p-value := 0.95 and degrees of freedom := 1
 chi2.ppf(0.95, 1)
 ```
 This example asserts that the chi-square statistic0 for columns `user_state` and `user_type` does not exceed 6.635:
@@ -579,7 +579,7 @@ This example asserts that the chi-square statistic0 for columns `user_state` and
 ```sql linenums="1"
 MODEL (
   audits (
-    chi_square(column=user_state, target_column=user_type, critical_value=6.635)
+    chi_square(column := user_state, target_column := user_type, critical_value := 6.635)
     )
 );
 ```
diff --git a/docs/guides/multi_repo.md b/docs/guides/multi_repo.md
index 9c095d71..19e8d2d4 100644
--- a/docs/guides/multi_repo.md
+++ b/docs/guides/multi_repo.md
@@ -163,3 +163,17 @@ The target environment has been updated successfully
 ```
 
 SQLMesh correctly detects a breaking change and allows you to perform a multi-repo backfill.
+
+## Configuring projects with multiple repositories
+
+To add support for multiple repositories, add a `project` key to the config file in each of the respective repos. 
+
+```yaml
+project: repo_1
+
+gateways:
+...
+```
+
+Even if you do not have a need for multiple repos now, consider adding a `project` key so that you can easily support multiple repos in the future.
+
diff --git a/docs/reference/configuration.md b/docs/reference/configuration.md
index c5f24fb6..7e2f561d 100644
--- a/docs/reference/configuration.md
+++ b/docs/reference/configuration.md
@@ -19,6 +19,7 @@ Configuration options for SQLMesh project directories.
 | Option            | Description                                                                                                        |     Type     | Required |
 | ----------------- | ------------------------------------------------------------------------------------------------------------------ | :----------: | :------: |
 | `ignore_patterns` | Files that match glob patterns specified in this list are ignored when scanning the project folder (Default: `[]`) | list[string] |    N     |
+| `project`         | The project name of this config. Used for [multi-repo setups](../guides/multi_repo.md).                            | string       |    N     |
 
 ### Environments
 
diff --git a/examples/sushi/models/top_waiters.sql b/examples/sushi/models/top_waiters.sql
index 639374d7..95e163df 100644
--- a/examples/sushi/models/top_waiters.sql
+++ b/examples/sushi/models/top_waiters.sql
@@ -3,7 +3,7 @@ MODEL (
   name sushi.top_waiters,
   owner jen,
   audits (
-    unique_values(columns=(waiter_id))
+    unique_values(columns := waiter_id)
   ),
   grain waiter_id
 );
diff --git a/examples/sushi/models/waiter_as_customer_by_day.sql b/examples/sushi/models/waiter_as_customer_by_day.sql
index 8dd13ce6..642512e1 100644
--- a/examples/sushi/models/waiter_as_customer_by_day.sql
+++ b/examples/sushi/models/waiter_as_customer_by_day.sql
@@ -7,8 +7,8 @@ MODEL (
   owner jen,
   cron '@daily',
   audits (
-    not_null(columns = (waiter_id)),
-    forall(criteria = (LENGTH(waiter_name) > 0))
+    not_null(columns := (waiter_id)),
+    forall(criteria := (LENGTH(waiter_name) > 0))
   )
 );
 
diff --git a/examples/sushi/models/waiter_revenue_by_day.sql b/examples/sushi/models/waiter_revenue_by_day.sql
index ac8ac50c..c01a6b20 100644
--- a/examples/sushi/models/waiter_revenue_by_day.sql
+++ b/examples/sushi/models/waiter_revenue_by_day.sql
@@ -8,7 +8,7 @@ MODEL (
   owner jen,
   cron '@daily',
   audits (
-    NUMBER_OF_ROWS(threshold=0)
+    NUMBER_OF_ROWS(threshold := 0)
   ),
   grain (waiter_id, event_date)
 );
diff --git a/sqlmesh/core/engine_adapter/snowflake.py b/sqlmesh/core/engine_adapter/snowflake.py
index f089cb0d..ae5972aa 100644
--- a/sqlmesh/core/engine_adapter/snowflake.py
+++ b/sqlmesh/core/engine_adapter/snowflake.py
@@ -90,7 +90,10 @@ class SnowflakeEngineAdapter(GetCurrentCatalogFromFunctionMixin):
                 #
                 # The above issue has already been fixed upstream, but we keep the following
                 # line anyway in order to support a wider range of Snowflake versions.
-                self.cursor.execute(f'USE SCHEMA "{temp_table.db}"')
+                schema = f'"{temp_table.db}"'
+                if temp_table.catalog:
+                    schema = f'"{temp_table.catalog}".{schema}'
+                self.cursor.execute(f"USE SCHEMA {schema}")
 
                 # See: https://stackoverflow.com/a/75627721
                 for column, kind in columns_to_types.items():
diff --git a/sqlmesh/core/macros.py b/sqlmesh/core/macros.py
index 844250ab..f8375095 100644
--- a/sqlmesh/core/macros.py
+++ b/sqlmesh/core/macros.py
@@ -60,6 +60,7 @@ class RuntimeStage(Enum):
     LOADING = "loading"
     CREATING = "creating"
     EVALUATING = "evaluating"
+    TESTING = "testing"
 
 
 class MacroStrTemplate(Template):
diff --git a/sqlmesh/core/model/definition.py b/sqlmesh/core/model/definition.py
index 8c4b21b4..e4037cca 100644
--- a/sqlmesh/core/model/definition.py
+++ b/sqlmesh/core/model/definition.py
@@ -1457,6 +1457,7 @@ def load_sql_based_model(
         python_env=meta_python_env,
         default_catalog=default_catalog,
         quote_identifiers=False,
+        normalize_identifiers=False,
     )
     rendered_meta_exprs = meta_renderer.render()
     if rendered_meta_exprs is None or len(rendered_meta_exprs) != 1:
diff --git a/sqlmesh/core/model/meta.py b/sqlmesh/core/model/meta.py
index 5d69ab16..9b9bafa3 100644
--- a/sqlmesh/core/model/meta.py
+++ b/sqlmesh/core/model/meta.py
@@ -89,11 +89,11 @@ class ModelMeta(_Node):
                 return v.name.lower(), {}
 
             for arg in args:
-                if not isinstance(arg, exp.EQ):
+                if not isinstance(arg, (exp.PropertyEQ, exp.EQ)):
                     raise ConfigError(
-                        f"Function '{func}' must be called with key-value arguments like {func}(arg=value)."
+                        f"Function '{func}' must be called with key-value arguments like {func}(arg := value)."
                     )
-                kwargs[arg.left.name] = arg.right
+                kwargs[arg.left.name.lower()] = arg.right
             return func.lower(), kwargs
 
         if isinstance(v, (exp.Tuple, exp.Array)):
diff --git a/sqlmesh/core/renderer.py b/sqlmesh/core/renderer.py
index cebc10cd..ef69613a 100644
--- a/sqlmesh/core/renderer.py
+++ b/sqlmesh/core/renderer.py
@@ -48,6 +48,7 @@ class BaseExpressionRenderer:
         default_catalog: t.Optional[str] = None,
         quote_identifiers: bool = True,
         model_fqn: t.Optional[str] = None,
+        normalize_identifiers: bool = True,
     ):
         self._expression = expression
         self._dialect = dialect
@@ -57,6 +58,7 @@ class BaseExpressionRenderer:
         self._python_env = python_env or {}
         self._only_execution_time = only_execution_time
         self._default_catalog = default_catalog
+        self._normalize_identifiers = normalize_identifiers
         self._quote_identifiers = quote_identifiers
         self.update_schema({} if schema is None else schema)
         self._cache: t.List[t.Optional[exp.Expression]] = []
@@ -288,9 +290,12 @@ class BaseExpressionRenderer:
 
     @contextmanager
     def _normalize_and_quote(self, query: E) -> t.Iterator[E]:
-        with d.normalize_and_quote(
-            query, self._dialect, self._default_catalog, quote=self._quote_identifiers
-        ) as query:
+        if self._normalize_identifiers:
+            with d.normalize_and_quote(
+                query, self._dialect, self._default_catalog, quote=self._quote_identifiers
+            ) as query:
+                yield query
+        else:
             yield query
 
     def _should_cache(self, runtime_stage: RuntimeStage, *args: t.Any) -> bool:


Test patch:
diff --git a/sqlmesh/core/test/definition.py b/sqlmesh/core/test/definition.py
index 8ec6402c..0e1bf116 100644
--- a/sqlmesh/core/test/definition.py
+++ b/sqlmesh/core/test/definition.py
@@ -19,6 +19,7 @@ from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
 from sqlmesh.core import constants as c
 from sqlmesh.core.dialect import normalize_model_name, schema_
 from sqlmesh.core.engine_adapter import EngineAdapter
+from sqlmesh.core.macros import RuntimeStage
 from sqlmesh.core.model import Model, PythonModel, SqlModel
 from sqlmesh.utils import UniqueKeyDict, random_id, type_is_known, yaml
 from sqlmesh.utils.date import pandas_timestamp_to_pydatetime
@@ -434,6 +435,7 @@ class SqlModelTest(ModelTest):
             **self.body.get("vars", {}),
             engine_adapter=self.engine_adapter,
             table_mapping=mapping,
+            runtime_stage=RuntimeStage.TESTING,
         )
 
         self.test_ctes(
@@ -614,6 +616,7 @@ def generate_test(
             **t.cast(t.Dict[str, t.Any], variables),
             engine_adapter=test_engine_adapter,
             table_mapping=mapping,
+            runtime_stage=RuntimeStage.TESTING,
         )
 
         if include_ctes:
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index 6d6b9d23..99609e22 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -2289,7 +2289,10 @@ def test_model_normalization():
         MODEL (
             name foo,
             dialect snowflake,
-            kind INCREMENTAL_BY_UNIQUE_KEY(unique_key a)
+            kind INCREMENTAL_BY_UNIQUE_KEY(unique_key a),
+              audits (
+                not_null(COLUMNS := id)
+            ),
         );
 
         SELECT x.a AS a FROM test.x AS x
@@ -2297,6 +2300,8 @@ def test_model_normalization():
     )
     model = SqlModel.parse_raw(load_sql_based_model(expr).json())
     assert model.unique_key == [exp.column("A", quoted=True)]
+    # we should never normalize the model meta, additionally, we should force lower case
+    assert model.audits[0][1] == {"columns": exp.column("id")}
 
     model = create_sql_model(
         "foo",
diff --git a/tests/core/test_test.py b/tests/core/test_test.py
index 516b7ec0..27d3f191 100644
--- a/tests/core/test_test.py
+++ b/tests/core/test_test.py
@@ -15,6 +15,7 @@ from sqlmesh.core import constants as c
 from sqlmesh.core.config import Config, DuckDBConnectionConfig, ModelDefaultsConfig
 from sqlmesh.core.context import Context
 from sqlmesh.core.dialect import parse
+from sqlmesh.core.macros import MacroEvaluator, macro
 from sqlmesh.core.model import Model, SqlModel, load_sql_based_model, model
 from sqlmesh.core.test.definition import ModelTest, PythonModelTest, SqlModelTest
 from sqlmesh.utils.errors import ConfigError
@@ -1020,6 +1021,29 @@ test_foo:
         assert table.db == f"sqlmesh_test_jzngz56a"
 
 
+def test_runtime_stage() -> None:
+    @macro()
+    def test_macro(evaluator: MacroEvaluator) -> t.List[bool]:
+        return [evaluator.runtime_stage == "testing", evaluator.runtime_stage == "loading"]
+
+    _check_successful_or_raise(
+        _create_test(
+            body=load_yaml(
+                """
+test_foo:
+  model: foo
+  outputs:
+    query:
+      - c: [true, false]
+                """
+            ),
+            test_name="test_foo",
+            model=_create_model("SELECT [@test_macro()] AS c"),
+            context=Context(config=Config(model_defaults=ModelDefaultsConfig(dialect="duckdb"))),
+        ).run()
+    )
+
+
 def test_test_generation(tmp_path: Path) -> None:
     init_example_project(tmp_path, dialect="duckdb")