A Jitterbit Case Study: Higher Education Adventures in Salesforce
Tags: salesforce, database, etl
Published: 2019-11-04 22:40:02

---
title: A Jitterbit Case Study: Higher Education Adventures in Salesforce
published: true
date: 2019-11-04 15:00:00 UTC
tags: salesforce,database,etl
series: "Salesforce (Jitterbit) ETL Integration Higher Ed Case Study:  3rd-Party Admissions Applications"
cover_image: https://katiekodes.com/images/header-jitterbit-higher-ed-case-study-en.png
canonical_url: https://katiekodes.com/jitterbit-case-study-intro/
---

Boy have I been on a database integration adventure these last few weeks.  In this series, I'll review some of the more interesting hoops I jumped through with Jitterbit to load data in CSV files provided by an external admissions application partner _(think common app ... only this was a much smaller organization)_ into tables of a Salesforce org.

---

## Intro

Before I launch into a bunch of "**how**" party tricks, I'd like to spend this post talking about **what** business purpose I was trying to accomplish.

As I mentioned, I am building a scheduled daily data-load job to fetch 3 [CSV](https://en.wikipedia.org/wiki/Comma-separated_values) files, plus a number of PDF/PNG/etc. files, from a vendor's server and selectively load their contents into Salesforce.

### Source Data Structure

The 3 CSV files are:

1. `students.csv`:  1 student per row, containing details like their first name, last name, date of birth, etc.
	* Each row contains a unique record-identifying value in the `STUDENT_ID` column.
2. `applications.csv`:  1 admissions application per row, containing details like the level to which they are applying  _(e.g. "undergraduate")_, the type of application they are submitting _(e.g. "first-year / transfer")_, what they'd like to major in, in which semester they'd like to start, etc.
	* Each row contains a unique record-identifying value in the `APPLICATION_ID` column.
	* Each row also contains a column with a cross-reference to a `STUDENT_ID` value.
3. `previous_schools.csv`:  1 "previously attended school" per row, containing details like the name of the school they attended, whether it was a high school or a university, the date on which they started attending the school, the date on which they stopped attending the school, their GPA at that school, etc.
	* There is no unique record-identifying column in this data set.
	* Each row contains a column with a cross-reference to a `APPLICATION_ID` value.
	* Each row contains a column with a cross-reference to a `STUDENT_ID` value.

Furthermore, the data I fetch includes a number of PDF, PNG, etc. photographs of transcripts, test scores, and other supplemental documents to support the admissions application.  These are named with a strict pattern as follows:

* `STUDENT_ID`, followed by an underscore
* `APPLICATION_ID`, followed by an underscore
* Free text describing what kind of document it is _(e.g. "High School transcript 1")_
* A period to separate the filename from the file extension
* A file extension (e.g. `pdf`, `png`, etc.)

To the extent that a collection of files in the same folder can have an "entity-relationship diagram," here's how I interpret the data to be "structured:"

![Entity-relationship diagram of CSV and other files](https://katiekodes.com/images/jitterbit-case-study-1-csv-erd.png)

> Note that in this blog series, I am using the word "**student**" to describe a **person** -- a **human being** -- because that's how the vendor named our files and my mind was on this particular project when I began writing.
> 
> Attentive reader [Luke](https://twitter.com/alwaysthinkin) pointed out that the term "student" is often context-specific.  For example, working with Salesforce's Education Data Architecture _("EDA")_, he uses the word "student" to mean the junction between a Salesforce `Contact` and a Salesforce `Account` _(or "school" within an institution)_.
> 
> My use of the word "student" in this series is simply a synonym for "person."


### Destination Data Structure

Luckily, our senior admin managed to convince the 3rd-party application provider to make sure that their data feed of CSVs broken up into the same object _(table)_ structure and foreign-key/primary-key relationships as our Salesforce org.

_(That is, they negotiated to ensure that the CSV files from the vendor would be [normalized](https://en.wikipedia.org/wiki/Database_normalization) the way we normalize our Salesforce org.)_

In plain English, **this means that 1 row** of source-CSV-file data **should map to 1 row** of Salesforce data.

The ERD of our Salesforce org looks almost identical to the one I provided for the "files," and that's no accident:

![Entity-relationship diagram of 4 Salesforce tables](https://katiekodes.com/images/jitterbit-case-study-1-sf-erd.png)

The two main differences are that:

1. There's an actual table connected to `Contact` called `Attachment` that we will be inserting 1 record per "loose PDF/PNG/etc." into, loading the file itself into the `Body` field.
2. There's a circular lookup to `Application__c` from `Contact` called `Active_Application__c`.  Even though `Contact` is a parent table to `Application__c` ... think of this as a "favorite child" sort of field.

There are other lookups, particularly on `Application__c`, _(e.g. "in which term someone would like to start" is a lookup to `Term__c`)_, but I've tried to keep the basic inter-table relationships simple.

Within Salesforce, `Contact` and `Application__c` have **unique external ID**-typed fields for storing the CSV-side values of `STUDENT_ID` and `APPLICATION_ID`, respectively.

---

![Stock photo of a person folding laundry and stacking it in piles by type](https://katiekodes.com/images/sortinglaundry.jpg)

## What Needs To Go Where

It would be lovely if I could simply map similar tables to each other with a basic `UPSERT` on **external ID**, but that isn't the case.

Let's take a look at each table in Salesforce and talk about the business rules for loading data into it.

---

### People

Let's get this out of the way:  selling university seats to prospective students is a business-to-customer endeavor, not a business-to-business one.

We don't bother with Salesforce's native `Lead` object.  Everyone's a `Contact`.

However, to facilitate deduplication, we also have a table called `Contact_Temp__c` with some Visualforce apps built on top of its data.

`Contact_Temp__c` is a great place to load potentially-unclean batches of prospective students and sort out who's who, comparing them against actual `Contact` records, before updating or inserting records in the `Contact` table.

Because humans are in charge of actually putting data into `Contact`, I get off pretty easy when it comes to loading data into `Contact_Temp__c`.

I'll show my cards later, but suffice it to say that it's not complicated and is almost a straightforward "dump each row of the CSV file into a Salesforce record" mapping.

---

### Admissions Applications

Then we have the heart of the matter:  data representing an "application for admission" to a program at the university.

I was told that I don't have to keep `Application__c` records up-to-date once they've been initially loaded _(even if any details change on the CSV-file side at a later date)_, so you'd think the business process would go like this:

1. [Make sure](https://katiekodes.com/jitterbit-salesforce-insert-update-caches/) a `Contact` record exists with a value in `External_Student_Id__c` that matches `STUDENT_ID` from `applications.csv`.
2. Make sure an `Application__c` record **does not yet** exist with a value in `External_Application_Id__c` matching `APPLICATION_ID` from `applications.csv`.
3. **If** you clear those two preconditions, `INSERT` a new `Application__c` record into Salesforce cross-referencing the `Contact` in question and fill in the details as appropriate.

{% link https://dev.to/katiekodes/better-salesforce-insert-update-operations-with-jitterbit-caching-2g5o %}

But that'd be **_easy_**.

We can't have that!

![Flowchart of how simple I wish application automations were](https://katiekodes.com/images/jitterbit-case-study-1-flow-app-notthatsimple.png)

It turns out that during the human-driven process of creating `Contact` records, thanks to the magic of Salesforce automations that people generally find useful to our business processes, placeholder `Application__c` records get created with almost no details filled in but a `Status__c` of "Placeholder."

So it's actually this:

1. Make sure a `Contact` record exists with a value in `External_Student_Id__c` that matches `STUDENT_ID` from `applications.csv`.
2. Make sure an `Application__c` record **does not yet** exist with a value in `External_Application_Id__c` matching `APPLICATION_ID` from `applications.csv`.
3. **If** you clear those two preconditions, check if the `Contact` in question has any existing placeholder `Application__c` records.
	* If you find 1 or more, pick the oldest one and `UPDATE` it, filling in the details as appropriate, including setting `Status__c` to "Started."
	* If you don't find any, `INSERT` a new `Application__c` record into Salesforce cross-referencing the `Contact` in question and fill in the details as appropriate, including setting `Status__c` to "Started."

_Got it??_

#### Admissions Applications:  Bonus Round

We had to set `Status__c` to "Started" because our Salesforce org has a bazillion automations dependent upon that value.

However, as soon as they've had a moment to run, we need to go back through the `Application__c` records we just `UPSERT`ed and set `Status__c` to "Submitted."

_Still with me?_

#### Contacts:  Bonus Round

Finally, we have to make sure that `Active_Application__c` on `Contact` points to the `Application__c` record we just `UPSERT`ed.

_(Exception:  If we upserted 2 applications for the same parent `Contact`, we'll arbitrarily pick 1 to do the honors.)_

#### Lookup Errors:  Bonus Round

Earlier, I mentioned that I hadn't shown lookup fields within `Application__c` to objects such as `Term__c`, `Major__c`, etc. in the Salesforce entity-relationship diagram above.

However, it _is_ still important to populate these fields correctly.

If `applications.csv` has data in `START_TERM_NAME` or `MAJOR_NAME` that Jitterbit can't easily match to records within Salesforce's `Term__c` or `Major__c` tables via SOQL query, then I need to e-mail out an error log so our data integrity team can contact the 3rd-party vendor.

#### Flowchart

Here's a flowchart to recap how data needs to flow out of `applications.csv`:

![Flowchart of application automations](https://katiekodes.com/images/jitterbit-case-study-1-flow-app.png)

---

### Previous Schools Attended

Loading "previous schools attended" follows similar architecture to loading "admissions applications," mercifully without "bonus steps:"

1. We don't want to continue to update the Salesforce records corresponding to any CSV records we've already data-loaded once.
2. We have to do a tricky "take over the first placeholder you find, if applicable" operation because we have Salesforce automations on `Application__c` in place that create placeholder `Previous_School__c` records whenever `Level__c` is undergraduate:
	* 1 high school placeholder
	* \+ 1 college placeholder for transfers

Of course, the first requirement is rendered tricky because `previous_schools.csv` doesn't have any sort of obvious "unique record ID" that facilitates matching CSV records against existing `Previous_School__c` Salesforce records.

Since the business logic for "yes, it's a match" was simply "do nothing," I decided there wasn't severe harm in a slight chance of false positives.  I made my matching criteria as broad as seemed reasonable:

* A match on name counts as a match.
* A match on date-range also counts as a match _(in case someone "tidied up" the name in Salesforce later on)_.
	* After all, what are the odds of attending two schools over the exact same timespan?

Therefore, our business process for data-loading `Previous_School__c` records is as follows:

1. Make sure a `Contact` record exists with a value in `External_Student_Id__c` that matches `STUDENT_ID` from `previous_schools.csv`.
2. Make sure an `Application__c` record exists with a value in `External_Application_Id__c` that matches `APPLICATION_ID` from `previous_schools.csv`.
3. Make sure a `Previous_School__c` record **does not yet exist** with `SCHOOL TYPE` matching `RecordType.DeveloperName` and with either of the following "matches" to data from `previous_schools.csv`:
	* A `SCHOOL NAME` value _(CSV)_ matching `Name` _(Salesforce)_
	* A `START DATE` and `END DATE` value pair _(CSV)_ matching `Start_Date__c` and `End_Date__c` _(Salesforce)_
4. **If** you clear those three preconditions, check if the `Application__c` in question has any existing placeholder `Previous_School__c` records of the same `SCHOOL TYPE`.  For blogging purposes, we'll say that we can recognize a placeholder because its `Name` is "`~~~PLACEHOLDER~~~`."
	* If you find 1 or more, pick the oldest one and `UPDATE` it, filling in the details as appropriate.
	* If you don't find any, `INSERT` a new `Previous_School__c` record into Salesforce cross-referencing the `Application__c` in question and fill in the details as appropriate.

#### Flowchart

![Flowchart of previous school automations](https://katiekodes.com/images/jitterbit-case-study-1-flow-prevsch.png)

---

### Supplemental Document Attachments

Here's our business process for loading PDF/PNG/etc. files from the vendor into `Attachment` in Salesforce:

1. Make sure a `Contact` record exists with a value in `External_Student_Id__c` that matches `STUDENT_ID` _(available by parsing the filename)_.
2. Make sure an `Attachment` record **does not yet** exist for that `Contact` with a value in `Name` matching matching the filename.
3. **If** you clear those two preconditions, `INSERT` a new `Attachment` record into Salesforce cross-referencing the `Contact` in question _(filename to `Name`, contents to `Body`)_.

#### Contacts:  Bonus Round

1. If we just `INSERT`ed any `Attachment` records for a `Contact`, we have to `UPDATE` the `Contact`'s `Last_Attachment_Upload_Date__c` field to the current date and time.
2. If we just `INSERT`ed any `Attachment` records for a `Contact` whose filenames contain the phrase "`transcript`," we have to `UPDATE` the `Contact`'s `Last_Transcript_Upload_Date__c` field to the current date and time.

#### Flowchart

![Flowchart of attachment automations](https://katiekodes.com/images/jitterbit-case-study-1-flow-attachment.png)

---

## Up Next:  How I Did It

For the rest of this series, I'll break these business rules into bite-sized pieces and talk about the architecture I chose in Jitterbit to implement them.

I'm sure we'll hit a few fun Jitterbit-specific tricks along the way.

For the most part, I suspect I'll be covering architectural design concepts that should be transferable to any schedule-based ETL tool, so stay tuned, adminelopers, devs, and architects.