Skip to content

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

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 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?

  1. 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
  2. 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
  3. CHash the card numbers in a Dataflow pipeline that reads the files and writes only the hashed rows to BigQuery
  4. 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.

Question 2 · choose 1

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?

  1. AA Storage Transfer Service event-driven transfer that copies each file when the partner publishes it and checks its published size and MD5
  2. BA scheduled Storage Transfer Service job that reads a TSV list of the file URLs with their sizes and MD5 values
  3. CA Storage Transfer Service job from an S3-compatible source that points at the partner's server
  4. 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.

Question 3 · choose 1

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?

  1. AA SQL assertion rule whose query selects the rows with an invalid email
  2. BA table condition rule that tests the email pattern, with a 98% threshold
  3. CA regular expression check on the email column, with a 98% threshold
  4. 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.

Question 4 · choose 1

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?

  1. AWrap the conversion as `IFNULL(CAST(amount AS NUMERIC), 0)`
  2. BConvert with `CAST(TRIM(amount) AS NUMERIC)` instead
  3. CConvert with `SAFE_CAST(amount AS NUMERIC)` instead
  4. 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.

Question 5 · choose 1

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?

  1. ABuild the steps as a recipe in Wrangler, then create a batch pipeline from it that runs for each daily file
  2. BBuild the steps as a recipe in Wrangler, then add it to a realtime pipeline that picks up each file as it arrives
  3. CApply the steps in the Wrangler workspace each morning and let Wrangler write the cleaned file back to the bucket
  4. 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.

Question 6 · choose 1

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?

  1. A`AVG(IF(TRIM(tax_id) = '', 1, 0))`
  2. B`1 - AVG(IF(tax_id IS NOT NULL AND tax_id != '', 1, 0))`
  3. C`AVG(IF(TRIM(tax_id) IN (NULL, ''), 1, 0))`
  4. 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.

Question 7 · choose 1

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?

  1. ATransfer Appliance
  2. BA Storage Transfer Service job
  3. CThe BigQuery Data Transfer Service
  4. 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.

Question 8 · choose 1

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?

  1. AThe appliance's online mode
  2. BA second appliance ordered for the same copy
  3. CCustomer-managed encryption keys for the appliance
  4. 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.

Google Cloud documentation

Question 9 · choose 1

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?

  1. AA data quality scan with only SQL assertion rules
  2. BA data profile scan on each of the tables
  3. CA Cloud Logging alert on BigQuery load jobs
  4. 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.

Question 10 · choose 1

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?

  1. AETL
  2. BReverse ETL
  3. CELT
  4. 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.

Question 11 · choose 1

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?

  1. ADataflow, running an Apache Beam pipeline
  2. BDataform, running a SQLX workflow
  3. CCloud Data Fusion, running a pipeline from its Studio
  4. 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.

Question 12 · choose 1

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?

  1. AScheduled queries that run each DML script in turn
  2. BDataform, with SQL workflows written in SQLX
  3. CContinuous queries that rewrite the target tables
  4. 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.

Practice domain 1 →Practice all domains →