117 ) KPMG questions

 how did you setup unity catalog and key vault to keep analytics data secure in azure  ?

Securing analytics data in Azure requires a unified governance model that isolates data access and centralizes secret management. To achieve this, configure Unity Catalog to control data access permissions and Azure Key Vault via Unity Catalog Service Credentials to eliminate plain-text passwords and legacy workspace secret scopes. [1, 2, 3, 4]

1. Provision the Azure Databricks Access Connector

The Access Connector serves as the identity bridge between Databricks and Azure resources.

• Create an Access Connector for Azure Databricks in your Azure resource group.
• Opt for a User-Assigned Managed Identity for the connector. This ensures the identity persists even if resources are relocated or restructured. [5, 6, 7]

2. Configure Storage Governance in Unity Catalog

Unity Catalog replaces direct platform or cluster-level storage permissions, ensuring users only interact with data through the governance layer.

• Grant the Access Connector's managed identity the Storage Blob Data Contributor role on your Azure Data Lake Storage (ADLS Gen2) account.
• In the Databricks Account Console, create a Unity Catalog Metastore using the Access Connector's Resource ID.
• Define an External Location and Storage Credential inside Databricks, mapping directly to the ADLS Gen2 path. Users now query tables without possessing direct keys or access to the underlying storage account. [1, 8, 9, 10, 11]

3. Link Azure Key Vault Using Service Credentials

Instead of utilizing legacy, workspace-restricted secret scopes via , leverage workspace-agnostic Unity Catalog Service Credentials.

• Navigate to your Azure Key Vault.
• Use Access Control (IAM) to assign the Key Vault Secrets User role to the Access Connector.
• Open Databricks, navigate to Unity Catalog > Credentials, and create a new Service Credential.
• Paste the Resource ID of your Azure Access Connector to bind the Key Vault to Unity Catalog globally. This allows platform administrators to manage fine-grained, central permissions over who can access specific keys and third-party data passwords. [2, 3, 6]

4. Apply Advanced Security Controls

Enforce precise data security directly over your analytics tables:

• Row Filters: Restrict data visibility based on user groups or regions (e.g., a German analyst only sees rows where ).
• Column Masking: Redact or hash sensitive PII data dynamically (e.g., masking credit card or phone numbers).
• Data Lineage: Unity Catalog automatically captures data access and transformation lineage across all workspaces, providing an immutable audit trail for compliance. [1, 4, 12, 13, 14]

 

 2 . Sales , finance and bi teams use different definitions for booked order and invoice revenue how would you resolve governance definitions in the model ?

    To resolve definition conflicts across sales, finance, and BI teams, create a single data model with unified glossary terms, layered data transformations, and role-based metric views. 

• Use clear business definitions
• Build conformed dimensions
• Apply layer-based data flow

Data Governance Steps

• Central Glossary: Write down one shared meaning for "booked order" and "invoice revenue" in a shared tool that all teams agree to use.
• Conformed Dimensions: Use the same time, customer, and product tables for every team so reports match at the base level.
• Layered Architecture: Keep raw data separate from final business views, transforming metrics step-by-step in a central tool like dbt.
• Semantic Layer: Build a metric layer so tools show the exact same formula for revenue no matter who runs the report.
• Data Steward Approval: Assign owners from sales, finance, and data teams to sign off on any changes to the rules. 




3. how did you reverse engineer legacy physical model to cleaner enterprise model ? 


To convert a legacy physical database into a clean enterprise model, erwin Data Modeler or ER/Studio Data Architect are the industry-standard tools. [1, 2]
Here are the precise step-by-step instructions and commands using erwin Data Modeler. [3, 4]
Step 1: Extract the Legacy Physical Model (Reverse Engineering)
This steps pulls the messy, undocumented legacy schema out of the source database and turns it into a visual model. [5, 6]
1. Open erwin Data Modeler.
2. Click Actions > Reverse Engineer in the top menu.
3. In the New Model window, select Logical/Physical.
4. Set the Target Database field to match your legacy system (e.g., Oracle, SQL Server) and click Next.
5. In the Reverse Engineer Process Wizard, choose Database (or select Script if you are importing a .ddl or .sql text file).
6. Click Next to open the database connection window.
7. Enter your database credentials (Host, Port, User, Password) and click Connect/Next.
8. Check the boxes for the tables, views, and system objects you want to import, then click Finish. [1, 4, 7, 8, 9]
The tool will automatically create a raw physical layout diagram alongside a corresponding logical model view. [2, 7]
Step 2: Clean and Restructure (The Enterprise Transformation)
Once the raw model is loaded, you must fix the bad design choices from the legacy system.
1. Fix Names: Switch the tool view to the Logical Model tab on the left pane. Legacy physical models often use cryptic shorthand (e.g., cust_tbl_01). Right-click the entity, select Properties, and rename it to a clean enterprise business term (e.g., Customer). [10]
2. Infer Missing Relationships: Legacy systems often drop foreign key constraints to speed up performance, leaving data orphaned. Go to Actions > Infer Keys/Relationships. Set the wizard to look for matching column names (e.g., matching customer_id across different tables) to automatically rebuild the missing relationships. [11]
3. Normalize and Clean: Right-click inside the diagram, select New > Entity to create missing lookup/reference tables. Move mixed columns out of massive legacy "god tables" into these new entities to resolve duplicate data issues. [12]
Step 3: Deploy the Clean Enterprise Schema (Forward Engineering)
After finalizing your clean enterprise design, you must generate the code to build it on your new enterprise target platform. [13]
1. Switch to the Physical Model view tab.
2. In the top menu, click Actions > Forward Engineer.
3. In the Forward Engineering Wizard, go to the Option Selection tab.
4. Check the boxes for Create Table, Create Primary Key, and Create Foreign Key.
5. Click the Preview button to review the generated, clean SQL/DDL script.
6. Click Generate to deploy the script directly to your new database instance, or click Save to export an .sql file for your DevOps pipeline. [13, 14, 15, 16]
If you want, let me know:
• What database engine (SQL Server, Oracle, PostgreSQL, etc.) you are connecting to
• Whether you want the specific steps for ER/Studio instead of erwin
I can provide the exact connection parameters or menu names for your setup.