Skip to content

Associate Data Practitioner · Domain 3: Data Pipeline Orchestration · 18% of the exam

Task 3.1: Design and implement simple data pipelines.

Choosing a transformation tool among Dataflow, Cloud Data Fusion, Dataform and the managed Spark and Airflow services, ELT versus ETL, and the products a basic pipeline needs.

Study it

  • Choosing a transformation tool: Dataflow, Cloud Data Fusion, Dataform, managed Spark and Airflow

    Lesson coming

  • ELT or ETL for a pipeline, and the products a basic pipeline needs

    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

In a Dataform repository, the file daily_orders.sqlx builds a table with `SELECT ... FROM analytics.orders_clean`, where orders_clean is another table that the same repository builds. During scheduled runs, daily_orders is sometimes built before orders_clean has been refreshed. What is the simplest fix?

  1. ARename the files so that orders_clean sorts before daily_orders alphabetically
  2. BReplace the table name with `${ref("orders_clean")}` in the SELECT
  3. CGive both files the same tag and select that tag in the scheduled run
  4. DSchedule daily_orders in a separate run one hour after the main run
Show the answer and why
  • ARename the files so that orders_clean sorts before daily_orders alphabetically

    Incorrect

    Dataform orders actions by their dependency tree, not by file names, so renaming does not create a dependency.

  • BReplace the table name with `${ref("orders_clean")}` in the SELECT

    Correct

    The ref function references the table and automatically declares it as a dependency, so Dataform runs orders_clean before daily_orders.

  • CGive both files the same tag and select that tag in the scheduled run

    Incorrect

    A tag only selects which actions a run includes; it declares no dependency, so daily_orders can still be built before orders_clean.

  • DSchedule daily_orders in a separate run one hour after the main run

    Incorrect

    A time gap only guesses at how long the refresh takes; nothing makes Dataform wait for orders_clean to finish.

Dataform builds a dependency tree from the dependencies declared in each SQLX file and runs dependents after their dependencies. Referencing a table with ref() both resolves its name and declares the dependency; a hard-coded table name declares nothing.

Question 2 · choose 1

In a Dataform repository, daily_revenue reads orders_clean and fx_rates through ref(), and both of those tables define built-in assertions in their config blocks. After a night when duplicates slipped into orders_clean, the team found that its assertion had failed but daily_revenue was still rebuilt with doubled revenue. From now on, daily_revenue must be skipped whenever a check on either of its inputs fails. What should the team change?

  1. ASet `dependOnDependencyAssertions: true` in the config block of daily_revenue
  2. BMove the built-in assertions of both inputs into the config block of daily_revenue
  3. CSet `dependOnDependencyAssertions: true` in the config blocks of orders_clean and fx_rates
  4. DDeclare `dependencies: ["orders_clean", "fx_rates"]` in the config block of daily_revenue
Show the answer and why
  • ASet `dependOnDependencyAssertions: true` in the config block of daily_revenue

    Correct

    This parameter adds the direct assertions of all of daily_revenue's dependency actions as dependencies, so daily_revenue runs only if the checks on both inputs pass.

  • BMove the built-in assertions of both inputs into the config block of daily_revenue

    Incorrect

    Built-in assertions run after their own table is created, so the checks would run on daily_revenue after it was already rebuilt.

  • CSet `dependOnDependencyAssertions: true` in the config blocks of orders_clean and fx_rates

    Incorrect

    The parameter adds the assertions of the dependencies of the action where it is set, so it would gate the two inputs on their own sources' checks, not daily_revenue on the checks of its inputs.

  • DDeclare `dependencies: ["orders_clean", "fx_rates"]` in the config block of daily_revenue

    Incorrect

    This makes daily_revenue depend on the two table actions only; it does not add their assertions, and a failed assertion of a dependency does not block its dependents.

Dataform runs assertions on every workflow update and alerts on failures, but a failed assertion of action A does not block action B that depends on A. To gate B on A's data quality, set A's assertions as dependencies of B: list them in dependencies, use includeDependentAssertions on a selected dependency, or set dependOnDependencyAssertions for all dependencies.

Question 3 · choose 1

A Cloud Data Fusion batch pipeline reads customer records, standardizes them in a transform stage and writes them to BigQuery. A few records a day have null or malformed fields that make the transform stage fail. The team wants those records caught and written to a separate table for analysis while the good records keep flowing. What should it add to the pipeline?

  1. AAn analytics plugin, such as a joiner, after the transform
  2. BA condition and action plugin that runs at the end of the pipeline
  3. CA second source plugin that rereads the failed records
  4. DAn error handler plugin after the transform, writing to the table
Show the answer and why
  • AAn analytics plugin, such as a joiner, after the transform

    Incorrect

    Analytics plugins perform aggregations and joins across data; they are not the plugins that catch errors raised by a stage.

  • BA condition and action plugin that runs at the end of the pipeline

    Incorrect

    Condition and action plugins run steps that do not manipulate the data in the workflow, such as moving files, so they do not catch bad records.

  • CA second source plugin that rereads the failed records

    Incorrect

    Source plugins connect to the systems a pipeline reads from; they have no way to know which records a later stage rejected.

  • DAn error handler plugin after the transform, writing to the table

    Correct

    Error handler plugins catch the errors that stages hit, such as null values or logical errors, so the failing records can be written to a database or table for analysis.

Cloud Data Fusion groups its plugins into categories by role in the pipeline. Error handler plugins catch records that cause errors in a transform or analytics stage and send them to their own output, so one bad record does not stop the run.

Google Cloud documentation

Question 4 · choose 1

A two-person analytics team transforms data that already sits in BigQuery, entirely in SQL, a few times a day. It wants the dependencies between its steps handled for it, and it does not want the transformation tool to add charges of its own beyond the BigQuery queries that the tool runs. Which tool fits?

  1. ACloud Data Fusion, with pipelines on a Basic edition instance
  2. BManaged Service for Apache Airflow, with BigQuery tasks in a DAG
  3. CDataform, with SQLX workflows whose queries all run in BigQuery
  4. DDataflow, with a Beam pipeline that reads and writes BigQuery
Show the answer and why
  • ACloud Data Fusion, with pipelines on a Basic edition instance

    Incorrect

    Data Fusion is billed for its instance by the minute, from the time the instance is created until it is deleted, whether pipelines run or not; the Basic edition's 120 free hours a month do not cover an instance that stays up, and each pipeline run is also charged for the Managed Service for Apache Spark cluster that runs it.

  • BManaged Service for Apache Airflow, with BigQuery tasks in a DAG

    Incorrect

    A Managed Airflow environment is billed for the time it runs, including the resources of its workers, schedulers and web server.

  • CDataform, with SQLX workflows whose queries all run in BigQuery

    Correct

    Dataform is a free service: it compiles SQLX into SQL, runs the queries in BigQuery in dependency order, and the costs come from the BigQuery queries and related services it uses.

  • DDataflow, with a Beam pipeline that reads and writes BigQuery

    Incorrect

    Dataflow bills for the worker resources each job uses, and a Beam pipeline is code in Java, Python or Go rather than the team's SQL.

Pick the transformation tool from the team's skills and the cost model. For SQL transformations of data already in BigQuery, Dataform manages dependencies and has no charge of its own, while Data Fusion instances, Managed Airflow environments and Dataflow workers are billed as resources.

Question 5 · choose 1

A finance team runs a Cloud Data Fusion batch pipeline every night with the default compute settings. Their cloud bill shows Managed Service for Apache Spark (Dataproc in the guide) clusters appearing in their project only during the runs. Why?

  1. AData Fusion stores pipeline metadata on a Spark cluster that it starts for each user session
  2. BThe Studio starts a Spark cluster in the project while pipelines are being designed
  3. CSomeone attached an always-on Spark cluster to the instance, and it is only billed while it is busy
  4. DData Fusion provisions an ephemeral Spark cluster for each run and deletes it when the run finishes
Show the answer and why
  • AData Fusion stores pipeline metadata on a Spark cluster that it starts for each user session

    Incorrect

    Pipeline metadata and the services that manage pipelines run in a tenant project, not on clusters in the customer project.

  • BThe Studio starts a Spark cluster in the project while pipelines are being designed

    Incorrect

    Designing pipelines happens in the Studio of the instance; clusters are provisioned in the customer project when a pipeline runs.

  • CSomeone attached an always-on Spark cluster to the instance, and it is only billed while it is busy

    Incorrect

    Running pipelines against existing clusters is an option you configure; by default Data Fusion provisions a new cluster for each run.

  • DData Fusion provisions an ephemeral Spark cluster for each run and deletes it when the run finishes

    Correct

    By default Data Fusion creates an ephemeral Managed Service for Apache Spark cluster in the customer project at the start of a run, runs the pipeline with Spark, and deletes the cluster afterwards.

Cloud Data Fusion designs and orchestrates pipelines, and runs them on Managed Service for Apache Spark. By default each run gets an ephemeral cluster that is deleted after the run; teams that manage their own clusters can configure Data Fusion to run on existing clusters instead.

Question 6 · choose 1

A Dataform table definition must check, every time the table is built, that the customer_id and order_date columns never contain NULL values. What is the simplest way to add that check?

  1. AAdd `assertions: { nonNull: ["customer_id", "order_date"] }` to the table's config block
  2. BChange the table's config block to `type: "view"`
  3. CAdd `NOT NULL` to both columns in a separate CREATE TABLE statement that runs before each build
  4. DAdd a `WHERE customer_id IS NOT NULL` filter to the table's SELECT
Show the answer and why
  • AAdd `assertions: { nonNull: ["customer_id", "order_date"] }` to the table's config block

    Correct

    nonNull is a built-in assertion that checks the listed columns are not null in any row; Dataform runs built-in assertions after it creates the table.

  • BChange the table's config block to `type: "view"`

    Incorrect

    The type decides what Dataform builds in BigQuery, a table or a view; it does not test the data.

  • CAdd `NOT NULL` to both columns in a separate CREATE TABLE statement that runs before each build

    Incorrect

    Dataform builds the table from the SQLX definition; checking data on each run is the job of assertions.

  • DAdd a `WHERE customer_id IS NOT NULL` filter to the table's SELECT

    Incorrect

    A filter silently drops the bad rows instead of reporting them, and it leaves order_date unchecked.

Dataform assertions are data quality tests. Built-in assertions such as nonNull, uniqueKey and rowConditions go in a table's config block and run after the table is built; manual assertions can be written in separate SQLX files.

Question 7 · choose 1

A Dataform workflow must read a BigQuery table, `crm.accounts`, that another team loads and that Dataform does not create. The developers want to reference it in their SQLX files the same way as the tables Dataform builds. What should they add to the repository?

  1. AA data source declaration that describes crm.accounts
  2. BA table definition that copies crm.accounts with SELECT *
  3. CA built-in assertion on crm.accounts in a config block
  4. DA workflow configuration that includes crm.accounts
Show the answer and why
  • AA data source declaration that describes crm.accounts

    Correct

    A data source declaration describes a BigQuery table that Dataform does not build, so that table definitions and SQL operations can reference it.

  • BA table definition that copies crm.accounts with SELECT *

    Incorrect

    This would build a second copy of the data on every run instead of simply referencing the existing table.

  • CA built-in assertion on crm.accounts in a config block

    Incorrect

    Assertions test data; they do not make an external table available to ref().

  • DA workflow configuration that includes crm.accounts

    Incorrect

    Workflow configurations schedule runs of actions that Dataform builds; a table that another team loads is not one of them.

Dataform works with BigQuery tables it does not create through data source declarations. Once declared, such a table can be referenced in table definitions like any table that Dataform builds.

Question 8 · choose 1

A Cloud Data Fusion user needs a connector plugin that is not yet in the pipeline palette and wants to browse available plugins and sample pipelines from inside the web interface. Where should the user look?

  1. AThe Wrangler Insights tab
  2. BThe Cloud Data Fusion console, also called the control plane
  3. CThe pipeline's compute profile settings
  4. DThe Hub in the Cloud Data Fusion web interface
Show the answer and why
  • AThe Wrangler Insights tab

    Incorrect

    The Insights tab performs data discovery on a dataset in Wrangler; it does not list plugins to add.

  • BThe Cloud Data Fusion console, also called the control plane

    Incorrect

    The control plane handles the instance itself, such as creating, updating and restarting it, not plugins inside pipelines.

  • CThe pipeline's compute profile settings

    Incorrect

    Compute profiles describe where and how pipelines run, not which plugins are available.

  • DThe Hub in the Cloud Data Fusion web interface

    Correct

    Clicking Hub lets you browse plugins, sample pipelines and other integrations; new plugin versions appear there for compatible instances.

Plugins extend Cloud Data Fusion with sources, transformations, sinks and more. The Hub in the web interface is where users find and add plugins and sample pipelines, including new releases.

Question 9 · choose 1

A company already runs a well-tested transformation process in its integration tool, which cleans and reshapes each file before it leaves the data center. As it moves its reporting to BigQuery, it wants to keep that process and to keep the BigQuery resources spent on transformation low. Which approach fits?

  1. AELT, loading the raw files and rewriting the logic as Dataform workflows
  2. BELT, loading the raw files and then running scheduled queries
  3. CETL, keeping the existing process and loading only its finished output
  4. DELT, streaming the raw records in through the Storage Write API first
Show the answer and why
  • AELT, loading the raw files and rewriting the logic as Dataform workflows

    Incorrect

    ELT is Google's general recommendation, but it moves the transformations into BigQuery, replacing the existing process and using BigQuery resources for the work.

  • BELT, loading the raw files and then running scheduled queries

    Incorrect

    Scheduled queries also transform inside BigQuery after loading, so the transformation work still runs in BigQuery instead of the existing process.

  • CETL, keeping the existing process and loading only its finished output

    Correct

    ETL transforms data before it reaches BigQuery, and Google notes that it is beneficial when an existing transformation process is in place or when the aim is to reduce resource usage in BigQuery.

  • DELT, streaming the raw records in through the Storage Write API first

    Incorrect

    Streaming raw records into BigQuery and transforming them there is still ELT; the transformations would run in BigQuery rather than in the existing process.

The basic decision is where the transformations run. ELT loads raw data into BigQuery and transforms it there, which Google recommends for most customers and suits SQL teams. ETL transforms data before loading, which fits when a transformation process already exists or BigQuery resource usage should stay low.

Practice domain 3 →Practice all domains →