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

+2 More Questions with Expert Answers

Other Companies

Deloitte Data Engineer Interview Questions

+2 More Questions with Expert Answers

Other Companies