Building Serverless Batch Pipelines with BigQuery SQL

PDE By islever Editorial Last updated

Planning the data pipelines. Planning for using a data warehouse.

Short answer

C Ingest your data into BigQuery from Cloud Storage, convert your PySpark commands into BigQuery SQL queries to transform the data, and then write the transformations to a new table.

Question

You want to rebuild your batch pipeline for structured data on Google Cloud. You are using PySpark to conduct data transformations at scale, but your pipelines
are taking over twelve hours to run. To expedite development and pipeline run time, you want to use a serverless tool and SOL syntax. You have already moved
your raw data into Cloud Storage. How should you build the pipeline on Google Cloud while meeting speed and processing requirements?
  1. Convert your PySpark commands into SparkSQL queries to transform the data, and then run your pipeline on Dataproc to write the data into BigQuery.
  2. Ingest your data into Cloud SQL, convert your PySpark commands into SparkSQL queries to transform the data, and then use federated quenes from BigQuery for machine learning.
  3. Ingest your data into BigQuery from Cloud Storage, convert your PySpark commands into BigQuery SQL queries to transform the data, and then write the transformations to a new table. Correct Answer
  4. Use Apache Beam Python SDK to build the transformation pipelines, and write the data into BigQuery.

Key Takeaways

The main trap is confusing Dataproc Serverless with BigQuery; while both support SQL, BigQuery is truly serverless and optimized for rapid SQL-based transformations of structured data stored in GCS.

This page explains how to build a serverless batch pipeline on Google Cloud using BigQuery SQL for fast structured data transformations, replacing slow PySpark jobs.

Common mistake: Users often choose Option A (Dataproc) because they focus on the 'Spark SQL' aspect and overlook the strict 'serverless' requirement and performance benefits of BigQuery's MPP engine for simple SQL transformations.

Expert Analysis

The question asks for a serverless solution using SQL syntax to expedite a batch pipeline that currently takes over twelve hours. The key constraints are "serverless tool" and "SQL syntax." While Dataproc is a managed Hadoop/Spark service, it is not fully serverless in the same way BigQuery is; it requires managing clusters (even with autoscaling) or paying for serverless Spark which still runs Spark SQL, not native BigQuery SQL. BigQuery is inherently serverless and uses its own SQL dialect. Therefore, moving data from Cloud Storage directly into BigQuery (ELT pattern) and performing transformations via BigQuery SQL is the most direct path to meeting both the speed and serverless requirements.

Community comments highlight this distinction. Some users initially suggested Dataproc Serverless (Option A), arguing that Spark SQL is sufficient. However, others correctly pointed out that BigQuery SQL is more performant for structured data transformations and strictly adheres to the "serverless" constraint without any cluster overhead. One comment noted that while Dataproc Serverless supports Spark SQL, the question emphasizes "SQL syntax" in the context of speeding up development and run time, where BigQuery's massively parallel processing (MPP) engine excels. Another comment clarified that Option D (Apache Beam) uses Python SDK by default, not SQL, unless specifically configured with Beam SQL, making it less ideal than native BigQuery SQL.

The operational consequence of choosing Dataproc (Option A) is that you are still managing compute resources indirectly and potentially facing longer setup times and higher latency compared to BigQuery's on-demand execution. Getting it wrong means missing the opportunity to leverage BigQuery's optimized storage and compute engine for rapid, scalable SQL-based transformations.

Exam Strategy

When a question specifies 'serverless' and 'SQL', prioritize BigQuery over Dataproc or Dataflow unless there is a specific need for complex logic or non-SQL APIs. Remember that BigQuery is best suited for ELT patterns where data is loaded first and then transformed using SQL. For complex stream processing or custom logic, consider Dataflow, but note that its primary API is Python/Java, not SQL.

Frequently Asked Questions

Why not use Dataproc Serverless for Spark SQL?

While Dataproc Serverless supports Spark SQL, BigQuery is inherently serverless and offers superior performance for structured data transformations using native SQL. It eliminates cluster management overhead entirely.

Can Apache Beam be used with SQL?

Yes, Apache Beam has a SQL interface, but it is not the primary SDK method. The question specifies using SQL syntax for simplicity and speed, making BigQuery SQL the more direct and optimized choice for structured data.

More PDE FAQ →

Community Votes

C
73%
A
27%

73% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 22 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 22

  1. devaid 👍 14 Answered C
    The question is C but not because the SQL Syntax, as you can perfectly use SparkSQL on Dataproc reading files from GCS. It's because the "serverless" requirement.
  2. TNT87 👍 4 Answered C
    This same question is there on Google's Professional Machine Learning Engineer, Question 4 Answer is C.
  3. MaxNRG 👍 3 Answered C
    Option C is the best approach to meet the stated requirements. Here's why: BigQuery SQL provides a fast, scalable, and serverless method for transforming structured data, easier to develop than PySpark. Directly ingesting the raw Cloud Storage data into BigQuery avoids needing an intermediate processing cluster like Dataproc. Transforming the data via BigQuery SQL queries will be faster than PySpark, especially since the data is already loaded into BigQuery. Writing the transformed results to a new BigQuery table keeps the original raw data intact and provides a clean output. So migrating to BigQuery SQL for transformations provides a fully managed serverless architecture that can significantly expedite development and reduce pipeline runtime versus PySpark. The ability to avoid clusters and conduct transformations completely within BigQuery is the most efficient approach here.
  4. AWSandeep 👍 3
    Changing answer to A as this is a new question referring to Dataproc Serverless. Dataproc Serverless for Spark batch workloads supports Spark SQL. Why modify ETL to ELT and convert PySpark to BigQuery SQL when it can be similar to a lift-and-shift?
  5. AWSandeep 👍 3 Answered C
    C. Ingest your data into BigQuery from Cloud Storage, convert your PySpark commands into BigQuery SQL queries to transform the data, and then write the transformations to a new table. Keys: "Serverless" and "SQL"
Show 17 more comments
  1. Atnafu 👍 3
    Dataproc is diffrent than Dataproc Serveless. This question is talking about dataproc. By the way dp serverless support both pyspark and sparkSql no need of conversion. C is best answer
  2. MoeHaydar 👍 3 Answered C
    Note: Dataproc by itself is not serverless https://cloud.google.com/dataproc-serverless/docs/overview
  3. Prudvi3266 👍 3 Answered C
    because of serverless nature
  4. maci_f 👍 2 Answered A
    In the GCP Machine Learning Engineer practice question (Q4) there's the same question with similar answers and the correct answer is A since B "is incorrect, here transformation is done on Cloud SQL, which wouldn’t scale the process" and C "is incorrect as this process wouldn’t scale the data transformation routine. And, it is always better to transform data during ingestion": https://medium.com/@gcpguru/google-google-cloud-professional-machine-learning-engineer-practice-questions-part-1-3ee4a2b3f0a4
  5. Wasss123 👍 2 Answered C
    I choose C BigQuery SQL is more performant but more expensive. Here, it's a performance issue ( time reduction) Source : https://medium.com/paypal-tech/comparing-bigquery-processing-and-spark-dataproc-4c90c10e31ac
  6. Atnafu 👍 2
    A could be answer if it was Dataproc serverless and no conversion of code. Dp serverless support: scala,pyspark,sparksql and SparkR
  7. evanfebrianto 👍 2
    Dataproc is not a serverless tool unless it mentions "Dataproc Serverless" explicitly.
  8. GCP001 👍 2 Answered A
    A) Looks more suitable , serverless approach for handling and performance.
  9. TNT87 👍 2
    Answer C
  10. carbonscave 👍 1 Answered C
    Why C is correct The ELT Pattern: This is the classic Extract, Load, Transform (ELT) architecture. For structured data, the absolute fastest way to process it on Google Cloud is to load the raw data directly into BigQuery's proprietary storage and let BigQuery's massive compute engine handle the heavy lifting. Jobs that take 12 hours on an on-premises Spark cluster often finish in minutes in BigQuery. Fully Serverless: BigQuery is Google's flagship fully serverless data warehouse. You don't provision nodes, manage clusters, or tune infrastructure. You just run your query. Native SQL: The prompt explicitly states you want to use "SQL syntax" to expedite development. BigQuery uses standard ANSI SQL, allowing your team to easily rewrite those PySpark transformations into clean, highly optimized SQL statements.
  11. musumusu 👍 1
    Answer C: need to setup SQL based job means transformation in not very complex. And Biqquery sql are faster than spark sql context. (google claims) However, i will make a test by myself to check it.
  12. John_Pongthorn 👍 1
    C is the most likely , bigquery is severless and sql D is dataflow severless but it is wrong at using python sdk but using sql beam rthen it will be correct
  13. Atnafu 👍 1
    C D-is incorrect because you are rebuild your batch pipeline for structured data on Google Cloud.
  14. ducc 👍 1
    After thinking a while, I think the question is not clear enough. To be honest
  15. ducc 👍 1
    A or C. I go for C because they said they want to use SQL syntax...
  16. ducc 👍 1
    The question said "use SQL syntax" C might still correct
  17. ducc 👍 1 Answered A
    A - You have to maintain PySpark Code -> Proc

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

Bigtable Row Key Design for Time-Series Data

S-grade

Monitoring Cloud Bigtable for Cluster Scaling Decisions

S-grade

← Back to PDE Study Guide