wayground logo

Free Printable Worksheets

Font size

S
M
L
XL
Worksheets

Advanced Data Engineer Assessment

Total questions: 106

Worksheet time: 53mins

Name
Class
Date
1.

What is the purpose of WITH RECURSIVE in SQL?

a)

Caches query results

b)

Creates writable temporary tables

c)

Enables iterative queries for trees or graphs

d)

Partitions large tables

2.

When should EXISTS be preferred over IN in a subquery?

a)

Small subtables

b)

Duplicate-heavy parent tables

c)

Existence checks for performance

d)

Multiple OR conditions

3.

When is a bitmap index most effective?

a)

High-cardinality columns

b)

Low-cardinality columns

c)

Text-heavy columns

d)

Multi-join queries

4.

What does ANALYZE do in PostgreSQL?

a)

Collects query optimization statistics

b)

Deletes duplicates

c)

Accelerates writes

d)

Partitions tables

5.

What is a MATERIALIZED VIEW in PostgreSQL?

a)

Physically stored query results

b)

Auto-updating view

c)

Recursive query support

d)

Temporary table replacement

6.

Which window function accesses the next row’s value?

a)

RANK()

b)

LAG()

c)

LEAD()

d)

NTILE()

7.

What does VACUUM do in PostgreSQL?

a)

Deletes duplicates

b)

Reclaims space and updates statistics

c)

Speeds up writes

d)

Creates indexes

8.

When is PARTITION BY used in window functions?

a)

Groups data without reducing rows

b)

Joins tables

c)

Removes duplicates

d)

Physically partitions tables

9.

In SQL Server, what does INCLUDE in an index achieve?

a)

Accelerates writes

b)

Stores extra columns in the index

c)

Partitions tables

d)

Updates statistics

10.

How can you reduce GROUP BY query cost?

a)

Use HAVING instead of WHERE

b)

Index GROUP BY columns and filter with WHERE

c)

Use DISTINCT

d)

Increase cache

11.

In PySpark, what does persist(StorageLevel.DISK_ONLY) do?

a)

Stores in memory

b)

Stores on disk if memory is low

c)

Stores entirely on disk

d)

Chooses memory or disk

12.

What is the difference between repartition() and coalesce() in PySpark?

a)

Both reduce partitions

b)

repartition() shuffles, coalesce() does not

c)

coalesce() adds partitions

d)

repartition() is for narrow transformations

13.

When is a broadcast join useful in Spark?

a)

Both tables are large

b)

Small table can be broadcast

c)

Outer joins

d)

Pre-partitioned data

14.

What distinguishes RDDs from DataFrames in Spark?

a)

RDDs lack schema, DataFrames have schema

b)

RDDs are always faster

c)

DataFrames lack transformations

d)

RDDs cannot be cached

15.

What does cache() do in PySpark?

a)

Stores data in memory

b)

Stores on disk

c)

Removes duplicates

d)

Adds partitions

16.

What is the purpose of explode() in PySpark?

a)

Splits array column into rows

b)

Compresses data

c)

Removes nulls

d)

Creates indexes

17.

How can you process a large CSV in Pandas?

a)

Use chunksize in pd.read_csv

b)

Increase RAM

c)

Use NumPy

d)

Use groupBy()

18.

What does spark.sql.autoBroadcastJoinThreshold do?

a)

Automates broadcast joins

b)

Increases partitions

c)

Disables broadcast joins

d)

Processes text

19.

What is the Spark UI used for in PySpark?

a)

Monitors execution and performance

b)

Counts rows

c)

Shows schema

d)

Lists nulls

20.

How can you process streaming data in PySpark?

a)

Use spark.readStream and writeStream

b)

Use spark.read and write

c)

Use RDDs

d)

Use groupBy()

21.

In Airflow, what is a task instance?

a)

DAG definition

b)

Specific task execution

c)

Task group by date

d)

Python code line

22.

What does dbt run do?

a)

Deletes data

b)

Executes all models

c)

Checks errors

d)

Builds documentation

23.

How does Luigi differ from Airflow?

a)

No UI

b)

No DAGs

c)

Task dependency focus

d)

Python 2 only

24.

Why is ELT preferred over ETL?

a)

ETL scales better

b)

ELT uses Data Warehouse compute

c)

ELT skips logging

d)

ETL fails with semi-structured data

25.

How do you handle a failed task in Airflow?

a)

Set retries and retry_delay

b)

Add executors

c)

Use Luigi

d)

Delete DAG

26.

What is an incremental model in dbt?

a)

Processes all data

b)

Processes new/changed data

c)

Deletes old data

d)

Creates documentation

27.

What is XCom in Airflow?

a)

Passes data between tasks

b)

Stores data on disk

c)

Speeds tasks

d)

Documents DAGs

28.

What does ref() do in dbt?

a)

References models

b)

Creates indexes

c)

Speeds queries

d)

Removes duplicates

29.

What is a Sensor in Airflow?

a)

Checks conditions

b)

Speeds tasks

c)

Documents DAGs

d)

Removes duplicates

30.

What is a snapshot in dbt?

a)

Tracks data history

b)

Speeds queries

c)

Creates indexes

d)

Deletes duplicates

31.

What is the focus of the Kimball model?

a)

Star schemas for analytics

b)

Data Lakes for raw data

c)

No-transformation integration

d)

Streaming pipelines

32.

How does Inmon differ from Kimball?

a)

3NF vs. star schemas

b)

No query support

c)

Semi-structured data

d)

Data Lake focus

33.

What does a fact table hold in Kimball?

a)

Metrics and dimension keys

b)

Raw data

c)

Change history

d)

Query indexes

34.

What is a hub in Data Vault?

a)

Business keys

b)

Raw data

c)

Star schemas

d)

Query speed

35.

What is a star schema’s benefit in Kimball?

a)

Fast analytics

b)

Semi-structured support

c)

Multi-source integration

d)

History tracking

36.

What is a conformed dimension in Kimball?

a)

Shared across fact tables

b)

Raw data

c)

Single fact table

d)

History storage

37.

What is a satellite in Data Vault?

a)

Attributes and history

b)

Business keys

c)

Star schemas

d)

Query speed

38.

When is Data Vault most suitable?

a)

Multi-source integration with history

b)

Fast star schema queries

c)

Streaming data

d)

Duplicate removal

39.

What is Trino best for?

a)

Multi-source analytics

b)

Streaming

c)

ETL pipelines

d)

Raw storage

40.

When is DuckDB suitable?

a)

Single-machine moderate data

b)

Multi-node Big Data

c)

Streaming

d)

Multi-source integration

41.

What feature does Delta Lake provide?

a)

ACID and schema enforcement

b)

Auto-partitioning

c)

Query speed

d)

Duplicate removal

42.

What file format does DuckDB prefer?

a)

Parquet

b)

JSON

c)

CSV

d)

Avro

43.

What is Apache Iceberg’s key feature?

a)

Schema evolution and time travel

b)

Faster queries

c)

Streaming

d)

Lower storage

44.

What is time travel in Delta Lake?

a)

Historical data queries

b)

Query speed

c)

Streaming

d)

Duplicate removal

45.

What is a catalog in Trino?

a)

Connects data sources

b)

Speeds queries

c)

Creates pipelines

d)

Removes duplicates

46.

What does a Lakehouse combine?

a)

Raw storage and analytics

b)

Raw data only

c)

Query speed only

d)

Streaming only

47.

What is a metastore’s role in a Lakehouse?

a)

Manages metadata

b)

Speeds queries

c)

Stores raw data

d)

Creates streams

48.

How do you secure on-premise data?

a)

Encryption and IAM

b)

More partitions

c)

Disable caching

d)

Raw data

49.

What optimizes Spark jobs in Databricks?

a)

AQE and Delta Cache

b)

More partitions

c)

Pandas

d)

groupBy()

50.

What is Unity Catalog in Databricks?

a)

Metadata and access management

b)

Query speed

c)

Streaming

d)

Duplicate removal

51.

How do you optimize a query with multiple JOINs on a billion-row table?

a)

Index all columns

b)

Use EXPLAIN, partition tables, and add selective indexes

c)

Convert to UNION

d)

Increase cache

52.

How do you handle data skew in a partitioned table query?

a)

Increase partitions

b)

Use DISTRIBUTE BY for redistribution

c)

Drop indexes

d)

Increase cache

53.

In PostgreSQL, how does CLUSTER affect a frequently updated table?

a)

Improves reads but slows writes

b)

Speeds writes

c)

Removes duplicates

d)

Refreshes statistics

54.

How do you optimize a query with multiple OR conditions?

a)

Rewrite as UNION for indexing

b)

Increase cache

c)

Use IN

d)

Drop indexes

55.

In Oracle, how does PARALLEL hint impact complex joins?

a)

Enables parallel execution with I/O risks

b)

Reduces records

c)

Disables indexes

d)

Partitions tables

56.

How does PARALLEL hint impact complex joins?

a)

Enables parallel execution with I/O risks

b)

Reduces records

c)

Disables indexes

d)

Partitions tables

57.

How do you optimize MERGE for millions of daily updates?

a)

Index primary key

b)

Partition and index WHEN MATCHED columns, batch updates

c)

Disable indexes

d)

Use temporary table

58.

When does INDEX SKIP SCAN improve performance?

a)

First index column unused, later columns selective

b)

Dropping index

c)

Small tables

d)

GROUP BY

59.

How do you fix a PostgreSQL query plan ignoring an index?

a)

Use ANALYZE and tune cost

b)

Drop and recreate index

c)

Increase timeout

d)

Use non-indexed table

60.

How do you optimize a partitioned table with poor pruning?

a)

Include partition key filters, check with EXPLAIN

b)

Drop partitions

c)

Increase partitions

d)

Use non-partitioned table

61.

In MySQL, when is FORCE INDEX used, and what are risks?

a)

Suboptimal index choice, risks outdated plans

b)

Dropping index

c)

Small tables

d)

GROUP BY analysis

62.

How do you mitigate data skew in a PySpark job with billions of rows?

a)

Use repartition() and salting

b)

Add executors

c)

Disable caching

d)

Replace join with groupBy

63.

How do you optimize a PySpark job with excessive shuffling?

a)

Use broadcast joins, tune spark.sql.shuffle.partitions

b)

Increase memory

c)

Disable caching

d)

Use RDDs

64.

How do you handle OutOfMemoryError in a terabyte-scale Spark job?

a)

Tune spark.executor.memory, partition size, spill-to-disk

b)

Disable caching

c)

Use Pandas

d)

Use groupBy()

65.

How do you process complex nested JSON in PySpark?

a)

Use spark.read.json(), explode(), schema inference

b)

Convert to RDD

c)

Use groupBy()

d)

Ignore nested fields

66.

How do you implement SCD Type 2 in PySpark with Delta Lake?

a)

Use MERGE for is_current, end_date

b)

Use groupBy()

c)

Use Pandas

d)

Delete old data

67.

How do you detect and fix data skew in PySpark?

a)

Check df.rdd.getNumPartitions(), use repartition() or salting

b)

Use df.count()

c)

Add executors

d)

Use groupBy()

68.

How does ZORDER improve PySpark query performance?

a)

Clusters data to reduce I/O

b)

Removes duplicates

c)

Adds partitions

d)

Disables caching

69.

How do you optimize Dask for a 100GB dataset?

a)

Use dask.dataframe, optimize partitioning

b)

Use Pandas

c)

Increase RAM

d)

Use NumPy

70.

How do you handle streaming with Spark Structured Streaming and Delta Lake?

a)

Use writeStream, merge, checkpointing

b)

Use spark.read

c)

Use RDDs

d)

Use groupBy()

71.

How do you tune a Spark job for low-latency on a 1TB dataset?

a)

Use ZORDER, tune spark.sql.shuffle.partitions, enable AQE

b)

Add nodes

c)

Use Pandas

d)

Disable caching

72.

How do you optimize a 50+ task Airflow DAG?

a)

Limit parallelism, use LocalExecutor, SubDAGs

b)

Add workers

c)

Use dbt

d)

Disable tasks

73.

How do you ensure idempotency in an ELT pipeline?

a)

Use MERGE or upsert with unique keys

b)

Add partitions

c)

Disable caching

d)

Use groupBy()

74.

How do you handle schema evolution in dbt?

a)

Use on_schema_change with incremental models

b)

Use groupBy()

c)

Add partitions

d)

Use Airflow

75.

How do you troubleshoot a failing Airflow pipeline?

a)

Analyze UI, logs, dependencies

b)

Use dbt test

c)

Add executors

d)

Use Luigi

76.

How do you manage complex dependencies in Luigi?

a)

Use requires() with dynamic scheduling

b)

Use SubDAGs

c)

Add workers

d)

Use Airflow

77.

How do you integrate Airflow with Databricks for streaming?

a)

Use DatabricksSubmitRunOperator, Delta Live Tables

b)

Use dbt run

c)

Add partitions

d)

Use Luigi

78.

How do you ensure data quality in a dbt pipeline?

a)

Custom tests with dbt test, schema validation

b)

Use dbt run

c)

Use Airflow

d)

Use groupBy()

79.

How do you rerun a failed Luigi pipeline?

a)

Use --force

b)

Add workers

c)

Use Airflow

d)

Delete pipeline

80.

How do you handle streaming in an ELT pipeline with Airflow?

a)

Integrate with Spark Streaming or Kafka

b)

Use Airflow directly

c)

Use dbt

d)

Use SubDAGs

81.

How do you scale Airflow for 1000+ daily tasks?

a)

Use CeleryExecutor, distributed workers, DB tuning

b)

Increase tasks

c)

Use Luigi

d)

Disable DAGs

82.

How do you implement SCD Type 2 in Data Vault?

a)

Use satellites with load_date, end_date

b)

Use hubs

c)

Use links

d)

Delete data

83.

How does snowflake schema differ from star schema in Kimball?

a)

Normalizes dimensions

b)

Supports semi-structured data

c)

Uses 3NF

d)

Slows queries

84.

What is Data Vault’s advantage over Inmon?

a)

Scalability, history tracking

b)

Faster queries

c)

Streaming

d)

Lower storage

85.

How do you design a Data Vault for frequent schema changes?

a)

Flexible satellites, dynamic hubs

b)

Star schema

c)

Inmon

d)

Delete models

86.

How do you optimize query performance in a Kimball star schema?

a)

Denormalized tables, indexes, materialized views

b)

Normalize dimensions

c)

Use Data Vault

d)

Add partitions

87.

When is Inmon preferred over Kimball?

a)

Enterprise 3NF integration

b)

Fast star schema queries

c)

Streaming

d)

Data Lakes

88.

How do you handle complex relationships in Data Vault?

a)

Use links for many-to-many

b)

Use satellites

c)

Create star schemas

d)

Speed queries

89.

When is Data Vault preferred over Kimball?

a)

Multi-source data with history

b)

Fast star schema queries

c)

Streaming

d)

Lower storage

90.

How does Iceberg differ from Delta Lake?

a)

Open format, less engine dependency

b)

No ACID

c)

No time travel

d)

Streaming only

91.

How do you optimize Trino for petabyte-scale data?

a)

Partition predicates, dynamic partitioning

b)

Add nodes

c)

Use Spark

d)

Use groupBy()

92.

How do you optimize DuckDB for a 500GB dataset?

a)

Use Parquet, query optimization

b)

Use Snowflake

c)

Add nodes

d)

Use Spark

93.

How does Iceberg improve query performance?

a)

Data skipping, hidden partitioning

b)

More nodes

c)

Disable caching

d)

Use Spark

94.

How do you integrate DuckDB with Delta Lake?

a)

Query Delta as Parquet with DuckDB

b)

Use DuckDB for ETL

c)

Use Spark

d)

Add nodes

95.

How do you handle schema evolution in Iceberg?

a)

Use versioning, metadata updates

b)

Delete data

c)

Use Delta

d)

Add partitions

96.

How do you optimize ZORDER in Delta Lake for a 10TB table?

a)

Use high-selectivity columns, optimize partitioning

b)

Remove duplicates

c)

Add partitions

d)

Merge data

97.

How do you synchronize data in a hybrid Lakehouse?

a)

Use Kafka or Delta Streaming

b)

Add on-premise nodes

c)

Move to cloud

d)

Use groupBy()

98.

How do you optimize storage in a hybrid Lakehouse?

a)

Use Parquet, tiered storage

b)

Add nodes

c)

Use Pandas

d)

Use groupBy()

99.

What is a challenge in a hybrid Lakehouse?

a)

Metadata sync and performance

b)

No SQL support

c)

No open-source tools

d)

No Airflow integration

100.

How do you ensure portability in a hybrid Lakehouse?

a)

Use Iceberg or Delta Lake

b)

Add executors

c)

Use dbt

d)

Use Pandas

101.

How do you monitor a hybrid Lakehouse pipeline?

a)

Use Airflow UI, Databricks monitoring

b)

Use dbt test

c)

Add nodes

d)

Use Luigi

102.

How do you handle data skew in Databricks?

a)

Use AQE, repartition()

b)

Add nodes

c)

Use RDBMS

d)

Use groupBy()

103.

How does AQE improve Spark performance?

a)

Adjusts plans with runtime stats

b)

Adds partitions

c)

Disables caching

d)

Uses Pandas

104.

How do you integrate Databricks with Airflow?

a)

Use DatabricksSubmitRunOperator

b)

Use dbt run

c)

Add partitions

d)

Use Luigi

105.

How do you optimize Delta Lake queries in Databricks?

a)

Use ZORDER, partitioning

b)

Use RDBMS

c)

Add nodes

d)

Use groupBy()

106.

How do you build an ML pipeline in Databricks?

a)

Use MLflow, Spark MLlib

b)

Use Pandas

c)

Add executors

d)

Use dbt run

Similar Resources on Wayground

Instrument đầu

103 questions

Instrument đầu

quiz

Worksheet

•

Professional Development

Reciprocating Engines Worksheet Questions

107 questions

Reciprocating Engines Worksheet Questions

quiz

Worksheet

•

Professional Development

Principles of Medical Laboratory Science

110 questions

Principles of Medical Laboratory Science

quiz

Worksheet

•

University - Professi...

UAS Geologi Geotermal

108 questions

UAS Geologi Geotermal

quiz

Worksheet

•

12th Grade - Professi...

BIOL-112-Principles of Biology II- Chapters 8, 9, & 10: TYU

106 questions

BIOL-112-Principles of Biology II- Chapters 8, 9, & 10: TYU

quiz

Worksheet

•

6th Grade - Professio...