Prepare and test your skills
Prepare and test your skills
Applying data cleaning techniques using BigQuery (opens in a new tab) means removing duplicates, handling missing values, and standardizing formats so datasets are ready for analysis. BigQuery is Google Cloud's fully managed data warehouse for storing, cleaning, and analyzing large datasets with SQL. Cleaning can happen before data reaches BigQuery or inside BigQuery after loading, and validation confirms the result is ready for downstream dashboards, reports, or machine learning.
Deciding where cleaning happens in the load flow means choosing whether to transform data before loading into BigQuery or after loading. In the extract, transform, and load (ETL) pattern, data is trimmed, cleansed, or enriched before it is loaded into BigQuery. In the extract, load, and transform (ELT) pattern, raw data is loaded into BigQuery first and then cleaned with SQL transformations inside BigQuery. The ELT approach is useful when you want to reduce work outside BigQuery and use its scalable compute for cleaning.
Data moves from the source system to BigQuery through an ordered flow:
A five-step left-to-right process flow showing Extract to staging, Upload to Cloud Storage, Load into BigQuery, Transform and clean with SQL, and Validate with DVT for downstream use.
The choice of cleaning location involves tradeoffs in compute, tooling, and network cost:
| Cleaning location | Where work runs | Choose when |
|---|---|---|
| Current environment | Source system compute and tooling cleans data before transfer | You have an existing transformation process or want to reduce resource usage in BigQuery, but available capacity may limit throughput |
| Google Cloud | Dataflow (opens in a new tab), Cloud Data Fusion, or BigQuery cleans data after staging | You need virtually limitless processing capacity or want to enrich data during load, but enrichment may need extra transfer time and bandwidth |
Standardizing file formats and schemas for loading means making source data arrive in BigQuery with consistent structure and types. To reduce transfer complexity, export data in Apache AVRO, ORC, or Parquet, where schema information is embedded with the data. If you use CSV or another simple delimited format, you must specify the schema separately, either by providing a schema at load time or by allowing auto-detection of the schema from the CSV contents.
The Cloud Storage bucket that holds the staged files affects performance and cost. When transferring data through the network, choose the bucket location closest to the data center to minimize latency, and when possible choose the bucket location to be the same as the location of the BigQuery dataset. For on-premises data with good bandwidth, the Google Cloud CLI supports multi-threaded parallel uploads, resumes after errors, and encrypts traffic in transit for security. If network bandwidth is limited, use compression to limit data size or use Transfer Appliance for offline transfer.
Exam tip: Choose AVRO, ORC, or Parquet when you want the schema to travel with the data; choose CSV only when you plan to supply the schema separately or use auto-detection.
Cleaning and reshaping data inside BigQuery means using SQL queries, views, and pipelines to deduplicate rows, handle missing values, and standardize formats after loading. Dataform provides an end-to-end experience to build, version control, and orchestrate SQL pipelines in BigQuery, so repeated cleaning steps run in order on a schedule. Facade views can sit between BigQuery tables and downstream processes, so the view definition can combine old and new tables with a SQL UNION operation while upstream feeds are changed without breaking dashboards.
Several managed tools can clean data as it moves into BigQuery:
BigQuery also supports nested and repeated fields that allow further denormalization of new tables to take advantage of its columnar storage in Capacitor. Because storage and compute are decoupled, storage resources are allocated as data is added and deallocated as data is removed or tables are dropped. After cleaning, results can be exported to a local file, Google Drive, Google Sheets, Cloud Storage, Bigtable, Spanner, AlloyDB for PostgreSQL, and Pub/Sub in a process called reverse ETL.
Validating cleaned data before downstream analysis means confirming that the transfer and cleaning steps preserved correctness and completeness. The open source Data Validation Tool (DVT) compares data from the source system to the target in BigQuery as a Python command-line tool. You specify the aggregations to run, such as count, sum, and average, and the columns those aggregations apply to, and DVT reports differences between source and target.
Validation works best as an iterative check after each load expansion, not only at the end of migration. Transferring the schema without changes during initial iterations lets existing pipelines keep running while proofs of concept proceed in parallel. Once validated tables feed facade views and evolved downstream processes, the old source tables can be retired from the data flow and downstream tools can read directly from the cleaned BigQuery tables.
Utilizing Cloud Data Fusion (opens in a new tab) for visual data preparation means building code-free pipelines that explore, clean, and load data for analysis in BigQuery. Cloud Data Fusion is a fully managed, cloud-native, enterprise data integration service for quickly building and managing data pipelines. The service is powered by the open source project CDAP and provides code-free data integration with a graphical interface, so business users, developers, and data scientists can cleanse, prepare, blend, transfer, and transform data without managing infrastructure.
What Cloud Data Fusion provides for data cleaning is a visual environment for exploring data and fixing quality problems without writing code. The service graphically builds fully managed extract, transform, load (ETL) and extract, load, transform (ELT) data pipelines using an open source library of preconfigured connectors and transformations. Because the work happens through the graphical interface, the same cleaning logic can be built and understood by business users as well as developers. This approach removes the need to wrestle with servers or custom framework code during preparation.
Cloud Data Fusion supports the core cleaning and preparation actions in one place:
How visual preparation corrects inconsistencies is by letting a user inspect sample records and apply prebuilt transformation directives instead of scripting each fix. A user connects a source, inspects the sample records for anomalies such as missing values or mismatched formats, and then applies transformation directives to correct those inconsistencies. The preconfigured transformations handle common cleaning operations, so corrections are applied consistently across all records. This visual method lets teams validate data quality early while keeping the logic reusable.
A typical cleaning flow moves data from staging into an analytical table in order:
Building automated cleaning pipelines in Cloud Data Fusion means turning visual cleaning steps into a repeatable sequence that runs on new data. The pipeline depends on its configured connectors for input and output, while the fully managed control plane runs the processing without the team provisioning infrastructure. Once the visual steps are defined, the pipeline can be run on demand or on a schedule to cleanse, prepare, and load each new batch the same way. This automation turns one-time fixes into a reliable preparation process that keeps analytical datasets consistent over time.
Choosing Cloud Data Fusion over other transformation options depends on whether visual simplicity or custom processing power matters more. Each option uses a different development model and level of management, so the choice depends on the cleaning requirement and team skills.
| Option | Development model | Best fit from documentation |
|---|---|---|
| Cloud Data Fusion | Graphical, code-free pipelines with preconfigured connectors and transformations | Quickly build managed ETL/ELT cleaning pipelines without infrastructure work |
| Dataflow | Apache Beam model for complex transformations and enrichment graphs | Run complex, serverless transformations with virtually limitless processing capacity |
| Managed Service for Apache Spark | Apache Spark and Apache Hadoop clusters on a managed service | Process data with Spark or Hadoop-based logic, including JDBC extraction and Spark transformations |
Exam tip: Choose Cloud Data Fusion when the requirement is graphical, code-free cleaning with preconfigured transformations, and choose Dataflow when the requirement calls for complex custom transformations with the Apache Beam model.
Identifying and implementing data cleaning procedures means selecting the right Google Cloud tool to fix raw data so it is accurate and ready for analysis in BigQuery. Data cleaning includes deduplication to remove duplicate rows, missing value imputation to fill or flag empty fields, and data type correction to make dates, numbers, and strings match the target schema. On Google Cloud, cleaning is handled as part of transformation, either before data reaches BigQuery or after it is loaded, using Cloud Data Fusion (opens in a new tab), BigQuery (opens in a new tab) with SQL, Dataflow (opens in a new tab), and Dataprep by Trifacta.
Where cleaning happens in the load path is either in the current environment before transfer or on Google Cloud during load into BigQuery. Transforming in the current environment keeps BigQuery resource use lower and can use existing processes, but throughput is limited by local compute capacity and tooling. Enriching data locally can also add transfer time and require more network bandwidth. Transforming on Google Cloud moves the heavy work to managed services that scale with data size.
Data typically flows from source files to staging storage to Cloud Storage to BigQuery. The recommended flow is:
Preparing source files for clean loads starts with a file format that preserves schema information. Apache AVRO, ORC, and Parquet embed schema with the data, which reduces transfer complexity and helps catch type mismatches early. CSV and other delimited formats do not embed schema, so the schema must be specified separately at load time or auto-detected from file contents.
Upload options depend on where the data lives and how much bandwidth is available:
Compression can reduce data size when bandwidth is limited, and third-party partner tools are another option when the gcloud CLI cannot be used.
Cloud Data Fusion for code-free cleaning is a fully managed, cloud-native enterprise data integration service for building and managing data pipelines. The service provides code-free data integration with a graphical interface and is powered by the open source project CDAP (Cask Data Application Platform). Pipelines are built from a library of preconfigured connectors and transformations to cleanse, prepare, blend, transfer, and transform data without managing infrastructure.
Cloud Data Fusion fits teams that need visual, repeatable cleaning for deduplication, filling missing values, and fixing types across many sources. Business users, developers, and data scientists can reuse the same connectors to read from databases and files, apply cleaning steps, and write clean tables to BigQuery. Choose Cloud Data Fusion when the requirement is fast pipeline development with managed execution rather than hand-written processing code.
BigQuery and SQL for post-load cleaning is the analysis warehouse where cleaned data lands and where SQL-based cleaning can also run after loading. This extract, load, and transform pattern loads raw or lightly cleaned files from Cloud Storage into BigQuery, then uses GoogleSQL queries to trim, filter, deduplicate, cast data types, and handle nulls. Dataform helps data teams build, version control, and orchestrate those SQL pipelines inside BigQuery for scheduled transformations.
BigQuery SQL fits cleaning that is set-based and close to analytics, such as removing duplicates, standardizing formats, and correcting types before dashboards and reports. Loading options from Cloud Storage include BigQuery Data Transfer Service, the Storage Write API, and batch load. Choose BigQuery and SQL when the data is already in BigQuery and the cleaning logic can be expressed clearly as queries.
Dataflow for large-scale transformations is a serverless and fully managed service for defining and running complex data transformations and enrichment graphs. Pipelines are written with the Apache Beam model, which reads from sources such as Cloud Storage, applies cleaning and enrichment steps in order, and writes results to BigQuery through the BigQuery I/O connector. The service provides virtually limitless processing capacity because Google manages scaling and infrastructure.
Dataflow fits large, complex, or streaming cleaning that must run before or during load, including multi-step validation, enrichment with outside data, and high-volume deduplication. The same pipeline pattern can handle the initial bulk load of terabytes and later incremental updates. Choose Dataflow when cleaning needs custom logic, very large scale, or continuous processing that visual pipelines or SQL alone cannot handle efficiently.
Dataprep by Trifacta for interactive exploration is a data service for visually exploring, cleaning, and preparing structured and unstructured data for analysis, reporting, and machine learning. Source data is exported into JSON or CSV files, transformed interactively in Dataprep to fix inconsistencies and missing values, and then loaded at scale using Dataflow. This combination pairs human review of messy samples with managed execution for the full dataset. Choose Dataprep by Trifacta when analysts need to see and shape dirty values before automating the cleaning pipeline.
Validating cleaned data with the Data Validation Tool confirms that cleaning preserved completeness and correctness after data reaches BigQuery. The Data Validation Tool (DVT) is an open source Python command-line tool that compares source data to the BigQuery target. DVT runs aggregations such as count, sum, and average on selected columns and reports differences. A clean load shows matching counts after deduplication, expected handling of nulls, and values that fit the corrected types.
Exam tip: Choose Cloud Data Fusion for code-free visual pipelines, BigQuery SQL for set-based cleaning after loading, and Dataflow for large-scale or complex Apache Beam transformations before loading.
Choosing a cleaning tool for the requirement means matching how the cleaning is built to when each approach is most efficient:
| Tool | How cleaning is built | Choose when |
|---|---|---|
| Cloud Data Fusion | Graphical pipeline with preconfigured connectors and transformations | Teams need managed, code-free ETL/ELT across many sources |
| BigQuery with SQL | SQL queries and scheduled Dataform pipelines in the warehouse | Data is already loaded and cleaning is deduplication, filtering, and type casting |
| Dataflow | Apache Beam pipeline with serverless autoscaling | Cleaning is complex, very large, enriched, or streaming |
All three paths share the same goal of delivering trusted tables for analysis. Raw files land in Cloud Storage, the selected tool applies deduplication, imputation, and type correction, and BigQuery stores the validated result for export to tools such as Looker, Sheets, or Pub/Sub.
In extract, transform, and load (ETL), data is trimmed, cleansed, or enriched before it is loaded into BigQuery, while in extract, load, and transform (ELT), raw data is loaded into BigQuery first and then cleaned with SQL transformations inside BigQuery. ELT is useful when you want to reduce work outside BigQuery and use its scalable compute for cleaning.
Apache AVRO, ORC, and Parquet embed schema information with the data, which reduces transfer complexity when loading into BigQuery. CSV and other simple delimited formats do not embed schema, so you must provide the schema at load time or allow auto-detection from the file contents.
Use Cloud Data Fusion when you need to quickly build managed cleaning pipelines through a graphical, code-free interface using preconfigured connectors and transformations without managing infrastructure. Use Dataflow when cleaning requires complex custom transformations and enrichment graphs defined with the Apache Beam model with serverless, virtually limitless processing capacity.
The Data Validation Tool (DVT) is an open source Python command-line tool that compares data from the source system to the target in BigQuery. You specify aggregations such as count, sum, and average and the columns those aggregations apply to, and DVT reports differences between source and target.
A data analytics team has loaded raw customer transaction records directly into a BigQuery table. Before the dataset can be analyzed, the team must perform several data cleaning tasks:
Which BigQuery transformation method should the team use to perform these in-place data cleaning operations?
Last updated