Unable to use function calls in start_date and end_date

[alexberryman]

Description of the Issue

When defining end_date='dateadd(day, -1, date_trunc("day", getdate()))' to dynamically get "yeterday" on a metric, the compiled SQL (Snowflake) throws an error:

[22007][100040] Date 'dateadd(day, -1, date_trunc("day", getdate()))' is not recognized

This is due to the fact that #20 and release tag 0.1.5 is now wrapping all start_date and end_date input in single-quoted strings.

How to reproduce:

##metric_test.sql
select 
    *
from {{ metrics.metric(
    metric_name='new_opportunities',
    grain='month',
    dimensions=['lead_source'],
    start_date="2022-01-01",
    end_date='dateadd(day, -1, date_trunc("day", getdate()))'
) }}

Execute dbt run -s metric_test which will complete succsfully. The error occurs when querying the table/view in the database:

Error

ANALYTICS_DEV> SELECT t.*
               FROM ANALYTICS_DEV.DEV_WAREHOUSE.NEW_OPPORTUNITIES t
               LIMIT 501
[2022-03-31 00:25:22] [22007][100040] Date 'dateadd(day, -1, date_trunc("day", getdate()))' is not recognized

Compiled SQL snipet

bounded as (
    select 
        *,
        cast('202201-01' as date) as lower_bound,
        cast('dateadd(day, -1, date_trunc("day", getdate()))' as date) as upper_bound
    from joined 
),

Proposed Solution

I want to define a metric that uses a mix of functions to calculate a date 'date_trunc("day", getdate()))', and plain date strings '2021-01-01'. The macro should detect:

• If start_date contains a function call, then leave it un-quoted so the call will be executed
• Else if state_date contains only a basic date string, wrap it in quotes
• Else act like start_date isn't defined

Desired compile SQL snipet

bounded as (
    select 
        *,
         cast('2021-01-01' as date) as lower_bound,
         cast(dateadd(day, -1, date_trunc("day", getdate())) as date) as upper_bound
    from joined 
),

PR to resolve

• Allow start/end dates in metrics to be simple strings and function calls #28

[joellabes]

For anyone else following along, discussion is happening on the PR: #28 (comment)

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.

[joellabes]

Since this has been reopened, and there's not really much discussion on the PR (#28), copy-pasting my comment to save future people a click:

Thanks for opening this alexberryman! I agree with your diagnosis of the problem (it should be possible to use functions as proxies for dates), but not your proposed solution.

An easier, more stable, and more consistent with prior art approach would be to remove the enclosing quotes and instead require callers with a fixed date to double quote their entries. This means that your example would instead look like:

--metric_test.sql
select 
    *
from {{ metrics.metric(
    metric_name='new_opportunities',
    grain='month',
    dimensions=['lead_source'],
    start_date="'2022-01-01'", --note the extra quotes here
    end_date='dateadd(day, -1, date_trunc("day", getdate()))'
) }}

This would line up with how other macros in dbt_utils work, e.g. dateadd, datediff, split_part.

I happened to talk a lot about a similar problem in this thread last week: dbt-labs/dbt-utils#528 (comment)

If you wanted to change this PR to remove the existing quotes and update the documentation to reflect the new best practice, we'd be happy to get that merged in!