I’m fairly new to Airtable but I was speaking with a data engineer/entrepreneur who has a lot of experience building systems using Airtable. She told me that if she were able to perform complex queries across tables that would be a game-changer. Because she currently needs to copy all of that Airtable to another database in order to analyze the data.
My question is would other Airtable users find that useful as well? Why doesn’t Airtable already have something like that? Or, does it?
@djseeds I think it’s close! I believe the crucial missing element is aggregation because I believe using summary statistics in the queries is what we want. (and it seems like that’s on the roadmap)
But from what I understand, SQL and GraphQL serve very different purposes. GraphQL is meant more for fetching data and SQL is meant for relational algebra. Please correct me if I’m wrong!
Learning more about Airtable, I’m now wondering if most of what SQL can do can be done with Rollup fields and some scripting and AI. And done with very little difference in effort compared to writing a SQL query.
Or are those experienced with SQL just able to write their queries that much more effortlessly?
I was certified MSSQL DBA for 5 years before I started to work with Airtable. At first i tried to find a way to use SQL queries to manage data.
I also have colleagues who copy data from Airtable to Excel, perform some data management and then copy to Airtable. (in most cases the same can be done in Airtable 2 times faster)
In my opinion, Airtable needs more time and efforts to be familiar with and it’s harder to learn at some stage, but then I found that many complex tasks or queries can be performed in Airtable in a better anв a more simple way. Of course, with some exceptions. It has less freedom in a single sheet, but when talking about inter-table data communications, many crafted complex solutions in Excel are present in Airtable as built-in features. Also, for me it was a good visual representation for things that I couldn’t understand in SQL before and a perfect sandbox for learning JS.
When you need to manage huge amounts of data, like 1M+ of records or you need special formulas, like running totals for accountants, at some cases Airtable is just not a right tool to do that.
Thanks for sharing your insights @Alexey_Gusev.
Super cool that Airtable actually gave you a better understanding of SQL!
I do agree that a lot of the time it feels like Airtable is being used for the wrong cases by many users. But, as a beginner, it is hard for me to tell where the boundaries truly are.
Thanks for trying it out! Yes you’re correct, GraphQL is a bit more focused on fetching data, however it is quite flexible so it can be used for many use cases. What sorts of statistics are you looking to generate?
@djseeds Well, here’s one example that I’ve been thinking about lately: a query that gets all the students that have a test score above the average.
SELECT name, test_score
FROM Students
WHERE test_score > (SELECT AVG(test_score) FROM Students);
The main concept I’m trying to convey here is that it uses the average test score to filter the results.
Of course, in reality, you’d probably have to join tables to get the test_score and filter for a certain test, which would look like this:
SELECT s.name, ts.test_score
FROM Students s
JOIN TestScores ts ON s.student_id = ts.student_id
WHERE ts.test_id = 101
AND ts.test_score > (
SELECT AVG(test_score)
FROM TestScores
WHERE test_id = 101
);
You can use self-linking table for that, but usually I’m trying to avoid self-linking unless I really need it. Because self-linking is not 2-sided, and this adds extra headache.
In Airtable, if you want to work with any kind of totals dynamically changed, it is better to create a totals table containing just 1 record linked to each record of students table (it’s quite easy to establish auto-link for possible new student records)
Then you put this average back to students table via lookup and use it in formula
In this total table you can also use built-in field filter, for example to get average for students, whose mark higher than some value, like HAVING in SQL.
In real cases you can have 10 different groups of students, each group with it’s own average and each student from a large table of students will be linked to their group.
So, it will be a table of groups instead of totals, and it looks less strange.
In spreadsheet, you might want to create separate table for each group, while it is wrong for database, where similar entities should (at least very recommended to) be in the same table. With filtered views you can see separate groups. In SQL, I knew how to create and use views, but I didn’t understand - why
In Airtable I could clearly see it.
