Quickstart for dbt and Salesforce Data Cloud

Back to guides

Salesforce

Data Cloud

platform

Quickstart

Beginner

Menu

Introduction

In this quickstart guide, you'll learn how to use dbt with Salesforce Data Cloud using the new dbt Salesforce Data Cloud adapter (powered by dbt Fusion). You'll learn how to:

• Install Fusion and the Salesforce adapter
• Connect dbt or your local dbt environment to Salesforce Data Cloud
• Run and test dbt models using Data Cloud objects
• Understand key limitations and Data Cloud–specific model requirements

Note

This adapter is currently in Alpha. It is not production-ready and should only be used in sandbox or test environments. Features, commands, and workflows are subject to change.

Prerequisites​

• You have a dbt account or a local dbt Fusion setup.
• You have access to a Salesforce Data Cloud instance with API access and credentials (OAuth and service token).
• You have a code editor such as VS Code (or Cursor) with permissions to install extensions.
• You have a connected Git provider with repository access.

You’ll need the following Salesforce credentials:

• Client ID (Consumer Key) from your connected app secrets
• Private Key Path (the full file path to your downloaded server.key)
• Salesforce username

Related content

• Learn more with dbt Learn courses
• CI jobs
• Deploy jobs
• Job notifications
• Source freshness

Install Fusion

The Salesforce adapter is available through dbt Fusion, a next-generation dbt binary. Follow the installation steps for your platform.

macOS & Linux

curl -fsSL https://public.cdn.getdbt.com/fs/install/install.sh | sh -s -- --update
exec $SHELL

Windows (Powershell)

irm https://public.cdn.getdbt.com/fs/install/install.ps1 | iex
Start-Process powershell

To verify Fusion installation run

dbtf --version

Installing Fusion automatically provides the Salesforce adapter and its ADBC driver.

Install the dbt Extension for VS Code

1. In VS Code, navigate to the Extensions tab.
2. Search for dbt and locate the extension published by dbtLabsInc or dbt Labs Inc.
3. Click Install.
4. Once installed, look for the dbt Extension label in your VS Code status bar.

• Hover over it to see diagnostic info.
• Register and activate the extension.

After activation, the extension will automatically download the correct dbt Language Server for your OS. You can learn more about how to use the dbt VS Code extension in the dbt Extension documentation .

Set up a dbt Project

1. Upload the provided dbt Salesforce Data Cloud example project (zip file) to your Git repository.
2. Clone the repository locally through VS Code.
3. Follow the instructions in the project’s README, starting from Step 3:

• Create a profiles.yml
• Confirm your connection
• Compile and execute the sample Jaffle Shop dbt project
• Run data tests to validate your models

You should now be able to run dbtf run and dbtf test successfully against your Salesforce Data Cloud instance.

Connection Configuration

Your profiles.yml file will look similar to the following:

salesforce_data_cloud:
  target: dev
  outputs:
    dev:
      type: salesforce_data_cloud
      method: oauth
      client_id: "<YOUR_CLIENT_ID>"
      private_key_path: "<YOUR_PATH>/server.key"
      user: "<YOUR_USERNAME>"
      database: "<YOUR_DATA_SPACE>"
      schema: "default"
      threads: 4

Then run:

dbtf debug

If successful, you should see:

Connection test: OK connection

Execute Your First dbt Run

1. Open your cloned dbt Salesforce project in VS Code.
2. Run the following command in the terminal:

dbtf run --static-analysis off

This disables static analysis (required due to current Data Cloud limitations). If successful, your models will be built in Salesforce Data Cloud as Data Lake Objects (DLOs).

Known Limitations

Feature	Timeline	Notes
Running dbt run twice	Coming soon	Due to Data Cloud’s architecture, rerunning the same model is not currently supported. Manually delete dependencies before rerunning.
Materializations supported	—	Only table materializations are supported. Incremental, snapshot, view, and ephemeral are not.
Static Analysis	Ongoing	Must include --static-analysis off in every dbt command. Impacts column-level lineage and VS Code dbt buttons.
dbt Seeds	Coming soon	Not yet supported.
dbt Docs	N/A	Not currently available in Fusion for Data Cloud.
Arbitrary Queries	Not on roadmap	All queries must be tied to defined dbt sources.
SELECT *	Coming	Standard metadata queries currently fail due to injected system columns.
Multi-dataspace writes	Coming soon	Future support for writing to multiple dataspaces.
Configurable timeout	Coming soon	Defaults to 5 minutes; not configurable yet.
Canceling dbt runs	Coming soon	Future support planned.

VS Code Callouts

• The Problems table in VS Code may show namespace errors due to Data Cloud’s lack of schema support.
• Models must end with __dll. If omitted, it is appended automatically (for example, model_name → model_name__dll).
• Columns must end with __c. Omitting this suffix causes syntax errors.
• Model names cannot include double underscores (__) except before __dll.
• For category=profile, all models must include:

config:
  category: profile
  primary_key: id

Example: Simple Model in Salesforce Data Cloud

Create file in models folder

select
    id__c as customer_id__c,
    first_name__c,
    last_name__c
from {{ source('jaffle_shop', 'customers') }}

Run the model:

dbtf run --models customers__dll --static-analysis off

You should see the Data Lake Object created successfully in your Salesforce Data Cloud environment.

FAQs

How do I run one model at a time?

To run one model, use the --select flag (or -s flag), followed by the name of the model:

$ dbt run --select customers

Check out the model selection syntax documentation for more operators and examples.

Do ref-able resource names need to be unique?

Within one project: yes! To build dependencies between resources (such as models, seeds, and snapshots), you need to use the ref function, and pass in the resource name as an argument. dbt uses that resource name to uniquely resolve the ref to a specific resource. As a result, these resource names need to be unique, even if they are in distinct folders.

A resource in one project can have the same name as a resource in another project (installed as a dependency). dbt uses the project name to uniquely identify each resource. We call this "namespacing." If you ref a resource with a duplicated name, it will resolve to the resource within the same namespace (package or project), or raise an error because of an ambiguous reference. Use two-argument ref to disambiguate references by specifying the namespace.

Those resource will still need to land in distinct locations in the data warehouse. Read the docs on custom aliases and custom schemas for details on how to achieve this.

As I create more models, how should I keep my project organized? What should I name my models?

There's no one best way to structure a project! Every organization is unique.

If you're just getting started, check out how we (dbt Labs) structure our dbt projects.

Add tests to your models

Adding data tests to a project helps validate that your models are working correctly.

To add data tests to your project:

1. Create a new YAML file in the models directory, named models/schema.yml

2. Add the following contents to the file:

   models/schema.yml

   version: 2
   
   models:
     - name: customers
       columns:
         - name: customer_id
           data_tests:
             - unique
             - not_null
   
     - name: stg_customers
       columns:
         - name: customer_id
           data_tests:
             - unique
             - not_null
   
     - name: stg_orders
       columns:
         - name: order_id
           data_tests:
             - unique
             - not_null
         - name: status
           data_tests:
             - accepted_values:
                 arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
                   values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
         - name: customer_id
           data_tests:
             - not_null
             - relationships:
                 arguments:
                   to: ref('stg_customers')
                   field: customer_id
   

3. Run dbt test, and confirm that all your tests passed.

When you run dbt test, dbt iterates through your YAML files, and constructs a query for each test. Each query will return the number of records that fail the test. If this number is 0, then the test is successful.

FAQs

What tests are available for me to use in dbt? Can I add my own custom tests?

Out of the box, dbt ships with the following data tests:

• unique
• not_null
• accepted_values
• relationships (i.e. referential integrity)

You can also write your own custom schema data tests.

Some additional custom schema tests have been open-sourced in the dbt-utils package, check out the docs on packages to learn how to make these tests available in your project.

Note that although you can't document data tests as of yet, we recommend checking out this dbt Core discussion where the dbt community shares ideas.

How do I test one model at a time?

Running tests on one model looks very similar to running a model: use the --select flag (or -s flag), followed by the name of the model:

dbt test --select customers

Check out the model selection syntax documentation for full syntax, and test selection examples in particular.

One of my tests failed, how can I debug it?

To debug a failing test, find the SQL that dbt ran by:

• dbt:

  • Within the test output, click on the failed test, and then select "Details".

• dbt Core:

  • Open the file path returned as part of the error message.
  • Navigate to the target/compiled/schema_tests directory for all compiled test queries.

Copy the SQL into a query editor (in dbt, you can paste it into a new Statement), and run the query to find the records that failed.

Does my test file need to be named `schema.yml`?

No! You can name this file whatever you want (including whatever_you_want.yml), so long as:

• The file is in your models/ directory¹
• The file has .yml extension

Check out the docs for more information.

¹If you're declaring properties for seeds, snapshots, or macros, you can also place this file in the related directory — seeds/, snapshots/ and macros/ respectively.

Why do model and source YAML files always start with `version: 2`?

Once upon a time, the structure of these .yml files was very different (s/o to anyone who was using dbt back then!). Adding version: 2 allowed us to make this structure more extensible.

From dbt Core v1.5, the top-level version: key is optional in all resource YAML files. If present, only version: 2 is supported.

Also starting in v1.5, both the config-version: 2 and the top-level version: key in the dbt_project.yml are optional.

Resource YAML files do not currently require this config. We only support version: 2 if it's specified. Although we do not expect to update YAML files to version: 3 soon, having this config will make it easier for us to introduce new structures in the future

What data tests should I add to my project?

We recommend that every model has a data test on a primary key, that is, a column that is unique and not_null.

We also recommend that you test any assumptions on your source data. For example, if you believe that your payments can only be one of three payment methods, you should test that assumption regularly — a new payment method may introduce logic errors in your SQL.

In advanced dbt projects, we recommend using sources and running these source data-integrity tests against the sources rather than models.

When should I run my data tests?

You should run your data tests whenever you are writing new code (to ensure you haven't broken any existing models by changing SQL), and whenever you run your transformations in production (to ensure that your assumptions about your source data are still valid).

Document your models

Adding documentation to your project allows you to describe your models in rich detail, and share that information with your team. Here, we're going to add some basic documentation to our project.

1. Update your models/schema.yml file to include some descriptions, such as those below.

   models/schema.yml

   version: 2
   
   models:
     - name: customers
       description: One record per customer
       columns:
         - name: customer_id
           description: Primary key
           data_tests:
             - unique
             - not_null
         - name: first_order_date
           description: NULL when a customer has not yet placed an order.
   
     - name: stg_customers
       description: This model cleans up customer data
       columns:
         - name: customer_id
           description: Primary key
           data_tests:
             - unique
             - not_null
   
     - name: stg_orders
       description: This model cleans up order data
       columns:
         - name: order_id
           description: Primary key
           data_tests:
             - unique
             - not_null
         - name: status
           data_tests:
             - accepted_values:
                 arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
                   values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
         - name: customer_id
           data_tests:
             - not_null
             - relationships:
                 arguments:
                   to: ref('stg_customers')
                   field: customer_id

2. Run dbt docs generate to generate the documentation for your project. dbt introspects your project and your warehouse to generate a JSON file with rich documentation about your project.

3. Click the book icon in the Develop interface to launch documentation in a new tab.

FAQs

How do I write long-form explanations in my descriptions?

If you need more than a sentence to explain a model, you can:

1. Split your description over multiple lines using >. Interior line breaks are removed and Markdown can be used. This method is recommended for simple, single-paragraph descriptions:

  version: 2

  models:
  - name: customers
    description: >
      Lorem ipsum **dolor** sit amet, consectetur adipisicing elit, sed do eiusmod
      tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
      quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
      consequat.

2. Split your description over multiple lines using |. Interior line breaks are maintained and Markdown can be used. This method is recommended for more complex descriptions:

  version: 2

  models:
  - name: customers
    description: |
      ### Lorem ipsum

      * dolor sit amet, consectetur adipisicing elit, sed do eiusmod
      * tempor incididunt ut labore et dolore magna aliqua.

3. Use a docs block to write the description in a separate Markdown file.

How do I access documentation in dbt Catalog?

If you're using dbt to deploy your project and have a Starter, Enterprise, or Enterprise+ plan, you can use Catalog to view your project's resources (such as models, tests, and metrics) and their lineage to gain a better understanding of its latest production state.

Access Catalog in dbt by clicking the Explore link in the navigation. You can have up to 5 read-only users access the documentation for your project.

dbt developer plan and dbt Core users can use dbt Docs, which generates basic documentation but it doesn't offer the same speed, metadata, or visibility as Catalog.

Commit your changes

Now that you've built your customer model, you need to commit the changes you made to the project so that the repository has your latest code.

If you edited directly in the protected primary branch:

1. Click the Commit and sync git button. This action prepares your changes for commit.
2. A modal titled Commit to a new branch will appear.
3. In the modal window, name your new branch add-customers-model. This branches off from your primary branch with your new changes.
4. Add a commit message, such as "Add customers model, tests, docs" and and commit your changes.
5. Click Merge this branch to main to add these changes to the main branch on your repo.

If you created a new branch before editing:

1. Since you already branched out of the primary protected branch, go to Version Control on the left.
2. Click Commit and sync to add a message.
3. Add a commit message, such as "Add customers model, tests, docs."
4. Click Merge this branch to main to add these changes to the main branch on your repo.

Deploy dbt

Use dbt's Scheduler to deploy your production jobs confidently and build observability into your processes. You'll learn to create a deployment environment and run a job in the following steps.

Create a deployment environment

1. From the main menu, go to Orchestration > Environments.
2. Click Create environment.
3. In the Name field, write the name of your deployment environment. For example, "Production."
4. In the dbt Version field, select the latest version from the dropdown.
5. Under Deployment connection, enter the name of the dataset you want to use as the target, such as "Analytics". This will allow dbt to build and work with that dataset. For some data warehouses, the target dataset may be referred to as a "schema".
6. Click Save.

Create and run a job

Jobs are a set of dbt commands that you want to run on a schedule. For example, dbt build.

As the jaffle_shop business gains more customers, and those customers create more orders, you will see more records added to your source data. Because you materialized the customers model as a table, you'll need to periodically rebuild your table to ensure that the data stays up-to-date. This update will happen when you run a job.

1. After creating your deployment environment, you should be directed to the page for a new environment. If not, select Orchestration from the main menu, then click Jobs.
2. Click Create job > Deploy job.
3. Provide a job name (for example, "Production run") and select the environment you just created.
4. Scroll down to the Execution settings section.
5. Under Commands, add this command as part of your job if you don't see it:
   • dbt build
6. Select the Generate docs on run option to automatically generate updated project docs each time your job runs.
7. For this exercise, do not set a schedule for your project to run — while your organization's project should run regularly, there's no need to run this example project on a schedule. Scheduling a job is sometimes referred to as deploying a project.
8. Click Save, then click Run now to run your job.
9. Click the run and watch its progress under Run summary.
10. Once the run is complete, click View Documentation to see the docs for your project.

Congratulations 🎉! You've just deployed your first dbt project!

FAQs

What happens if one of my runs fails?

If you're using dbt, we recommend setting up email and Slack notifications (Account Settings > Notifications) for any failed runs. Then, debug these runs the same way you would debug any runs in development.

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.