Welcome, diligent readers of the InterWorks blog. Today, I am kicking off a great two-part blog. Internally, we have been tossing around conversation about a really unique tool called Airtable.

Overview

Airtable is a cloud-based spreadsheet-database-formbuilder piece of technology that offers a very unique approach to the way people collect data. In the first part of this mini-series, I will introduce you to the features of Airtable and talk a little about where we see its value. At the end of the post, I will show you how to take advantage of Airtable’s API and segue into part two where we use Matillion to pull this data into Snowflake.

Seems simple enough right? Let’s go ahead and get started by talking about Airtable.

Airtable

Airtable is a unique middle-ground for spreadsheets and databases. Many organizations are still manually updating their handy Excel spreadsheet for reports. While this approach may seem archaic, it makes sense for a business without the budget or expertise to fully hook up a website with a database on the backend.

So, what is Airtable? It’s essentially a database, and at the same time, it’s essentially a spreadsheet. Airtable offers an intuitive user interface to interact with its backend through the use of views. It does an incredible job of offering you the ability to interact with your database in the way that you want.

The Airtable Organization Structure

Airtable is organized into Bases, which act as a single database for a project or interest. These Bases then contain tables, similar to the worksheets we’re accustomed to in Excel. The flexibility offered within these tables is where Airtable first begins to differentiate itself from traditional spreadsheet tools. Each column can be configured for a vast array of functionalities. Are you tracking projects and want to keep tabs on task durations and percentages complete? Airtable has a field type for that. I could go deep down a rabbit hole and give you a scenario for each kind of field type, but I think if you want to just check out the list here, it will save us both some time.

One of the most interesting features of these tables is that you can define relationships between them. A field type for linking field is available, and once activated, it gives you the ability to link to your chosen table. At the end of the line when you are ready to use this data, the link acts as a Left Join. When you spit this data out via the API, you are given all the data from the table you requested, as well as the data from the table where a relationship between linked fields exists.

Unprecedented Flexibility and User Ease

If we are looking at a full-stack data environment, the worksheets and bases are just the beginning. After you begin building tables, Airtable allows you the opportunity to overlay different views on top of them. I am a huge fan of the Form View; this gives you the flexibility to create a webform using the table as a backend with minimal configuration. Having the ability to convert any table into a form in seconds is one of the most beneficial features of Airtable. Fundamentally, Airtable is a database, but with the option to overlay views on top of your base, it can be user friendly in an unprecedented manner. The impact of this makes data collection extremely easy—I used it for this blog to ask my coworkers a few questions. Look at how easy it is to build a webform off an existing table with AirTable:

While the views are powerful enough, Airtable’s functionality increases tenfold with something they call Blocks. Blocks are additional plugins that you can pair with tables to build dashboards, clean data, collaborate with team members and track timelines. With Airtable’s ability to meet needs around data collection, data management and basic reporting, it is a great fit for businesses in the exploratory phase of data analytics. Airtable provides flexibility and the opportunity to explore new data acquisition. When you begin to see the value of this investment, getting the data out of Airtable is as simple as an API call, which leads us to the next phase of this series: using the Matillion API Query profile.

Why You Care

Airtable essentially has created the lightweight Lego CRM system. You have the tools, you have the know-how, now just try it out and see what you might build! The data is easy to put in and easy to get out.

Getting the data out is what leads me to the next part of this mini-series: configuring Matillion to make API requests.