50 gcp data engineer Practice Questions: Question Bank 2025

A retail company wants to ingest clickstream events from a mobile app with minimal operational overhead and near-real-time processing for dashboards. Event volume can spike unpredictably. Which architecture is the best fit on Google Cloud?

A data engineering team needs a data warehouse for interactive SQL analytics over petabytes of data. They want separation of storage and compute with minimal cluster management. Which service should they choose?

Your organization requires that all objects written to a Cloud Storage bucket be encrypted with customer-managed encryption keys (CMEK) and that access to decrypt be centrally controlled. What should you do?

A team needs a simple way to transform and load data from Cloud Storage into BigQuery on a schedule with minimal custom code. The transformations are mostly SQL-based. What is the recommended approach?

You are building a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. During deployment, you see occasional duplicate rows in BigQuery during worker restarts. What is the most appropriate mitigation?

A company stores raw logs in Cloud Storage and wants to query them using standard SQL without loading them into BigQuery first. They also want fine-grained access controls on specific columns when results are written to BigQuery tables later. What should they do?

A finance dataset in BigQuery must enforce that analysts can only see rows belonging to their department. The departments are stored in a column named dept_id. What is the best way to implement this requirement with centralized governance?

A team runs multiple nightly pipelines that depend on each other (extract, validate, transform, publish). They want managed orchestration, retries, and clear dependency visualization while using Google Cloud services. What should they use?

You must design a pipeline to process sensitive healthcare records. Requirements: (1) Data must not traverse the public internet, (2) services should use private connectivity, and (3) the pipeline should support both batch file ingestion and streaming updates. Which design best meets these requirements?

A global application needs to store user profile data with strong consistency, high availability, and horizontal scalability. The data model is relational and requires transactions across multiple rows and tables. The team also wants minimal operational overhead. Which storage option is most appropriate?

You are building a new analytics platform on Google Cloud. A single data pipeline must support both daily batch aggregation and near-real-time dashboards from the same event stream. You want to minimize duplicated code and operational overhead. What is the recommended approach?

A data engineering team frequently re-runs the same BigQuery queries during development and notices inconsistent results when underlying source tables change mid-day. They want reproducible query results for debugging without copying entire tables. What should they do?

A data pipeline is writing files to Cloud Storage and then loading them into BigQuery. The pipeline often writes duplicate files when it retries after transient errors. You want to ensure that retries do not create duplicates in the final dataset. What is the best approach?

Your organization has IoT telemetry arriving in Pub/Sub. You need to store raw events for replay, but analysts also need SQL access to recent data with minimal operational work. Data volume is high, and schema may evolve. What design best meets these needs?

A Dataflow streaming job reading from Pub/Sub is falling behind during traffic spikes. Workers show high CPU and frequent garbage collection. The pipeline includes a heavy per-record UDF that calls an external HTTP service. What is the most effective fix?

You need to allow a partner to query a subset of your BigQuery dataset without copying data. They should only see specific columns and rows, and you want to centrally manage the access rules. What should you implement?

A team wants to standardize recurring data processing (Dataflow jobs, BigQuery queries, and validation steps) into a reusable workflow with dependency management, retries, and alerting. They also want to deploy workflows via CI/CD. Which solution best fits?

Your Dataflow batch pipeline writes Parquet files to Cloud Storage for downstream Spark jobs. Downstream jobs are slow because they scan too many files and columns. You want to optimize storage layout and access patterns without changing the compute engine. What should you do?

You ingest clickstream events into BigQuery. Each event includes a nested JSON-like payload with optional fields that change frequently. Analysts need stable schemas for core metrics, but you must retain all raw attributes for occasional deep dives. What is the best BigQuery modeling approach?

A regulated company requires that data processing jobs run with the least privilege and that access to sensitive datasets is audited and centrally controlled. Multiple teams run Dataflow pipelines that write to BigQuery and read from Cloud Storage. What is the best practice for identity and access management?

A retail company stores daily CSV exports in Cloud Storage. Analysts want to query the data in BigQuery without loading it, and they want queries to automatically pick up new files as they arrive. What should you do?

A team uses BigQuery with streaming inserts for real-time metrics. They notice occasional duplicate rows due to retries from the producer. They need to make the stream idempotent with minimal changes. What should they do?

Your organization requires that all analytic datasets in BigQuery are encrypted with customer-managed encryption keys (CMEK) and that key usage is auditable. What is the recommended approach?

A media company needs a data processing architecture that can handle sudden spikes of millions of events per minute, process them in near real time, and write aggregated results to BigQuery. They want minimal operational overhead and automatic scaling. Which design is best?

A Dataflow pipeline reading from Pub/Sub and writing to BigQuery occasionally fails with errors indicating that the pipeline cannot create BigQuery load/temporary jobs. The Dataflow worker service account has been granted BigQuery Data Editor on the target dataset. What is the most likely missing permission?

A financial services company runs nightly transformations in BigQuery. Some transformations are long-running and occasionally fail mid-way, leaving partially updated tables. They need atomic updates and easy rollback while keeping storage costs reasonable. What should they do?

You need to store user profile documents that are frequently updated, must support strongly consistent point reads by key, and require automatic multi-region replication for high availability. Which storage option best fits?

A company is building a data lake in Cloud Storage. They must ensure that only the data platform service accounts can access a sensitive prefix (e.g., gs://lake/pii/), while other teams can access non-sensitive prefixes in the same bucket. They also need to avoid granting broad bucket-level permissions. What should they implement?

A Dataflow streaming job performs windowed aggregations. After a pipeline update, the team observes that aggregates are being recomputed from scratch and output spikes appear. They want to deploy updates without losing in-flight state and without double-counting. What should they do?

An analytics platform in BigQuery must support row-level access so that each customer can query only their own records while using the same shared tables. The solution must be centrally managed and work for both BI tools and ad hoc SQL. What should you implement?

Your organization uses BigQuery and wants to ensure that analysts can query datasets but cannot export query results to Cloud Storage or Google Drive. What should you do?

A data pipeline writes files to Cloud Storage. A downstream system must only process a file after it is fully written, and partial/temporary files must be ignored. What is the recommended approach?

You want to store user session state that requires very low latency reads/writes and predictable performance at high QPS. The data is key-value and can be lost without major impact. Which storage service is the best fit?

Your team needs to schedule a daily BigQuery SQL transformation and wants built-in scheduling, dependency management, and version-controlled SQL with an easy promotion workflow from dev to prod. What should you use?

A streaming Dataflow pipeline occasionally produces duplicate output records to BigQuery when workers are restarted. The source is Pub/Sub and messages can be redelivered. You need to minimize duplicates without sacrificing throughput. What should you do?

You need to store time-series IoT sensor data (millions of writes per second globally). Queries are primarily by device_id and recent time ranges. The schema may evolve with new attributes. Which storage design is most appropriate?

A BigQuery table is partitioned by ingestion time. Analysts frequently query the last 7 days but sometimes accidentally scan the entire table, increasing cost. You want guardrails that prevent queries without a partition filter. What should you do?

You have a Composer (Airflow) environment orchestrating Dataflow, BigQuery, and Dataproc jobs. Security requires least privilege and avoiding long-lived keys. What is the best approach for authentication between Composer tasks and Google Cloud services?

Your company must ensure that sensitive PII columns in BigQuery are only visible to a small set of users, while other users can still query non-PII columns in the same table. You also need to preserve row-level access for specific departments. What should you implement?

You run a batch pipeline that reads from Cloud Storage, performs heavy transformations, and writes partitioned output to Cloud Storage and BigQuery. The pipeline runs in Dataflow and sometimes fails mid-run. You need to ensure that re-running the pipeline does not create duplicate outputs and that partial results are not consumed. What is the best design?

You need to provide analysts with a curated BigQuery dataset that is always up to date from raw landing tables. You want to ensure consistent transformations, reuse SQL, and support incremental updates with minimal operational overhead. What should you use?

A data engineering team wants to manage IAM for hundreds of BigQuery datasets and tables with consistent permissions across projects. They also want changes to be auditable and code-reviewed. What is the recommended approach?

Your team needs to store large video files alongside JSON metadata. They need low-cost, highly durable storage for the videos, and fast, flexible queries on the metadata by fields such as customerId and uploadTime. What combination should you choose?

A Dataflow pipeline writes streaming events into a partitioned BigQuery table. You notice intermittent failures with errors indicating too many partition modifications. The upstream system can resend late events for many historical days. What is the best mitigation?

You are designing an IoT ingestion system that must handle very high write throughput, support time-series lookups by device ID and timestamp range, and provide predictable low-latency reads for operational dashboards. Which storage choice best fits?

You need to orchestrate a daily pipeline that includes: (1) running a Dataflow batch job, (2) executing multiple BigQuery SQL steps, and (3) waiting for an external REST API call to succeed before publishing a dataset. You want retries, dependencies, and a visual DAG. What should you use?

A data science team wants to share a BigQuery dataset with a partner but must restrict the partner to only a subset of rows (for example, only their own customer_id values). The partner should not be able to bypass the restriction by querying the base tables. What is the recommended design?

A batch ingestion pipeline loads daily files into BigQuery. Occasionally, upstream sends duplicate files (same data) causing double-counting in downstream reports. You need an idempotent ingestion approach with minimal complexity. What should you do?

A global data platform uses Pub/Sub to ingest events and Dataflow to process them. During a regional outage, you must continue ingesting and processing events with minimal data loss and minimal application changes. Which architecture best meets this requirement?

Your organization must enforce that all BigQuery datasets only contain data encrypted with customer-managed encryption keys (CMEK). The policy should prevent non-compliant dataset creation across multiple projects. What is the best solution?