117 ) KPMG questions

1 .  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.
 


4. How did you keep metadata and data integrity  consistent when moving  models across oracle 
and redshift  


-----------------------------------------------------------------------
5. mfg has sales , billing and finance using diff customer definitions for revenue and customer reporting 
how do you design a governed customer canonical cycle without disrupting the current reporting cycles 


-----------------------------------------------------------------------
petronas energy data modeling 
how do you decide when design a energy model entity in a dimensional structure compared normalized structure
-----------------------------------------------------------------------
how do you chose the grain of a main fact table 
-----------------------------------------------------------------------
you worked on oracle ebs testing , how do you decide which test cases were most useful for uat
-----------------------------------------------------------------------
how do you keep business (semantic ) events intact in order to cash model , 
while moving from logical to physical
-----------------------------------------------------------------------
when an erp process is mandatory in one process to another , how do you chose cardinatlity in the model 
-----------------------------------------------------------------------
how do keep customer, product and   dimensions confirmed across sales , procurement and mfg and fin domains 
without losing each business meaning
-----------------------------------------------------------------------
how do you chose scd type 1 and type 2 when customer and product master data  in erp analytics model 
-----------------------------------------------------------------------
how do you model order status changes in relational schema so that you can keep the current state and previous state in history 
-----------------------------------------------------------------------


how do you chose business event grain for a ecc proces tables for joins aggregation string 
-----------------------------------------------------------------------
how do you decide whether an ecc process belongs to a fact table or dimensional in your semantic tables
-----------------------------------------------------------------------
how do you keep data model consistent when you move 
-----------------------------------------------------------------------
how do you keep naming conventions consistent across canonical model or conformed models 
-----------------------------------------------------------------------
how do you get engineers , architects and analytics teams to agree on modeling standards before modeling goes live 
-----------------------------------------------------------------------
how do you define confored key so that suppee
 
-----------------------------------------------------------------------
 how do you decide between a transaction fact and periodic snapshot  
 -----------------------------------------------------------------------


 inventory , revenue and balance measure are additive ,semi additive or non additive
-----------------------------------------------------------------------
 
 how do you model cancellations and reversals so that the original transaction history remains auditable 
-----------------------------------------------------------------------
 
 how do keep a shared customer or product model reusable accross the  supply chain and finance without changing 
the meaning drifting for eg . by using clear definition in governance 
-----------------------------------------------------------------------


how do you decide what belongs in the operational model vs the canonical model vs the analytical model 
-----------------------------------------------------------------------


how do you enforce modeling standards across naming ,lineage and quality without slowing delivery 
-----------------------------------------------------------------------


how do you use lineage to trace or recurring data quality defect  back to the right model transformation owner 
-----------------------------------------------------------------------


how do you apply a governance framework  to a reusable enterprise data product
  -----------------------------------------------------------------------
  
 how do you standardize status values across ecc sources in a canonical model 
 -----------------------------------------------------------------------
 
 if the sales and finance define same customer segment differently how do you decide 
 which meaning belongs to a conformed dimension
 -----------------------------------------------------------------------
 
in ECC reporting model ,  what problem do you create if you let the source transaction table grain