Unlock Salesforce CDP Data with DBeaver
DBeaver is a free multiplatform database tool for developers, database administrators, analysts, and anyone who needs to work with databases. DBeaver supports SQL query access to all Salesforce CDP objects including:
- Data Model Objects (DMOs) — for harmonized groupings of data in CDP.
- Data Lake Objects (DLOs) — for data containers data brought into CDP.
- Calculated Insights — for multidimensional metrics.
DBeaver provides a single universal interface for all CDP data, which helps you extract useful information, patterns, and trends from your CDP data model.
Key use cases
The most important use case for DBeaver is data validation. You can create SQL queries to interrogate your CDP data and create complex joins across DMOs and DLOs. For example, to verify the count of the number of records that came in via different data streams and populated your Individual objects you can use the following query:
You can also create queries for Calculated Insights. This enables you to validate not just the syntax but also the logic and the data being returned by your queries before you create them in the CDP Calculated Insights user interface. For some sample SQL queries that you can execute in DBeaver, check out this GitHub repository.
Another common use case is for quality assurance, where you can query the data directly and perform validations as needed. You can also run queries to see how your Individual data has been harmonized into your unified data.
Setting up DBeaver to connect to your CDP instance
Follow these steps to set up DBeaver and connect it to your CDP instance.
Step 1: Install the required components
Download and install DBeaver from dbeaver.io. To get started querying data, you can use the free community edition.
Step 2: Create a connected app
- If your org uses multi-factor authentication (which we recommend) or single sign-on (SSO) you will need to use key-pair authentication to connect. Login to Salesforce as an admin and follow the instructions on this GitHub repository to create a connected app, generate a key pair, and configure a certificate.
- For sandboxes and other non-MFA Environments, follow the instructions here to create your connected app.
Step 3: Connect to CDP
In DBeaver, select Database and choose New Database Connection. Then search for and select CDP and click Next.
When DBeaver gives you the option to automatically download the Salesforce JDBC driver, click Download.
Step 4: Authenticate
- If you’re connecting DBeaver to an org that is not using MFA, type your username and password and click Test Connection.
- For orgs that are using MFA:
Type the username that you used while setting up the private key and secret in Step 2.
Since you’ll be using a private key to authenticate, click Edit Driver Settings and then select Allow Empty Password.
Navigate to the Driver Properties tab, select User Properties, and then right-click and select Add New Property.
- Add a new property called “clientid”. Copy the ClientID generated during setup and paste it here.
- Add a new property called “privatekey”. Copy the private key generated during setup, remove all new line characters and then paste it here.
Finally, click Test Connection and verify that you’re able to connect successfully.
Conclusion
Once you are able to connect successfully, you will be able to view all CDP data objects in the data explorer as well as write queries to access CDP data. While there are other ways to query your data (for example via the Query API), DBeaver provides a quick and easy way for both data analysts and developers to access CDP data for validation, QA, and other use cases.