search

fivetran / dbt_ad_reporting

Fivetran's ad reporting dbt package. Combine your Facebook, Google, Pinterest, LinkedIn, Twitter, Snapchat, Microsoft, TikTok, Reddit, Amazon, and Apple Search advertising metrics using this package.
https://fivetran.github.io/dbt_ad_reporting/#!/overview
Apache License 2.0
142 stars 55 forks source link

incremental processing #10

Closed leinemann closed 3 years ago

leinemann commented 3 years ago

Hi there, I'm wondering what the thinking is on making this package incremental in nature. Is this a realistic endeavour? To what extend will it scale to re-compute the consolidated tables and views with every run? I'm wondering what the experiences are so far and if someone has operated this at scale? many thanks, Christoph.

kristin-bagnall commented 3 years ago

@leinemann thanks for the question. This package does not have any incremental logic built in, however I'm curious to understand your experience. A few questions for you:

  1. How long does a full run take you?
  2. Which ad sources are you using?
  3. How many records are in your final model?
  4. Which warehouse are you using?
leinemann commented 3 years ago

@fivetran-kristin we are using snowflake and our runs are currently brief, working on small datasets and we are using facebook, linkedin, googleads, bingads.
So my question was more of forward-looking nature. How will the model perform after 2 years worth of data and a significant marketing spend? What is the cost I'm accumulating in additional warehouse compute spend as opposed to only processing increments? I'm new to dbt and I was wondering what would be the level of effort of making the processing incremental, if that is even possible or whether there are certain elements that are difficult or can't be computed incrementally.

kristin-bagnall commented 3 years ago

@leinemann great to meet you last week. As discussed, here are some logs that will help you better understand the processing time this package can take in the future after you've spent significant amounts in marketing spend. This run is in BigQuery, but a data point nonetheless. Running the full package, without incremental materialization, is < 1 min.

As your company scales, let's revisit incorporating the incremental logic if you find that this package is taking a long time to run. Until then, I'll close this issue out. Thanks!

16:32:31 | Concurrency: 4 threads (target='bigquery')
16:32:31 | 
16:32:31 | 1 of 77 START view model dbt_kristin.stg_facebook_ads__creative_history_tmp [RUN]
16:32:31 | 2 of 77 START view model dbt_kristin.stg_facebook_ads__account_history_tmp [RUN]
16:32:31 | 3 of 77 START view model dbt_kristin.stg_facebook_ads__ad_history_tmp [RUN]
16:32:31 | 4 of 77 START view model dbt_kristin.stg_facebook_ads__ad_set_history_tmp [RUN]
16:32:32 | 2 of 77 OK created view model dbt_kristin.stg_facebook_ads__account_history_tmp [OK in 0.76s]
16:32:32 | 5 of 77 START view model dbt_kristin.stg_facebook_ads__basic_ad_tmp.. [RUN]
16:32:32 | 3 of 77 OK created view model dbt_kristin.stg_facebook_ads__ad_history_tmp [OK in 0.86s]
16:32:32 | 6 of 77 START view model dbt_kristin.stg_facebook_ads__campaign_history_tmp [RUN]
16:32:32 | 4 of 77 OK created view model dbt_kristin.stg_facebook_ads__ad_set_history_tmp [OK in 0.94s]
16:32:32 | 7 of 77 START view model dbt_kristin.stg_linkedin__account_history_tmp [RUN]
16:32:32 | 1 of 77 OK created view model dbt_kristin.stg_facebook_ads__creative_history_tmp [OK in 1.38s]
16:32:32 | 8 of 77 START view model dbt_kristin.stg_linkedin__ad_analytics_by_creative_tmp [RUN]
16:32:32 | 5 of 77 OK created view model dbt_kristin.stg_facebook_ads__basic_ad_tmp [OK in 0.70s]
16:32:32 | 9 of 77 START view model dbt_kristin.stg_linkedin__campaign_group_history_tmp [RUN]
16:32:33 | 7 of 77 OK created view model dbt_kristin.stg_linkedin__account_history_tmp [OK in 0.63s]
16:32:33 | 10 of 77 START view model dbt_kristin.stg_linkedin__campaign_history_tmp [RUN]
16:32:33 | 6 of 77 OK created view model dbt_kristin.stg_facebook_ads__campaign_history_tmp [OK in 0.91s]
16:32:33 | 11 of 77 START view model dbt_kristin.stg_linkedin__creative_history_tmp [RUN]
16:32:33 | 8 of 77 OK created view model dbt_kristin.stg_linkedin__ad_analytics_by_creative_tmp [OK in 0.63s]
16:32:33 | 12 of 77 START table model dbt_kristin.stg_microsoft_ads__account_history [RUN]
16:32:33 | 9 of 77 OK created view model dbt_kristin.stg_linkedin__campaign_group_history_tmp [OK in 0.60s]
16:32:33 | 13 of 77 START table model dbt_kristin.stg_microsoft_ads__ad_group_history [RUN]
16:32:33 | 11 of 77 OK created view model dbt_kristin.stg_linkedin__creative_history_tmp [OK in 0.52s]
16:32:33 | 14 of 77 START table model dbt_kristin.stg_microsoft_ads__ad_history. [RUN]
16:32:33 | 10 of 77 OK created view model dbt_kristin.stg_linkedin__campaign_history_tmp [OK in 0.84s]
16:32:33 | 15 of 77 START table model dbt_kristin.stg_microsoft_ads__ad_performance_daily_report [RUN]
16:32:35 | 12 of 77 OK created table model dbt_kristin.stg_microsoft_ads__account_history [CREATE TABLE (1.0 rows, 26.0 Bytes processed) in 2.29s]
16:32:35 | 16 of 77 START table model dbt_kristin.stg_microsoft_ads__campaign_history [RUN]
16:32:35 | 13 of 77 OK created table model dbt_kristin.stg_microsoft_ads__ad_group_history [CREATE TABLE (90.0 rows, 3.3 KB processed) in 2.29s]
16:32:35 | 17 of 77 START view model dbt_kristin.stg_twitter_ads__account_history_tmp [RUN]
16:32:36 | 14 of 77 OK created table model dbt_kristin.stg_microsoft_ads__ad_history [CREATE TABLE (309.0 rows, 14.9 KB processed) in 2.46s]
16:32:36 | 18 of 77 START view model dbt_kristin.stg_twitter_ads__campaign_history_tmp [RUN]
16:32:36 | 15 of 77 OK created table model dbt_kristin.stg_microsoft_ads__ad_performance_daily_report [CREATE TABLE (17.7k rows, 1.2 MB processed) in 2.45s]
16:32:36 | 19 of 77 START view model dbt_kristin.stg_twitter_ads__line_item_history_tmp [RUN]
16:32:36 | 17 of 77 OK created view model dbt_kristin.stg_twitter_ads__account_history_tmp [OK in 0.78s]
16:32:36 | 20 of 77 START view model dbt_kristin.stg_twitter_ads__promoted_tweet_history_tmp [RUN]
16:32:37 | 18 of 77 OK created view model dbt_kristin.stg_twitter_ads__campaign_history_tmp [OK in 0.94s]
16:32:37 | 21 of 77 START view model dbt_kristin.stg_twitter_ads__promoted_tweet_report_tmp [RUN]
16:32:37 | 19 of 77 OK created view model dbt_kristin.stg_twitter_ads__line_item_history_tmp [OK in 0.90s]
16:32:37 | 22 of 77 START view model dbt_kristin.stg_twitter_ads__tweet_url_tmp. [RUN]
16:32:37 | 20 of 77 OK created view model dbt_kristin.stg_twitter_ads__promoted_tweet_history_tmp [OK in 0.87s]
16:32:37 | 23 of 77 START view model dbt_kristin.stg_google_ads__final_url_performance_tmp [RUN]
16:32:37 | 21 of 77 OK created view model dbt_kristin.stg_twitter_ads__promoted_tweet_report_tmp [OK in 0.63s]
16:32:37 | 24 of 77 START view model dbt_kristin.stg_google_ads__click_performance_tmp [RUN]
16:32:37 | 22 of 77 OK created view model dbt_kristin.stg_twitter_ads__tweet_url_tmp [OK in 0.65s]
16:32:37 | 25 of 77 START view model dbt_kristin.stg_google_ads__criteria_performance_tmp [RUN]
16:32:38 | 16 of 77 OK created table model dbt_kristin.stg_microsoft_ads__campaign_history [CREATE TABLE (21.0 rows, 1.0 KB processed) in 2.28s]
16:32:38 | 26 of 77 START table model dbt_kristin.utils__facebook_ads__numbers.. [RUN]
16:32:38 | 23 of 77 OK created view model dbt_kristin.stg_google_ads__final_url_performance_tmp [OK in 0.84s]
16:32:38 | 27 of 77 START table model dbt_kristin.stg_facebook_ads__account_history [RUN]
16:32:38 | 25 of 77 OK created view model dbt_kristin.stg_google_ads__criteria_performance_tmp [OK in 0.53s]
16:32:38 | 28 of 77 START table model dbt_kristin.stg_facebook_ads__ad_history.. [RUN]
16:32:38 | 24 of 77 OK created view model dbt_kristin.stg_google_ads__click_performance_tmp [OK in 0.85s]
16:32:38 | 29 of 77 START table model dbt_kristin.stg_facebook_ads__ad_set_history [RUN]
16:32:41 | 28 of 77 OK created table model dbt_kristin.stg_facebook_ads__ad_history [CREATE TABLE (2.8k rows, 169.7 KB processed) in 2.59s]
16:32:41 | 30 of 77 START table model dbt_kristin.stg_facebook_ads__creative_history [RUN]
16:32:41 | 29 of 77 OK created table model dbt_kristin.stg_facebook_ads__ad_set_history [CREATE TABLE (2.4k rows, 106.1 KB processed) in 2.45s]
16:32:41 | 27 of 77 OK created table model dbt_kristin.stg_facebook_ads__account_history [CREATE TABLE (568.0 rows, 14.4 KB processed) in 2.88s]
16:32:41 | 26 of 77 OK created table model dbt_kristin.utils__facebook_ads__numbers [CREATE TABLE (1.0k rows, 0.0 Bytes processed) in 3.30s]
16:32:41 | 31 of 77 START table model dbt_kristin.stg_facebook_ads__basic_ad.... [RUN]
16:32:41 | 32 of 77 START view model dbt_kristin.stg_linkedin__account_history.. [RUN]
16:32:41 | 33 of 77 START table model dbt_kristin.stg_facebook_ads__campaign_history [RUN]
16:32:42 | 32 of 77 OK created view model dbt_kristin.stg_linkedin__account_history [OK in 0.73s]
16:32:42 | 34 of 77 START view model dbt_kristin.stg_linkedin__ad_analytics_by_creative [RUN]
16:32:43 | 34 of 77 OK created view model dbt_kristin.stg_linkedin__ad_analytics_by_creative [OK in 1.16s]
16:32:43 | 35 of 77 START view model dbt_kristin.stg_linkedin__campaign_group_history [RUN]
16:32:43 | 33 of 77 OK created table model dbt_kristin.stg_facebook_ads__campaign_history [CREATE TABLE (1.9k rows, 166.2 KB processed) in 2.26s]
16:32:43 | 36 of 77 START view model dbt_kristin.stg_linkedin__creative_history. [RUN]
16:32:44 | 31 of 77 OK created table model dbt_kristin.stg_facebook_ads__basic_ad [CREATE TABLE (10.8k rows, 553.4 KB processed) in 2.66s]
16:32:44 | 37 of 77 START view model dbt_kristin.stg_linkedin__campaign_history. [RUN]
16:32:44 | 35 of 77 OK created view model dbt_kristin.stg_linkedin__campaign_group_history [OK in 0.81s]
16:32:44 | 38 of 77 START view model dbt_kristin.stg_twitter_ads__account_history [RUN]
16:32:45 | 36 of 77 OK created view model dbt_kristin.stg_linkedin__creative_history [OK in 1.11s]
16:32:45 | 39 of 77 START view model dbt_kristin.stg_twitter_ads__campaign_history [RUN]
16:32:45 | 37 of 77 OK created view model dbt_kristin.stg_linkedin__campaign_history [OK in 1.09s]
16:32:45 | 40 of 77 START view model dbt_kristin.stg_twitter_ads__line_item_history [RUN]
16:32:45 | 38 of 77 OK created view model dbt_kristin.stg_twitter_ads__account_history [OK in 1.13s]
16:32:45 | 41 of 77 START view model dbt_kristin.stg_twitter_ads__promoted_tweet_history [RUN]
16:32:45 | 39 of 77 OK created view model dbt_kristin.stg_twitter_ads__campaign_history [OK in 0.74s]
16:32:45 | 42 of 77 START view model dbt_kristin.stg_twitter_ads__promoted_tweet_report [RUN]
16:32:46 | 40 of 77 OK created view model dbt_kristin.stg_twitter_ads__line_item_history [OK in 0.97s]
16:32:46 | 43 of 77 START view model dbt_kristin.stg_twitter_ads__tweet_url..... [RUN]
16:32:46 | 41 of 77 OK created view model dbt_kristin.stg_twitter_ads__promoted_tweet_history [OK in 0.92s]
16:32:46 | 44 of 77 START table model dbt_kristin.microsoft_ads__ad_adapter..... [RUN]
16:32:46 | 42 of 77 OK created view model dbt_kristin.stg_twitter_ads__promoted_tweet_report [OK in 1.04s]
16:32:46 | 45 of 77 START table model dbt_kristin.stg_google_ads__final_url_performance [RUN]
16:32:47 | 30 of 77 OK created table model dbt_kristin.stg_facebook_ads__creative_history [CREATE TABLE (383.4k rows, 112.0 MB processed) in 6.14s]
16:32:47 | 46 of 77 START table model dbt_kristin.stg_google_ads__criteria_performance [RUN]
16:32:47 | 43 of 77 OK created view model dbt_kristin.stg_twitter_ads__tweet_url [OK in 0.97s]
16:32:47 | 47 of 77 START table model dbt_kristin.stg_google_ads__click_performance [RUN]
16:32:49 | 44 of 77 OK created table model dbt_kristin.microsoft_ads__ad_adapter [CREATE TABLE (2.9k rows, 1.1 MB processed) in 2.73s]
16:32:49 | 48 of 77 START table model dbt_kristin.linkedin__ad_adapter.......... [RUN]
16:32:51 | 45 of 77 OK created table model dbt_kristin.stg_google_ads__final_url_performance [CREATE TABLE (96.9k rows, 19.4 MB processed) in 4.35s]
16:32:51 | 49 of 77 START table model dbt_kristin.stg_facebook_ads__url_tag..... [RUN]
16:32:52 | 46 of 77 OK created table model dbt_kristin.stg_google_ads__criteria_performance [CREATE TABLE (272.9k rows, 51.2 MB processed) in 5.43s]
16:32:52 | 50 of 77 START table model dbt_kristin.int__facebook_ads__carousel_media_prep [RUN]
16:32:54 | 49 of 77 OK created table model dbt_kristin.stg_facebook_ads__url_tag [CREATE TABLE (163.7k rows, 23.7 MB processed) in 3.59s]
16:32:54 | 51 of 77 START table model dbt_kristin.stg_facebook_ads__app_link.... [RUN]
16:32:54 | 50 of 77 OK created table model dbt_kristin.int__facebook_ads__carousel_media_prep [CREATE TABLE (0.0 rows, 56.5 MB processed) in 2.36s]
16:32:54 | 52 of 77 START table model dbt_kristin.stg_facebook_ads__creative_history_asset_feed_spec_link_url [RUN]
16:32:55 | 48 of 77 OK created table model dbt_kristin.linkedin__ad_adapter..... [CREATE TABLE (39.1k rows, 8.2 MB processed) in 6.49s]
16:32:55 | 53 of 77 START table model dbt_kristin.twitter__ad_adapter........... [RUN]
16:32:57 | 52 of 77 OK created table model dbt_kristin.stg_facebook_ads__creative_history_asset_feed_spec_link_url [CREATE TABLE (0.0 rows, 11.0 MB processed) in 2.36s]
16:32:57 | 54 of 77 START view model dbt_kristin.stg_microsoft_ads.............. [RUN]
16:32:57 | 47 of 77 OK created table model dbt_kristin.stg_google_ads__click_performance [CREATE TABLE (550.5k rows, 123.7 MB processed) in 9.98s]
16:32:57 | 55 of 77 START table model dbt_kristin.microsoft_ads__account_report. [RUN]
16:32:58 | 54 of 77 OK created view model dbt_kristin.stg_microsoft_ads......... [OK in 0.77s]
16:32:58 | 56 of 77 START table model dbt_kristin.microsoft_ads__ad_group_report [RUN]
16:32:59 | 53 of 77 OK created table model dbt_kristin.twitter__ad_adapter...... [CREATE TABLE (432.0 rows, 83.6 KB processed) in 3.56s]
16:32:59 | 57 of 77 START table model dbt_kristin.microsoft_ads__campaign_report [RUN]
16:32:59 | 55 of 77 OK created table model dbt_kristin.microsoft_ads__account_report [CREATE TABLE (238.0 rows, 143.7 KB processed) in 2.46s]
16:32:59 | 58 of 77 START table model dbt_kristin.google_ads__url_ad_adapter.... [RUN]
16:33:00 | 56 of 77 OK created table model dbt_kristin.microsoft_ads__ad_group_report [CREATE TABLE (2.9k rows, 304.0 KB processed) in 2.18s]
16:33:00 | 59 of 77 START table model dbt_kristin.google_ads__criteria_ad_adapter [RUN]
16:33:00 | 51 of 77 OK created table model dbt_kristin.stg_facebook_ads__app_link [CREATE TABLE (1.5m rows, 13.9 MB processed) in 6.15s]
16:33:00 | 60 of 77 START view model dbt_kristin.facebook_ads__creative_history_prep [RUN]
16:33:02 | 57 of 77 OK created table model dbt_kristin.microsoft_ads__campaign_report [CREATE TABLE (913.0 rows, 241.3 KB processed) in 2.47s]
16:33:02 | 60 of 77 OK created view model dbt_kristin.facebook_ads__creative_history_prep [OK in 0.83s]
16:33:02 | 61 of 77 START table model dbt_kristin.stg_facebook_ads__carousel_media [RUN]
16:33:02 | 62 of 77 START table model dbt_kristin.stg_facebook_ads__carousel_media_url_tags [RUN]
16:33:03 | 58 of 77 OK created table model dbt_kristin.google_ads__url_ad_adapter [CREATE TABLE (96.9k rows, 17.1 MB processed) in 3.59s]
16:33:03 | 63 of 77 START view model dbt_kristin.stg_linkedin_ads............... [RUN]
16:33:04 | 63 of 77 OK created view model dbt_kristin.stg_linkedin_ads.......... [OK in 0.68s]
16:33:04 | 64 of 77 START table model dbt_kristin.linkedin__account_ad_report... [RUN]
16:33:04 | 62 of 77 OK created table model dbt_kristin.stg_facebook_ads__carousel_media_url_tags [CREATE TABLE (0.0 rows, 0.0 Bytes processed) in 2.22s]
16:33:04 | 65 of 77 START table model dbt_kristin.linkedin__campaign_ad_report.. [RUN]
16:33:04 | 61 of 77 OK created table model dbt_kristin.stg_facebook_ads__carousel_media [CREATE TABLE (0.0 rows, 0.0 Bytes processed) in 2.23s]
16:33:04 | 66 of 77 START table model dbt_kristin.linkedin__campaign_group_ad_report [RUN]
16:33:05 | 59 of 77 OK created table model dbt_kristin.google_ads__criteria_ad_adapter [CREATE TABLE (262.8k rows, 34.7 MB processed) in 5.23s]
16:33:05 | 67 of 77 START table model dbt_kristin.google_ads__click_performance. [RUN]
16:33:06 | 64 of 77 OK created table model dbt_kristin.linkedin__account_ad_report [CREATE TABLE (960.0 rows, 2.0 MB processed) in 2.30s]
16:33:06 | 68 of 77 START view model dbt_kristin.stg_twitter_ads................ [RUN]
16:33:06 | 66 of 77 OK created table model dbt_kristin.linkedin__campaign_group_ad_report [CREATE TABLE (1.2k rows, 3.2 MB processed) in 2.36s]
16:33:06 | 69 of 77 START table model dbt_kristin.twitter__campaign_report...... [RUN]
16:33:07 | 68 of 77 OK created view model dbt_kristin.stg_twitter_ads........... [OK in 0.70s]
16:33:07 | 70 of 77 START table model dbt_kristin.twitter__line_item_report..... [RUN]
16:33:07 | 65 of 77 OK created table model dbt_kristin.linkedin__campaign_ad_report [CREATE TABLE (37.5k rows, 5.9 MB processed) in 2.98s]
16:33:07 | 71 of 77 START table model dbt_kristin.facebook_ads__ad_adapter...... [RUN]
16:33:08 | 69 of 77 OK created table model dbt_kristin.twitter__campaign_report. [CREATE TABLE (429.0 rows, 30.9 KB processed) in 2.17s]
16:33:08 | 72 of 77 START view model dbt_kristin.stg_google_ads................. [RUN]
16:33:09 | 70 of 77 OK created table model dbt_kristin.twitter__line_item_report [CREATE TABLE (429.0 rows, 46.7 KB processed) in 2.52s]
16:33:09 | 72 of 77 OK created view model dbt_kristin.stg_google_ads............ [OK in 0.87s]
16:33:11 | 71 of 77 OK created table model dbt_kristin.facebook_ads__ad_adapter. [CREATE TABLE (10.8k rows, 55.8 MB processed) in 4.09s]
16:33:11 | 73 of 77 START view model dbt_kristin.stg_facebook_ads............... [RUN]
16:33:11 | 74 of 77 START table model dbt_kristin.facebook_ads__account_report.. [RUN]
16:33:11 | 75 of 77 START table model dbt_kristin.facebook_ads__ad_set_report... [RUN]
16:33:11 | 67 of 77 OK created table model dbt_kristin.google_ads__click_performance [CREATE TABLE (550.5k rows, 55.9 MB processed) in 6.10s]
16:33:11 | 76 of 77 START table model dbt_kristin.facebook_ads__campaign_report. [RUN]
16:33:12 | 73 of 77 OK created view model dbt_kristin.stg_facebook_ads.......... [OK in 0.93s]
16:33:12 | 77 of 77 START table model dbt_kristin.ad_reporting.................. [RUN]
16:33:13 | 74 of 77 OK created table model dbt_kristin.facebook_ads__account_report [CREATE TABLE (231.0 rows, 500.4 KB processed) in 2.04s]
16:33:13 | 75 of 77 OK created table model dbt_kristin.facebook_ads__ad_set_report [CREATE TABLE (10.8k rows, 1.5 MB processed) in 2.37s]
16:33:13 | 76 of 77 OK created table model dbt_kristin.facebook_ads__campaign_report [CREATE TABLE (10.8k rows, 1.3 MB processed) in 2.23s]
16:33:16 | 77 of 77 OK created table model dbt_kristin.ad_reporting............. [CREATE TABLE (150.2k rows, 25.4 MB processed) in 4.16s]
16:33:16 | 
16:33:16 | Finished running 37 view models, 40 table models in 47.18s.

Completed successfully

Done. PASS=77 WARN=0 ERROR=0 SKIP=0 TOTAL=77