Why use Airtable?

Airtable combines the power of a database with the convenient user interface of a spreadsheet. Then it adds some cherries on top, too. 🍒 It’s a solid choice for many use cases.

If you’re setting up a team without a data pro and you want automation, organization, and insights you may want to use Airtable as your data hub. Like Tableau, it’s a quick-to-learn tool — and it’s a nice addition to your resume. 🖋

In this article you’ll learn the basics of this popular relational database that feels like Google Sheets on steroids. Whether you’ve used relational databases or not there are a few pitfalls — you’ll see those too. Then I’ll share tips to get the most out of Airtable. Finally we’ll discuss a solution that might make the tech and non-tech folks in your organization happy. 👍

Interestingly, Google is rolling out its own Airtable clone, Google Tables — which I might explore in a future article, so follow me on Medium to make sure you don’t miss it. 😀

What problem does Airtable solve?

Spreadsheets are great tools for analyzing data with a few clicks and formulas. But they aren’t great for keeping track of and showing relationships in your data.

Relational databases are great tools for storing large amounts of data and showing relationships across tables. However, they aren’t great for quickly analyzing data if you don’t know SQL.

SQL (Structured Query Language) is how you extract information from a database. It’s not hard to learn the basics, but it IS another language. And learning a new language takes time and is a BIG hurdle for many people. Shameless plug, I wrote the book Memorable SQL so you can learn the basics quickly. 😉

If you want to use a database but want to avoid using SQL, you need a programmer to create an API to display your data — probably through a form on a website. The technical work to create and keep up the interface requires time, money, and technical person-power. 💰

Airtable is a low-cost solution to this problem. It’s like a built-in graphical user interface (GUI) for your database. In other words, what Apple did to the intimidating DOS command line, Airtable does to SQL databases.

Let’s dig into Airtable concepts next. 👍

Airtable Concepts

To use Airtable you need to know a few terms — many of which are borrowed directly from SQL.

Workspaces are Airtable’s highest level abstraction. If you have multiple workspaces they might be for multiple organizations you work with.

Bases are databases. You can’t access them with SQL, but that’s what Airtable is doing under the hood. 🚙

Tables are SQL tables. If you haven’t used SQL, you can think of a table like a spreadsheet.

Each row is a record.

Columns are called Fields. Fields are important, so let’s dig in.

A single field contains the same type of data. Possible data types include your usual text, integers, decimals, and dates. Airtable puts a new sheen on some other common data types including specially validated fields such as email addresses and phone numbers. 📞 A checkbox is a boolean True-False column. ✅

Another field option is an attachment. This is really just a link to a file that gets stored on Airtable’s server. If you upload an image, you can click the thumbnail to see the full size version, download it, or leave comments for other users to see.

There is even a barcode field data type. You could scan barcodes from the Airtable’s Android or IOS apps as part of an inventory tracking system. Both apps get excellent reviews. Oh — and Airtable has a star data type, too. ⭐️⭐️⭐️⭐️⭐️

I found the Android app easy to install and use for basic functionality, but mostly work with Airtable on my computer, because, well, it has tables. 😉

You can also type a formula a formula field. The syntax is similar to Google Sheets or Microsoft Excel. You can combine the values in columns, do control flow with if statements, etc. Here’s a playground where you can play with the various functions.

In the Add field menu you can create a connection to another table. Select Link to another record and then select the field in the linked table to join on (your foreign key is you speak database). That’s how you create a persistent join between two tables. 😀

You can choose to display more fields on the next screen by adding lookup fields, but you don’t have to. Later you can choose to display more fields from the join by adding a lookup field.

The joined tables will have the linked field as a column. You can then add specific records to the linked field. Or make a new record that will get added to the linked field. The linked field is kind of like a magic portal between the two tables. 💫

Joining tables through these persistent links takes some practice — whether you’re new to databases or a SQL pro.

Airtable let’s you add nice metadata fields. For example, you can see who and when created and updated records. And of course you can undo things with my most-used keyboard shortcut CMD + Z on the Mac (CTRL + Z on Windows).

Cool views 😎

Airtable’s views are a selling point. Views are ways to display a table’s data in various visual formats.

Grid view is the normal tabular like you’d get from a spreadsheet or database query. You can manipulate grid views to display the data like you’d like. For example, here’s some psuedo-SQL to do some filtering and ordering and hiding of columns:

SELECT col1, col2
FROM my_table
WHERE col1 > 3
ORDER BY col2 DESC;

To create this query in Airtable, you’d use the menu buttons along the top.

When you create your view you can Hide all the fields except col1 and col2. Or you can Filter all the records meet some condition, such as col1 > 3. You can group records with the Group button. Finally, you can Sort the records by a column with a click of the mouse.👍

If you know SQL, think of an Airtable Grid view like a SQL view.

You can hide fields in views. That’s how you get the same result as just listing columns in a SQL Select statement.

You view is a live look at a table. If someone updates the table that feeds your view, it updates in near real time.

Airtable has a nice way to aggregate information in fields. You can summarize columns by displaying a descriptive statistics or even histogram for fields at the bottom of a grid view.

Grid views aren’t the only way you can display data. With one click the data in your table can be seen as a calendar, kanban board, or image gallery. Paid plans can see a Gantt chart view, too. These views are super nifty. It’s like they give you the power to add a variety of front-end apps for your team members. 🎉

The view menu is also where you can make a form to get data into your table. Although a form is a bit strange to think of as a view, that’s where you find the option in Airtable. 🤔

Airtable uses your table columns and data types to auto-generate a customizable form.

The quick form generation is really nice. 👌

Automations 🏄‍♀️

Airtable automations can be set to run every time a certain trigger fires. For example, when a record is updated in one table, you can add a new row to another table. Each month, the Pro plan lets you run 50,000 automations. The Free plan gives you 100 per month.

Custom automations are similar to Zapier’s zaps or other no code solutions. The UX could be a bit more clear, and the error messages a bit more helpful when debugging, but the apps get the job done.

As of mid-2021 Airtable’s built-in automation integrations include a number of Google products and several other popular services. I expect the number of apps to increase soon.

If you want other actions you can use your favorite integration tools, such as Zapier, Integromat, or IFTTT. For example, Zapier has two dozen integrations for Airtable and Slack. 👍

Apps

Airtable groups a number of functions under its built-in apps. Apps include basic data visualizations, pivot tables, deduping, more advanced CSV imports, and running custom scripts. For example, here’s the code snippet to run a script to then find and replace.

It’s pretty painless, but it would be nice to see this very common operation moved into Airtable’s core functionality.

Quickly finding a record is possible in a grid view with the trusty keyboard shortcut CMD + F on the Mac (CTRL + F on Windows).

The Airtable Base schema app is cool for visualizing your tables and the relationships of the fields within them. You might know the schematic as an Enterprise Resource Diagram (ERD).

Setup Advice

Here are some tips to help you set up Airtable for success. 🎉

• Use very few bases. You can’t easily share information across bases. You might have just one base in your workspace, and that’s fine. Someone new to databases or Airtable might create a bunch of bases. You don’t want to do this, because you lose the primary power of a a relational database, being able to easily see relationships and keep data in sync.
• Use a unique primary key for each record. You will use the first column as a primary key for linking with other tables (joining). This breaks down if the each record’s primary key is not unique. You might need to dedupe frequently to ensure your data makes sense.
• If you are putting the same data into two tables, it’s a sure sign you should use a linked column in one of the tables. Then you don’t have to worry about people changing the data in one place but not the other, making the tables out of sync.
• Use views liberally. A single table might have many views.

Over time you should have few bases, a good number of tables, and lots of views. 👍

Advanced Airtable

If you want to auto-sync with your own SQL database — either to query that database or use a tool that does such as PowerBI, you can use Sync Inc. It spins up a queryable database and keeps it in sync with your Airtable base.

You can use the Airtable API and build your own integrations.

 has some nice discussion and code of using Python’s requests library to upload and download data from Airtable here and pyAirtable makes it easier to interact with the API.

You can add a metadata description to bases, tables, fields, and views. Just right click on the object you want to describe. This is helpful for team communication.

You can easily setup Airtable to receive emails or text messages when you are mentioned, because who doesn’t want more alerts. 🙃

As noted earlier Airtable supports scripting with Javascript. You can create scripts that are actions or use a scripting block. What’s the difference? A scripting block is for a person who is working in Airtable. Use it when you need one of your team members to input some information as part of the automation. In contrast, an automation script runs automatically behind the scenes. 😎

There’s also an API available, with a max of 5 requests per second. It has a very nice self-documenting examples. In addition to Javascript Ruby and .NET clients are available. There is not a Python client linked in the docs, but the pyAirtable package works well to fetch and update records. 🐍

Limitations and Costs

So why isn’t everyone using Airtable instead of SQL? There are three major reasons: cost, size limits, and no raw SQL access.

Airtable has a free plan, but many teams will find they need the Pro plan at $20 per user. Enterprise, which last I saw started at $60 a seat, gives you more features including a max of 100k records per base. See more about pricing and features here.

Airtable is not a big data solution. The Pro plan gives you a max of 50,000 records per base and Enterprise gives you 100,000. Got a few million rows of data? Airtable isn’t your tool. ☹️

Airtable doesn’t let you work across bases easily. You can sync a whole table or hack something together with integrations and duct tape, but it isn’t possible to link individual records. You might find yourself downloading CSVs and uploading them using the Airtable app if you’re untangling a pasta bowl full of bases for a client. 🍝

Relatedly, Airtable doesn’t give you the ability to execute SQL queries directly on your databases. As mentioned above, using it with Sync Inc. might be a win-win solution for larger teams. Folks without skills can add data and self-serve their queries in Airtable. Your data analysts who want to do advanced things can serve themselves outside Airtable. 🎉

Should my team use Airtable?

If neither your team nor your data are huge and you don’t have extra data and tech folks hanging out, it’s a good choice. You can use it to solve a lot of common business problems and it won’t break the bank.

The onboarding process for Airtable is pretty quick. A decently tech savvy person who has used spreadsheets a bit should be able to pick up the basics in a few days. The Airtable docs are great and the user interface is largely intuitive. That said, it does help to use someone with more experience to make sure you set up things for success. 👍

It’s worth noting that the Airtable company isn’t a flash in the pan. 🍳 This well-funded startup was founded in 2013 and was recently valued at nearly $6 billion.

As this great discussion of the Airtable company’s strategy explains, Airtable isn’t the most comprehensive solution for a 1,000 person sales department. For that, there’s Salesforce or other specialized solutions. But Airtable is a great Swiss Army knife for smaller teams. 👍

Wrap

In this article you’ve learned how to use Airtable. Now get out there and get your hands dirty. A personal CRM is a great first project to create something valuable and learn the basics of the tool.

I hope you find this guide to Airtable helpful. If you did, please share it on your favorite social media. 🚀

I teach, consult, and write about Python, data science, and other tech topics. If you’re into that stuff read more here and subscribe to my Data Awesome newsletter for occasional updates with helpful data resources.

Happy Airtabling! 🛩