0

I am using Fivetran for Facebook Ads and Google Ads connectors.

In the case of Facebook Ads, the tables that record historical changes in the dimensions (ad_history, ad_set_history, campaign_history and account_history) have a single temporary column (updated_time) that records the timestamp of the modification of some attribute of that dimension.

It seems to me a CDC solution instead of a formal SCD type 2 implementation.

On the other hand, we use tables with metrics segmented by days. Those tables have a few attributes of those dimensions (or hierarchical levels).

When I want to perform a LEFT JOIN between the metrics table (or FACT TABLE) and the dimension table, I run into a problem.

If the (historical) dimension tables responded to a Slowly Changing Dimension type 2 configuration, I would perform the following Query:

SELECT * FROM facebook_ads.facebook_metrics AS a

LEFT JOIN facebook_ads.ad_history AS b

ON a.ad_id = b.ad_id

AND a.date BETWEEN b.updated_time AND b.next_update_time

But in my historical tables I've got only 'updated_time' column

Can anyone help me build a query or a transformation that solves this problem

Thank you very much.

Juan Ignacio Solerno
  • 1
  • 1
  • Please update your question with table definitions (DDL), sample data and the result you want to achieve based on your sample data - all as editable text. This can be a minimal reproducible example - it doesn't need to include, for example, every column on every table if a subset of columns is enough to illustrate the issue. – NickW Apr 12 '23 at 16:34

0 Answers0