Repository: PrefectHQ/prefect
Pull request: Subclassing to set the max_range per sandox environment (#18083)
closes #18083 

When prefect is imported, it sets a jinja.sandbox.MAX_RANGE globally. This affects all other processes that might use jinja, including dbt.  

Therefore, the change subclasses the environment to a new class so that the range can be set for just that class and thus dbt (and other jinja environments) can continue to use the default (100000).

### Checklist
<!-- These boxes may be checked after opening the pull request. -->

- [ ] If this pull request adds new functionality, it includes unit tests that cover the changes



Problem statement:
prefect-dbt with dbt-snowlfake>=1.9.4 errors out
### Bug summary

I tried upgrading my dependencies which moved `dbt-snowflake` from 1.9.2 to 1.9.4

However my `prefect-dbt==0.5.5` flows began to fail with:
```
in exception_handler
    raise DbtRuntimeError(str(e)) from e
dbt_common.exceptions.base.DbtRuntimeError: Runtime Error
  Range too big. The sandbox blocks ranges larger than MAX_RANGE (100).
```

However, outside of Prefect, with the same version of dbt-snowflake, dbt itself works fine.

downgrading `dbt-snowflake` to 1.9.2 fixed the issue. 

I also tried `prefect-dbt[snowflake]` but this will install 1.9.4. and get the same error. 



### Version info

```Text
Version:             2.20.18
API version:         0.8.4
Python version:      3.12.6
Git commit:          b7059e89
Built:               Wed, Apr 30, 2025 2:37 PM
OS/Arch:             darwin/arm64
Profile:             dev
Server type:         cloud
```

### Additional context

I am using `trigger_dbt_cli_command` to invoke dbt.

Hints from issue discussion:
Here's an MRE. 

```python

# /// script
# requires-python = ">=3.11"
# dependencies = [
#   "prefect==2.20.18",
#   "prefect-dbt",
#   "dbt-snowflake==1.9.4",
# ]
# ///

from pathlib import Path
import tempfile
import textwrap

from prefect import flow
from prefect_dbt.cli.commands import trigger_dbt_cli_command


@flow
def reproduce_range_error():
    with tempfile.TemporaryDirectory() as tmpdir:
        tmp = Path(tmpdir)

        # Minimal dbt project configuration
        (tmp / "dbt_project.yml").write_text(
            textwrap.dedent(
                """
                name: example
                version: 1.0
                profile: my-snowflake-db
                """
            )
        )

        # Dummy Snowflake profile (parse does not require a real connection)
        (tmp / "profiles.yml").write_text(
            textwrap.dedent(
                """
                my-snowflake-db:
                  target: dev
                  outputs:
                    dev:
                      type: snowflake
                      account: fake
                      user: fake
                      password: fake
                      database: fake
                      warehouse: fake
                      schema: public
                """
            )
        )

        # Macro using a range larger than Prefect's sandbox limit
        macros = tmp / "macros"
        macros.mkdir()
        macros.joinpath("large_range.sql").write_text(
            textwrap.dedent(
                """
                {% macro large_range() %}
                {% for i in range(101) %}
                {{ i }}
                {% endfor %}
                {% endmacro %}
                """
            )
        )

        # Model invoking the macro
        models = tmp / "models"
        models.mkdir()
        models.joinpath("model.sql").write_text("select '{{ large_range() }}' as numbers")

        # Running dbt through Prefect triggers the sandbox limit
        trigger_dbt_cli_command(
            command="dbt parse",
            project_dir=str(tmp),
            profiles_dir=str(tmp),
        )


if __name__ == "__main__":
    reproduce_range_error()
```
The error originates from Prefect’s template sandbox configuration. When Prefect is imported, it sets a global Jinja2 limit that reduces the allowed range size for Jinja’s range() function. This happens in src/prefect/server/utilities/user_templates.py:

```python
logger: "logging.Logger" = get_logger(__name__)
jinja2.sandbox.MAX_RANGE = 100
MAX_LOOP_COUNT = 10
MAX_NESTED_LOOP_DEPTH = 2
```

Our tests confirm that exceeding this limit triggers an error:

```python
assert rendered[0] == (
    "Failed to render template due to the following error: "
    "OverflowError("
    "'Range too big. The sandbox blocks ranges larger than MAX_RANGE (100).'"
    ")\n"
```

Because trigger_dbt_cli_command runs dbt within the Prefect process, the lowered MAX_RANGE setting also applies to dbt’s Jinja environment. dbt‑snowflake 1.9.4 introduces macros that call range() with more than 100 items, so they fail under Prefect with “Range too big. The sandbox blocks ranges larger than MAX_RANGE (100).” Running dbt outside Prefect does not hit this limit since Jinja’s default MAX_RANGE is much higher (100000).

I don't know how important it is for us to have such a low MAX_RANGE, @zzstoatzz do you know off hand? Can we bump this up willy nilly?
i'm not familiar with a reason this is set so low, i'd assume we can bump it
seems like a small change and would be happy to contribute a PR for it. Shall I just remove it and accept the dbt default? 

Do I need to do anything to make it compat with 2.x lineage etc.
hey @aaazzam @zzstoatzz  taken a stab at it, where can keep it low for the user_templates but other environments should be unaffected. Although the PR tests are running into a deadlock situation so not sure how to resolve that

Commit message: Merge branch 'main' into bump-jinja-range

Patch to review:
diff --git a/src/prefect/server/utilities/user_templates.py b/src/prefect/server/utilities/user_templates.py
index 320459b03d62..ff70b4788b62 100644
--- a/src/prefect/server/utilities/user_templates.py
+++ b/src/prefect/server/utilities/user_templates.py
@@ -2,7 +2,6 @@
 
 from typing import TYPE_CHECKING, Any, Optional
 
-import jinja2.sandbox
 from jinja2 import ChainableUndefined, nodes
 from jinja2.sandbox import ImmutableSandboxedEnvironment
 
@@ -14,12 +13,30 @@
 logger: "logging.Logger" = get_logger(__name__)
 
 
-jinja2.sandbox.MAX_RANGE = 100
+MAX_TEMPLATE_RANGE = 100
 MAX_LOOP_COUNT = 10
 MAX_NESTED_LOOP_DEPTH = 2
 
 
-_template_environment = ImmutableSandboxedEnvironment(
+def _check_template_range(*args: int) -> range:
+    rng = range(*args)
+
+    if len(rng) > MAX_TEMPLATE_RANGE:
+        raise OverflowError(
+            "Range too big. The sandbox blocks ranges larger than"
+            f" MAX_RANGE ({MAX_TEMPLATE_RANGE})."
+        )
+    return rng
+
+
+class UserTemplateEnvironment(ImmutableSandboxedEnvironment):
+    def __init__(self, *args: Any, **kwargs: Any) -> None:
+        super().__init__(*args, **kwargs)
+        # Override the range function to limit its size
+        self.globals["range"] = _check_template_range  # type: ignore
+
+
+_template_environment = UserTemplateEnvironment(
     undefined=ChainableUndefined,
     enable_async=True,
     extensions=[
@@ -29,7 +46,7 @@
     ],
 )
 
-_sync_template_environment = ImmutableSandboxedEnvironment(
+_sync_template_environment = UserTemplateEnvironment(
     undefined=ChainableUndefined,
     enable_async=False,
     extensions=[


Code review comments:
diff_hunk: @@ -14,12 +13,30 @@
 logger: "logging.Logger" = get_logger(__name__)
 
 
-jinja2.sandbox.MAX_RANGE = 100
+MAX_TEMPLATE_RANGE = 100
 MAX_LOOP_COUNT = 10
 MAX_NESTED_LOOP_DEPTH = 2
 
 
-_template_environment = ImmutableSandboxedEnvironment(
+def _check_template_range(*args: int) -> range:
+    rng = range(*args)
+
+    if len(rng) > MAX_TEMPLATE_RANGE:
+        raise OverflowError(
+            "Range too big. The sandbox blocks ranges larger than"
+            f" MAX_RANGE ({MAX_TEMPLATE_RANGE})."
path: src/prefect/server/utilities/user_templates.py
text: @user1:
```suggestion
            f" {MAX_TEMPLATE_RANGE=}."
```