Data should now be loaded into your warehouse. In this section, we will take a closer look at the output to mitigate data issues and get familiar with the derived tables.
Head to the SQL editor of your choice (e.g.: Snowflake Web UI) to check the model’s output. You should be able to see three new schemas created:
Take some time to familiarize yourself with the derived tables. You could run a few simple queries such as the ones listed below. Make sure to modify the schema to be aligned with your custom dbt schema.
Find out the number of screen views using
WITH VIEWS AS ( SELECT SCREEN_VIEW_NAME, COUNT(*) FROM YOUR_CUSTOM_SCHEMA_DERIVED.SNOWPLOW_MOBILE_SCREEN_VIEWS GROUP BY 1 ORDER BY 2 DESC ) SELECT * FROM VIEWS
Calculate the bounce rate using
WITH BOUNCE_RATE AS ( SELECT APP_ID, COUNT(DISTINCT SESSION_ID) AS SESSIONS, COUNT(DISTINCT CASE WHEN SCREEN_VIEWS = 1 THEN SESSION_ID END) / COUNT(DISTINCT SESSION_ID) AS BOUNCE_RATE FROM YOUR_CUSTOM_SCHEMA_DERIVED.SNOWPLOW_MOBILE_SESSIONS GROUP BY 1 ORDER BY SESSIONS DESC ) SELECT * FROM BOUNCE_RATE
Find out details about the highest engaged user using
WITH ENGAGEMENT AS ( SELECT * FROM YOUR_CUSTOM_SCHEMA_DERIVED.SNOWPLOW_MOBILE_USERS ORDER BY SCREEN_VIEWS DESC LIMIT 1 ) SELECT * FROM ENGAGEMENT
Check out the database section of the documentation site for a full breakdown of what the output should look like.