Cleaning Data in Dataflow Before BigQuery Load

What an EL Architecture and BigQuery Data Transfer Service Are

Extract and Load (EL) is a data movement pattern that focuses exclusively on transferring data from a source directly into a destination sink without performing any data manipulation, cleaning, filtering, or transformation along the way.

BigQuery Data Transfer Service (DTS) is a fully managed service designed to automate scheduled batch data transfers into BigQuery from supported sources such as Google Ads, YouTube, Cloud Storage, or third-party cloud data warehouses.

Why It Doesn't Meet the Requirements

• Omits data transformations (✗): EL deliberately bypasses the "Transform" phase. The scenario explicitly requires data cleansing, business rule validation, and aggregations before storage.
• No custom Apache Beam code support (✗): BigQuery Data Transfer Service is an out-of-the-box configuration tool for moving files and supported datasets. It cannot execute custom Python or Java Apache Beam pipelines.
• Raw landing only: EL lands unvalidated, raw data into the warehouse, requiring a subsequent transformation step to clean the data.

Key Characteristics and Limitations of EL

• Simplified movement: Minimizes pipeline complexity by relying on automated managed tools to replicate tables as-is.
• No intermediate compute overhead: Avoids running dedicated compute servers prior to storage, but leaves all data cleansing to downstream consumers.
• Prerequisite to ELT: EL is commonly used as the first stage of an ELT pipeline, after which in-warehouse transformations occur.

This Answer Would Be Correct If:

The team wanted an automated, scheduled mechanism to copy raw data into BigQuery without performing any filtering, data preparation, or custom transformations prior to loading.

What Reverse ETL Is

Reverse ETL is a downstream data movement process where enriched, aggregated, and processed data from a centralized data warehouse (such as BigQuery) is exported back into operational business systems—such as CRM applications, ERP systems, or marketing automation platforms—to support operational workflows and automated decision-making.

Why It Doesn't Meet the Requirements

• Opposite data direction (✗): The scenario describes an upstream ingestion pipeline that brings data from external transactional sources into the warehouse. Reverse ETL is a downstream export process moving data outward from the warehouse to operational endpoints.
• Does not ingest or clean source data (✗): Reverse ETL assumes data has already been ingested, cleaned, and modeled within the data warehouse.
• Not an ingestion framework (✗): Reverse ETL does not address how external database records are extracted, cleansed with Apache Beam, and loaded into BigQuery.

Key Characteristics and Limitations of Reverse ETL

• Operational enablement: Often referred to as "operational analytics," it synchronizes customer metrics (like churn risk or lifetime value) into tools like Salesforce or HubSpot.
• Feedback loop pattern: It implements an architectural feedback loop where data originates in operational systems, is processed in the warehouse, and feeds back into day-to-day transaction tools.
• Specialized connectors: It focuses on API rate limiting, incremental synchronization, and entity mapping into third-party SaaS APIs and operational databases.

This Answer Would Be Correct If:

The enterprise had already modeled customer profiles inside BigQuery and needed a pipeline to push curated aggregate scores out of BigQuery into an external transactional CRM or product catalog system.

What ELT and Dataform Are

Extract, Load, Transform (ELT) is a modern cloud data warehousing pattern where raw, unmodified data is extracted from sources and immediately loaded directly into the data warehouse (such as staging tables in BigQuery). Once loaded, transformations and business logic are executed directly inside the data warehouse using SQL scripts or transformation management tools like Dataform.

Why It Doesn't Meet the Requirements

• Fails the pre-storage transformation requirement (✗): The scenario explicitly specifies that transformations, cleaning, and validations must take place prior to data reaching the data warehouse. ELT loads raw, uncleaned records directly into BigQuery first.
• Consumes BigQuery resources for transformation (✗): Under an ELT pattern, transforming data utilizes BigQuery query processing slots and compute capacity. This conflicts directly with the requirement that BigQuery resources must be reserved exclusively for concurrent user reporting and dashboard queries.
• Does not utilize Apache Beam (✗): Dataform operates by generating and orchestrating SQL-based transformations inside BigQuery, rather than executing Apache Beam Java or Python pipelines.

Key Characteristics and Limitations of ELT

• In-warehouse computation: ELT leverages the massive distributed SQL engine of cloud data warehouses like BigQuery to perform transformations rapidly at scale.
• Raw data retention: ELT stores raw, uncurated tables inside the warehouse, allowing data analysts to query raw events for audits and exploration.
• Resource competition: Running massive transformation jobs inside the data warehouse during peak hours can compete with interactive analytical queries unless dedicated slot reservations or off-peak scheduling are implemented.

This Answer Would Be Correct If:

The organization wanted to load raw data directly into BigQuery staging tables first, preferred using SQL and Dataform for transformation logic, and had sufficient BigQuery capacity or off-peak processing windows to handle in-warehouse transformation.