PROFESSIONAL-DATA-ENGINEER Practice Questions

The onfailurecallback parameter is a standard feature of Apache Airflow operators. It allows you to specify a Python callable (a function) that will be executed only when a task instance fails. This is the most direct and appropriate mechanism to trigger a custom action, such as sending a notification, in response to a task failure. By assigning a function with notification logic to this parameter for the specific task, you ensure that you are alerted precisely when that task does not succeed after all configured retries have been exhausted.

A. The slamissed metric is triggered when a task does not complete within its defined Service Level Agreement (SLA) timeframe. A task can miss its SLA and still succeed, so this does not specifically track failures.

B. The slamisscallback function is executed when a task misses its SLA. This is related to the timeliness of task completion, not its success or failure status.

C. The onretrycallback function is executed each time a task is retried. A task might retry and then ultimately succeed, so this callback does not signify a final failure.

1. Apache Airflow Documentation, BaseOperator API Reference: The official documentation for Airflow's BaseOperator (the parent class for all operators) defines the callback parameters.

onfailurecallback: "a function to be called when a task instance of this task fails."

onretrycallback: "a function to be called when a task instance of this task is retried."

slamisscallback: "function to be called when a task instance of this task misses its SLA."

Source: Apache Airflow Documentation, apacheairflow.models.baseoperator.py.

2. Google Cloud Documentation, Writing DAGs (Airflow): Google's documentation for Cloud Composer explicitly directs users to the core Apache Airflow concepts for authoring DAGs, including the use of callbacks for notifications. It states, "Airflow provides several ways to set up notifications... A common way is to use Airflow's built-in callback mechanism."

Source: Google Cloud, Cloud Composer Documentation, "Writing DAGs (Airflow)", section on "Sending email notifications".

3. Google Cloud Documentation, Monitoring environments: This page lists the Airflow metrics exported to Cloud Monitoring. It includes airflow/dag/task/slamisses, confirming that SLA misses are tracked as a distinct metric from general task failures.

Source: Google Cloud, Cloud Composer Documentation, "Key metrics for monitoring environments", section "Airflow metrics".

The most effective solution is to adopt a serverless Extract-Load-Transform (ELT) pattern using Cloud Storage and BigQuery. First, load the raw structured data from Cloud Storage directly into a staging table in BigQuery. BigQuery is a fully-managed, serverless data warehouse designed for petabyte-scale analytics. Then, convert the PySpark transformation logic into BigQuery Standard SQL queries. Executing these SQL queries within BigQuery leverages its massively parallel processing engine to perform the transformations far more quickly than the original Spark job. The final results are then written to a new, permanent table in BigQuery. This approach meets all requirements: it is serverless, uses SQL, and significantly expedites both development and execution time.

A: Dataproc is a managed service for Spark/Hadoop, but it is not fully serverless as it requires cluster configuration and management, which does not meet the "serverless tool" and "expedite development" requirements as effectively as BigQuery.

B: Cloud SQL is a relational database service (OLTP) and is not designed or optimized for large-scale analytical data transformations. Ingesting and processing large batch data in Cloud SQL would be inefficient and slow.

D: This option suggests using the Apache Beam Python SDK. While this can be run on the serverless Dataflow service, it contradicts the user's explicit requirement to move from a programmatic tool (PySpark) to SQL syntax.

1. BigQuery as a Serverless Data Warehouse: "BigQuery is Google's fully managed, petabyte-scale, and cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near real time. With BigQuery, there’s no infrastructure to manage..."

Source: Google Cloud Documentation, "What is BigQuery?", Introduction.

2. ELT Pattern with BigQuery: "A common pattern is to load data from a transactional data source into Cloud Storage, and then use a batch load job to load the data into BigQuery. You can then use BigQuery to transform the data."

Source: Google Cloud Documentation, "Introduction to designing data processing systems", Section: "Batch data processing".

3. Dataproc vs. BigQuery: Dataproc provides a managed environment for open-source tools like Apache Spark. In contrast, "BigQuery is a serverless, highly scalable, and cost-effective cloud data warehouse... You don't need to deploy or manage any clusters." This highlights why BigQuery is the superior choice for the "serverless" requirement.

Source: Google Cloud Documentation, "BigQuery for data warehouse practitioners", Section: "BigQuery and other technologies".

4. Cloud SQL Use Case: "Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server." It is optimized for online transaction processing (OLTP), not the large-scale batch analytics (OLAP) described in the scenario.

Source: Google Cloud Documentation, "Cloud SQL", Product overview.

The pipeline must be a streaming job because it ingests data from Cloud Pub/Sub, which is an unbounded, continuous data source. The pipeline will use PubSubIO to read from Pub/Sub and BigQueryIO to both read the static reference data and write the enriched output.

The key requirement is enriching the streaming data with a small, static reference dataset. The most efficient and standard Apache Beam pattern for this scenario is using side-inputs. A side input allows a ParDo transform to access an entire PCollection (the reference data) in-memory while processing each element of the main PCollection (the streaming data), making it ideal for lookups.

A. A batch job is incorrect because Cloud Pub/Sub provides a continuous, unbounded stream of data that requires a streaming pipeline for real-time processing.

B. JdbcIO is for connecting to databases via a JDBC driver, not for BigQuery. Side-outputs are used to split a single PCollection into multiple outputs, not for data enrichment.

D. Side-outputs are for splitting a PCollection into multiple distinct collections based on some criteria, which is different from the required enrichment task.

1. Apache Beam Programming Guide, "Side inputs": "A side input is an additional input that a ParDo transform can access each time it processes an element in the input PCollection... Side inputs are useful when your ParDo needs to inject data that is computed at pipeline construction time, or data that is computed in an earlier stage of the same pipeline... especially when the side input is much smaller than the main input PCollection." This directly supports using side-inputs for the small, static reference data.

2. Google Cloud Documentation, "Common stream analytics patterns": Under the "Data enrichment" section, it describes the pattern of joining a streaming data source with a slowly changing or static dataset. For small datasets, it states: "If the dataset is small enough to fit into memory on a worker, you can load the dataset from Cloud Storage or a database like Cloud SQL or Bigtable and use it as a side input for a Beam ParDo transform." This validates the use of side-inputs for small enrichment tables.

3. Apache Beam Documentation, "Built-in I/O Transforms": This page lists the I/O connectors. It specifies ReadFromPubSub and WriteToPubSub (part of PubSubIO) for Cloud Pub/Sub, and ReadFromBigQuery and WriteToBigQuery (part of BigQueryIO) for BigQuery, confirming the correct I/O transforms.

The primary constraint is that Cloud Dataproc nodes cannot access the public internet, which prevents initialization actions from downloading dependencies from public repositories. The correct and secure approach is to pre-stage all required dependencies in a location accessible from within the Google Cloud private network. A Cloud Storage bucket is the ideal service for this. By copying dependencies to a bucket and ensuring the cluster's subnet has Private Google Access enabled, the initialization action script can use gsutil to securely copy the files from the bucket to the cluster nodes for installation, fully complying with the security policy.

A. The Cloud SQL Proxy is a specific tool for connecting securely to Cloud SQL instances; it is not used for managing or deploying general software dependencies on a cluster.

B. Creating an SSH tunnel to the internet directly contravenes the explicit security requirement that nodes must not have internet access. This is a policy violation, not a solution.

D. The Network User role (roles/compute.networkUser) grants permissions to use network resources, such as subnets in a Shared VPC. It does not provide a mechanism for accessing software dependencies.

1. Google Cloud Documentation - Dataproc Initialization Actions: "We recommend that you store initialization actions in a Cloud Storage bucket... When you create a cluster, Dataproc copies the scripts from the bucket to the master and worker nodes in the cluster and then runs the scripts." This establishes Cloud Storage as the standard location for initialization action resources. (Source: Google Cloud, "Initialization actions," Section: "Where to store initialization actions").

2. Google Cloud Documentation - Configuring Private Google Access: "With Private Google Access, VMs that only have internal IP addresses (no external IP addresses) can reach the external IP addresses of Google APIs and services," including Cloud Storage. This is the underlying network configuration that enables VMs without internet access to reach the Cloud Storage API. (Source: Google Cloud, "Private Google Access," Section: "Overview").

3. Google Cloud Documentation - Dataproc Cluster Networking: In scenarios without external IP addresses, "To access Google Cloud services, including Cloud Storage where the Dataproc staging bucket is located, you must enable Private Google Access on the cluster subnet." This explicitly links the need for Private Google Access to allow Dataproc to access Cloud Storage in isolated networks. (Source: Google Cloud, "Dataproc Cluster Network Configuration," Section: "Cluster network requirements and considerations").

The analysts are querying geospatial trends in the lifecycle of a package, which means their queries will frequently filter by a specific packagetrackingID. The current table is partitioned by ingest date, which forces BigQuery to scan the entirety of each relevant date partition to find all events for a single package. By implementing clustering on the packagetrackingID column, BigQuery will physically co-locate all data for the same package within each partition. This allows the query engine to prune the blocks it needs to scan, significantly reducing query processing time and cost for these common analytical queries.

A. Clustering on the ingest date column provides minimal benefit because the table is already partitioned by that same column, which already segregates the data.

C. Tiering data to Cloud Storage and using external tables is a cost-optimization strategy for cold data, not a performance enhancement for frequently accessed data.

D. Re-partitioning by delivery date is ineffective for analyzing the full package lifecycle, as many records for in-transit packages would have a NULL delivery date.

1. Google Cloud Documentation, "Introduction to clustered tables": "When you submit a query that filters by a clustered column, BigQuery uses the sorted blocks to eliminate scans of unnecessary data... Use clustering for columns that are frequently used in query filters... especially columns with high cardinality (many distinct values)." The package-trackingID is a high-cardinality column frequently used for filtering, making it an ideal candidate for clustering. (Source: Google Cloud, Introduction to clustered tables, https://cloud.google.com/bigquery/docs/clustered-tables#whentouseclustering)

2. Google Cloud Documentation, "Introduction to partitioned tables": This document explains that partitioning and clustering can be used together. "For example, if you partition your table by date and cluster it by customerid, each partition will contain data for that date, sorted by customerid." This directly maps to the scenario of partitioning by ingest date and clustering by package-trackingID to optimize queries. (Source: Google Cloud, Introduction to partitioned tables, https://cloud.google.com/bigquery/docs/partitioned-tables#combinepartitioningandclustering)

3. Google Cloud Blog, "BigQuery explained: An overview of BigQuery's architecture": This article discusses the separation of storage and compute. "Colossus... organizes data in a columnar format... When you run a query, the BigQuery query engine reads only the columns it needs... Clustering tables can further improve query performance by sorting the data based on the contents of one or more columns in the table’s schema." This confirms that clustering physically organizes data to improve scan efficiency. (Source: Google Cloud Blog, BigQuery explained: An overview of BigQuery's architecture, https://cloud.google.com/blog/products/bigquery/bigquery-explained-an-overview-of-bigquerys-architecture)

Creating an authorized view is the most effective solution as it directly meets all the requirements. An authorized view allows you to grant third parties access to the results of a query without giving them access to the underlying source table. This ensures data is always current because the view queries the live data. For cost-effectiveness, the data owner only pays for the storage of the original dataset. The third-party company is billed for the queries they run against the view in their own Google Cloud project, thus keeping costs low for the data owner.

B: Exporting data to Cloud Storage creates a separate, static copy. This fails the requirement for the data to be current and incurs additional storage costs in Cloud Storage for the data owner.

C: Creating a separate dataset duplicates the data, which doubles the storage costs in BigQuery for the data owner. It also requires a separate process to keep the data synchronized, failing the "current data" requirement.

D: Using a Cloud Dataflow job is overly complex and expensive for this use case. It incurs compute costs for the Dataflow job in addition to duplicating data, and the data would still only be as current as the last job run.

1. Google Cloud Documentation - Introduction to authorized views: "An authorized view allows you to share query results with particular users and groups without giving them access to the underlying tables... Because the view is a query, the data shared with the view's users is always current with the view's source tables." This directly supports the "current data" and access control aspects of the correct answer.

2. Google Cloud Documentation - BigQuery pricing, Query pricing models: "When you query data in BigQuery... you are charged for the number of bytes read... Query costs are billed to the Google Cloud project of the user who queries the view." This confirms that the third party, not the data owner, pays for the queries, satisfying the "low cost" requirement for the owner.

3. Google Cloud Documentation - Create authorized views: "By using an authorized view, you can share your data with third parties without exposing your raw data and by controlling the costs incurred for querying the data." This statement explicitly recommends authorized views for the exact scenario described in the question.

4. Google Cloud Documentation - BigQuery export pricing: "There is no charge for exporting data from BigQuery, but you do incur charges for storing the exported data in Cloud Storage." This shows that option B, while having a free export job, still incurs additional storage costs, making it more expensive than an authorized view which does not duplicate storage.

To query specific partitions in a BigQuery ingestion-time partitioned table, you must use a pseudo-column in the WHERE clause. The PARTITIONTIME pseudo-column contains the UTC timestamp for the start of the partition. By filtering on PARTITIONTIME (or the simpler PARTITIONDATE for daily partitions), you instruct BigQuery to scan only the relevant partitions. This process, known as partition pruning, significantly reduces the amount of data scanned, which lowers query costs and improves performance.

A. Using a column named DAY only works if the table is partitioned on a time-unit column with that specific name, which is not the general or required method for ingestion-time partitioning.

B. Applying functions like EXTRACT() on a partitioning column can prevent BigQuery's query optimizer from being able to prune partitions, leading to a full table scan and higher costs.

D. DATE BETWEEN is a condition used within a WHERE clause. While it can be used on a time-unit partitioning column to prune partitions, it is not the specific pseudo-column mechanism for ingestion-time partitioning.

1. Google Cloud Documentation, "Querying partitioned tables": "To filter queries on ingestion-time partitioned tables, use the PARTITIONTIME pseudo-column... When you filter on PARTITIONTIME, you can use a date or timestamp function to specify the partition." This document explicitly details the use of PARTITIONTIME for partition pruning.

2. Google Cloud Documentation, "Introduction to partitioned tables": "BigQuery provides two pseudo columns in ingestion-time partitioned tables: PARTITIONTIME... and PARTITIONDATE... To prune partitions, filter on these pseudo columns in your query." This source reinforces that PARTITIONTIME is the designated mechanism.

3. UC Berkeley, Data 101: Data Engineering, Lecture 12: BigQuery: In the section on "Partitioned Tables," the course material explains: "For ingestion time partitioned tables, use the pseudo column PARTITIONTIME to filter partitions." This confirms the concept in an academic context. (Reference to specific slide/page number varies by semester, but the concept is consistently taught in this course module).

BigQuery automatically caches the results of queries that have been run previously. When an identical query is submitted again, BigQuery can retrieve the results directly from this temporary cache. This process is significantly faster than re-executing the query. Because no data is processed for a cached query, there is no charge for on-demand analysis when the results are served from the cache. The job statistics will indicate a cacheHit.

A. By default, a query's results are not cached.

This is incorrect. Query result caching is an automatic feature and is enabled by default in BigQuery to improve performance and reduce costs.

B. BigQuery caches query results for 48 hours.

This is incorrect. BigQuery caches query results for approximately 24 hours, but the cache is a best-effort mechanism and may be invalidated sooner.

C. Query results are cached even if you specify a destination table.

This is incorrect. When a query specifies a destination table for its results, the query is always executed, and the cache is not used.

1. Google Cloud Documentation, BigQuery, "Using cached query results":

"When you run a query, BigQuery checks to see if the query results are in the cache... Retrieving results from the cache is much faster than running the query, and there is no charge for cached queries."

"Query results are cached for 24 hours."

Under the "Limitations" section: "You can't use the query cache in the following situations: ... When a destination table is specified."

2. Google Cloud Documentation, BigQuery, "Control costs in BigQuery":

Under the "Avoid running the same query multiple times" section: "BigQuery writes all query results to a table... If you submit the exact same query again, BigQuery will retrieve the results from the cached results table, and you will not be charged for the query."

3. Google Cloud Documentation, BigQuery, "Introduction to BigQuery jobs":

Under the statistics.cacheHit field description: "If true, indicates that the query result was fetched from the query cache." This confirms the mechanism for identifying a cached query.

An authorized view is the ideal solution for this scenario. It is a virtual table defined by a SQL query that can perform aggregations on the source data. By sharing only the view, you grant access to the aggregated results without exposing the underlying user-level data. Since a view is a logical construct and does not store data, it incurs no additional storage costs, satisfying the cost-minimization requirement. When users from other projects query the authorized view, the processing costs are billed to their respective projects, fulfilling the final requirement.

B: Creating a new dataset for the view is an unnecessary step. The core mechanism that provides secure, cost-effective access is the authorized view itself, not the dataset it resides in.

C: Creating a new table with aggregate results (a materialized view) would incur additional storage costs, which violates the requirement to minimize storage costs.

D: Granting the dataViewer IAM role on the dataset would provide read access to all tables within it, directly exposing the sensitive user-level data and failing the primary security requirement.

1. Google Cloud Documentation - Introduction to views: "A view is a virtual table defined by a SQL query. ... You are charged for the storage of the data in the tables referenced by the view, but you are not charged for the storage of the view." This confirms the storage cost minimization aspect.

Source: Google Cloud BigQuery Documentation, "Introduction to views", Section: "View limitations".

2. Google Cloud Documentation - Create authorized views: "An authorized view allows you to share query results with particular users and groups without giving them access to the underlying tables. You can also use the view's SQL query to restrict the columns and rows that the users can see." This directly supports the access control requirement.

Source: Google Cloud BigQuery Documentation, "Create authorized views".

3. Google Cloud Documentation - BigQuery pricing: "When a user runs a query, including a query against a view... the user is billed for the query based on the pricing model of the project that the user is using." This confirms that analysis costs are assigned to the querying project.

Source: Google Cloud BigQuery Documentation, "Understand BigQuery pricing", Section: "Query pricing models".

The optimal design for this time-series use case is to use a single table with a composite row key that facilitates the primary query: retrieving the most recent entry. The row key [index]#[reversetimestamp] achieves this. By prefixing with the stock index, all data for a given index is grouped together. Using a reverse timestamp (e.g., Long.MAXVALUE - timestamp) ensures that the most recent data (with the highest original timestamp) has the lowest reverse timestamp value. This places the newest entries at the very beginning of each index's row range, allowing the most recent price to be retrieved with a simple and highly efficient prefix scan limited to one row.

A. Using a standard timestamp ([index]#[timestamp]) sorts data chronologically, placing the most recent entries at the end of the row range. Retrieving the latest entry would require a less efficient reverse scan.

C. Creating a separate table for each index is a Bigtable anti-pattern that increases management overhead. Additionally, using a standard timestamp presents the same query inefficiency as option A.

D. While using a reverse timestamp is correct for the query pattern, creating a separate table for each index is a suboptimal design practice for storing related data in Cloud Bigtable.

1. Google Cloud Documentation, "Schema design for time series data": This document explicitly recommends using a reverse timestamp for this exact use case. It states, "To make it easy to read the latest values first, use a reversed timestamp in your row key. A reversed timestamp is a timestamp that is subtracted from the maximum possible value for a timestamp...". It also provides an example row key [metrictype]#[reversedtimestamp], which directly maps to the structure in the correct answer.

Source: Google Cloud Documentation, Cloud Bigtable > Documentation > Guides > Schema design for time series data.

2. Google Cloud Documentation, "Cloud Bigtable schema design": This guide outlines general best practices, including the preference for a single table over multiple small tables for related data. It notes, "In general, a schema with one table is the most efficient." This supports the "one unique table" approach in options A and B and refutes the multi-table approach in C and D.

Source: Google Cloud Documentation, Cloud Bigtable > Documentation > Guides > Schema design.

3. Designing Data-Intensive Applications by Martin Kleppmann (ISBN-13: 978-1449373320): While not a Google-specific source, this is a foundational academic text on data systems. Chapter 3 discusses SSTables and LSM-Trees, the underlying technology for Bigtable. It explains how data is stored sorted by key, which is the fundamental principle making a reverse-timestamp key efficient for "latest-first" queries, as it turns the query into a simple scan from the beginning of a key range.

Source: Chapter 3, "Storage and Retrieval", Section on "SSTables and LSM-Trees".