If you're putting together a team without a data expert and want automation, structure, and insights, Airtable could be the way to go. It's a quick-to-learn application, similar to Tableau, and it's a wonderful addition to your résumé. 🖋
You'll discover the fundamentals of this popular relational database that feels like Google Sheets on steroids in this article. There are a few hazards to be aware of, whether you've used relational databases before or not.
Then I'll give you some pointers on how to get the most out of Airtable. Finally, we'll talk about a solution that will satisfy both the technical and non-technical members of your team.
Interestingly, Google is rolling out its own Airtable clone, Google Tables and I have more on that in other posts.
Spreadsheets are excellent tools for quickly evaluating data using formulas and a few clicks. However, they aren't very good at keeping track of and displaying relationships in data. However, r elational databases are excellent for storing massive volumes of data and displaying cross-table relationships. But if you don't know SQL, they're not ideal for swiftly evaluating data.
If you want to access a database but don't want to use SQL, you'll need a programmer to design an API that will allow you to show your data – most likely through a website form. The technological labor required to design and maintain the interface takes time, money, and technical expertise.
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. -TDS
Cool views 😎
Views are the probably the best feature of Airtable and are a very powerful way to visualize your data.
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
WHERE col1 > 3
ORDER BY col2 DESC;
To create this query in Airtable, you’d use the menu buttons along the top.Menu buttons
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.Grouped by Section
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.
Your 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.
Data can be shown in a variety of ways, not just grid views. The data in your table can be viewed as a calendar, kanban board, or image gallery with just one click. Paid plans get access to a Gantt chart view as well. These perspectives are fantastic. It's as if they provide you the ability to give your team members access to a selection of front-end apps.
Airtable uses your table columns and data types to auto-generate a customizable form.The form builder view
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.
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.A subset of automation integrations
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.
Find and Replace script
It would be better if this very common operation moved into Airtable’s functionality, however we will have to wait and see if it becomes more deeply integrated.
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.
Suggestions for setting up your Airtable instance.
- 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 but this would cause you to loose critical functionality and performance.
- Use a unique primary key for each record. You will use the first column as a primary key for linking with other tables (joining).
- 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.
- 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.
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 is not for you!
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.
Unfortutantly, Airtable doesn’t give you the ability to execute SQL queries directly on your bases. Citizen developers without coding 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.
Google Tables offers similar functionality and is apparently going to be integrated with the Google Suite of productivity tools. Look for more information in following blogs.