Question 1

Explain Fact and Dimension Tables with examples.

Accepted Answer

Architecture: Star schema centralizes measurable events in fact tables; dimensions provide semantic context. Why this design: Facts are append-heavy and grow unbounded; dimensions are smaller and change slowly. Separating them optimizes for different access patterns. Fact grain defines the entire schema—get it wrong and joins become wrong. Example: sales_fact (quantity, revenue, date_key, product_key, customer_key) at grain one row per transaction....

Question 2

Architect incremental load in ADF + Databricks with idempotency, late-arrival handling, and cost/scalability implications of watermark vs. change data capture.

Accepted Answer

**Pattern**: Process only new/changed data by tracking last processed boundary (watermark) or using CDC.

**ADF approach**: Watermark via lookup/stored procedure storing max(modified_date); filter source WHERE modified_date > @lastRun; parameterize pipeline for lastRun; write to sink. Use triggers for scheduling.

**Databricks approach**: Delta Lake MERGE INTO for upserts; Change Data Feed (CDF) for CDC; read CDF or filter on _change_version....

Question 3

ADF Optimization Techniques?

Accepted Answer

Why optimize: ADF costs scale with DIU (Data Integration Units) and runtime; unoptimized pipelines waste budget and miss SLAs. Architectural logic: (1) Mapping data flows run on Spark—use for complex transforms; they scale horizontally. (2) DIU slider—increase for heavy workloads; trade-off: higher DIU = higher cost per hour. (3) Partition sources/sinks—enables parallelism; small partition counts = underutilized clusters....

Question 4

Data Load in Synapse Table?

Accepted Answer

Architectural logic: Synapse is MPP—distribution and indexing dictate load and query performance. COPY INTO is the native bulk path; ADF Copy Data supports it via PolyBase or bulk INSERT. Why COPY INTO: Reads from external stage (Blob/ADLS); parallel load across distributions; minimal log overhead. Scalability: Distribution key choice matters—hash on join keys for fact tables; round-robin for staging. CCI vs HEAP: CCI for analytics (column-store); HEAP for staging or small tables....

Question 5

Error Handling in ADF?

Accepted Answer

Architectural layers: (1) Activity retry with backoff. (2) On failure/On success routing. (3) @activity().Error in expressions. (4) Try-catch via Execute Pipeline. (5) Web Activity → Logic App for notifications. Why: Prevent silent failures; enable alerting and traceability....

Question 6

Parallel Copies in ADF?

Accepted Answer

**Why parallelism**: Single-threaded copy underutilizes source, sink, and network. parallelCopies controls concurrent read operations in a Copy activity. **Architecture**: parallelCopies (default 1, max ~32 on Azure IR) × dataIntegrationUnits (DIU) determines throughput. For partitioned sources (e.g., tables with partition key), set partitionOption to physical partition so ADF splits work. For file sources, increase parallelCopies; for database, partition by key....

Question 7

Synapse Analytics Features and Use Cases?

Accepted Answer

**Why Synapse**: Unified analytics—data lake (ADLS Gen2), SQL, Spark, and orchestration in one service. Reduces integration complexity. **Features**: Dedicated SQL pool (MPP, previous SQL DW)—for heavy analytical workloads, predictable performance. Serverless SQL pool—pay-per-query on ADLS, no infra to manage. Spark pools—for data engineering, ML. Pipelines—ADF-based orchestration....

Question 8

Notebook Optimization Strategies?

Accepted Answer

(1) Restart kernel to avoid stale state. (2) Limit data—sample, filter early, lazy eval. (3) Cache intermediate with .cache() or @lru_cache. (4) Profile with %%time, %%memit, Spark UI. (5) del/gc.collect() to free memory. (6) Spark/Polars for big data. (7) Parameterize (Papermill, widgets) for automation. (8) Refactor to production scripts. WHY: Notebooks are for exploration; production needs reproducibility and scheduling. COST: Large retained objects increase cluster cost; cache wisely....

Deloitte Data Engineer Interview Questions

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

Other Companies

Deloitte Data Engineer Interview Questions

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

Other Companies