How to connect Airtable to BigQuery

Collaborative services, such as Airtable, and data warehouses, such as BigQuery, are included in the base toolkit set of practically any company. But why connect Airtable to BigQuery? And what benefits can businesses obtain from such integration?

Connecting Airtable to BigQuery is essential to extend the functionality of the Airtable beyond its limits. Integrating it into BigQuery guarantees advanced analytics and data processing.

In this article, you’ll discover the two main methods for integrating Airtable data into BigQuery. The first uses the manual data loading approach, while the second method uses the professional data integration tool Skyvia.

Table of Contents

1. Understanding Airtable and BigQuery
2. Why Connect Airtable to BigQuery
3. Method 1: Manual Airtable Integration with BigQuery
4. Method 2: Integrating Airtable with BigQuery using Skyvia
5. Comparative Analysis
6. Conclusion

Understanding Airtable and BigQuery

Before plunging into all the technical stuff about the Airtable to BigQuery integration, let’s have a fresh overview of each tool’s strengths.

Airtable Overview

Airtable is an innovative cloud-based collaborative platform that combines spreadsheet and database functionality. Similar to spreadsheets, this tool has a grid structure, but rows are called records, as in a database. Another database-like feature is the admission of text, image, and date formats as field types, while spreadsheets recognize only numerical formats.

Key features

• Simultaneous collaboration on the same base (Airtable’s principal unit for organizing and structuring data) by several users.
• Preconfigured templates for specific purposes (project management, content planning, inventory tracking, etc.).
• Capabilities for integration with other services via in-app connections or third-party tools, such as Skyvia.

Airtable is frequently used among sales and marketing teams, project managers, and e-commerce specialists.

BigQuery Overview

BigQuery is Google’s product for data warehousing, designed to store large amounts of data. It also allows users to perform complex analytics over data and utilize built-in machine learning algorithms.

Key features

• Serverless architecture deprives users of the need to manage data storage and processing architecture, as Google handles everything automatically.
• Supports real-time analytics by ingesting data as it arrives.
• Uses SQL dialect for querying data.
• Natively integrates with other Google services, greatly assisting non-tech specialists in data transfer, analysis, and other tasks.

Overall, BigQuery is a powerful tool for organizations looking to perform analytics and gain insights from large and complex datasets without the need to manage the underlying infrastructure.

Why Connect Airtable to BigQuery

Airtable to BigQuery integration allows users to extend the functionality of each tool. By connecting BigQuery and Airtable, businesses obtain multiple advantages, and here they are:

• Data warehousing. Export Airtable data to BigQuery to consolidate all your data in a data warehouse. It enhances the overall data management within the organization and improves data consistency.
• Advanced analytics. Connect Airtable to BigQuery to enjoy complex SQL queries, machine learning, and data visualization, which assist businesses in gaining deeper insights from their data.
• Security. Migrating Airtable data to BigQuery protects sensitive business data.
• Cost. BigQuery has a free plan with up to 10 GB of storage and 1TB of queries. Then, the pay-as-you-go model applies, depending on querying, analysis, and machine learning intensity. Airtable also offers a free plan with unlimited bases but with restrictions on the record quantity and attachments’ size in a base.
• Collaboration. Migrating data from BigQuery to Airtable means that users can effectively interact with the enriched high-quality data.

Looks fantastic, right?

Data migration between BigQuery and Airtable is bi-directional, so let’s explore the methods of performing this.

Method 1: Manual Airtable Integration with BigQuery

Note that there is no native connection between tools, but the manual integration in one of the following ways is possible:

1. Using API
2. Working with CSV files

The first method is rather complex, requiring advanced knowledge of programming languages, middleware, and many other IT-related concepts. If you have such expertise, check Airtable API and BigQuery API for details.

Given the complexity of the first approach, we’ll focus on the manual integration using CSV files. It’s rather trivial but, at the same time, simple, and you can check this by following the setup instructions below. 

In Airtable

1. Log into Airtable and select the needed base.

2. Click on Grid View and select Download CSV from the menu.

3. Browse the location on your computer where the CSV file will be saved.

In BigQuery

4. Log into BigQuery with your Google account and go to BigQuery Studio.
5. Click Load file under the Add your own data section.

6. Select the CSV file with the Airtable data from your computer and fill in the required fields in the Create table window. Click Create table.

7. Navigate to the dataset where the table with Airtable data was created. Click PREVIEW to see the data.

Despite its simplicity, this method is rather slow and doesn’t align with the modern data generation pace. Moreover, it’s prone to various kinds of errors and data loss.

Method 2: Integrating Airtable with BigQuery using Skyvia

Skyvia is a universal cloud SaaS platform for data integration, workflow automation, backup, and query. Its interface is second to none, allowing users to perform all data-related procedures using 170+ data sources in minutes.

This service has the following products:

1. Data Integration contains various data import, export, synchronization, and replication tools. It provides powerful data mapping and transformation features for building compound data integration pipelines.
2. Automation has triggers that apply the needed actions on the destination side once a particular event happens on the source side.
3. Query is an online SQL client that allows users to execute SQL queries against cloud or relational data.
4. Backup allows users to back up data from supported cloud resources automatically on schedule or manually at any time.
5. Connect is an API-server-as-a-service tool for quickly creating web API endpoints to ensure access to data from anywhere.

Despite the rich functionality of Skyvia, the Data Integration product best suits the Airtable BigQuery integration. In particular, we focus on the Replication and Import scenarios to ensure data transfer between Airtable and BigQuery.

Prerequisites

• Airtable account
• BigQuery account
• Ready-to-go connectors in Skyvia:
  • See how to set up the Airtable connector
  • See how to set up the BigQuery connector

Replication Scenario

Skyvia’s Replication tool allows users to bulk load and update data into a DWH owing to ELT. Skyvia supports all popular data warehouses, including BigQuery, Amazon Redshift, Snowflake, etc.

Let’s have a look at Airtable Data replication to Google BigQuery using Skyvia.

1. Log into your Skyvia account.
2. Click +New->Replication in the upper menu.
3. Select Airtable as the source and BigQuery as the target.
4. Select the objects for replication.
5. Select Incremental Updates if you want to load new or modified records to a DWH over time.

6. Click Schedule to set up timing for regular Airtable data updates into BigQuery. Click Save.

Note that the free plan allows for one update per day, but if the near-real-time replication is critical for you, other pricing plans allow for more frequent updates. Check out here.

8. Click Create.
9. Click Run to start replication and observe the progress in the Monitor tab.

After the replication is complete, you can see the results in BigQuery.

Benefits of replication:

• Flexible scheduling for automated incremental data updates.
• Automated schema creation on the first replication.
• Complete or partial replication.
• Detailed logging.

Import Scenario

Skyvia’s Import can perform ETL and Reverse ETL scenarios, depending on the direction of data transfer and sources involved. In our case, we observe Airtable updates with enriched data (Task Score column added) from BigQuery.

1. Log into your Skyvia account.
2. Click +New->Import in the upper menu.
3. Select BigQuery under Source and Airtable under Target.

NOTE: In this example, the Task Score column was added to the table in BigQuery. So, we need to add the corresponding column table in Airtable before importing enriched data from BigQuery.

4. Click Add task to select the object for import and select the needed DML operation:
   • INSERT copies records from BigQuery to Airtable without checking for duplicates.
   • UPDATE updates the existing records in Airtable.
   • UPSERT isn’t supported for BigQuery.
   • DELETE removes the records by the specified primary key.
5. Map source and target columns to match the data structures.
6. Click Schedule to set up regular imports. Click Save.
7. Click Run to start the integration and observe the progress in the Monitor tab.

Once the process is complete, see the results in Airtable.

Comparative Analysis

We crafted a comparison table highlighting the differences between manual and automatic integration. It presents the strengths and weaknesses of each approach.

While the manual method is relatively simple and doesn’t require deep technical knowledge, it contains lots of pitfalls, including such critical ones as possible data loss and low speed. Meanwhile, Skyvia effectively addresses these drawbacks by offering near-real-time data integration, precision, and powerful data mapping capabilities with an intuitive interface.

Conclusion

Airtable helps companies to effectively track inventory, manage customer relationships, monitor project execution, etc. It doesn’t have visualization or analytical capabilities, though loading its data into the BigQuery data warehouse provides access to such an option.

Here, we have reviewed two methods for data exchange between Airtable and BigQuery – manual integration and Skyvia. Seasoned developers can also explore API documentation of both tools and set up custom integrations, which takes time and effort. As time is money in the modern world, consider using Skyvia to bring Airtable and BigQuery together and transfer data in both directions to boost your business’s overall productivity.