Data Preparation and Ingestion for BigQuery

1.1ETL With Transformation Before Loading

Extract, transform, and load (ETL) is a pipeline that cleans and reshapes data before it lands in the warehouse. Data flows from source systems such as relational databases into a middle processing layer, then into BigQuery as the warehouse sink. The order is fixed: first extract data from sources, then transform it by cleaning values, applying business rules, checking integrity, and creating aggregates, then load the finished result. Because heavy work runs outside the warehouse, warehouse compute stays free for concurrent queries. The tradeoff is extra tooling and failure points, since teams must use languages beyond SQL and each new source adds another place that can fail.

1.2ELT With Transformation Inside BigQuery

Extract, load, and transform (ELT) is a pipeline that loads raw data into BigQuery first and transforms it inside the warehouse with SQL. Data flows from the source into raw BigQuery tables, then SQL workflows run in dependency order to build tested tables and views for analysis. Dataform manages this work with declarative SQL that defines the desired tables, while Dataform figures out the steps to reach that state. This approach preserves raw data and uses BigQuery scale for transformation, testing, documentation, and version control. BigQuery pipelines, powered by Dataform and managed in BigQuery Studio, can run both ELT and ETL processes.

1.3ETLT With Two Transformation Stages

Extract, transform, load, and transform (ETLT) is a hybrid pipeline with a light transform before loading and a fuller transform after loading. Data flows from the source system to a pipeline service for initial cleaning, filtering, or replication handling, then to BigQuery for loading, then through in-warehouse SQL for final business modeling. This pattern fits migrations and complex sources, such as capturing Oracle data, handling schema updates, and then using Dataform to build tested tables, views, or materialized views. Choose ETLT when raw data needs early cleanup but the main logic still benefits from BigQuery SQL and dependency management.

1.4How Cloud Services Map to ETL, ELT, and ETLT

The mapping of Google Cloud services to ETL, ELT, and ETLT is based on where transformation compute runs. Cloud Data Fusion provides visual drag-and-drop pipelines with source plugins, sink plugins, and Wrangle for code-free cleaning, while Dataflow runs custom Python or Java pipelines with the Apache Beam SDK for both batch and streaming. Managed Service for Apache Airflow orchestrates the steps with directed acyclic graphs (DAGs), where a data flow DAG moves data from sources to sinks and a control flow DAG coordinates work between pipelines. Batch pipelines process data collected over time such as once per day, while streaming pipelines handle real-time events such as change data capture from operational databases.

1.5What Storage Transfer Service Moves Online

Storage Transfer Service is a fully managed online service that moves data over the network into Cloud Storage without custom code. Data flows from the source system over the network to a Cloud Storage bucket, with integrity validation, automatic retries, load balancing, and highly parallel transfer. The service is optimized for transfers larger than 1 TiB up to petabytes and supports one-time migrations and repeated transfers such as backups. Supported paths include Amazon S3, Azure Blob Storage, or Cloud Storage to Cloud Storage, on-premises storage to Cloud Storage and back, between on-premises systems, from HDFS to Cloud Storage, and from public URLs to Cloud Storage. For on-premises Network File System (NFS) data, data flows from the NFS file system through transfer agents in Docker containers to the destination bucket, where all agents must see the same mounts with the same paths and more agents give more speed.

1.6How Transfer Appliance Moves Data Offline

Transfer Appliance is Google-owned hardware for offline migration of very large volumes to Cloud Storage. Data flows from on-premises storage to the appliance during local copy, then the shipped appliance travels to Google where data is ingested to Cloud Storage and the device is wiped. Data does not travel over the business internet during capture, so production work is not disrupted. The appliance is recommended for moves larger than 20 TB up to 1 petabyte and fits one-time bulk migrations where online transfer would take too long. Acquiring it follows a fixed order: first request appliances in the console, then receive and copy data during the capture period, then ship them back, then Google uploads to the bucket. Transfer Appliance can also run in online mode, where data flows from the local network to the appliance to Cloud Storage with low latency and end-to-end encryption.

1.7Choosing a Transfer Tool by Size, Time, and Network

Choosing a transfer tool means matching data volume, usable bandwidth, deadline, and frequency to the right path. Ingress to Cloud Storage is free, but moves from another cloud can incur egress and read charges, and private data centers may need added bandwidth. A realistic time estimate uses dataset size, available bandwidth, bandwidth efficiency, and management time for setup and retries. Time math drives the choice because 100 TB takes about 12 days at 1 Gbps but over 100 days at 100 Mbps, while 300 TB takes about 9 months at 100 Mbps online versus under 25 days of capture plus about 10 business days with Transfer Appliance.

1.8Checking Validity and Uniqueness With Data Quality Scans

Checking validity and uniqueness means verifying that values follow business rules and that key identifiers appear only once. In Knowledge Catalog, a data quality scan is a DataScan object that validates one BigQuery table and reports pass rates, failure counts, and failing records. A uniqueness check verifies that a column such as transaction_id is 100% unique, so a metric like 98% unique signals duplicates from repeated records or ingestion errors. A validity check verifies that each row matches a required format, pattern, range, or allowed set, and it fails when passing rows fall below a threshold such as 100%. Using a scan follows an order: first define rules, then configure scope and schedule, then analyze results, then set up monitoring and alerting, then troubleshoot failures by fixing source data or pipeline logic.

1.9Checking Accuracy and Consistency After Migration

Checking accuracy and consistency means confirming that stored values match real source values and give the same answer in every system. Data typically flows from the existing warehouse through staging into BigQuery in a fixed order: first extract schema and data into files such as Avro, ORC, or Parquet, then upload files to a Cloud Storage bucket that acts as the staging boundary, then Dataflow reads from the bucket, transforms, and writes to BigQuery, then validate the target against the source. Validation must run only after loading, and transforming on Google Cloud avoids the compute limits of the source environment. The Data Validation Tool (DVT) is an open source Python tool that runs the same aggregations on source and target to prove the match, checking schema completeness, row counts, random row samples, column count, sum, avg, min, max, and hashes.

1.10Checking Completeness Before Training

Checking completeness means confirming that a dataset has all the values and rows needed for reliable processing. Gaps such as missing values, nulls, inconsistent spelling, or uneven time intervals make data less useful for training, and biased or erroneous values carry through into poor predictions. Agent Platform shows an overview after import with each column, its detected type, and its nullability, which reveals where blanks are allowed and where they signal a problem. For forecasting, a gap can also be a missing row when intervals between training rows are not consistent. Handling gaps means correcting what can be fixed and preparing the rest, since Agent Platform can process nullable blanks and can impute missing forecasting rows, but results are most optimal when all values are present and intervals are consistent.

1.11Deciding Where Cleaning Happens in the Load Flow

Deciding where cleaning happens means choosing whether to clean before loading into BigQuery or after loading inside BigQuery. In ETL, data is trimmed, cleansed, or enriched before loading, while in ELT, raw data is loaded first and then cleaned with SQL inside BigQuery using its scalable compute. Data moves in order from source files to staging storage to a Cloud Storage bucket to BigQuery tables: first extract schema and data into files, then upload to a bucket close to the data center and ideally in the same location as the BigQuery dataset, then transform and load with an ETL or ELT tool, then validate counts and values against the source. Standardizing file formats reduces complexity because Apache AVRO, ORC, and Parquet embed schema with the data, while CSV needs a schema supplied at load time or auto-detected.

1.12Cleaning Visually With Cloud Data Fusion

Cloud Data Fusion is a fully managed, cloud-native data integration service powered by CDAP that builds code-free ETL and ELT pipelines through a graphical interface. Data flows from sources through source plugins into transformation steps, then through sink plugins such as the BigQuery plugin to the destination. The service can cleanse inconsistent, incomplete, or duplicate values, prepare fields by trimming and standardizing, blend data from disparate sources, and handle schema updates on load. A typical flow runs in order: first connect the source by reading staged files from Cloud Storage or through a connector such as JDBC, then explore samples and apply prebuilt directives to fix anomalies, then load the cleaned output to BigQuery. Choose Cloud Data Fusion when the need is fast, managed, code-free cleaning across many sources rather than hand-written code.

1.13Cleaning at Scale With BigQuery SQL and Dataflow

Cleaning at scale with BigQuery SQL and Dataflow covers post-load SQL cleaning and large custom transformations. BigQuery is the warehouse where cleaned data lands and where GoogleSQL queries trim, filter, deduplicate, cast types, and handle nulls after loading. Dataform builds, version-controls, and orchestrates those SQL pipelines on a schedule, while facade views with UNION can combine old and new tables so upstream changes do not break dashboards. Dataflow is a serverless platform that defines and runs complex transformation graphs with the Apache Beam model, where data flows from sources such as Cloud Storage through Beam transforms in Dataflow to BigQuery through the BigQuery I/O connector, with autoscaling for batch and streaming. Dataprep by Trifacta pairs with it by letting analysts visually explore samples and then run the full load through Dataflow.

2.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 (opens in a new tab), and Cloud SQL can import CSV files for relational storage. CSV is simple and widely supported but has no embedded types or nesting and can break on commas inside values, while JSON is flexible for events and API responses but is verbose and slower to scan. For Cloud SQL, the import depends on an existing destination, so the operator must specify the Database and Table that will receive the rows.

2.2Apache Parquet and Apache Avro as Binary Formats

Apache Parquet 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, while Avro fits streaming ingestion because blocks can be read in parallel even when compressed and fields can change without breaking readers. BigQuery supports Snappy, DEFLATE, and ZSTD compression for Avro, and loading Avro files into a new table automatically creates the schema from the alphabetically last file when schemas are compatible.

2.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, so choose files when the goal is fast ingestion, exchange, or backup, and choose native tables when the goal is repeated, cost-effective SQL analysis.

2.4BigQuery Data Transfer Service for Scheduled Loads

BigQuery Data Transfer Service 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, including SaaS platforms such as Salesforce, marketing platforms such as Facebook Ads and Stripe, databases and warehouses such as MySQL and Snowflake, cloud storage such as Amazon S3, and Google services such as Google Ads and YouTube. Data flows only into BigQuery, because the service cannot transfer data out of BigQuery.

2.5Cloud 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.

2.6Dataflow 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 pattern moves data in order: first stage extracted files from temporary on-premises storage to a Cloud Storage staging bucket, then data flows from the bucket into the Dataflow pipeline where Beam transforms each record, then data flows from Dataflow to BigQuery through the BigQuery I/O connector. Choose Dataflow when data must be trimmed, joined, enriched, or reshaped, especially for both one-time bulk loads and ongoing arrivals.

2.7Matching 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 still uses Cloud Storage for staging even for direct warehouse-to-BigQuery moves from systems like Amazon Redshift and Teradata. The choice balances automation against control, since scheduled no-code loads need the least work and Beam code handles the most complex graphs.

2.8Structured, Unstructured, and Semi-Structured Requirements

Structured, unstructured, and semi-structured requirements describe how fixed the schema is and which system should hold the data. Structured data has a fixed schema and fits rows and columns, where Cloud SQL provides relational storage with full SQL for online transaction processing (OLTP) and BigQuery provides interactive querying for online analytical processing (OLAP). Unstructured data has no fixed schema and is stored as whole objects such as PDFs, images, and backups, where Cloud Storage stores objects inside buckets and applications access objects by ID. When unstructured documents need descriptive fields, the files stay in Cloud Storage while a BigQuery table holds the metadata and the uri field points to the Cloud Storage location. Semi-structured data has fields and nesting without a shared schema, where Firestore in Datastore mode fits profiles and catalogs and Bigtable fits very large, high-throughput workloads without ACID transactions.

2.9Storage 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, regional storage replicates synchronously across zones and survives a zonal failure, and 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.

2.10Residency, 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, Nearline for infrequent access with at least 30 days retention, Coldline for at least 90 days, Archive for at least 365 days, and Autoclass for automatic transitions when access is unpredictable. Databases 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.

2.11BigQuery Data Transfer Service Configuration and Scheduling

BigQuery Data Transfer Service configuration and scheduling define how recurring ingestion runs without code. Data flows from the source system to a destination BigQuery dataset after a transfer configuration is created. Source files for Cloud Storage transfers 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. A console transfer is created in order: first select source type, then enter a display name, then set schedule frequency or event-driven Pub/Sub subscription, then select the destination dataset and existing table, then specify data path, file format, write preference, and format options. Before creating a Cloud Storage transfer, the practitioner creates the destination dataset and table with its schema in advance, and write preference defaults to APPEND so an unmodified file loads only once.

2.12Storage Transfer Service for Object and File Movement

Storage Transfer Service for object and file movement is a fully managed service that moves large amounts of 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 quickly, with supported combinations including Amazon S3, Azure Blob Storage, POSIX file systems, HDFS, and HTTP URLs to Cloud Storage. A typical job setup follows this order: first choose source and destination, then configure source access, then specify the destination bucket, then choose one-time, repeating, or event-driven scheduling, then set overwrite, delete, and metadata options, then enable logging and run. Destination behavior is controlled by Never, If different, and Always overwrite options, plus delete options that can mirror the source.

2.13gcloud and bq CLI for Scripted Loading

The Google Cloud CLI 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, since sample tables live in the US multi-region and cannot be joined or written across regions. Loading a CSV into Cloud SQL follows an order: first create a Cloud Storage bucket, then upload the file, then grant the Cloud SQL service account the storage.objectAdmin role, then run the import such as gcloud sql import csv with instance, path, database, and table.

Key Facts

• ETL transforms data outside the warehouse before loading, ELT loads raw data into BigQuery first and transforms inside with SQL, and ETLT does a light transform before loading plus a fuller transform after.
• Storage Transfer Service is optimized for online transfers larger than 1 TiB up to petabytes, while Transfer Appliance is recommended for offline moves larger than 20 TB up to 1 petabyte.
• A data quality scan validates one BigQuery table, where uniqueness checks detect duplicates and row-level validity checks use regex, range, and value-set rules.
• Apache Parquet is columnar for analytics that scan selected columns, while Apache Avro is row-based with schema evolution for streaming ingestion.
• BigQuery Data Transfer Service automates scheduled loads only into BigQuery, while regional storage survives zonal failure and dual-regional and multi-regional storage survive regional failure with RTO = 0.