Professional Data EngineerDemo

Data freshness measures the difference between an element’s event time and processing time; a value of about 40 seconds shows that some elements are being processed too late to meet a 30-second requirement. The approximately 1-second delay before Pub/Sub publication does not account for the lateness, so the pipeline requires performance optimization or additional worker capacity.

Cloud Pub/Sub provides managed, asynchronous event ingestion that decouples publishers from downstream processing and is designed for scalable, reliable event delivery. Publishing directly from the data-acquisition devices removes the leased-line dependency without the operational cost of self-managed Kafka clusters or the expense of Cloud Interconnect. Pub/Sub is also a documented fit for streaming data from IoT devices and sensors.

BigQuery supports customer-managed encryption keys (CMEK) from Cloud KMS for data at rest. Creating a CMEK-protected BigQuery table and migrating the old table’s data ensures the stored historical and destination data is protected by the centrally managed key; changing only the ingestion path or Pub/Sub topic does not re-encrypt the BigQuery table’s existing data.

BigQuery’s query execution graph presents the execution plan as stages and exposes timing, resource-use, and step details. A JOIN stage can be inspected directly to identify stages that dominate execution time or resource consumption, making it the most direct way to diagnose JOIN-related bottlenecks.

VPC Service Controls is an additional, IAM-independent control layer that can restrict access to specifically protected Google Cloud services. Placing both projects in a perimeter that restricts Cloud Storage, and allowing only the Development Team through an access level, permits that team to access Cloud Storage while blocking the External Team's Cloud Storage requests despite its inherited IAM role. Because BigQuery is not restricted by this perimeter, both teams retain their existing IAM-based BigQuery read access.

Cloud Dataproc is a fully managed service for Apache Hadoop workloads, reducing cluster-management overhead while supporting existing Hadoop jobs. Its automatically installed Cloud Storage connector exposes Cloud Storage through Hadoop-compatible paths, and data stored in Cloud Storage persists after the Dataproc cluster is shut down.

A non-incremental BigQuery materialized view supports outer joins and analytic functions when allow_non_incremental_definition is enabled. Its max_staleness setting can permit results up to four hours old, so visualization queries can use precomputed data for lower latency. Enabling refresh maintains the materialized view automatically, avoiding a separately managed export pipeline.

Signed URLs grant access only until their configured expiration time. A long-running transfer can therefore receive HTTP 403 responses for objects whose signed URLs expire before Storage Transfer Service fetches them. Regenerating signed URLs with a longer validity period for the remaining objects restores authorization, and splitting the work into smaller parallel jobs shortens the time before each URL is used.

Cloud Workflows is a fully managed, serverless service for executing Google Cloud services in an explicitly defined order. Its connectors support Dataflow jobs, BigQuery, and Vertex AI, making it suitable for a sequential cross-service pipeline without managing orchestration infrastructure.

BigQueryInsertJobOperator submits the BigQuery SQL job that performs the transformation and can write results to an existing destination table. Cloud Composer/Airflow task settings support retries and email_on_failure; the email is sent when the operator reaches its final failed state after retries are exhausted. This meets the requirement to retry the SQL job and notify only after the configured failed attempts.

BigQuery sharing (formerly Analytics Hub) uses publishable listings to securely share BigQuery datasets across organizational boundaries at scale. Partners can discover and subscribe to a listing, creating a linked dataset in their own Google Cloud project for read-only querying without replicating the shared data.

Copy the required tables to a dataset that is not protected by the original CMEK, then publish that dataset through an Analytics Hub listing. Analytics Hub supports cross-organization sharing through listings and gives subscribers a read-only linked dataset. CMEK access is managed through Cloud KMS permissions for BigQuery service accounts; keys are not distributed to partner organizations or embedded in authorized views.

A data mesh decentralizes data ownership to business domains while enabling governed sharing. Dataplex Universal Catalog can model departmental domains as data lakes, organize them with zones, and attach Cloud Storage buckets and BigQuery datasets as data assets. This structure enables each department to own and share its domain data.

BigQuery recommends denormalizing hierarchical parent-child data that is frequently queried together by storing the parent as one row and the child records as nested, repeated fields. This preserves the one-to-many transaction-header-to-line relationship while avoiding the communication and shuffle overhead of frequent joins.

Dataproc initialization actions can download job dependencies from Cloud Storage. When cluster nodes lack Internet access, dependencies should be placed in Cloud Storage and accessed through private Google connectivity; staging them in a bucket within the VPC Service Controls perimeter preserves the required security boundary.

Cloud Pub/Sub is a scalable, durable service for ingesting and distributing events from many sources. Cloud Dataflow provides managed, low-latency stream processing at scale, and Cloud Storage supplies reliable durable storage for the processed data. This combination directly supports real-time ingestion, processing, and storage.

Public Data Catalog tags can be found by tag value with a simple search query and do not require separate tag-template access to appear with assets the user can view. The bigquery.metadataViewer role permits table metadata discovery but not table-data reads, while bigquery.dataViewer granted on individual tables permits reading those tables. Thus, make the template public and grant bigquery.dataViewer only to HR on the sensitive tables.

Worker pod evictions commonly result from out-of-memory conditions. Reducing worker concurrency lowers the number of tasks handled simultaneously by each worker, giving each task more memory; increasing the maximum number of workers can maintain overall task capacity. Increasing the memory allocated to Airflow workers directly raises the resource limit that is causing the evictions.

A 1-hour sliding (hopping) window evaluated every 5 minutes creates overlapping windows, each containing the most recent hour of messages, so it can calculate and alert on a current 1-hour moving average at five-minute intervals. Google Cloud documents that hopping windows overlap and are used for running averages; fixed windows are disjoint intervals.

Cloud Interconnect supplies a high-bandwidth private connection from the on-premises environment to Google Cloud, while Storage Transfer Service is built to move large on-premises datasets to Cloud Storage reliably at scale. Storage Transfer Service encrypts data in transit with TLS, including when the transfer uses Cloud Interconnect, and supports private API endpoints for an additional security layer.

Looker Studio/Data Studio temporarily stores query results and serves them while the applicable freshness threshold remains in effect; reloading the browser can therefore continue to show cached data. Disabling the report cache causes visualizations to retrieve current data from the BigQuery source rather than using cached results.

BigQuery column-level access control restricts access to individual fields through policy tags and IAM permissions. Applying separate tags to medical, financial, and identifier columns lets each group receive access only to the columns it needs, enforcing least privilege without duplicating the table.

Duplicate data in only a subset of tables after a pipeline deployment indicates that more than one pipeline version may be ingesting the same Pub/Sub data into those tables. Duplicate rows confirm the symptom; BigQuery audit logs identify the jobs associated with table writes, and Dataflow monitoring correlates those jobs with their start times and pipeline versions. Stopping all but the latest version removes the concurrent writer and prevents further duplicate ingestion. BigQuery audit logs record table and job activity, while the Dataflow monitoring interface provides job status and timing information.

Cloud GPUs can run TensorFlow custom operations when those operations have GPU kernel implementations, allowing the matrix-multiplication-heavy training loop to be accelerated. Cloud TPUs do not become compatible with arbitrary custom C++ TensorFlow ops simply because GPU kernel support was added; TPU execution requires supported TPU/XLA-compatible operations.