Ingesting Data into BigQuery: How to set up a Materialization in Estuary Flow
Try us free:
https://www.estuary.dev/
Join our Slack channel with a community of developers:
https://estuary-dev.slack.com/
BigQuery blog:
https://estuary.dev/cloud-sql-to-bigquery/
BigQuery sandbox:
https://estuary.dev/bigquery-sandbox/
________________________________________________________________________
Building a Pipeline With Estuary Flow.
Estuary Flow is a real-time data integration platform that allows you to connect Cloud SQL to BigQuery and other data sources. Estuary is streaming native and has an intuitive no-code UI that’s quick to use once your data systems meet the prerequisites. Like Dataflow, it's also highly scaleable and hands-off once the initial setup is done.
To connect Cloud SQL to BigQuery using Estuary, you'll need to meet the following requirements:
Google Cloud SQL instance: You need to have a running Cloud SQL instance that contains the data you want to transfer to BigQuery.
Allow connections from Estuary Flow: You'll need to enable public IP on your database and add the IP address of Estuary Flow (currently 34.121.207.128) as an authorized IP address.
Depending on whether your Cloud SQL instance is MySQL, Postgres, or SQL Server, you’ll have to meet a few more requirements to prepare your database. See the guides below:
MySQL
Postgres
SQL Server
A Google Cloud Storage bucket in the same region as the BigQuery dataset.
A Google Service account with roles/bigquery.dataEditor, roles/bigquery.jobUser, and roles/storage.objectAdmin; and a service account key generated. See this guide for help.
Once you've met these requirements, you can follow these steps to connect Cloud SQL to BigQuery using Estuary Flow:
Log in to your Estuary account, or sign up to get started for free.
Go to the create a new capture page of the Estuary web app and select either the MySQL, PostgreSQL, or SQL Server connector, depending on your Cloud SQL database type.
Add a unique name for the capture. Provide the Cloud SQL server address, database username (this should be “flow_capture” if you followed the prerequisite steps), and a password.
Click the Next button. Flow lists all the tables in your database, which it will convert into Flow data collections described by JSON schema. You can remove any tables you don’t want to capture.
Click Save and Publish.
On the dialog box showing your capture was successful, click the Materialize Collections button to continue.
Choose the BigQuery connector.
Add a unique name for the materialization.
Provide the following details for your BigQuery dataset:
Google Cloud project ID
Service account JSON credentials (which you generated per the prerequisites)
The project’s Google Cloud Region
Dataset name
Staging Google Cloud Storage bucket name
Scroll down to the Collection Selector. Each table you just captured from Cloud SQL will be mapped to a new table in BigQuery. Provide a name for each (you might choose to use the same names).
Optionally, you can modify the collection's schema, determining how it'll be mapped to BigQuery, but that shouldn't be necessary: Flow will output the data in a queryable format in BigQuery tables.
Click Next.
Click Save and Publish.
All historical data from your Cloud SQL database will be copied to BigQuery. Any new data that appears in Cloud SQL will also be copied to BigQuery in real-time. Along the way, data will be cleaned and re-formatted to adhere to BigQuery's data types and valid schema options.
Using this method requires minimal technical expertise, and your data pipeline is backed up securely with schema validation and exactly-once semantics. Additionally, a single data pipeline can sync many (or all) tables in your Cloud SQL database into equivalent BigQuery tables.
#bigquery #data #dataengineering #datapipeline
More videos
Estuary 101: How To Build Right-Time Data Pipelines
Join hosts Dani and Zulf for a fast-paced walkthrough of how to design and ship right-time data pipelines with Estuary. In this session, you’ll get: - Context: What “right-time” really means, where Estuary fits among batch vs. streaming and managed vs. self-hosted options, and why unified ingestion reduces cost and complexity. - Live End-to-End Demo: Connect CDC sources, apply declarative transformations, and materialize data simultaneously into a warehouse, analytical engines, and object storage—plus a look at observability, error recovery, and real-world scenarios like schema drift and backfills. - Live Q&A: Ask about your specific stack, pipeline designs, and how to scale Estuary for enterprise workloads. Perfect for data and analytics engineers, architects, and platform owners who want fresher data with fewer moving parts.
How to Stream Data into Snowflake
Ingest data into a Snowflake warehouse using real-time Snowpipe Streaming or using batch COPY INTO commands. Estuary makes Snowflake integration simple with pre-built no-code connectors. Following along? Find the copy/pasteable commands in Estuary’s Snowflake docs: https://docs.estuary.dev/reference/Connectors/materialization-connectors/Snowflake/ - Set up your first data pipeline for free at Estuary: https://dashboard.estuary.dev/register/?utm_source=youtube&utm_medium=social&utm_campaign=snowflake_ingestion - Learn more about Estuary’s Snowflake capabilities: https://estuary.dev/solutions/technology/real-time-snowflake-streaming/ - Read the complete guide to Snowpipe Streaming: https://estuary.dev/blog/snowpipe-streaming-fast-snowflake-ingestion/ - Discover how Snowflake fared in Estuary’s Data Warehouse Benchmark: https://estuary.dev/data-warehouse-benchmark-report/ - Download Snowflake Ingestion Playbook: https://estuary.dev/snowflake-ingestion-whitepaper/ FAQ 1. What is the fastest way to load data into Snowflake? Snowpipe Streaming with row-based ingestion. In Estuary, you can enable it per table using Delta Updates. 2. Why use key pair authentication for Snowflake? It provides strong security, short-lived tokens, and is Snowflake’s recommended approach for service integrations like Estuary. 3. Can I mix real-time and batch ingestion in the same pipeline? Yes. With Estuary’s Snowflake connector, you can run some tables in batch (COPY INTO or Snowpipe) and others in real time with Snowpipe Streaming. Media resources used in this video are from Pexels, Canva, and the YouTube Studio Audio Library. 0:00 Introduction 1:05 Snowflake concerns 1:51 Ingestion options 3:23 Beginning the demo 3:47 Create Snowflake resources 4:28 User auth setup 5:17 Estuary connector config 6:44 Customization options 8:07 Wrapping up
Seamless Data Integration, Unlimited Potential
Discover the simplest way to connect and move your data.
Get hands-on for free, or schedule a demo to see the possibilities for your team.