Question 1

Explain GetMetadata, ForEach, and Copy Data in Azure Data Factory.

Accepted Answer

Architectural logic: GetMetadata lists files/folders; ForEach iterates; Copy Data moves data. Flow: GetMetadata path → childItems → ForEach items → Copy Data per item. Why: Dynamic file iteration; per-file control. Scalability: ForEach batchCount for parallelism; Copy parallelCopies....

Question 2

Fetch the rows with the highest scores for each student in a year.

Accepted Answer

**Solution (Window)**: ROW_NUMBER() OVER (PARTITION BY student_id, year ORDER BY score DESC); filter rn=1. Handles ties (picks one).

**Alternative**: MAX with GROUP BY + JOIN if only max score needed. RANK/DENSE_RANK for different tie behavior....

Question 3

Find the names of managers who have at least 7 employees directly reporting to them.

Accepted Answer

**SQL**: JOIN employees e ON e.manager_id = m.employee_id; GROUP BY m.employee_id; HAVING COUNT(*) >= 7. Or subquery with manager_id. Index manager_id. 'Direct' = one level; multi-level needs recursive CTE.

Question 4

Aggregate surface areas and calculate cumulative surface area using the LAG function.

Accepted Answer

**LAG:** Previous row. LAG(surface_area, 1, 0) OVER (ORDER BY id). **Cumulative:** SUM(surface_area) OVER (ORDER BY id) or ROWS UNBOUNDED PRECEDING.

SELECT id, surface_area,
       SUM(surface_area) OVER (ORDER BY id) AS cumulative_area,
       surface_area + LAG(surface_area, 1, 0) OVER (ORDER BY id) AS prev_plus_curr
FROM surfaces;

Question 5

Find each student's previous year's scores using the LAG function.

Accepted Answer

SELECT student_id, year, score, LAG(score) OVER (PARTITION BY student_id ORDER BY year) AS prev_year_score FROM student_scores. **LAG(score, 2)** for two years back. **Default**: First row has NULL prev. **COALESCE** for default....

Question 6

How would you optimize a slow-running notebook in Databricks?

Accepted Answer

**Why it matters**: At scale, design choices directly impact reliability, latency, and cost. Wrong decisions compound across jobs and teams.

Optimize slow Databricks notebook: (1) Cluster—right-size, enable autoscaling. (2) Data—use Delta/Parquet; filter early; avoid full scans. (3) Caching—cache reused DataFrames. (4) Parallelism—increase partitions. (5) Code—remove unnecessary cells; combine operations. (6) Use Photon if available....

Deolite Data Engineer Interview Questions

+3 More Questions with Expert Answers

Other Companies

Deolite Data Engineer Interview Questions

+3 More Questions with Expert Answers

Other Companies