Free PDE practice questions

A retail analytics team ingests clickstream events into Pub/Sub and processes them with a Dataflow streaming pipeline that aggregates page views per user session, where a session ends after 20 minutes of inactivity. Sessions can run from seconds to several hours, and late events arrive up to 10 minutes behind the watermark. Which windowing strategy in Apache Beam should the team apply to compute one aggregate per logical session per user?

• AApply session windows with a gap duration of 20 minutes keyed by user, with allowed lateness of 10 minutes.check_circle Correct
• BApply fixed windows of 20 minutes keyed by user, with allowed lateness of 10 minutes and accumulating panes.
• CApply sliding windows of 20 minutes with a 1-minute period, with allowed lateness of 10 minutes.
• DApply global windows with an early trigger every 20 minutes, with allowed lateness of 10 minutes.

Why A is correct: Session windows are data-driven and group events for the same key whenever the gap between successive event timestamps is below the configured duration, producing exactly one window per logical session of activity.

Why B is wrong: Fixed windows split a long browsing session into arbitrary 20-minute buckets aligned to wall-clock time, so a single user session straddling a boundary is reported as two aggregates rather than one logical session.

Why C is wrong: Sliding windows emit overlapping aggregates and produce many panes per user, which is appropriate for moving averages but not for a one-aggregate-per-session contract because each event belongs to multiple windows.

Why D is wrong: The global window groups all events into a single window per key and relies on triggers for emission, which cannot express the inactivity-gap semantics of a session and would mix events from unrelated sessions.

A fraud-detection pipeline in Dataflow joins a high-volume stream of card transactions from Pub/Sub against a moderately sized lookup of merchant risk scores that is refreshed in BigQuery every 15 minutes. The lookup fits comfortably in worker memory, and each transaction must be enriched with the latest available risk score for its merchant. Which Apache Beam construct should the team use to perform this enrichment efficiently?

• ARead the BigQuery lookup as a bounded PCollection and apply CoGroupByKey against the streaming transactions on merchant id.
• BModel the BigQuery lookup as a periodically refreshed side input and reference it from a ParDo that enriches each transaction.check_circle Correct
• CApply CombinePerKey on the merged stream of transactions and lookup rows to retain the most recent risk score per merchant.
• DCall the BigQuery Storage Read API from inside a ParDo for every incoming transaction to fetch the latest risk score.

Why A is wrong: CoGroupByKey requires both inputs to be keyed PCollections in compatible windows and would shuffle the entire transaction stream by merchant id, which is far heavier than a broadcast lookup and does not match the periodic-refresh semantics of the risk table.

Why B is correct: A periodically refreshed side input materialises the lookup on every worker, refreshes it on the configured cadence, and lets a ParDo read it as an in-memory map, which is the canonical pattern for broadcast joins of streams with slowly changing dimensions.

Why C is wrong: CombinePerKey aggregates values per key but does not produce per-transaction enriched output; it would collapse transactions and risk rows into a single value per merchant and lose every individual transaction record.

Why D is wrong: Per-element remote calls add latency and quota pressure proportional to throughput, and they ignore the fact that the lookup is small and changes only every 15 minutes, making this both slower and more expensive than a side input.

A logistics team runs a Dataflow streaming job that computes per-minute delivery counts in tumbling windows. Roughly 2 percent of GPS events arrive between 30 seconds and 8 minutes after their event time because of intermittent driver connectivity, and downstream dashboards must reflect corrected counts when late data lands. The job currently uses the default trigger with no allowed lateness, and late events are being dropped. Which configuration best preserves accuracy while keeping per-window state bounded?

• ASwitch to processing-time windows of one minute so events are bucketed on arrival and lateness becomes irrelevant.
• BKeep the default trigger and set allowed lateness to 10 minutes, accepting that late panes will overwrite earlier results in the sink.
• CConfigure an event-time trigger at the end of the window plus a late-firing trigger after each late element, with allowed lateness of 10 minutes and accumulating panes.check_circle Correct
• DDisable the watermark by setting allowed lateness to an unlimited duration and rely on a global trigger to fire once at job drain.

Why A is wrong: Processing-time windows mis-attribute late events to the window in which they happen to arrive, which corrupts per-minute delivery counts and is the opposite of what the dashboard requires for event-time correctness.

Why B is wrong: The default trigger fires once at the end of the window and again per late element, but without an explicit late trigger the late-data semantics are implementation-defined and discarding state behaviour cannot be coordinated with accumulation mode for the dashboard.

Why C is correct: An event-time trigger emits an on-time result when the watermark passes the window end, while a late trigger emits an updated pane for each late element within the 10-minute allowance, and accumulating panes ensure each emission represents the corrected cumulative count for the window.

Why D is wrong: Unlimited allowed lateness causes per-window state to grow without bound, and waiting for drain defeats the purpose of streaming dashboards by delaying every result until the job stops.

A bank runs a BigQuery dataset of transaction records that must be encrypted with keys the bank controls, rotated on a fixed schedule, and destroyable on request, while a separate security team alone administers those keys and the analytics engineers who query the data must never be able to disable, rotate, or destroy a key. Which TWO controls together enforce both the customer-controlled encryption requirement and the separation of key administration from data access? (Select TWO.)

• ACreate the BigQuery dataset with a customer-managed encryption key from a Cloud KMS key ring, and configure a key rotation period on that key so new key versions are generated automatically on the required schedule.check_circle Correct
• BRely on Google-managed default encryption for the dataset and document the rotation behaviour in the data protection policy, since all BigQuery data is encrypted at rest regardless of key ownership.
• CGrant the security team the Cloud KMS Admin role on the key ring and grant the analytics engineers only the Cloud KMS CryptoKey Encrypter/Decrypter role, so engineers can use the key for queries but cannot manage its lifecycle.check_circle Correct
• DGive the analytics engineers the Cloud KMS Admin role on the key ring so their queries can transparently use the key without a separate grant, and audit their usage with Cloud Audit Logs.
• EApply customer-supplied encryption keys to the BigQuery dataset and store the raw key material in a Secret Manager secret that the analytics engineers read at query time.

Why A is correct: Correct because a customer-managed encryption key places key lifecycle, scheduled rotation, and destruction under the bank's control rather than relying on Google-managed defaults, which directly satisfies the requirement that the bank own and be able to destroy the encryption key.

Why B is wrong: Tempting because BigQuery does encrypt all data at rest by default, but Google-managed keys cannot be rotated on the bank's schedule or destroyed on demand by the customer, so this fails the customer-control and per-request destruction requirements entirely.

Why C is correct: Correct because splitting the administrative role from the encrypt/decrypt role enforces separation of duties: the security team manages rotation and destruction while engineers hold only the permission needed to read and write data, never to disable or destroy a key.

Why D is wrong: Tempting because granting a broad role removes a permissions hurdle for queries, but Cloud KMS Admin allows disabling, rotating, and destroying keys, which violates the explicit requirement that engineers must never be able to manage key lifecycle.

Why E is wrong: Tempting because customer-supplied keys also give the customer control of key material, but BigQuery datasets do not accept customer-supplied encryption keys, and exposing raw key material to engineers would defeat the separation of key administration the scenario demands.

A data platform team grants an analyst the BigQuery Data Viewer role at the project level so the analyst can query several datasets. The team now wants the analyst to read only tables whose names start with the prefix sales_ in one specific dataset, without creating a new custom role and without changing the analyst's existing project-level grants. Which approach achieves this most precisely?

• AAdd a deny policy at the project level that denies BigQuery read permissions on tables whose name does not start with sales_, attached to the analyst's principal.
• BRemove the project-level BigQuery Data Viewer grant and instead grant BigQuery Data Viewer on every individual table whose name starts with sales_ in the target dataset.
• CAdd an IAM condition to the analyst's BigQuery Data Viewer binding that uses resource.name.startsWith with the table path prefix for sales_ tables in the target dataset.check_circle Correct
• DCreate an authorised view in a separate dataset that selects from the sales_ tables, and grant the analyst BigQuery Data Viewer on that dataset only.

Why A is wrong: Deny policies can restrict permissions but cannot match BigQuery table names with a startsWith expression on a resource attribute, so the negation cannot be authored cleanly and would block far more than the intended tables.

Why B is wrong: Per-table grants would work but the requirement is to leave existing project-level grants in place, and managing one binding per table does not scale as new sales_ tables are created over time.

Why C is correct: IAM conditions on a role binding evaluate CEL expressions against request and resource attributes, and resource.name.startsWith on the BigQuery table path is the documented pattern for restricting access to tables matching a name prefix.

Why D is wrong: Authorised views are useful for column or row filtering but they require maintaining one view per table or a union view, and they do not transparently expose the underlying sales_ tables to ad hoc queries by name.

An organisation administrator wants to guarantee that no Cloud Storage bucket in any project beneath a particular folder can ever be created or modified to allow public access, regardless of which project owner attempts the change. The control must be enforced centrally and must not rely on auditing after the fact. Which mechanism best meets this requirement?

• AA custom IAM role at the folder that omits storage.buckets.setIamPolicy, assigned to every principal who might otherwise grant public access.
• BA Cloud Audit Logs sink to BigQuery that alerts when a bucket binding includes allUsers, paired with a Cloud Function that revokes the binding.
• CA VPC Service Controls perimeter around the folder's projects that blocks all unauthenticated requests to the Cloud Storage API.
• DAn organisation policy at the folder that enforces the constraint preventing public access to Cloud Storage buckets, inherited by all projects under the folder.check_circle Correct

Why A is wrong: Removing the setIamPolicy permission from one custom role does not prevent other principals with project owner or storage admin roles from making buckets public, so the control is not centrally enforced.

Why B is wrong: Detection and remediation after the fact leaves a window where data is publicly accessible, and the requirement explicitly rules out an audit-driven approach in favour of central preventive enforcement.

Why C is wrong: VPC Service Controls restrict API access across network boundaries to mitigate data exfiltration, but they are not the mechanism for preventing a bucket's IAM policy or setting from being changed to allow public access.

Why D is correct: Organisation policy constraints are evaluated at admission time across the resource hierarchy, and the constraint that disables public access for Cloud Storage prevents any IAM binding or bucket setting that would grant allUsers or allAuthenticatedUsers, inherited automatically by descendant projects.

An architect is comparing BigQuery and Bigtable for a workload that records device telemetry from two million industrial sensors. Each sensor emits a reading every second, and downstream applications need to retrieve the most recent 24 hours of readings for any single sensor within tens of milliseconds, while a separate weekly analytical job scans aggregates across the full fleet. The architect wants to understand the fundamental role boundary between the two services. Which statement most accurately describes how BigQuery and Bigtable differ for this workload?

• ABigtable is a wide-column NoSQL store with sorted row keys that gives single-digit millisecond reads for a known key, while BigQuery is a columnar analytical warehouse designed for high-throughput scans across very large tables; the per-sensor lookup belongs in Bigtable and the weekly aggregate belongs in BigQuery.check_circle Correct
• BBigQuery is a wide-column NoSQL store optimised for single-row lookups by key, while Bigtable is a columnar analytical warehouse tuned for ad hoc SQL scans, so the per-sensor lookups should target BigQuery and the weekly aggregate should target Bigtable.
• CBigQuery and Bigtable both target operational workloads, but BigQuery is preferred whenever rows exceed one kilobyte and Bigtable is preferred whenever rows are smaller, regardless of access pattern.
• DBigtable and BigQuery are interchangeable for telemetry because both are columnar; the team should pick the cheaper one for the region and accept identical latency characteristics from each service.

Why A is correct: Bigtable is sorted by row key and serves point and small range reads in low single-digit milliseconds, which suits the per-sensor 24-hour lookup, while BigQuery's columnar storage and slot-based execution are designed to scan and aggregate across very large tables on schedule, which suits the weekly cross-fleet job.

Why B is wrong: This reverses the actual roles. BigQuery is the columnar analytical warehouse and Bigtable is the wide-column key-ordered NoSQL store, so the description swaps the two services. A candidate who only half-remembers the column orientation of BigQuery can fall into this trap.

Why C is wrong: BigQuery is an analytical warehouse, not an operational store, and the selection between Bigtable and BigQuery is driven by access pattern rather than row size. The size-based rule sounds concrete but is fabricated and will mislead a candidate who has not internalised the role boundary.

Why D is wrong: Although both services use a column-oriented physical layout, their access patterns and latency profiles are very different. Bigtable serves low-latency keyed reads while BigQuery serves throughput-oriented scans, so they are not interchangeable for a real-time per-sensor lookup.

A data platform team has petabytes of Parquet files in Cloud Storage that are produced by external partners and continue to be written to by Spark jobs outside Google Cloud. The team wants analysts to query these files using BigQuery SQL with fine-grained row and column security, without copying the data into BigQuery managed storage. Which statement most accurately describes the role of BigLake in this scenario?

• ABigLake ingests the Parquet files into BigQuery managed storage on a schedule, after which BigQuery treats them as native tables and applies row and column policies during query execution.
• BBigLake exposes files in Cloud Storage as BigQuery tables through a connection that delegates access to a service account, enabling BigQuery to enforce row-level and column-level security on the open-format data while leaving the underlying files in place.check_circle Correct
• CBigLake is a separate analytical warehouse that replaces BigQuery for open-format files, so the team must migrate from BigQuery to BigLake and rewrite their SQL against the new engine.
• DBigLake is a feature of Bigtable that exposes wide-column data to external Parquet readers, and analysts should load the partner files into Bigtable before querying them.

Why A is wrong: This describes scheduled ingestion into native BigQuery storage, which is exactly what the team wants to avoid. BigLake's distinguishing feature is leaving data in place in Cloud Storage rather than copying it into managed BigQuery storage, so this option contradicts the requirement.

Why B is correct: BigLake tables wrap files in object storage with a BigQuery table definition backed by a connection resource; queries run through the BigQuery engine and inherit its row-level and column-level security model, while the underlying Parquet files remain in Cloud Storage.

Why C is wrong: BigLake is not a separate warehouse and does not replace BigQuery. It extends BigQuery with table definitions over object storage, so SQL continues to run through the BigQuery engine. This distractor appeals to candidates who have only seen the BigLake name without reading the architecture.

Why D is wrong: BigLake is associated with BigQuery and object storage, not Bigtable, and loading Parquet partner files into a wide-column NoSQL store would not match the analytical query requirement. The mention of Parquet may tempt candidates who have not separated the analytical and operational stacks.

An engineering lead is choosing between Bigtable and BigQuery for a workload that ingests roughly 400,000 events per second, where every event must be written within tens of milliseconds and the schema is best modelled as a sparse wide row of optional attributes per device. Aggregations across the whole dataset are run only once per day in a separate batch. Which characteristic of Bigtable most directly explains why it suits the ingestion side of this workload better than BigQuery?

• ABigtable executes queries on a serverless slot pool that scales automatically with the size of the scan, so high-throughput writes complete faster than they would in BigQuery's storage engine.
• BBigtable enforces a strict relational schema and primary-key indexes on every column, which guarantees lower write latency than BigQuery's schemaless columnar layout.
• CBigtable partitions tables into tablets ordered by row key and serves writes through node-local memtables backed by Colossus, which sustains very high steady-state write throughput at low latency without the streaming buffer behaviour of BigQuery.check_circle Correct
• DBigtable stores data in a columnar format that is compressed for analytical scans, which incidentally makes per-row writes faster than the row-oriented storage used by BigQuery.

Why A is wrong: Slot-based serverless execution is a BigQuery characteristic, not a Bigtable one, and slots scale query throughput rather than write latency. A candidate who blurs the two services' execution models can be drawn to this answer.

Why B is wrong: Bigtable does not enforce a relational schema or per-column indexes; it has a single row key and flexible column families. BigQuery is also not schemaless. The option inverts the schema models of both services and is wrong on two counts.

Why C is correct: Bigtable shards a table into tablets by row key, and writes hit an in-memory memtable on the owning node before being flushed to immutable files on Colossus. This architecture gives consistent low-latency, high-throughput writes that BigQuery's streaming ingestion path is not designed to match at this scale.

Why D is wrong: BigQuery uses columnar storage tuned for analytical scans, while Bigtable stores cells keyed by row key, column family, column qualifier, and timestamp. This option assigns BigQuery's columnar analytical layout to Bigtable and reverses the row-versus-column framing.

A logistics company runs about 30 independent Spark batch jobs each night, each lasting 15 to 80 minutes and reading and writing exclusively to Cloud Storage. They currently keep one large persistent Dataproc cluster running all day even though daytime utilisation sits below 5 percent, and the finance team wants to cut Dataproc spend without lengthening the nightly window. The jobs share no in-cluster state and most of their capacity need is short-lived burst during each run. Which TWO changes, taken together, best minimise cost while preserving the nightly completion time? (Select TWO.)

• AKeep the single persistent cluster running 24 hours a day but switch every node to a smaller machine type, so the cluster costs less per hour while still being available for the nightly jobs and any daytime ad hoc work.
• BSubmit each job to its own ephemeral, job-scoped Dataproc cluster that is created at submission and deleted automatically once the job finishes, so compute is paid for only while a job actually runs.check_circle Correct
• CMove all 30 jobs onto a single persistent cluster sized for the busiest job and rely on YARN fair scheduling to interleave them, so one always-on cluster serves every job and avoids repeated cluster creation latency.
• DProvision each cluster entirely from preemptible secondary workers with no primary workers, so the whole fleet runs at the lowest possible price for the duration of the nightly window.
• ECompose each job cluster from a small base of standard primary workers plus preemptible (Spot) secondary workers for the burst capacity, so the bulk of the short-lived demand is served at the discounted Spot price.check_circle Correct

Why A is wrong: Shrinking the machine type lowers the hourly rate, but the cluster still bills for roughly 19 idle hours every day and smaller nodes risk slowing the nightly jobs, so it neither removes the dominant idle cost nor protects the completion window.

Why B is correct: Because the jobs share no in-cluster state and use only Cloud Storage, a job-scoped cluster that is created per job and torn down on completion eliminates the idle daytime cost entirely while giving each job dedicated capacity for its run, which preserves the nightly window.

Why C is wrong: A single always-on cluster sized for the peak keeps paying for idle capacity all day and forces independent jobs to contend for the same slots, which can extend rather than protect the nightly completion time, so it works against the cost goal.

Why D is wrong: Preemptible secondary workers are cheap, but a Dataproc cluster must have primary workers to run the HDFS and YARN management roles, and an all-preemptible fleet risks mass reclamation mid-run that triggers stage retries, so it is neither valid nor deadline-safe.

Why E is correct: Keeping a small stable base of primary workers while supplying the burst capacity from discounted preemptible secondary workers serves the short-lived peak at the lowest price without risking the whole job to reclamation, which cuts cost while still meeting the run-time need.

An analytics platform on BigQuery Editions runs a predictable nightly ELT load that needs a guaranteed amount of capacity every night, plus daytime interactive analytics whose demand spikes unpredictably and occasionally far exceeds the steady baseline. The platform owner wants the lowest possible unit price for the predictable portion of capacity, while still letting interactive queries burst above the baseline during busy periods and paying nothing extra for that headroom when it is not used. Which TWO reservation configuration choices, taken together, best minimise cost while meeting both the guaranteed-capacity and burst requirements? (Select TWO.)

• APurchase a one-year or three-year slot commitment to cover the predictable baseline capacity, so the steady portion of demand is served at the lowest committed unit price rather than at the higher pay-as-you-go rate.check_circle Correct
• BSet the reservation baseline slots equal to the highest interactive peak ever observed, so the reservation always has enough capacity on hand and never needs to scale up during busy periods.
• CEnable autoscaling on the reservation with a maximum above the baseline, so interactive queries can burst into autoscaled slots during spikes and the team is billed for those extra slots only while they are actually allocated.check_circle Correct
• DMove the interactive workload off reservations onto on-demand (per-byte) pricing while keeping the ELT on a reservation, so the bursty queries are billed purely by bytes scanned and never consume reservation slots.
• EDisable autoscaling and instead manually raise the baseline each morning and lower it each night through a scheduled script, so capacity tracks the daily pattern without paying for an autoscaling maximum.

Why A is correct: A longer-term slot commitment locks in the lowest unit price for capacity the team knows it will use every night, so funding the predictable baseline through a one-year or three-year commitment minimises the cost of the steady portion of the workload.

Why B is wrong: Sizing the baseline to the rare peak guarantees capacity but means paying continuously for slots that sit idle most of the time, which is the opposite of minimising cost and wastes the headroom the team only needs occasionally.

Why C is correct: Autoscaling adds slots above the baseline on demand and bills only for the autoscaled slots while they are in use, so it provides the burst headroom for unpredictable interactive spikes without paying for that capacity when demand is low.

Why D is wrong: On-demand per-byte pricing is tempting for spiky usage, but it removes the slot-based cost predictability the team wants for interactive analytics and can be far more expensive for heavy scans, so it does not minimise cost for an unpredictable high-volume interactive workload.

Why E is wrong: Scripted baseline changes add brittle operational overhead and still cannot react to the unpredictable intraday spikes the scenario describes, so the reservation either underprovisions during a surge or overprovisions between surges, missing the burst requirement that autoscaling handles natively.

A data engineering team runs a nightly transformation that must always execute on a fixed daily schedule, retry the same transformation step automatically when a transient BigQuery error occurs, and start a downstream reporting step only after the transformation step succeeds. They want this defined once as code so any environment can reproduce the identical run order and recovery behaviour. Which TWO Cloud Composer features, configured together, make this workload repeatable on its own schedule with automatic recovery and ordered dependencies? (Select TWO.)

• ATrigger each run from a Cloud Scheduler job that calls the Airflow REST API, so the schedule lives outside the DAG and the DAG itself stays free of any timing configuration that could drift between environments.
• BPlace the transformation and reporting steps in two separate DAGs and rely on each DAG's own start_date so that the second DAG happens to run after the first because its schedule is set a few minutes later.
• CSet the DAG's schedule_interval to a daily value so the Airflow scheduler creates one run per logical date, giving the workload a fixed recurring cadence that is defined directly in the DAG code and reproduced in every environment.check_circle Correct
• DAdd an Airflow SLA on the transformation task set to the nightly window so that, when the step is slow, Airflow re-runs the step from the start and reorders the downstream tasks to recover the schedule.
• ESet retries and retry_delay on the transformation task and declare the reporting task downstream with set_downstream or the bitshift operator, so the failed step retries automatically and reporting starts only after the transformation succeeds.check_circle Correct

Why A is wrong: Calling the Airflow REST API from Cloud Scheduler can start a run, but moving the schedule outside the DAG splits the definition across two systems and is not the in-DAG scheduling mechanism Airflow provides, so it does not give a single reproducible code artefact for the timing.

Why B is wrong: Staggering two DAGs by a few minutes feels like it orders them, but a clock offset is not a real dependency and the reporting DAG would still run even if the transformation failed, which breaks the required run-only-after-success ordering.

Why C is correct: A daily schedule_interval is the in-DAG mechanism that makes the Airflow scheduler create exactly one run per logical date, which establishes the fixed recurring cadence the team needs and lives in the same code artefact as the rest of the DAG.

Why D is wrong: An SLA looks like a recovery control, but it only raises a miss notification when a task runs long; it does not retry a failed task or change task ordering, so it cannot deliver the automatic recovery and dependency behaviour described.

Why E is correct: The retries and retry_delay parameters make Airflow re-execute the failed transformation task automatically with backoff, and declaring the reporting task downstream enforces that it starts only after the transformation succeeds, together supplying the recovery and ordered-dependency behaviour.

A retail analytics team runs a Looker dashboard on a 6 TB BigQuery orders table in the EU region. Each tile reissues the same aggregation over the last 90 days, and analysts must never see the raw card_number column although they group revenue by region and product. The team wants sub-second tile rendering and wants the masking enforced in the same physical table at query time based on the caller's group. Which TWO preparation steps, applied together, will both accelerate the dashboard and secure the card_number column? (Select TWO.)

• ACreate a BI Engine reservation in the EU region so the repeated aggregation queries are served from in-memory cache.check_circle Correct
• BExport the orders table to a Looker Studio extract refreshed every twelve hours so tiles read the cached extract instead of BigQuery.
• CApply a BigQuery column-level dynamic data masking policy on card_number that returns masked values to the analyst group while privileged callers see the raw value.check_circle Correct
• DGrant the analyst group the BigQuery Data Viewer role on the dataset and rely on that role to hide the card_number column.
• ECluster the orders table by card_number so the masked column is pruned and the aggregation queries scan less data.

Why A is correct: A regional BI Engine reservation co-located with the dataset caches hot columnar data in memory and accelerates the repeated aggregate scans, which is the correct path to sub-second tiles.

Why B is wrong: A twelve-hour extract is tempting because it caches data, but it does not enforce column-level masking and is not the in-engine acceleration the requirement asks for, so it fails the security goal.

Why C is correct: Dynamic data masking with a data policy on a policy-tagged column enforces masking at query time on one physical table based on the caller's group, exactly meeting the security requirement.

Why D is wrong: Data Viewer is tempting as an access control, but a dataset-level role grants full row and column visibility and cannot mask a single column, so card_number would remain exposed.

Why E is wrong: Clustering by card_number sounds like a performance lever, but clustering on a sensitive identifier does not align with the 90-day aggregation filters and provides no masking, so it neither accelerates the right queries nor secures the column.

An analytics team has enabled a BigQuery BI Engine reservation in the same region as a Looker dashboard that queries a 40 GB fact table with several aggregations per panel. The team wants to understand exactly which queries the reservation will accelerate so they can size it correctly. Which statement best describes how BI Engine acceleration is applied to incoming queries?

• ABI Engine accelerates only queries issued through Looker Studio and ignores queries submitted by other clients such as the BigQuery console or the bq command-line tool.
• BBI Engine accelerates all queries that touch any table referenced by the dashboard regardless of region, because reservations are global resources shared across BigQuery locations.
• CBI Engine accelerates eligible SQL queries against tables in the reserved project and region by serving them from an in-memory cache, falling back to standard BigQuery slots for unsupported features.check_circle Correct
• DBI Engine accelerates queries by precomputing aggregations into a materialised view that is automatically registered in the reservation and refreshed on every base table change.

Why A is wrong: It is tempting because BI Engine was originally promoted as a Looker Studio accelerator. In practice acceleration is client-agnostic and applies to any SQL query that fits within the reservation's supported feature set, including queries from the console, bq, drivers, and Looker.

Why B is wrong: Region matching trips up many candidates. BI Engine reservations are regional, and a reservation only accelerates queries that run in the same location as the reserved data. Cross-region queries cannot be served from the cache.

Why C is correct: This is correct. BI Engine maintains an in-memory representation of frequently accessed data and rewrites supported query patterns to read from that cache. Queries or query fragments that use unsupported SQL features run on standard slots, so partial acceleration is possible.

Why D is wrong: This blurs BI Engine with materialised views. BI Engine is an in-memory caching layer, not a precomputation engine, and it does not create or own materialised views on the user's behalf.

A data engineer has created a BigQuery materialised view that aggregates daily revenue from a partitioned base table updated continuously by a streaming pipeline. The team wants to understand how the materialised view behaves when a dashboard queries it shortly after new rows are appended. Which statement best describes the freshness and query behaviour of the materialised view?

• AQueries against the materialised view always return data only as fresh as the last scheduled full refresh, and any rows arriving between refreshes are invisible until the next scheduled run.
• BThe materialised view becomes invalid as soon as any row is inserted into the base table, and queries against it return an error until the engineer triggers a manual refresh.
• CMaterialised views can be queried directly but the optimiser will not use them to rewrite queries against the base table unless the user explicitly hints at the view in the SQL.
• DBigQuery transparently combines the materialised view's stored aggregates with any base table data changed since the last refresh, so queries reflect the latest committed rows automatically.check_circle Correct

Why A is wrong: This describes a manually refreshed cache, not a BigQuery materialised view. The optimiser uses the view's precomputed deltas plus the base table changes since the last refresh, so recent rows are not invisible.

Why B is wrong: Invalidation on every write would make streaming use untenable. BigQuery does not invalidate the view on insert; it tracks changes and either serves stale-and-delta results or, for unsupported changes, refuses the rewrite while the view remains queryable.

Why C is wrong: Manual hints feel familiar from other engines. BigQuery performs automatic query rewrite when a query against the base table matches a registered materialised view, so explicit hints are not required for the optimiser to pick the view.

Why D is correct: This is correct. BigQuery uses smart tuning so a query against the materialised view, or one that can be rewritten to use it, reads precomputed aggregates plus a delta scan of the base table. The dashboard sees current data without the user managing refreshes.