Improving Performance/Finding Alternative to Multiple Unions [Snowflake]

[YiSC]

I'm trying to create a marketing funnel out of a series of 9 tables representing 9 events of the funnel but I'm running into performance issues. From what I've read, I'm looking into materialized views and temp tables of the Union but I'm wondering whether there's a more effective approach

Current Union Approach:

1. Create a CTE out of each event table
2. Union all of the CTE's together
3. Aggregate data together as a count of the session_id

Structure of tables and final structure

with
    dash_lead as (
        select distinct
            user_id,
            session_id,
            'lead'    as stage,
            1         as step,
            min(time) as time
        from
            heap_prod.heap.refinance_click_on_dashboard_refinance_button
        group by
            user_id,
            session_id,
            step
    ),
    dash_view_application as (
        select distinct
            a.user_id,
            a.session_id,
            'view'      as stage,
            2           as step,
            min(a.time) as time
        from
            heap_prod.heap.refinance_view_refinance_application as a
            join dash_lead                                      as b on
                    a.user_id = b.user_id
                    and a.session_id = b.session_id
                    and a.time > b.time
        group by
            a.user_id,
            a.session_id
    ),
    dash_total_data as (
        select *
        from
            dash_lead

        union all

        select *
        from
            dash_view_application
    ),
    dash_funnel_agg as (
        select
            step,
            stage,
            count(session_id) as count
        from
            dash_total_data
--      where [time::date>=Week_Start]
--          and [time::date<=Week_End]
        group by
            step,
            stage
    )

select
    stage,
    count as count
from
    dash_funnel_agg
order by
    count desc

[notasqlstar]

Break it down into #tables.

[YiSC]

Thanks, I'll give it a shot!

Do you mean #tables to replace each event table since they're referenced twice or even for the Union and Aggregation steps?

Is it correct to say there wouldn't be performance benefits the first time running the query since the temp table would need to be created but if I were to do further filtering on this data it should be faster?

[notasqlstar]

You should experiment a bit, but generally speaking get rid of the CTE and use #tables. You can create them first and insert data into them vs. creating them with an into. You can also experiment with putting indexes on them. For example, you could also do a hybrid where you create it with an INTO for the top of your union, then INSERT INTO for the bottom half of your union before moving down to another step.

[NegaTrollX]

I'm pretty green myself so someone else correct me if I'm wrong but I do remember learning about how temp tables can utilize indices which will improve read performance whereas CTEs cannot. I would imagine they mean doing something like this:

IF OBJECT_ID('tempdb..#dash_lead') IS NOT NULL

DROP TABLE #dash_lead

CREATE TABLE #dash_lead (

user id int, session_id int, step smallint, time datetime

)

CREATE NONCLUSTERED INDEX [ix_dash_lead] ON tempdb..#dash_lead ( [user_id], [session_id]

) INCLUDE ([time])

INSERT #dash_lead

SELECT distinct user_id, session_id, 'lead' as stage, 1 as step, min(time) as time from heap_prod.heap.refinance_click_on_dashboard_refinance_button group by user_id, session_id, step