Prepare and test your skills
Prepare and test your skills
Contrast Data Pipeline Architectures of ETL, ELT, and ETLT means comparing when transformation happens relative to loading data into the warehouse. Extract, transform, and load (ETL) transforms data before loading, extract, load, and transform (ELT) loads raw data first and transforms it inside the warehouse, and extract, transform, load, and transform (ETLT) does a light transform before loading plus a fuller transform after loading. In Google Cloud, raw or transformed data is typically loaded into BigQuery (opens in a new tab) as the warehouse, while movement and outside-warehouse processing use services such as Cloud Data Fusion, Dataflow, and Managed Service for Apache Airflow.
Extract, transform, and load (ETL) is a procedure where data flows from source systems through transformation outside the warehouse and then into the warehouse. Sources are usually transactional systems such as relational databases, the middle processing layer cleans and reshapes the data, and the sink connects to the data warehouse. Because transformation runs outside the warehouse, warehouse resources stay available for concurrent querying instead of being used to prepare data.
A typical ETL pipeline follows an ordered sequence:
Pipelines work best with as few source systems as possible because each additional source adds a failure point such as an unavailable system. The tradeoff is tooling, because teams must learn additional tools and languages beyond SQL to express transformations that run outside the warehouse.
Extract, load, and transform (ELT) is a procedure where raw data is extracted from source systems, loaded into BigQuery tables in raw form, and then transformed inside BigQuery. After loading, Dataform manages the transformation into an organized, tested, and documented suite of tables. While data manipulation language takes an imperative approach by telling BigQuery exactly how to change data, Dataform uses declarative statements where Dataform determines the transformation needed to reach that state.
An ELT pipeline runs in a fixed order: first, data flows from the source to BigQuery tables in raw form; then, SQL workflows run inside BigQuery in order of object dependencies; finally, the defined tables and views become available for analysis. This approach preserves raw data in the warehouse and uses BigQuery scale for transformation. BigQuery pipelines, powered by Dataform and managed visually in BigQuery Studio, can implement both ELT and ETL processes.
Exam tip: ETL transforms data outside the warehouse before loading, while ELT loads raw data into BigQuery first and transforms it inside BigQuery with SQL.
Extract, transform, load, and transform (ETLT) is a hybrid architecture that splits transformation into two stages around the load step. A light transform happens before loading, such as cleaning, filtering, or replication handling, and a fuller business transform happens after loading inside the warehouse. Data flows from the source system to a pipeline service for initial processing, then to BigQuery for loading, then through in-warehouse SQL for final modeling.
This hybrid fits migrations and complex sources. For example, an initial pipeline can capture Oracle or other relational data, handle schema updates, and load it to BigQuery, and Dataform can then build tested tables, views, or materialized views from that raw landing data. Choose ETLT when raw data needs early cleanup or shaping but the main business logic still benefits from BigQuery SQL, version control, and dependency management.
Batch pipelines and streaming pipelines are two timing patterns that any of these architectures can use. Batch pipelines process data collected over time, such as once per day, while streaming pipelines handle real-time events such as change data capture row changes from online transaction processing databases. Dataflow supports both patterns with autoscaling, and Cloud Data Fusion supports batch and streaming replication from many source systems.
Comparing ETL, ELT, and ETLT shows differences in sequence, location of heavy work, and fit for the workload:
| Architecture | Sequence | Choose when |
|---|---|---|
| ETL | Extract, then transform outside warehouse, then load | Warehouse compute must stay free for queries, or data must be cleaned and aggregated before landing |
| ELT | Extract, then load raw data, then transform in BigQuery | Raw data should be preserved and transformed with scalable SQL, testing, and version control |
| ETLT | Light transform, then load, then fuller transform in BigQuery | Sources need early cleanup or replication handling, but final modeling belongs in the warehouse |
Lift-and-shift keeps an existing integration tool and changes storage stages to BigQuery, including running vendor tools on Compute Engine. Rearchitecting replaces that tooling with Cloud Data Fusion for visual ingestion, Dataflow for coded batch and streaming processing, Managed Service for Apache Airflow for orchestration, and BigQuery with Dataform for post-load transformation.
Map Google Cloud Services to Processing Architectures means deciding where transformation work runs on Google Cloud. Cloud Data Fusion and Dataflow perform compute-based transformation outside the warehouse, while BigQuery (opens in a new tab) performs warehouse-native transformation inside the warehouse using SQL. Managed Service for Apache Airflow orchestrates these steps across cloud and on-premises systems.
Cloud Data Fusion is a managed CDAP service that builds batch and streaming pipelines through a visual drag-and-drop interface. Data flows from source systems through source plugins into transformation and cleaning steps, then through sink plugins to the destination. A BigQuery plugin loads data to BigQuery and handles schema updates, and the Wrangle feature cleans and prepares data without writing code.
Choose Cloud Data Fusion when teams want visual development, many ready-made connectors, and replication from systems such as Oracle into BigQuery. It captures data from many kinds of source systems with batch and streaming replication, so it fits ingestion-heavy work where coding a custom pipeline would be slower.
Dataflow is a serverless data processing platform that autoscales for both batch and streaming workloads. Developers write pipelines once in Python or Java using the Apache Beam SDK, and Dataflow runs the same code for streaming events and batch collections. Data flows from sources such as relational databases through Beam transforms in Dataflow to BigQuery as the sink.
Ready-made templates speed up standard movement, such as the JDBC to BigQuery template that extracts from Oracle or other relational databases and loads into BigQuery without writing a full pipeline. Choose Dataflow when transformation logic requires custom code and a single codebase must serve both batch and streaming.
Warehouse-native transformation in BigQuery means raw data is loaded first and then transformed inside the warehouse with SQL. This ELT pattern keeps transformation close to the stored data and uses warehouse compute for SQL transformation and concurrent querying, while ETL performs transformation outside the warehouse before loading. Warehouse-native work in BigQuery includes several peer options:
A typical Dataform ELT sequence runs in a fixed order: first, raw data is extracted from source systems; second, raw data is loaded into BigQuery tables; third, Dataform executes SQL queries in BigQuery in order of object dependencies to produce tested, documented tables and views for analysis. Dataform stores SQL workflow code in repositories tracked with Git, supports isolated development workspaces, and connects to providers including GitHub, GitLab, Bitbucket, and Azure DevOps Services.
Exam tip: Cloud Data Fusion and Dataflow transform data outside BigQuery before loading, while Dataform and DML transform data already loaded inside BigQuery.
Orchestration with Managed Service for Apache Airflow coordinates multi-step workflows that span pipelines and systems. The service is fully managed workflow orchestration built on Apache Airflow for authoring, scheduling, and monitoring pipelines across cloud environments and on-premises data centers. Orchestration uses directed acyclic graphs (DAGs), where sources pull from transactional systems and sinks connect to destination systems such as a data warehouse.
A data flow DAG moves data from sources to sinks, while an orchestration or control flow DAG coordinates movement between pipelines and other systems. Its operators can run extract and load steps, ELT transformations through the BigQuery operator, and REST API calls. An Airflow DAG can therefore orchestrate a Dataflow or Cloud Data Fusion job that loads raw data, then trigger a Dataform or BigQuery job that transforms that loaded data inside BigQuery.
Understand the ETL Process means describing how data moves through extract, transform, and load in order. Extract, Transform, and Load (ETL) is a data pipeline procedure that pulls data from source systems, changes it into a clean and ready form outside the warehouse, and then loads it into a data warehouse. In Google Cloud, the pipeline typically starts at transactional sources such as a relational database, performs transformation outside the warehouse, and ends at BigQuery (opens in a new tab) as the sink that stores data for analysis.
The three ordered stages of the ETL pipeline are extract, transform, and load, with data flowing from sources to transformations to the warehouse sink. Arrows in the flow point in the direction data travels, while source steps pull data from the source systems into the pipeline. The sequence is fixed:
The transform stage often includes several kinds of work applied in series before loading, including cleaning data, applying business rules, checking data integrity, and creating aggregates or disaggregates. Pipelines work best with as few source systems as possible because each additional source adds a failure point such as an unavailable system.
Why ETL fits traditional data warehousing is that all heavy preparation work happens outside the warehouse, leaving warehouse resources free for concurrent querying. The source side of the boundary holds transactional systems, the middle holds ETL processing tools, and the destination side holds the data warehouse that serves queries and dashboards. This separation protects query performance when many users query at the same time.
The tradeoff is tooling complexity, because transformation runs outside the warehouse and teams must learn additional tools and languages beyond SQL. ETL pipelines can run as batch pipelines that process data collected over time, such as once per day, or as streaming pipelines that handle real-time events such as change-data-capture row changes from operational databases.
Running ETL with Google Cloud processing services means extracting from on-premises or cloud sources, transforming with managed processing services, and loading the result into BigQuery for warehousing. Dataflow is a serverless data processing platform that autoscales and runs both batch and streaming pipelines from the same code. It executes programs written with Apache Beam and is a good choice for Python and Java developers, including use of the JDBC to BigQuery template to extract from Oracle or other relational databases and load into BigQuery.
Cloud Data Fusion is a managed CDAP service that provides a visual interface with drag-and-drop pipeline development and many plugins. Cloud Data Fusion captures data from many kinds of source systems with batch and streaming replication, uses a BigQuery plugin to load data into BigQuery and handle schema updates, and provides the Wrangle feature for cleaning and preparing data. Managed Service for Apache Airflow is a fully managed workflow orchestration service built on Apache Airflow that authors, schedules, and monitors pipelines across cloud and on-premises data centers using directed acyclic graphs (DAGs).
A data flow DAG connects sources to a warehouse sink, while an orchestration or control flow DAG coordinates movement between pipelines and other systems. Teams that already own an ETL tool can also lift and shift by keeping the tool and changing storage stages to BigQuery, or by installing the vendor tool on Compute Engine or from Google Cloud Marketplace.
Exam tip: ETL transforms data outside the data warehouse so warehouse compute stays available for querying, but the team must use transformation tooling beyond SQL.
Extract, Transform, and Load (ETL) transforms data outside the warehouse before loading, Extract, Load, and Transform (ELT) loads raw data into BigQuery first and transforms it inside BigQuery, and Extract, Transform, Load, and Transform (ETLT) does a light transform before loading plus a fuller transform after loading. ETL keeps warehouse resources available for concurrent querying, ELT preserves raw data and uses BigQuery scale for transformation, and ETLT fits migrations and complex sources that need early cleanup with final modeling in the warehouse.
Choose Extract, Transform, Load, and Transform (ETLT) when sources need early cleanup or replication handling but final modeling belongs in the warehouse. Choose Extract, Transform, and Load (ETL) when warehouse compute must stay free for queries or data must be cleaned and aggregated before landing, and choose Extract, Load, and Transform (ELT) when raw data should be preserved and transformed with scalable SQL, testing, and version control.
Cloud Data Fusion and Dataflow perform compute-based transformation outside the warehouse, while BigQuery performs warehouse-native transformation inside the warehouse using SQL. Cloud Data Fusion provides visual drag-and-drop pipelines with Wrangle and BigQuery plugins, Dataflow runs custom Python or Java Apache Beam code for batch and streaming with autoscaling, and BigQuery transformations include Dataform declarative SQL workflows and Data manipulation language (DML) statements, while Managed Service for Apache Airflow orchestrates these steps with directed acyclic graphs (DAGs).
A data analytics team is designing a data ingestion pipeline on Google Cloud. The team has established the following operational requirements:
Which data pipeline methodology does this architecture represent?
Last updated