[Feature] "Source Identifier Override" as a pattern (not just individual sources)

AaronCoquet-Easypark commented 1 year ago

Is there an existing feature request for this?

[X] I have searched the existing issues

Describe the Feature

Currently (v.0.2.2), if one wants to override the default source table name for a Fivetran-synced ASA table, one needs to set a variable in their dbt_proect.yml like so:

vars:
    apple_search_ads_<default_source_table_name>_identifier: your_table_name

(Taken from the Readme). However, if one is changing ONE table's name, it's likely because they are changing ALL of the table names (IE, to match a naming convention). To this end, it would be much easier (and more resilient to change) if there were an option to set a prefix and/or suffix variable for these overrides. Something like:

vars:
  apple_search_ads_source_identifier_prefix: <prefix>
  apple_search_ads_source_identifier_suffix: <suffix>

This would allow for the following logic to be used when finding tables:

if table-specific identifier override is set, use it. Else...
if prefix or suffix is set, use them in combination with the default table name. Else...

use the default table name Which could be implemented like this:


# src_apple_search_ads.yml
# Expanded for clarity; this could just as easily be a single (long) line

name: ad_level_report identifier: | "{{ var( 'apple_search_ads_ad_report_identifier', var( 'apple_search_ads_source_identifier_prefix', '' ) ) }}ad_level_report{{ var( 'apple_search_ads_source_identifier_suffix', '' ) }}"

Describe alternatives you've considered

The same can be achieved by setting every source override individually, which is what I've currently done.

Are you interested in contributing this feature?

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

Anything else?

Scope of this feature will probably change if/when #46 is sorted, since the renaming (in my case, at least) is due to unioning multiple models.

fivetran-jamie commented 1 year ago

Hi there @AaronCoquet-Easypark, thanks for taking the time to open this feature request!

You mentioned that you're renaming tables in order to union -- if/when the package will union them for you, would you still need to rename any singular tables? If so, this will be a little tricky...if not, https://github.com/fivetran/dbt_apple_search_ads_source/issues/46 should be merged in 2-3 weeks

Another alternative for the time being could be adjusting how you rename the unioned tables. If you were to keep the table names and store them in a separate apple_search_ads_unioned schema, you could provide that schema to the package's apple_search_ads_schema var

AaronCoquet-Easypark commented 1 year ago

My current process is this: Setup:

2 ASA accounts, each with one API key
1 Fivetran ASA connector per account
1 Snowflake destination
2 dbt projects; one registered with fivetran (dbt-fivetran), one not (dbt)

Process:

Fivetran syncs the ASA accounts each to a dedicated schema
Both schemas are referenced in the dbt-fivetran project
- The project consists of the tables that were created by either (or both) ASA connector being unioned together "SELECT * FROM ASA_one.table_name UNION ALL SELECT * FROM ASA_two.table_name" > ASA.table_name_unioned (_unioned suffix because dbt doesn't like having multiple tables with the same name in the project)
- I manually created missing tables in the "other" schema to make this work
The output for dbt-fivetran (unioned tables) is placed in the apple_search_ads schema
in the dbt project, the fivetran/apple_search_ads package is pointed at the unioned tables

Notes:

I have no hard requirement to keep the data separate (as should be obvious), so if there's an easier way to manage this, I'm happy to hear it.

fivetran-jamie commented 1 year ago

@AaronCoquet-Easypark Ah I see. Then I think that the package will do this for you pending the merge of https://github.com/fivetran/dbt_apple_search_ads_source/pull/45

If you'd like to try it out beforehand here's the branch info!

# packages.yml
packages:
  - git: https://github.com/fivetran/dbt_apple_search_ads_source.git
    revision: MagicBot/add-union-schema 
    warn-unpinned: false

A branch of the same name also exists in the transform package if you'd like to try it out with the downstream end models. The transform branch points to the working branch of the source package, so you'd just need this

# packages.yml
packages:
  - git: https://github.com/fivetran/dbt_apple_search_ads.git
    revision: MagicBot/add-union-schema 
    warn-unpinned: false

fivetran-jamie commented 9 months ago

the above feature has been added and merged (to all the Ad Reporting packages as well), so i will close out this issue for now, but please feel free to reopen if the current solution doesn't work for your use case!

fivetran / dbt_apple_search_ads_source