Associate Data Practitioner · Domain 1: Data Preparation and Ingestion · 30% of the exam
Task 1.1: Prepare and process data.
ETL, ELT and ETLT, choosing a transfer tool such as Storage Transfer Service or Transfer Appliance, assessing data quality, and cleaning data with Cloud Data Fusion, BigQuery SQL or Dataflow.
Study it
ETL, ELT and ETLT: where the transformation runs
Lesson coming
Choosing a transfer tool: Storage Transfer Service, Transfer Appliance, gcloud storage
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 payments company will load daily transaction files into BigQuery for its analysts, who only ever need a hashed form of each card number. The security team rules that once the daily job has finished, no clear card number may remain anywhere in BigQuery, including data that BigQuery keeps so that changed or deleted rows can be recovered. The analysts would like the rest of the work to stay in SQL where possible. Which approach meets the rule?
ALoad the files into a staging table, replace each card number with its SHA-256 hash by using an UPDATE statement, and then drop the staging table
BLoad the files into the reporting table and attach a data masking policy that shows analysts a SHA-256 hash of the card number column
CHash the card numbers in a Dataflow pipeline that reads the files and writes only the hashed rows to BigQuery
DLoad the files into a landing dataset whose time travel window is set to zero, build the hashed table with SQL, then delete the landing table
Show the answer and why
ALoad the files into a staging table, replace each card number with its SHA-256 hash by using an UPDATE statement, and then drop the staging table
Incorrect
Rows changed by the UPDATE and the dropped table both stay recoverable for the dataset's time travel window, so clear card numbers remain in BigQuery for days after the job finishes.
BLoad the files into the reporting table and attach a data masking policy that shows analysts a SHA-256 hash of the card number column
Incorrect
Masking changes what a query returns to the users the policy covers; the table itself still stores the clear card numbers that were loaded.
CHash the card numbers in a Dataflow pipeline that reads the files and writes only the hashed rows to BigQuery
Correct
This is ETL: the transformation happens before the load, so BigQuery only ever receives hashed values and has nothing sensitive to keep for recovery. Later reporting logic can still be written in SQL.
DLoad the files into a landing dataset whose time travel window is set to zero, build the hashed table with SQL, then delete the landing table
Incorrect
The time travel window can't be set below two days, so the deleted landing table stays recoverable for at least two days.
ELT loads raw data into BigQuery and transforms it there; ETL transforms it before it reaches the warehouse. When a value must not remain recoverable, loading the raw rows into a BigQuery table first is ruled out: BigQuery keeps changed, deleted and replaced data for the time travel window, which is seven days by default and can't be set below two. Transforming in Dataflow first means the sensitive value never reaches BigQuery at all.
A research partner republishes about 2,000 data files every night on its public HTTPS web server, gives each file a fixed address, and publishes each file's size and MD5 checksum. A data team needs fresh copies in a Cloud Storage bucket every morning, and any file whose bytes do not match the published values must fail. The team does not want to run, patch or scale any machines for the job. Which option should the team choose?
AA Storage Transfer Service event-driven transfer that copies each file when the partner publishes it and checks its published size and MD5
BA scheduled Storage Transfer Service job that reads a TSV list of the file URLs with their sizes and MD5 values
CA Storage Transfer Service job from an S3-compatible source that points at the partner's server
DA Datastream stream that reads the partner's server and writes to a Cloud Storage destination
Show the answer and why
AA Storage Transfer Service event-driven transfer that copies each file when the partner publishes it and checks its published size and MD5
Incorrect
Event-driven transfers listen to event notifications from Amazon S3, Azure Storage or Cloud Storage sources; a partner's web server sends no such events, so nothing would start the copy or the checks.
BA scheduled Storage Transfer Service job that reads a TSV list of the file URLs with their sizes and MD5 values
Correct
URL list transfers are agentless, can run on a schedule, and fail any object whose received size or base64 MD5 checksum differs from the values given in its row of the list.
CA Storage Transfer Service job from an S3-compatible source that points at the partner's server
Incorrect
Transfers from S3-compatible storage need a server that speaks the Amazon S3 API and use transfer agents running on VMs that you deploy and maintain.
DA Datastream stream that reads the partner's server and writes to a Cloud Storage destination
Incorrect
Cloud Storage is a Datastream destination, but Datastream replicates changes from databases such as MySQL, PostgreSQL and Oracle; files on a web server are not a source it reads.
Storage Transfer Service has agentless sources (Cloud Storage, Amazon S3, Azure Storage and lists of public HTTP or HTTPS URLs) and agent-based ones (file systems, HDFS and S3-compatible storage). A URL list is a TSV file whose rows give each URL and, optionally, its size and base64 MD5 checksum; a mismatch fails that object's transfer. Event-driven transfers exist only for Amazon S3, Azure and Cloud Storage sources.
A team already runs a Knowledge Catalog auto data quality scan on its BigQuery customers table. It now wants one more rule: the email column must look like a valid address, but because some legacy records are known to be messy, the rule should fail only when fewer than 98% of rows pass. Which rule should the team add?
AA SQL assertion rule whose query selects the rows with an invalid email
BA table condition rule that tests the email pattern, with a 98% threshold
CA regular expression check on the email column, with a 98% threshold
DA regular expression check on the email column, with a 2% threshold
Show the answer and why
AA SQL assertion rule whose query selects the rows with an invalid email
Incorrect
An assertion rule fails as soon as its query returns any row, so a single legacy record would fail it; it has no passing percentage to tolerate the known 2%.
BA table condition rule that tests the email pattern, with a 98% threshold
Incorrect
A table condition is an aggregate rule: it runs once for the whole table and returns a single true or false, so there is no per-row pass count for a passing threshold to apply to.
CA regular expression check on the email column, with a 98% threshold
Correct
The regular expression check is a row-level rule: each row passes or fails the pattern, and the rule fails only when the share of passing rows drops below the passing threshold you set.
DA regular expression check on the email column, with a 2% threshold
Incorrect
The passing threshold is the percentage of rows that must pass, not the share allowed to fail; at 2% the rule would keep passing even if almost every address were malformed.
Auto data quality has row-level rules (range, null, set, regular expression and SQL row condition checks), where each row passes or fails and the rule fails when the percentage of passing rows falls below the passing threshold, and aggregate rules (uniqueness, statistic, table condition and SQL assertion), which evaluate the data as a whole. A format rule that tolerates a known share of bad rows is a row-level check with the threshold set to the share of rows that must pass.
A raw BigQuery table stores order amounts as strings. A cleaning query converts them with `CAST(amount AS NUMERIC)`, but the whole query now fails because a few rows hold values such as `n/a` and `pending`. The analyst wants the query to finish, keeping those rows with an empty amount that can be reviewed later. What should the analyst change?
AWrap the conversion as `IFNULL(CAST(amount AS NUMERIC), 0)`
BConvert with `CAST(TRIM(amount) AS NUMERIC)` instead
CConvert with `SAFE_CAST(amount AS NUMERIC)` instead
DConvert with `PARSE_NUMERIC(amount)` instead
Show the answer and why
AWrap the conversion as `IFNULL(CAST(amount AS NUMERIC), 0)`
Incorrect
IFNULL only replaces a NULL result; the CAST still raises its error on `n/a` before IFNULL is evaluated, and 0 would hide the bad rows anyway.
BConvert with `CAST(TRIM(amount) AS NUMERIC)` instead
Incorrect
TRIM only removes leading and trailing spaces; `pending` is still not a number, so the cast still fails.
CConvert with `SAFE_CAST(amount AS NUMERIC)` instead
Correct
SAFE_CAST returns NULL instead of raising a runtime error when a value cannot be converted, so the query completes and the bad rows keep a NULL amount.
DConvert with `PARSE_NUMERIC(amount)` instead
Incorrect
PARSE_NUMERIC accepts more number formats, but a string with invalid characters is still an error, so `n/a` would stop the query.
CAST fails the query when one value cannot be converted. SAFE_CAST, and the SAFE. prefix on functions, replace that runtime error with NULL, which is the usual way to clean mixed-quality string columns in SQL and then find the failed rows with a WHERE amount IS NULL filter.
Operations analysts who do not write code receive a 3-million-row CSV file in Cloud Storage once a day. In Cloud Data Fusion they want to try cleaning steps (trim fields, split a name column, drop test rows), see the effect of each step on the data while they work, and then have exactly those steps applied to every row of each new daily file. What should they do?
ABuild the steps as a recipe in Wrangler, then create a batch pipeline from it that runs for each daily file
BBuild the steps as a recipe in Wrangler, then add it to a realtime pipeline that picks up each file as it arrives
CApply the steps in the Wrangler workspace each morning and let Wrangler write the cleaned file back to the bucket
DLay out the steps as nodes in the Studio and run the pipeline in preview each morning to write the cleaned rows
Show the answer and why
ABuild the steps as a recipe in Wrangler, then create a batch pipeline from it that runs for each daily file
Correct
Wrangler shows each directive's effect on a sample of the data, and the recipe becomes the Wrangler transformation of a pipeline whose runs apply the steps to the whole dataset. Wrangler is supported in batch pipelines.
BBuild the steps as a recipe in Wrangler, then add it to a realtime pipeline that picks up each file as it arrives
Incorrect
Wrangler is supported only for batch ETL pipelines, so a realtime pipeline is not where its recipe can run.
CApply the steps in the Wrangler workspace each morning and let Wrangler write the cleaned file back to the bucket
Incorrect
The workspace applies the steps only to a sample, limited to the first 1,000 records, so most of the 3 million rows would never be cleaned; the full data is processed only when a pipeline runs.
DLay out the steps as nodes in the Studio and run the pipeline in preview each morning to write the cleaned rows
Incorrect
Preview exists to test a pipeline design on a subset of the data, and it runs in the tenant project; it is not how the whole of each daily file gets processed.
Wrangler is the visual data preparation tool in the Cloud Data Fusion Studio. Directives make up a recipe, which Wrangler applies to a sample (the first 1,000 records) so users can see the effect before they create a pipeline; the recipe then runs as the Wrangler transformation over the entire dataset in a batch pipeline, which can be scheduled.
In the BigQuery table `billing.accounts`, the tax_id column is NULL in some rows, an empty string in others, and only spaces in a few more. For a data quality dashboard, an analyst needs one number: the share of rows whose tax_id is missing in any of these ways. Which expression should the query return?
A`AVG(IF(TRIM(tax_id) = '', 1, 0))`
B`1 - AVG(IF(tax_id IS NOT NULL AND tax_id != '', 1, 0))`
C`AVG(IF(TRIM(tax_id) IN (NULL, ''), 1, 0))`
D`AVG(IF(tax_id IS NULL OR TRIM(tax_id) = '', 1, 0))`
Show the answer and why
A`AVG(IF(TRIM(tax_id) = '', 1, 0))`
Incorrect
For a NULL tax_id, TRIM returns NULL and the comparison returns NULL, so IF takes the else branch and counts the row as present.
B`1 - AVG(IF(tax_id IS NOT NULL AND tax_id != '', 1, 0))`
Incorrect
NULL and empty values are caught, but a value of spaces only is not equal to an empty string, so those rows count as present.
C`AVG(IF(TRIM(tax_id) IN (NULL, ''), 1, 0))`
Incorrect
Listing NULL in IN does not match a NULL value: the equality comparison with NULL produces NULL, so IN returns NULL and IF counts the row as present.
D`AVG(IF(tax_id IS NULL OR TRIM(tax_id) = '', 1, 0))`
Correct
IS NULL catches the NULL rows, and TRIM turns a value of spaces only into an empty string, so every missing form counts as 1 and the average is the share.
Data quality checks often treat several forms as missing. In GoogleSQL a function with a NULL operand returns NULL, any comparison with NULL returns NULL, and IN returns NULL when the comparison with a listed NULL produces NULL; IF returns its else result unless the condition is TRUE. NULL rows must therefore be tested with IS NULL, while TRIM removes leading and trailing whitespace so that blank values compare equal to an empty string.
An analyst has 20 GB of CSV extracts on her workstation and needs them in a Cloud Storage bucket this afternoon. It is a one-time copy over the office's normal internet connection. Which tool is the simplest fit?
ATransfer Appliance
BA Storage Transfer Service job
CThe BigQuery Data Transfer Service
DThe `gcloud storage cp` command
Show the answer and why
ATransfer Appliance
Incorrect
Transfer Appliance is a device that Google ships for transfers that would take more than about a week over the network; it is far too slow and heavy for 20 GB.
BA Storage Transfer Service job
Incorrect
Storage Transfer Service is optimized for transfers of more than 1 TiB and, for on-premises files, needs agents installed first; Google points smaller transfers elsewhere.
CThe BigQuery Data Transfer Service
Incorrect
The BigQuery Data Transfer Service schedules loads into BigQuery tables from supported sources; it does not upload files from a workstation to a bucket.
DThe `gcloud storage cp` command
Correct
The gcloud storage command is the standard tool for small to medium transfers run on an as-needed basis from a command line, such as copying a few local files to a bucket.
Match the tool to the size and pattern of the transfer: gcloud storage for small to medium, as-needed copies; Storage Transfer Service for large or recurring managed transfers (optimized for more than 1 TiB); Transfer Appliance when the network cannot move the data in time.
A company is copying its archive onto a Transfer Appliance at its data center. The analytics team wants the first folders to reach the Cloud Storage bucket while copying continues, instead of waiting for the device to be shipped back to Google. What should the company use?
AThe appliance's online mode
BA second appliance ordered for the same copy
CCustomer-managed encryption keys for the appliance
DIts 100 Gbps QSFP28 network interface
Show the answer and why
AThe appliance's online mode
Correct
With online mode enabled, data copied to the appliance is streamed directly to the Cloud Storage bucket, without waiting for the appliance to be shipped back.
BA second appliance ordered for the same copy
Incorrect
More appliances let you copy more data in parallel, but in offline mode the data still reaches the bucket only after the devices are shipped back and uploaded.
CCustomer-managed encryption keys for the appliance
Incorrect
Customer-managed keys in Cloud KMS control the encryption of the data on the device; they do not change when the data reaches the bucket.
DIts 100 Gbps QSFP28 network interface
Incorrect
The faster interface speeds up copying from your systems onto the appliance, not the delivery of the data to Cloud Storage.
Transfer Appliance normally works offline: you copy data onto it, seal it, ship it back, and Google uploads the data to your bucket. Enabling online mode streams the copied data straight to the bucket, so the data arrives sooner, and you can toggle between online and offline mode.
A data team inherits dozens of BigQuery tables and must start checking their quality, but nobody knows yet which ranges, sets of values or null limits each column should meet. They want Knowledge Catalog to suggest a first set of validation rules from the data itself. What should they run first?
AA data quality scan with only SQL assertion rules
BA data profile scan on each of the tables
CA Cloud Logging alert on BigQuery load jobs
DA Dataform assertion on each table
Show the answer and why
AA data quality scan with only SQL assertion rules
Incorrect
A SQL assertion needs someone to write the invalid-state query, which is the knowledge the team does not have yet; it does not suggest rules.
BA data profile scan on each of the tables
Correct
A profile scan reports column statistics such as common values, distributions and null percentages, and its results can be used to generate recommended rules for auto data quality scans.
CA Cloud Logging alert on BigQuery load jobs
Incorrect
Logs and alerts report what jobs did; they do not examine the values in the tables or propose rules.
DA Dataform assertion on each table
Incorrect
Dataform assertions are tests that you write in your SQL workflow code; they check rules you already know rather than suggesting them.
Data profiling calculates column-level statistics for a table and can turn its results into recommended auto data quality rules, which is a practical starting point when expectations are not yet known. Auto data quality scans then validate the table against the chosen rules.
A team scores every customer for churn risk in BigQuery. Each night it writes the scores out of BigQuery into the Spanner database that the support application reads, so agents see the score on screen. Which data integration pattern is this?
AETL
BReverse ETL
CELT
DChange data capture from a source database
Show the answer and why
AETL
Incorrect
ETL transforms data before it reaches BigQuery; here the data has already been processed in BigQuery and is leaving it.
BReverse ETL
Correct
Exporting processed results from BigQuery so that other systems can act on them, for example to Spanner, Bigtable or Pub/Sub, is what the BigQuery documentation calls reverse ETL.
CELT
Incorrect
ELT loads raw data into BigQuery first and transforms it there; it describes data coming into BigQuery, not results going out of it.
DChange data capture from a source database
Incorrect
Change data capture streams the inserts, updates and deletes of a source database into a destination; it does not describe publishing BigQuery results to an operational database.
ETL transforms data before it is loaded into BigQuery, and ELT loads raw data first and transforms it with BigQuery. Exporting results from BigQuery to apply them in other systems, such as Spanner, Bigtable, AlloyDB or Pub/Sub, is known as reverse ETL.
A team writes its cleaning logic in Python and wants to run the same pipeline code on yesterday's files in batch and on live events as a stream, on a managed service that spreads the processing across many machines for them. Which service fits?
ADataflow, running an Apache Beam pipeline
BDataform, running a SQLX workflow
CCloud Data Fusion, running a pipeline from its Studio
DManaged Service for Apache Airflow, running a Python DAG
Show the answer and why
ADataflow, running an Apache Beam pipeline
Correct
Dataflow runs Apache Beam pipelines, written with the Java, Python or Go SDKs, for both batch and streaming, and processes the data in parallel across many VMs that it manages.
BDataform, running a SQLX workflow
Incorrect
Dataform runs SQL workflows in BigQuery; its transformations are written in SQLX or JavaScript, not as Python pipelines over files and live events.
CCloud Data Fusion, running a pipeline from its Studio
Incorrect
Data Fusion pipelines are designed visually by connecting source, transform and sink plugins; they do not run the team's own Python pipeline code.
DManaged Service for Apache Airflow, running a Python DAG
Incorrect
Airflow DAGs are written in Python, but Managed Airflow is a workflow orchestration service: it schedules and monitors tasks that call other services rather than being the engine that processes the data in parallel.
Dataflow is the managed runner for Apache Beam, a unified model for batch and streaming pipelines. The same Beam code can process bounded files and unbounded streams, and Dataflow distributes the work across workers. An orchestrator such as Managed Airflow can start such a job, but does not do the processing itself.
A team transforms raw tables in BigQuery with long scripts of DML statements that spell out each INSERT, UPDATE and DELETE step. It wants instead to declare the tables and views it wants to end up with, keep the SQL in a Git repository, and let a tool work out the order to build them in. Which BigQuery option matches this?
AScheduled queries that run each DML script in turn
BDataform, with SQL workflows written in SQLX
CContinuous queries that rewrite the target tables
DSaved queries shared with the team
Show the answer and why
AScheduled queries that run each DML script in turn
Incorrect
Scheduled queries run the same imperative statements on a schedule; they do not work out dependencies or keep the code in Git.
BDataform, with SQL workflows written in SQLX
Correct
In Dataform you write declarative statements and Dataform determines the transformation needed, runs the SQL in BigQuery in dependency order, and stores the code in Git-backed repositories.
CContinuous queries that rewrite the target tables
Incorrect
Continuous queries process incoming data in real time with SQL; they are not a way to declare a set of tables and views and their build order.
DSaved queries shared with the team
Incorrect
Saved queries store and share query text in BigQuery Studio; they do not build tables in order or track a repository.
BigQuery describes DML as an imperative approach (you tell BigQuery how to change the data) and Dataform as declarative: you define the desired tables and views in SQLX, Dataform resolves the dependencies, and the code lives in repositories with Git version control.