Handling Data Quality Issues with Cloud Data Fusion

PDE By islever Editorial Last updated

Planning the data pipelines. Building the pipelines.

Short answer

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

Question

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?
  1. Use Data Fusion to transform the data before loading it into BigQuery. Correct Answer
  2. Use Data Fusion to convert the CSV files to a self-describing data format, such as AVRO, before loading the data to BigQuery.
  3. 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.
  4. Create a table with the desired schema, load the CSV files into the table, and perform the transformations in place using SQL.

Key Takeaways

The main trap is assuming you can load dirty data into a strict BigQuery schema; you must transform the data first using a tool like Data Fusion that handles type coercion.

Learn how to use Cloud Data Fusion to cleanse and transform messy CSV data, such as mixed data types, before loading it into BigQuery to avoid load failures.

Common mistake: Choosing Option C (Staging Table) is a common mistake because users assume SQL cleaning is easier. However, loading mismatched types into any BigQuery table with a fixed schema will cause the load job to fail immediately.

Expert Analysis

The question requires creating a data pipeline to load CSV files into BigQuery while handling significant data quality issues, specifically mismatched data types (STRING vs INT64) and inconsistent formatting. The key constraint is that the source data has structural integrity problems that prevent direct loading into a strictly typed target schema.

Option A is correct because Cloud Data Fusion, particularly with the Wrangler plugin, is designed for visual data preparation and transformation without code. It can handle type coercion, cleansing, and formatting adjustments on raw or semi-structured data before it reaches the final destination. This allows the pipeline to absorb the 'dirty' input and output clean, schema-compliant data. Comments highlight that Data Fusion's visual interface and pre-built transformations are ideal for this 'data type trap' where raw strings must be converted to integers or formatted values cleaned.

Option B is incorrect because converting to AVRO does not solve the underlying data quality issues; it merely changes the container format. The type mismatches would still exist within the AVRO records, requiring subsequent transformation.

Option C involves loading into a staging table. This fails because BigQuery tables have strict schemas. If you attempt to load a column defined as INT64 but containing STRING data, the load job will fail immediately. You cannot load 'bad' data into a 'strict' table to fix it later in SQL unless you use a flexible schema or load into a generic JSON/STRING column first, which adds complexity not implied by the simple 'staging table' option. While some argue for cost reasons, exam logic prioritizes architectural correctness: you cannot load malformed data into a typed table.

Option D is impossible for the same reason as C. You cannot create a table with a desired schema (e.g., INT64) and then load CSVs containing STRINGs into it without error. The load operation itself will reject the data.

The decisive factor is that Data Fusion provides the necessary transformation layer before or during ingestion to ensure data conforms to the target schema, whereas direct loading options (C and D) will crash due to type conflicts.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When faced with 'dirty' data and a strict target schema, always look for a transformation step that occurs before the final load. If the data violates the target schema (e.g., text in an integer column), direct loads (even to staging tables with that schema) will fail. Use managed integration tools like Data Fusion for visual, code-free cleaning of complex formats.

Frequently Asked Questions

Can I load CSVs with wrong data types into a BigQuery staging table?

No, if the staging table has a strict schema (e.g., INT64) and the CSV contains non-numeric strings, the load job will fail. You must either use a flexible schema or transform the data first.

What is Data Fusion Wrangler?

Wrangler is a plugin in Cloud Data Fusion that provides a visual, interactive environment for exploring, cleaning, and transforming data without writing code, making it ideal for handling messy inputs.

More PDE FAQ →

Community Votes

A
91%
C
9%

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

Discussion 19 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 19

  1. saurabhsingh4k 👍 6 Answered A
    I'm kinda inclined towards C as SQL seems a powerful option to treat this kind of use case. Also, I didn't get how the transformations mentioned on this page will help to clean the data (https://cloud.google.com/data-fusion/docs/concepts/transformation-pushdown#supported_transformations) But I guess using Wrangler plugin, this kind of stuff can be done on DataFusion, also the question talks about an pipeline, so A is the final choice.
  2. maci_f 👍 4 Answered A
    Data Fusion enables changing the data type directly as shown in this lab: https://www.cloudskillsboost.google/focuses/25335?parent=catalog Wrangler is the feature to enable that, as already mentioned: https://stackoverflow.com/questions/58699872/google-cloud-data-fusion-how-to-change-datatype-from-string-to-date
  3. squishy_fishy 👍 4
    Okay, second thought, it is asking for a pipeline, so the answer should be A. At work, we use dataflow inside the composer to build a pipeline injecting data into landing/staging table, then transform/clean data in the sort table, then send the cleaned data to deliver table.
  4. musumusu 👍 4
    Answer C, Datafusion is costly and current transformation is just a cast transformation in a column. I guess no one wanna pay for datafusion for this little transformation and Staging table processing handles such minor cleaning.
  5. squishy_fishy 👍 4
    The answer is C. That is what we do at work. We have landing/staging table, sort table and deliver table,
Show 14 more comments
  1. Adswerve 👍 4 Answered C
    C is the right answer. Do ELT in BigQuery. Data Fusion is not the right too for this job.
  2. phidelics 👍 4 Answered A
    Keyword: Data Pipeline
  3. MaxNRG 👍 3 Answered 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.
  4. Mike422 👍 2
    Apparently chatGPT thinks C is the correct answer just sayin (for the same reason that @saurabhsingh4k wrote)
  5. mialll 👍 2 Answered A
    same as @saurabhsingh4k
  6. samirzubair 👍 2
    The Correct Ans is C
  7. MaxNRG 👍 1
    Why other options are less suitable: B. Converting to AVRO: While AVRO is a self-describing format, it doesn't inherently address data quality issues. Transformations would still be needed, and Data Fusion provides a more comprehensive environment for this. C. Staging table: Requires manual SQL transformations, which can be time-consuming and error-prone for large datasets with complex data quality issues. D. Transformations in place: Modifying data directly in the destination table can lead to data loss or corruption if errors occur. It's generally safer to keep raw data intact and perform transformations separately. By using Data Fusion, you can create a robust and efficient data pipeline that addresses data quality issues upfront, ensuring that only clean and consistent data is loaded into BigQuery for accurate analysis and insights.
  8. carbonscave 👍 1 Answered A
    Why this is the right move The Data Type Trap: The massive clue here is that you have strings and integers mixed in the exact same column. If you try to push a text string (like "Unknown" or a malformed number) into a BigQuery column that is strictly defined as an integer (INT64), the load job will just crash and reject the data. You physically can't get it into the database to fix it later. Cleaning before loading (ETL): Because you can't load dirty data into a strict schema, you have to intercept and clean it before it hits BigQuery. Cloud Data Fusion is built exactly for this kind of messy integration. It has a visual UI (Wrangler) that makes it incredibly easy to standardize messy phone numbers, fix addresses, and untangle mixed data types so the data is completely clean before it ever knocks on BigQuery's door.
  9. Atnafu 👍 1
    A https://cloud.google.com/data-fusion/docs/concepts/overview#:~:text=The%20Cloud%20Data%20Fusion%20web%20UI%20lets%20you%20to%20build%20scalable%20data%20integration%20solutions%20to%20clean%2C%20prepare%2C%20blend%2C%20transfer%2C%20and%20transform%20data%2C%20without%20having%20to%20manage%20the%20infrastructure.
  10. TNT87 👍 1 Answered A
    Ans A https://cloud.google.com/data-fusion/docs/concepts/transformation-pushdown#supported_transformations
  11. jkhong 👍 1
    although this is my preferred answer. this doesn’t satisfy how this becomes a pipeline.
  12. AWSandeep 👍 1 Answered A
    A. Use Data Fusion to transform the data before loading it into BigQuery.
  13. ducc 👍 1 Answered A
    A is correct for me
  14. hiromi 👍 1 Answered A
    Data Fusion

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

Migrating Hadoop Spark Jobs to Dataproc Serverless and Cloud Storage

S-grade

← Back to PDE Study Guide