GCP-194 · Lesson 4 of 12 · 19 min

Review: Data preparation and ingestion

▶ Video coming soon A video version of this lesson is on the way.

Section 1 is the largest section of the exam, at about 30 in 100, so it was the first review of the Saturday workshop. In the diagnostic Mateo had answered 7 of its 11 questions correctly. On a 60-question exam it is worth about 18 questions. Mateo had spent six weeks on this material in GCP-220, loading Birchline's till files, cleaning supplier price lists and streaming web events, so Ravi did not reteach it. Instead he went through the two objectives in the exam guide one consideration at a time and asked Mateo to explain each in a sentence, with an example. Where Mateo hesitated, they stopped and filled the gap.

The section has two objectives. Objective 1.1 is about preparing and processing data: choosing between ETL, ELT and ETLT, choosing transfer tools, judging data quality and cleaning data. Objective 1.2 is about extracting and loading: recognizing formats, choosing extraction tools, choosing where to store data and in what kind of location, classifying data, and the methods for loading it. This lesson follows that order.

ETL, ELT and ETLT

The three patterns differ in where the transformation happens. In ETL, data is extracted from a source, transformed in a separate processing step, and only the clean result is loaded into the destination. In ELT, raw data is loaded into the destination first, usually BigQuery, and transformed there with SQL. ETLT is a mix: a light transformation happens before loading, often to remove or mask sensitive fields or fix a format, and the main transformation happens after loading.

The three patterns, with the Birchline pipelines from GCP-220.
PatternWhere transformation happensFits whenBirchline example
ETLBefore loading, in a tool such as Dataflow or Cloud Data FusionRaw data must not reach the destination, or the destination cannot transform it wellSupplier price lists cleaned in Cloud Data Fusion before loading to staging
ELTAfter loading, inside BigQuery with SQL or DataformThe warehouse can do the work, and keeping raw data is usefulTill files loaded raw, then transformed with Dataform
ETLTA light step before loading, the rest afterSome fields must be removed or masked before they land, but most work suits SQLReturns JSON converted in Cloud Run and the phone number dropped, then transformed in BigQuery

The exam often presents these as a choice. ELT is the usual modern default on Google Cloud, because BigQuery is fast and keeping the raw data lets a team rerun transformations when the logic changes. ETL fits when the raw data must not land in the warehouse, for example because it contains fields that are not allowed to be stored, or when heavy processing is easier in a pipeline tool. ETLT fits the middle case. When a question mentions personal information that must never be stored, look for ETL or ETLT. When it mentions analysts who want to reprocess history with new rules, look for ELT.

Transfer tools

Moving existing data into Google Cloud is a different job from loading daily data, and the exam distinguishes the tools clearly. Storage Transfer Service moves data online into Cloud Storage from other clouds, from HTTP sources, from other Cloud Storage buckets, or from on-premises file systems through agents installed locally. It can run once or on a schedule. Transfer Appliance is a physical storage device that Google ships to the customer, who fills it and ships it back; it fits when the amount of data is so large, or the network so limited, that an online transfer would take too long. For small, one-off copies, the gcloud storage command is enough.

A decision chart. Start: moving existing data into Cloud Storage. First question: is it a small one-off copy? Yes leads to gcloud storage cp. No leads to: would an online transfer finish in time over the available network? Yes leads to Storage Transfer Service (other clouds, HTTP, buckets, or on-premises with agents). No leads to Transfer Appliance (ship a physical device).
Choosing a tool for moving existing data. The question that decides between online and offline is how long the transfer would take over the network.

Worked problem

Online or offline?

A manufacturer wants to move 200 TB of historical sensor files from its own data centre to Cloud Storage. Its internet connection is 1 gigabit per second, and the team expects to use about 70 in 100 of it for the transfer. The data must be in Google Cloud within three weeks. Should it use Storage Transfer Service or Transfer Appliance?

Show solution
  1. Convert the data to bits: 200 TB is 200 × 10^12 bytes, which is 1.6 × 10^15 bits.
  2. At the full 10^9 bits per second, the transfer would take 1.6 × 10^6 seconds.
  3. 1.6 × 10^6 ÷ 86,400 seconds per day is about 18.5 days.
  4. At 70 in 100 of the link, it takes about 18.5 ÷ 0.7, or 26.5 days.
  5. That is longer than three weeks, even before allowing for retries or other traffic.

Answer: Transfer Appliance, because an online transfer would take about 26 days, beyond the three-week deadline.

The example is the arithmetic behind the decision tree. Birchline faced the same question with its 40 TB photo library in GCP-220, and there the answer was Storage Transfer Service with an agent in the head office data centre, because 40 TB over the company's link would finish in about five days. Mateo makes a note: the exam may not give the numbers, only the hint, such as "limited bandwidth" or "hundreds of terabytes".

Data quality

The guide asks candidates to assess data quality. In GCP-220 Mateo learned six common dimensions, and the exam uses the same ideas even if it does not always name them.

Six dimensions of data quality.
DimensionThe question it asksExample check
CompletenessIs anything missing?Every store sent a till file; no null store IDs
AccuracyDoes the data match reality?Till totals reconcile with end-of-day register reports
ValidityDoes each value follow the rules?Dates parse, quantities are not negative except refunds
ConsistencyDo related values agree?The same SKU has the same category in every table
UniquenessAre there duplicates?Each sale line appears once, even if a file is reloaded
TimelinessIs the data current enough?Curated tables are complete by 06:00

For cleaning, the guide names four tools. BigQuery and SQL handle most cleaning in an ELT design: trimming, casting, deduplicating with window functions, and filtering bad rows into a quarantine table. Dataflow handles cleaning as part of a batch or streaming pipeline written with Apache Beam, which suits large volumes, streaming data and complex logic. Cloud Data Fusion offers a visual interface, with its Wrangler tool for interactive cleaning, which suits teams who prefer not to write code. BigQuery data preparation, generally available since March 2025, suggests cleaning steps from a sample of the data with help from Gemini and saves them as a repeatable preparation that can be scheduled.

Recognizing data formats

The guide lists CSV, JSON, Parquet, Avro and structured database tables. The exam often asks which format suits a job, and the answers follow from a few properties.

Common file formats and their properties.
FormatLayoutSchemaGood forWatch out for
CSVRows of textNone in the fileSimple exports, spreadsheets, small loadsDelimiters inside values, headers, no types
JSON (newline-delimited for BigQuery)Text records, can be nestedNone in the fileWeb events, API output, nested dataBigQuery loads one JSON object per line, not a single array
AvroBinary rowsStored with the dataFast, reliable loads; pipelines and streaming recordsNot human-readable
ParquetBinary columnsStored with the dataAnalytics, external tables, smaller filesLess suited to writing one record at a time
ORCBinary columnsStored with the dataHadoop and Hive systemsLess common outside that ecosystem

Two rules of thumb cover most questions. If a question asks for the fastest or most reliable load into BigQuery, a self-describing binary format such as Avro is usually best, because the schema travels with the data and BigQuery can read it in parallel. If a question asks for efficient analytics on files that stay in Cloud Storage, a columnar format such as Parquet is usually best, because queries read only the columns they need. When Birchline converted 1.2 TB of warehouse history from CSV to Parquet in GCP-220, it shrank to about 0.6 TB.

Structured, semi-structured and unstructured

The guide asks candidates to classify data. Structured data has a fixed schema of rows and columns, such as a sales table. Semi-structured data has structure that can vary from record to record, such as JSON web events with optional fields. Unstructured data has no schema a database can use directly, such as product photos, PDFs and audio. The classification matters because it points to storage: structured data to BigQuery or a database, semi-structured data to BigQuery with JSON or nested columns, or to Firestore, and unstructured data to Cloud Storage.

Extraction and loading tools

Tools for extracting and loading data.
ToolWhat it doesChoose it when
BigQuery Data Transfer ServiceScheduled, managed loads into BigQuery from Google sources such as Google Ads, from Cloud Storage, and from some other clouds and warehousesA recurring load from a supported source, with no code
DataflowBatch and streaming pipelines with Apache Beam, many ready-made templatesLarge or streaming data, custom transformation, or a template fits
Database Migration ServiceMigrates databases such as MySQL, PostgreSQL and SQL Server to Cloud SQL or AlloyDB, with continuous replication until cut-overMoving an operational database into a managed database service
DatastreamChange data capture: streams inserts, updates and deletes from databases into BigQuery or Cloud StorageKeeping analytics copies of a live database current
Cloud Data FusionVisual pipelines with many connectorsA low-code team needs to pull from many sources
bq load, gcloud storage, client librariesCommand-line and programmatic loadingScripts, small jobs or application code

The look-alike that catches many candidates is Database Migration Service against Datastream. Both read from a database. Database Migration Service moves the database to a new home: the destination is another operational database, and after cut-over the application uses the new one. Datastream copies changes out of a database that stays where it is, so analysts can query a current copy. Birchline's shop database stayed in MySQL, and Datastream fed its orders into BigQuery; if Birchline had been moving the shop to Cloud SQL, the answer would have been Database Migration Service.

A bq load command reading Avro files from a Cloud Storage folder. With Avro, the schema comes from the files, so none is given on the command line.
bq load --source_format=AVRO raw.pos_sales_20261112 gs://bl-data-landing/pos/2026-11-12/*.avro

Choosing where to store data

The guide lists seven storage options. The exam rarely asks for deep detail about any of them, but it often asks which one fits a described workload. The clues are the shape of the data, the way it is read and written, and the scale.

A chart with seven boxes grouped in three columns. Files and objects: Cloud Storage. Analytics: BigQuery. Operational databases: Cloud SQL (managed MySQL, PostgreSQL, SQL Server); AlloyDB (PostgreSQL-compatible, fast analytics on operational data); Spanner (relational, global scale, strong consistency); Firestore (documents for apps); Bigtable (wide-column, very high throughput, time series). Each box has a short clue phrase.
Seven storage options and the clue that usually points to each.
Storage options and their clues.
ServiceKindTypical clue in a question
Cloud StorageObject storageFiles, images, backups, a data lake, raw landing zone
BigQueryAnalytics data warehouseSQL analysis over large data, reporting, BI, machine learning in SQL
Cloud SQLManaged relational database (MySQL, PostgreSQL, SQL Server)An application's transactional database, lift and shift of a familiar engine
AlloyDBPostgreSQL-compatible databaseDemanding PostgreSQL workloads, fast analytics on operational data
SpannerRelational database that scales horizontallyGlobal users, very high scale, strong consistency across regions
FirestoreDocument databaseMobile or web app data, flexible documents, offline sync
BigtableWide-column NoSQL databaseHuge volumes of time-series or sensor data, very low latency at very high throughput

Mateo had used three of these at work. The other four were the known gap from lesson 1, so Ravi gave him a simple rule for each: SQL analytics means BigQuery; an app's ordinary database means Cloud SQL; PostgreSQL with extra speed means AlloyDB; worldwide relational scale means Spanner; app documents mean Firestore; enormous streams of keyed readings mean Bigtable; and files of any kind mean Cloud Storage.

Location types

The guide asks about four location types. A region is a single geographic area such as Toronto or Columbus, containing several zones. A dual-region is a specific pair of regions, which keeps data available if one region fails. A multi-region is a large area such as the US or the EU containing several regions. A zone is a single location inside a region; most storage does not use zonal locations, but Cloud Storage's Rapid storage class does, for very fast access close to compute in that zone.

Data residency is a common constraint. Google Cloud has no Canadian multi-region, but it offers two Canadian regions, Montréal and Toronto, and a configurable dual-region that pairs them. For data that must stay in Canada and survive the loss of one region, that dual-region is the answer. Birchline's landing bucket is in the Toronto region.

Common mistakes

  • MistakeChoosing Transfer Appliance for every large migration.

    InsteadWork out whether an online transfer would finish in time; choose the appliance only when it would not.

  • MistakeConfusing Database Migration Service with Datastream.

    InsteadMigration moves a database to a new home; Datastream copies changes out of a database that stays.

  • MistakeLoading a JSON array file into BigQuery.

    InsteadBigQuery expects newline-delimited JSON, one object per line.

  • MistakeChoosing Bigtable for SQL analytics.

    InsteadBigtable is for very high-throughput keyed access; SQL analytics belongs in BigQuery.

  • MistakeAssuming there is a Canadian multi-region.

    InsteadUse a Canadian region or the Montréal and Toronto dual-region.

Summary and key terms

  • Section 1 is about 30 in 100 of the exam.
  • ETL transforms before loading, ELT after, and ETLT does a light step before and the rest after.
  • Storage Transfer Service moves data online; Transfer Appliance ships a device when the network is too slow.
  • Data quality has six common dimensions, and BigQuery, Dataflow, Cloud Data Fusion and BigQuery data preparation can clean data.
  • Avro loads quickly with its schema; Parquet suits analytics on files.
  • Database Migration Service moves databases; Datastream copies changes.
  • Seven storage services each have a clue, and four location types control where data lives.
ETL
Extract, transform, load: transformation before the destination.
ELT
Extract, load, transform: raw data loaded first and transformed in the destination.
ETLT
A light transformation before loading and the main transformation after.
Storage Transfer Service
A managed service for moving data online into Cloud Storage.
Transfer Appliance
A physical device shipped to a customer for moving very large data offline.
Datastream
A change data capture service that copies database changes into BigQuery or Cloud Storage.
Database Migration Service
A service for moving databases to Cloud SQL or AlloyDB.
Dual-region
A storage location made of a specific pair of regions.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. A team must never store customers' full card numbers, but wants to analyze the rest of the record in BigQuery. Which pattern fits?

    Show answer

    ETL or ETLT: remove or mask the card number before loading, then transform the rest.

  2. Which format usually gives the fastest, most reliable load into BigQuery?

    Show answer

    Avro, because the schema travels with the data and it can be read in parallel.

  3. A game stores player profiles that change shape often and must sync to phones. Which storage fits?

    Show answer

    Firestore, a document database for app data with offline sync.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz