rcaddell / dbt_salesforce

Fivetran's Salesforce dbt package

Salesforce Modeling dbt Package (Docs)

📣 What does this dbt package do?

• Produces modeled tables that leverage Salesforce data from Fivetran's connector in the format described by this ERD and builds off the output of our Salesforce source package.

• This package enables users to:

  • Understand the performance of your opportunities
  • Drill into how the members of your sales team are performing
  • Have a daily summary of sales activities
  • Leverage an enhanced contact list
  • View more details about opportunity line items

This package also generates a comprehensive data dictionary of your source and modeled Salesforce data via the dbt docs site You can also refer to the table below for a detailed view of all models materialized by default within this package.

model	description
salesforce__manager_performance	Each record represents a manager, enriched with data about their team's pipeline, bookings, losses, and win percentages.
salesforce__owner_performance	Each record represents an individual member of the sales team, enriched with data about their pipeline, bookings, losses, and win percentages.
salesforce__sales_snapshot	A single row snapshot that provides various metrics about your sales funnel.
salesforce__opportunity_enhanced	Each record represents an opportunity, enriched with related data about the account and opportunity owner.
salesforce__contact_enhanced	Each record represents a contact with additional account and owner information.
salesforce__daily_activity	Each record represents a daily summary of the number of sales activities, for example tasks and opportunities closed.
salesforce__opportunity_line_item_enhanced	Each record represents a line item belonging to a certain opportunity, with additional product details.

🎯 How do I use the dbt package?

Step 1: Pre-Requisites

You will need to ensure you have the following before leveraging the dbt package.

• Connector: Have the Fivetran Salesforce connector syncing data into your warehouse.
• Database support: This package has been tested on BigQuery, Snowflake, Redshift, Databricks, and Postgres. Ensure you are using one of these supported databases.
  • If you are using Databricks you'll need to add the below to your dbt_project.yml.

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['spark_utils', 'dbt_utils']

• dbt Version: This dbt package requires you have a functional dbt project that utilizes a dbt version within the respective range >=1.0.0, <2.0.0.

Step 2: Installing the Package

Include the following salesforce package version in your packages.yml

Check dbt Hub for the latest installation instructions, or read the dbt docs for more information on installing packages.

packages:
  - package: fivetran/salesforce
    version: [">=0.7.0", "<0.8.0"]

Step 3: Configure Your Variables

Database and Schema Variables

By default, this package will run using your target database and the salesforce schema. If this is not where your Salesforce data is, add the following configuration to your root dbt_project.yml file:

vars:
    salesforce_database: your_database_name    
    salesforce_schema: your_schema_name

Disabling Models

It is possible that your Salesforce connector does not sync every table that this package expects. If your syncs exclude certain tables, it is because you either don't use that functionality in Salesforce or actively excluded some tables from your syncs.

To disable the corresponding functionality in this package, you must add the corresponding variable(s) to your dbt_project.yml, which are listed below. By default, that is if none of these variables are added, all variables are assumed to be true. Add variables only for the tables you would like to disable:

vars:
  salesforce__user_role_enabled: false # Disable if you do not have the user_role table
  salesforce__lead_enabled: false # Disable if you do not have the lead table
  salesforce__event_enabled: false # Disable if you do not have the event table
  salesforce__task_enabled: false # Disable if you do not have the task table
  salesforce__opportunity_line_item_enabled: false # Disable if you do not have the opportunity_line_item table
  salesforce__order_enabled: false # Disable if you do not have the order table
  salesforce__product_2_enabled: false # Disable if you do not have the product_2 table

The corresponding metrics from the disabled tables will not populate in the downstream models.

(Optional) Step 4: Additional Configurations

Change the Source Table References

Source tables are referenced using default names. If an individual source table has a different name than expected, provide the name of the table as it appears in your warehouse to the respective variable:

IMPORTANT: See the package's source dbt_project.yml variable declarations to see the expected names.

vars:
    <package_name>__<default_source_table_name>_identifier: your_table_name

Salesforce History Mode

If you have Salesforce History Mode enabled for your connector, in your dbt_project.yml, you will want to add and set the desired using_[table]_history_mode_active_records variable(s) as true to filter for only active records as the package is designed for non-historical data. These variables are disabled by default.

vars:
  using_account_history_mode_active_records: true      # false by default. Only use if you have history mode enabled.
  using_opportunity_history_mode_active_records: true  # false by default. Only use if you have history mode enabled.
  using_user_role_history_mode_active_records: true    # false by default. Only use if you have history mode enabled.
  using_user_history_mode_active_records: true         # false by default. Only use if you have history mode enabled.
  using_contact_history_mode_active_records: true      # false by default. Only use if you have history mode enabled.
  using_lead_history_mode_active_records: true         # false by default. Only use if you have history mode enabled.
  using_task_history_mode_active_records: true         # false by default. Only use if you have history mode enabled.
  using_event_history_mode_active_records: true        # false by default. Only use if you have history mode enabled.
  using_product_2_history_mode_active_records: true    # false by default. Only use if you have history mode enabled.
  using_order_history_mode_active_records: true        # false by default. Only use if you have history mode enabled.
  using_opportunity_line_item_history_mode_active_records: true       # false by default. Only use if you have history mode enabled.

Change the Build Schema

By default, this package builds the GitHub staging models within a schema titled (<target_schema> + _stg_salesforce) in your target database. If this is not where you would like your GitHub staging data to be written to, add the following configuration to your root dbt_project.yml file:

models:
    salesforce_source:
      +schema: my_new_schema_name # leave blank for just the target_schema

Adding Passthrough Columns

This package allows users to add additional columns to the opportunity enhanced model and contact enhanced model by using the below variables in you dbt_project.yml file. For the opportunity enhanced model, columns passed through in opportunity_enhanced_pass_through_columns must also be present in the upstream source opportunity, account, user, or user_role table. If you want to include a column from the user table, you must specify if you want it to be a field related to the opportunity_manager or opportunity_owner.

Additionally, you may add additional columns to the staging models. For example, for passing columns to stg_salesforce__product_2 you would need to configure product_2_pass_through_columns.

vars:
  opportunity_enhanced_pass_through_columns: [account_custom_field_1, my_opp_custom_field, user_role_custom_field_1, opportunity_manager.user_custom_column_1]
  account_pass_through_columns: [account_custom_field_1, account_custom_field_2]
  user_pass_through_columns: [user_custom_column_1,user_custom_column_2]
  contact_pass_through_columns: [contact_custom_field_1, contact_custom_field_2]
  opportunity_pass_through_columns: [my_opp_custom_field]
  lead_pass_through_columns: [lead_custom_field_1, lead_custom_field_2]
  task_pass_through_columns: [task_custom_field_1, task_custom_field_2]
  event_pass_through_columns: [event_custom_field_1, event_custom_field_2]
  product_2_pass_through_columns: [product_2_custom_field_1, product_2_custom_field_2]
  order_pass_through_columns: [order_custom_field_1, order_custom_field_2]
  opportunity_line_item_pass_through_columns: [opportunity_line_item_custom_field_1, opportunity_line_item_custom_field_2]
  user_role_pass_through_columns: [user_role_custom_field_1, user_role_custom_field_2]

(Optional) Step 5: Adding Formula Fields as Pass Through Columns

Adding Formula Fields as Pass Through Columns

The source tables Fivetran syncs do not include formula fields. If your company uses them, you can generate them by referring to the Salesforce Formula Utils package. To pass through the fields, add the following configuration. We recommend confirming your formula field models successfully populate before integrating with the Salesforce package.

Include the following within your packages.yml file:

packages:

  - package: fivetran/salesforce_formula_utils
    version: [">=0.6.0", "<0.7.0"]

Include the following within your dbt_project.yml file:

# Using the opportunity source table as example, update the opportunity variable to reference your newly created model that contains the formula fields:
  opportunity: "{{ ref('my_opportunity_formula_table') }}"

# In addition, add the desired field names as pass through columns
  opportunity_pass_through_columns: ['formula_field_1','formula_field_2']

(Optional) Step 6: Orchestrate your models with Fivetran Transformations for dbt Core™

Fivetran offers the ability for you to orchestrate your dbt project through the Fivetran Transformations for dbt Core™ product. Refer to the linked docs for more information on how to setup your project for orchestration through Fivetran.

🔍 Does this package have dependencies?

This dbt package is dependent on the following dbt packages. For more information on the below packages, refer to the dbt hub site.

If you have any of these dependent packages in your own packages.yml I highly recommend you remove them to ensure there are no package version conflicts.

packages:
    - package: fivetran/salesforce_source
      version: [">=0.5.0", "<0.6.0"]
    - package: fivetran/fivetran_utils
      version: [">=0.3.0", "<0.4.0"]
    - package: dbt-labs/dbt_utils
      version: [">=0.8.0", "<0.9.0"]

🙌 How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

These dbt packages are developed by a small team of analytics engineers at Fivetran. However, the packages are made better by community contributions!

We highly encourage and welcome contributions to this package. Check out this post on the best workflow for contributing to a package!

🏪 Are there any resources available?

• If you encounter any questions or want to reach out for help, please refer to the GitHub Issue section to find the right avenue of support for you.
• If you would like to provide feedback to the dbt package team at Fivetran, or would like to request a future dbt package to be developed, then feel free to fill out our Feedback Form.
• Have questions or want to just say hi? Book a time during our office hours here or send us an email at [email protected].