Unioning Multiple ads Connectors

[steven91]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

If you have multiple facebook ads connectors in Fivetran, you can use this package on all of them simultaneously

Describe alternatives you've considered

-

Are you interested in contributing this feature?

• [ ] Yes.
• [ ] Yes, but I will need assistance and will schedule time during your office hours for guidance.
• [ ] No.

Anything else?

-

[elanfivetran]

Hey Steven, thanks for reaching out about this! I definitely understand the benefit of being able to utilize multiple connectors of the same source in our packages.

This is actually a feature that we have implemented on a select few models (for an example reference the Quickbooks dbt package). We do plan to add in the unioned schema feature to the rest of our Ad connectors eventually. Unfortunately I can not provide an exact time frame right now, but we will discuss this internally over the next couple weeks and provide you an update soon!

If you would like to expediate the process, you are more than welcome to create a PR to add this feature and we will assist you with getting any changes published.

[fivetran-catfritz]

Hi @steven91! To update you, I created a test branch with the ability to union multiple ad connectors together. If you are able to try it out, you can install it using the below code in your packages.yml in place of the normal facebook_ads lines.

- git: https://github.com/fivetran/dbt_facebook_ads.git
  revision: MagicBot/add-union-schema
  warn-unpinned: false

Further, below are the instructions from the README on how to set it up. Let us know if you have any questions about this as well. We look forward to your feedback!

The package will union all of the data together and pass the unioned table into the transformations. You will be able to see which source it came from in the source_relation column of each model. To use this functionality, you will need to set either the facebook_ads_union_schemas OR facebook_ads_union_databases variables (cannot do both) in your root dbt_project.yml file:

vars:
    facebook_ads_union_schemas: ['facebook_ads_usa','facebook_ads_canada'] # use this if the data is in different schemas/datasets of the same database/project
    facebook_ads_union_databases: ['facebook_ads_usa','facebook_ads_canada'] # use this if the data is in different databases/projects but uses the same schema name

Please be aware that the native source.yml connection set up in the package will not function when the union schema/database feature is utilized. Although the data will be correctly combined, you will not observe the sources linked to the package models in the Directed Acyclic Graph (DAG). This happens because the package includes only one defined source.yml.

To connect your multiple schema/database sources to the package models, follow the steps outlined in the Union Data Defined Sources Configuration section of the Fivetran Utils documentation for the union_data macro. This will ensure a proper configuration and correct visualization of connections in the DAG.