145 ) Which Azure features and project did you work on

 

Relevant Azure Features for Insurance Migration (SSMS to Azure)

• Azure SQL Database / Azure SQL Managed Instance: Fully managed relational database services used as the target replacement for on-premises SQL Server Management Studio (SSMS) databases, offering automated backups, high availability, and auto-scaling.

• Azure Data Factory (ADF): A cloud-scale ETL/ELT orchestration service used to build data pipelines for migrating legacy insurance data and syncing incremental changes.

• Azure Databricks: A collaborative Apache Spark-based analytics platform used for heavy data transformation, cleansing, and preparing large volumes of insurance claims and policy datasets.

• Azure Blob Storage / Azure Data Lake Storage (ADLS Gen2): Scalable cloud object storage used as a secure staging landing zone for raw policy documents, CSVs, and backup files before loading into target databases.

• Azure Key Vault: Secures and manages sensitive insurance credentials, database connection strings, and encryption keys in compliance with data privacy regulations (e.g., HIPAA/GDPR).

• Microsoft Purview: Manages enterprise data governance, data mapping, and automated data lineage tracking across legacy and cloud insurance data assets.

End-to-End Migration Steps (SSMS to Azure)

1. Assessment and Discovery:

   • Audit the existing on-premises SQL Server databases using tools like the Data Migration Assistant (DMA) to identify compatibility issues, unsupported features, and schema dependencies.

2. Infrastructure and Security Setup:

   • Provision Azure resources (Azure SQL, ADF, storage accounts) and configure networking, Virtual Networks (VNets), firewalls, and role-based access control (RBAC).

   • Secure connection strings and secrets inside Azure Key Vault.

3. Data Modeling and Architecture Preparation:

   • Adapt the existing SSMS physical schema for cloud optimization, ensuring proper indexing, partitioning strategies, and data vault or star-schema structures for analytics.

4. Data Migration (Schema and Data Transfer):

   • Use the Azure Database Migration Service (DMS) or ADF pipelines to perform initial schema migration, followed by full historical data loads (bulk copy) from SSMS to Azure SQL.

5. Data Synchronization and Testing:

   • Establish change data capture (CDC) or replication streams to keep the on-premises SSMS and cloud Azure databases synchronized during testing phases.

   • Run parallel test validation, performance benchmarking, and data quality checks.

6. Cutover and Go-Live:

   • Stop incoming writes to the legacy SSMS system, complete the final delta synchronization, switch application connection strings to Azure, and launch the production environment.

Applications Involved in the Insurance Ecosystem

• Core Policy Administration System: Manages policy lifecycles, quotes, underwriting rules, and renewals.

• Claims Management System: Tracks incident reports, claims adjustments, payouts, and fraud detection workflows.

• Billing and Premium Management System: Handles recurring payments, invoicing, ledger reconciliation, and payment gateway integrations.

• Legacy SQL Server Management Studio (SSMS): The on-premises database management environment being migrated away from.

• Downstream BI & Reporting Tools (e.g., Power BI): Connects to the new Azure cloud data warehouse to generate executive dashboards, risk profiles, and regulatory reports.

Role of a Data Modeler in the Migration Project

• Legacy Schema Analysis: Reverse-engineer existing SSMS database schemas to understand legacy insurance entities, relationships, constraints, and business logic.

• Cloud-Optimized Data Design: Redesign tables, constraints, and relationships to fit the target cloud environment (whether migrating to transactional cloud SQL or dimensional data models in Azure Synapse/Databricks).

• Handling Complex Insurance Entities: Structure complex hierarchical insurance concepts, such as multi-party policies, coverage riders, claims-to-policy mappings, and historical audit trails.

• Implementing Data Governance Rules: Define standardized naming conventions, data types, surrogate keys, and data integrity constraints to ensure clean data flows into the new Azure architecture.

• Collaboration with Data Engineers: Partner with engineering teams to ensure physical data models support efficient ETL transformations, partitioning strategies, and indexing performance in Azure.

144 ) What are ACID Properties?

 

What are ACID Properties?

ACID is a set of four key properties that guarantee reliable and predictable transaction processing in relational database management systems (RDBMS). A transaction is a single unit of work comprising one or more SQL operations (inserts, updates, deletes).

• Atomicity: The "all-or-nothing" rule. Either every operation within the transaction succeeds, or the entire transaction is rolled back. No partial updates are permitted.

• Consistency: Ensures that a transaction brings the database from one valid state to another, strictly adhering to all defined rules, constraints, cascades, and foreign keys.

• Isolation: Ensures that concurrent transactions execute independently without interfering with one another, preventing dirty reads, non-repeatable reads, and phantom reads.

• Durability: Once a transaction is committed, its changes are permanent and survive system crashes, power failures, or server reboots.

Examples of Each ACID Property

1. Atomicity Example (Bank Transfer):

   • Scenario: Transferring $100 from Account A to Account B requires two operations: subtract $100 from A and add $100 to B.

   • Failure: If the system crashes after step 1, atomicity ensures the $100 is not lost; the database rolls back the deduction from Account A.

2. Consistency Example (Inventory Balance):

   • Scenario: A database constraint dictates that inventory stock cannot drop below zero (stock >= 0).

   • Failure: If a transaction attempts to purchase 10 items when only 5 are in stock, consistency prevents the transaction from committing and violating the rule.

3. Isolation Example (Concurrent Balance Checks):

   • Scenario: User A reads their bank balance while a background process (User B) is depositing funds.

   • Control: Isolation ensures User A doesn't see a "half-written" or dirty state during the simultaneous execution.

4. Durability Example (Order Confirmation):

   • Scenario: A user completes an online checkout, and the application returns a "Success" message.

   • Guarantee: Even if the database server instantly loses power right after the commit, the order record is safely written to non-volatile disk storage (Write-Ahead Logging).

How to Confirm and Test via SQL Commands

You can verify and test ACID behavior using standard transactional control statements (BEGIN / START TRANSACTION, COMMIT, ROLLBACK, and isolation-level configuration commands).

1. Testing Atomicity (COMMIT vs. ROLLBACK)

This command confirms that failed operations can be entirely undone, leaving zero trace in the database.

SQL

-- Start a transaction block
BEGIN TRANSACTION;

-- Step 1: Deduct funds
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Step 2: Simulate an error or bad condition (e.g., target account doesn't exist)
UPDATE accounts SET balance = balance + 500 WHERE account_id = 99999; 

-- If something goes wrong, you explicitly roll back
ROLLBACK;

-- Confirmation: Account 1's balance remains completely unchanged
SELECT balance FROM accounts WHERE account_id = 1;

2. Testing Consistency (Constraint Enforcement)

This confirms that invalid data states are automatically rejected by the database engine.

SQL

-- Attempt to insert a record that violates a CHECK constraint (e.g., age must be >= 18)
BEGIN TRANSACTION;

INSERT INTO users (username, age) VALUES ('john_doe', 15);
-- Error: Check constraint violated!

COMMIT; 
-- Confirmation: The database automatically aborted or will reject this, 
-- and the row does not exist in the table.
SELECT * FROM users WHERE username = 'john_doe';

3. Testing Isolation (Session Isolation Levels)

You can set and verify the isolation level to control how transactions interact concurrently.

SQL

-- Check the current isolation level (PostgreSQL example)
SHOW TRANSACTION ISOLATION LEVEL;

-- Set transaction isolation level to Serializable (the highest level of isolation)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- While this transaction is open, updates from other concurrent sessions are blocked or deferred.
COMMIT;

4. Confirming Durability

While durability is handled automatically by the storage engine via Write-Ahead Logging (WAL), you confirm it by issuing a successful COMMIT:

SQL

BEGIN TRANSACTION;
INSERT INTO audit_logs (event_desc, logged_at) VALUES ('System backup verified', CURRENT_TIMESTAMP);
COMMIT; 
-- Once the COMMIT returns success, durability is guaranteed by the RDBMS storage layer,  

-- ensuring the data persists even if the database service is immediately restarted.