Hi team

I'm Juan Ignacio. I'm working with 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.

On the other hand, we use tables with metrics segmented by days. Those tables have a few attributes of those dimensions (or hierarchical levels). For example, ad_id, ad_status, and some more.

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

'next_update_time' is the column that does not exist and that would contain the information of how far that combination of attributes got. That next_update_time would be the updated_time of the next update of that dimension.

From what I understand, please correct me, Fivetran configures a Change Data Capture type solution, with a single time reference column.

I wanted to ask to assess the possibility of changing this type of approach to a classic SCD type 2 approach. Or if you could, in any case, help me build a query that solves this problem

From already thank you very much.