start_date and end_date arguments need to be cast as date

[fivetran-joemarkiewicz]

Description of the Issue

When leveraging the start_date and end_date arguments for a metric I receive an error indicating the datatypes for period and upper/lower_bound fields within the final cte "where" condition do not match. I believe this issue is originating from within the get_metric_sql.sql macro.

How to reproduce

Create the following metric_test.sql file:

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

Execute dbt run -s metric_test and see the below error:

22:52:46  Running with dbt=1.0.3
22:52:47  Found 15 models, 14 tests, 0 snapshots, 0 analyses, 524 macros, 0 operations, 0 seed files, 4 sources, 0 exposures, 1 metric
22:52:47  
22:52:47  Concurrency: 4 threads (target='bigquery')
22:52:47  
22:52:47  1 of 1 START table model dbt_joe_salesforce.metric_test......................... [RUN]
22:52:52  1 of 1 ERROR creating table model dbt_joe_salesforce.metric_test................ [ERROR in 4.89s]
22:52:52  
22:52:52  Finished running 1 table model in 5.52s.
22:52:52  
22:52:52  Completed with 1 error and 0 warnings:
22:52:52  
22:52:52  Database Error in model metric_test (models/metric_test.sql)
22:52:52    No matching signature for operator >= for argument types: DATE, STRING. Supported signature: ANY >= ANY at [143:11]
22:52:52    compiled SQL at target/run/my_new_project/models/metric_test.sql
22:52:52  
22:52:52  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Proposed Solution

Digging further into this, I think the solution is pretty straight forward. I believe the issue can be addressed by casting the start_date and end_date references in the below line as dates:

dbt_metrics/macros/get_metric_sql.sql

Lines 146 to 147 in 861b095

	{% if start_date %} '{{ start_date }}' {% else %} min(case when has_data then period end) over () {% endif %} as lower_bound,
	{% if end_date %} '{{ end_date }}' {% else %} max(case when has_data then period end) over () {% endif %} as upper_bound

I actually have a working branch on my forked repo that I am using to get around this error. See the quick changes here.

I am happy to open a PR if this is an issue others are encountering. Additionally, please let me know if I am missing something. Thanks!

[joellabes]

Interesting! Not to be all "works on my machine", but this did literally work on my machine 😂 your way is better though! dates are fiddly and it's better to be explicit.

I'd happily merge that PR 🥳

[fivetran-joemarkiewicz]

That's so strange? I wonder if it could be a warehouse specific strangeness? I was trying the arguments using bigquery and was not having any luck.

Dates are fiddly indeed haha I'll open up the PR shortly.

[dannyshaw]

Confirming this issue on Redshift via the error message:

failed to find conversion function from "unknown" to date

#22 solves it. Looking forward to the release!

[joellabes]

Thanks for confirming @dannyshaw! I'll be doing a release shortly 🎉