Data Engineer

Task #2 Write a query that outputs the TOP-3 employees by salary in each department. Display the department name, employee name, and salary. employee table: | id | name | salary | dept_id | |----|-------|--------|---------| | 10 | John | 1000 | 2 | | 20 | Tom | 5000 | 3 | | 30 | Bill | 3000 | 2 | department table: | id | name | |----|-----------| | 1 | Marketing | | 2 | IT | | 3 | Finance | Output: department_name, num, employee_name, salary with cte as( select d.name as department_name, e.name as employee_name, e.salary, dense_rank from employee e join department d on e.id = d.id

Middle

Островок

What is the difference between ACID and the CAP theorem?

Middle

Островок

What is Adaptive Query Execution (AQE)?

Middle

Островок

Final result: | customer_id | gap_days | |-------------|----------| | 1 | 123 | | 2 | 120 | with cte as( select order_id, customer_id, order_dt, lag(order_dt) over(partition by customer_id order by order_dt) as prev_order_ft, datediff(day, prev_order_ft, order_dt) as gap_days from Orders ) select customer_id, max(gap_days) as gap_days from cte where gap_days > 60 group by customer_id

Middle

Островок

What is the danger of shuffle in Spark?

Middle

Островок

What are the stages of a transaction?

Middle

Островок

Have you ever worked with decorators in Python? What are they and where are they used?

Middle

Островок

In what situation can dictionary search degrade to the worst case?

Middle

Островок

Tell me about mutable and immutable data types in Python and provide examples.

Middle

Островок

Have you worked with parameters and hints that determine the use of Broadcast Join?

Middle

Островок

How do you usually handle errors and alerts in pipelines?

Middle

Островок

How to avoid running about 700 DAG runs simultaneously during catchup/backfill in Airflow?

Middle

Островок

Tell me more about the network: what is it and why is it important?

Middle

Островок

Explain lazy and immediate computations in Spark.

Middle

Островок

Task #1 What number of records will a query return with inner, left, right, full join of two tables based on the attribute id t1 t2 | id | | id | |-----| |-----| | 1 | | 1 | | 2 | | 1 | | 3 | | 3 | | 0 | | 5 | | NULL| | NULL| | NULL| inner: left: right: full outer:

Middle

Островок

What are the risks of data quality?

Middle

Островок

What do you know about serialization and deserialization in the context of Spark/UDF?

Middle

Островок

How do you distinguish between technical and business data checks, and who should set the requirements for them?

Middle

Островок

What physical strategies of join execution do you know?

Middle

Островок

What is the difference between repartition and coalesce in Spark?

Middle

Островок