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: Update missing numeric values with column defaults Delete duplicate transaction records Modify existing records in-place using standard GoogleSQL Which BigQuery transformation method should the team use to perform these in-place data cleaning operations?

Execute Data Manipulation Language (DML) statements in BigQuery: ### **What Data Manipulation Language (DML) in BigQuery is:** **Data Manipulation Language (DML)** in BigQuery consists of standard **GoogleSQL** statements (`UPDATE`, `DELETE`, `INSERT`, `MERGE`, and `TRUNCATE`) that allow users to mutate and manipulate data stored directly inside existing BigQuery tables. ### **How it meets the data cleaning requirements:** - **Impute missing values (✓):** An `UPDATE` statement can easily scan table columns and replace `NULL` entries with standard defaults (such as `0` or `'Unknown'`). - **Deduplicate records (✓):** A `DELETE` or `MERGE` statement can identify and remove duplicate rows based on primary keys or timestamp logic directly within the table. - **In-place SQL modifications (✓):** DML statements operate directly on existing tables in place, modifying rows without requiring external data pipelines or exporting to another storage layer. ### **Key advantages and capabilities:** - **Standard SQL syntax:** Data analysts can perform complex data cleaning procedures using familiar SQL syntax without learning specialized pipeline code. - **ACID transactions:** BigQuery ensures data integrity and consistency during concurrent DML operations. - **Support for partitioned tables:** DML can target specific partitions, optimizing scan costs and performance during large-scale data maintenance. - **Queue management:** BigQuery automatically manages and queues concurrent DML operations. ### **Why this is optimal:** When raw data is already stored inside BigQuery, executing **SQL DML statements** provides the simplest, lowest-friction, and most cost-effective method to cleanse and prepare data in place before downstream reporting.