Repository: TobikoData/sqlmesh
Pull request #2368 (2024-03-29 21:15:44)

fix: don't load models if start is ahead of end
Prior to this PR if a start was set on a model that exceeded the max that had been loaded to prod then you would get an error that start > end. Although this would likely be uncommon in practice, while testing it could happen if you created a project without a start and had a daily model and then tested out adding an hourly model. 

This change checks if the node start exceeds the set end and if so we don't return any missing intervals. This does mean that you could end up with an empty table in production but I believe this is fine.

Example:
I have daily models in production that have been running for a bit and have loaded through until 2024-01-01. It is now 5 PM the next day and I add a new hourly model that starts at noon that day (2024-01-02 12:00:00). I deploy this to prod. Prod has only been loaded through 2024-01-01 so this model is now effectively "ahead" of production. Therefore, the fact the table is empty is not a gap but rather correct since it is "ahead" of prod. You could obviously run which would then fill in the intervals for the model. 


Patch:
diff --git a/sqlmesh/core/snapshot/definition.py b/sqlmesh/core/snapshot/definition.py
index 6acd5ded..dba2ef61 100644
--- a/sqlmesh/core/snapshot/definition.py
+++ b/sqlmesh/core/snapshot/definition.py
@@ -772,7 +772,10 @@ class Snapshot(PydanticModel, SnapshotInfoMixin):
             A list of all the missing intervals as epoch timestamps.
         """
         # If the node says that it has an end, and we are wanting to load past it, then we can return no empty intervals
-        if self.node.end and to_datetime(start) > to_datetime(self.node.end):
+        # Also if a node's start is after the end of the range we are checking then we can return no empty intervals
+        if (self.node.end and to_datetime(start) > to_datetime(self.node.end)) or (
+            self.node.start and to_datetime(self.node.start) > to_datetime(end)
+        ):
             return []
         # If the amount of time being checked is less than the size of a single interval then we
         # know that there can't being missing intervals within that range and return


Test patch:
diff --git a/tests/core/test_context.py b/tests/core/test_context.py
index 6595abbd..4f931991 100644
--- a/tests/core/test_context.py
+++ b/tests/core/test_context.py
@@ -5,12 +5,14 @@ from datetime import date, timedelta
 from tempfile import TemporaryDirectory
 from unittest.mock import PropertyMock, call, patch
 
+import freezegun
 import pytest
 from pytest_mock.plugin import MockerFixture
 from sqlglot import parse_one
 from sqlglot.errors import SchemaError
 
 import sqlmesh.core.constants
+import sqlmesh.core.dialect as d
 from sqlmesh.core.config import (
     Config,
     DuckDBConnectionConfig,
@@ -24,7 +26,13 @@ from sqlmesh.core.dialect import parse, schema_
 from sqlmesh.core.environment import Environment
 from sqlmesh.core.model import load_sql_based_model
 from sqlmesh.core.plan import BuiltInPlanEvaluator, PlanBuilder
-from sqlmesh.utils.date import make_inclusive_end, now, to_date, yesterday_ds
+from sqlmesh.utils.date import (
+    make_inclusive_end,
+    now,
+    to_date,
+    to_timestamp,
+    yesterday_ds,
+)
 from sqlmesh.utils.errors import ConfigError
 from tests.utils.test_filesystem import create_temp_file
 
@@ -482,6 +490,42 @@ def test_plan_default_end(sushi_context_pre_scheduling: Context):
     assert forward_only_dev_plan.start == plan_end
 
 
+@pytest.mark.slow
+def test_plan_start_ahead_of_end(copy_to_temp_path):
+    path = copy_to_temp_path("examples/sushi")
+    with freezegun.freeze_time("2024-01-02 00:00:00"):
+        context = Context(paths=path, config="local_config")
+        context.plan("prod", no_prompts=True, auto_apply=True)
+        assert context.state_sync.max_interval_end_for_environment("prod") == to_timestamp(
+            "2024-01-02"
+        )
+        context.close()
+    with freezegun.freeze_time("2024-01-03 00:00:00"):
+        context = Context(paths=path, config="local_config")
+        expression = d.parse(
+            """
+                MODEL(
+            name sushi.hourly,
+            KIND FULL,
+            cron '@hourly',
+            start '2024-01-02 12:00:00',
+        );
+
+        SELECT 1"""
+        )
+        model = load_sql_based_model(expression, default_catalog=context.default_catalog)
+        context.upsert_model(model)
+        context.plan("prod", no_prompts=True, auto_apply=True)
+        # Since the new start is ahead of the latest end loaded for prod, the table is deployed as empty
+        # This isn't considered a gap since prod has not loaded these intervals yet
+        # As a results the max interval end is unchanged and the table is empty
+        assert context.state_sync.max_interval_end_for_environment("prod") == to_timestamp(
+            "2024-01-02"
+        )
+        assert context.engine_adapter.fetchone("SELECT COUNT(*) FROM sushi.hourly")[0] == 0
+        context.close()
+
+
 @pytest.mark.slow
 def test_schema_error_no_default(sushi_context_pre_scheduling) -> None:
     context = sushi_context_pre_scheduling
diff --git a/tests/core/test_snapshot.py b/tests/core/test_snapshot.py
index 88ae4a78..e33f9b91 100644
--- a/tests/core/test_snapshot.py
+++ b/tests/core/test_snapshot.py
@@ -1249,7 +1249,7 @@ def test_inclusive_exclusive_hourly(make_snapshot):
             owner="owner",
             dialect="",
             cron="@hourly",
-            start="1 week ago",
+            start="2023-01-29",
             query=parse_one("SELECT id, @end_ds as ds FROM name"),
         )
     )