Skip to main content

Web to Unified

This migration guide is for the Snowplow Web (legacy dbt package) users who would like to upgrade to the Snowplow Unified Digital package which is our most supported and feature-rich dbt package you can use to model web and/or mobile event data.

Simplified Upgrade Overview​

Migration steps - part IMigration steps - part I

Migration steps - part IIMigration steps - part II

Breaking changes between the packages​

  • the page_views table has been renamed to views
  • the domain_userid field has been renamed to user_identifier, as you now have the ability to define your own custom logic / alternative user id field for the aggregation
  • the domain_sessionid has been renamed to session_identifier, as you now have the ability to define your own custom logic / alternative session id field for the aggregation
  • apart from these, fields generated by the the out-of-the-box entities and enrichments have been added a prefix to indicate where they are coming from e.g. device_version has been renamed to yauaa__device_version
  • these out-of-the-box entities and enrichments can optionally be enabled or disabled for the model (previously these values were there as NULLs) through variables e.g. snowplow__enable_yauaa: true which are then flattened automatically within the base_events_this_run table and can be referred to from there directly for custom models, if needed
  • some package variables have been renamed (as well as new ones added)

Things to bear in mind (no 100% accuracy!)​

Due to the above mentioned breaking changes, ideally it is best to start from scratch and run the new package on your whole historic events dataset. However, in certain scenarios (e.g. in case of large data volumes) to save time, effort and cost it may be useful to try and upgrade instead using the existing modeled derived dataset.

Please bear in mind that the there is no 100% accurate upgrade solution but with the below steps and sql scripts we may be able to get you to roughly 95% of the way there:

  1. Incomplete / inaccurate fields for historic data: The unified package contains a list of new fields, some of which we can compute on a row-by-row basis, in which case we provided update scripts to compute them for your existing derived data. There are, however, a few other new fields that are a result of aggregate or other computation that we cannot calculate without a full refresh. E.g. absolute_time_in_s for users therefore they will stay NULL. Then when a new event comes to update that record it may either correct it or it will just be inaccurate.

  2. Fields that are not available in the out-of-the-box derived tables: There are a few fields that we have dropped as they were incorporated into a new field based on different grouping. There may also be custom fields you have added to the derived tables. The below passthrough variables come in handy in this case:

    snowplow__view_passthroughs: []
    snowplow__session_passthroughs: []
    snowplow__user_first_passthroughs: []
    snowplow__user_last_passthroughs: []

For more on this, have a look at the passthrough fields section.

Upgrade steps​

Step 1: Migrate to the latest version of the web package​

Once you decided you would like to go ahead with the upgrade process you will first need to make sure your version of the web model is on par with the latest ( v.1.0.1). The Web model migration guides will help you decide what other changes you need to take into consideration and if the changes apply to your warehouse, you may need to execute the upgrade sql scripts to bridge the gap.

Step 2: Execute SQL scripts to create the new tables​

You will then need to execute a list of sql scripts we provide below, which will first create the new tables based on your existing derived tables created by the web package, then make the changes (e.g. renaming, adding, dropping and updating columns wherever possible). The manifest tables will also be altered making sure that you will then have everything ready for a new run in the unified package as if nothing happened.

Execute the below sql scripts with your database IDE to create your new derived and manifest tables at once without having to reprocess your event data from scratch. Make sure to update (your_schema)_derived to match your derived schema name beforehand. Please also be aware you might need to adjust the data varchar data type to string depending on your warehouse (Bigquery, Databricks users mainly) or limit it to the maximum in case there are limitations (potentially Redshift).

SQL scripts
create table (your_schema)_derived.snowplow_unified_views as (select * from (your_schema)_derived.snowplow_web_page_views);
create table (your_schema)_derived.snowplow_unified_sessions as (select * from (your_schema)_derived.snowplow_web_sessions);
create table (your_schema)_derived.snowplow_unified_users as (select * from (your_schema)_derived.snowplow_web_users);
create table (your_schema)_derived.snowplow_unified_incremental_manifest as (select * from (your_schema)_derived.snowplow_web_incremental_manifest);
create table (your_schema)_snowplow_manifest.snowplow_unified_base_sessions_lifecycle_manifest as (select * from (your_schema)_snowplow_manifest.snowplow_web_base_sessions_lifecycle_manifest)

alter table (your_schema)_derived.snowplow_unified_views rename column domain_sessionid to session_identifier;
alter table (your_schema)_derived.snowplow_unified_views rename column domain_userid to user_identifier;
alter table (your_schema)_derived.snowplow_unified_views rename column original_domain_userid to device_identifier;
alter table (your_schema)_derived.snowplow_unified_views drop column original_domain_sessionid;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_class to yauaa__agent_class;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_name to yauaa__agent_name;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_name_version to yauaa__agent_name_version;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_name_version_major to yauaa__agent_name_version_major;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_version to yauaa__agent_version;
alter table (your_schema)_derived.snowplow_unified_views rename column agent_version_major to yauaa__agent_version_major;
alter table (your_schema)_derived.snowplow_unified_views rename column category to iab__category;
alter table (your_schema)_derived.snowplow_unified_views drop column device_brand;
alter table (your_schema)_derived.snowplow_unified_views rename column device_class to yauaa__device_class;
alter table (your_schema)_derived.snowplow_unified_views rename column device_family to ua__device_family;
alter table (your_schema)_derived.snowplow_unified_views rename column device_name to yauaa__device_name;
alter table (your_schema)_derived.snowplow_unified_views rename column device_version to yauaa__device_version;
alter table (your_schema)_derived.snowplow_unified_views rename column domain_sessionidx to device_session_index;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_class to yauaa__layout_engine_class;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_name to yauaa__layout_engine_name;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_name_version to yauaa__layout_engine_name_version;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_name_version_major to yauaa__layout_engine_name_version_major;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_version to yauaa__layout_engine_version;
alter table  (your_schema)_derived.snowplow_unified_views rename column layout_engine_version_major to yauaa__layout_engine_version_major;
alter table  (your_schema)_derived.snowplow_unified_views rename column operating_system_class to yauaa__operating_system_class;
alter table  (your_schema)_derived.snowplow_unified_views rename column operating_system_name to yauaa__operating_system_name;
alter table  (your_schema)_derived.snowplow_unified_views rename column operating_system_name_version to yauaa__operating_system_name_version;
alter table  (your_schema)_derived.snowplow_unified_views rename column operating_system_version to yauaa__operating_system_version;
alter table  (your_schema)_derived.snowplow_unified_views drop column os_family;
alter table  (your_schema)_derived.snowplow_unified_views rename column os_major to ua__os_major;
alter table  (your_schema)_derived.snowplow_unified_views rename column os_minor to ua__os_minor;
alter table  (your_schema)_derived.snowplow_unified_views rename column os_patch to ua__os_patch;
alter table  (your_schema)_derived.snowplow_unified_views rename column os_patch_minor to ua__os_patch_minor;
alter table  (your_schema)_derived.snowplow_unified_views rename column os_version to ua__os_version;
alter table  (your_schema)_derived.snowplow_unified_views rename column page_views_in_session to views_in_session;
alter table  (your_schema)_derived.snowplow_unified_views rename column page_view_id to view_id;
alter table  (your_schema)_derived.snowplow_unified_views rename column page_view_in_session_index to view_in_session_index;
alter table  (your_schema)_derived.snowplow_unified_views rename column primary_impact to iab__primary_impact;
alter table  (your_schema)_derived.snowplow_unified_views rename column reason to iab__reason;
alter table  (your_schema)_derived.snowplow_unified_views rename column spider_or_robot to iab__spider_or_robot;
alter table  (your_schema)_derived.snowplow_unified_views rename column useragent_family to ua__useragent_family;
alter table  (your_schema)_derived.snowplow_unified_views rename column useragent_major to ua__useragent_major;
alter table  (your_schema)_derived.snowplow_unified_views rename column useragent_minor to ua__useragent_minor;
alter table  (your_schema)_derived.snowplow_unified_views rename column useragent_patch to ua__useragent_patch;
alter table  (your_schema)_derived.snowplow_unified_views rename column useragent_version to ua__useragent_version;
alter table  (your_schema)_derived.snowplow_unified_views add column if not exists default_channel_group varchar(25);
alter table  (your_schema)_derived.snowplow_unified_views add column event_name varchar(1000);
update (your_schema)_derived.snowplow_unified_views
set event_name = 'page_view'
where 1=1;
alter table  (your_schema)_derived.snowplow_unified_views add column os_type varchar(16777216);
alter table  (your_schema)_derived.snowplow_unified_views add column os_version varchar(16777216);
alter table  (your_schema)_derived.snowplow_unified_views add column session__previous_session_id varchar(36);
alter table  (your_schema)_derived.snowplow_unified_views add column if not exists stitched_user_id varchar(16777216);

alter table (your_schema)_derived.snowplow_unified_sessions rename column domain_sessionid to session_identifier;
alter table (your_schema)_derived.snowplow_unified_sessions rename column domain_userid to user_identifier;
alter table (your_schema)_derived.snowplow_unified_sessions rename column original_domain_userid to device_identifier;
alter table (your_schema)_derived.snowplow_unified_sessions drop column original_domain_sessionid;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_class to yauaa__agent_class;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_name to yauaa__agent_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_name_version to yauaa__agent_name_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_name_version_major to yauaa__agent_name_version_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_version to yauaa__agent_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column agent_version_major to yauaa__agent_version_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column br_lang to first_br_lang;
alter table (your_schema)_derived.snowplow_unified_sessions rename column br_lang_name to first_br_lang_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column category to iab__category;
alter table (your_schema)_derived.snowplow_unified_sessions drop column device_brand;
alter table (your_schema)_derived.snowplow_unified_sessions rename column device_class to yauaa__device_class;
alter table (your_schema)_derived.snowplow_unified_sessions rename column device_family to ua__device_family;
alter table (your_schema)_derived.snowplow_unified_sessions rename column device_name to yauaa__device_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column device_version to yauaa__device_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column domain_sessionidx to device_session_index;
alter table (your_schema)_derived.snowplow_unified_sessions rename column geo_city to first_geo_city;
alter table (your_schema)_derived.snowplow_unified_sessions rename column geo_continent to first_geo_continent;
alter table (your_schema)_derived.snowplow_unified_sessions rename column geo_country to first_geo_country;
alter table (your_schema)_derived.snowplow_unified_sessions rename column geo_country_name to first_geo_country_name;
alter table (your_schema)_derived.snowplow_unified_sessions drop column geo_region;
alter table (your_schema)_derived.snowplow_unified_sessions rename column geo_region_name to first_geo_region_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_class to yauaa__layout_engine_class;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_name to yauaa__layout_engine_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_name_version to yauaa__layout_engine_name_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_name_version_major to yauaa__layout_engine_name_version_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_version to yauaa__layout_engine_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column layout_engine_version_major to yauaa__layout_engine_version_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column operating_system_class to yauaa__operating_system_class;
alter table (your_schema)_derived.snowplow_unified_sessions rename column operating_system_name to yauaa__operating_system_name;
alter table (your_schema)_derived.snowplow_unified_sessions rename column operating_system_name_version to yauaa__operating_system_name_version;
alter table (your_schema)_derived.snowplow_unified_sessions rename column operating_system_version to yauaa__operating_system_version;
alter table (your_schema)_derived.snowplow_unified_sessions drop column os_family;
alter table (your_schema)_derived.snowplow_unified_sessions rename column os_major to ua__os_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column os_minor to ua__os_minor;
alter table (your_schema)_derived.snowplow_unified_sessions rename column os_patch to ua__os_patch;
alter table (your_schema)_derived.snowplow_unified_sessions rename column os_patch_minor to ua__os_patch_minor;
alter table (your_schema)_derived.snowplow_unified_sessions rename column page_views to views;
alter table (your_schema)_derived.snowplow_unified_sessions rename column primary_impact to iab__primary_impact;
alter table (your_schema)_derived.snowplow_unified_sessions rename column reason to iab__reason;
alter table (your_schema)_derived.snowplow_unified_sessions rename column referrer to page_referrer;
alter table (your_schema)_derived.snowplow_unified_sessions rename column spider_or_robot to iab__spider_or_robot;
alter table (your_schema)_derived.snowplow_unified_sessions rename column useragent_family to ua__useragent_family;
alter table (your_schema)_derived.snowplow_unified_sessions rename column useragent_major to ua__useragent_major;
alter table (your_schema)_derived.snowplow_unified_sessions rename column useragent_minor to ua__useragent_minor;
alter table (your_schema)_derived.snowplow_unified_sessions rename column useragent_patch to ua__useragent_patch;
alter table (your_schema)_derived.snowplow_unified_sessions rename column useragent_version to ua__useragent_version;
alter table (your_schema)_derived.snowplow_unified_sessions add column os_type varchar(16777216);
alter table (your_schema)_derived.snowplow_unified_sessions add column session_duration_s number(18,0);
alter table (your_schema)_derived.snowplow_unified_sessions add column session__previous_session_id varchar(36);
alter table (your_schema)_derived.snowplow_unified_sessions add column first_event_name varchar(1000);
alter table (your_schema)_derived.snowplow_unified_sessions add column last_event_name varchar(1000);
alter table  (your_schema)_derived.snowplow_unified_sessions add column if not exists stitched_user_id varchar(16777216);

alter table (your_schema)_derived.snowplow_unified_users rename column domain_userid to user_identifier;
alter table (your_schema)_derived.snowplow_unified_users drop column original_domain_userid;
alter table (your_schema)_derived.snowplow_unified_users rename column page_views to views;
alter table (your_schema)_derived.snowplow_unified_users rename column referrer to page_referrer;
alter table (your_schema)_derived.snowplow_unified_users add column on_mobile boolean;
update (your_schema)_derived.snowplow_unified_users
set on_mobile = false
where 1=1;
alter table (your_schema)_derived.snowplow_unified_users add column on_web boolean;
update (your_schema)_derived.snowplow_unified_users
set on_web = true
where 1=1;
alter table (your_schema)_derived.snowplow_unified_users add column screen_names_viewed number(30,0);
alter table (your_schema)_derived.snowplow_unified_users add column sessions_duration_s number(30,0);
alter table (your_schema)_derived.snowplow_unified_users add column active_days number(18,0);
alter table (your_schema)_derived.snowplow_unified_users add column last_platform varchar(255);
update (your_schema)_derived.snowplow_unified_users
set last_platform = 'web'
where 1=1;
alter table (your_schema)_derived.snowplow_unified_users add column last_screen_resolution varchar(16777216);
alter table (your_schema)_derived.snowplow_unified_users add column last_os_type varchar(16777216);
alter table (your_schema)_derived.snowplow_unified_users add column last_os_version varchar(16777216);
alter table (your_schema)_derived.snowplow_unified_users add column first_platform varchar(255);
update (your_schema)_derived.snowplow_unified_users
set first_platform = 'web'
where 1=1;
alter table (your_schema)_derived.snowplow_unified_users add column geo_latitude float;
alter table (your_schema)_derived.snowplow_unified_users add column geo_longitude float;
alter table (your_schema)_derived.snowplow_unified_users add column geo_timezone varchar(64);
alter table (your_schema)_derived.snowplow_unified_users add column geo_zipcode varchar(15);
alter table  (your_schema)_derived.snowplow_unified_users add column if not exists stitched_user_id varchar(16777216);

update (your_schema)_snowplow_manifest.snowplow_unified_incremental_manifest
set model = replace(model, 'snowplow_web', 'snowplow_unified')
where 1=1;

update (your_schema)_snowplow_manifest.snowplow_unified_incremental_manifest
set model = case when model = 'snowplow_unified_page_views_this_run' then 'snowplow_unified_views_this_run'
when model = 'snowplow_unified_page_views' then 'snowplow_unified_views' else model end
where 1=1;

Step 3: Setting up a new dbt project for unified​

Due to a package conflict resulting from the last web and latest unified requiring a different version of the snowplow-utils package it is probably best to create a new dbt project for running the package.

3.1 Packages.yml

packages:
  - package: snowplow/snowplow_unified
    version: 0.4.1

3.2 Project.yml

In the project.yml file make sure to disable the mobile events:

vars:
  snowplow_unified:
    snowplow__enable_mobile: false

In case you changed any of the default variables in your web project, add them here as well. Bear in mind that while 90% of the variables stayed the same, there are some notable changes, deletions, additions and default modifications, please have a look below or at the detailed configuration page and adjust accordingly:

Removed variables:

  • snowplow__limit_page_views_to_session

Renamed variables:

  • snowplow__page_view_stitching to snowplow__view_stitching
  • snowplow__consent_cmp_visible to snowplow__cmp_visible_events (Redshift only)
  • snowplow__consent_preferences to snowplow__consent_preferences_events (Redshift only)
  • snowplow__cwv_context to snowplow__cwv_events (Redshift only)

Added variables:

  • snowplow__conversion_stitching
  • snowplow__conversion_passthroughs
  • snowplow__enable_browser_context
  • snowplow__enable_app_errors
  • snowplow__enable_conversions
  • snowplow__use_refr_if_mkt_null
  • snowplow__browser_context: com_snowplowanalytics_snowplow_browser_context_1 (Redshift only)
  • snowplow__session_context: com_snowplowanalytics_snowplow_client_session_1 (Redshift only)
  • snowplow__mobile_context: com_snowplowanalytics_snowplow_mobile_context_1 (Redshift only)
  • snowplow__geolocation_context: com_snowplowanalytics_snowplow_geolocation_context_1 (Redshift only)
  • snowplow__application_context: com_snowplowanalytics_mobile_application_1 (Redshift only)
  • snowplow__screen_context: com_snowplowanalytics_mobile_screen_1 (Redshift only)
  • snowplow__application_error_events: com_snowplowanalytics_snowplow_application_error_1 (Redshift only)
  • snowplow__screen_view_events: com_snowplowanalytics_mobile_screen_view_1 (Redshift only)
  • snowplow__deep_link_context: com_snowplowanalytics_mobile_deep_link_1 (Redshift only)
  • snowplow__screen_summary_context: com_snowplowanalytics_mobile_screen_summary_1 (Redshift only)

Variables with changed defaults:

  • snowplow__session_identifiers: [{"schema" : "atomic", "field" : "domain_sessionid"}]
  • snowplow__user_identifiers: [{"schema": "atomic", "field" : "domain_userid"}] to none snowplow__databricks_catalog: 'hive_metastore'

Step 4: Verifying the new derived datasets​

At this stage you could potentially run both packages simultaneously until you make sure you are happy to stop the old jobs that updated the web package.

We have also provided views which replicate the original derived tables to help you maintain your existing reporting (in case you use the original derived tables as a source for your BI tool or for downstream data models).

SQL scripts to create views
create view (your_schema)_derived.mock_snowplow_web_page_views as (
  select
    view_id as page_view_id,
    event_id,
    app_id,
    platform,
    user_id,
    user_identifier as domain_userid,
    device_identifier as original_domain_userid,
    stitched_user_id,
    network_userid,
    session_identifier as domain_sessionid,
    null as original_domain_sessionid,
    device_session_index as domain_sessionidx,
    view_in_session_index as page_view_in_session_index,
    views_in_session as page_views_in_session,
    dvce_created_tstamp,
    collector_tstamp,
    derived_tstamp,
    start_tstamp,
    end_tstamp,
    model_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,
    content_group,
    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,
    default_channel_group,
    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,
    iab__category as category,
    iab__primary_impact as primary_impact,
    iab__reason as reason,
    iab__spider_or_robot as spider_or_robot,
    ua__useragent_family as useragent_family,
    ua__useragent_major as useragent_major,
    ua__useragent_minor as useragent_minor,
    ua__useragent_patch as useragent_patch,
    ua__useragent_version as useragent_version,
    null as  os_family,
    ua__os_major as os_major,
    ua__os_minor as os_minor,
    ua__os_patch as os_patch,
    ua__os_patch_minor as os_patch_minor,
    ua__os_version as os_version,
    ua__device_family as device_family,
    yauaa__device_class as device_class,
    device_category,
    screen_resolution,
    yauaa__agent_class as agent_class,
    yauaa__agent_name as agent_name,
    yauaa__agent_name_version as agent_name_version,
    yauaa__agent_name_version_major as agent_name_version_major,
    yauaa__agent_version as agent_version,
    yauaa__agent_version_major as agent_version_major,
    null as device_brand,
    yauaa__device_name as device_name,
    yauaa__device_version as device_version,
    yauaa__layout_engine_class as layout_engine_class,
    yauaa__layout_engine_name as layout_engine_name,
    yauaa__layout_engine_name_version as layout_engine_name_version,
    yauaa__layout_engine_name_version_major as layout_engine_name_version_major,
    yauaa__layout_engine_version as layout_engine_version,
    yauaa__layout_engine_version_major as layout_engine_version_major,
    yauaa__operating_system_class as operating_system_class,
    yauaa__operating_system_name as operating_system_name,
    yauaa__operating_system_name_version as operating_system_name_version,
    yauaa__operating_system_version as operating_system_version,
  from (your_schema)_derived.snowplow_unified_views
);

create view (your_schema)_derived.mock_snowplow_web_sessions as (
  select
    app_id,
    platform,
    session_identifier as domain_sessionid,
    null as original_domain_sessionid,
    device_session_index as domain_sessionidx,
    start_tstamp,
    end_tstamp,
    model_tstamp,
    user_id,
    user_identifier as domain_userid,
    device_identifier as original_domain_userid,
    stitched_user_id,
    network_userid,
    views as page_views,
    engaged_time_in_s,
    total_events,
    is_engaged,
    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,
    page_referrer as 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,
    mkt_source_platform,
    default_channel_group,
    first_geo_country as geo_country,
    null as geo_region,
    first_geo_region_name as geo_region_name,
    first_geo_city as geo_city,
    geo_zipcode,
    geo_latitude,
    geo_longitude,
    geo_timezone,
    first_geo_country_name as geo_country_name,
    first_geo_continent as geo_continent,
    last_geo_country,
    last_geo_region_name,
    last_geo_city,
    last_geo_country_name,
    last_geo_continent,
    user_ipaddress,
    useragent,
    br_renderengine,
    first_br_lang as br_lang,
    first_br_lang_name as br_lang_name,
    last_br_lang,
    last_br_lang_name,
    os_timezone,
    iab__category as category,
    iab__primary_impact as primary_impact,
    iab__reason as reason,
    iab__spider_or_robot as spider_or_robot,
    ua__useragent_family as useragent_family,
    ua__useragent_major as useragent_major,
    ua__useragent_minor as useragent_minor,
    ua__useragent_patch as useragent_patch,
    ua__useragent_version as useragent_version,
    null as  os_family,
    ua__os_major as os_major,
    ua__os_minor as os_minor,
    ua__os_patch as os_patch,
    ua__os_patch as os_patch_minor,
    os_version,
    ua__device_family as device_family,
    yauaa__device_class as device_class,
    device_category,
    screen_resolution,
    yauaa__agent_class as agent_class,
    yauaa__agent_name as agent_name,
    yauaa__agent_name_version as agent_name_version,
    yauaa__agent_name_version_major as agent_name_version_major,
    yauaa__agent_version as agent_version,
    yauaa__agent_version_major as agent_version_major,
    null as device_brand,
    yauaa__device_name as device_name,
    yauaa__device_version as device_version,
    yauaa__layout_engine_class as layout_engine_class,
    yauaa__layout_engine_name as layout_engine_name,
    yauaa__layout_engine_name_version as layout_engine_name_version,
    yauaa__layout_engine_name_version_major as layout_engine_name_version_major,
    yauaa__layout_engine_version as layout_engine_version,
    yauaa__layout_engine_version_major as layout_engine_version_major,
    yauaa__operating_system_class as operating_system_class,
    yauaa__operating_system_name as operating_system_name,
    yauaa__operating_system_name_version as operating_system_name_version,
    yauaa__operating_system_version as operating_system_version
  from (your_schema)_derived.snowplow_unified_sessions
);

create view (your_schema)_derived.mock_snowplow_web_users as (
  select
    user_id,
    user_identifier as domain_userid,
    null as original_domain_userid,
    network_userid,
    start_tstamp,
    end_tstamp,
    model_tstamp,
    views as pageviews,
    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,
    first_geo_country,
    first_geo_country_name,
    first_geo_continent,
    first_geo_city,
    first_geo_region_name,
    first_br_lang,
    first_br_lang_name,
    last_geo_country,
    last_geo_country_name,
    last_geo_continent,
    last_geo_city,
    last_geo_region_name,
    last_br_lang,
    last_br_lang_name,
    last_page_title,
    last_page_url,
    last_page_urlscheme,
    last_page_urlhost,
    last_page_urlpath,
    last_page_urlquery,
    last_page_urlfragment,
    page_referrer as 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,
    mkt_source_platform,
    default_channel_group
    from (your_schema)_derived.snowplow_unified_users
);