Repository: TobikoData/sqlmesh
Pull request #4317 (2025-05-06 11:32:16)

Feat: add suport for `@EACH` dynamic blueprints, improve docs


Patch:
diff --git a/docs/concepts/models/python_models.md b/docs/concepts/models/python_models.md
index c735dfa1..1e6e2e2b 100644
--- a/docs/concepts/models/python_models.md
+++ b/docs/concepts/models/python_models.md
@@ -367,9 +367,32 @@ def entrypoint(
     )
 ```
 
-!!! note
+Blueprint variable mappings can also be constructed dynamically, e.g., by using a macro: `blueprints="@gen_blueprints()"`. This is useful in cases where the `blueprints` list needs to be sourced from external sources, such as CSV files.
+
+For example, the definition of the `gen_blueprints` may look like this:
+
+```python linenums="1"
+from sqlmesh import macro
+
+@macro()
+def gen_blueprints(evaluator):
+    return (
+        "((customer := customer1, field_a := x, field_b := y),"
+        " (customer := customer2, field_a := z, field_b := w))"
+    )
+```
 
-    Blueprint variable mappings can also be evaluated dynamically, by using a macro (i.e. `blueprints="@gen_blueprints()"`). This is useful in cases where the `blueprints` list needs to be sourced from external sources, e.g. CSV files.
+It's also possible to use the `@EACH` macro, combined with a global list variable (`@values`):
+
+```python linenums="1"
+
+@model(
+    "@{customer}.some_table",
+    blueprints="@EACH(@values, x -> (customer := schema_@x))",
+    ...
+)
+...
+```
 
 ## Examples
 ### Basic
diff --git a/docs/concepts/models/sql_models.md b/docs/concepts/models/sql_models.md
index 5be04ecc..c57a70bd 100644
--- a/docs/concepts/models/sql_models.md
+++ b/docs/concepts/models/sql_models.md
@@ -175,9 +175,33 @@ SELECT
 FROM customer2.some_source
 ```
 
-!!! note
+Blueprint variable mappings can also be constructed dynamically, e.g., by using a macro: `blueprints @gen_blueprints()`. This is useful in cases where the `blueprints` list needs to be sourced from external sources, such as CSV files.
+
+For example, the definition of the `gen_blueprints` may look like this:
+
+```python linenums="1"
+from sqlmesh import macro
+
+@macro()
+def gen_blueprints(evaluator):
+    return (
+        "((customer := customer1, field_a := x, field_b := y),"
+        " (customer := customer2, field_a := z, field_b := w))"
+    )
+```
+
+It's also possible to use the `@EACH` macro, combined with a global list variable (`@values`):
 
-    Blueprint variable mappings can also be evaluated dynamically, by using a macro (i.e. `blueprints @gen_blueprints()`). This is useful in cases where the `blueprints` list needs to be sourced from external sources, e.g. CSV files.
+```sql linenums="1"
+MODEL (
+  name @customer.some_table,
+  kind FULL,
+  blueprints @EACH(@values, x -> (customer := schema_@x)),
+);
+
+SELECT
+  1 AS c
+```
 
 ## Python-based definition
 
@@ -262,9 +286,33 @@ def entrypoint(evaluator: MacroEvaluator) -> str | exp.Expression:
 
 The two models produced from this template are the same as in the [example](#SQL-model-blueprinting) for SQL-based blueprinting.
 
-!!! note
+Blueprint variable mappings can also be constructed dynamically, e.g., by using a macro: `blueprints="@gen_blueprints()"`. This is useful in cases where the `blueprints` list needs to be sourced from external sources, such as CSV files.
+
+For example, the definition of the `gen_blueprints` may look like this:
+
+```python linenums="1"
+from sqlmesh import macro
+
+@macro()
+def gen_blueprints(evaluator):
+    return (
+        "((customer := customer1, field_a := x, field_b := y),"
+        " (customer := customer2, field_a := z, field_b := w))"
+    )
+```
 
-    Blueprint variable mappings can also be evaluated dynamically, by using a macro (i.e. `blueprints="@gen_blueprints()"`). This is useful in cases where the `blueprints` list needs to be sourced from external sources, e.g. CSV files.
+It's also possible to use the `@EACH` macro, combined with a global list variable (`@values`):
+
+```python linenums="1"
+
+@model(
+    "@{customer}.some_table",
+    is_sql=True,
+    blueprints="@EACH(@values, x -> (customer := schema_@x))",
+    ...
+)
+...
+```
 
 ## Automatic dependencies
 
diff --git a/sqlmesh/core/model/decorator.py b/sqlmesh/core/model/decorator.py
index 952b8276..0151e9ec 100644
--- a/sqlmesh/core/model/decorator.py
+++ b/sqlmesh/core/model/decorator.py
@@ -117,6 +117,9 @@ class model(registry_decorator):
             if not blueprints:
                 raise_config_error("Failed to render blueprints property", path)
 
+            if len(blueprints) > 1:
+                blueprints = [exp.Tuple(expressions=blueprints)]
+
             blueprints = blueprints[0]
 
         return create_models_from_blueprints(
diff --git a/sqlmesh/core/model/definition.py b/sqlmesh/core/model/definition.py
index ff46232d..3dd73429 100644
--- a/sqlmesh/core/model/definition.py
+++ b/sqlmesh/core/model/definition.py
@@ -1966,7 +1966,13 @@ def load_sql_based_models(
         if not rendered_blueprints:
             raise_config_error("Failed to render blueprints property", path)
 
-        blueprints = t.cast(t.List, rendered_blueprints)[0]
+        # Help mypy see that rendered_blueprints can't be None
+        assert rendered_blueprints
+
+        if len(rendered_blueprints) > 1:
+            rendered_blueprints = [exp.Tuple(expressions=rendered_blueprints)]
+
+        blueprints = rendered_blueprints[0]
 
     return create_models_from_blueprints(
         gateway=gateway,


Test patch:
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index 3d9f3a81..3686b354 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -8493,10 +8493,10 @@ def entrypoint(evaluator):
     )
 
 
-def test_dynamic_blueprinting(tmp_path: Path) -> None:
+def test_dynamic_blueprinting_using_custom_macro(tmp_path: Path) -> None:
     init_example_project(tmp_path, dialect="duckdb", template=ProjectTemplate.EMPTY)
 
-    dynamic_template_sql = tmp_path / "models/dynamic_template.sql"
+    dynamic_template_sql = tmp_path / "models/dynamic_template_custom_macro.sql"
     dynamic_template_sql.parent.mkdir(parents=True, exist_ok=True)
     dynamic_template_sql.write_text(
         """
@@ -8514,7 +8514,7 @@ def test_dynamic_blueprinting(tmp_path: Path) -> None:
         """
     )
 
-    dynamic_template_py = tmp_path / "models/dynamic_template.py"
+    dynamic_template_py = tmp_path / "models/dynamic_template_custom_macro.py"
     dynamic_template_py.parent.mkdir(parents=True, exist_ok=True)
     dynamic_template_py.write_text(
         """
@@ -8556,6 +8556,53 @@ def gen_blueprints(evaluator):
     assert '"memory"."customer2"."some_other_table"' in ctx.models
 
 
+def test_dynamic_blueprinting_using_each(tmp_path: Path) -> None:
+    init_example_project(tmp_path, dialect="duckdb", template=ProjectTemplate.EMPTY)
+
+    dynamic_template_sql = tmp_path / "models/dynamic_template_each.sql"
+    dynamic_template_sql.parent.mkdir(parents=True, exist_ok=True)
+    dynamic_template_sql.write_text(
+        """
+        MODEL (
+          name @customer.some_table,
+          kind FULL,
+          blueprints @EACH(@values, x -> (customer := schema_@x)),
+        );
+
+        SELECT
+          1 AS c
+        """
+    )
+
+    dynamic_template_py = tmp_path / "models/dynamic_template_each.py"
+    dynamic_template_py.parent.mkdir(parents=True, exist_ok=True)
+    dynamic_template_py.write_text(
+        """
+from sqlmesh import model
+
+@model(
+    "@{customer}.some_other_table",
+    kind="FULL",
+    blueprints="@EACH(@values, x -> (customer := schema_@x))",
+    is_sql=True,
+)
+def entrypoint(evaluator):
+    return "SELECT 1 AS c"
+"""
+    )
+
+    model_defaults = ModelDefaultsConfig(dialect="duckdb")
+    variables = {"values": ["customer1", "customer2"]}
+    config = Config(model_defaults=model_defaults, variables=variables)
+    ctx = Context(config=config, paths=tmp_path)
+
+    assert len(ctx.models) == 4
+    assert '"memory"."schema_customer1"."some_table"' in ctx.models
+    assert '"memory"."schema_customer2"."some_table"' in ctx.models
+    assert '"memory"."schema_customer1"."some_other_table"' in ctx.models
+    assert '"memory"."schema_customer2"."some_other_table"' in ctx.models
+
+
 def test_single_blueprint(tmp_path: Path) -> None:
     init_example_project(tmp_path, dialect="duckdb", template=ProjectTemplate.EMPTY)