SQL Queries + Salesforce Dashboards: Answer Business Questions Faster

Share this article...

You can do almost anything with the Salesforce REST API, but it can be a heavy lift for a developer. You have to call API endpoints, unpack JSON results, and combine results from different endpoints.

Steampipe is an open-source tool that radically simplifies the process. It enables developers to:

  • Write simple SQL queries that ask common questions (e.g. “How many wins in Q2?” or “Which leads are hot?”) and populate database tables with the answers.
  • Create interactive dashboards that render query results as tables, charts, infocards, and more.
  • Combine results from Salesforce APIs with results from other APIs.

Here’s a short screencast that illustrates all of these capabilities:

In this guide we’ll cover how to set up Steampipe in your Salesforce, some basic SQL queries, and other Steampipe uses, as well as how to edit your dashboard.

How to Get Started

Here’s how to get started with Steampipe + Salesforce:

1. Install Steampipe

2. Install the Salesforce plugin:

steampipe plugin install salesforce

3. Create a Connected Application and configure the plugin to use its credentials

4. Run Steampipe:

steampipe query

5. Query the API. For example:

select * from salesforce_contact

The plugin provides these tables by default:


The Steampipe hub provides documentation and examples for each of these tables. But in fact there are many more Salesforce tables available.

The last item in the list, “salesforce_{object_name}” is a placeholder for other APIs that you can materialize by naming them in a configuration file. The “Today’s Events” panel, for example, uses the “salesforce_event” table made available in that way.

Basic SQL Queries

The command “steampipe query” launches a console where you can interactively explore the available tables. There you can use the “.inspect” command to review the columns available in each table.

You can write simple queries to ask and answer questions. For example:

And you can join across APIs. For example the query that drives the “Today’s Events” panel shown above combines information from four tables. Salesforce developers typically use other programming languages to do this API access and consolidation. Here’s a concise and elegant alternative:

   u.name as owner_name,
   a.name as account_name,
   c.name as contact_name
   salesforce_event e
   salesforce_account a on e.account_id = a.id
   salesforce_contact c on e.who_id = c.id
   salesforce_user u on e.owner_id = u.id

Joining Salesforce APIs With Other APIs

The Salesforce plugin is just one of (currently) 83 plugins that encapsulate a wealth of APIs including, all the major clouds (AWS, Azure, GCP, Oracle) and a number of other services including Twitter.

Would you like to be able to track the Twitter reach of your contacts? The dashboard shown in the screencast does that.

You’ve already seen how to use Steampipe with the Salesforce plugin to query Salesforce APIs. You can also use the Twitter plugin to query Twitter APIs. And you can join across those APIs in an easy and natural way. In this example, twitter_username__c is a custom field added to the Salesforce Contact record.

with salesforce_info as (
    twitter_username__c as twitter_username
    twitter_username__c is not null
  order by
  'https://twitter.com/' || s.twitter_username as twitter_url,
  t.public_metrics->>'tweet_count' as tweets,
  t.public_metrics->>'followers_count' as followers
  salesforce_info s
  twitter_user t
  s.twitter_username = t.username

If you are a Salesforce developer using Java, Python, C#, or another programming language, you may be thinking: “OK, that’s pretty nice but I still prefer my own language over SQL”. No problem! You can keep using it because Steampipe is based on Postgres, and there are Postgres connectors for all the languages. So you can benefit from Steampipe even if you only use it as a convenient way to make Salesforce (and other) APIs available to your language.

While you’re evaluating that option, however, you might want to check out how easily you can use Steampipe to visualize data from those APIs on dashboards.

Running the Steampipe + Salesforce Dashboard

If you’ve completed the installation steps above, you can run the dashboard shown in the screencast from a terminal, like so:

git clone https://github.com/turbot/steampipe-samples
cd all/salesforce
steampipe dashboard

Steampipe launches the local dashboard server and invites you to visit http://localhost:9194 in a browser.

There you’ll see the dashboard shown in the screencast, as defined by file.


Editing the Steampipe + Salesforce Dashboard

Dashboards are made of SQL queries (like the ones above) wrapped in a language that binds the queries to dashboard widgets. For example, here is the first infocard shown in the screencast.

Here is the code that drives it. Pretty simple stuff!

card {
  width = 2
  sql = “select count(*) as contacts from salesforce_contact”

Charts are similarly straightforward. Here’s the ”Contacts – by lead department” panel.

And here’s the code that drives it:

chart {
  width = 5
  type = "donut"
  title = "by lead department"
  sql = <<EOQ
      department is not null
    group by

How easy is it to make changes? Check out the final seconds of the screencast where a live edit turns the “Contacts – by department” chart into a “Contacts – by title” chart.

Final Thoughts

Once you’ve made a few small changes you’ll inevitably have ideas for your own dashboards. Our tutorial will help you get going. As you explore what Steampipe + Salesforce makes possible, don’t miss out on the opportunity to tap into other APIs as well. The Twitter Activity panel shown in the screencast, for example, reports the numbers of tweets and followers for Salesforce contacts whose records include Twitter usernames. Joining a Salesforce table to a Twitter table (or a table made from another API) works the same way and just as easily.

If you build something useful and would like to show it off, we’d love to learn about it in our community forum. We are an open-source community full of people who are passionate about what’s possible when it becomes easier to visualize your Salesforce data and combine it with data from other sources.

2 thoughts on “SQL Queries + Salesforce Dashboards: Answer Business Questions Faster

    1. Steampipe’s open source dashboards can be hosted by the user beyond the local machine for others to access, or could create shareable outputs of the report in a variety of formats.

      Steampipe Cloud (in private preview; https://cloud.steampipe.io) provides a centralized distribution to host Steampipe making it easier to share & access dashboards as a service.

      Whether locally or with Steampipe Cloud, you can also use Steampipe as a Postgres endpoint to integrate any SQL IDE or BI tool such as Tableau: https://steampipe.io/docs/cloud/integrations/tableau

      Let us know how it goes, our Slack community is a great place to share feedback and speak with others using Steampipe: https://steampipe.io/community/join

Add Comment