Fundamental
Prepare and test your skills
Fundamental
Prepare and test your skills
ELT Loads Raw Data Before Transforming in BigQuery
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,132 more like it. Try 10 free: instant score, no account.
A data analytics team is designing a data ingestion pipeline on Google Cloud. The team has established the following operational requirements:
- Raw transaction records from Cloud Storage must be ingested directly into BigQuery staging tables without prior processing.
- Data cleansing, standardization, and complex business metric aggregations must be performed directly within BigQuery using scheduled SQL queries.
- Data analysts must have continuous access to the original, unmodified raw datasets stored within the warehouse for exploratory queries.
Which data pipeline methodology does this architecture represent?
Explanations
ETLT (Extract, Transform, Load, Transform)
ETLT (Extract, Transform, Load, Transform)
What ETLT is
ETLT (Extract, Transform, Load, Transform) is a hybrid data pipeline architecture that combines elements of both ETL and ELT. In an ETLT pipeline, data undergoes an initial lightweight transformation (such as masking sensitive PII or data format normalization) in an external processing engine, is loaded into the warehouse, and then undergoes heavier business-logic transformations using in-warehouse SQL.
Why it doesn't meet the requirement
- Missing initial transformation: The scenario explicitly states that raw data is ingested directly from Cloud Storage into BigQuery without any prior processing or transformation.
- Architectural mismatch: An ETLT pattern requires two distinct transformation phases—one prior to warehouse ingestion and another inside the warehouse—which is not present in this scenario.
Key characteristics and limitations
- Hybrid complexity: Involves orchestrating two distinct transformation layers (e.g., Cloud Dataflow for pre-load masking followed by BigQuery SQL for aggregation).
- Compliance-oriented: Frequently used in regulated industries where raw data cannot legally enter the cloud data warehouse without masking or tokenization.
- Operational maintenance: Requires managing both data processing code (e.g., Java/Python) and warehouse SQL scripts.
This answer would be correct if
The pipeline required data to be pre-filtered and masked for compliance in Dataflow before loading into BigQuery, followed by further aggregation and joining using SQL inside the warehouse.
Reverse ETL
Reverse ETL
What Reverse ETL is
Reverse ETL is a data operationalization methodology that copies processed, standardized data out of a centralized data warehouse (like BigQuery) and syncs it back into downstream operational business applications (such as CRM systems, marketing automation platforms, ERPs, or customer service tools).
Why it doesn't meet the requirement
- Opposite data direction: Reverse ETL moves data out of analytical storage into operational tools, whereas the scenario describes ingesting raw source data into a data warehouse and transforming it internally.
- Different business purpose: The purpose of Reverse ETL is operational activation (e.g., updating customer scores in Salesforce), not data ingestion, staging, or warehouse transformation.
Key characteristics and limitations
- Downstream integration: Relies on data that has already been ingested, cleaned, and aggregated within a data warehouse.
- API-driven destinations: Pushes structured records to third-party SaaS APIs and operational databases.
- Complements ingestion pipelines: Does not replace ingestion methodologies (ETL, ELT); rather, it acts as the final step in closing the loop between analytics and operational workflows.
This answer would be correct if
The scenario described querying aggregated customer metrics in BigQuery and syncing those calculated metrics into third-party business tools like Salesforce or Zendesk.
ETL (Extract, Transform, Load)
ETL (Extract, Transform, Load)
What ETL is
ETL (Extract, Transform, Load) is a traditional data integration methodology where data is extracted from source systems, processed and transformed in an intermediate compute environment (such as a dedicated ETL engine or a processing service like Cloud Dataflow), and only then loaded into the target data warehouse or database.
Why it doesn't meet the requirement
- Order of operations: In an ETL architecture, transformations occur before the data reaches the storage destination. In the scenario, data is loaded into BigQuery first and transformed after ingestion.
- Loss of raw data: Traditional ETL typically discards or leaves raw source data outside the target warehouse, loading only the refined, transformed output.
- Compute placement: In ETL, transformations are executed by an external data processing layer (e.g., Apache Beam/Dataflow) rather than natively inside the data warehouse using SQL.
Key characteristics and limitations
- High compute overhead upfront: Requires provisioning and managing intermediate processing infrastructure to transform data before loading.
- Rigid data models: If downstream reporting requirements change, data must be re-extracted from original sources and processed through the ETL pipeline again.
- Ingestion bottleneck: Large-scale transformations before loading can increase end-to-end latency before analysts can access the data.
This answer would be correct if
The scenario required sensitive data (such as PII) to be anonymized, filtered, or restructured using Cloud Dataflow before any records were loaded into BigQuery for security or compliance purposes.
ELT (Extract, Load, Transform)
ELT (Extract, Load, Transform)
What ELT is
ELT (Extract, Load, Transform) is a modern data integration methodology where data is first extracted from source systems, immediately loaded into a centralized data repository (such as a cloud data warehouse or data lake), and subsequently transformed directly within that target system using its native compute capabilities.
ELT: Load First, Transform Inside BigQuery
Process Flow
How it meets the pipeline requirements
- Direct ingestion without pre-processing (✓): Raw records from Cloud Storage are loaded directly into BigQuery staging tables, minimizing pipeline complexity and ingestion latency.
- In-warehouse transformations (✓): Data cleansing, normalization, and business aggregations are performed inside BigQuery using its massively parallel processing (MPP) architecture and scalable SQL engine.
- Raw data preservation (✓): Because transformations happen after loading, the raw, unmodified data remains permanently accessible in warehouse staging tables for ad-hoc analysis, re-processing, and auditing.
Key advantages and capabilities
- High scalability: Leverages the distributed compute power of modern cloud data warehouses like BigQuery instead of relying on separate, dedicated transformation compute clusters.
- Faster time-to-insight: Data is made available in the warehouse immediately upon loading, allowing analysts to write custom SQL transformations as analytical requirements evolve.
- Simplified architecture: Eliminates intermediate transformation steps and specialized ETL servers prior to storage.
- Flexibility: Raw data can be transformed into multiple different target models repeatedly without needing to re-extract data from the source systems.
Why this is optimal
In modern cloud architectures featuring high-performance data warehouses like BigQuery, ELT is the optimal choice. It decouples extraction and loading from data transformation, maximizing throughput while reducing infrastructure management overhead.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,132 practice questions across all 4 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
FundamentalAssociate Data Practitioner
BigQuery Data Transfer Service and Dataform for ELT
Differentiate between different data manipulation methodologies (e.g., ETL, ELT, ETLT)
FundamentalAssociate Data Practitioner
ETL with Dataflow Pre-Load Transformations
Differentiate between different data manipulation methodologies (e.g., ETL, ELT, ETLT)
FundamentalAssociate Data Practitioner
GROUP BY Clause Requirement for Aggregation
Define and execute SQL queries in BigQuery to generate reports and extract key insights
FundamentalAssociate Data Practitioner
BigQuery ML
Identify ML use cases for developing models by using BigQuery ML and AutoML
FundamentalAssociate Data Practitioner
Customer-Managed Encryption Keys
Understand the role of Cloud Key Management Service (Cloud KMS) to manage encryption keys
AdvancedProfessional Cloud Security Engineer
Workload Identity Federation Audit Logging
Configuring Workload Identity Federation.
Last updated