A data analytics team is designing an Extract, Load, Transform (ELT) architecture for their organization's marketing data. The requirements specify: Automatically ingest raw data from supported external SaaS platforms on a scheduled basis without custom code. Load the raw data directly into the cloud data warehouse first. Perform transformations, data modeling, and aggregations natively within the data warehouse using SQL. Which combination of Google Cloud services should the team use to meet these requirements?

Use BigQuery Data Transfer Service to load raw data into BigQuery, and use Dataform to execute transformations inside BigQuery.: ### **What BigQuery Data Transfer Service and BigQuery/Dataform are** **BigQuery Data Transfer Service (DTS)** is a fully managed service that automates the scheduled ingestion of data from external SaaS applications, third-party platforms, and cloud storage into **BigQuery**. **BigQuery** is Google Cloud's serverless, highly scalable enterprise data warehouse, and **Dataform** is a service used to develop, version control, and orchestrate SQL workflows directly inside BigQuery. ### **How it meets the ELT requirements** - **Extract and Load (E & L):** BigQuery Data Transfer Service extracts raw marketing and SaaS data (such as Google Ads, Campaign Manager, Salesforce, and Cloud Storage) on a predefined schedule and loads the unmodified raw data directly into BigQuery datasets without requiring custom extraction code. - **Transform (T):** Once the raw data resides within BigQuery, SQL-based tools like **Dataform** or BigQuery scheduled queries perform transformations, deduplication, and aggregation natively inside the warehouse engine, leveraging BigQuery's scalable compute power. - **Codeless Ingestion:** Data engineers do not need to write or maintain complex data connectors or pipeline scripts to ingest external data sources. ### **Key advantages of this architecture** - **Warehouse-native processing:** Shifts compute loads to BigQuery's distributed SQL engine, avoiding external intermediate processing clusters. - **Preservation of raw data:** Retains raw ingested data within the warehouse for auditing, re-processing, or exploratory analysis. - **Automated orchestration:** Dataform provides built-in dependency management, data quality testing, and SQL workflow scheduling. - **Low operational overhead:** Both services are serverless and fully managed by Google Cloud. ### **Why this is optimal** This architecture perfectly demonstrates the **ELT (Extract, Load, Transform)** pattern. Instead of transforming data prior to loading it into the warehouse, data is loaded rapidly in its native raw state and transformed post-load using SQL.