[Feature] Add source definitions for Task, Contact, and other built-in object types

[al-the-x]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Currently, the salesforce_source package only defines the tables account, opportunity, user and user_role in its sources property config, and it does so quite verbosely! Thanks!

It would be quite helpful if it also defined some of the other common table objects that are already included with the Fivetran Salesforce connector for use in {{ source(..., ...) }} tags. There are a LOT of core objects in Salesforce, and having canonical source / model definitions would save us a lot of codegen.

Describe alternatives you've considered

We need at least the Contact and Task objects at this time, which we've worked around by defining name-only tables definitions in a salesforce.yml file containing Source Properties:

version: 2

sources:
  - name: salesforce
    tables:
      - name: task
      - name: contact

We can use generate_source from dbt-labs/codegen to generate source definitions for them directly from the data dumped into the warehouse, but they won't be as descriptive and will contain custom fields.

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?

No response

[fivetran-joemarkiewicz]

Hey @al-the-x this is a great idea for a future feature to the package, and one we have been pondering for quite some time 🤔. The main issue we constantly encounter is the high variability of Salesforce schemas across Fivetran customers. There are a large number of tables that a majority of customers do not leverage. Therefore, we most likely will never achieve the dream of including every Salesforce table within this package... But who knows!!

In the meantime, we are actually working on a pretty big update to our Salesforce package at this moment, and good news... both the task and contact sources will be included!! You can see more of what we are adding by referencing PR #30 and the work that @fivetran-reneeli is doing on this package and the transform package.

For the future, after we release the next update there will most likely still be sources that you will want to leverage that our package doesn't include. If you want to keep it all in one place still, you can always leverage the source override feature within dbt-core to include your sources outside of our package, but still have them documented with our package sources.

[al-the-x]

Thanks for the insight, Joe, and I'll definitely keep an eye on Renee's work in #30.

My workaround approach as documented only works so well, so maybe I'm doing it wrong?

When using overrides and specifying additional tables, I get compilation errors complaining about the models referencing them, as if they don't exist. Makes sense, since those tables aren't defined in the salesforce package, I suppose, and overriding doesn't necessarily create new tables just override existing ones.

When I don't use overrides, dbt run fails because we have a custom schema name: sfdc instead of salesforce for historical reasons. I have to provide a schema key and pull the schema from the var('salesforce_schema') like the package does. Makes sense, as I'm basically redefining the source named salesforce... but the other tables defined in salesforce_source and salesforce are available for reference, e.g. {{ source('salesforce', 'opportunity') }}.

We want all the Salesforce-related models to display together in documentation and appear under the same namespace. What am I doing wrong here?

[fivetran-joemarkiewicz]

@al-the-x this is very interesting as I have never tried to override the package source by adding new models (I have changed descriptions and various freshness tests), and see what you are referring to with the error.

It looks like the overrides feature does not allows for an override of the name component of the source and therefore doesn't allow you to expand upon this. At the moment I am not too sure what type of solution is available, but I will do some searching to see if anything comes up on my end!

[al-the-x]

Thanks, Joe. Appreciate your help and insight.

[fivetran-joemarkiewicz]

Hey @al-the-x I was able to do some digging yesterday and this morning on managing all of the source documentation in one place (for salesforce in this instance). Unfortunately, I wasn't able to find anything that can be supported natively within dbt-core for this to work as you are hoping. I did find a viable solution; however, it is not as elegant as I would have liked.

Essentially, forking this repo and using the fork in your packages.yml would most likely result in the outcome you are looking for. You can fork this repo and apply all you other custom changes to the src_salesforce.yml file. This way all the documentation can remain in one central location. You can then thankfully leverage GitHubs ability to fetch upstream changes to ensure you are getting any updates from my teams repo.

I know this is not the ideal form, but I believe it would result in your desired outcome.

[al-the-x]

Thanks for the extra digging, sir. We considered forking the package to add the source definitions that were missing, but the only way that makes sense is to contribute those changes back to the main project.

Since, as you've pointed out, those objects aren't used universally, and most folks customize the Contact object, as we have done, so I'm not sure how valuable those contributions would be. What are your thoughts?