Question 1

Explain end-to-end pipeline design for large-scale batch and streaming.

Accepted Answer

Batch pipeline : Streaming pipeline : Key design decisions : Bronze/Silver/Gold (Medallion) : separate raw, cleansed, and business ready layers Incremental loading : only process new data using watermarks or CDC Partitioning : partition Delta tables by date for efficient pruning Orchestration : ADF pipelines or Databricks Workflows for scheduling and dependency management Monitoring : Azure Monitor + Log Analytics for job health; Data Factory monitoring for pipeline runs

Question 2

How do you build fault-tolerant ETL systems in Azure?

Accepted Answer

ADF pipeline retries : configure retry count and interval on each activity (typically 3 retries, 2 minute delay) Idempotent writes : use Delta Lake MERGE INTO so re runs don't create duplicates Checkpointing : in Spark Streaming, write Kafka offsets to ADLS after each micro batch Dead letter pattern : route malformed records to a quarantine Delta table instead of failing the pipeline Activity level error handling : in ADF, use dependsOn with Failed condition to route errors to notification activities Key Vault secrets : never hardcode credentials — use Managed Identity + Key Vault references Alerting : Azure Monitor alerts on pipeline failure → Logic App → email/Teams notification Validation gates : row count comparison between source and target before marking run as successful

Question 3

Design a data warehouse using Synapse Analytics.

Accepted Answer

Architecture : Synapse SQL Pool (dedicated): star schema for BI workloads Synapse Spark Pool : heavy transformations, ML feature engineering ADLS Gen2 : underlying storage for both pools Synapse Link : near real time sync from Cosmos DB or Dataverse Schema design : Optimization : use hash distribution on the most joined key; replicate small dimension tables; use RESULT SET CACHING for repeated expensive queries.

Question 4

Write performant Spark code for data transformations.

Accepted Answer

Key rules : prefer built in functions over Python UDFs (10 100x faster), push filters early, repartition on join keys before large joins.

Question 5

Discuss data partitioning and indexing strategies in large tables.

Accepted Answer

Partitioning : Range partition : by date — most common. WHERE date = '2024 01 01' skips older partitions entirely Hash partition : by user id — even distribution for skew free parallel processing List partition : by region/country — useful for data residency requirements Target partition size : 128 MB–1 GB per file in Spark/Delta Delta Lake file skipping : OPTIMIZE + ZORDER BY (customer id, date) : co locate related data → fewer files read per query Bloom filters on high cardinality string columns (email, product sku) Synapse/Redshift : Clustered Columnstore Index (CCI) : default for fact tables — best compression and scan speed Distribution : HASH on join key for large tables, REPLICATE for small dimensions Sorted indexes : range based queries benefit from sorted columnstore

Question 6

Implement incremental loads in Azure Data Factory.

Accepted Answer

Watermark pattern (most common): In ADF: 1. Lookup activity: read last loaded ts from watermark table 2. Copy activity: source query = SELECT FROM orders WHERE updated at @{activity('Lookup').output.firstRow.last loaded ts} 3. Stored Procedure activity: update watermark with MAX(updated at) from the loaded batch CDC alternative : use ADF's built in Change Tracking (SQL Server) or Debezium → Event Hub → ADF → Delta. Partition based : load only today's partition dt = @{formatDateTime(utcnow(), 'yyyy MM dd')} — simple but requires source to be partitioned by date. Key constraint : source must have an updated at column (or CDC enabled) — timestamp based incremental misses hard deletes.

Question 7

Manage credentials using managed identities vs service principals.

Accepted Answer

Managed Identity Service Principal Credentials No secrets — Azure manages automatically Client ID + secret or certificate Rotation Automatic Manual (or with Key Vault rotation) Scope Azure resources only Any OAuth2 endpoint Use case ADF → ADLS, Databricks → Key Vault App accessing external APIs, cross tenant Best practice : use Managed Identity wherever possible — eliminates credential management overhead. In ADF : assign System assigned Managed Identity to the ADF instance → grant Storage Blob Data Contributor role on ADLS → no connection string needed. When to use Service Principal : multi tenant scenarios, non Azure services, or when you need granular permission control across subscriptions. Never hardcode credentials in pipeline code — always use Key Vault references or Managed Identity.

Question 8

Explain integrating Databricks with ADF for orchestration.

Accepted Answer

Pattern : ADF for scheduling and workflow orchestration; Databricks for heavy Spark computation. Integration steps : 1. Create a Databricks Linked Service in ADF (using Managed Identity or PAT token) 2. Use Databricks Notebook or Databricks Job activity in ADF pipeline 3. Pass parameters from ADF to notebook: dbutils.widgets.get("start date") Architecture : Monitoring : ADF monitors notebook execution; failure propagates up to the pipeline and triggers alerts. Alternative : Databricks Workflows (native job orchestration) can replace ADF for purely Databricks centric pipelines — better cluster reuse, lower latency between tasks.

Microsoft Interview Questions