Q: Explain Azure DevOps pipelines.

Azure DevOps Pipelines is a CI/CD (Continuous Integration / Continuous Deployment) service that automates the build, test, and deployment of code. In data engineering, it's used to deploy ADF pipelines, Databricks jobs, dbt models, and infrastructure. Core concepts: 1. YAML Pipelines (preferred) Pipeline defined as code in azure pipelines.yml stored in the repo Version controlled alongside application code Supports multi stage pipelines 2. Stages, Jobs, and Steps hierarchy: 3. Triggers CI trigger : Runs on every push to main branch PR trigger : Runs on pull request creation/update Scheduled trigger : Cron based runs 4. Environments and Approvals Define environments (dev, staging, prod) with approval gates Require manual approval before deploying to production 5. Service Connections Securely connect to Azure subscriptions, GitHub, Docker Hub Use Service Principals or Managed Identity 6. Variable Groups + Key Vault Store environment specific variables (connection strings, secrets) in variable groups Link variable groups to Azure Key Vault for secret rotation Data engineering CI/CD workflow: Git commit → CI: lint + unit test → Deploy to Dev → Integration test → Approval gate → Deploy to Prod

Q: How do you implement data masking in ADF?

Data masking in ADF hides or obfuscates sensitive data (PII, PHI, financial data) during data movement or transformation, ensuring non privileged consumers cannot see raw values. 1. Dynamic Data Masking in Azure SQL (source level) Configure masking rules directly on the source Azure SQL Database Non privileged users see masked values; privileged users see actual data ADF pipelines copying from Azure SQL will receive masked data if the service principal lacks UNMASK permission 2. Masking in ADF Mapping Data Flows (transformation level) Use derived column transformations to mask data during the pipeline: 3. Tokenization and Hashing Use consistent hashing to preserve referential integrity across tables while masking the actual value. 4. Using Azure Key Vault for Encryption Based Masking ADF retrieves encryption keys from Key Vault at runtime Encrypt sensitive columns before writing to sink Only authorized services with Key Vault access can decrypt 5. Column Filtering In Copy Activity column mapping, simply omit sensitive columns to exclude them from the sink entirely In Mapping Data Flows, use Select transformation to drop columns Best practices: Apply masking at the source/transformation layer, not just at reporting layer Use service principal with minimum required permissions (principle of least privilege) Log which columns are masked in your data catalog (Purview) Test masked output before deploying to non prod environments

Question 1

Difference between repartition and coalesce in PySpark.

Accepted Answer

Both repartition() and coalesce() change the number of partitions in a PySpark DataFrame, but they work very differently: repartition(n) Performs a full shuffle — redistributes all data evenly across n partitions Can both increase and decrease the number of partitions Creates evenly balanced partitions (important for preventing data skew) More expensive due to the shuffle operation Optional: repartition(n, col) — hash partitions by a column value coalesce(n) Performs a partial shuffle (or no shuffle) — merges partitions on the same executor where possible Can only decrease the number of partitions (not increase) Resulting partitions may be uneven in size Much cheaper than repartition for reducing partitions Comparison table: Feature repartition coalesce Shuffle Full shuffle Minimal/no shuffle Direction Increase or decrease Decrease only Partition balance Even May be uneven Cost High Low Use case Fixing skew, pre join Reducing files before write When to use which: Use coalesce() before writing to reduce output file count (cheap) Use repartition() when data is skewed and you need balanced partitions for downstream joins/aggregations Use repartition(n, col) when you plan multiple operations on the same column (amortizes the shuffle cost)

Question 2

How do you persist and cache data in PySpark?

Accepted Answer

PySpark's persist and cache mechanisms store intermediate DataFrames in memory (or disk) to avoid recomputing them from scratch when they are used multiple times. cache() — shorthand for default persistence persist(storageLevel) — explicit storage level control Storage level guide: Level Memory Use CPU Cost Disk Fallback MEMORY ONLY High Low No (recompute) MEMORY AND DISK Medium Low Yes MEMORY ONLY SER Lower Higher No DISK ONLY Minimal Higher Always unpersist() — release cached data When to cache: DataFrame is used 2+ times in the same application Computation is expensive (join + aggregation) and the result fits in memory Iterative ML algorithms (repeated passes over training data) When NOT to cache: DataFrame is used only once (caching adds overhead) DataFrame is larger than available executor memory Streaming DataFrames (use checkpointing instead)

Question 3

How do Azure Logic Apps automate SQL workflows?

Accepted Answer

Azure Logic Apps is a serverless workflow automation service that connects Azure services and external systems using a visual designer. It can automate SQL related workflows without writing server code. Common SQL automation scenarios: 1. Scheduled SQL execution (e.g., daily report generation) Trigger: Recurrence trigger (every day at 8AM) Action: SQL Server — Execute stored procedure or Execute query Action: Send email (Office 365) with query results 2. File arrival triggered SQL load Trigger: Azure Blob Storage — When a blob is created Action: Azure Data Factory — Create pipeline run (triggers ADF to load the file) Action: SQL Server — Insert row (log the file arrival in an audit table) 3. SQL driven notification workflow Trigger: SQL Server — When a row is modified (polling trigger) Condition: Check if status = 'Failed' Action: Send Teams notification or Create Jira ticket 4. Cross system data sync Trigger: HTTP request (webhook) Action: SQL Server — Get rows from source DB Loop: For each row → HTTP — POST to REST API Available SQL connectors: SQL Server connector: supports Azure SQL, SQL Server (on prem via on premises data gateway) Actions: Execute query, Get rows, Insert row, Update row, Delete row, Execute stored procedure Benefits over manual scripting: Visual designer — no code required for simple workflows Built in retry logic and error handling Native Azure service integration (Event Grid, Key Vault, Service Bus) Audit log for every workflow run Limitation: For complex ETL with transformations, ADF + Databricks is more appropriate. Logic Apps excels at lightweight orchestration and notification workflows.

Question 4

Difference between ADLS Gen1 and Gen2.

Accepted Answer

ADLS Gen1 (Azure Data Lake Store) and ADLS Gen2 (built on Azure Blob Storage) are both Azure data lake offerings, but Gen2 represents a significant architectural upgrade. Key differences: Feature ADLS Gen1 ADLS Gen2 Underlying storage Dedicated service Azure Blob Storage with HNS Hierarchical Namespace Yes Yes (must be enabled) Blob Storage API Not supported Fully supported Protocol support WebHDFS, REST ABFS (Azure Blob File System), REST, NFS 3.0 Access control POSIX ACLs only POSIX ACLs + Azure RBAC Lifecycle management Not supported Supported (tiering: hot/cool/archive) Geo redundancy LRS, GRS LRS, ZRS, GRS, GZRS, RA GRS Integration Hadoop, HDInsight Hadoop, Databricks, Synapse, HDInsight, ADF Pricing Higher Lower (Blob pricing) Status Retired (Feb 29, 2024) Current standard ADLS Gen2 key advantages: Hierarchical Namespace (HNS) : Efficient directory operations (rename, delete directory) — O(1) atomic operations Multi protocol access : Use Blob APIs and ADFS (ABFS) driver simultaneously Lifecycle policies : Automatically tier data to save cost Unified storage : One storage account supports both Data Lake and Blob workloads Better security : Combines ACLs with Azure RBAC for fine grained access ABFS driver (Gen2 native protocol): Migration: Microsoft retired ADLS Gen1 in February 2024. All Gen1 workloads must migrate to Gen2.

Question 5

Write a SQL query to find gaps in a sequence.

Accepted Answer

Finding gaps in a sequence (e.g., missing IDs, order numbers, or dates) is a classic SQL problem solved using window functions or self joins. Setup: Approach 1: Using LAG() window function (find start of each gap) Result: Approach 2: Generate expected sequence and find missing values Approach 3: Self join approach (for databases without window functions) Date gap example:

Question 6

High availability and disaster recovery for Azure SQL.

Accepted Answer

Azure SQL Database has built in High Availability (HA) and multiple Disaster Recovery (DR) options. Built in High Availability: 1. General Purpose / Standard tier Uses remote storage (Azure Premium Storage) with compute/storage separation 3 replicas of data files; compute restarts on a new node if failure occurs 99.99% SLA , ~30 second failover time 2. Business Critical / Premium tier Always On Availability Group : 3 4 synchronous replicas (primary + readable secondaries) Failover in seconds (synchronous replication) Readable secondary available for read workloads (scale out reads) 99.995% SLA 3. Hyperscale tier Up to 4 named replicas, log service architecture Near instantaneous backups via storage snapshots Scales compute/storage independently Disaster Recovery Options: 1. Geo Replication (Active Geo Replication) Create up to 4 readable secondary databases in different Azure regions Asynchronous replication (small RPO) Manual failover — you initiate the failover to the secondary 2. Failover Groups (Auto Failover) Group one or more databases together under a single endpoint Automatic failover based on health policy (no manual intervention needed) Uses a listener endpoint that auto redirects connections after failover RPO: typically < 5 seconds; RTO: < 60 seconds 3. Automated Backups + Long Term Retention (LTR) Full (weekly), differential (daily), transaction log (every 5 12 min) backups Retained 7 35 days by default; LTR up to 10 years Point in time restore (PITR) within the retention window Best practice: Use Failover Groups for critical applications (automatic failover + transparent endpoint), combined with LTR for compliance and backup retention requirements.

Question 7

Explain Azure DevOps pipelines.

Accepted Answer

Azure DevOps Pipelines is a CI/CD (Continuous Integration / Continuous Deployment) service that automates the build, test, and deployment of code. In data engineering, it's used to deploy ADF pipelines, Databricks jobs, dbt models, and infrastructure. Core concepts: 1. YAML Pipelines (preferred) Pipeline defined as code in azure pipelines.yml stored in the repo Version controlled alongside application code Supports multi stage pipelines 2. Stages, Jobs, and Steps hierarchy: 3. Triggers CI trigger : Runs on every push to main branch PR trigger : Runs on pull request creation/update Scheduled trigger : Cron based runs 4. Environments and Approvals Define environments (dev, staging, prod) with approval gates Require manual approval before deploying to production 5. Service Connections Securely connect to Azure subscriptions, GitHub, Docker Hub Use Service Principals or Managed Identity 6. Variable Groups + Key Vault Store environment specific variables (connection strings, secrets) in variable groups Link variable groups to Azure Key Vault for secret rotation Data engineering CI/CD workflow: Git commit → CI: lint + unit test → Deploy to Dev → Integration test → Approval gate → Deploy to Prod

Question 8

How do you implement data masking in ADF?

Accepted Answer

Data masking in ADF hides or obfuscates sensitive data (PII, PHI, financial data) during data movement or transformation, ensuring non privileged consumers cannot see raw values. 1. Dynamic Data Masking in Azure SQL (source level) Configure masking rules directly on the source Azure SQL Database Non privileged users see masked values; privileged users see actual data ADF pipelines copying from Azure SQL will receive masked data if the service principal lacks UNMASK permission 2. Masking in ADF Mapping Data Flows (transformation level) Use derived column transformations to mask data during the pipeline: 3. Tokenization and Hashing Use consistent hashing to preserve referential integrity across tables while masking the actual value. 4. Using Azure Key Vault for Encryption Based Masking ADF retrieves encryption keys from Key Vault at runtime Encrypt sensitive columns before writing to sink Only authorized services with Key Vault access can decrypt 5. Column Filtering In Copy Activity column mapping, simply omit sensitive columns to exclude them from the sink entirely In Mapping Data Flows, use Select transformation to drop columns Best practices: Apply masking at the source/transformation layer, not just at reporting layer Use service principal with minimum required permissions (principle of least privilege) Log which columns are masked in your data catalog (Purview) Test masked output before deploying to non prod environments

Cognizant Interview Questions