231 ) FAQ AZURE interview qsns

 Here are the 10 technical interview questions along with their ideal answers based on the end-to-end SSMS to Azure migration project:

1. Assessment & Discovery

• Question: Why did you use Microsoft Data Migration Assistant (DMA) in Step 1, and what specific types of issues does it help identify before migrating from on-premises SSMS to Azure SQL Database?
  
  
• Answer: DMA is used to analyze the source database for compatibility issues. It scans the database schema, stored procedures, and T-SQL code to flag breaking changes, unsupported or deprecated features (like cross-database queries or specific CLR integrations), and schema dependencies that won't work out-of-the-box in Azure SQL Database, allowing us to fix them before attempting the migration.
  
  

2. Data Governance & Discovery

• Question: How does Microsoft Purview complement DMA during the initial assessment and discovery phase of an insurance database migration?
  
  
• Answer: While DMA focuses purely on technical database engine compatibility, Microsoft Purview is used for enterprise data governance and discovery. It scans all data assets to classify sensitive information (like PII, customer names, and policy details) and maps data lineage across systems, which is crucial for meeting compliance regulations like GDPR and HIPAA.
  
  

3. Security & Secret Management

• Question: Why is it considered a security risk to store connection strings directly in application code, and how does Azure Key Vault solve this during migration?
  
  
• Answer: Hardcoding connection strings exposes database credentials, usernames, and passwords to anyone with access to the code repository, creating a major security vulnerability. Azure Key Vault solves this by centralizing secrets management, allowing applications to securely fetch connection strings at runtime via managed identities without exposing plain-text credentials.
  
  

4. Staging & Landing Zones

• Question: In Step 3, why didn't you migrate data directly from SSMS to Azure SQL Database? What is the benefit of using Azure Data Lake Storage (ADLS Gen2) as an intermediate staging landing zone?
  
  
• Answer: Extracting massive volumes of operational data directly can cause performance bottlenecks and lock tables on a live production SSMS server. Staging raw data (such as CSV exports or backups) into ADLS Gen2 provides a safe, fault-tolerant landing zone, creates a raw historical backup, and allows heavy data cleansing before loading it into the target database.
  
  

5. Big Data Transformation

• Question: When would you choose Azure Databricks over a standard database query for transforming your insurance claims and policy datasets?
  
  
• Answer: Azure Databricks is chosen when dealing with massive volumes of data or complex, unstructured/semi-structured datasets that require distributed computing (using Apache Spark). While a standard SQL query handles relational updates well, Databricks is built for heavy data cleansing, large-scale transformations, and machine learning preparation across huge volumes of raw files.
  
  

6. Target Architecture Selection

• Question: What are the key advantages of migrating core tables (customer, policy, claims, agents) to Azure SQL Database compared to keeping them on an on-premises SSMS instance?
  
  
• Answer: Azure SQL Database provides fully managed operations, meaning automated patching, backups, and high availability out of the box. It also offers elastic scalability, allowing you to instantly scale up vCores or storage during peak workloads (like annual policy renewal seasons) without over-provisioning expensive physical hardware.
  
  

7. ETL & Pipeline Orchestration

• Question: How does Azure Data Factory (ADF) integrate with Azure Databricks and Azure SQL Database in an end-to-end data pipeline?
  
  
• Answer: ADF acts as the central orchestrator. It triggers the extraction of raw data into ADLS Gen2, invokes Azure Databricks notebooks to run data cleansing and transformation jobs, and finally orchestrates the copy activity to load the processed data into the target Azure SQL Database tables.
  
  

8. Handling Incremental Changes (Delta Sync)

• Question: During the transition phase, how do you handle ongoing transactions (inserts/updates) on your legacy SSMS tables so you don't lose data before final cutover?
  
  
• Answer: We handle ongoing transactions by implementing Change Data Capture (CDC) or by configuring ADF incremental load pipelines using watermark columns (such as LastModifiedDate or auto-incrementing IDs). This ensures that any new policies or claims added after the initial bulk load are synced over to Azure.
  
  

9. Validation and Testing

• Question: What validation steps would you perform after moving the policy and claims tables to Azure SQL to guarantee zero data loss?
  
  
• Answer: We perform a multi-tier validation:
  
  
  1. Row-count verification to ensure the number of records matches between SSMS and Azure SQL for each table.
     
     
  2. Aggregate checksum tests (e.g., comparing SUM(claimAmount) or SUM(premiumAmt)) to verify mathematical accuracy.
     
     
  3. Running parallel query performance tests to ensure foreign key relationships and indexes are functioning correctly.
     
     

10. Troubleshooting Cutover Failures

• Question: If an application throws a connectivity error immediately after switching its connection string to Azure SQL Database during go-live, what are the first three things you would troubleshoot?
  
  
• Answer:
  
  
  1. Firewall / Network Rules: Check if the application's IP address or Virtual Network (VNet) is allowed in Azure SQL's firewall or private endpoint settings.
     
     
  2. Credentials & Key Vault: Verify that the application is successfully fetching the correct username and password from Azure Key Vault and that the database user has proper permissions.
     
     
  3. Connection String Syntax: Confirm that the connection string includes required parameters like Encrypt=True and specifies the correct server and database names.