Google Exam Professional Data Engineer Topic 3 Question 85 Discussion

Actual exam question for Google's Professional Data Engineer exam

Question #: 85
Topic #: 3

[All Professional Data Engineer Questions]

You are loading CSV files from Cloud Storage to BigQuery. The files have known data quality issues, including mismatched data types, such as STRINGS and INT64s in the same column, and inconsistent formatting of values such as phone numbers or addresses. You need to create the data pipeline to maintain data quality and perform the required cleansing and transformation. What should you do?

AUse Data Fusion to transform the data before loading it into BigQuery.

BLoad the CSV files into a staging table with the desired schema, perform the transformations with SQL. and then write the results to the final destination table.

CCreate a table with the desired schema, toad the CSV files into the table, and perform the transformations in place using SQL.

DUse Data Fusion to convert the CSV files lo a self-describing data formal, such as AVRO. before loading the data to BigOuery.

Show Suggested Answer

Suggested Answer: A

Data Fusion's advantages:

Visual interface: Offers a user-friendly interface for designing data pipelines without extensive coding, making it accessible to a wider range of users.

Built-in transformations: Includes a wide range of pre-built transformations to handle common data quality issues, such as:

Data type conversions

Data cleansing (e.g., removing invalid characters, correcting formatting)

Data validation (e.g., checking for missing values, enforcing constraints)

Data enrichment (e.g., adding derived fields, joining with other datasets)

Custom transformations: Allows for custom transformations using SQL or Java code for more complex cleaning tasks.

Scalability: Can handle large datasets efficiently, making it suitable for processing CSV files with potential data quality issues.

Integration with BigQuery: Integrates seamlessly with BigQuery, allowing for direct loading of transformed data.

by Yolando at Jun 02, 2024, 11:48 AM

Limited Time Offer

25%

Off

Get Premium Professional Data Engineer Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Izetta

5 months ago

Option B all the way! Gotta love that SQL power to whip those data quality issues into shape.

upvoted 0 times

...

Golda

6 months ago

Hmm, Option C might work, but I'd be worried about performing the transformations in-place on the final table. Better to have a separate staging area to play around with the data first.

upvoted 0 times

Ceola

5 months ago

Yeah, that way you can perform the transformations with SQL before writing to the final destination table.

upvoted 0 times

...

Scarlet

5 months ago

I think Option B is the way to go. Load the CSV files into a staging table first.

upvoted 0 times

...

Felicitas

6 months ago

I'm not sure about Option D. Converting to a self-describing format like AVRO might be overkill for this use case. I'd stick with the SQL-based approach in Option B.

upvoted 0 times

Raul

5 months ago

Option D does seem like it might be too complex for what we need to do with the data.

upvoted 0 times

...

Zachary

5 months ago

Yeah, using SQL to perform the transformations makes sense and is more straightforward.

upvoted 0 times

...

Paulene

5 months ago

I agree, Option B seems like the most practical solution for this scenario.

upvoted 0 times

...

Geoffrey

6 months ago

I agree with Evangelina. Option B seems like the best choice to ensure data quality and maintain flexibility in the transformation process.

upvoted 0 times

Leana

5 months ago

I agree with Evangelina. Option B seems like the best choice to ensure data quality and maintain flexibility in the transformation process.

upvoted 0 times

...

Alison

5 months ago

B) Load the CSV files into a staging table with the desired schema, perform the transformations with SQL, and then write the results to the final destination table.

upvoted 0 times

...

Felicia

5 months ago

A) Use Data Fusion to transform the data before loading it into BigQuery.

upvoted 0 times

...

Sue

6 months ago

I agree with Sarina. Option B allows for more control over the cleansing and transformation process.

upvoted 0 times

...

Evangelina

6 months ago

Option B is the way to go here. Staging the data first and then using SQL to perform the transformations is the most efficient and reliable approach.

upvoted 0 times

Sharee

5 months ago

Agreed, using SQL for transformations after loading the data into a staging table is a solid approach.

upvoted 0 times

...

Buddy

5 months ago

Option B is the way to go here. Staging the data first and then using SQL to perform the transformations is the most efficient and reliable approach.

upvoted 0 times

...

Onita

6 months ago

B) Load the CSV files into a staging table with the desired schema, perform the transformations with SQL, and then write the results to the final destination table.

upvoted 0 times

...