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....

Question 9

Where to store secret keys?

Accepted Answer

**Why dedicated secret managers**: AWS Secrets Manager, GCP Secret Manager, Azure Key Vault, HashiCorp Vault—encrypted, audited, rotated. **Never**: In code, config files in git, or .env in production. **Runtime injection**: Environment variables, mounted files, workload identity. **For pipelines**: Airflow Connections, Kubernetes Secrets, or IAM roles (prefer no keys when possible). **Scalability**: Centralized secrets scale with least-privilege; rotation without redeploy....

Question 10

How to check Spark version?

Accepted Answer

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

Check Spark version: In PySpark: `spark.version` or `spark.sparkContext.version`. Scala: `org.apache.spark.SparkContext.SPARK_VERSION`. Shell: `spark-submit --version` or `pyspark --version`. Databricks: Runtime version in cluster config (e.g., 13.3 LTS = Spark 3.4)....

Deloitte Data Engineer Interview Questions

Companies asking these questions

All 10 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Deloitte Data Engineer Interview Questions

Companies asking these questions

All 10 Questions

More Interview Prep Guides

Get All Answers in PDF Format