search

Velir / dbt-ga4

dbt Package for modeling raw data exported by Google Analytics 4. BigQuery support, only.
MIT License
324 stars 135 forks source link

feat: Add support for Snowflake #310

Open miron-kremenetskiy opened 7 months ago

miron-kremenetskiy commented 7 months ago

Description & motivation

Checklist

dgitis commented 7 months ago

@miron-kremenetskiy, do you realize that this package works with the native Google Analytics 4 integration with BigQuery? That data is in a streaming format that includes structures that aren't supported by Snowflake and Google does not make this format available to Snowflake.

You can use the API export, but that data is in a totally different format so this package is no use to you.

If you want GA4 data in Snowflake, these are your options.

  1. Stream to BQ, transform data to a Snowflake-friendly format, export to Snowflake via ETL
  2. Stream to BQ, use this package to create flattened data marts, export to Snowflake vie ETL
  3. Use SS GTM and send events to GA4 and Snowflake in parallel
  4. Use the API, usually via an ETL platform to export data to Snowflake
  5. Quit GA4 entirely and use Snowplow to collect and send data to Snowflake

The disadvantage of 1 and 2 is that you need to maintain BQ and Snowflake infrastructure.

The disadvantage of 3 is that you need to use SS GTM and you may have to write a SS GTM Snowflake tag to send the data.

The disadvantage of 4 is that the API data is incomplete. It is subject to the various GA4 thresholding and cardinality issues.

The disadvantage of 4 is that you lose the GA4 integrations with Google Ads (but you can go that route and also send data to GA4).

Regardless, this package isn't suitable for Snowflake unless you are going route 2 and then there's no need to make changes to the package specific to Snowflake.

adamribaudo-velir commented 7 months ago

@dgitis Miron is on my team :) Snowflake now offers a native connection to BQ to import GA4 data which has received interest from our clients https://other-docs.snowflake.com/en/connectors/google/gard/gard-connector-about

Miron and I were exploring what it would take to allow the package to support both BQ and Snowflake. It's complicated, but theoretically doable. I'd like to leave this open as we explore.