An organization is designing a data pipeline to process marketing analytics data collected from external advertising platforms. The analytics team wants to load the raw data directly into the cloud data warehouse first without pre-processing it in transit, and then use SQL scripts and Dataform to clean, model, and aggregate the data inside the warehouse. Which data integration pattern and Google Cloud service strategy should the team implement?

Implement an ELT (Extract, Load, Transform) pattern by loading raw data into BigQuery using BigQuery Data Transfer Service and transforming it within BigQuery using Dataform and SQL.: ### What the ELT pattern with BigQuery is The **ELT (Extract, Load, Transform)** pattern is a modern data integration strategy where raw data is extracted from source systems, loaded directly into the target data warehouse in its original format, and then transformed in place using the warehouse's built-in compute engine and SQL-based tooling such as **Dataform** or **Data Manipulation Language (DML)**. ### How it meets the requirements - **Load raw data first (✓):** Using the **BigQuery Data Transfer Service**, raw marketing data is ingested directly into **BigQuery** tables without requiring intermediate processing or preprocessing compute layers. - **In-warehouse transformations (✓):** Once staged in BigQuery, the unstructured or raw records are modeled, cleaned, and transformed into analytics-ready metrics using SQL queries, **Dataform pipelines**, or **materialized views**. - **Scalable SQL execution (✓):** Leverages BigQuery's massively parallel processing (MPP) architecture to execute transformations quickly on large datasets without maintaining external server infrastructure. ### Key advantages and capabilities - **Decoupled ingestion and modeling:** Raw data is preserved intact in storage, allowing analysts to re-run or adjust transformation logic at any time without re-extracting from sources. - **Native orchestration:** Tools like **Dataform** allow teams to manage SQL workflows with version control, dependency management, and automated testing natively inside BigQuery. - **Reduced pipeline complexity:** Ingestion is fully managed via automated transfers, removing the operational overhead of managing external transformation clusters or custom streaming jobs. - **Cost efficiency:** Organizations only consume BigQuery processing slots when running scheduled transformation queries rather than keeping processing workers constantly active. ### Why this is optimal Unlike traditional ETL pipelines that require heavy transformation logic to execute before storage, ELT shifts the transformation step to BigQuery, which is purpose-built for high-performance SQL analytics and transformation modeling.