Fixing Airtable Reporting in Google Looker (Data Studio)

A client recently approached me about a dashboard they had built in Google Looker.

NOTE: Google Looker is Google’s rebranded name for Google Data Studio. I think it is a horrible re-brand. The name sounds like a peeping Tom product.

Of course, “Google Far Fewer Features Than Tableau” - my rebranding attempt, may be worse - if not accurate.

Suffice to say, while lacking some features of other dashboard products, Google Looker is tightly integrated with the rest of the Google Cloud world. Plus, it’s free to use and works nicely.

The Airtable Dilemma

Airtable is a spreadsheet-esque, no-code/low-code database product. It provides a reasonably simple interface for creating relational databases and for allowing teams to work on that data simultaneously.

I’ve created several Airtable databases for clients over the past few years. I’ve fixed a few more.

Due to its simplicity, it allows anyone to create a database. That’s good and bad. Most average users can figure out basic linking but in 100% of the cases I’ve been brought into, the data design falls somewhere between bad and horrible.

This isn’t a knock on those who built the initial database. It is simply a lack of expertise in relational database design. This is NOT a trivial skill.?

I’ve been creating relational databases since 1991. I recall evaluating one of my early designs in 1993 and musing, “What were you thinking? This is garbage!” I suspect in 1995, I looked back at some of my design choices from 1993 and thought the same thing. It takes time to learn. Plus, you need to have made some poor design choices to recognize patterns and get better.

Airtable is great because anyone can build a database. Airtable is a challenge because anyone can build a database.

Add to that, meaningful and consolidated reporting is a challenge. Robust reporting tools don’t really exist in the Airtable. If you want to provide reports to external stakeholders - vendors, clients, etc. - the situation is even worse.

Airtable Reporting in Google Looker

There is a third party connector that allows you to create a dashboard in Google Looker using Airtable data. It works. But…. if you have any desire for truly complex data or if you have even marginally large datasets, it is SLOOOOWWWWWWWWWW!?

I mean, really slow. It can take minutes to load a page. And if you have dynamic parameters, which virtually all dashboards do (changing dates, filtering and drilling down), it is painful!!

Another dramatic limit of Airtable is that it has nothing akin to a cross table query language. Rather, you need to create views that connect the tables you need and then reference those views or blend data in Looker.

There is a better way!

BigQuery Saves the Day

BigQuery is Google’s SQL based reporting data repository. It’s FAST and integrates nicely with Google Looker.?

We use Google Apps Script to extract our client’s entire Airtable database and port that into BigQuery. This allows us to do some data cleanup, normalize field names (when people create field names, they are rarely standard), and, when necessary, create special reporting tables with complex metrics already calculated.

Basically, you can turn Airtable data into a reporting cube in BigQuery.

Bam!!! Performance for Days

We recently recreated a page of charts for a client using this method. The dashboard page loading performance numbers were as follows.

Airtable Data directly to Google Looker:

57 seconds for the first chart. Another 45 seconds for the next two. 1:42 for one page to render.

Google BigQuery to Google Looker

4 seconds for the entire page (3 graphs) to load.

How long does porting the data take

For the above referenced project, our Google App Scripts that create the BigQuery reporting data takes about 45 seconds to extract ALL the Airtable data and another 8 to 12 seconds to put it into BigQuery.

We have several BigQuery views that we use for reporting or to create some of the more complex metric tables.

We have it running on a timed trigger every hour plus provide our client a simple web app to request an immediate data refresh.

Conclusion

Airtable continues to improve their product. They recently added OAuth security versus a single, non-monitored, api key. I’ve been concerned about that api key for a few years, so, thank you.

Also, their interface system has improved quite a bit. It’s not great! But it works.

Their reporting is still dismal. It is likely we will continue to use BigQuery or some other SQL database to provide robust and high-performance dashboards for our Airtable clients.

You might want to do the same.