Prepare and test your skills
Prepare and test your skills
Extract, transform, and load (ETL) is a pipeline where data is cleaned and reshaped outside the warehouse before it is loaded. Data flows from source systems such as relational databases into a middle processing layer, then into BigQuery as the warehouse sink. The ordered steps are first extract data from sources, then transform it by cleaning, applying business rules, checking integrity, and creating aggregates, then load the finished result into the warehouse. Because heavy work runs outside the warehouse, warehouse compute stays free for concurrent queries. The tradeoff is extra tooling, since teams must use languages and tools beyond SQL, and each added source adds another possible failure point.
Extract, load, and transform (ELT) is a pipeline where raw data is loaded into BigQuery first and transformed inside the warehouse with SQL. Data flows from the source into raw BigQuery tables, then SQL workflows run in order of object dependencies to build tested tables and views for analysis. Dataform manages this in-warehouse work with declarative SQL that defines the desired tables, while Dataform figures out the steps to reach that state. This approach preserves the 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.
Extract, transform, load, and transform (ETLT) is a hybrid pipeline that splits transformation into a light step before loading and a fuller step 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.
Exam tip: ETL transforms data outside the warehouse before loading, while ELT loads raw data into BigQuery first and transforms it inside BigQuery with SQL.
The mapping of Google Cloud services to these architectures 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.
| Architecture | Sequence | Choose when |
|---|---|---|
| ETL | Extract, then transform outside warehouse, then load | Warehouse compute must stay free for queries, or data must be cleaned before landing |
| ELT | Extract, then load raw data, then transform in BigQuery | Raw data should be preserved and transformed with scalable SQL and tests |
| ETLT | Light transform, then load, then fuller transform in BigQuery | Sources need early cleanup, but final modeling belongs in the warehouse |
Storage Transfer Service (opens in a new tab) 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 both one-time migrations and repeated transfers such as backups and pipelines. 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 to the destination bucket. Agents run in Docker containers on computers that can see the NFS data, and all agents must see the same NFS mounts with the same relative paths. More agents give more speed because work is parallelized, while a bandwidth cap protects other workloads. The service preserves file names, supports full and incremental copies up to petabytes and billions of files, monitors health through Cloud Monitoring, and writes a full TSV record of files and errors that can be reviewed in BigQuery.
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 copied to the appliance streams directly to the Cloud Storage bucket. In online mode, data flows from the local network to the appliance to Cloud Storage with low latency and end-to-end encryption, and data is removed from the appliance after transfer. Offline use has higher overall latency, needs physical logistics, and is available only in certain countries. Time math drives the choice: 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.
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. Large online moves should avoid peak hours if they share production bandwidth.
| Tool | Best for | Network requirement |
|---|---|---|
gcloud storage command | Transfers under 1 TB, command-line copies, and syncing a moderate directory | Needs enough enterprise bandwidth to meet the deadline |
| Storage Transfer Service | Large online transfers over 1 TB up to petabytes, cloud-to-cloud and bucket-to-bucket moves, and scheduled copies | Needs sufficient bandwidth over internet, Direct Peering, or Cloud Interconnect |
| Transfer Appliance | Very large offline moves where bandwidth is missing or too slow | Works with poor or no connectivity, but needs shipping logistics |
Exam tip: Storage Transfer Service is optimized for online transfers over 1 TiB, while Transfer Appliance is recommended for offline moves over 20 TB up to 1 petabyte.
Checking validity and uniqueness means verifying that values follow business rules and that key identifiers appear only once. In Knowledge Catalog (opens in a new tab) (formerly Dataplex Universal 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 or trip_id is 100% unique, so a profile 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%.
| Rule type | What it validates | Example |
|---|---|---|
| Regex check | Value matches a required pattern | customer_id must match 8 letters followed by 16 digits |
| Range check | Numeric, date, or timestamp falls between min and max | discount_pct must be between 0 and 100 |
| Value set check | Value belongs to an allowed list | currency_id must be one of USD,JPY,INR,GBP,CAN |
A range check needs at least a min or a max plus a passing threshold, and it can use strict minimum, strict maximum, and ignore null options. 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.
Exam tip: A uniqueness check uses a threshold of Not Applicable, while row-level validity checks for regex, range, and set use a percentage threshold such as 100%.
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 proves the migration matched by running the same aggregations on source and target. DVT checks that tables and schema mappings are complete, matches row counts, samples random rows, validates columns with count, sum, avg, min, max, or group by, and compares hashes to catch hidden corruption. Dataform enforces consistency during pipeline execution with assertion queries that should return zero rows, such as a column never null or keys unique. If an assertion returns rows, the run is marked as failed so bad data never reaches dashboards.
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 for training. Common actions are checking for missing values and correcting them or leaving blanks where nullable, checking that forecasting intervals are consistent, and cleaning errors in spelling, abbreviations, and formatting. 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. When a model behaves unexpectedly, checking completeness is often the most effective fix.
Continue with this study guide: Assess data quality
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. Cleaning in the current environment can reuse existing processes but is limited by local capacity, while cleaning on Google Cloud provides virtually limitless capacity but may need extra transfer time.
Standardizing file formats reduces transfer complexity and catches type problems early. Apache AVRO, ORC, and Parquet embed schema with the data, while CSV needs a schema supplied at load time or auto-detected. For on-premises data with good bandwidth, the Google Cloud CLI supports parallel multi-threaded uploads that resume after errors and encrypt traffic in transit. When bandwidth is limited, teams use compression or Transfer Appliance for offline shipment.
Exam tip: Choose AVRO, ORC, or Parquet when you want the schema to travel with the data; choose CSV only when you plan to supply the schema separately or use auto-detection.
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. Because logic is visual, business users, developers, and data scientists can build and understand the same pipeline without managing infrastructure.
A typical visual cleaning 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 for analysis. Once defined, the pipeline can run on demand or on schedule so each new batch is cleansed the same way. Choose Cloud Data Fusion when the need is fast, managed, code-free cleaning across many sources rather than hand-written code.
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. Loading into BigQuery uses batch load, the Storage Write API, or BigQuery Data Transfer Service, and BigQuery supports nested and repeated fields for denormalization on columnar storage.
Dataflow is a serverless platform that defines and runs complex transformation and enrichment graphs with the Apache Beam model. 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 JSON or CSV samples, fix inconsistencies, and then run the full load through Dataflow. The open source Data Validation Tool confirms the result by comparing source and target with aggregations such as count, sum, and average.
| Tool | How cleaning is built | Choose when |
|---|---|---|
| Cloud Data Fusion | Graphical pipeline with preconfigured connectors | Teams need managed, code-free ETL/ELT across many sources |
| BigQuery with SQL | SQL queries and scheduled Dataform pipelines | Data is already loaded and cleaning is deduplication and type casting |
| Dataflow | Apache Beam pipeline with autoscaling | Cleaning is complex, very large, enriched, or streaming |
gcloud storage command is typically used for transfers under 1 TB.Extract, transform, and load (ETL) extracts data from sources, transforms it outside the warehouse by cleaning, applying business rules, checking integrity and creating aggregates, then loads the finished result into BigQuery. Extract, load, and transform (ELT) loads raw data into BigQuery first and then transforms it inside the warehouse with SQL workflows managed by Dataform. Extract, transform, load, and transform (ETLT) is a hybrid that does a light cleaning, filtering or replication handling step before loading and then a fuller business modeling step in BigQuery with SQL.
Storage Transfer Service is a fully managed online service that moves data over the network into Cloud Storage and is optimized for transfers larger than 1 TiB up to petabytes. Transfer Appliance is Google-owned hardware for offline migration that is recommended for very large moves larger than 20 TB up to 1 petabyte where online transfer would take too long or bandwidth is missing. For example, 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.
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 key identifier appears only once with a threshold of Not Applicable, so less than 100 percent unique signals duplicates from repeated records or ingestion errors. A validity check verifies that each row matches a required regex pattern, range or allowed value set with a percentage threshold such as 100 percent.
Last updated