Hard Difficulty Questions

Use a smaller machine type for the project.

Why it fails: BigQuery bills by bytes scanned, not by slot or VM size, so shrinking the machine type changes nothing about query cost. Smaller machine types suit workloads constrained by compute or memory, such as long-running interactive analysis on fixed-reservation slots, not scan-based billing.

Increase the query timeout limit.

Why it fails: Timeout limits control how long a query may run, not how much data it scans, so raising it cannot reduce bytes-billed cost. It tempts when queries fail on timeouts, where extending the limit is the right fix, but here the goal is lowering cost, which requires reducing scanned data.

Switch to BigQuery Omni.

Why it fails: BigQuery Omni runs queries against data in Amazon S3 or Azure Blob Storage, so it cannot reduce the bytes scanned for data already held in BigQuery. It is the right choice only when the data physically resides in another cloud and you must avoid egress.

address->city

Why it fails: BigQuery uses dot notation for nested record fields, so address.city is required; the -> operator is not valid BigQuery SQL syntax. The arrow form appears in other dialects, which makes it tempting, but it fails to parse here.

address[city]

Why it fails: Square brackets denote array element or subscript access in BigQuery, not record field traversal, so address[city] is invalid for a STRUCT. Bracket syntax would be correct when indexing into a REPEATED field, such as address[OFFSET(0)], not for reading a named nested field.

JSON_EXTRACT(address, '$.city')

Why it fails: JSON_EXTRACT expects a JSON-formatted STRING column and a JSONPath; on a native STRUCT column it errors or returns NULL. It is the right tool when nested data is stored as a JSON string, but BigQuery's native record type uses dot notation instead.

Create a BigQuery dataset and use authorized views to restrict access to specific columns.

Why it fails: Authorized views can restrict access to specific columns, but they require the data to be in BigQuery tables, not external tables. For external data, you would need to create a view over the external table, but authorized views do not provide the same column-level policy tag enforcement. This approach does not leverage the native column-level security features for external tables.

Enable uniform bucket-level access on the Cloud Storage bucket.

Why it fails: Uniform bucket-level access simplifies permissions at the bucket level, but it does not provide column-level access control for BigQuery queries. It controls access to objects in Cloud Storage, not to columns in a query. This does not meet the requirement for fine-grained column-level security.

Set up a Dataplex lake and zone, and register the Cloud Storage bucket as an asset.

Why it fails: While Dataplex can manage and govern data in Cloud Storage, it does not by itself provide column-level access control for BigQuery queries. You would still need to use BigQuery external tables and policy tags. Registering the bucket in Dataplex adds governance but does not directly enable querying or column-level security.

BigQuery's on-demand query cache is returning an older result set to the dashboard instead of reading current data.

Why it fails: The query cache returns a stored result only when the underlying table has not changed, and any write invalidates it. With continuous streaming inserts, cached results would be invalidated constantly rather than served stale. A caching explanation would also produce identical results for repeated identical queries, not a divergence between two different query paths.

The dashboard's Looker model uses a different time zone setting than the BigQuery report, shifting some rows into adjacent days.

Why it fails: A time zone mismatch can move rows between day boundaries and change daily breakdowns, but it does not alter grand totals across the full period. The users report disagreement in totals, not in how rows are distributed across days. Time zone configuration is worth auditing for date-level reporting, yet it cannot explain a difference in the overall sum.

Looker is applying a symmetric aggregate that recalculates totals differently each time the dashboard is loaded.

Why it fails: Symmetric aggregates are deterministic: they produce the same result for the same underlying rows and exist to prevent double counting across joins. They do not introduce run-to-run variation in totals. The observed disagreement between two views of the same data points to timing rather than to aggregation logic, so this explanation does not fit the symptom.

Faster processing

Why it fails: Extra workers add scheduling and shuffle overhead on a small dataset, so wall-clock time often rises rather than falls. It is tempting because worker count scales throughput on large, parallelisable pipelines, where more workers genuinely shorten processing.

Decreased latency

Why it fails: Latency is governed by pipeline design and per-element work, not worker count; adding workers does not reduce it for a small dataset. It is tempting because horizontal scaling is the standard lever for latency in high-volume streaming pipelines, where it does help.

Data corruption

Why it fails: Dataflow guarantees exactly-once processing regardless of worker count, so extra workers cannot corrupt data. It is tempting because concurrent writes to a shared sink can race, which is why sinks need idempotent writes; that risk exists at any worker count, not specifically a high one.

In the dashboard filter settings.

Why it fails: Dashboard filters restrict which rows appear; they do not alter a measure's value formatting. Filter settings are tempting because dashboards expose many display controls, but number formatting belongs to the measure's value_format parameter in the LookML definition, not the filter panel.

By editing the underlying SQL.

Why it fails: A measure's format is set by its value_format parameter in LookML, not by rewriting generated SQL, which Looker produces from the model. Editing SQL is tempting because it appears to control output, but custom SQL changes query logic, not the display format of aggregated values.

In the 'User' settings.

Why it fails: User settings control personal preferences such as locale and timezone, not a measure's value_format, which is defined in LookML. This is tempting because locale can influence number rendering, but the report's measure formatting is model-defined and unaffected by individual user configuration.

Enable 'Version History' in the Airflow UI.

Why it fails: Airflow's Version History tracks DAG code revisions within the scheduler's metadata, not object-level changes in the GCS bucket, and omits actor identity. It is correct for reviewing DAG code edits in the Airflow UI, not bucket auditing.

Use Cloud Monitoring to alert on 'gcs_object_write' events.

Why it fails: Cloud Monitoring alerting on gcs_object_write detects writes but retains no immutable record of who changed which DAG or when, so it cannot serve as an audit trail. It suits real-time operational alerting, not compliance auditing.

Write a custom DAG to scan the bucket every minute.

Why it fails: A polling DAG captures only the bucket state at each minute, missing intermediate overwrites and deletions, and adds scheduler load; it provides no immutable log. Custom DAGs suit bespoke orchestration tasks, not tamper-evident audit trails.

The user has a slow internet connection

Why it fails: Data quality tasks execute server-side against cloud storage; a slow client connection affects only the administrator's console responsiveness, not task execution. It tempts because latency is a familiar cause of failures elsewhere, but Dataplex and BigQuery quality jobs run within Google Cloud, so bandwidth at the user's location is irrelevant.

The data files were moved to another lake

Why it fails: Moving files to another lake changes the storage location, but quality tasks reference configured datasets and tables, so relocation alone does not break them unless the referenced path or permissions change. It tempts because missing source data does cause failures, yet the actual cause would be a broken reference or revoked access, not the move itself.

Data quality task

Why it fails: A data quality task evaluates rules such as null checks or range constraints; it does not scan and tag PII columns. It is tempting because data quality tasks also run across Dataplex lakes and produce scan results, and enabling one would be correct when validating completeness or conformance rather than detecting sensitive data.

Lifecycle management policy

Why it fails: Lifecycle management policies govern storage tiering and deletion of Dataplex assets, not content inspection. Detecting and tagging PII requires the Sensitive Data Protection discovery scan, which profiles BigQuery data and applies classification tags automatically. Lifecycle rules would be the right choice for expiring or archiving lake zones on a schedule.

IAM policy inheritance

Why it fails: IAM policy inheritance propagates access permissions down the resource hierarchy; it performs no data inspection and cannot identify PII. Automatic detection and tagging of sensitive columns is delivered by the Sensitive Data Protection discovery scan. IAM inheritance would be correct when you need consistent access control across projects, folders and lakes.

Use Dataflow with the Pub/Sub to BigQuery template and set the --discardUnknownFields option.

Why it fails: The --discardUnknownFields option is not a Dataflow pipeline option; it is used in other contexts like BigQuery load jobs to ignore unknown fields. Using the Pub/Sub to BigQuery template without additional configuration does not guarantee exactly-once processing or handle late data. This option is incorrect because it references an irrelevant flag and does not address the requirements.

Use BigQuery's streaming ingestion with a Pub/Sub subscription directly.

Why it fails: BigQuery's streaming ingestion can write data directly from Pub/Sub, but it does not provide exactly-once semantics by default; duplicates can occur. It also does not handle late-arriving data with custom windowing. While it is low-latency, it lacks the processing capabilities needed for exactly-once and late data handling. Dataflow is required to implement these advanced features.

Use Cloud Data Fusion with a Realtime pipeline that writes to BigQuery.

Why it fails: Cloud Data Fusion is a managed ETL service, but it is not designed for exactly-once processing in the same way as Dataflow. While it can handle real-time data, achieving exactly-once semantics and handling late data with custom windowing is more complex and may not be as cost-effective. Dataflow with Apache Beam is the recommended tool for this scenario due to its native support for these features.

Define a SQL view on the folder

Why it fails: Dataplex assets select data via include/exclude patterns on the storage bucket, not SQL views, which query BigQuery tables rather than defining GCS folder scope. A SQL view is tempting because it filters data logically, and would be correct for restricting rows in a BigQuery dataset asset.

Use an IAM condition on the bucket

Why it fails: IAM conditions govern who may access objects, not which files Dataplex discovers and catalogues; the asset still scans the entire bucket. IAM conditions are tempting because they restrict access at scale, and would be correct when the requirement is limiting principals rather than defining asset scope.

Move the files to a separate bucket

Why it fails: Moving files to a separate bucket changes object location rather than defining an inclusion rule, so the asset still cannot express which folder within a bucket to scan. Bucket relocation suits physical data-residency or lifecycle separation; folder scoping is configured through the asset's include-prefix or filter pattern.

In the Cloud Storage metadata

Why it fails: Cloud Storage metadata stores object-level attributes such as content type and custom key-value pairs; it has no column-level schema or constraint model, so a NOT NULL rule on user_id cannot be expressed there. It is tempting because Cloud Storage holds the underlying files, but data quality rules belong in the Dataplex Data Quality task specification.

In the BigQuery table schema

Why it fails: A BigQuery table schema defines column names and data types, and can enforce NOT NULL as a column constraint, but the Data Quality task evaluates rules defined in its own specification, not inherited from the schema. It is tempting because the schema is where column definitions live, yet the task's rule set is configured separately.

In the Dataplex Asset definition

Why it fails: A Dataplex Asset definition registers the storage resource and its discovery settings; it holds no column-level rule configuration, so a NOT NULL check on user_id cannot be declared there. It is tempting because assets represent the governed data, but validation rules are defined within the Data Quality task itself.

Eventarc

Why it fails: Eventarc routes events from sources such as Cloud Storage or Pub/Sub to targets; it has no cron scheduling capability. It is tempting because it triggers workflows from event-driven sources, which is correct when a workflow must react to a file upload or audit log event rather than a fixed time.

Cloud Tasks

Why it fails: Cloud Tasks queues asynchronous HTTP requests for deferred or rate-limited delivery; it does not generate recurring schedules. It is tempting because it decouples invocation from execution, which suits dispatching tasks to a worker endpoint on demand, not firing a workflow at set intervals.

Pub/Sub

Why it fails: Pub/Sub delivers published messages to subscribers; it does not itself emit messages on a cron timetable. It is tempting because it decouples producers from workflow triggers, which is correct when an external system publishes an event, not when the trigger must occur at scheduled times.

Network latency

Why it fails: Network latency does not drive Dataflow autoscaling; the runner evaluates backlog, measured as the ratio of outstanding work to throughput, to size the worker pool. Latency monitoring is tempting because it indicates pipeline health, and would matter when diagnosing slow I/O against external services, not worker count.

Memory usage

Why it fails: Memory usage is not the autoscaling signal; the Dataflow runner scales on backlog, comparing outstanding work against current throughput. Memory monitoring is tempting because it identifies hot keys and out-of-memory workers, and would be the right metric when tuning worker machine type or diagnosing memory-related failures.

User-defined triggers

Why it fails: User-defined triggers control window emission timing in the pipeline's business logic, not worker pool sizing. They are tempting because triggers influence throughput and latency, and would be correct when you need custom late-data handling or early speculative results from windows, not autoscaling decisions.

Dataprep with rigid recipe

Why it fails: Dataprep recipes are generally rigid regarding schema.

Cloud Functions with manual schema mapping

Why it fails: Manual mapping requires code changes when the schema evolves.

Dataflow with fixed schema

Why it fails: Fixed schemas break when the source file changes.

Binary

Why it fails: Binary is not a Dataflow windowing type; the supported types are fixed, sliding, session, and global windows. It is tempting because binary appears in other streaming contexts such as binary watermarks or binary encoding, but windowing is defined by time or session boundaries, not numeric bases.

Random

Why it fails: Random is not a Dataflow windowing type; windows are fixed, sliding, session, or global, all defined by time or data-driven boundaries. It is tempting because random partitioning exists in sharding and load-balancing, but that governs key distribution, not how elements are grouped into finite windows.

Nested subqueries.

Why it fails: Nested subqueries inline a derived table at each point of use, so a table needed repeatedly must be duplicated, harming readability and efficiency. They are tempting because they are valid BigQuery syntax, but the stem asks for a temporary table defined once and reused, which WITH provides.

Variable declaration.

Why it fails: Variable declaration creates scalar or array variables, not a named relation, so it cannot hold a temporary table for later reference in the same query. It is tempting because it does define reusable values, but the stem requires a temporary table, which WITH clauses provide.

CREATE TABLE AS SELECT.

Why it fails: CREATE TABLE AS SELECT persists a real table in a dataset, incurring storage and write costs, rather than defining a temporary table scoped to one query. It is tempting because it does create a table, but it is for durable materialisation, not the same-query reuse the stem requires.

Automated email alerts

Why it fails: Email alerts are an operational monitoring concern, not a development step for building a pipeline. It is tempting because alerting is genuinely part of running pipelines in production, and it would be correct when configuring observability after the pipeline is deployed.

Manual data entry

Why it fails: Manual data entry is not a Dataflow development step; pipelines ingest programmatically from sources such as Pub/Sub or GCS. It is tempting because entering data feels like part of preparing a pipeline, and it would apply when seeding a small reference dataset by hand outside Dataflow.

Keep all historical logs forever

Why it fails: Retaining every historical log indefinitely accrues Cloud Logging storage charges, raising rather than lowering spend. It is tempting because long retention aids audit and debugging, and is the right choice when compliance mandates it, but cost reduction requires log exclusion filters or a shorter retention period.

Use the largest environment size

Why it fails: Larger environment sizes provision more workers and database capacity, directly increasing hourly Composer charges. It is tempting because extra workers speed up heavy parallel DAG execution, which is correct when throughput is the constraint, but cost reduction comes from right-sizing to the smallest environment that meets workload demand.

Increase scheduler frequency

Why it fails: Raising scheduler frequency triggers more parsing and scheduling cycles, consuming extra CPU and Airflow database operations that raise cost. It is tempting because frequent scheduling shortens task latency, which is correct for time-sensitive pipelines, but cost reduction favours a longer scheduler interval and fewer DAG parses.

A federated query from BigQuery that uses an external connection to read the Oracle tables directly.

Why it fails: Federated queries can read some external sources without loading, but they require network reachability from BigQuery to the source, which is blocked here. They also are not designed for moving terabytes into native BigQuery storage. The scenario asks for a nightly transfer into BigQuery, not ad hoc querying, so this approach fails both the connectivity and the ingestion goal.

A Pub/Sub subscription that receives change events streamed from the Oracle database by a third-party tool.

Why it fails: Streaming change events through Pub/Sub could support continuous replication, but it requires additional tooling to capture and publish Oracle changes, increasing operational overhead. The team explicitly wants minimal overhead and a nightly batch transfer. Pub/Sub alone also does not load data into BigQuery; a separate subscriber would be needed, making this option more complex than the managed connector.

A Cloud Storage transfer that reads database export files placed in a bucket by an on-premises script.

Why it fails: A Cloud Storage transfer moves objects between buckets or from external sources into Cloud Storage, but it does not parse Oracle tables or load them into BigQuery. The team would still need to export, stage, and load the data separately, adding operational steps. This connector does not provide the managed database-to-BigQuery path the scenario requires, and it depends on the on-premises script remaining reliable.