Data Engineering ETL and ELT Pipelines Questions and Answers

Question 1

A retail company wants to migrate its data analytics platform to a modern cloud data warehouse like Google BigQuery.
The company deals with high volumes of semi-structured data from various sources (e.g., weblogs, social media, transactions) and wants to provide its data science team with the flexibility to explore the raw data.
Which approach is most suitable for this scenario?

Accepted Answer

ELT, because it leverages the scalable compute power of the cloud data warehouse to transform data after loading, providing flexibility with raw data.

Answer

ETL, because it ensures data is structured and clean before loading, which is better for traditional BI reporting.

Answer

ETL, because it is a more mature process with better-established tools for data governance and security.

Answer

ELT, because it requires less storage in the target warehouse since transformations are done in-memory.

Question 2

What is the primary architectural difference in where the 'Transform' step is executed between ETL and ELT pipelines?

Accepted Answer

In ETL, transformations occur on a separate, dedicated processing server or staging area, while in ELT, they occur within the target data warehouse itself.

Answer

In ETL, transformations occur on the source system, while in ELT, they occur on the target system.

Answer

In ETL, transformations are performed after loading the data, while in ELT, they are performed before loading.

Answer

In ETL, transformations are coded in Python, while in ELT, they must be coded in SQL.

Question 3

Which of the following is a primary advantage of using a traditional ETL process, particularly in industries with strict data privacy regulations like healthcare or finance?

Accepted Answer

The capability to cleanse, mask, or anonymize sensitive data before it is loaded into the target data warehouse.

Answer

Faster data ingestion speeds compared to ELT.

Answer

The ability to handle unstructured data more effectively.

Answer

Lower initial setup costs due to not needing a separate transformation server.

Question 4

A financial institution is building a real-time fraud detection system. The system needs to process a continuous stream of transaction events, enrich them with customer data, and apply a detection model, all within milliseconds. Which data processing approach is most appropriate?

Accepted Answer

A streaming ETL process that transforms and enriches data in-flight as it is generated.

Answer

A batch ELT process that loads raw transaction data and transforms it nightly.

Answer

A micro-batch ETL process that collects data for five minutes before processing.

Answer

A daily batch ETL process to ensure high data quality through complex transformations.

Question 5

In an ELT pipeline, which component's processing power is most critical for the performance of the data transformation tasks?

Accepted Answer

The target data warehouse or data lake.

Answer

The source database server.

Answer

The data ingestion tool or service.

Answer

A dedicated middleware transformation engine.

Question 6

Which of the following scenarios is LEAST suitable for an ELT pipeline and would be better served by a traditional ETL approach?

Accepted Answer

A company with a small, on-premises legacy database with limited computational power that needs to integrate highly structured data for daily reporting.

Answer

An organization building a data lake on a cloud platform to store massive volumes of diverse, raw data for future analysis.

Answer

A data science team that needs the flexibility to run novel transformations on raw historical data stored in a powerful cloud data warehouse.

Answer

A startup that needs to ingest data from dozens of SaaS applications quickly to provide immediate visibility to business users.