How to Connect Airtable to BigQuery

If you want to migrate data from Airtable to BigQuery, there are two well-known common ways to do so.

1. Creating data pipelines manually to transfer data from Airtable to BigQuery is feasible but comes with its challenges. This approach demands considerable time and necessitates the presence of a skilled engineer proficient in Python or SQL. This engineer must be adept at working with Airtable’s API to extract data and should also possess familiarity with BigQuery to ensure data is loaded in the correct format.

2. Use a data integration tool like Rivery to create end-to-end data pipelines in minutes. Tools like Rivery, help engineers with extracting data from sources like Airtable, transforming it efficiently, and loading it into BigQuery and other data warehouses, data lakes, and databases.

In this guide, we’ll demonstrate how Rivery seamlessly connects Airtable to BigQuery in three steps.

What is Airtable?

Airtable is a cloud-based database and project management platform that lets you concisely store information. With its user-friendly interface that creates databases—known as bases—Airtable handles various demands and workflows.  

Airtable is reliable for managing, storing, and retrieving data. You can store different types of information in various field types: Text, number, date, attachment, and much more. The platform also enables multiple users to work on the same base simultaneously, ensuring high productivity levels. 

Airtable’s built-in services include:

• Blocks: Airtable’s built-in apps and integrations provide additional features and capabilities, including calendar views, kanban boards, and Gantt charts, to help you visualize data.
• Automation: Airtable lets you automate repetitive tasks and workflows using its automation feature. You can set up triggers and actions to perform tasks automatically, such as sending notifications, updating records, or integrating with other apps and services.
• Integrations: Airtable integrates with various third-party apps and services—including Slack, Google Calendar, and Zapier—allowing you to streamline workflows.

What is BigQuery?

BigQuery is a cloud-based data warehousing platform designed for holding huge datasets. The platform ensures high productivity and efficiency, making it one of the most popular data storage platforms worldwide. 

BigQuery’s top features include: 

• Robust security: Emphasizes data security through meticulous access controls and encryption measures.
• Efficient data sharing: Facilitates smooth data exchange among teams, fostering seamless collaboration.
• Flexible data warehousing: Manages extensive datasets, empowering businesses to store and analyze vast amounts of information.
• Cutting-edge analytics: Empowers various data analysis tasks, including SQL queries, geospatial analysis, and machine learning capabilities.

Why Integrate Airtable data to BigQuery?

One of the main reasons as to why you would want to connect Airtable and BigQuery is to ensure your data remains accurate and up to date. Whenever a record is added or updated in Airtable it should generate a new or updated role in the specified Google BigQuery table.

This connection also provides a few other benefits that are worth calling out:

1. Centralization: Exporting Airtable data to BigQuery centralizes all your data in a cloud data warehouse, enhancing overall data management and consistency within the organization.
2. Better Insights: By connecting Airtable to BigQuery, businesses can leverage complex SQL queries, machine learning, and data visualization tools to gain deeper insights from their data.
3. Tighter Security Measures: Migrating Airtable data to BigQuery helps protect sensitive business data with robust security measures.
4. Pay-as-you -go model: BigQuery offers a free plan with generous storage and querying limits, and also offers a pay-as-you-go model beyond that. Compared to Airtable’s free plan that has restrictions on record quantity and attachment sizes.

The Manual Method of Integration 

You can manually integrate Airtable to BigQuery, however, it’s not a straightforward task. You’ll need to hire experienced developers to build custom scripts to pull, transform, and load data. Typically, this was done by developers demanding high-paying salaries building data pipelines manually using a Python script and leveraging tools like Apache Airflow. In total, this process can take a few days to complete and demands continual upkeep.

In essence, manually building data pipelines for each use case is not a scalable solution for managing large data streams, and often misuses developers’ time.

Thankfully, Rivery provides a seamless alternative. It facilitates Airtable-to-BigQuery integration with minimal or no coding required.

Integrating Airtable to BigQuery Using Rivery 

Combining the two platforms isn’t complicated, but you will need an important prerequisite for the integration:

You must obtain your Base ID:

1. Go to Airtable. 
2. Sign in with your email and password. 
3. Select your desired base to view the API documentation. 
4. Find your Base ID in the introduction section. 

If you’re struggling, here’s an alternative:

• Go to your base’s interface page
• Click the HELP (?) button at the top right. 
• From the menu, select <>API documentation. 

Once you have your Base ID, you’re ready for the first step. 

Step 1: Establish an Airtable Connection 

1. Fill in the Connection’s Name.
2. Choose Credentials Type.

• OAuth2:

1. Click Connect to Airtable.
2. Enter your email or choose from the available connection options.

• API Key:

1. Input API Key (scheduled for deprecation).

Alternatively, you can verify the connection by entering a Base ID and a Table name from that Base. Follow that by clicking the Test Connection button.

Step 2: Pull Data from Airtable 

You can use Rivery to pull data from Airtable and send it to BigQuery. Follow these steps:

1. Enter the ID of the Base you want to pull data from. 
2. Specify the names of the tables to pull data from.

There are various fields for filtering:

• Formula: Use an Airtable formula for advanced filtering.  
• View: Choose a view in your table to return records from that view. 
• Limit records: Choose a maximum number of records to fetch. 

The next step is auto-mapping. Rivery will configure your schema based on the selected tables. Therefore, pick which fields to fetch, add new fields, or remove unwanted fields.

Step 3: Choose Google BigQuery as a Target 

Before you set Google BigQuery as the target, ensure you’ve signed up for the Google Cloud and have a console admin user. 

Here are the steps to set Google BigQuery as the target: 

Enable BigQuery API: 

1. Go to APIs & Services on the Google Cloud Console menu pane and click on library. 
2. Search for Google Cloud Storage JSON API and click on Enable. 
3. Search for BigQuery API and click on Enable. 

Grant permission to the service account: 

1. Sign in to Google Cloud Platform Console. 
2. Go to IAM & Admin. 
3. Click on +add. 
4. Then, add the service account created by Rivery in the new window. 
5. Choose the BigQuery admin role and click save. 

Configure your BigQuery in Rivery:

1. Log into Rivery. 
2. Create a new connection for your BigQuery:
3. Go to Connections. 
4. Select New Connections. 
5. From the source list, choose Google BigQuery.

Enter the credentials information for your Google Platform Service Account:

1. Connection name. 
2. Description (optional). 
3. Project ID (can be found on the Google Cloud Platform Home Section). 
4. Project Number (can be found on the Google Cloud Platform Home Section). 
5. Region (Where your default bucket and database will be created). 

Click ‘Test Connection’ at the bottom to test. If successful, you have a working connection to Rivery. 

Now you can begin transferring data. 

Moving Forward

In this tutorial, we went over:

• Establishing an Airtable Connection with Rivery
• Pulling Data from Airtable to Rivery
• Choosing BigQuery as a target in Rivery

Rivery makes data integration simple for businesses of any size by unifying over 200 fully managed data sources via a modern data integration platform. Rivery is valuable for data analysts and data engineers alike with the platform’s ability to rapidly and easily connect to any data source and manage data pipelines at scale.