Associate Data Practitioner · Domain 1: Data Preparation and Ingestion · 30% of the exam
Task 1.2: Extract and load data into appropriate Google Cloud storage systems.
File formats such as CSV, JSON, Parquet and Avro, choosing an extraction tool, picking a store and a location type for structured, semi-structured or unstructured data, and loading it with the gcloud and bq tools, transfer services or client libraries.
Study it
Data formats: CSV, JSON, Parquet, Avro and database tables
Lesson coming
Extraction tools: Dataflow, BigQuery Data Transfer Service, Database Migration Service, Cloud Data Fusion
Structured, semi-structured and unstructured data, and location types
Lesson coming
Loading data with gcloud, bq, the transfer services and client libraries
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Google Cloud documentation page that proves it.
Question 1 · choose 1
A web service exports each day's orders as one file in Cloud Storage. The file is a single JSON array that wraps every order object, with the whole array pretty-printed over many lines. A load into BigQuery with `--source_format=NEWLINE_DELIMITED_JSON` fails with parse errors. What should the team change so that the daily files load?
AAdd the `--autodetect` flag to the load command
BAdd the `--allow_jagged_rows` flag to the daily load command
CCompress the file with gzip before each load
DChange the export to put one complete record on each line
Show the answer and why
AAdd the `--autodetect` flag to the load command
Incorrect
Auto-detection infers column types from the records it can read; it does not make BigQuery accept a file whose layout is not newline-delimited.
BAdd the `--allow_jagged_rows` flag to the daily load command
Incorrect
That flag accepts CSV rows that are missing trailing optional columns; it has no effect on how JSON records are laid out.
CCompress the file with gzip before each load
Incorrect
Compression changes the file size, not its structure; the decompressed content would still be one array spread over many lines.
DChange the export to put one complete record on each line
Correct
BigQuery loads JSON only as newline-delimited JSON, where each JSON object sits on a separate line; one record per line is exactly that format.
For JSON, BigQuery requires newline-delimited JSON (ndJSON or JSONL): one complete object per line, no enclosing array. Flags such as --autodetect or --ignore_unknown_values deal with schema and extra fields, not with the layout of the file.
Store systems drop CSV sales files into a Cloud Storage bucket throughout the day, all with the same columns. Their rows must reach a native BigQuery table at least every 30 minutes, and the table must keep every earlier row. Files stay in the bucket after they are loaded, and a file must not be loaded again by later runs. The team wants nothing that it has to run or patch itself. What should the team set up?
AA BigQuery Data Transfer Service transfer for Cloud Storage every 30 minutes, with the APPEND write preference
BA BigQuery scheduled query every 30 minutes that runs LOAD DATA INTO the table from the bucket's wildcard URI
CA Storage Transfer Service job every 30 minutes, with the BigQuery table as its sink
DA Pub/Sub notification on the bucket, with a BigQuery subscription that writes the messages to the table
Show the answer and why
AA BigQuery Data Transfer Service transfer for Cloud Storage every 30 minutes, with the APPEND write preference
Correct
Cloud Storage transfers load matching files on a schedule into a native table, and in the default APPEND mode an unmodified file is loaded only once; it is reloaded only if its modification time changes.
BA BigQuery scheduled query every 30 minutes that runs LOAD DATA INTO the table from the bucket's wildcard URI
Incorrect
Each run loads every file that matches the URI and INTO appends it to the table; since the files stay in the bucket, every run would load the earlier files again.
CA Storage Transfer Service job every 30 minutes, with the BigQuery table as its sink
Incorrect
Storage Transfer Service moves files between object and file storage systems such as Cloud Storage, other clouds and on-premises storage; a BigQuery table is not one of its sinks.
DA Pub/Sub notification on the bucket, with a BigQuery subscription that writes the messages to the table
Incorrect
A Cloud Storage notification carries the metadata of the changed object, not its contents, so the subscription would write file metadata rather than the sales rows.
The BigQuery Data Transfer Service for Cloud Storage schedules load jobs from a bucket into BigQuery (default every 24 hours; shorter intervals can be set). APPEND, the default write preference, adds new data and loads an unmodified file only once, while MIRROR overwrites the table on each run. All matching files must share the destination table's schema.
A nightly `bq load` of a 5-million-row CSV export of support tickets into an existing BigQuery table has started to fail. The export now wraps the comment field in double quotes, and many comments contain line breaks. Also, a few rows each night hold text in the numeric priority column, and the business accepts losing up to 100 such rows per night as long as everything else loads. Which flags should the load use?
The bad-record allowance covers the wrong-typed rows, but jagged rows only lets rows miss trailing optional columns; a line break inside a quoted comment still splits the row, so far more than 100 rows fail.
B`--allow_quoted_newlines`
Incorrect
The comments now parse, but max_bad_records defaults to 0, so a single row with text in the priority column still fails the whole load.
Ignoring unknown values drops extra values that the schema does not describe; a text value in a numeric column is still a bad record, and none are allowed by default.
D`--allow_quoted_newlines --max_bad_records=100`
Correct
Allowing quoted newlines lets the multi-line comments load as one field, and max_bad_records lets the job skip up to 100 records it cannot parse instead of failing.
Each CSV load flag relaxes one rule. --allow_quoted_newlines accepts quoted data sections that contain newline characters; --max_bad_records sets how many bad records a job may skip before it fails, 0 by default; --allow_jagged_rows accepts rows missing trailing optional columns; and --ignore_unknown_values ignores extra values the schema does not describe. A file with two different problems needs the flag for each of them.
A logistics company collects about 6 TiB of sensor summaries a month. The producers write them as Avro files to a Cloud Storage bucket every 15 minutes, and a lifecycle rule deletes each file after 7 days. Dashboards in BigQuery show 13 months of history and may show figures up to an hour old. The data team must keep the cost of getting the data into BigQuery as low as possible. How should the data be ingested?
AHave the producers stream every record with the Storage Write API default stream
BRun load jobs from the bucket after each new set of files arrives
CLet the dashboards read the Avro files in the bucket through an external table, so that nothing has to be loaded
DStart a Dataflow streaming job that reads the files and writes the rows to the tables
Show the answer and why
AHave the producers stream every record with the Storage Write API default stream
Incorrect
Streaming would be fresher than needed, but the Storage Write API (gRPC) is charged per GiB once the first 2 TiB of the month are used, so most of the 6 TiB would be billed.
BRun load jobs from the bucket after each new set of files arrives
Correct
Batch loading is free by default because load jobs use a shared slot pool, the loaded rows stay in BigQuery storage after the files expire, and loading every 15 minutes is well within the one-hour freshness.
CLet the dashboards read the Avro files in the bucket through an external table, so that nothing has to be loaded
Incorrect
External data stays in Cloud Storage and is read from there at query time, so once the lifecycle rule deletes a file its rows are gone and 13 months of history cannot be shown.
DStart a Dataflow streaming job that reads the files and writes the rows to the tables
Incorrect
Dataflow is meant for custom streaming sources or preprocessing before the load; here it adds running workers and streaming writes for data that simple load jobs can ingest.
BigQuery offers batch loading and streaming. Batch loads from Cloud Storage are not charged by default (they run on a shared slot pool), while the Storage Write API is billed by volume beyond its monthly free allowance. External tables avoid loading but read the data in place, so they depend on the files staying in the bucket. When the freshness target is minutes to hours and the files are already in Cloud Storage, frequent load jobs are the low-cost choice.
A BigQuery dataset lives in the EU multi-region and receives daily batch loads from a new landing bucket. The files must stay in the EU, must remain available if one region has an outage, and the daily load jobs must not incur data transfer charges between Cloud Storage and BigQuery. Where should the landing bucket be created?
AA regional bucket in europe-west4 (Netherlands), copied to a second regional bucket in europe-west1 (Belgium) that loads use during an outage
BA dual-region bucket of europe-west1 (Belgium) and europe-north1 (Finland)
CA dual-region bucket that includes europe-west4, such as EUR4
DA bucket in the EU multi-region
Show the answer and why
AA regional bucket in europe-west4 (Netherlands), copied to a second regional bucket in europe-west1 (Belgium) that loads use during an outage
Incorrect
Loads from europe-west4 are colocated, but europe-west1 is not a colocated location for an EU multi-region dataset even though it is in the EU, so every load during an outage incurs transfer charges.
BA dual-region bucket of europe-west1 (Belgium) and europe-north1 (Finland)
Incorrect
Both regions are in the EU and the bucket survives a regional outage, but a dual-region without europe-west4 is not colocated with the EU multi-region, so each load incurs data transfer charges.
CA dual-region bucket that includes europe-west4, such as EUR4
Correct
For a dataset in the EU multi-region, a dual-region that includes europe-west4 is colocated, so loads avoid transfer charges, and a dual-region keeps the files available through a regional outage.
DA bucket in the EU multi-region
Incorrect
For an EU multi-region dataset, BigQuery's colocation guidance names a dual-region that includes europe-west4 as the bucket location; a multi-region bucket does not meet it, so loads incur transfer charges.
Batch loads follow the same colocation guidance as queries on Cloud Storage: for an EU multi-region dataset, the bucket should be in europe-west4 or a dual-region that includes it. Other locations, even inside the EU, incur data transfer charges. Among those options, only a dual-region adds cross-region availability without moving the loads elsewhere.
Forty stores each upload a daily CSV file, and one load job with a wildcard URI and `--autodetect` loads them all into a new BigQuery table. The postal code column came out as INTEGER: codes such as 02134 lost their leading zero, and a few files with codes such as K1A 0B1 were rejected. Every row must load with the codes exactly as written. What should the team do?
AKeep auto-detection and add `--skip_leading_rows=1` for the header
BReplace auto-detection with a schema that types the column as STRING
CSave the table's schema with `bq show --schema` and pass it to the reload
DKeep auto-detection and sort each file so that letter codes come first
Show the answer and why
AKeep auto-detection and add `--skip_leading_rows=1` for the header
Incorrect
Skipping the header only changes which line is read first; the sampled data rows still look like integers, so the column is still inferred as INTEGER.
BReplace auto-detection with a schema that types the column as STRING
Correct
An explicit schema replaces inference, so every code loads as text with its leading zeros and letters kept, whatever rows any one file starts with.
CSave the table's schema with `bq show --schema` and pass it to the reload
Incorrect
bq show returns the schema that auto-detection produced, so the saved schema still types the postal code as INTEGER and the reload repeats the same result.
DKeep auto-detection and sort each file so that letter codes come first
Incorrect
Auto-detection selects a random file from the source and scans up to its first 500 rows, so sorting within files cannot make sure the sampled rows include letter codes.
Schema auto-detection picks a random file and infers each column's type from up to the first 500 rows. Values that look numeric in the sample become INTEGER even when other files hold codes that are not numbers. For columns such as postal codes, IDs or phone numbers, supply the schema (or that column's type) explicitly.
A partner writes a new Parquet export to an Amazon S3 bucket every night. A data analyst needs those rows in a native BigQuery table each morning, configured in the console without code, and without keeping a staging copy of the files in Cloud Storage. Which service should the analyst use?
AStorage Transfer Service, with a daily job from the S3 bucket
BBigQuery Data Transfer Service, with a daily Amazon S3 transfer
CDatastream, with the S3 bucket as source and BigQuery as destination
DDatabase Migration Service, with a daily one-time migration job
Show the answer and why
AStorage Transfer Service, with a daily job from the S3 bucket
Incorrect
Storage Transfer Service copies objects into Cloud Storage, so it would create the staging copy the analyst wants to avoid and still need a separate load into BigQuery.
BBigQuery Data Transfer Service, with a daily Amazon S3 transfer
Correct
Amazon S3 is a supported source of the BigQuery Data Transfer Service, which runs scheduled load jobs from the bucket straight into a BigQuery table.
CDatastream, with the S3 bucket as source and BigQuery as destination
Incorrect
Datastream replicates changes from databases and Salesforce; an S3 bucket of files is not one of its sources.
DDatabase Migration Service, with a daily one-time migration job
Incorrect
Database Migration Service migrates databases into Cloud SQL and AlloyDB for PostgreSQL; it does not read Parquet files from S3 into BigQuery.
The BigQuery Data Transfer Service automates scheduled loads into BigQuery from Google services, SaaS applications and cloud storage sources, including Cloud Storage, Amazon S3 and Azure Blob Storage. Storage Transfer Service moves objects between storage systems, and Datastream and Database Migration Service work with databases.
An analyst works only in the BigQuery Studio query editor and has no access to the bq tool. She must load two Parquet files from Cloud Storage into the existing table `sales.orders`, adding to the rows already there. Which statement does the job?
A`INSERT INTO sales.orders SELECT * FROM 'gs://exports/orders-*.parquet'`
B`EXPORT DATA OPTIONS (uri = 'gs://exports/orders-*.parquet', format = 'PARQUET') AS SELECT * FROM sales.orders`
C`LOAD DATA OVERWRITE sales.orders FROM FILES (format = 'PARQUET', uris = ['gs://exports/orders-*.parquet'])`
D`LOAD DATA INTO sales.orders FROM FILES (format = 'PARQUET', uris = ['gs://exports/orders-*.parquet'])`
Show the answer and why
A`INSERT INTO sales.orders SELECT * FROM 'gs://exports/orders-*.parquet'`
Incorrect
INSERT reads from tables and query results; a Cloud Storage path in quotes is a string literal, not a source that a SELECT can read.
B`EXPORT DATA OPTIONS (uri = 'gs://exports/orders-*.parquet', format = 'PARQUET') AS SELECT * FROM sales.orders`
Incorrect
EXPORT DATA writes query results out to Cloud Storage; it moves data in the opposite direction.
C`LOAD DATA OVERWRITE sales.orders FROM FILES (format = 'PARQUET', uris = ['gs://exports/orders-*.parquet'])`
Incorrect
OVERWRITE replaces the table's existing rows with the files' contents instead of adding to them.
D`LOAD DATA INTO sales.orders FROM FILES (format = 'PARQUET', uris = ['gs://exports/orders-*.parquet'])`
Correct
LOAD DATA creates a load job from SQL. With INTO, the rows are appended to the existing table.
The LOAD DATA statement runs a load job from the query editor: FROM FILES names the format and the Cloud Storage URIs, INTO appends to an existing table (or creates it), and OVERWRITE replaces the table's contents. EXPORT DATA is its counterpart for writing query results to Cloud Storage.
A three-person team is building a web app that saves each user's settings as a JSON-like record. Different users have different sets of fields, the app queries records by field values and sometimes updates two records in one ACID transaction, and the team wants a serverless database that scales on its own without instances or nodes to size. Which storage service fits best?
AFirestore
BCloud SQL for PostgreSQL
CBigtable
DBigQuery
Show the answer and why
AFirestore
Correct
Firestore is a fully managed, serverless document database for web and mobile apps; it stores JSON-compatible documents whose fields can differ and supports rich queries and ACID transactions.
BCloud SQL for PostgreSQL
Incorrect
Cloud SQL supports transactions, but it is a relational service built on instances that the team chooses a machine size for, with tables that expect a defined schema.
CBigtable
Incorrect
Bigtable runs on clusters of nodes and is not a traditional relational or document database; its own documentation points to Firestore for structured objects that need ACID transactions and SQL-like queries.
DBigQuery
Incorrect
BigQuery is an analytics data warehouse for querying large datasets, not the operational store that serves an app's per-user reads and writes.
Classify the data and the access pattern first. Semi-structured records with varying fields that an app reads and writes one at a time point to a document database; Firestore adds serverless scaling, queries on document fields and ACID transactions. Cloud SQL suits fixed relational schemas, Bigtable very high-throughput key-based access, and BigQuery analytics.
A supplier sends delimited text files whose columns are separated by the pipe character (|) instead of commas. Which `bq load` flag tells BigQuery how to split each row into columns?
A`--null_marker='|'`
B`--allow_jagged_rows`
C`--source_format=NEWLINE_DELIMITED_JSON`
D`--field_delimiter='|'`
Show the answer and why
A`--null_marker='|'`
Incorrect
null_marker sets the string that represents a NULL value in CSV data; it does not change how columns are separated.
B`--allow_jagged_rows`
Incorrect
This flag accepts rows that are missing trailing optional columns; it does not set the separator.
C`--source_format=NEWLINE_DELIMITED_JSON`
Incorrect
This format is for JSON objects, one per line; a pipe-separated file is still CSV-style data.
D`--field_delimiter='|'`
Correct
field_delimiter sets the character that marks the boundary between columns in CSV data; the default is a comma.
BigQuery loads any delimited text file as CSV data, and --field_delimiter (for example a pipe, or \t for tab) tells the load where one column ends and the next begins.
Each record in a nightly sensor export holds one device ID and a list of readings whose length varies from device to device. The team wants to load the export into a BigQuery table in which readings is a repeated field, without flattening the records first. Which file format should the export use?
ACSV with a header row, loaded with schema auto-detection
BCSV, with a schema file that marks readings as REPEATED
CAn Excel workbook with one sheet for each device
DNewline-delimited JSON, with readings as an array
Show the answer and why
ACSV with a header row, loaded with schema auto-detection
Incorrect
CSV files don't support nested or repeated data, so a list of readings cannot arrive as a repeated field, whatever auto-detection infers.
BCSV, with a schema file that marks readings as REPEATED
Incorrect
The limitation is in the format: CSV files don't support nested or repeated data, so a schema file cannot make a CSV column repeated.
CAn Excel workbook with one sheet for each device
Incorrect
BigQuery batch loads accept Avro, CSV, newline-delimited JSON, ORC and Parquet files; an Excel workbook is not one of the load formats.
DNewline-delimited JSON, with readings as an array
Correct
BigQuery supports loading nested and repeated data from formats that support object-based schemas, such as JSON, so an array of readings can load into a repeated field.
Match the file format to the shape of the data. Flat rows fit CSV, but CSV files don't support nested or repeated data. To keep a variable-length list inside each record, export a format with object-based schemas, such as newline-delimited JSON, and load the list into a repeated field.
A partner needs a CSV copy of the 30 GB BigQuery table `sales.orders`, delivered through a Cloud Storage bucket. The command `bq extract --destination_format=CSV sales.orders gs://partner-drop/orders.csv` fails. What change lets the export succeed?
AAdd `--compression=GZIP`, so that the exported file is much smaller
BExport the table to a shared Google Drive folder instead of the bucket
CUse a wildcard destination URI, such as `gs://partner-drop/orders-*.csv`
DChange `--destination_format` to AVRO, a compact binary format
Show the answer and why
AAdd `--compression=GZIP`, so that the exported file is much smaller
Incorrect
The limit counts the logical size of the table data written to one file, and that stays at 30 GB however much the file is compressed.
BExport the table to a shared Google Drive folder instead of the bucket
Incorrect
BigQuery cannot export table data to Google Drive; Cloud Storage is the only supported export location.
CUse a wildcard destination URI, such as `gs://partner-drop/orders-*.csv`
Correct
One file can hold at most 1 GB of logical table data; with a wildcard in the URI, BigQuery writes the export as multiple files.
DChange `--destination_format` to AVRO, a compact binary format
Incorrect
The 1 GB limit for a single file applies to every export format, so an Avro export to one file fails the same way.
BigQuery can export up to 1 GB of logical table data to a single file. For more data, the destination URI needs a wildcard, and BigQuery splits the export into multiple files whose sizes vary. Exports go only to Cloud Storage; compression and format choices do not lift the single-file limit.
A vendor delivers its product catalog as a MySQL export file, `catalog.sql`, full of CREATE TABLE and INSERT statements. The file is in a Cloud Storage bucket, and the data must be loaded into a database on a Cloud SQL for MySQL instance. Which command-line tool does this?
A`gcloud sql import sql` with the instance and the file's Cloud Storage URI
B`gcloud sql import csv` with the instance, the file and a table name
C`bq load` with the file's Cloud Storage URI and a destination table
D`gcloud storage cp` from the bucket to the Cloud SQL instance
Show the answer and why
A`gcloud sql import sql` with the instance and the file's Cloud Storage URI
Correct
gcloud sql import sql imports data into a Cloud SQL instance from a file of SQL statements in Cloud Storage, which is the format of catalog.sql.
B`gcloud sql import csv` with the instance, the file and a table name
Incorrect
gcloud sql import csv reads a plain text file with one line per row and comma-separated fields; a file of SQL statements is not that format.
C`bq load` with the file's Cloud Storage URI and a destination table
Incorrect
bq load loads files such as CSV, JSON, Avro or Parquet into BigQuery tables; it neither runs SQL statement files nor writes to Cloud SQL.
D`gcloud storage cp` from the bucket to the Cloud SQL instance
Incorrect
gcloud storage cp copies objects to, from and between buckets and local file systems; it does not run the statements of a SQL file inside a database.
Pick the loading tool by the format and the target. Cloud SQL imports SQL statement files with gcloud sql import sql and CSV files with gcloud sql import csv, both reading from Cloud Storage; bq load writes to BigQuery, and gcloud storage cp only moves objects.
An analyst's workstation has a local folder `daily_exports` that holds one subfolder of CSV files per month. She wants to upload the whole folder, subfolders included, to the bucket `gs://raw-landing` with one command. Which command does that?
Daisy-chain mode changes how an object is copied between buckets or providers (download, then upload); it does not copy directory contents.
gcloud storage cp copies data between a local file system and Cloud Storage, within the cloud and between providers. To copy an entire directory tree, such as a folder with subfolders, add the --recursive option (-r or -R).
A team sets up a recurring BigQuery Data Transfer Service transfer from a Cloud Storage bucket. What is the shortest repeat interval it can choose for this recurring transfer?
AEvery 1 minute
BEvery 5 minutes
CEvery 15 minutes
DOnce every 24 hours
Show the answer and why
AEvery 1 minute
Incorrect
Recurring Cloud Storage transfers cannot repeat this often; the minimum interval is 15 minutes.
BEvery 5 minutes
Incorrect
Five minutes is below the 15-minute minimum for recurring Cloud Storage transfers.
CEvery 15 minutes
Correct
The minimum interval between recurring Cloud Storage transfers is 15 minutes; for lower intervals, Google points to event-driven transfers.
DOnce every 24 hours
Incorrect
Every 24 hours is the default interval for a recurring transfer, not the shortest one.
Cloud Storage transfers in the BigQuery Data Transfer Service run every 24 hours by default and can repeat as often as every 15 minutes. Files are picked up with no minimum file age, and an event-driven transfer can react to new files faster than the scheduled minimum.