ETL PIPELINES

Which approach allows ETL pipelines to efficiently handle very large datasets without reprocessing unchanged historical data?

• Full batch load

• Incremental load using Change Data Capture (CDC)

• Streaming load without staging

• Manual data reconciliation

In ELT pipelines on cloud platforms like Snowflake or BigQuery, performing transformations outside the warehouse (in an external ETL server) is always more efficient than leveraging the warehouse compute.

• True

• False

In streaming ETL pipelines, using watermarks guarantees 100% accuracy for out-of-order events without any data loss.

• True

• False

A _____________ pattern in ETL pipelines processes both batch historical data and streaming real-time data in separate layers, combining results for analysis.

In incremental ETL pipelines, ____________ is a mechanism that allows capturing only inserted, updated, or deleted rows since the last ETL run.

In a cloud ELT pipeline, which of the following provides the best performance for transforming petabytes of data?

• Single-threaded ETL scripts

• Using the compute clusters of the Data Warehouse (distributed processing)

• Extracting data to local servers for transformation

• Only transforming data after exporting to CSV

What is the main advantage of using a “staging layer” in complex ETL pipelines?

• To skip data validation

• To directly serve analytical dashboards from raw data

• To allow debugging, auditing, and applying transformations without impacting the final warehouse

• To reduce the need for orchestration

In a Big Data streaming ETL pipeline, which technique allows handling late-arriving and out-of-order events efficiently?

• Ignoring late events

• Transforming data before ingestion

• Only batch processing

• Windowing with watermarks and buffering

In a Kappa Architecture ETL pipeline, what is the primary difference from Lambda Architecture?

• Kappa has separate batch and streaming layers

• Kappa cannot handle late-arriving data

• Kappa is limited to relational databases

• Kappa uses only a single streaming pipeline for data

Scenario:
Your organization is implementing a multi-tenant Data Warehouse in the cloud. Each tenant generates high-volume transactions, and the data must be segregated, auditable, and queryable in near real-time. You also need to handle schema evolution, data lineage, and historical versioning (Slowly Changing Dimensions type 2).

Question:
Which architectural design and pipeline practices would best satisfy these constraints?

• Single schema for all tenants, batch ELT nightly, and manual tracking of historical changes.

• Separate schemas per tenant, staging + transformation layers, CDC for incremental updates, SCD Type 2 for historical tracking, and automated lineage and quality checks.

• Use a single data lake for all tenants, no staging, and query data directly via SQL-on-Hadoop without transformation.

• Transform all data in-memory before loading to the warehouse, skip historical tracking, and store only current state.