Feature Request: Improve Audit Support for View Models in SQLMesh

[nickmuoh]

Description

We've encountered limitations when trying to implement audits that work consistently across both incremental models and view models in SQLMesh. Specifically, temporal macros like @start_date and @end_date are only available for incremental models as per the documentation, which makes it challenging to write unified audit logic that works across different model types.

Current Behavior

As documented in the SQLMesh documentation:

Other predefined variables are temporal, like start_ds and execution_date. They are used to build incremental model queries and are only available in incremental model kinds.

When using these temporal macros in audits against view models, they remain unresolved, causing conditional logic that depends on them to fail silently or behave unexpectedly.

Example Use Case

We have an audit that checks for required apps in our data models, with date-based conditions to exclude certain checks based on temporal ranges:

AUDIT(
  name assert_all_required_apps_present,
  dialect snowflake,
);

WITH required_apps AS (
  -- App list definitions
),
app_restrictions AS (
  SELECT
    'su-marketing' as app_name,
    '2024-04-20' as start_date,
    '2024-09-13' as end_date
),
existing_apps AS (
  SELECT DISTINCT app_name
  FROM @this_model
)
SELECT
  r.app_name as missing_app_name
FROM required_apps r
LEFT JOIN existing_apps e ON r.app_name = e.app_name
LEFT JOIN app_restrictions ar ON r.app_name = ar.app_name
WHERE e.app_name IS NULL
  AND (
    ar.app_name IS NULL
    OR NOT (
      @start_date::date > ar.end_date::date
      OR @end_date::date < ar.start_date::date
    )
  );

This works for incremental models but fails for view models because the temporal macros aren't available.

Feature Request

We request one of the following improvements to make audits more consistent across model types:

1. Provide temporal context for view models: Extend temporal macro support to view models, potentially deriving values from their upstream dependencies or execution context.

2. Add model type detection capabilities: Provide macros or functions in the audit context that allow checking the model type (e.g., @is_view_model, @is_incremental_model), enabling conditional logic in audits.

3. Audit-specific temporal parameters: Allow setting default temporal values for audits when run against view models.

Workaround Options

Currently, we must implement workarounds such as:

• Creating separate audit files for different model types
• Using current date functions instead of macros
• Implementing application-level date parameters

These workarounds add complexity and reduce the consistency of our data quality checks.

Value Proposition

Improving audit support across model types would:

• Reduce duplication of audit logic
• Make audits more maintainable
• Provide consistent data quality assurance across all model types
• Simplify the process of writing robust audits

Environment

• SQLMesh version: 0.224.0
• Python version: 3.12.10
• OS: Windows

Impact

This enhancement would benefit teams using SQLMesh with mixed model types who need consistent data quality checks across their entire data warehouse.