Repository: TobikoData/sqlmesh
Pull request #2990 (2024-08-08 00:23:21)

Feat: implement builtin date spine macro
The goal of this PR is to bring a lot of UX familiarity from dbt-utils into SQLMesh native macros similar to the deduplicate macro I just contributed. 

dbt-utils version of this macro: https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/date_spine.sql

This renders to SQL to generate a date spine table. It's typically used to join in unique, hard-coded, date ranges to join with other tables/views so people don't have to constantly adjust date ranges in `where` clauses everywhere.
- From @afzaljasani : "When I worked with a lot of e-commerce companies they would have daily aggregate metrics from different sources like Facebook ads, Google ads, Shopify, etc. because most of these sources can’t be joined to each other they would join to a date spine table to get a 360 view of the business"
- Do analysis specific to this range of promotional dates
- I talked with someone at Modern Animal who said they don't like how dbt does it natively, so they built their own date_spine macro in house. 

**Design Highlights**
- Large upstream changes in SQLGlot had to happen due to `generate_date_array` transpiling and behaving very differently across query engines. Huge thanks to @georgesittas to making this PR way easier for me. He's a real one. 
- Verified it works with live query manual testing so far: snowflake, postgres, duckdb, databricks, spark, redshift, bigquery. I intentionally test for these as they have the most common usage.
- Verified it works with a development version of SQLGlot: `25.11.2.dev1`

**Differences from dbt-utils' version**
- The spine will include the `start_date` (if it is aligned to the datepart), AND it will include the `end_date`
- You can input macros AND plain string dates instead of casting them: `@date_spine('quarter', '2022-01-01', '2024-12-16')`
- Instead of using cross joins to arbitrarily generate date intervals, this relies on native `generate_series`-like sql functions to be faster and cheaper for the majority of query engines. If those functions don't exist for a particular engine (ex: redshift), recursion will be used. 

Other examples of ad hoc queries working across engines:
BigQuery
![image](https://github.com/user-attachments/assets/c0d3d21f-c417-4bf1-829f-d10188d3ace0)

Redshift
![image](https://github.com/user-attachments/assets/d9b6ce4e-088e-4c56-b508-ecac0ad406d1)

Snowflake
![image](https://github.com/user-attachments/assets/7059023a-7705-404d-bdb0-c70bb66dccdf)



CLAassistant: [![CLA assistant check](https://cla-assistant.io/pull/badge/not_signed)](https://cla-assistant.io/TobikoData/sqlmesh?pullRequest=2990) <br/>Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you all sign our [Contributor License Agreement](https://cla-assistant.io/TobikoData/sqlmesh?pullRequest=2990) before we can accept your contribution.<br/>**1** out of **2** committers have signed the CLA.<br/><br/>:white_check_mark: sungchun12<br/>:x: Sung Won Chung<br/><hr/>**Sung Won Chung** seems not to be a GitHub user. You need a GitHub account to be able to sign the CLA. If you have already a GitHub account, please [add the email address used for this commit to your account](https://help.github.com/articles/why-are-my-commits-linked-to-the-wrong-user/#commits-are-not-linked-to-any-user).<br/><sub>You have signed the CLA already but the status is still pending? Let us [recheck](https://cla-assistant.io/check/TobikoData/sqlmesh?pullRequest=2990) it.</sub>
sungchun12: I signed the CLA with the correct github username. Commits under my personal name is likely due to using my personal laptop for commits. For all purposes, both commit signatures are the same person.

Patch:
diff --git a/docs/concepts/macros/sqlmesh_macros.md b/docs/concepts/macros/sqlmesh_macros.md
index 29b8e168..a9f9e175 100644
--- a/docs/concepts/macros/sqlmesh_macros.md
+++ b/docs/concepts/macros/sqlmesh_macros.md
@@ -909,7 +909,7 @@ It supports the following arguments, in this order:
 
 - `relation`: The table or CTE name to deduplicate
 - `partition_by`: column names, or expressions to use to identify a window of rows out of which to select one as the deduplicated row
-- `order_by`: A list of strings representing the ORDER BY clause, optional - nulls ordering: ['<column> <asc|desc> nulls <first|last>']
+- `order_by`: A list of strings representing the ORDER BY clause, optional - you can add nulls ordering like this: ['<column_name> desc nulls last']
 
 For example, the following query:
 ```sql linenums="1"
@@ -935,6 +935,44 @@ SELECT
 FROM "raw_data" AS "raw_data"
 ```
 
+### @DATE_SPINE
+
+`@DATE_SPINE` returns the SQL required to build a date spine. The spine will include the start_date (if it is aligned to the datepart), AND it will include the end_date. This is different from the [`date_spine`](https://github.com/dbt-labs/dbt-utils?tab=readme-ov-file#date_spine-source) macro in `dbt-utils` which will NOT include the end_date. It's typically used to join in unique, hard-coded, date ranges to with other tables/views, so people don't have to constantly adjust date ranges in `where` clauses across many SQL models.
+
+It supports the following arguments, in this order:
+
+- `datepart`: The datepart to use for the date spine - day, week, month, quarter, year
+- `start_date`: The start date for the date spine in format YYYY-MM-DD
+- `end_date`: The end date for the date spine in format YYYY-MM-DD
+
+For example, the following query:
+```sql linenums="1"
+WITH discount_promotion_dates AS (
+  @date_spine('day', '2024-01-01', '2024-01-16')
+)
+
+SELECT * FROM discount_promotion_dates
+```
+
+would be rendered as:
+
+```sql linenums="1"
+WITH "discount_promotion_dates" AS (                                                                             
+  SELECT                                                                                                         
+    "_exploded"."date_day" AS "date_day"                                                                         
+  FROM UNNEST(CAST(GENERATE_SERIES(CAST('2024-01-01' AS DATE), CAST('2024-01-16' AS DATE), INTERVAL '1' DAY) AS  
+DATE[])) AS "_exploded"("date_day")                                                                              
+)                                                                                                                
+SELECT                                                                                                           
+  "discount_promotion_dates"."date_day" AS "date_day"                                                            
+FROM "discount_promotion_dates" AS "discount_promotion_dates"
+```
+
+Note: This is DuckDB SQL and other dialects will be transpiled accordingly. 
+- Recursive CTEs (common table expressions) will be used for `Redshift / MySQL / MSSQL`.
+- For `MSSQL` in particular, there's a recursion limit of approximately 100. If this becomes a problem, you can add an `OPTION (MAXRECURSION 0)` clause after the date spine macro logic to remove the limit. This applies for long date ranges.
+
+
 ### @AND
 
 `@AND` combines a sequence of operands using the `AND` operator, filtering out any NULL expressions.
diff --git a/mkdocs.yml b/mkdocs.yml
index d89d1a1a..4b3b03bb 100644
--- a/mkdocs.yml
+++ b/mkdocs.yml
@@ -117,7 +117,6 @@ theme:
     - navigation.tabs
     - navigation.sections
     - navigation.top
-    - toc.integrate
     - toc.follow
     - search.suggest
     - search.highlight
diff --git a/sqlmesh/core/macros.py b/sqlmesh/core/macros.py
index f12a277e..d4971205 100644
--- a/sqlmesh/core/macros.py
+++ b/sqlmesh/core/macros.py
@@ -10,6 +10,7 @@ from functools import reduce
 from itertools import chain
 from pathlib import Path
 from string import Template
+from datetime import datetime
 
 import sqlglot
 from jinja2 import Environment
@@ -1168,6 +1169,75 @@ def deduplicate(
     return query
 
 
+@macro()
+def date_spine(
+    evaluator: MacroEvaluator,
+    datepart: exp.Expression,
+    start_date: exp.Expression,
+    end_date: exp.Expression,
+) -> exp.Select:
+    """Returns a query that produces a date spine with the given datepart, and range of start_date and end_date. Useful for joining as a date lookup table.
+
+    Args:
+        datepart: The datepart to use for the date spine - day, week, month, quarter, year
+        start_date: The start date for the date spine in format YYYY-MM-DD
+        end_date: The end date for the date spine in format YYYY-MM-DD
+
+    Example:
+        >>> from sqlglot import parse_one
+        >>> from sqlglot.schema import MappingSchema
+        >>> from sqlmesh.core.macros import MacroEvaluator
+        >>> sql = "@date_spine('week', '2022-01-20', '2024-12-16')"
+        >>> MacroEvaluator().transform(parse_one(sql)).sql()
+        "SELECT date_week FROM UNNEST(GENERATE_DATE_ARRAY(CAST(\'2022-01-20\' AS DATE), CAST(\'2024-12-16\' AS DATE), INTERVAL \'1\' WEEK)) AS _exploded(date_week)"
+    """
+    datepart_name = datepart.name.lower()
+    start_date_name = start_date.name
+    end_date_name = end_date.name
+
+    if datepart_name not in ("day", "week", "month", "quarter", "year"):
+        raise SQLMeshError(
+            f"Invalid datepart '{datepart_name}'. Expected: 'day', 'week', 'month', 'quarter', or 'year'"
+        )
+
+    try:
+        start_date_obj = datetime.strptime(start_date_name, "%Y-%m-%d").date()
+        end_date_obj = datetime.strptime(end_date_name, "%Y-%m-%d").date()
+    except Exception as e:
+        raise SQLMeshError(
+            f"Invalid date format - start_date and end_date must be in format: YYYY-MM-DD. Error: {e}"
+        )
+
+    if start_date_obj > end_date_obj:
+        raise SQLMeshError(
+            f"Invalid date range - start_date '{start_date_name}' is after end_date '{end_date_name}'."
+        )
+
+    alias_name = f"date_{datepart_name}"
+    start_date_column = exp.cast(start_date, "DATE")
+    end_date_column = exp.cast(end_date, "DATE")
+    if datepart_name == "quarter" and evaluator.dialect in (
+        "spark",
+        "spark2",
+        "databricks",
+        "postgres",
+    ):
+        date_interval = exp.Interval(this=exp.Literal.number(3), unit=exp.var("month"))
+    else:
+        date_interval = exp.Interval(this=exp.Literal.number(1), unit=exp.var(datepart_name))
+
+    generate_date_array = exp.func(
+        "GENERATE_DATE_ARRAY",
+        start_date_column,
+        end_date_column,
+        date_interval,
+    )
+
+    exploded = exp.alias_(exp.func("unnest", generate_date_array), "_exploded", table=[alias_name])
+
+    return exp.select(alias_name).from_(exploded)
+
+
 def normalize_macro_name(name: str) -> str:
     """Prefix macro name with @ and upcase"""
     return f"@{name.upper()}"


Test patch:
diff --git a/tests/core/test_macros.py b/tests/core/test_macros.py
index 13bafb15..0c8d21dd 100644
--- a/tests/core/test_macros.py
+++ b/tests/core/test_macros.py
@@ -754,3 +754,192 @@ def test_deduplicate_error_handling(macro_evaluator):
         str(e.value.__cause__)
         == "order_by must be a list of strings, optional - nulls ordering: ['<column> <asc|desc> nulls <first|last>']"
     )
+
+
+@pytest.mark.parametrize(
+    "dialect, date_part",
+    [
+        (dialect, date_part)
+        for dialect in [
+            "duckdb",
+            "snowflake",
+            "postgres",
+            "spark",
+            "bigquery",
+            "databricks",
+            "redshift",
+        ]
+        for date_part in ["day", "week", "month", "quarter", "year"]
+    ],
+)
+def test_date_spine(assert_exp_eq, dialect, date_part):
+    date_spine_macro = f"""
+        @date_spine(
+            '{date_part}',
+            '2022-01-01',
+            '2024-12-31'
+        )
+        """
+    schema = MappingSchema({}, dialect=dialect)
+    evaluator = MacroEvaluator(schema=schema, dialect=dialect)
+
+    # Generate the expected SQL based on the dialect and date_part
+    if dialect == "duckdb":
+        if date_part == "week":
+            interval = "(7 * INTERVAL '1' DAY)"
+        elif date_part == "quarter":
+            interval = "(90 * INTERVAL '1' DAY)"
+        else:
+            interval = f"INTERVAL '1' {date_part.upper()}"
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM
+            UNNEST(
+                CAST(
+                    GENERATE_SERIES(
+                        CAST('2022-01-01' AS DATE),
+                        CAST('2024-12-31' AS DATE),
+                        {interval}
+                    ) AS DATE[]
+                )
+            ) AS _exploded(date_{date_part})
+        """
+    elif dialect == "snowflake":
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM (
+            SELECT
+                DATEADD(
+                    {date_part.upper()},
+                    CAST(date_{date_part} AS INT),
+                    CAST('2022-01-01' AS DATE)
+                ) AS date_{date_part}
+            FROM
+                TABLE(
+                    FLATTEN(
+                        INPUT => ARRAY_GENERATE_RANGE(
+                            0,
+                            (
+                                DATEDIFF(
+                                    {date_part.upper()},
+                                    CAST('2022-01-01' AS DATE),
+                                    CAST('2024-12-31' AS DATE)
+                                ) + 1 - 1
+                            ) + 1
+                        )
+                    )
+                ) AS _exploded(seq, key, path, index, date_{date_part}, this)
+        ) AS _exploded(date_{date_part})
+        """
+    elif dialect == "postgres":
+        interval = "3 MONTH" if date_part == "quarter" else f"1 {date_part.upper()}"
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM (
+            SELECT
+                CAST(value AS DATE)
+            FROM
+                GENERATE_SERIES(
+                    CAST('2022-01-01' AS DATE),
+                    CAST('2024-12-31' AS DATE),
+                    INTERVAL '{interval}'
+                ) AS value
+        ) AS _exploded(date_{date_part})
+        """
+    elif dialect == "spark":
+        interval = "3 MONTH" if date_part == "quarter" else f"1 {date_part.upper()}"
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM
+            EXPLODE(
+                SEQUENCE(
+                    CAST('2022-01-01' AS DATE),
+                    CAST('2024-12-31' AS DATE),
+                    INTERVAL {interval}
+                )
+            ) AS _exploded(date_{date_part})
+        """
+    elif dialect == "databricks":
+        interval = "3 MONTH" if date_part == "quarter" else f"1 {date_part.upper()}"
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM
+            EXPLODE(
+                SEQUENCE(
+                    CAST('2022-01-01' AS DATE),
+                    CAST('2024-12-31' AS DATE),
+                    INTERVAL {interval}
+                )
+            ) AS _exploded(date_{date_part})
+        """
+    elif dialect == "bigquery":
+        expected_sql = f"""
+        SELECT
+            date_{date_part}
+        FROM
+            UNNEST(
+                GENERATE_DATE_ARRAY(
+                    CAST('2022-01-01' AS DATE),
+                    CAST('2024-12-31' AS DATE),
+                    INTERVAL '1' {date_part}
+                )
+            ) AS date_{date_part}
+        """
+    elif dialect == "redshift":
+        expected_sql = f"""
+        WITH RECURSIVE _generated_dates(date_{date_part}) AS (
+            SELECT
+                CAST('2022-01-01' AS DATE) AS date_{date_part}
+            UNION ALL
+            SELECT
+                CAST(DATEADD({date_part}, 1, date_{date_part}) AS DATE)
+            FROM _generated_dates
+            WHERE
+                CAST(DATEADD({date_part}, 1, date_{date_part}) AS DATE) <= CAST('2024-12-31' AS DATE)
+        )
+        SELECT
+            date_{date_part}
+        FROM (
+            SELECT
+                date_{date_part}
+            FROM _generated_dates 
+        ) AS _generated_dates
+        """
+    assert_exp_eq(evaluator.transform(parse_one(date_spine_macro)), expected_sql, dialect=dialect)
+
+
+def test_date_spine_error_handling(macro_evaluator):
+    # Test error handling: invalid datepart
+    with pytest.raises(SQLMeshError) as e:
+        macro_evaluator.evaluate(parse_one("@date_spine('invalid', '2022-01-01', '2024-12-31')"))
+    assert (
+        str(e.value.__cause__)
+        == "Invalid datepart 'invalid'. Expected: 'day', 'week', 'month', 'quarter', or 'year'"
+    )
+
+    # Test error handling: invalid start_date format
+    with pytest.raises(SQLMeshError) as e:
+        macro_evaluator.evaluate(parse_one("@date_spine('day', '2022/01/01', '2024-12-31')"))
+    assert str(e.value.__cause__).startswith(
+        "Invalid date format - start_date and end_date must be in format: YYYY-MM-DD"
+    )
+
+    # Test error handling: invalid end_date format
+    with pytest.raises(SQLMeshError) as e:
+        macro_evaluator.evaluate(parse_one("@date_spine('day', '2022-01-01', '2024/12/31')"))
+    assert str(e.value.__cause__).startswith(
+        "Invalid date format - start_date and end_date must be in format: YYYY-MM-DD"
+    )
+
+    # Test error handling: start_date after end_date
+    with pytest.raises(SQLMeshError) as e:
+        macro_evaluator.evaluate(parse_one("@date_spine('day', '2024-12-31', '2022-01-01')"))
+    assert (
+        str(e.value.__cause__)
+        == "Invalid date range - start_date '2024-12-31' is after end_date '2022-01-01'."
+    )