Question 1

Explain the purpose of SparkContext and SparkSession.

Accepted Answer

SparkContext and SparkSession are the two primary entry points into Apache Spark, each representing different generations of the Spark API. SparkContext (Spark 1.x era) The original entry point to Spark functionality Represents a connection to a Spark cluster and is used to create RDDs (Resilient Distributed Datasets) One SparkContext per JVM process Used for low level RDD operations SparkSession (Spark 2.0+, recommended) Unified entry point that encompasses SparkContext, SQLContext, HiveContext, and StreamingContext Provides the high level DataFrame and Dataset APIs Automatically creates or reuses an existing SparkContext under the hood Supports SQL queries, catalog access, and Hive integration Multiple SparkSessions can coexist in one application (different configs, same SparkContext) Key relationship: SparkSession wraps SparkContext — accessing spark.sparkContext gives you the underlying SC Use SparkSession for all modern Spark work (DataFrames, SQL, Structured Streaming) Use SparkContext directly only for RDD level operations or legacy code In Databricks: A spark SparkSession is pre created in every notebook — no manual initialization needed.

Question 2

How do you handle incremental load in PySpark when the table lacks a last_modified column?

Accepted Answer

When a source table has no last modified or updated at timestamp, standard watermark based incremental loads are not possible. Alternative strategies: 1. Row Hash Comparison (detect any column change) 2. Database CDC / Change Data Capture Use source database CDC features (SQL Server CDC, Oracle LogMiner, Debezium) Stream change events via Kafka into Spark Structured Streaming No modification to source table schema required 3. Full Load with Upsert (SCD Type 1) 4. Partition Based Incremental Load If data is naturally partitioned by date in the source (even without an explicit column) Load only today's or this week's partition 5. Sequence ID / Auto Increment Key Watermark If the table has an auto increment id , use MAX(id) as a watermark proxy Recommended approach: Row hashing + Delta MERGE for tables with stable primary keys; CDC for high volume tables with frequent changes.

Question 3

How do you use Azure Stream Analytics for real-time data processing?

Accepted Answer

Azure Stream Analytics (ASA) is a fully managed, serverless real time analytics service that processes streaming data using a SQL like query language. Architecture: Creating a Stream Analytics Job: 1. Create a Stream Analytics job in Azure portal 2. Add Input (e.g., Event Hub with JSON serialization) 3. Write query in Stream Analytics Query Language (SAQL) 4. Add Output (e.g., ADLS Gen2, Power BI) 5. Start the job Example queries: Tumbling Window — aggregate every 5 minutes: Sliding Window — alert on threshold: Joining streams with reference data: Key features: Built in ML functions : AnomalyDetection SpikeAndDip, AnomalyDetection ChangePoint Late arrival handling : TIMESTAMP BY with configurable tolerance Exactly once processing for certain output connectors Auto scaling via Streaming Units (SU) Parallelization : use PARTITION BY to scale across partitions Use cases: IoT sensor monitoring, clickstream analysis, fraud detection, real time dashboards.

Question 4

What are the security features available in ADLS (e.g., Access Control Lists, Role-Based Access Control)?

Accepted Answer

ADLS Gen2 provides a layered security model combining Azure RBAC, POSIX ACLs, network controls, and encryption. 1. Azure Role Based Access Control (RBAC) Coarse grained access control at the storage account or container level Common built in roles: Storage Blob Data Owner — full read/write/delete + ACL management Storage Blob Data Contributor — read/write/delete, cannot change ACLs Storage Blob Data Reader — read only Assigned to users, groups, service principals, or Managed Identities Use RBAC for broad access permissions at the account/container level 2. Access Control Lists (ACLs) — POSIX style ACLs provide fine grained control at the file and directory level: Access ACLs : Control permissions for a specific file/directory Default ACLs : Set on directories and inherited by child files/subdirectories Permission bits: r (read), w (write), x (execute/traverse) Entities: owner, owning group, named user, named group, other, mask 3. Shared Access Signatures (SAS) Time limited, permission scoped tokens for temporary access Service SAS: access to specific container/blob Account SAS: access to multiple services User delegation SAS: signed with Azure AD credentials (preferred) 4. Network Security Firewall rules : Allow access from specific IP ranges or Azure VNet subnets Service Endpoints : Route traffic over Azure backbone Private Endpoints : Fully private access via VNet — no public internet exposure Disable public blob access : Prevent anonymous access 5. Encryption At rest : AES 256 via Azure Storage Service Encryption (SSE), enabled by default Customer Managed Keys : Bring your own keys via Azure Key Vault In transit : TLS 1.2 enforced 6. Microsoft Defender for Storage Threat detection: unusual access patterns, potential data exfiltration, malware uploads Best practice: Combine RBAC (for broad container level access) + ACLs (for granular path level access) + Private Endpoints + CMK for enterprise security.

Question 5

Write a SQL query to remove duplicate rows from a table.

Accepted Answer

Here are several approaches to remove duplicate rows from a SQL table, depending on the database system: Setup: Approach 1: CTE with ROW NUMBER() (most reliable, all databases) Approach 2: Using a temp table (safe approach) Approach 3: PostgreSQL — using ctid (internal row identifier) Approach 4: MySQL — self join Best practice: Always run a SELECT version first to verify which rows will be deleted, then run DELETE . Use the CTE with ROW NUMBER() approach as it works across most modern databases (SQL Server, PostgreSQL, Azure SQL) and is the most explicit about which duplicate to retain.

Question 6

How do you manage data lifecycle policies in ADLS?

Accepted Answer

Data lifecycle management in ADLS Gen2 automates the tiering and deletion of data based on rules, reducing storage costs as data ages. Lifecycle Management Policy Structure: Policies are JSON rules applied at the storage account level. Each rule specifies: Filters : which blobs/paths the rule applies to Actions : what to do (tier transition or delete) and when Configuring via Azure Portal: 1. Storage Account → Data Management → Lifecycle Management 2. Add rule → Set filters (prefix, blob type) and actions (days since creation/modification/access) Example JSON policy: Storage access tiers: Tier Storage Cost Access Cost Min Duration Use Case Hot Highest Lowest None Frequent access Cool Medium Medium 30 days Infrequent access Cold Lower Higher 90 days Rarely accessed Archive Lowest Highest (+ rehydrate) 180 days Long term retention Last access time tracking: Enable Last Access Time Tracking to tier based on when data was last read (not just modified) Useful for tiering data that hasn't been queried recently Rehydration from Archive: Data in Archive tier must be rehydrated before access (hours to 15 hours) Priority options: Standard or High priority (faster but more expensive) Azure CLI to apply policy:

Question 7

What are the key considerations for designing a scalable data architecture in Azure?

Accepted Answer

A scalable Azure data architecture must balance performance, cost, reliability, and security. Key considerations: 1. Layered Architecture (Medallion / Lambda) Bronze (Raw) : Land data as is from sources — no transformations Silver (Curated) : Cleaned, deduplicated, joined data Gold (Aggregated) : Business ready aggregates and metrics Use Delta Lake throughout for ACID guarantees and time travel 2. Right Service for the Right Workload Batch ETL : ADF orchestration + Databricks/Synapse for transformation Real time : Event Hub → Stream Analytics / Spark Structured Streaming Ad hoc SQL : Synapse Serverless SQL (pay per query, no cluster management) ML/Advanced : Databricks with MLflow 3. Storage Strategy Use ADLS Gen2 as the central data lake (cost effective, hierarchical namespace) Apply partitioning by date/region for query performance Use Parquet/Delta format — never CSV at scale Apply lifecycle policies for hot/cool/archive tiering 4. Scalability Patterns Horizontal scaling : Databricks auto scaling clusters, ADF parallel copy Serverless : Synapse Serverless SQL, ADF serverless IR for cost efficiency Metadata driven pipelines : Scale to hundreds of tables without code changes 5. Security and Governance Managed Identity everywhere (no stored credentials) Azure Key Vault for secrets, CMK for encryption Microsoft Purview for data catalog, lineage, and classification Unity Catalog (Databricks) for table level and column level access control 6. Reliability and Monitoring Delta Lake ACID transactions for data consistency Azure Monitor + Log Analytics for pipeline monitoring Retry policies in ADF; checkpointing in Spark Streaming Geo redundant storage (GRS/GZRS) for critical data 7. Cost Management Tag all resources for cost allocation by team/project Auto terminate idle Databricks clusters Use reserved capacity for predictable workloads Monitor with Azure Cost Management + budgets and alerts

Question 8

How do you integrate Azure Key Vault with other Azure services?

Accepted Answer

Azure Key Vault integrates with Azure services through Managed Identities , Azure RBAC , and native SDK/connector support. 1. Integration via Managed Identity (preferred — no stored credentials) 2. Azure Data Factory In Linked Service configuration, use Azure Key Vault linked service for passwords ADF's Managed Identity must have Key Vault Secrets User role on the Key Vault Secrets are fetched at pipeline runtime — never stored in ADF artifacts 3. Azure Databricks Create a secret scope backed by Azure Key Vault Access secrets in notebooks: dbutils.secrets.get('scope', 'key') Works with JDBC passwords, storage access keys, API tokens 4. Azure Functions / App Service Reference Key Vault secrets in app settings using the syntax: @Microsoft.KeyVault(SecretUri=https://myvault.vault.azure.net/secrets/mySecret/) Enable system assigned Managed Identity and grant Key Vault Secrets User RBAC role 5. Azure Synapse Analytics Link Key Vault in Synapse Studio (Manage → Linked Services) Reference secrets in Synapse pipelines and Spark notebooks 6. Azure Kubernetes Service (AKS) Use Secret Store CSI Driver with Azure Key Vault provider Secrets are mounted as files or environment variables in pods RBAC Roles for Key Vault: Role Permissions Key Vault Secrets Officer Create, update, delete secrets Key Vault Secrets User Read secrets only Key Vault Administrator Full control including access policies Best practice: Use Azure RBAC (not legacy access policies) for fine grained, auditable access. Enable soft delete + purge protection + Key Vault Firewall with Private Endpoint for production vaults.

EXL Interview Questions