Gitlab hero border pattern left svg Gitlab hero border pattern right svg

Snowplow

On this page

Snowplow Overview

See Snowplow's architecture overview for more detail.

See the GitLab implementation of Snowplow here.

Snowpipe

We ingest events using Snowpipe, which is a feature of our Data Warehouse Snowflake. An Amazon SQS event queue was set up for the good and bad event paths.

To run properly, Snowpipe needs a "stage" in Snowflake and a table to write to. The good and bad S3 paths each have their own Stage within Snowflake. These are named gitlab_events and gitlab_bad_events, respectively. They are owned by the LOADER role.

The create table statements for the good and bad events are as follows:

-- Good Events
create or replace table snowplow.gitlab_events (
app_id varchar,
platform varchar,
etl_tstamp varchar,
collector_tstamp varchar,
dvce_created_tstamp varchar,
event varchar,
event_id varchar,
txn_id varchar,
name_tracker varchar,
v_tracker varchar,
v_collector varchar,
v_etl varchar,
user_id varchar,
user_ipaddress varchar,
user_fingerprint varchar,
domain_userid varchar,
domain_sessionidx varchar,
network_userid varchar,
geo_country varchar,
geo_region varchar,
geo_city varchar,
geo_zipcode varchar,
geo_latitude varchar,
geo_longitude varchar,
geo_region_name varchar,
ip_isp varchar,
ip_organization varchar,
ip_domain varchar,
ip_netspeed varchar,
page_url varchar,
page_title varchar,
page_referrer varchar,
page_urlscheme varchar,
page_urlhost varchar,
page_urlport varchar,
page_urlpath varchar,
page_urlquery varchar,
page_urlfragment varchar,
refr_urlscheme varchar,
refr_urlhost varchar,
refr_urlport varchar,
refr_urlpath varchar,
refr_urlquery varchar,
refr_urlfragment varchar,
refr_medium varchar,
refr_source varchar,
refr_term varchar,
mkt_medium varchar,
mkt_source varchar,
mkt_term varchar,
mkt_content varchar,
mkt_campaign varchar,
contexts varchar,
se_category varchar,
se_action varchar,
se_label varchar,
se_property varchar,
se_value varchar,
unstruct_event varchar,
tr_orderid varchar,
tr_affiliation varchar,
tr_total varchar,
tr_tax varchar,
tr_shipping varchar,
tr_city varchar,
tr_state varchar,
tr_country varchar,
ti_orderid varchar,
ti_sku varchar,
ti_name varchar,
ti_category varchar,
ti_price varchar,
ti_quantity varchar,
pp_xoffset_min varchar,
pp_xoffset_max varchar,
pp_yoffset_min varchar,
pp_yoffset_max varchar,
useragent varchar,
br_name varchar,
br_family varchar,
br_version varchar,
br_type varchar,
br_renderengine varchar,
br_lang varchar,
br_features_pdf varchar,
br_features_flash varchar,
br_features_java varchar,
br_features_director varchar,
br_features_quicktime varchar,
br_features_realplayer varchar,
br_features_windowsmedia varchar,
br_features_gears varchar,
br_features_silverlight varchar,
br_cookies varchar,
br_colordepth varchar,
br_viewwidth varchar,
br_viewheight varchar,
os_name varchar,
os_family varchar,
os_manufacturer varchar,
os_timezone varchar,
dvce_type varchar,
dvce_ismobile varchar,
dvce_screenwidth varchar,
dvce_screenheight varchar,
doc_charset varchar,
doc_width varchar,
doc_height varchar,
tr_currency varchar,
tr_total_base varchar,
tr_tax_base varchar,
tr_shipping_base varchar,
ti_currency varchar,
ti_price_base varchar,
base_currency varchar,
geo_timezone varchar,
mkt_clickid varchar,
mkt_network varchar,
etl_tags varchar,
dvce_sent_tstamp varchar,
refr_domain_userid varchar,
refr_dvce_tstamp varchar,
derived_contexts varchar,
domain_sessionid varchar,
derived_tstamp varchar,
event_vendor varchar,
event_name varchar,
event_format varchar,
event_version varchar,
event_fingerprint varchar,
true_tstamp varchar,
uploaded_at timestamp_ntz(9) default CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ(9))
)

-- Bad Events Table
  create or replace table snowplow.gitlab_bad_events (
  jsontext variant,
    uploaded_at timestamp_ntz(9) default CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ(9))
  );

Since TSV is not as straight-forward as CSV, a custom file format was created with the following statment:

create or replace file format snowplow_tsv type = csv field_delimiter='\t'

The actual pipe for good events was created using:

create or replace pipe raw.snowplow.gitlab_good_event_pipe auto_ingest=true as
  copy into raw.snowplow.gitlab_events
                from (select $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,
                      CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ(9)) as uploaded_at from @raw.snowplow.gitlab_events)
                file_format = (format_name = 'snowplow_tsv')
                on_error='skip_file';

This highlights the fact that JSON would be a better format. This could be a future iteration of the infrastructure.

The bad event pipe is created as follows:

  create or replace pipe raw.snowplow.gitlab_bad_event_pipe auto_ingest=true as
  copy into raw.snowplow.gitlab_bad_events (jsontext)
  from @raw.snowplow.gitlab_bad_events
  file_format = (type = 'JSON')
  ;

To view pipes:

show pipes in raw.snowplow;

To describe a pipe:

describe pipe raw.snowplow.gitlab_good_event_pipe;

To pause a running pipe:

alter pipe raw.snowplow.gitlab_good_event_pipe set pipe_execution_paused=true;

To force resume a pipe:

select system$pipe_force_resume('raw.snowplow.gitlab_good_event_pipe');

To check the status of a pipe

select system$pipe_status('raw.snowplow.gitlab_good_event_pipe');

To force a refresh of the stage so that snowpipe picks up older events:

alter pipe gitlab_good_event_pipe refresh;