Question 1

Difference between a job cluster and an interactive cluster in Databricks.

Accepted Answer

Job clusters and interactive clusters serve different purposes in Databricks: Job Cluster: Created automatically when a job is triggered and terminated when the job completes Optimized for automated workloads — no idle cost since the cluster is destroyed after the run Cannot be shared across multiple jobs simultaneously Configured through the job definition (cluster policy, node type, autoscaling) Best for: ETL pipelines, scheduled batch jobs, CI/CD workflows Interactive Cluster: Long running clusters manually started and kept alive for exploratory or collaborative work Multiple users can attach notebooks and run concurrent sessions Supports autoscaling and auto termination (e.g., after 30 minutes of inactivity) More expensive if left running idle Best for: data exploration, development, ad hoc analysis, ML experimentation Key differences: Feature Job Cluster Interactive Cluster Lifecycle Per job Long lived Cost Pay only during run Accrues idle cost Sharing Dedicated to job Multi user Creation Automatic Manual In production data engineering, job clusters are preferred for scheduled pipelines, while interactive clusters are used in development workspaces.

Question 2

How do you copy all tables using metadata-driven pipelines in ADF?

Accepted Answer

A metadata driven pipeline in Azure Data Factory (ADF) dynamically copies all tables without hardcoding each table name. The pattern uses a control table and a ForEach loop. Steps: 1. Create a control table (e.g., in Azure SQL or a config file) listing tables to copy: 2. Lookup Activity — query the control table to retrieve the list of active tables. 3. ForEach Activity — iterate over the Lookup output ( @activity('LookupTables').output.value ) with batchCount for parallelism. 4. Copy Activity inside ForEach — use dynamic content for source/sink: Source query: @{concat('SELECT FROM ', item().source schema, '.', item().source table)} Sink table: @item().sink table 5. Parameterize Linked Services and Datasets so they accept table names at runtime. Benefits: Add new tables by inserting a row in the control table — no pipeline changes needed Centralized logging and error handling per table Works across schemas, databases, and even cross cloud sources This pattern is widely used in full and incremental load scenarios (watermark based incremental can be added to the control table).

Question 3

How do you implement data encryption in Azure SQL Database?

Accepted Answer

Azure SQL Database provides multiple layers of encryption to protect data at rest and in transit: 1. Transparent Data Encryption (TDE) — Data at Rest Enabled by default on all Azure SQL databases Encrypts the database, backups, and transaction logs using AES 256 Uses a Database Encryption Key (DEK) wrapped by a TDE protector Can use service managed keys or customer managed keys (CMK) via Azure Key Vault (Bring Your Own Key) 2. Always Encrypted — Column Level Encryption Encrypts sensitive columns (e.g., SSN, credit card) so even DBAs cannot see plaintext Encryption/decryption happens in the client application using column master keys stored in Azure Key Vault Supports deterministic (allows equality search) and randomized (more secure, no search) encryption types 3. Dynamic Data Masking Masks data at the query result level for non privileged users (not true encryption, but limits exposure) 4. Encryption in Transit (TLS) Azure SQL enforces TLS 1.2 for all connections by default Ensure connection strings include Encrypt=True;TrustServerCertificate=False 5. Azure Key Vault Integration Store and rotate encryption keys centrally Enforce access policies with Managed Identities Best practice: Combine TDE + Always Encrypted for sensitive columns + Key Vault CMK for enterprise grade security posture.

Question 4

Write Python code to generate Fibonacci numbers.

Accepted Answer

Here are three common approaches to generate Fibonacci numbers in Python: 1. Iterative approach (most efficient for large n): 2. Generator (memory efficient for streaming): 3. Recursive with memoization (elegant but uses call stack): Time/Space complexity: Iterative: O(n) time, O(n) space Generator: O(1) space (yields one at a time) Recursive+memo: O(n) time with cache, O(n) call stack

Question 5

Best practices for managing and optimizing storage costs in ADLS.

Accepted Answer

Azure Data Lake Storage (ADLS) Gen2 costs grow quickly without proper management. Key best practices: 1. Implement Lifecycle Management Policies Automatically tier data from Hot → Cool → Cold → Archive based on last access or modification time Example: Move to Cool after 30 days, Archive after 90 days Configure in Azure portal: Storage Account → Lifecycle Management 2. Use Appropriate Access Tiers Hot : Frequently accessed data (higher storage cost, lower access cost) Cool : Infrequently accessed, kept 30+ days Archive : Rarely accessed, rehydration takes hours but storage is cheapest 3. Enable Data Compression Store files in Parquet or ORC format (columnar, compressed) Parquet with Snappy compression typically achieves 5 10x size reduction vs CSV 4. Implement Partitioning Strategy Partition by date/region to enable partition pruning and reduce data scanned Avoid too many small files (the "small file problem") — use Delta Lake OPTIMIZE to compact 5. Set Retention Policies and Delete Unused Data Use soft delete policies carefully; disable if not needed to avoid double cost Remove intermediate/temporary processing files promptly 6. Monitor with Azure Cost Management Set budgets and alerts on the storage account Use Storage Insights to identify unused containers 7. Use Delta Lake / Managed Tables VACUUM command removes old data files not needed by current table version

Question 6

How do you implement security for data in transit and at rest in Azure?

Accepted Answer

Security in Azure spans two dimensions: data at rest (stored data) and data in transit (moving between services or over networks). Data at Rest: Azure Storage Service Encryption (SSE) : AES 256 encryption for all Azure Storage (Blob, ADLS, Files, Queues) — enabled by default TDE for Azure SQL/Synapse : Encrypts database files and backups automatically Azure Disk Encryption : Encrypts OS and data disks for VMs using BitLocker (Windows) or dm crypt (Linux) Customer Managed Keys (CMK) : Store encryption keys in Azure Key Vault for full key control and auditability Always Encrypted : Column level encryption in SQL where keys never leave the client application Data in Transit: TLS 1.2+ : Enforced across Azure services (Storage, SQL, Key Vault) — older protocols disabled HTTPS only traffic : Enable "Secure transfer required" on storage accounts VNet Service Endpoints / Private Endpoints : Route traffic through Azure backbone, eliminating public internet exposure Azure VPN Gateway / ExpressRoute : Encrypt on premises to Azure traffic Managed Identity + RBAC : Services authenticate without credentials; no passwords in connection strings Governance and Monitoring: Microsoft Defender for Cloud : Detects threats and misconfigurations Azure Monitor + Diagnostic Logs : Audit all access Azure Policy : Enforce encryption standards across subscriptions

Question 7

Describe the role of triggers and schedules in ADF.

Accepted Answer

Triggers in Azure Data Factory (ADF) determine when pipelines execute. There are three types: 1. Schedule Trigger Runs pipelines on a defined recurrence (hourly, daily, weekly, cron expression) Decoupled from pipelines — one trigger can fire multiple pipelines Supports time zone configuration Example: Run ETL pipeline every day at 2:00 AM UTC 2. Tumbling Window Trigger Fires at fixed size, non overlapping time intervals starting from a defined start time Maintains state — if a window is missed (e.g., ADF was down), it will backfill Supports dependencies between windows (current window waits for previous window to succeed) Best for: incremental loads where each window processes a specific time slice of data 3. Event Based Trigger (Storage Events) Fires when a blob is created or deleted in Azure Blob Storage / ADLS Uses Azure Event Grid under the hood Best for: file arrival driven pipelines (process file as soon as it lands in a container) 4. Custom Events Trigger Fires based on custom events published to Azure Event Grid topics Enables event driven architectures beyond storage events Key considerations: Triggers pass triggerName , triggerTime , and window parameters to pipelines via @trigger() expressions Multiple pipelines can be associated with one trigger Tumbling window triggers are preferred over schedule triggers for reliable incremental processing with backfill capability

Question 8

How do you optimize data storage and retrieval in ADLS?

Accepted Answer

Optimizing ADLS Gen2 involves both storage layout and query/retrieval patterns . Storage Format: Use Parquet or ORC (columnar formats) instead of CSV/JSON — enables column pruning and predicate pushdown Apply Snappy or ZSTD compression to reduce storage footprint and I/O Delta Lake adds ACID transactions, indexing ( Z ORDER ), and data skipping on top of Parquet File Size Optimization: Target 128MB–1GB per file for optimal parallelism in Spark Avoid small files (< 10MB) — use Delta OPTIMIZE or coalesce() before writing Use ADF's block blob type for large sequential writes Partitioning Strategy: Partition by high cardinality but query aligned columns: year/month/day , region , category Avoid over partitioning (too many small directories) which kills list operations In Delta Lake, combine partitioning with Z ORDER BY on frequently filtered non partition columns Access Patterns: Use hierarchical namespace (HNS) in ADLS Gen2 for efficient directory operations Place hot data in SSD backed storage tiers; use lifecycle policies for cold data Use Azure CDN or caching for frequently read reference data Networking: Use Private Endpoints to reduce egress costs and latency Co locate compute (Databricks/Synapse) and storage in the same Azure region Monitoring: Azure Storage Metrics to track read/write latency, transaction count, and egress bytes

Infosys Interview Questions