Skip to main content

Migrating from SQL Runner to dbt

info

This guide assumes you are running the standard web and/or mobile SQL Runner models, if you have built any custom models or customized the standard models in any way, you will need to make these same changes to the dbt models. See our section on custom dbt models for more information on how to do this.

Why Migrate?โ€‹

SQL Runner is currently in maintenance mode, while we will continue to fix bugs when they are identified, we are not actively developing the tool or the models anymore and at some point in the future may deprecate it entirely. Our dbt models on the other hand are under active development, with new features and optimizations being made regularly. It is also a more widely used tool, meaning installation and management is far easier (or you can use tools like dbt Cloud, or our BDP customers can run dbt models the same way you can SQL Runner). We also have a far wider range of packages available in dbt including e-commerce, marketing attribution, and a package to normalize your Snowplow data.

In dbt we also support Databricks & Postgres warehouses in addition to Snowflake, BigQuery, and Redshift. Our Accelerators contain our dbt models, and newer tracking plugins are only being modeled within our dbt packages.

In general, if you are happy with SQL Runner and don't foresee a need to add more models in the future then there is no need to migrate; however if you are starting from scratch, or would like to make use of our wider range of models, then you should consider migrating to dbt.

Differences between the toolsโ€‹

The core of the web and mobile models (e.g. page/screen views, sessions, and users) are the same across SQL Runner and dbt, however the dbt versions contain some additional fields which may be useful for analysis (such as conversions, event counts, and human-readable language information in the web package), as well as additional optional modules for web such as consent and core web vitals. Below this the logic used to process the data is roughly the same, although we have made some optimizations and added more flexibility in how this processing is done in dbt.

We recommend you take a look at the docs for our dbt packages to get a better understanding of how they work and how you can use them going forward.

Pre-requisitesโ€‹

We assume that you have dbt installed, a working connection, and some basic understanding of using dbt including installing packages and running models.

Mapping the variablesโ€‹

While the variables for your SQL Runner models are spread throughout the files, in dbt all variables are in your dbt_project.yml file. We have mostly been consistent between the two tools, with the dbt variables being prefixed by snowplow__, but some have new names. The table below maps each SQL Runner variable to the equivalent dbt variable, but there are many more you can set to customize how the models run - you can read about these in the relevant configuration page.

caution

When using multiple dbt packages you must be careful to specify which scope a variable or configuration is defined within. In general, always specify each value in your dbt_project.yml nested under the specific package e.g.

dbt_project.yml
vars:
snowplow_web:
snowplow__atomic_schema: schema_with_snowplow_web_events
snowplow_mobile:
snowplow__atomic_schema: schema_with_snowplow_mobile_events

You can read more about variable scoping in dbt's docs around variable precedence.

Values in bold have a different name instead of just the prefix

SQL Runner Variabledbt variable
app_errorssnowplow__enable_app_errors_module
app_id_filterssnowplow__app_id
application_contextsnowplow__enable_application_context
cleanup_modeNo equivalent variable (closest is snowplow__allow_refresh combined with dbt --full-refresh flag)
cluster_byNo equivalent variable (Clustering defined in model)
days_late_allowedsnowplow__days_late_allowed
derived_tstamp_partitionedsnowplow__derived_tstamp_partitioned
enabled (Mobile app errors only)snowplow__enable_app_errors_module
ends_runNo equivalent variable
entropyNo equivalent variable
geolocation_contextsnowplow__enable_geolocation_context
heartbeatsnowplow__heartbeat
iabsnowplow__enable_iab
input_schemasnowplow__atomic_schema
lookback_window_hourssnowplow__lookback_window_hours
minimumVisitLengthsnowplow_min_visit_length
mobile_contextsnowplow__enable_mobile_context
model_versionNo equivalent variable
output_schemaSet in models part of project file, see relevant configuration page for more info.
platform_filterssnowplow__platform
scratch_schemaSet in models part of project file, see relevant configuration page for more info.
screen_contextsnowplow__enable_screen_contextt
session_lookback_dayssnowplow__session_lookback_days (default increased to 730)
skip_derivedNo equivalent variable (use dbt --select flag)
stage_nextNo equivalent variable
start_datesnowplow__start_date
ua_bot_filtersnowplow__ua_bot_filter
ua_parsersnowplow__enable_ua
update_cadence_dayssnowplow__backfill_limit_days (default increased to 30)
upsert_lookback_dayssnowplow__upsert_lookback_days
yauaasnowplow__enable_yauaa

Setting up and running our dbt packagesโ€‹

The latest information for our packages can be found in the quickstart section of our docs. It is highly recommended you just start running your dbt project from scratch and process all data from the start date to ensure the package works as intended and all your data is processed in the same way.

Migrate existing derived dataโ€‹

danger

The dbt package uses slightly different logic for processing, including the quarantining of sessions and different format manifest tables. It is highly recommended that you just run the dbt project from your start date. The following is a best-effort suggestion and we make no guarantee that all data will be correctly processed or that issues may not happen later in the lifetime of the project.

This method will also not correctly populate the user stitching table or process user stitching for historic data.

There may be cases where running the dbt models from scratch is not a viable option for you, in this case it is possible to migrate your existing derived SQL Runner data into the derived tables produced by dbt, however this will result in your data being generated from two slightly differing logics.

It is advisable to produce your dbt tables into new schemas where possible, even though the derived tables should have different names; this will help keep your data separate and ensure that as we go through the following steps that dbt does not overwrite your SQL Runner tables.

note

Postgres only supports the MERGE statement in version 15 and up, if you are using an older version you will need to alter the commands to be a DELETE+INSERT instead.

Create dbt tables by doing a recent-dated runโ€‹

Because of the difference in manifest tables and incremental logic between SQL Runner and dbt models it makes sense to first create the dbt tables and then insert your existing data into them, rather than try and create the dbt tables directly from your SQL Runner data.

Once you have your dbt project and variables set up, change your snowplow__start_date to a recent date, say 7 days before the end of your last SQL Runner processed date, and run the project once. This will produce all the dbt tables including the manifest tables needed to manage the incremental logic, and ensure a good overlap between the end of your SQL Runner processing and the start of dbt processing.

Merge your existing data into the new tables (web)โ€‹

The following SQL will merge the existing web records in your SQL Runner derived tables into the new dbt derived tables, please run each one as required. This approach will leave any columns that only exist in dbt null. Please ensure you replace the schema and table names with your ones where appropriate.

If you are using Redshift, be sure to commit your changes.

caution

It is possible, particularly for columns which may have been null, that the types of columns across the two tables don't entirely match. Your warehouse may manage this for you, or you may have to use a cast(col_name as new_type) in place of just selecting the column based on any error message you receive.

Page Viewsโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_web_page_views t
USING <SQL_RUNNER_DERIVED_SCHEMA>.page_views s
ON T.page_view_id = s.page_view_id
WHEN NOT MATCHED THEN
INSERT
(
page_view_id,
event_id,
app_id,
user_id,
domain_userid,
network_userid,
domain_sessionid,
domain_sessionidx,
page_view_in_session_index,
page_views_in_session,
dvce_created_tstamp,
collector_tstamp,
derived_tstamp,
start_tstamp,
end_tstamp,
engaged_time_in_s,
absolute_time_in_s,
horizontal_pixels_scrolled,
vertical_pixels_scrolled,
horizontal_percentage_scrolled,
vertical_percentage_scrolled,
doc_width,
doc_height,
page_title,
page_url,
page_urlscheme,
page_urlhost,
page_urlpath,
page_urlquery,
page_urlfragment,
mkt_medium,
mkt_source,
mkt_term,
mkt_content,
mkt_campaign,
mkt_clickid,
mkt_network,
page_referrer,
refr_urlscheme,
refr_urlhost,
refr_urlpath,
refr_urlquery,
refr_urlfragment,
refr_medium,
refr_source,
refr_term,
geo_country,
geo_region,
geo_region_name,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_timezone,
user_ipaddress,
useragent,
br_lang,
br_viewwidth,
br_viewheight,
br_colordepth,
br_renderengine,
os_timezone,
category,
primary_impact,
reason,
spider_or_robot,
useragent_family,
useragent_major,
useragent_minor,
useragent_patch,
useragent_version,
os_family,
os_major,
os_minor,
os_patch,
os_patch_minor,
os_version,
device_family,
device_class,
agent_class,
agent_name,
agent_name_version,
agent_name_version_major,
agent_version,
agent_version_major,
device_brand,
device_name,
device_version,
layout_engine_class,
layout_engine_name,
layout_engine_name_version,
layout_engine_name_version_major,
layout_engine_version,
layout_engine_version_major,
operating_system_class,
operating_system_name,
operating_system_name_version,
operating_system_version
)
VALUES
(
s.page_view_id,
s.event_id,
s.app_id,
s.user_id,
s.domain_userid,
s.network_userid,
s.domain_sessionid,
s.domain_sessionidx,
s.page_view_in_session_index,
s.page_views_in_session,
s.dvce_created_tstamp,
s.collector_tstamp,
s.derived_tstamp,
s.start_tstamp,
s.end_tstamp,
s.engaged_time_in_s,
s.absolute_time_in_s,
s.horizontal_pixels_scrolled,
s.vertical_pixels_scrolled,
s.horizontal_percentage_scrolled,
s.vertical_percentage_scrolled,
s.doc_width,
s.doc_height,
s.page_title,
s.page_url,
s.page_urlscheme,
s.page_urlhost,
s.page_urlpath,
s.page_urlquery,
s.page_urlfragment,
s.mkt_medium,
s.mkt_source,
s.mkt_term,
s.mkt_content,
s.mkt_campaign,
s.mkt_clickid,
s.mkt_network,
s.page_referrer,
s.refr_urlscheme,
s.refr_urlhost,
s.refr_urlpath,
s.refr_urlquery,
s.refr_urlfragment,
s.refr_medium,
s.refr_source,
s.refr_term,
s.geo_country,
s.geo_region,
s.geo_region_name,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_timezone,
s.user_ipaddress,
s.useragent,
s.br_lang,
s.br_viewwidth,
s.br_viewheight,
s.br_colordepth,
s.br_renderengine,
s.os_timezone,
s.category,
s.primary_impact,
s.reason,
s.spider_or_robot,
s.useragent_family,
s.useragent_major,
s.useragent_minor,
s.useragent_patch,
s.useragent_version,
s.os_family,
s.os_major,
s.os_minor,
s.os_patch,
s.os_patch_minor,
s.os_version,
s.device_family,
s.device_class,
s.agent_class,
s.agent_name,
s.agent_name_version,
s.agent_name_version_major,
s.agent_version,
s.agent_version_major,
s.device_brand,
s.device_name,
s.device_version,
s.layout_engine_class,
s.layout_engine_name,
s.layout_engine_name_version,
s.layout_engine_name_version_major,
s.layout_engine_version,
s.layout_engine_version_major,
s.operating_system_class,
s.operating_system_name,
s.operating_system_name_version,
s.operating_system_version
);

Sessionsโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_web_sessions t
USING <SQL_RUNNER_DERIVED_SCHEMA>.sessions s
ON T.domain_sessionid = s.domain_sessionid
WHEN NOT MATCHED THEN
INSERT
(
app_id,
domain_sessionid,
domain_sessionidx,
start_tstamp,
end_tstamp,
user_id,
domain_userid,
network_userid,
page_views,
engaged_time_in_s,
absolute_time_in_s,
first_page_title,
first_page_url,
first_page_urlscheme,
first_page_urlhost,
first_page_urlpath,
first_page_urlquery,
first_page_urlfragment,
last_page_title,
last_page_url,
last_page_urlscheme,
last_page_urlhost,
last_page_urlpath,
last_page_urlquery,
last_page_urlfragment,
referrer,
refr_urlscheme,
refr_urlhost,
refr_urlpath,
refr_urlquery,
refr_urlfragment,
refr_medium,
refr_source,
refr_term,
mkt_medium,
mkt_source,
mkt_term,
mkt_content,
mkt_campaign,
mkt_clickid,
mkt_network,
geo_country,
geo_region,
geo_region_name,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_timezone,
user_ipaddress,
useragent,
br_renderengine,
br_lang,
os_timezone,
category,
primary_impact,
reason,
spider_or_robot,
useragent_family,
useragent_major,
useragent_minor,
useragent_patch,
useragent_version,
os_family,
os_major,
os_minor,
os_patch,
os_patch_minor,
os_version,
device_family,
device_class,
agent_class,
agent_name,
agent_name_version,
agent_name_version_major,
agent_version,
agent_version_major,
device_brand,
device_name,
device_version,
layout_engine_class,
layout_engine_name,
layout_engine_name_version,
layout_engine_name_version_major,
layout_engine_version,
layout_engine_version_major,
operating_system_class,
operating_system_name,
operating_system_name_version,
operating_system_version
)
VALUES
(
s.app_id,
s.domain_sessionid,
s.domain_sessionidx,
s.start_tstamp,
s.end_tstamp,
s.user_id,
s.domain_userid,
s.network_userid,
s.page_views,
s.engaged_time_in_s,
s.absolute_time_in_s,
s.first_page_title,
s.first_page_url,
s.first_page_urlscheme,
s.first_page_urlhost,
s.first_page_urlpath,
s.first_page_urlquery,
s.first_page_urlfragment,
s.last_page_title,
s.last_page_url,
s.last_page_urlscheme,
s.last_page_urlhost,
s.last_page_urlpath,
s.last_page_urlquery,
s.last_page_urlfragment,
s.referrer,
s.refr_urlscheme,
s.refr_urlhost,
s.refr_urlpath,
s.refr_urlquery,
s.refr_urlfragment,
s.refr_medium,
s.refr_source,
s.refr_term,
s.mkt_medium,
s.mkt_source,
s.mkt_term,
s.mkt_content,
s.mkt_campaign,
s.mkt_clickid,
s.mkt_network,
s.geo_country,
s.geo_region,
s.geo_region_name,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_timezone,
s.user_ipaddress,
s.useragent,
s.br_renderengine,
s.br_lang,
s.os_timezone,
s.category,
s.primary_impact,
s.reason,
s.spider_or_robot,
s.useragent_family,
s.useragent_major,
s.useragent_minor,
s.useragent_patch,
s.useragent_version,
s.os_family,
s.os_major,
s.os_minor,
s.os_patch,
s.os_patch_minor,
s.os_version,
s.device_family,
s.device_class,
s.agent_class,
s.agent_name,
s.agent_name_version,
s.agent_name_version_major,
s.agent_version,
s.agent_version_major,
s.device_brand,
s.device_name,
s.device_version,
s.layout_engine_class,
s.layout_engine_name,
s.layout_engine_name_version,
s.layout_engine_name_version_major,
s.layout_engine_version,
s.layout_engine_version_major,
s.operating_system_class,
s.operating_system_name,
s.operating_system_name_version,
s.operating_system_version
);

Usersโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_web_users t
USING <SQL_RUNNER_DERIVED_SCHEMA>.users s
ON T.domain_userid = s.domain_userid
WHEN NOT MATCHED THEN
INSERT
(
user_id,
domain_userid,
network_userid,
start_tstamp,
end_tstamp,
page_views,
sessions,
engaged_time_in_s,
first_page_title,
first_page_url,
first_page_urlscheme,
first_page_urlhost,
first_page_urlpath,
first_page_urlquery,
first_page_urlfragment,
last_page_title,
last_page_url,
last_page_urlscheme,
last_page_urlhost,
last_page_urlpath,
last_page_urlquery,
last_page_urlfragment,
referrer,
refr_urlscheme,
refr_urlhost,
refr_urlpath,
refr_urlquery,
refr_urlfragment,
refr_medium,
refr_source,
refr_term,
mkt_medium,
mkt_source,
mkt_term,
mkt_content,
mkt_campaign,
mkt_clickid,
mkt_network
)
VALUES
(
s.user_id,
s.domain_userid,
s.network_userid,
s.start_tstamp,
s.end_tstamp,
s.page_views,
s.sessions,
s.engaged_time_in_s,
s.first_page_title,
s.first_page_url,
s.first_page_urlscheme,
s.first_page_urlhost,
s.first_page_urlpath,
s.first_page_urlquery,
s.first_page_urlfragment,
s.last_page_title,
s.last_page_url,
s.last_page_urlscheme,
s.last_page_urlhost,
s.last_page_urlpath,
s.last_page_urlquery,
s.last_page_urlfragment,
s.referrer,
s.refr_urlscheme,
s.refr_urlhost,
s.refr_urlpath,
s.refr_urlquery,
s.refr_urlfragment,
s.refr_medium,
s.refr_source,
s.refr_term,
s.mkt_medium,
s.mkt_source,
s.mkt_term,
s.mkt_content,
s.mkt_campaign,
s.mkt_clickid,
s.mkt_network,
);

Merge your existing data into the new tables (mobile)โ€‹

The following SQL will merge the existing mobile records in your SQL Runner derived tables into the new dbt derived tables, please run each one as required. This approach will leave any columns that only exist in dbt null. Please ensure you replace the schema and table names with your ones where appropriate.

If you are using Redshift, be sure to commit your changes.

caution

It is possible, particularly for columns which may have been null, that the types of columns across the two tables don't entirely match. Your warehouse may manage this for you, or you may have to use a cast(col_name as new_type) in place of just selecting the column based on any error message you receive.

Screen Viewsโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_mobile_screen_views t
USING <SQL_RUNNER_DERIVED_SCHEMA>.mobile_screen_views s
ON T.screen_view_id = s.screen_view_id
WHEN NOT MATCHED THEN
INSERT
(
screen_view_id,
event_id,
app_id,
user_id,
device_user_id,
network_userid,
session_id,
session_index,
previous_session_id,
session_first_event_id,
screen_view_in_session_index,
screen_views_in_session,
dvce_created_tstamp,
collector_tstamp,
derived_tstamp,
model_tstamp,
screen_view_name,
screen_view_transition_type,
screen_view_type,
screen_fragment,
screen_top_view_controller,
screen_view_controller,
screen_view_previous_id,
screen_view_previous_name,
screen_view_previous_type,
platform,
dvce_screenwidth,
dvce_screenheight,
device_manufacturer,
device_model,
os_type,
os_version,
android_idfa,
apple_idfa,
apple_idfv,
open_idfa,
device_latitude,
device_longitude,
device_latitude_longitude_accuracy,
device_altitude,
device_altitude_accuracy,
device_bearing,
device_speed,
geo_country,
geo_region,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_region_name,
geo_timezone,
user_ipaddress,
useragent,
carrier,
network_technology,
network_type,
build,
version
)
VALUES
(
s.screen_view_id,
s.event_id,
s.app_id,
s.user_id,
s.device_user_id,
s.network_userid,
s.session_id,
s.session_index,
s.previous_session_id,
s.session_first_event_id,
s.screen_view_in_session_index,
s.screen_views_in_session,
s.dvce_created_tstamp,
s.collector_tstamp,
s.derived_tstamp,
s.model_tstamp,
s.screen_view_name,
s.screen_view_transition_type,
s.screen_view_type,
s.screen_fragment,
s.screen_top_view_controller,
s.screen_view_controller,
s.screen_view_previous_id,
s.screen_view_previous_name,
s.screen_view_previous_type,
s.platform,
s.dvce_screenwidth,
s.dvce_screenheight,
s.device_manufacturer,
s.device_model,
s.os_type,
s.os_version,
s.android_idfa,
s.apple_idfa,
s.apple_idfv,
s.open_idfa,
s.device_latitude,
s.device_longitude,
s.device_latitude_longitude_accuracy,
s.device_altitude,
s.device_altitude_accuracy,
s.device_bearing,
s.device_speed,
s.geo_country,
s.geo_region,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_region_name,
s.geo_timezone,
s.user_ipaddress,
s.useragent,
s.carrier,
s.network_technology,
s.network_type,
s.build,
s.version,
);

Sessionsโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_mobile_sessions t
USING <SQL_RUNNER_DERIVED_SCHEMA>.mobile_session s
ON T.session_id = s.session_id
WHEN NOT MATCHED THEN
INSERT
(
app_id,
session_id,
session_index,
previous_session_id,
session_first_event_id,
session_last_event_id,
start_tstamp,
end_tstamp,
model_tstamp,
user_id,
device_user_id,
network_userid,
session_duration_s,
has_install,
screen_views,
screen_names_viewed,
app_errors,
fatal_app_errors,
first_event_name,
last_event_name,
first_screen_view_name,
first_screen_view_transition_type,
first_screen_view_type,
last_screen_view_name,
last_screen_view_transition_type,
last_screen_view_type,
platform,
dvce_screenwidth,
dvce_screenheight,
device_manufacturer,
device_model,
os_type,
os_version,
android_idfa,
apple_idfa,
apple_idfv,
open_idfa,
device_latitude,
device_longitude,
device_latitude_longitude_accuracy,
device_altitude,
device_altitude_accuracy,
device_bearing,
device_speed,
geo_country,
geo_region,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_region_name,
geo_timezone,
user_ipaddress,
useragent,
name_tracker,
v_tracker,
carrier,
network_technology,
network_type,
first_build,
last_build,
first_version,
last_version
)
VALUES
(
s.app_id,
s.session_id,
s.session_index,
s.previous_session_id,
s.session_first_event_id,
s.session_last_event_id,
s.start_tstamp,
s.end_tstamp,
s.model_tstamp,
s.user_id,
s.device_user_id,
s.network_userid,
s.session_duration_s,
s.has_install,
s.screen_views,
s.screen_names_viewed,
s.app_errors,
s.fatal_app_errors,
s.first_event_name,
s.last_event_name,
s.first_screen_view_name,
s.first_screen_view_transition_type,
s.first_screen_view_type,
s.last_screen_view_name,
s.last_screen_view_transition_type,
s.last_screen_view_type,
s.platform,
s.dvce_screenwidth,
s.dvce_screenheight,
s.device_manufacturer,
s.device_model,
s.os_type,
s.os_version,
s.android_idfa,
s.apple_idfa,
s.apple_idfv,
s.open_idfa,
s.device_latitude,
s.device_longitude,
s.device_latitude_longitude_accuracy,
s.device_altitude,
s.device_altitude_accuracy,
s.device_bearing,
s.device_speed,
s.geo_country,
s.geo_region,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_region_name,
s.geo_timezone,
s.user_ipaddress,
s.useragent,
s.name_tracker,
s.v_tracker,
s.carrier,
s.network_technology,
s.network_type,
s.first_build,
s.last_build,
s.first_version,
s.last_version
);

Usersโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_mobile_users t
USING <SQL_RUNNER_DERIVED_SCHEMA>.mobile_users s
ON t.device_user_id = s.device_user_id
WHEN NOT MATCHED THEN
INSERT
(
user_id,
device_user_id,
network_userid,
start_tstamp,
end_tstamp,
model_tstamp,
screen_views,
screen_names_viewed,
sessions,
sessions_duration_s,
active_days,
app_errors,
fatal_app_errors,
first_screen_view_name,
first_screen_view_transition_type,
first_screen_view_type,
last_screen_view_name,
last_screen_view_transition_type,
last_screen_view_type,
platform,
dvce_screenwidth,
dvce_screenheight,
device_manufacturer,
device_model,
os_type,
first_os_version,
last_os_version,
android_idfa,
apple_idfa,
apple_idfv,
open_idfa,
geo_country,
geo_region,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_region_name,
geo_timezone,
first_carrier,
last_carrier
)
VALUES
(
s.user_id,
s.device_user_id,
s.network_userid,
s.start_tstamp,
s.end_tstamp,
s.model_tstamp,
s.screen_views,
s.screen_names_viewed,
s.sessions,
s.sessions_duration_s,
s.active_days,
s.app_errors,
s.fatal_app_errors,
s.first_screen_view_name,
s.first_screen_view_transition_type,
s.first_screen_view_type,
s.last_screen_view_name,
s.last_screen_view_transition_type,
s.last_screen_view_type,
s.platform,
s.dvce_screenwidth,
s.dvce_screenheight,
s.device_manufacturer,
s.device_model,
s.os_type,
s.first_os_version,
s.last_os_version,
s.android_idfa,
s.apple_idfa,
s.apple_idfv,
s.open_idfa,
s.geo_country,
s.geo_region,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_region_name,
s.geo_timezone,
s.first_carrier,
s.last_carrier
);

App Errorsโ€‹

MERGE INTO <DBT_DERIVED_SCHEMA>.snowplow_mobile_app_errors t
USING <SQL_RUNNER_DERIVED_SCHEMA>.mobile_app_errors s
ON t.event_id = s.event_id
WHEN NOT MATCHED THEN
INSERT
(
event_id,
app_id,
user_id,
device_user_id,
network_userid,
session_id,
session_index,
previous_session_id,
session_first_event_id,
dvce_created_tstamp,
collector_tstamp,
derived_tstamp,
model_tstamp,
platform,
dvce_screenwidth,
dvce_screenheight,
device_manufacturer,
device_model,
os_type,
os_version,
android_idfa,
apple_idfa,
apple_idfv,
open_idfa,
screen_id,
screen_name,
screen_activity,
screen_fragment,
screen_top_view_controller,
screen_type,
screen_view_controller,
device_latitude,
device_longitude,
device_latitude_longitude_accuracy,
device_altitude,
device_altitude_accuracy,
device_bearing,
device_speed,
geo_country,
geo_region,
geo_city,
geo_zipcode,
geo_latitude,
geo_longitude,
geo_region_name,
geo_timezone,
user_ipaddress,
useragent,
carrier,
network_technology,
network_type,
build,
version,
event_index_in_session,
message,
programming_language,
class_name,
exception_name,
is_fatal,
line_number,
stack_trace,
thread_id,
thread_name
)
VALUES
(
s.event_id,
s.app_id,
s.user_id,
s.device_user_id,
s.network_userid,
s.session_id,
s.session_index,
s.previous_session_id,
s.session_first_event_id,
s.dvce_created_tstamp,
s.collector_tstamp,
s.derived_tstamp,
s.model_tstamp,
s.platform,
s.dvce_screenwidth,
s.dvce_screenheight,
s.device_manufacturer,
s.device_model,
s.os_type,
s.os_version,
s.android_idfa,
s.apple_idfa,
s.apple_idfv,
s.open_idfa,
s.screen_id,
s.screen_name,
s.screen_activity,
s.screen_fragment,
s.screen_top_view_controller,
s.screen_type,
s.screen_view_controller,
s.device_latitude,
s.device_longitude,
s.device_latitude_longitude_accuracy,
s.device_altitude,
s.device_altitude_accuracy,
s.device_bearing,
s.device_speed,
s.geo_country,
s.geo_region,
s.geo_city,
s.geo_zipcode,
s.geo_latitude,
s.geo_longitude,
s.geo_region_name,
s.geo_timezone,
s.user_ipaddress,
s.useragent,
s.carrier,
s.network_technology,
s.network_type,
s.build,
s.version,
s.event_index_in_session,
s.message,
s.programming_language,
s.class_name,
s.exception_name,
s.is_fatal,
s.line_number,
s.stack_trace,
s.thread_id,
s.thread_name
);
Was this page helpful?