Extract Data with Dataflow and Load into BigQuery

Distinguishing data formats means matching structure, schema handling, and read pattern to the right ingestion and analysis task. Text formats favor exchange and flexibility, binary formats favor speed and size, and managed tables favor enforced structure and repeated SQL analysis. Data typically lands first as files in Cloud Storage and then moves into a table system for querying.

1.1CSV and JSON as Text Exchange Formats

CSV and JSON are plain-text formats that store data before it is loaded into tables. Comma-Separated Values (CSV) is row-based, with one line per row and commas separating flat values. JavaScript Object Notation (JSON) stores records as key-value objects that can contain nested arrays and objects. BigQuery (opens in a new tab) can load both formats directly from Cloud Storage, and Cloud SQL can import CSV files for relational storage.

CSV is simple, human-readable, and widely supported by spreadsheets and export tools. CSV has no embedded data types or nesting, produces larger scans than compressed formats, and can break when commas or line breaks appear inside values. JSON is flexible and readable for event data, API responses, and semi-structured records where fields vary or nest. JSON files are verbose and slower to scan because each full record must be read and parsed.

For import into Cloud SQL, both uncompressed .csv and compressed .gz files are supported. The import depends on an existing destination, so the operator must specify the Database and Table inside the Cloud SQL instance that will receive the rows. For BigQuery, a JSON load can use autodetect schema, where BigQuery infers field names and types, or an explicit schema supplied with the load job.

1.2Apache Parquet and Apache Avro as Binary Formats

Apache Parquet (opens in a new tab) and Apache Avro are binary formats built for efficient storage and movement. Parquet is columnar and groups values by column with built-in compression and an embedded schema. Avro is row-based and stores records row by row with a compact embedded schema and strong support for schema evolution. Both are much smaller and faster to load than CSV or JSON.

Parquet fits large, mostly read-only analytics because a query can read only the columns it needs instead of scanning entire rows. Parquet is not human-readable and is a poor fit for frequent single-row updates. Avro fits streaming ingestion and ongoing transfers because data blocks can be read in parallel even when compressed, and fields can be added or changed without breaking existing readers. For queries that scan a few columns over billions of rows, Avro is less efficient than a columnar format.

BigQuery supports Snappy, DEFLATE, and ZSTD compression for Avro file contents. Avro files carry their own schema, so loading Avro files into a new BigQuery table automatically creates the table schema. When multiple Avro files load together, all schemas must be compatible and BigQuery derives the new table schema from the alphabetically last file.

1.3Structured Tables Compared With Portable Files

Structured database tables are managed objects with enforced column names, types, and modes for consistent SQL access. An Avro or Parquet file in Cloud Storage is portable but must be parsed on every read, while a BigQuery native table stays compressed, stays organized, and supports partitioning, clustering, and column statistics. BigQuery stores data in Colossus in a compressed columnar format called Capacitor and can operate directly on compressed data. Query execution scales separately with the Dremel engine, while storage is allocated as data arrives.

Datasets are the highest-level containers that organize access to tables, while schemas and labels provide further organization inside datasets. Partitioning and clustering limit the data scanned, which improves performance and cost and helps manage the information lifecycle. Choose files when the goal is fast ingestion, exchange, or backup, and choose native tables when the goal is repeated, cost-effective SQL analysis.

Choosing an extraction tool means matching how work is defined and how much transformation is needed to the right managed service. BigQuery Data Transfer Service automates scheduled loads without code, Cloud Data Fusion builds visual pipelines without code, Dataflow runs custom code-defined transformations at scale, and Database Migration Service migrates operational databases. All except Database Migration Service focus on loading warehouse and analytics data into BigQuery.

2.1BigQuery Data Transfer Service for Scheduled Loads

BigQuery Data Transfer Service (opens in a new tab) is a managed service that automates data movement into BigQuery on a regular schedule. After a transfer configuration is created, data flows from the source system to the destination BigQuery dataset automatically, and backfills can recover from outages or gaps. The transfer configuration lives in the same location as the destination dataset, and the service processes and stages data in that same location.

The service supports many source groups without building a pipeline:

• SaaS platforms such as Salesforce and ServiceNow
• Marketing and payment platforms such as Facebook Ads, HubSpot, Mailchimp, PayPal, Stripe, and Shopify
• Databases and warehouses such as MySQL, PostgreSQL, Oracle, Amazon Redshift, Snowflake, and Teradata
• Cloud storage such as Cloud Storage, Amazon S3, and Azure Blob Storage
• Google services such as Google Ads, Google Analytics 4, Google Play, and YouTube

Data flows only into BigQuery, because the service cannot transfer data out of BigQuery. In addition to external loads, the service also powers dataset copies and scheduled queries inside BigQuery. Choose this service when the need is recurring, automated ingestion from a supported source directly into BigQuery.

2.2Cloud Data Fusion for Visual Pipelines

Cloud Data Fusion is a fully managed service for creating ETL and ELT pipelines on a graphical canvas. A developer drags connectors and transformations onto the canvas instead of writing extraction code, and the service runs the resulting pipeline on Google Cloud. The pipelines reuse an open-source library of preconfigured connectors and transformations, so common sources and cleaning steps do not have to be rebuilt.

Data flows from the source system through the visual pipeline steps into the destination such as BigQuery, with transformation happening as part of the load. Choose Cloud Data Fusion when extraction requires visual construction plus cleaning, joining, or enrichment before loading. The tradeoff is less control than code for very complex processing graphs.

2.3Dataflow for Custom Batch and Streaming Transformation

Dataflow is a serverless and fully managed data-processing service that runs both bulk and continuously arriving data with one model. A pipeline is defined with the Apache Beam model and run by the Dataflow service, which provides virtually limitless capacity without servers to manage. Because transformation runs on Google Cloud, teams avoid extra transfer time and bandwidth that enrichment in the source environment would require.

A common extraction and load pattern moves data through an ordered sequence:

1. Stage in Cloud Storage: Extracted files are copied from temporary on-premises storage to a Cloud Storage staging bucket.
2. Process in Dataflow: Data flows from the staging bucket into the Dataflow pipeline, where Beam transforms and enriches each record.
3. Load to destination: Data flows from Dataflow to the destination, such as directly to BigQuery through the BigQuery I/O connector.

The pipeline depends on Beam I/O connectors for sources and destinations, including BigQueryIO, BigtableIO, AvroIO, and ParquetIO. Google-provided templates cover both modes, with streaming templates such as Bigtable change streams to BigQuery and batch templates such as Cloud Storage Avro to Bigtable and Cloud Storage Parquet to Bigtable. Choose Dataflow when data must be trimmed, joined, enriched, or reshaped, especially when the same logic must serve both one-time bulk loads and ongoing arrivals.

2.4Matching Tools to Migration Needs

Matching tools to migration needs depends on coding preference, transformation depth, and starting point. For an initial warehouse load, the common pattern is to export schema and data to vendor-neutral files such as Avro, ORC, or Parquet, upload those files to a Cloud Storage bucket in the same location as the BigQuery dataset, and then load through the transfer service, Dataflow, or Cloud Data Fusion. Large dataset transfer (opens in a new tab) still uses Cloud Storage for staging even for direct warehouse-to-BigQuery moves from systems like Amazon Redshift and Teradata.

Selecting a storage solution means matching data shape, workload type, and placement needs to the right Google Cloud system. Structured data fits tables with SQL, unstructured data fits objects in Cloud Storage (opens in a new tab), and semi-structured data fits document and wide-column stores. Location type then controls where bucket data physically lives and how it survives outages.

3.1Structured, Unstructured, and Semi-Structured Requirements

Structured data has a fixed schema and fits rows and columns with known names and types. Cloud SQL provides a relational database with full SQL support for online transaction processing (OLTP), where a table holds rows, each row holds columns, and a primary key gives each row a unique ID. BigQuery provides interactive querying for online analytical processing (OLAP) over large tabular datasets. Firestore in Datastore mode shows the same structured idea in NoSQL form, where kind maps to table, entity maps to row, property maps to column, and key maps to primary key.

Unstructured data has no fixed schema and is stored and read as whole objects. Common examples include PDFs, images, videos, backups, and archives. Cloud Storage stores data as objects inside buckets where each object has a globally unique ID plus metadata, and applications access objects by ID from inside and outside Google Cloud. When unstructured documents need descriptive fields, the files stay in Cloud Storage while a BigQuery table or NDJSON file holds the metadata, and the uri field in each record points to the Cloud Storage location.

Semi-structured data has fields, keys, and nested objects but does not require every record to share the same schema. Firestore in Datastore mode is the document option for user profiles and catalogs that need automatic scaling, high availability, strong consistency, and atomic transactions. Bigtable is the wide-column option for very large, high-throughput workloads that do not require ACID transactions. Datastore queries scale with the size of the result set rather than the size of the dataset, while Bigtable favors scale and throughput over transactions.

3.2Storage Location Types and Failover

Cloud Storage location type is chosen when the bucket is created and is permanent. Regions are independent geographic areas designed as independent failure domains, while zones are deployment areas inside a region that act as single failure domains. Regional, dual-regional, and multi-regional buckets provide automated failover and failback with recovery time objective (RTO) = 0 because Google Cloud manages redirection automatically.

Zonal storage keeps copies inside one zone, so a zonal outage can affect availability but performance is highest when compute and storage sit together. Regional storage replicates synchronously across zones in the same region and survives a zonal failure. Dual-regional and multi-regional storage replicate asynchronously across regions and survive a regional failure. Dual-regional buckets support Turbo replication, which replicates 100% of newly written objects between the paired regions within 15 minutes.

Choose zonal when the workload needs the highest performance in one zone, regional when the workload runs in one region and needs low latency to nearby compute, dual-regional when the workload runs in two regions and needs fast pair replication, and multi-regional when the workload needs wide geographic availability.

3.3Residency, Access Frequency, and Retention

Data residency controls determine the physical location of data plus the regulations that govern storage, encryption, and access. Cloud SQL supports compliance by letting the instance region control where data rests, including backups, and a cross-region read replica places a readable copy in a designated region. Organizational policy constraints enforce placement at the organization, folder, or project level by defining where users can create resources.

Access frequency further refines the Cloud Storage choice:

• Standard for frequently accessed data such as data lakes and streaming video
• Nearline for infrequent access with at least 30 days retention
• Coldline for infrequent access with at least 90 days retention
• Archive for infrequent access with at least 365 days retention
• Autoclass for automatic transitions when access is unpredictable

Databases (opens in a new tab) for structured work follow a simple rule: choose Cloud SQL for highly structured OLTP work with full SQL and ACID transactions, and choose BigQuery for highly structured OLAP work with interactive analysis over large tables.

Loading data with the appropriate tool means choosing between command-line loads, managed transfers into BigQuery, and large object transfers into Cloud Storage. The bq command-line tool runs BigQuery jobs from a terminal, gcloud provides authentication for scripts, BigQuery Data Transfer Service (opens in a new tab) automates scheduled ingestion, and Storage Transfer Service moves files and objects into Cloud Storage.

4.1BigQuery Data Transfer Service Configuration and Scheduling

BigQuery Data Transfer Service automates scheduled ingestion without writing code. Data flows from the source system to a destination BigQuery dataset after a transfer configuration is created. Supported sources include SaaS platforms such as Salesforce and ServiceNow, marketing and payment platforms such as Facebook Ads and Stripe, databases and warehouses such as MySQL, Oracle, Snowflake, and Amazon Redshift, external storage such as Amazon S3 and Azure Blob Storage, and Google services such as Google Ads, Google Analytics 4, YouTube, and Cloud Storage.

Scheduled ingestion runs automatically after the configuration is saved. Source files for Cloud Storage transfers (opens in a new tab) are picked up immediately with no minimum file age, the minimum interval between recurring transfers is 15 minutes, and the default interval is every 24 hours. An event-driven transfer that uses a Pub/Sub subscription can trigger runs when new events arrive. The transfer configuration is always set to the same location as the destination dataset, and the service processes and stages data in that same location.

A console transfer is created in an ordered workflow:

1. Select source: Choose the source type, such as Google Cloud Storage or Amazon S3, as the origin of the flow.
2. Name configuration: Enter a display name that identifies the transfer for later changes.
3. Set schedule: Choose Hours, Days, Weeks, Months, Custom, On-demand, or Event-driven, then set frequency or subscription.
4. Set destination: Select the destination dataset and the existing native or managed table that receives the data.
5. Set load details: Specify the data path, file format, write preference, and format options so files flow from the source path to the destination table.

Before creating a Cloud Storage transfer, the destination must already exist. The practitioner creates the destination dataset, creates the destination table with its schema in advance, and confirms encryption and key access when using a customer-managed encryption key. To create a transfer, the identity needs BigQuery Admin or equivalent permissions plus storage.objects.get on the bucket, with storage.objects.list for wildcards and storage.objects.delete when source files should be deleted after transfer. Write preference defaults to APPEND, so an unmodified file loads only once.

4.2Storage Transfer Service for Object and File Movement

Storage Transfer Service is a fully managed service that moves large amounts of object and file data into Cloud Storage without custom scripts. Data flows from a configured source to a destination Cloud Storage bucket, and the service handles scale, speed, and reliability automatically. The service is optimized for transfers larger than 1 TiB and can move petabytes of data quickly.

Supported combinations include Amazon S3, Azure Blob Storage, or Cloud Storage to Cloud Storage, on-premises POSIX file systems to Cloud Storage or the reverse, transfers between on-premises systems, publicly accessible HTTP or HTTPS URLs to Cloud Storage, and Hadoop Distributed File System (HDFS) to Cloud Storage. Jobs can be created through the console, gcloud, REST APIs, and Java and Python client libraries. Transfers can use Google-managed infrastructure or self-hosted agents in an agent pool for control over routing and bandwidth.

A typical job setup follows this order:

1. Choose source and destination: Select the source type and select Cloud Storage as the destination type.
2. Configure source access: Provide credentials or select an agent pool and fully qualified source directory path.
3. Specify the destination: Enter the destination bucket and optional folder name.
4. Choose scheduling: Select a one-time batch run, a repeating batch schedule, or event-driven continuous monitoring.
5. Set overwrite, delete, and metadata options: Define handling of matching names, deletions, and preserved metadata.
6. Enable logging and run: Turn on logging, create the job in ENABLED status, and start now or by schedule.

Scheduling controls when data moves: batch transfers run once or on a repeating schedule, while event-driven transfers continuously monitor the source. Destination behavior is controlled by overwrite and delete settings:

Delete options include Never, Delete file from source after transfer, and Delete files from destination if not at source so the destination exactly matches the source. The storage class can be specified for transferred objects, but that setting is ignored when the destination bucket has Autoclass enabled, in which case objects initially receive Standard storage. All HTTPS communication supports TLS 1.3, and integrity validation, retries, and parallel execution keep large transfers reliable.

4.3gcloud and bq CLI for Scripted Loading

The Google Cloud CLI (gcloud) and the bq tool provide a scriptable alternative to the console for data preparation and ingestion. A practitioner can compose a query in the console, run bq query in the terminal, call the BigQuery REST API, or use client libraries. BigQuery jobs run through the same backend no matter which interface starts them, generating an execution tree that breaks work into parallel stages and moves intermediate data through a fast distributed shuffle tier.

The bq tool must specify the correct processing location when reading location-bound data. Sample tables are stored in the US multi-region, so commands use --location=US, the console uses US as the processing location, or the API specifies location in jobReference. Sample table results cannot be written to a table in another region, and sample tables cannot be joined with tables in another region.

The gcloud tool provides identity and project context that scripts depend on. A common pattern obtains an access token with gcloud auth print-access-token and passes it as a Bearer token in the Authorization header of a curl POST request. The destination service then validates the token before accepting the import or load. For batch loading (opens in a new tab), troubleshooting starts with billing, API enablement, and IAM roles such as BigQuery Admin, BigQuery Job User, and Storage Object Viewer.

Loading a CSV file into Cloud SQL follows an ordered workflow:

1. Create bucket: Create a Cloud Storage bucket to hold the source file.
2. Upload file: Upload the CSV or compressed file to the bucket.
3. Grant access: Grant the Cloud SQL instance service account the storage.objectAdmin role on the bucket.
4. Import: Run the import, for example gcloud sql import csv, with the instance name, the gs://BUCKET_NAME/FILE_NAME path, and the --database and --table values.

Key Facts

• Apache Parquet is a binary columnar format for analytics that scans selected columns, while Apache Avro is a binary row-based format with embedded schema and evolution for streaming ingestion.
• BigQuery Data Transfer Service automates scheduled loads and backfills only into BigQuery and cannot transfer data out of BigQuery.
• Dataflow runs Apache Beam pipelines for unified batch and streaming transformation, with data flowing from Cloud Storage into Dataflow and then to BigQuery.
• Regional storage replicates synchronously across zones while dual-regional and multi-regional storage replicate asynchronously across regions, all with automatic failover and RTO = 0.
• Storage Transfer Service moves object data from sources including Amazon S3, Azure Blob Storage, POSIX file systems, HDFS, and HTTP URLs into a Cloud Storage bucket with Never, If different, and Always overwrite options.