I think it's a limitation on the Snowflake side, unless I'm just unaware of how to write the query. But, here's the info you're looking for:
Diagnostic Info:
{
"browser-info": {
"language": "en-us",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.3 Safari/605.1.15",
"vendor": "Apple Computer, Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_252-heroku-b09",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_252-heroku",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.252-b09",
"os.name": "Linux",
"os.version": "4.4.0-1088-aws",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"postgres",
"snowflake"
],
"hosting-env": "heroku",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.6 (Ubuntu 12.6-1.pgdg16.04+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.35.4",
"date": "2020-05-28",
"branch": "release-0.35.x",
"hash": "b3080fa"
},
"settings": {
"report-timezone": null
}
}
}
Log:
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:17:02-06:00 DEBUG metabase.middleware.log POST /api/card/200/query 202 [ASYNC: completed] 566.1 ms (4 DB calls) App DB connections: 0/7 Jetty threads: 2/50 (4 idle, 0 queued) (120 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:17:07-06:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 693.1 ms (4 DB calls) App DB connections: 1/7 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:20:14-06:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 539.7 ms (4 DB calls) App DB connections: 1/7 Jetty threads: 5/50 (3 idle, 0 queued) (119 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:12-06:00 DEBUG metabase.middleware.log GET /api/database/5/autocomplete_suggestions 200 13.8 ms (5 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (4 idle, 0 queued) (118 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:15-06:00 DEBUG metabase.middleware.log GET /api/database/5/autocomplete_suggestions 200 11.8 ms (5 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (4 idle, 0 queued) (120 total active threads) Queries in flight: 0 (0 queued)
[092a0896-d156-4106-859b-339257d04819] 2021-04-27T15:23:17-06:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null {:database_id 5, :started_at (t/zoned-date-time "2021-04-27T21:23:16.303Z[Etc/UTC]"), :state "42000", :json_query {:database 5, :native {:query "
WITH
raw_data AS (
SELECT
end_of_month
, sum(converted_balance) AS ending_receivables
, sum(credit_sales) AS credit_sales
FROM analytics_public.m_full_invoice_history
WHERE company_id = {{ company_id }}
-- Invoice Assignees
[[ AND arrays_overlap(split({{ assignees__in }}, ','), invoice_assignees) ]]
-- Invoice Tags
[[ AND arrays_overlap(split({{ invoice_tags__in }}, ','), invoice_tags) ]]
[[ AND (NOT arrays_overlap(split({{ invoice_tags__not_in }}, ','), invoice_tags) OR invoice_tags IS NULL) ]]
-- Customer Tags
[[ AND arrays_overlap(split({{ customer_tags__in }}, ','), customer_tags) ]]
[[ AND (NOT arrays_overlap(split({{ customer_tags__not_in }}, ','), customer_tags) OR customer_tags IS NULL) ]]
-- Subsidiary
[[ AND array_contains(to_char(subsidiary_id)::variant, split({{ subsidiary__in }}, ',')) ]]
[[ AND (NOT array_contains(to_char(subsidiary_id)::variant, split({{ subsidiary__not_in }}, ',')) OR subsidiary_id IS NULL) ]]
-- Billing Country
[[ AND array_contains(billing_country::variant, split({{ billing_country__in }}, ',')) ]]
[[ AND (NOT array_contains(billing_country::variant, split({{ billing_country__not_in }}, ',')) OR billing_country IS NULL) ]]
-- Currency
[[ AND array_contains(currency::variant, split({{ currency__in }}, ',')) ]]
[[ AND (NOT array_contains(currency::variant, split({{ currency__not_in }}, ',')) OR currency IS NULL) ]]
GROUP BY 1
),
calculation AS (
SELECT
end_of_month
, round(div0(
ending_receivables,
-- Only include rows where all periods have data
CASE
WHEN count(credit_sales) OVER (
ORDER BY end_of_month
ROWS BETWEEN {{ num_periods }} + 1 PRECEDING AND CURRENT ROW
) = {{ num_periods }} + 1 THEN sum(credit_sales) OVER (
ORDER BY end_of_month
ROWS BETWEEN {{ num_periods }} + 1 PRECEDING AND CURRENT ROW
)
ELSE NULL
END
) * CASE
WHEN {{ calc_type }} = 'actual' THEN sum(day(end_of_month)) OVER (
ORDER BY end_of_month
ROWS BETWEEN {{ num_periods }} PRECEDING AND CURRENT ROW
)
ELSE (30 * ({{ num_periods }} + 1))
END, 0) AS DSO
FROM raw_data
ORDER BY 1
)
SELECT date_trunc('month', end_of_month) AS \"DATE\", DSO
FROM calculation
WHERE end_of_month [[BETWEEN {{ date_start }} AND {{ date_end }} --]] >= add_months(current_date, -13)
", :template-tags {:date_end {:id "1fc1f26e-7eba-c559-afc0-3cafb327c5fa", :name "date_end", :display-name "Date end", :type "date", :default nil}, :customer_tags__not_in {:id "0774ea16-ab22-c38f-779e-fcb49894aae9", :name "customer_tags__not_in", :display-name "Customer tags not in", :type "text"}, :billing_country__in {:id "c0494050-cc35-8236-6cef-41ea6a3e5ef4", :name "billing_country__in", :display-name "Billing country in", :type "text"}, :invoice_tags__not_in {:id "bc075144-b818-6ee5-b7f8-8eebabb770f5", :name "invoice_tags__not_in", :display-name "Invoice tags not in", :type "text"}, :assignees__in {:id "a604d917-d214-63c1-c53b-0cc03599311f", :name "assignees__in", :display-name "Assignees in", :type "text"}, :currency__in {:id "0f9aff5f-71c8-54c1-5ac0-c01382ee2a19", :name "currency__in", :display-name "Currency in", :type "text"}, :subsidiary__in {:id "a8a556c9-2465-80bc-d875-ac13eb567e5f", :name "subsidiary__in", :display-name "Subsidiary in", :type "text"}, :company_id {:id "d1c0c913-bc4d-8669-f98f-74ba022302a9", :name "company_id", :display-name "Company", :type "number", :required true}, :date_start {:id "f1d6dfa3-258c-d5cb-c7e9-ca306fa147d8", :name "date_start", :display-name "Date start", :type "date", :default nil}, :calc_type {:id "51053690-a1f1-0991-293f-3d4882e273ef", :name "calc_type", :display-name "Calc type", :type "text", :required true, :default "simple"}, :currency__not_in {:id "d7ae253e-67b4-030d-3d18-8daf7c201beb", :name "currency__not_in", :display-name "Currency not in", :type "text"}, :num_periods {:id "807569aa-95db-d2e6-9fe6-ed29ea6649cb", :name "num_periods", :display-name "Num periods", :type "number", :default "11", :required true}, :invoice_tags__in {:id "a577480f-bc5a-410d-73ac-5402c3f6d1b4", :name "invoice_tags__in", :display-name "Invoice tags in", :type "text"}, :subsidiary__not_in {:id "fa5ad352-e598-82e9-1919-13d59ecbaf59", :name "subsidiary__not_in", :display-name "Subsidiary not in", :type "text"}, :billing_country__not_in {:id "3c4aed4e-b07f-ae69-be43-02b9c599efd2", :name "billing_country__not_in", :display-name "Billing country not in", :type "text"}, :customer_tags__in {:id "a0f1dc69-36c2-1cbe-7837-59162d4da79d", :name "customer_tags__in", :display-name "Customer tags in", :type "text"}}}, :type "native", :parameters [{:type "category", :target ["variable" ["template-tag" "num_periods"]], :value "11"} {:type "category", :target ["variable" ["template-tag" "calc_type"]], :value "simple"} {:type "category", :target ["variable" ["template-tag" "company_id"]], :value "16"}], :middleware {:add-default-userland-constraints? true}}, :status :failed, :class net.snowflake.client.jdbc.SnowflakeSQLException, :stacktrace ["net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:151)" "net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:76)" "net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:495)" "net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:372)" "net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:575)" "net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:265)" "net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:203)" "net.snowflake.client.core.SFStatement.execute(SFStatement.java:874)" "net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:259)" "net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:181)" "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)" "--> driver.sql_jdbc.execute$fn__70879.invokeStatic(execute.clj:267)" "driver.sql_jdbc.execute$fn__70879.invoke(execute.clj:265)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)" "driver.sql_jdbc$fn__73052.invokeStatic(sql_jdbc.clj:49)" "driver.sql_jdbc$fn__73052.invoke(sql_jdbc.clj:47)" "query_processor.context$executef.invokeStatic(context.clj:59)" "query_processor.context$executef.invoke(context.clj:48)" "query_processor.context.default$default_runf.invokeStatic(default.clj:69)" "query_processor.context.default$default_runf.invoke(default.clj:67)" "query_processor.context$runf.invokeStatic(context.clj:45)" "query_processor.context$runf.invoke(context.clj:39)" "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)" "query_processor.reducible$pivot.invoke(reducible.clj:31)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__43149.invoke(mbql_to_native.clj:26)" "query_processor.middleware.check_features$check_features$fn__42463.invoke(check_features.clj:42)" "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__43314.invoke(optimize_datetime_filters.clj:133)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__47282.invoke(wrap_value_literals.clj:137)" "query_processor.middleware.annotate$add_column_info$fn__41084.invoke(annotate.clj:577)" "query_processor.middleware.permissions$check_query_permissions$fn__42338.invoke(permissions.clj:64)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__43813.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__42536.invoke(cumulative_aggregations.clj:61)" "query_processor.middleware.resolve_joins$resolve_joins$fn__44345.invoke(resolve_joins.clj:183)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__39271.invoke(add_implicit_joins.clj:245)" "query_processor.middleware.limit$limit$fn__43135.invoke(limit.clj:38)" "query_processor.middleware.format_rows$format_rows$fn__43116.invoke(format_rows.clj:81)" "query_processor.middleware.desugar$desugar$fn__42602.invoke(desugar.clj:22)" "query_processor.middleware.binning$update_binning_strategy$fn__41628.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__42144.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__38807.invoke(add_dimension_projections.clj:270)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__39027.invoke(add_implicit_clauses.clj:147)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__39420.invoke(add_source_metadata.clj:105)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__44010.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__41269.invoke(auto_bucket_datetimes.clj:125)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__42191.invoke(resolve_source_table.clj:46)" "query_processor.middleware.parameters$substitute_parameters$fn__43795.invoke(parameters.clj:97)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__42243.invoke(resolve_referenced.clj:80)" "query_processor.middleware.expand_macros$expand_macros$fn__42858.invoke(expand_macros.clj:158)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__39451.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__47166.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021$fn__44025.invoke(resolve_database_and_driver.clj:33)" "driver$do_with_driver.invokeStatic(driver.clj:61)" "driver$do_with_driver.invoke(driver.clj:57)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021.invoke(resolve_database_and_driver.clj:27)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__43064.invoke(fetch_source_query.clj:243)" "query_processor.middleware.store$initialize_store$fn__47175$fn__47176.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:46)" "query_processor.store$do_with_store.invoke(store.clj:40)" "query_processor.middleware.store$initialize_store$fn__47175.invoke(store.clj:10)" "query_processor.middleware.cache$maybe_return_cached_results$fn__42120.invoke(cache.clj:209)" "query_processor.middleware.validate$validate_query$fn__47184.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__43162.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39289.invoke(add_rows_truncated.clj:36)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__47151.invoke(results_metadata.clj:128)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__42479.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__43884.invoke(process_userland_query.clj:136)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__42422.invoke(catch_exceptions.clj:174)" "query_processor.reducible$async_qp$qp_STAR___38090$thunk__38091.invoke(reducible.clj:101)" "query_processor.reducible$async_qp$qp_STAR___38090.invoke(reducible.clj:107)" "query_processor.reducible$sync_qp$qp_STAR___38099$fn__38102.invoke(reducible.clj:133)" "query_processor.reducible$sync_qp$qp_STAR___38099.invoke(reducible.clj:132)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:218)" "query_processor$process_userland_query.doInvoke(query_processor.clj:214)" "query_processor$fn__47350$process_query_and_save_execution_BANG___47359$fn__47362.invoke(query_processor.clj:233)" "query_processor$fn__47350$process_query_and_save_execution_BANG___47359.invoke(query_processor.clj:225)" "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403$fn__47406.invoke(query_processor.clj:245)" "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403.invoke(query_processor.clj:238)" "api.dataset$fn__50692$fn__50695.invoke(dataset.clj:52)" "query_processor.streaming$streaming_response_STAR_$fn__34128$fn__34129.invoke(streaming.clj:73)" "query_processor.streaming$streaming_response_STAR_$fn__34128.invoke(streaming.clj:72)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)" "async.streaming_response$do_f_async$fn__23118.invoke(streaming_response.clj:85)"], :context :ad-hoc, :error "SQL compilation error:
syntax error line 46 at position 36 unexpected '+'.
syntax error line 46 at position 40 unexpected 'PRECEDING'.
syntax error line 46 at position 54 unexpected 'CURRENT'.
syntax error line 47 at position 27 unexpected 'THEN'.
syntax error line 47 at position 35 unexpected '('.
syntax error line 47 at position 36 unexpected 'credit_sales'.
syntax error line 47 at position 50 unexpected 'OVER'.", :row_count 0, :running_time 0, :data {:rows [], :cols []}}