spectacles
spectacles copied to clipboard
spectacles-ci
Spectacles SQL raising an error on dimensions from extended views
I am working on a proof of concept for using spectacles and running into some unexpected errors when running spectacles sql. Specifically, we are seeing errors when testing an explore who's view extends another view. In our case, the extended view is used to provide some parameters for filters on the 'parent' view.
Screenshot for reference:
Each of the 4 dimensions in the parameters view is raising an error like:
====================== ******/cohort_by_sale_month.region =====================
The Snowflake database encountered an error while running this query. SQL Syntax
Error: SQL compilation error: error line 258 at position 4 invalid identifier
'COHORT_BY_SALE_MONTH.REGION'
LookML: https://******.cloud.looker.com/projects/******/files/****paramters.view.lkml?line=7
Test SQL: logs/queries/******__cohort_by_sale_month__cohort_by_sale_month_region.sql
When we test these parameterized filters in Looker, they work as expected.
I did a quick search of github issues and didn't see any similar issue reported.
Hi @jakeberesford-palmetto. Thanks for creating the issue. Would you be able to share the SQL that's in the file at the end of the error message?
Hey Dylan, sure thing - here's one of the errors:
The Snowflake database encountered an error while running this query. SQL Syntax
Error: SQL compilation error: error line 258 at position 4 invalid identifier
'COHORT_BY_SALE_MONTH.STATE'
And the resulting SQL (with database.schema obfuscated as ****.****) and some internal values changed
WITH cohort_by_sale_month AS (with constants as (
select
55
)
, unique_key as (
with month_key as (
select
dateadd(
'month'
, row_number() over (
order by
seq4()
) *-1
, dateadd(
'month'
, 1
, date_trunc(
'month'
, current_timestamp::timestampntz
)
)
) as cohort_month_key
from
table(
generator(
rowcount => (
select
*
from
constants
)
)
)
)
, over_time as (
select
row_number() over (
order by
seq4()
) as sk
from
table(
generator(
rowcount => (
select
*
from
constants
)
)
)
union all
select
0
)
select
*
from
month_key
cross join over_time
)
, user_filter as (
select
*
, max(cohort_month_key) over (partition by 1) as max_cohort_month
, datediff('month', cohort_month_key, max_cohort_month) as months_from_start
from
unique_key
where 1=1 -- no filter on 'cohort_by_sale_month.sold_at_filter'
)
, cohort_month as (
with get_month as (
select
distinct sales_member_id
, min(contract_received_confirmed_at::timestampntz) over (partition by sales_member_id) as cohort_month
from
****.****.tmp_sales_funnel_next
where
tmp_sales_funnel_next.contract_received_confirmed_at is not null
and 1=1 -- no filter on 'cohort_by_sale_month.sales_partner_id_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.state_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.region_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.partner_filter'
)
select
date_trunc(
'month'
, cohort_month
) as cohort_month_key
, count(distinct sales_member_id) as cohort_size
from
get_month
group by
1
)
, cohort_details as (
with cohort_unique_month as (
select
tmp_sales_funnel_next.sales_member_id as cohort_id
, tmp_sales_funnel_next.salesforce_opportunity_id as activity_id
, tmp_salesforce_opportunity.amount as activity_amount
, decode(
tmp_salesforce_opportunity.installation_complete_date__c
, null
, 0
, 1
) as activity_install
, decode(
tmp_salesforce_opportunity.installation_complete_date__c
, null
, 0
, tmp_salesforce_opportunity.amount
) as activity_revenue
, min(tmp_sales_funnel_next.contract_received_confirmed_at::timestampntz) over (
partition by tmp_sales_funnel_next.sales_member_id
) as anchor_month
, tmp_sales_funnel_next.contract_received_confirmed_at::timestampntz as over_time
from
****.****.tmp_sales_funnel_next
inner join ****.****.tmp_salesforce_opportunity tmp_salesforce_opportunity on
tmp_sales_funnel_next.salesforce_opportunity_id = tmp_salesforce_opportunity.id
where
tmp_sales_funnel_next.contract_received_confirmed_at is not null
and 1=1 -- no filter on 'cohort_by_sale_month.sales_partner_id_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.state_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.region_filter'
and 1=1 -- no filter on 'cohort_by_sale_month.partner_filter'
)
, cohort_unique_month_amount as (
select
date_trunc(
'month'
, anchor_month
) as cohort_month_key
, sum(activity_amount) as cohort_sales_amount
, sum(activity_install) as cohort_installs
, sum(activity_revenue) as cohort_revenue
from
cohort_unique_month
group by
1
)
, cohort_active_month as (
select
date_trunc(
'month'
, anchor_month
) as cohort_month_key
, datediff(
'month'
, anchor_month
, over_time
) as cohort_sales_over_time
, count(distinct cohort_id) as active_cohorts
, count(distinct activity_id) as cohort_sales
from
cohort_unique_month
group by
1
, 2
)
select
distinct cohort_active_month.cohort_month_key
, cohort_active_month.cohort_sales_over_time
, cohort_active_month.active_cohorts
, cohort_active_month.cohort_sales
, cohort_unique_month_amount.cohort_installs
, cohort_unique_month_amount.cohort_sales_amount
, cohort_unique_month_amount.cohort_revenue
from
cohort_active_month
inner join cohort_unique_month_amount on
cohort_active_month.cohort_month_key = cohort_unique_month_amount.cohort_month_key
inner join user_filter on
user_filter.cohort_month_key = cohort_active_month.cohort_month_key
and cohort_active_month.cohort_sales_over_time <= user_filter.months_from_start
where 1=1 -- no filter on 'cohort_by_sale_month.sold_at_filter'
)
, months_between_first_and_last_cohort_month as (
select
datediff(
'months'
, min(cohort_month_key)
, dateadd('months', max(cohort_sales_over_time), min(cohort_month_key))
) as months_between_first_and_last_cohort_month
from
cohort_details
)
select
unique_key.sk
, unique_key.cohort_month_key
, dateadd('months', unique_key.sk, unique_key.cohort_month_key) as cohort_month_x_axis
, ifnull(cohort_month.cohort_size, 0) as cohort_size
, ifnull(cohort_details.active_cohorts, 0) as active_cohorts
, max(ifnull(cohort_details.cohort_installs, 0)) over (
partition by unique_key.cohort_month_key
) as cohort_installs
, max(ifnull(cohort_details.cohort_sales_amount, 0)) over (
partition by unique_key.cohort_month_key
) as cohort_sales_amount
, max(ifnull(cohort_details.cohort_revenue, 0)) over (
partition by unique_key.cohort_month_key
) as cohort_revenue
, ifnull(active_cohorts / nullif(cohort_size, 0), 0) as cohort_conversion_ratio
, ifnull(cohort_details.cohort_sales, 0) as cohort_sales
, ifnull(first_value(cohort_sales) ignore nulls over (partition by unique_key.cohort_month_key order by unique_key.sk), 0) as cohort_first_month_sales
, ifnull(cohort_sales / nullif(cohort_first_month_sales, 0), 0) as cohort_improvement_ratio
, ifnull((cohort_sales - cohort_first_month_sales) / nullif(cohort_first_month_sales, 0), -1) as cohort_improvement_change
, ifnull(cohort_sales / nullif(active_cohorts, 0), 0) as cohort_sales_over_active_cohorts
, case date_trunc('quarter', unique_key.cohort_month_key):: varchar
when '2017-07-01 00:00:00.000' then 1
when '2017-10-01 00:00:00.000' then 2
when '2018-01-01 00:00:00.000' then 3
when '2018-04-01 00:00:00.000' then 4
when '2018-07-01 00:00:00.000' then 5
when '2018-10-01 00:00:00.000' then 6
when '2019-01-01 00:00:00.000' then 7
when '2019-04-01 00:00:00.000' then 8
when '2019-07-01 00:00:00.000' then 9
when '2019-10-01 00:00:00.000' then 10
when '2020-01-01 00:00:00.000' then 11
when '2020-04-01 00:00:00.000' then 12
when '2020-07-01 00:00:00.000' then 12
when '2020-10-01 00:00:00.000' then 14
when '2021-01-01 00:00:00.000' then 15
when '2021-04-01 00:00:00.000' then 16
when '2021-07-01 00:00:00.000' then 17
when '2021-10-01 00:00:00.000' then 18
else 19
end as member_acquisition_spend
from
unique_key
left join cohort_month on
unique_key.cohort_month_key = cohort_month.cohort_month_key
left join cohort_details on
unique_key.cohort_month_key = cohort_details.cohort_month_key
and unique_key.sk = cohort_details.cohort_sales_over_time
where
unique_key.sk <= (select * from months_between_first_and_last_cohort_month)
and 1=1 -- no filter on 'cohort_by_sale_month.sold_at_filter'
)
SELECT
cohort_by_sale_month.state AS "cohort_by_sale_month.state"
FROM cohort_by_sale_month
WHERE (1 = 2)
GROUP BY
1
ORDER BY
1
FETCH NEXT 0 ROWS ONLY
Thanks!
What I'm finding a little odd is that there's actually no reference in the code to COHORT_BY_SALE_MONTH.REGION. I presume this SQL runs when you run it in Snowflake?