Prepare and Load Data to Salesforce using Heroku Postgres & Heroku Connect

Hi. My name is Zach Berman. I'm the partner manager for xPlani. We provide an ETL platform that's available in the elements marketplace, which allows you to create automated data pipelines to and from Heroku Postgres.

In this video, I'm going to show an example of how customers that utilize Heroku Connect can provision the add on and leverage XPlanny to quickly and easily extend the benefits provided by Heroku Connect's bidirectional sync to Salesforce out to external services and data stores. So I have an app set up here with Heroku Connect provisioned as well as a Heroku Postgres database. This is connected to my Salesforce org and has been set up to sync a few select objects down into Postgres that I may want to bring data externally into the Postgres database to sync up into Salesforce or take data that's come from Salesforce and send that out via xPlanny to an external data warehouse, a data lake, maybe a flat file for a partner or customer or something along those lines.

So we'll jump into xPlanny here.

Customers can go through and access it by clicking on the Integrate.io app, or they can set up logins and go directly to Integrate.io dot com and log in.

So we go over to Integrate.io. The first thing we'll need to do is set up our connections. What I'm going to work through is a very basic scenario of an example of an ERP database where we have accounts receivable analysts that are determining our credit status of our customers. So whether their application for a line of credit has been approved, if they're in good standing, if they're a current customer or say if they are in delinquency, and that's going to have an effect on whether orders can be processed, maybe it changes the available discounts that a rep can offer them. And we'll sync that up into Salesforce regularly, so they can see it right when they go into an account.

So the first thing to do is set up our connection into Heroku Postgres to that database that is using connect.

This is very quick and easy. As you'll see, we have a variety of different connectors here. So I'm going to use a Heroku Postgres and MySQL connector. But you can bring data to and from all these different file stores, data warehouses, you can connect to Mongo.

Also, if you're looking to aggregate data and push further towards that single source of truth in Salesforce, we have a variety of services you can go and connect to as well. And you are not limited to our native service connectors here. We also have a REST API component that you can go out to any available REST service, set up the authentication, make calls to endpoints and bring in that data and send it into Postgres tables, combine it with other data that you already have. So we'll go through here and set up our Heroku Postgres connection.

Give it a name.

And then all of this database information is very quick to grab inside the Heroku interface setup takes less than a minute typically to get this up and running.

So we got this, we'll test our connection, we are all set.

And then we'll go through and we'll add this MySQL database that we have as well for our example.

And this will take just about the same amount of time as setting it up from the Heroku Postgres side as well.

All right, so now we have our source and destination databases set up and we can actually go through and start to build pipelines and we can get data moving really rapidly between Postgres out to MySQL or MySQL back to Postgres. And this would be the same with any of the data warehouses or other data repository options that we have in the platform.

So we'll go through and create a package. This is where you build out those data pipelines.

There's multiple types of packages, data flows and workflows. Customers will mostly work inside of the data flows. But what a workflow will allow is if they're exploring, say, know, bidirectional sync with an external system, you can build out an ordered workflow that has dependencies that makes sure certain jobs run and populate data before the next step runs to do a different sort of sync. So if you're syncing contacts in between them, you can make sure that those contacts are pushed up in new contacts are pushed up into Salesforce from an ERP or something like that, and gives time for the Salesforce ID populate and then it can do a sync back down to bring that Salesforce ID back into the system or any changes on that side after triggers or workflows run inside of Salesforce itself. So we will create a blank data flow here and go to create package.

So as I said, the example, we're going to look at bring data from an ERP into Salesforce. So the scenario here would be that we have sales reps looking at an account, and we have accounts receivable folks in an ERP system going through and determining the credit status of that existing account or a new account. So I'm gonna jump over here really quick into a data clip I have that is for the accounts database coming out of Heroku connect. We've got a few selected fields here, but you'll see credit status and AR owner are custom fields and they are no. So we haven't pushed this information into the platform yet.

So we'll go back here and we can quickly get a pipeline set up.

So we've got our MySQL database, we'll go in and add source components for that. I reference components because you'll see in a second that we need to grab fields from two different tables in this database. And we can really easily do that even without SQL knowledge because xPunny has a transformation layer where you can do these transforms mid flight, including joining data sets and it's in a very declarative manner. There's not code that you're going to have to write through SQL or anything like that. So we'll choose our example database from MySQL.

We define what schema we're going to, and that it is our customers table is what we're initially going to look at. There is a where clause here and what this would allow you to do is when you have a modification timestamp or anything like that, Once we run this initial load, you can modify this with a where clause that every time the job runs, it looks back a certain amount of time or it looks back to say the last time the job ran successfully. So you're getting that incremental load of just the fields or rows that have had a change to them and sending those updates to Salesforce rather than mass updating everything each time even if there hasn't been a change to the fields that we're looking at. So when we click Next, we will get the full schema from that table. We have access to all of the fields. We don't however need all of the fields here.

So we do want the credit status. We're going to want the FINRAP here, which is the financial department rep, which is our analyst that we're looking for.

And then previously in this system, they've mapped their Salesforce IDs and have those in their ERP.

But you could also do this with x plenty and go back through if you have a shared naming convention somewhere between the two platforms or you push say, the ID from the ERP up into Salesforce through a flat file or something along those lines.

You can do different things to get those identifiers in here. But we do have the Salesforce ID in this set. So we're going to use that as we'll sync on it. You can look down at a preview of the data here. And when I referenced having multiple tables to pull from that's because when you look at the fin rep, it's actually just an employee ID that is here, rather than the name and the spot in Salesforce under the account with the custom field that we have, we want the name for reference, not a number that the rep is going to have to go back and look up.

So we'll click Save, we have these three fields flowing through.

Then we'll add an additional database component. We're going back to the same database, same schema, but to the employees table.

So we'll go to the schema and now we have full info about the employees. We know we have that FinRep ID, which is the employee ID that will match on And then we want their first name and last name.

So we've got the preview done here again to see what those are. We can click save.

And from here, we need to get these datasets joined together. It's a very simple process and it's plenty. We have a variety of transformations that are declarative, you can just go and grab this join component.

Put it here, just drag connections between the two and then go and define your join without having to do any SQL scripting or anything like that. We know we want the full set of data from the left hand side, which is the customer database set. And then we're going to wanna match that up on that rep ID to the employee ID.

And now from there we have a combined data set flowing down.

And what we're gonna do here is go through and do a select component.

This lets you have a little more customization and transformation outside of the standard components that you see where you can do samples, joins, unions, filters, assertions, aggregation, all of that.

So in this instance, because in the database, we have a first name and the last name, but in our Salesforce component in the object in that field, it's just a listing for a single name. It's not a first name, last name. We wanna go through and combine those into a full name. We can do that through a select component. So we open up select and click auto fill. We've got all our different fields that are passing through.

And then from here, we can actually generate a new field in this data set. And what we'll go through and do is there's a huge amount of functions and explain that you can use to build out these expressions.

But what we're going to do is concatenate these two.

And for all of these, for anyone even without experience, so you can find concat, it goes gives you a tool tip explains exactly what the syntax is easy to use. But we also have a reference to the fields that we can input here as well. So we've got our concat, we want first name, and then we do want to add a space in between that first name and last name.

And then we'll add in our last name, and then we hit save.

And then we can just name it, whatever we want here as an alias. So we'll do ar name. So it's accounts receivable contact.

Here we click Save again. Now we have that new full field coming through.

And then we are ready to head to our Postgres database and hit Heroku Connect and sync back up to Salesforce. So choose Heroku Connect. We know our schema coming out of connect is Salesforce. And we are going to the account table.

From here, you've got options on creating tables and adding missing columns.

You can, if you're not syncing these back up into Salesforce, you can uncheck these options so you're never accidentally creating an additional column that you're not going to utilize.

And then for operation type, so you can just send inserts, can do overwrites, but for this, we're going to go through and do an update and insert because we've got specific fields that we want to update on existing rows.

So I'll go down, we'll go to the next step of schema mapping.

And this is a fast process as well.

So we have a few different columns that we want to go and populate data into. So FINRAP that we know is from our ERP database, we don't need it. Salesforce ID, we do want and that name is the same on the Postgres side. Employee ID also from our ERP, not something we want.

First name, last name, we're not going to use, but we are going to use this our AR name and our credit status. However, these naming conventions don't match what is in the field on the Salesforce side. So we have custom fields that were created for this. So credit status c under underscore c and then we have ar owner c is what would map to the ar name.

And then it's telling me here, I must select a merge key, which is going to be our Salesforce ID that we have mapped between two accounts. Then we can click save.

We have a save and validate option here, which will go through and check the package, make sure the connections are there, make sure any syntax that you used is correct. We have no errors and we can go and run our job.

So when I click run job manually, I have a cluster up and available to run on. You can also provision these clusters manually, but typically a customer would be using the system in an automated fashion. And in a second I'll show there's a full scheduler that you can go and use that will manage the clusters for you and make sure they're available, provision them to run jobs. You don't have to do any manual spinning up and spinning down of those. So we'll go through, we'll quick run job, and we'll let that go run.

Now I can jump over and I can show you the scheduler. So down here, go to new schedule, you have the option to choose a basic incremental repetition, the system will go down to as fast as every one minute.

And then you also have the option for chronic expressions. So where you can get very granular and when you want it to run. So for examples here, every Monday at eight am, you could have it run between eight and five pm, you know, every hour, every half hour.

You could then set a different schedule to start running the job, you know, every four hours overnight or however you might want to have it.

And then from there you can determine the cluster that it runs on, the resources that it has, as well as schedule multiple packages to run at the same time in parallel. So now let's go back and check on the status of our job and go to our job history here. As we see, our jobs got one hundred percent completion, we can view details and see it loaded the records.

And then we can jump over to our data clip. And when we refresh this, we should see the credit status and AR owner now have data populated into them.

And there we go. Now we've passed along the status of what their credit status is, who that finance department representative is, who's overseeing what their credit status is. And a rep can see this at any time when it's updated.

And you you can set up a schedule to go through and incrementally update this.

Same thing here on the Salesforce side, we'll jump over and when the update from Heroku Connect runs back into Salesforce, we'll see an update here where the credit status and AR owner for this Burlington textiles company is also updated.

And now you see credit status shows they are delinquent, which may prevent their orders or pricing changes or anything along those lines. And now the rep can go back and check who the accounts receivable owner is and get in contact with them to talk about it and try and work out the situation with the customer. Very simple example here, but you can see how fast you can actually get these things set up and actually get results to data insights or information that someone would need on the Salesforce side. Just a couple of minutes to set up the connections and then the packages often can just take a few minutes themselves to get them going and then run.

The same can be done the opposite direction as well, bringing data out of Salesforce out to Postgres and then outside of Postgres via xPlani to any of the connections that we support.

So SFTP, Amazon shared storage and other shared storage options, Any data warehouse you'd be looking at other external databases. It's a very simple process.

If we were to jump back here, create a new package, go through, you just choose to go have a source of Heroku, bring it through Salesforce is our schema.

We know we have a lead table from syncing it from connect.

And then we have all our available fields that we've synced through from leads and we can take and select specifically what we want or bring them all through and then follow down a pipeline again of doing any cleansing that we need or sending it straight across into a data warehouse to then combine with other information or in an xplanny package, we could say pull in, you know, Facebook ad insights information and try and sync it back to a lead that's converted and run analysis on that.

Outside of, you know, connections to the native connectors we have, we do also have a REST API source component. So customers can go through and bring in data sets from REST API, just as they were with the native sources of you've seen. So you can go through, we can use a open API here just as example.

This won't require authentication, but you can support any different authentication, the headers, the body, and then go through.

And if you have the JSON structure already, go through, make a request. Now, this is a live API endpoint request we've made. We're getting all the available fields in the schema. You've actually got a preview to go down through of what the JSON was if you want to dig into it further.

But then you can bring these fields through and in the same way that we joined two tables from a database, you can join the response from an API endpoint with a database table, a call to another API, data from the Heroku connect Postgres database. There's a lot of flexibility with what you can do here. So the real key here is you can have someone, you know, very inexperienced with development and experience with database management or going completely other way. They are very experienced.

The platform is easy for both sides to use and work through the specific use cases that they need, whether it's, you know, someone without experience going and simply getting a small data set across or getting something synced into Salesforce, or someone who wants to do more aggressive cleanup, maybe they're going through and doing, you know, some business intelligence work and want to grab a lot of different fields out of Salesforce and send them externally or aggregate everything they possibly can into Heroku Connect and bring it up into Salesforce to use Einstein Analytics or something along those lines for that single source of truth.

I'll throw up a quick screen here just to show you a view of kind of the ecosystem once you include Integrate.io and everything that you can bring into Heroku Postgres and export out from Heroku Postgres.

So hopefully this walkthrough has been helpful. I'll throw up my contact information here as well. If you have any questions or you have a prospect right now with a use case where you think this might be valuable to go through, I'm more than happy to discuss it.

Talk about how plans are sized based on what they need and then talk about what the steps are if they were to get on the platform and actually go and test it out. Thanks for your time. Feel free to reach out.

‍