217 ) All questions post

INDEX HERE

html

Table of Contents

 -----------------------------------------------------------------------

1. Project based questions

1. Project explaination 
1. Insurance project qsns
2. Project challenges (SCD 1 TO SCD2 ) 

1. When a product was renamed or repriced, the dimension row was overwritten, destroying historical point-in-time accuracy.

1. SQL queries ( 14)

• 1) find the 4th highest salary from an Employee table.
• 2) Delete Duplicate Records
• 3) Find Each Department wise - Highest Salary of employees
• 4) Count Number of Subjects per Student (Comma-Separated Data)
• 5) SSMS - Cumulative Salary of Each Employee by Department
• 6) Customers Purchasing the Same Product in Different Stores in Same Month
• 7) Most Sold Product by Each State
• 8) Department-Wise Sum of Salaries and Max Salary
• 9 ) query to find the customer who ordered more than 5 orders 
• 10 ) customers historical addresses
• 11. emps salary is greater than their manager 
• 12 .  show team  size of  each employee in the query result 
• 14 )  Query to find duplicate rows of loan id but time is different 
• 15 )   Find out  ( all sessions ,durations ) of a user in  a portal 
• ~~~~~16 ) SQL concept based qsns
• 
  
• Constraints in sql ( 8) ✅

1. Dimensional Modeling questions

1. Modeling qsns - 40

1. Fact Tables & Data Warehousing

   ------------------------------------------------------

   1. Why do we need Data Warehouse
   2. Enterprise data modeling - Insurance project OLTP
   3. Types of data models 
   4. star or snowflake schemas ( how to chose)
   5. Types of facts and dimensions
   6. OLTP & OLAP diff
   7. SCD types and implementation
   8. Experience in Metadata management MDM
   9. Data dictionary✅
   10. STTM 
   11.  181 ) How to handle volume - Dimensional Data Warehouse -
   12. 182 ) why we need OLAP when we can query with OLTP
   13.  184 ) How to merge, add, or delete columns or add new dimensions  in data model  Later
   14. 134 ) Types of indexes 
   15. 212 What is Medallion Architecture?
   16. 211 ) How do you calculate the size of each table
   17. 210 Did you work on NOSQL database
   18. 209 Views and materialized views
   19. 208 ) roles of data architect and responsibilities
   20. 207 What are roles of data modeler
   21. 205 ) What are ACID Properties?
   22. 204 ) Which tools used for Data profiling tools ...
   23. 203 ) Normalization with EGS
   24. 202 ) what is use of Surrogate key is a
   25. 201 ) OLAP & OLTP diff
   26. 200 ) what is ODS( operational data store)
   27. 199 ) Why do we need Data warehouse, when OLTP app...
   28. 198 ) What is Data Mart
   29. 197) Types of Dimensions
   30. 196 ) star and snowflake
   31. 195 ) logical data model vs. physical data model diff
   32. 194 ) Grain and granularity
   33. 193 ) Types of Fact tables amnd facts
   34. 192 ) Ralph kimball & inmon approaches
   35. 191 )How to add a completely new metric to an exi...
   36. 190 ) How do you handle missing or unknown Dimensi...
   37. 189 ) How do you deal with duplicate records into ...
   38. 188 ) Additive, Semi-Additive, and Non-Additive Fact?
   39. 187 ) How to handle (Slowly Changing Dimensions)
   40. 186 ) Late arriving dims & factless fact tables
   41. 185 ) How to ingest fact table a weekly, monthly...

Data modeling tools

1. ERWIN features`
1.  Erwin - Data modeler workspace
2. Erwin - Domains
3. Erwin - types of relationships
4. Erwin - which erwin features did you use
5. Erwin - which feature used to merge the subtype tables to supertype
6. Erwin - Forward and reverse engineering steps
7. Erwin - Complete compare feature
8. Erwin - Naming standards
9. Erwin - Name hardening
10. Erwin - querying tool
11. Erwin - subject areas
12. Erwin - identifying and
13. non identifying relationships differences...........
14. ERWIN - how maintain versions ( named & delta ) 
15. Compare Model Versions steps
2. ER STUDIO  features
   

Warehouse New  concepts

1. 
   
1. 33 )

   How to select which Data warehouse

   Database
1. - Snowflake / Azure  synapse / GCP  bigquery/ AWS Redshift
2.  
3. Data mesh✅
4. Data vault
1. did you work on Data vault project  ✅
2. How did you handle the changes in data model  ✅
3. Datavault faqs
5. Data pipeline✅
1. data pipeline is a set of automated scripts,   that move data from one or more source systems, transform it   and load it into a destination target (such as a data warehouse, or lake house)
6. Data lineage✅ 
1. (tracking data from source to destination in diagram )
7. Data lake    ✅
1. Data Lake is a centralized, highly scalable storage repository that holds a vast amount of raw, unprocessed data in its native format (structured, semi-structured, and unstructured) until it is needed for analytics
8. Data lakehouse and how to design ( DATA BRICKS )✅
   
   
9. Diff between Datalake and Data lakehouse


10. Data warehouse✅
11. Data governance  ✅
1. Data governance is a collection of policies, procedures, standards, roles, and responsibilities that collectively ensure an organization's data is accurate, secure, compliant, and effectively managed throughout its entire lifecycle.
2. Data Governance FAQS
3. HIPAA , GDPR 
12. How to do Data profiling✅
13. ETL FAQ questions
14. ETL & ELT ( when to suggest )
15. Did you work on NO SQL databased AND Cassandra DB

1. Data model implementation phase

1. Best practices   [ 2 ]
2. Data model Optimisation (Heavy data type column [ Varchar( Max) ] problem )
3. SQL query optimization steps 
   
4. Perf tuning
1. Reasons for slow running query 
2. how to tune slow query - Performance tuning questions ..
1. sql server✅
2. oracle ✅
3. mysql✅

1. Data bases 

1. SNOWFLAKE  DATABASE features  [ 15 ]
1. FAQS on SNOWFLAKE DB
2. Snowflake Admin position interview qsns

1. Cloud 

1. AZURE 145
2. AWS 146
3. MS fabric qsns [  5  ]