This is the largest domain, about 30% of the exam. Questions give you a source (files on premises, a SaaS app, a database, a stream) and a destination, then ask which tool moves or cleans the data with the least effort. Most answers are managed services; custom code is rarely the best choice.
ETL, ELT and ETLT
| Pattern | Order | Where the transform runs | Typical Google Cloud tools |
|---|---|---|---|
| ETL | Extract, transform, load | In a processing engine before the warehouse | Dataflow, Cloud Data Fusion, Dataproc |
| ELT | Extract, load, transform | Inside the warehouse after loading | BigQuery SQL, Dataform, scheduled queries |
| ETLT | A light transform first (mask, filter, parse), load, then heavy transform in the warehouse | Both | Dataflow, then BigQuery SQL |
- Choose ELT when the data lands in BigQuery anyway, the team knows SQL and you want to keep the raw data so you can re-transform later. It is the default modern answer for batch analytics.
- Choose ETL when data must be cleaned, masked or reshaped before it is stored (for example removing personal data before it reaches the warehouse), when the target is not a SQL engine, or when you need streaming transforms.
- Choose ETLT when you need a small amount of pre-processing (such as dropping sensitive columns) and then SQL modelling.
- Trap: “schema-on-read” raw storage in Cloud Storage plus external tables is a form of ELT; the transform happens when you query.
Assessing data quality
Check these dimensions before you trust a data set:
- Completeness: missing values and null rates per column.
- Accuracy and validity: values inside expected ranges, correct types, valid formats (dates, emails, country codes).
- Consistency: the same fact looks the same across tables and systems (units, spellings, time zones).
- Uniqueness: duplicate rows or duplicate keys.
- Timeliness: how fresh the data is compared with what the business needs.
- Integrity: foreign keys point at rows that exist.
In practice you profile with SQL (COUNT(*), COUNTIF(col IS NULL), COUNT(DISTINCT key), MIN/MAX, GROUP BY ... HAVING COUNT(*) > 1), with Cloud Data Fusion’s Wrangler profile view, or with Dataplex data profiling and data quality scans, which run rules (non-null, range, regex, uniqueness, SQL assertions) on BigQuery tables and report a score.
Cleaning data
| Task | How |
|---|---|
| Remove duplicates | SELECT DISTINCT, ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) = 1 with QUALIFY, or a Dataflow Distinct |
| Handle nulls | COALESCE, IFNULL, filter out, or impute in a notebook |
| Fix types and formats | CAST, SAFE_CAST (returns NULL instead of an error), PARSE_DATE, PARSE_TIMESTAMP |
| Standardise text | TRIM, LOWER, REGEXP_REPLACE |
| Flag outliers | Compare with percentiles or standard deviation (APPROX_QUANTILES, STDDEV) |
| Reshape | UNNEST for arrays, STRUCT fields, PIVOT/UNPIVOT |
| Point-and-click | Cloud Data Fusion Wrangler (visual directives), or Dataprep-style tools |
| Code at scale or streaming | Dataflow (Apache Beam) |
Which tool? If the data is already in BigQuery, cleaning with SQL is cheapest and simplest. Use Cloud Data Fusion when the team wants a visual, no-code pipeline with prebuilt connectors. Use Dataflow for large or streaming transforms that need code.
Choosing a data transfer tool
| Situation | Tool |
|---|---|
| Tens of TB to PB, limited bandwidth, offline is acceptable | Transfer Appliance (a physical device shipped to you; you load it and ship it back) |
| Online copy between Cloud Storage buckets, from Amazon S3, Azure Blob, HTTP lists or on-premises file systems; scheduled or recurring | Storage Transfer Service (on-premises needs a transfer agent installed) |
| Few files from a workstation | gcloud storage cp or the console upload |
| SaaS apps and other Google products into BigQuery on a schedule (Google Ads, YouTube, Campaign Manager, Amazon S3, Cloud Storage, Teradata, Redshift) | BigQuery Data Transfer Service |
| Move a running database to Cloud SQL or AlloyDB with minimal downtime | Database Migration Service |
| Continuous change data capture from Oracle, MySQL, PostgreSQL, SQL Server into BigQuery or Cloud Storage | Datastream |
Rules of thumb: network is fast enough and data is files, use Storage Transfer Service; network is too slow or the volume is huge, use Transfer Appliance; the source is a database and you must keep replicating, use Datastream; the source is a SaaS or ad platform and the target is BigQuery, use BigQuery Data Transfer Service.
Data formats
| Format | Type | Schema | Layout | Notes |
|---|---|---|---|---|
| CSV | Structured text | None; you supply or auto-detect | Row | Human readable, large, no types, delimiter and header problems |
| JSON (newline-delimited for BigQuery loads) | Semi-structured | Self-describing | Row | Nested and repeated fields; BigQuery needs newline-delimited JSON |
| Avro | Binary | Embedded in the file | Row | Best for loads and streaming; fast writes; schema evolution; BigQuery reads the schema from the file |
| Parquet | Binary | Embedded | Columnar | Best for analytics; compressed; reads only needed columns; good for data lakes |
| ORC | Binary | Embedded | Columnar | Common in Hive/Hadoop |
- Loading Avro, Parquet or ORC into BigQuery is faster and cheaper than CSV or JSON, needs no schema from you, and keeps types.
- Gzip-compressed CSV and JSON load slower than uncompressed because they can’t be read in parallel.
- Classify data by structure: structured (tables, CSV), semi-structured (JSON, XML, Avro with nesting, logs), unstructured (images, video, audio, free text, PDFs).
Extraction and load tools
| Tool | Use when |
|---|---|
| Dataflow | Batch or streaming pipelines written in Apache Beam, or from Google-provided templates (Pub/Sub to BigQuery, Cloud Storage text to BigQuery, JDBC to BigQuery). Serverless and autoscaling |
| BigQuery Data Transfer Service | Scheduled, managed loads from supported SaaS and cloud sources into BigQuery. No code |
| Database Migration Service | One-off or continuous migration of a database to Cloud SQL or AlloyDB |
| Cloud Data Fusion | Visual ETL with many connectors; runs on Dataproc under the hood; good for teams that prefer no code |
| Datastream | Change data capture into BigQuery or Cloud Storage |
| Pub/Sub plus a BigQuery subscription | Stream messages straight into a table without a pipeline |
| BigQuery load job | Batch load from Cloud Storage. Free (uses a shared slot pool) |
| Storage Write API / streaming inserts | Row-level streaming into BigQuery; Storage Write API is the newer, cheaper option |
| Federated and external tables | Query data in Cloud Storage, Bigtable, Spanner or Cloud SQL without loading it |
Trap: a load job is free, streaming is billed. If data arrives in hourly files, load from Cloud Storage rather than streaming.
Choosing a storage service
| Service | Data model | Best for |
|---|---|---|
| Cloud Storage | Objects (any file) | Data lake, raw files, backups, media, unstructured data |
| BigQuery | Columnar analytic tables, SQL | Warehouse, analytics, BI, ML on tables. Not for row-by-row transactions |
| Cloud SQL | Managed MySQL, PostgreSQL, SQL Server | Traditional relational apps, regional scale, lift-and-shift of existing databases |
| AlloyDB | PostgreSQL-compatible, higher performance | Demanding PostgreSQL workloads, hybrid transactional and analytical |
| Spanner | Relational, horizontally scalable, strongly consistent, global | Global relational apps needing very high scale and availability |
| Firestore | Document NoSQL | Mobile and web app data, flexible schema, real-time sync |
| Bigtable | Wide-column NoSQL, single-digit millisecond | Very large time series, IoT, ad tech, high write throughput; low latency by row key |
| Memorystore | In-memory Redis or Memcached | Caching, sessions |
Decision cues: analytics with SQL = BigQuery; files = Cloud Storage; small or medium relational app = Cloud SQL; relational plus global scale = Spanner; huge key-based reads and writes = Bigtable; documents for apps = Firestore.
Storage location types
| Type | Meaning | Use |
|---|---|---|
| Zonal | One zone (for example a Compute Engine disk or a single-zone resource) | Lowest cost, lowest availability |
| Regional | Data replicated across zones in one region | Data close to compute; data residency; low latency within a region |
| Dual-region | Two specific regions (for example nam4, or a custom pair) |
Highest availability and disaster recovery with region control and turbo replication option |
| Multi-region | A large area (US, EU, ASIA) with data spread over several regions |
Content serving, analytics over geographically spread users, highest availability, no choice of exact regions |
Points tested:
- You can’t change a bucket’s location after creation; you create a new bucket and copy.
- BigQuery datasets use a location (region or multi-region
US/EU). Queries can’t join tables across locations, and the dataset must be in the same location as the Cloud Storage bucket used for loads and external tables. - Pick the region closest to users and compute, subject to data residency rules and cost.
Loading with command-line tools and libraries
gcloud storage cp,gcloud storage rsync(the modern replacement forgsutil) copy files to Cloud Storage;-rfor folders; parallel composite uploads help for large files.bq load --source_format=PARQUET dataset.table gs://bucket/path/*.parquetloads files;--autodetectguesses a schema for CSV/JSON;--replaceoverwrites; use--skip_leading_rows=1for CSV headers.bq query,bq extract(export a table to Cloud Storage),bq mk(make a dataset or table) are the other core commands.- Client libraries (Python, Java, Go, Node.js) when loading is part of an application. They use Application Default Credentials.
- BigQuery load jobs are atomic: they succeed fully or not at all. Wildcards (
gs://bucket/data/*) load many files in one job. - Schema choices when loading: schema auto-detect, an explicit schema file, or self-describing formats. Write dispositions:
WRITE_APPEND,WRITE_TRUNCATE,WRITE_EMPTY.
Exam checklist
- ETL vs ELT vs ETLT: where the transform runs and when masking before storing forces ETL or ETLT.
- Name the quality dimensions and the SQL checks that find them.
- Pick between Storage Transfer Service, Transfer Appliance, BigQuery Data Transfer Service, Database Migration Service and Datastream from the wording.
- Avro and Parquet beat CSV and JSON for loads; BigQuery needs newline-delimited JSON.
- Match each storage service to its data model and use case.
- Know regional, dual-region, multi-region and zonal; location can’t be changed later.
- Load jobs are free; streaming is billed.
bq load,gcloud storage cpand client libraries are the load tools; Cloud Data Fusion and Dataflow are for cleaning at scale.