The engineering team here at DMI spends hundreds of hours each quarter building out new integrations with cloud APIs to give our DMI Data Warehouse customers access to more data. What we know more than anything is that no two APIs are created equally. From sorting and paging, to getting records counts, and even to “macgyvering” workarounds, here’s an inside look at our approach to vetting potential integrations.
APIs are web-based programs that provide functionality and instructions for apps to communicate with other apps. There are a few different popular architectures for APIs, and each of them behaves a bit differently. Regardless of the architecture, just remember this: developers use APIs to build integrations between systems.
A REST API is a data-centric web service that gives developers instructions for querying, creating, and modifying data. Developers build “HTTP requests” (or requests via the internet) to REST APIs using the instructions provided by the API’s developers. In most cases, these requests deliver responses that provide information about the success or failure of the request, record sets, and other metadata.
Our engineers use REST APIs to clone records from apps like Salesforce, Mailchimp, and Stripe (we call these “Sources”) into our customers’ DMI Data Warehouse instances. If the API allows, we capture new records, updated records, and deleted records from the Source and clone those to the data warehouse as they occur.
REST APIs often exchange data using an efficient, standard format called “JSON”, and most developers consider them to be easier to work with over SOAP APIs. That’s especially true at DMI: two out of every three integrations released for the DMI Data Warehouse in 2018 use REST APIs.
Yes, it’s 2019. No, SOAP isn’t dead yet.
SOAP is a more traditional and arguably more complicated approach to web services. While less adored by developers, SOAP APIs have a strong tradition of being more secure, more fault-tolerant, and more predictable than REST APIs. SOAP APIs come with a digital instruction manual called a “WSDL” (you’ll hear engineers calling it a “whizzdle”), which can be automatically monitored by code to check for changes –– meaning that engineers have more insight into why an integration might have broken.
SOAP APIs use XML formatted requests and responses to exchange requests and responses.
Web hooks are a different breed of API. With REST and SOAP, a developer is responsible for creating code that requests data from the API. Web hooks are different in that there are no requests. Instead, developers set up a one-time subscription with the Source app, and then the Source app automatically sends every created, updated, and deleted record to the subscribed application.
If you’ve ever used an “if this, then that” integration tool like Zapier, then you might have actually used a Web Hooks API yourself.
In order to accurately and confidently capture all of the data from a Source, DMI has to be able to loop through a Source’s records in a meticulous fashion. In most cases, this means ordering the response data using a “date modified” timestamp and then requesting a fixed number of records repeatedly. If a date modified timestamp isn’t available, we may be able to use date created timestamps, but that’s only the case if the API’s data can’t change over time.
For data that can change, if a date modified timestamp doesn’t exist, it isn’t possible for our integration to know what records to modify in the data warehouse. Our only choice in these cases is to completely rebuild the data clone in the DMI Data Warehouse each time the integration runs.
Our infrastructure processes billions of records each month; it’s a lot of data to keep track of. When it’s possible, we build reconciliation automation that asks the Source for a total count of records and compares that count to the total count of records in the DMI Data Warehouse. If for some reason the counts are different, our integrations automatically find and clone the missing records. Unfortunately, we aren’t always able to automate reconciliation because many APIs don’t provide functionality for requesting total counts.
Design flaws in a database or system are never more apparent than when working with an API. Poorly-thought-out database architectures make it difficult to query data. When the going gets tough, our engineers keep on going.
Our Stripe integration is able to build an initial clone of customers, transactions, and products quite efficiently using Stripe REST API. Unfortunately, Stripe doesn’t provide date modified indicators on these objects, and you know what that means –– we can’t keep track of which records have changed to keep the data clone in sync. Where there’s a will, there’s a way –– and in this case, the way was to create a second Stripe integration –– one that uses the Stripe Web Hooks API to subscribe to changes in the data.
At DMI, we encourage our customers to use the right tools for the job; usually that means an app for payments, an app for email campaigns, an app for events, and a few others too. Using the best tools on the market can be empowering for your fundraising objectives, but bad APIs can erase the functional gains of your favorite apps. Before you choose a new app to add to your organization’s digital architecture, make sure it has a robust, performant API that you can rely on to extract data.