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

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Deolite Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies