Need to collect the data in the EST timezone using the dbt-snowplow-web package

For engineers Collectors
1

I collect events by integrating Snowplow into the website using Google Tag Manager configuration. For the data models, I’m utilizing dbt-snowplow-web. However, I believe that this package collects event timestamps in UTC, whereas I need to gather events in the EST timezone. Could you please guide me on how to achieve that? Also, is it possible to collect the same event in both UTC and EST timezones?

2

Hi @Madhan , with Snowplow both collection and modelling keep all records in UTC for clarity, certainty, and simplicity; so it is not possible to collect or model your events directly in EST.

However it should be easy enough for you to either build a view of top of the derived tables that casts to a different timezone, or to build a custom model that uses the x_this_run tables and just does the timezone conversion to replace our derived tables.

Alternatively, depending on your use case and warehouse, it may be better to do this directly in your data extract/BI tool/your connection profile. Hope they helps!

3

Thanks, @Rayan, for this detailed information. If I add a timezone plugin, what will I get in events? Can I write a view based on the events captured by that plugin? Could you please provide a brief explanation of how that might work?

4

I’m one of the Analytics Engineers so I’m not 100% sure about tracking, but I don’t think it would be possible to actually track the events and store them in the atomic events table with a different timezone - we are pretty strict about things being in UTC to avoid the usual timezone issues.

But if you added views/custom models to your dbt project, you would get our derived tables and could add fields for the EST of the time. For example the below is the dbt to build a view on top of the page views table:

select 
    a.*,
    CONVERT_TIMEZONE('UTC', 'EST', DVCE_CREATED_TSTAMP) as DVCE_CREATED_TSTAMP_EST, 
    CONVERT_TIMEZONE('UTC', 'EST', COLLECTOR_TSTAMP) as COLLECTOR_TSTAMP_EST, 
    CONVERT_TIMEZONE('UTC', 'EST', DERIVED_TSTAMP) as DERIVED_TSTAMP_EST, 
    CONVERT_TIMEZONE('UTC', 'EST', START_TSTAMP) as START_TSTAMP_EST, 
    CONVERT_TIMEZONE('UTC', 'EST', END_TSTAMP) as END_TSTAMP_EST, 
    CONVERT_TIMEZONE('UTC', 'EST', MODEL_TSTAMP) as MODEL_TSTAMP_EST
from {{ ref('snowplow_web_page_views') }} a
5

Thank you, @Ryan, for providing these details. They are truly helpful to me.