New Directions

Since the first of the year, I’ve been on my own. After considering a number of really compelling job offers, I decided to start my own company and take a contract position with company doing work that’s meaningful to me (more on that in the future). I’m also picking back up with some GIS work … Read more

Stripe API Pagination in FME

I few weeks ago, I described an integration I built to pull data from the Stripe via its API and load it into BigQuery. There were two main problems with this approach: First, it was incredibly hacky – a Wile E. Coyote approach to the problem involving cron jobs and EC2 instances and GCS uploads and scheduled jobs in BigQuery. It got the job done, but in a way that was slightly embarrassing. Second, everything else we did was leveraging FME and this stood outside of that pattern.

I really wanted to bring this process into the FME fold, but I needed to tackle Stripe’s API pagination in order to do it. Most of Stripe’s helper libraries have an auto-pagination feature that allows you to keep loading objects until you reach the end. This feature is not available if you are using the raw Stripe REST API, which is essentially all that is available to FME via the HTTPCaller transformer.

Stripe’s API uses cursor-based pagination, meaning that it points to the ID of the next object to read in the dataset. Luckily, Safe Software has an article that describes how to handle cursor-based pagination. This article is that it uses the Slack API as an example. The Slack API is well designed and returns everything you need to handle pagination in one place in the response document, but this isn’t the case with Stripe’s API so I had to modify the approach somewhat.

Read more

Integrating Stripe with BigQuery

One of the projects that I mentioned in my post a couple of weeks ago was the migration of our billing system to Stripe. Stripe is widely used for billing on the internet, in both SaaS and non-SaaS use cases. A while back, I wrote about the general limitations of IPaaS platforms in terms of flexibility and Stripe exposes a lot of these.

One particular product did not expose all of the object type we needed to extract from Stripe. Another simply did not sync all of the object types it claimed to be syncing. A third had a clear bug in which it wrote the current date/time into all date fields. In each of these cases, we were left to file support tickets and wait. I moved on.

Read more

Salesforce to Jira: Using FME for Business Automation

I mentioned in my previous post that, at my current organization, we had made good use of FME in processing non-spatial business data. In this post, I’ll provide an example of that. One of the functions in my department is professional services. Our professional services team, like similar teams in many product companies, doesn’t capture its own work. It is dependent on our sales team to capture work and communicate when the deal has closed so work can begin. As we grew, and as we switched to fully remote during the pandemic, this communication often lagged, resulting in delays in starting work that had previously closed. We chose to tackle this problem with automation.

Read more

SaaS, IPaaS, and Interoperability

I started this blog back in 2006 during a time when I wasn’t doing much geospatial work at all. I was working on building a human resources system for a federal government customer who was falling under the then-new and now-defunct National Security Personnel System. Because it was new and sufficiently different from the GS system, there were no off-the-shelf products to acquire. So I found myself deep in the development of logic to model workflows for personnel reviews, tracking accomplishments, and other minutiae of managing different types of personnel. There was no room for anything geospatial and I felt it, probably incorrectly, slipping away so I started doing personal projects at home. This blog started out as the means for documenting those diversions, which included my first dabblings with PostGIS among many other things.

I find myself in a similar period now. I’ve been mostly occupied the past few months with migrating to a new billing system. It’s not sexy and it’s certainly not geospatial, but billing is a necessary engine of any business. When people talk about “growing pains” as businesses scale up, billing is one of the biggest.

Read more

FME, Salesforce, and BigQuery

More often that not in my current role, opportunities to get my hands dirty come from the data side of our operation rather than the engineering side. These days, the data side involves corporate data rather than a lot of geospatial data. If I were to be guided by my own personal inertia, I’d drift toward traditional geospatial data 99% of the time, but working with other data stores and building pipelines involving them is good exposure.

Most recently, I’ve been working a lot with Salesforce and other data sources to support customer success operations. Customer success, as a discipline, is relatively new, having grown out of the SaaS market from the best practices involved in nurturing and growing customers post-sale as part of the SaaS land-and-expand model.

SaaS typically begets SaaS – meaning that you won’t often find SaaS companies using on-prem versions of business tools. This presents interesting challenges for data integration and analytics. Fifteen years ago, there’d most likely be a server room with installs of various vertical systems that had been configured to use whatever the organization’s blessed database platform was. In the event that an individual system didn’t support that database, there might be some nominal ETL performing a one-way sync so that the necessary charts and graphs could be made as needed.

Read more

Refreshing a PostGIS Materialized View in FME

I am following up my previous post with an extremely simple example using FME to kick off the refresh of a materialized view (matview) after a data import. I had never used FME prior to coming to Spatial Networks, but now I’m hooked. I’m having a really hard time finding things it can’t do.

As I mentioned in my last post, it’s really easy to refresh a matview in PostgreSQL using the REFRESH MATERIALIZED VIEW statement. This leaves open the possibility of automating the refresh as appropriate in an application or other process.

I decided to illustrate this using a basic FME example. Using the cellular tower data set from my past post, I extracted a table containing only the records for the state of Maryland. The towers data set contains the two letter abbreviation for the state, but not the full state name. So, I built a matview to join the state name to a subset of columns from the towers data set. The SQL for that matview is here:

https://gist.github.com/geobabbler/76eb5db4ec685833a61e69bd58a57522

I will use FME to append the records for the state of Virginia from a GeoJSON file to the PostGIS table containing the records for Maryland.

Read more