Data modeling FAQ -Excel

 

Data governance my notes .pdf

https://docs.google.com/spreadsheets/d/1Dou4e0g5r61INb24nbdDKQFXFjcaTEuk0R-A1h9AHCk/edit#gid=1145721315
sno	Question	Answer	Links
1	Types of Fact tables	1. Transactional Fact Tables Transactional fact tables are designed to capture individual business events or transactions. 2. Periodic Snapshot Fact Tables Periodic snapshot fact tables provide a summarized view of metrics over regular time intervals. 3. Accumulating Snapshot Fact Tables Accumulating snapshot fact tables are designed to track the stages of a business process or workflow. 4. Factless facts	https://easy-data-modeling.blogspot.com/2024/03/types-of-fact-tables.html
45	types of fact tables 51	1. Additive      ( can be added like total qty , sales )  2. Semi additive      ( cannot be added but updated like account balance )   3. Non Additive      ( numerical but cannot be added like today price  of scotk )
2	Granularity and Granule	Granularity in data refers to the level of detail  >  high level of granularity would have a large number of individual pieces (columns) of information, such as individual records or measurements. >Data that has a low level of granularity would have a small number of individual pieces of information, such as summary data or aggregated data. Data granularity can affect how it is used and analyzed, and can impact the accuracy and usefulness of the results.
3	what are 3 levels of Data modeling	1. Conceptual model list of entities and relationships , no attributes diagram only 2. logical  3. physical model
4	Logical and physical model - Differences ?
5	Star schema & Snowflake schema - Differences ?
6	Types of Dimension tables (30, 46,48)	1) SCD  (slow changing ) 2) RCD  (Rapid changing ) 3)  JUNK    4)  INFERRED . 5) DEFERRED  6) CONFORMED (multiple facts )   7) DEGENERATE (invoice)   8) ROLE PLAY (same meaning ,  multiple names)   9) STATIC 10) DYNAMIC   10. SPARSE 11) DENSE  12) SHRUNKEN   1 Roleplay dimension Role playing dimensions are dimensions that are used more than once in a fact table, each time with a different meaning or role.   when we want two attributes from the source entity to be connected to 1 entity as destination, that is not possible. In such cases, we can connect both the columns of one table to 2nd table, but with a new role name that is called as role play dimension something like connecting address and customer table with 2 columns bill to address and ship to address  2 Conformed dimension  Conformed dimensions are dimensions that are shared by multiple stars 3 Degenerate dimension   A column of the key column in the fact table that does not have the associated dimension table but used for reporting and analysis, such column is called degenerate dimension   For ex, we have a fact table with customer_id, product_id, branch_id, employee_id, bill_no 4 Junk dimension This combined dimension table covers all possible set of values which might occur in fact table and is known as junk dimension.5 Shrunken dimension 5 Static dimensions Static dimension items do not change with time; they are fixed components that are always returned in a freeform table. The time , date , country code , status codes etc 6. Dynamic dim which change dynamically based on values and formula 7. Deferred dims  Sometimes the fact table have to be loaded with some data, like customer id. But the dimension of customer is not loaded with that customer id. In such cases, we delay inserting such rows into fact table And later on, once the the dimension of customer is loaded with this customerId, then we add this rows into fact table. This type of delaying the fact table  is called deferred dimensions 8 Inferred Dimensions When the fact data is available to be loaded in the source but related dimension table does not have that eg:  like the customerId is  in sales orders table but not in customer table .. in such case we add  an sample entry in customer table and enable 'inferred dimension' flag and later point of time we make a original entry into customer table and disable the inferred dimension 9 A slowly changing dimension (SCD) scd is a dimension which contains relatively static data which can change slowly but unpredictably, rather than according to a regular schedule     SCD  Type 0 Ignore any changes and audit the changes. Type 1 Overwrite the changes Type 2 History will be added as a new row. Type 3 History will be added as a new column. Type 4 A new dimension will be added Type 6 Combination of Type 2 and Type 3   10 Rapidly changing dimensions (RCD) Rapidly changing dimension (RCD) is a dimension which has attributes where values will be getting changed often. 11) sparse dim Some of the dimension tables are not loaded with full values 12) dense dim Sometimes most of the columns of the dimension tables are loaded, fully loaded. So such dimensions 13) shrunken dim Sometimes we create a replica of the original dimension, but it is with lesser, fewer number of columns than the original table
7	What is factless fact table	A factless Most of the columns are empty. Such dimensions are called sparse dimensions fact table is a table that contains only foreign keys to dimensions, but no numeric facts. Factless fact tables are used to capture events or situations that have no measurable outcome
8	What is Data Mart	Data mart is a subset of datawarehouse  purpose-built to support the analytical needs of a particular department, or line of business, or geographic region.
9	Steps in Datawarehouse life cycle steps	Requirements gathering Requirements analysis High level design (HLD) Low level design (LLD) Development – Automating scripts Testing User Acceptance Testing (UAT) Project release or deployment Warranty support	https://www.linkedin.com/pulse/data-warehouse-project-life-cycle-design-karumanchi-sujatha/?trk=article-ssr-frontend-pulse_more-articles_related-content-card
10	ERWIN qsns
11	Erwin - Data modeler workspace	is the space where the Tables are created and connected
12	Erwin - Domains	A domain is like a custom datatype an independent model object that you can use to quickly assign properties to an attribute or column
13	Erwin - types of relationships	Identifying relationship. Non-identifying relationship. Subtype relationship. Many-to-many relationship. Recursive relationship.
14	Erwin - which erwin features did you use	1. Name hardening 2. Domains for Data types 3. Naming standards( abbrevations) 4. Fwd & reverse engineering 5. Merge data models 6. complete compare 7. sub type and super type
14.1	Erwin - which feature used to merge the subtype tables to supertype
15	Erwin - Forward and reverse engineering steps	Forward engr steps : Actions > schema > Generate scripts Reverse engnr steps : Actions > reverse engnr > logical / physical > Database (or) script file > connect > ok	https://bookshelf.erwin.com/bookshelf/public_html/2019R2/Content/User%20Guides/erwin%20Help/foward_engineering_schema_generation.html
16	Erwin - Complete compare feature	Complete Compare is a powerful tool that lets you view and resolve the differences between two models, or a model and a database or script file
17	Erwin - Naming standards	Use the Naming Standard Editor to create naming standards and develop a glossary that contains glossary words, their abbreviations, and alternate abbreviations. Access the Model Explore> open Naming Standard Editor > Define naming standards for logical and physical models> Create abbreviations
18	Erwin - Name hardening	Name hardening is a mechanism in Erwin Data Modeler that prevents physical model names from changing when logical names change
19	Erwin - querying tool
20	Erwin - subject areas	Click Model, Subject Areas. The Subject Area Editor opens. Click New. A new subject area
21	Erwin - identifying and non identifying relationships differences...........	Identifying : Represented by a solid line, the primary key of the parent migrates to the primary key area of the child entity or table eg : Book & chapter tables , Country and state tables Non-identifying : The child entity is not dependent on the parent entity for its identity and can exist without it .. eg : supplier & product list ,
22	Fact and dimension table - differences	. Fact tables contain numerical data, while dimension tables provide context and background information.	https://www.simplilearn.com/fact-table-vs-dimension-table-article#what_is_a_fact_table
23	What is Datawarehouse	A data warehouse is a centralized system that stores data from various sources for reporting and analysis Data warehouses can: Consolidate data: Store data from multiple sources into a single source of truth Store historical data: Store and analyze long-term historical data Analyze data about a particular subject or functional area:
24	what is ODS( operational data store)	An operational data store usually stores and processes data in real time. An ODS is connected to multiple data sources and pulls data into a central location.
25	OLTP & OLAP- differences (pg 17) 53	https://easy-data-modeling.blogspot.com/2024/03/olap-oltp-diff.html
28
30	what is most likely reason that sql query may run slowly	Possible Reasons for Slow SQL Queries Lack of Indexes: I  Complex Joins:  Large Data Volume:   Inefficient Query Structure:  Database Configuration: Configuration settings such as memory allocation, cache size, and connection limits can affect performance. Server Load: High server load due to concurrent queries or insufficient resources (CPU, memory, I/O) can slow down execution times. Network Latency: If the database is remote, network latency can add to the time it takes to execute a query. Statistics Outdated: Database optimizers rely on statistics to create execution plans.   Locking and Blocking: If a query is waiting for locks held by other transactions, it can slow down execution. Types of Joins and Their Impact on Execution Time INNER JOIN: Generally efficient if both tables have indexes on the join columns.  LEFT JOIN (or RIGHT JOIN): These can be slower than INNER JOINS because they need to return all records f  FULL OUTER JOIN: Typically the slowest join type, as it combines the results o  CROSS JOIN: This join produces a Cartesian product,   It's usually slower and should be used with caution. SELF JOIN: While not inherently slow, performance   Optimization Tips Indexing:  Analyze Execution Plans: Use tools provided by your database (like EXPLAIN in PostgreSQL) to  Refactor Queries: Simplify complex queries,     Monitor Server Performance:
42	what operations can be done on OLAP cube	1) ROLL UP 2) DRILL DOWN 3) SLICE & DICE 4) PIVOT
43	data quality checks -pg 28	1. CONSISTENCY 2. ACCURACY 3. COMPLETENESS 4. AUDITABLITY 5. ORDERLINESS 6. UNIQUENESS 7. TIMELINESS
44	types of indexes 53	1. NORMAL INDEX 2. BITMAP INDEX 3. UNIQUE & NON UNIQUE INDEX 4. CLUSTERED & NON CLUSTERED 5. PARTITIONED & NON PARTITIONED 6. EXPRESSION BASED 7. B- TREE
47	surrogate key (33)	Surrogate key is a unique sequential number key column  Generated by the database operation  not provided by the client it makes it easy for the developers operations
48	Normalization (34)	1NF - There should not be multiple values in each cell 2NF- in 1NF and  All non Non key attributes should be dependent on key attributes and all. 3NF  -  It should be in 2NF  - And  there should not be any transitive dependency
49	sql query : find nth highest salary - 54	Difference between Row_number(), Rank() and Dense_rank() is  In case of value is same then  > Row_number()  gives : sequential number  > Rank  () : Assigns same Rank but the (next rank )= (total previous ranks +1 ) > Dense_Rank() : Assigns same rank and next rank = next sequential number  ========================================= a) Rownum : ( To find 2nd highest salary using ROW_NUMBER) select empid,salary from ( select e.*,row_number() over (order by salary desc) as row_num from emp e ) where row_num = 3 ; ====================== b) using Rank :   -- To show only 2nd highest -- select * from (select e.*,rank() over (order by salary desc) as rank from emp e ) where rank = 2; -- ** To display ranks ** -- select rank() over (order by salary desc) as rank,e.* from emp e ; ====================== c ) Dense Rank ( BEST ) -- To show only 2nd highest -- select * from (select e.*,dense_rank() over (order by salary desc) as rank from emp e ) where rank = 2; -- ** To display ranks ** -- select dense_rank() over (order by salary desc) as rank,e.* from emp e ; ====================== d) using count distinct select empno ,sal from emp e1 where 3 = (select count(distinct sal ) from emp e2 where e2.sal > e1.sal )
50	sql query : find and delete duplicate rows	Two methods to find and delete duplicate rows -================================= 1. Delete all rows other than min id value ..(used when id column is not duplicate but other columns values are duplicate) =========================================== Method 1. using id Column (if ID column is unique, but title is duplicate) ================================ delete films where film_id  not in (select min(film_id) from films group by title, rel_date) =========================================== Method 2. using CTE ( Two steps) ============================================ step 1 : with CTE as (select id ,name , email , row_number() over (partition by name , email order by name , email ) rownum from sales step 2 : Delete from CTE where rownum > 1 =========================================== Method 3. using ROWID ============================================ Delect from table1 where rowid not in (select min(rowid) from table 1 group by col1,col2)
52	what is inner join and outer join count of below A       B 1       1 1       1 2       2 2  3 null   null	Inner join = count of matching rows outer join = inner join + left side unmatched inner = 6 rows left outer = 6 + 1 = 7 rows
53	Which tools used for Data profiling tools	Informatica data profiling Open source data quality profiling
54	Which tools used for 'schema comparision'	1) SQL Server Data Tools (SSDT) 2) SQLDBDiff. : SQLDBDiff is a powerful and intuitive tool that compares the
55	What are ACID properties	Atomicity - each statement in a transaction Either the entire statement is executed, or none of it is executed. Consistency - This means that integrity constraints must be maintained so that the database is consistent before and after the transaction Isolation -Changes occurring in a particular transaction will not be visible to any other transaction until that transaction has been committed Durability - once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and are permanent
56	How to do Data model testing	Data model can tested using the following: Unit testing: This involves testing each component of the data model individually to ensure that it performs as expected. Integration testing: This involves testing how different components of the data model work together using test data
51	What are roles of data modeler 61	get the data from data source identify connect the data source pick a share data set select storage mode identify the query performance issues use or create a data flow profile the data identify data anomalies examine data structures clean transform load data resolve inconsistencies and unexpected values null values data quality issues apply value replacements identify the keys for joints identify and transform column data types apply naming conventions two columns perform data loading resolve data input errors model the data designer data model define the tables configure table and column properties flatten the parent child hierarchy role playing dimensions define relationships and cardinality design the data model resolve many to many relationships design a common date table define the data level granularity
52	what are layers in Datalake	1. landing layer 2. curated layer 3. application data layer ( summary data) 4. Sand box layer 5. Target layer
53	Layers of big data architecture	..
54	Roles of data architect	Data architect tasks and responsibilities Typical responsibilities range from evaluating the current data architecture to keeping databases secure. Depending on your organisation and industry, your day-to-day tasks might include: Translating business requirements into databases, data warehouses, and data streams. Creating procedures to ensure data accuracy and accessibility. Analysing, planning, and defining data architecture framework, including security, reference data, metadata, and master data. Creating and implementing data management processes and procedures. Collaborating with other teams within the organisation to devise and implement data strategies, build models, and assess shareholder needs and goals. Researching data acquisition opportunities. Developing application programming interfaces (APIs) to retrieve data.
56	steps to prepare STTM	Steps Involved in Source to Target Mapping You can map your data from a source of your choice to your desired destination by implementing the following steps: Step 1: Defining the Attributes  Step 2: Mapping the Attributes  Step 3: Transforming the Data  Step 4: Give Standard Naming Conventions Specifics  Step 5: Establish Flows for Data Mapping Step 6: Establish Data Transformation Guidelines  Step 7: Testing the Mapping Process  Step 8: Deploying the Mapping Process  Step 9: Maintaining the Mapping Process   wo
58	Which Azure features did you work
59	Which AWS features did you work
60	What is Data vault modeling and contents
62	steps for Dimensional model
63	Diff. View and materialized view
64	What is data lienage
65	Types of Nosql databases
66	Types of Data models
67	Kimball and inmon model matrix

61

What is Data governance (pg 109 and 116)

Data governance is the process of managing the  availability, usability, integrity and security of the data in enterprise systems, based on internal standards and policies that also control data usage.  Effective data governance ensures that data is consistent and trustworthy and doesn't get misused. Data governance is  increasingly critical as organizations are expanding data privacy regulations and  rely more and more on data analytics  to help optimize operations and  drive business decision-making. https://www.thoughtspot.com/data-trends/data-governance/how-to-implement-data-governance https://data-modeling-made-easy.blogspot.com/2024/08/how-to-implement-data-governance.html

 

Advantages of ELT (Extract, Load, Transform)	ELT (Extract, Load, Transform) adv ELT is better suited for larger data sets, situations where timeliness is crucial, or when you must keep raw data available for future analysis. For instance, ELT is faster than ETL because it loads data directly into the destination system and can perform transformations in similarity.
What is a Data Lake?	What is a Data Lake? Amazon Web Services https://aws.amazon.com › ... › Analytics A data lake is a centralized repository that allows you to store all your structured and unstructured data at any scale. You can store your data as-is,
data lakehouse	A data lakehouse  is a data architecture that combines a data lake and a data warehouse to provide a flexible solution for business intelligence and data science. It allows organizations to use low-cost, flexible storage for all types of data, while also benefiting from the data management, schema, and governance of a warehouse. Data lakes and data warehouses are both storage systems for big data used by business analysts, data engineers, and data scientists. They have different architectures and serve different business needs: Data lakes Store raw, structured, and semi-structured data from internal and external sources. They are good for data analytics, machine learning, experimenting with data, and training AI models. Data lakes prioritize storage volume and cost over performance, and can store any type or volume of data in full fidelity. They also allow users to ingest data from any system at any speed, and process data in real time or batch mode. Data warehouses Store structured data from multiple sources. They are good for business intelligence (BI), data visualizations, reporting, and accessing reliable, quality data. Data warehouses are designed for the fastest query performance, and are optimized to analyze relational data coming from transactional systems and line of business application

SQL : query to show Total salary  of each department	SELECT   Id,   empname,   deptid,   Salary,   SUM(Salary) OVER(PARTITION BY deptid) AS SUM_SAL FROM   emp  ORDER BY    id  =========================== # Id, empname, deptid, Salary, SUM_SAL '1', 'john', '1', '20000', '50000' '2', 'kem', '1', '30000', '50000' '3', 'chan', '2', '10000', '30000' '4', 'henry', '2', '20000', '30000' '5', 'bill', '3', '30000', '30000' '6', 'salt', '4', '20000', '20000'
SQL : query to sum of salary by dept and  cumulative salary by each dept	SELECT  deptid,salary ,          Sum(salary)          OVER(            partition BY deptid            ORDER BY id) AS CUMulative_SAL FROM   emp ORDER  BY id  =============================== # deptid, salary, CUMulative_SAL '1', '20000', '20000' '1', '30000', '50000' '2', '10000', '10000' '2', '20000', '30000' '3', '30000', '30000' '4', '20000', '20000'

=========================================================================

 68. When to use star schema and when to go for snowflake ?

 If you have a few dimensions   and low cardinality (less unique values in dims ) , but you require fast query execution, a star schema is the right choice. 

 However, if you have several dimensions and high cardinality, a snowflake schema will be a better scheme

=========================================================================

69. features of snowflake db

Snowflake can seamlessly integrate with popular cloud providers such as AWS, Azure, and Google Cloud Platform. It can be scaled up or down automatically, ensuring efficient data loading, integration, and analysis. This allows multiple users to run numerous workloads simultaneously without concerns about resources

https://airbyte.com/data-engineering-resources/snowflake-features#:~:text=Snowflake%20can%20seamlessly%20integrate%20with,simultaneously%20without%20concerns%20about%20resources.

https://docs.snowflake.com/en/user-guide/intro-supported-features

https://staragile.com/blog/snowflake-features

A Compact List of Snowflake Features

1. Decoupling of storage and compute in Snowflake
2. Auto-Resume, Auto-Suspend, Auto-Scale
3. Workload Separation and Concurrency
4. Snowflake Administration
5. Cloud Agnostic
6. Semi-structured Data Storage
7. Data Exchange
8. Time Travel
9. Cloning
10. Snowpark
11. Snowsight
12. Security Features
13. Snowflake Pricing

=========================================================================

ERWIN - how maintain versions

Model Versions
A model version records the various developmental changes that the model has undergone. Each time you save any changes in a model, a new model version is created with a sequential version number. Therefore, each version is preserved and serves as a record of each set of changes made in a particular save.

Two types of model versions exist:

Delta Version: A delta version is created when you initially save a new model or when you save changes to an existing model.

If you do not want to maintain multiple versions, you can clear the Maintain multiple versions check box. If you clear the checkbox, then erwin� Data Modeler does not create a delta version for any incremental save. erwin� Data Modeler updates the current version every time you save the model.

Named Version:

A named version is created from a delta version, or from another named version. A named version represents a milestone in the development of the model, or at least a version that you want to keep indefinitely. When you save the changes in a named version, the changes are saved in the same version.

The Based upon field in the Edit Catalog pane displays the version number from which the Named version is created.

=========================================================================

Create a Delta Version

You create a delta version when you save a model initially or when you incrementally save an existing model.

Follow these steps:

1. Click File, Mart, Open.

   The Open Model dialog opens.

2. Select a model and click OK.

   The model opens.

3. Make necessary changes to the model.
4. Click File, Mart, Save.

   A delta version of the model is created with the incremental changes.

=========================================================================

Create a Named Version

A named version of a model represents a milestone in the development of the model. You create a named version to keep that model version indefinitely.

Follow these steps:

1. Click File, Mart, Catalog Manager.

   The Catalog Manager opens.

2. Select a model version, right-click and click Mark Version.

   A named version is created with a default name.

3. Edit the name of the named version and press enter.

   A new named version is created in the catalog.

=========================================================================

Compare Model Versions

Compare two model versions of a data model to view the differences between them.

Follow these steps:

1. Click File, Mart, Catalog Manager.

   The Catalog Manager dialog opens.

2. Hold the Ctrl key and select two model versions of a model and click Version Compare.
3. Click Compare.

   The Complete Compare wizard opens.

4. Follow the instructions on the wizard pages to make your selection.

   The Resolve Difference dialog opens. Review the differences and use the tools on the toolbar to work with and manage the changes.

5. Click Finish.

   The differences in the model versions are resolved and the Resolve Difference dialog closes.

6. Click Close.

   The Complete Compare wizard closes.

=========================================================================

When to use a surrogate key

A surrogate key is a unique identifier for a row in a database table that's often used when a natural key is not suitable as a primary key. Surrogate keys can be useful in many situations, including:

• When natural keys change

  Natural keys can become useless in a data warehouse if they change in the source, such as when migrating to a new system. Surrogate keys, however, don't change while the row exists.
• When natural keys are too long or complex

  Surrogate keys can be simpler than natural keys.
• When natural keys aren't unique

  Surrogate keys can be used to create a unique primary key when natural keys aren't.
• To maintain data warehouse information

  Surrogate keys can help maintain data warehouse information when dimensions change.
• To improve query performance

  Surrogate keys, which are often simple integer values, can provide better performance during data processing and business queries.
• To simplify references

  Surrogate keys can simplify references from one table to another and joins when tables are referenced in queries.
• To deal with slowly changing dimensions

  Surrogate keys can be used to deal with slowly changing dimensions (SCDs), which are attributes of dimension tables that change over 

=========================================================================

what is data lineage ? and tools

Data lineage is the process of tracking the flow of data over time, providing a clear understanding of where the data originated, how it has changed, and its ultimate destination within the data pipeline.

https://www.montecarlodata.com/blog-open-source-data-lineage-tools/

• OpenMetadata
• OpenLineage + Marquez
• Egeria 
• Apache Atlas
• Spline
• The Game’s Afoot

=========================================================================

How do you calculate the size of each table 

What are the datatypes and their Memory size calculation 

https://data-modeling-made-easy.blogspot.com/2024/07/sql-data-types-and-sizes.html

=========================================================================

What is Data governance

• Data governance is 
• a set of processes, policies, roles, metrics, and standards  
• that help  management processes to keep 
• data secure, private, accurate, and usable throughout its life cycle. 

=========================================================================

Contents of Data Governance :

1. Data Architechture Management
2. Data Development
3. Database operations Management
4. Data security Management
5. Reference & Master data Management
6. DWH & BI Management
7. Document and content Management
8. Meta Data Management
9. Data Quality Management

=========================================================================

Data Governance Framework Steps :

=========================================================================

Goals	Methods	People	Processes	Technology	culture
Security	Policies	sponsor	Issues Management	ingestion	collaboration
privacy	Guides	owner	change Management	cataloging	Crowd sourcing
compliance	Guardrails	steward	quality Management	Data preparation	communication
Quality	Gates	curator	cataloging	Data analysis	Sharing
Integration	Code of Ethics	coach	Measurement	Pipeline Management	Reuse
Metadata	curating	consumer	Monitoring
Retention	coaching	Stakeholder
Risk
Impact

=========================================================

Explain Gdpr & Hipaa in data governance? 

• HIPAA  (Health Insurance Portability and Accountability Act) is United States legislation) 

What are the primary HIPAA goals?

• To limit the use of protected health information to those with a “need to know”
• To penalize those who do not comply with confidentiality regulations

What health information is protected?

• Any healthcare information with an identifier that links a specific patient to healthcare information (name, social security number, telephone number, email address, street address, among others)

https://www.techtarget.com/searchhealthit/definition/HIPAA

https://www.ncbi.nlm.nih.gov/books/NBK500019/

 

• GDPR (The General Data Protection Regulation )

https://en.wikipedia.org/wiki/General_Data_Protection_Regulation

https://gdpr.eu/what-is-gdpr/

The General Data Protection Regulation (GDPR) is a European law that protects individuals' personal information and their fundamental rights and freedoms

=========================================================================

What is Medallion architechture ?

https://www.blogger.com/blog/posts/6964310590349567722

===============================================================

 

===============================================================

where do we use data vault modeling

A data vault is a data modeling approach and methodology used in enterprise data warehousing to handle complex and varying data structures. It combines the strengths of 3rd normal form and star schema.

===============================================================

SQL : Department wise highest salary 

  select deptid,max(salary) from emp group by deptid;

===============================================================

>> To display emp name and id of each department 

 

SELECT deptid, id,empname,  salary FROM emp

  WHERE (deptid,salary) IN 

(

  select deptid,max(salary) from emp group by deptid

)

===============================================================

===============================================================

Parquet data types list

https://data-modeling-made-easy.blogspot.com/2024/10/parquet-datatypes.html

===============================================================

Types of Fact tables

 At its core, a fact table consists of the following components:

1. Primary Keys: These are unique identifiers that uniquely identify each row or record in the fact table.

2. Foreign Keys: These are references to dimension tables that provide context and additional information about the measures in the fact table.

3. Measures: These are the numerical values that we want to analyze. e.g., it can represent sales revenue, customer visits, inventory levels, or any other quantifiable data.

The combination of primary keys, foreign keys, and measures forms the foundation of a fact table, enabling powerful data analysis. In fact, we have three types: Transaction Fact Tables, Periodic Snapshot Tables, and Accumulating Snapshot Tables.

Transactional Fact Tables

Transactional fact tables are designed to capture individual business events or transactions. These tables are particularly useful for analyzing customer behavior, sales patterns, and operational efficiency. Let’s take a closer look at a sample transactional fact table to better understand its structure:

[Transactional Fact Table]

In this example, each row represents a unique transaction, with the transaction ID serving as the primary key. The customer ID and product ID columns are foreign keys that reference the respective dimension tables. The quantity sold and sales amount are the measures that quantify the transaction details.

Transactional fact tables enable us to perform various data analyses:

1. Analyzing Customer Behavior: By grouping transactions by customer ID, we can gain insights into individual customer preferences, buying patterns, and customer lifetime value.

2. Identifying Popular Products: By aggregating sales quantity or sales amount by product ID, we can determine the popularity of different products, identify best-sellers, and optimize inventory management.

3. Tracking Sales Trends: By examining the transactional data over time, we can identify sales trends and seasonal fluctuations and make informed decisions about pricing, promotions, and marketing strategies.

Note: Transactional fact tables provide granular details that allow for in-depth analysis and actionable insights.

Periodic Snapshot Fact Tables

Periodic snapshot fact tables provide a summarized view of metrics over regular time intervals. These tables are particularly useful for monitoring performance, identifying trends, and measuring progress. Let’s explore a sample periodic snapshot fact table:

[Periodic Snapshot Fact Table]

In this example, each row represents a specific time period, such as a month ID, with the Month ID column serving as the primary key. The total sales, revenue, and number of customers are the measures that provide an overview of the performance during each month.

Periodic snapshot fact tables gives different analytical use cases:

1. Monitoring Performance: By analyzing the total sales and revenue over different time periods, we can evaluate the overall performance of the business and track progress towards goals.

2. Identifying Trends: By comparing metrics across months or quarters, we can identify growth trends, seasonality patterns, and fluctuations in customer demand.

3. Decision-making: Based on the insights derived from periodic snapshot fact tables, businesses can make data-driven decisions regarding marketing campaigns, resource allocation, and inventory management.

Note: Periodic snapshot fact tables offer a high-level view of performance and allow for easy comparisons and trend analysis.

Accumulating Snapshot Fact Tables

Accumulating snapshot fact tables are designed to track the stages of a business process or workflow. These tables provide valuable insights into process efficiency, identifying bottlenecks, and optimizing operations. Let’s examine a sample accumulating snapshot fact table:

[Accumulating Snapshot Fact Table]

In this example, each row represents a specific order, with the order ID serving as the primary key. The order date, received date, stocked date, and sold date represent the different stages of the order fulfillment process.

This fact tables enable various practical applications, including:

1. Monitoring Order Fulfillment: By tracking the time it takes for orders to move through each stage, businesses can identify bottlenecks in the fulfillment process and streamline operations.

2. Tracking Inventory: By analyzing the time it takes for products to move from receiving to stocking to selling, businesses can optimize inventory management and ensure timely replenishment.

3. Evaluating Supply Chain Performance: By examining the accumulation of time across different stages, businesses can assess the overall efficiency of their supply chain and identify areas for improvement.

Note: Accumulating snapshot fact tables provide a comprehensive view of the entire process, facilitating process optimization and operational efficiency.

Factless Fact Tables

A “factless fact table” is a type of table in a data warehouse or database that captures relationships and events without containing any numerical or measurable facts. Unlike traditional fact tables that store quantitative data, a factless fact table focuses solely on the associations or occurrences between different dimensions or entities.

The purpose of a factless fact table is to track and analyze the presence or absence of certain events or combinations of events. It enables the identification of patterns, trends, and relationships based on the occurrences or non-occurrences of specific events across different dimensions.

Typically, a factless fact table consists of foreign keys referencing various dimension tables as well as any additional descriptive attributes that provide context to the relationships being captured. The absence of numerical measures distinguishes it from other types of fact tables.

A common example of a factless fact table is a “sales pipeline” table in sales analytics. It captures the stages of a sales opportunity (e.g., prospecting, qualification, and negotiation) and the associated timeframes but does not include any monetary values or quantities. This allows analysts to analyze the sales process, track conversion rates, identify bottlenecks, and measure the effectiveness of different sales strategies without focusing on specific sales figures.

A factless fact table serves as a bridge between dimensions, capturing events or associations without numerical measures. It provides valuable insights into relationships, patterns, and occurrences, helping analysts understand the context and behavior of data across different dimensions.

What is granularity? and granule

 

What is granularity?

Granularity in data refers to the level of detail or precision of the data. For example, data that has a high level of granularity would have a large number of individual pieces of information, such as individual records or measurements. Data that has a low level of granularity would have a small number of individual pieces of information, such as summary data or aggregated data. Data granularity can affect how it is used and analyzed, and can impact the accuracy and usefulness of the results.

Most common KPI list

 Below are the 100 important business KPI examples to track & measure.

• Marketing Key Performance Indicators
• Sales & Retail Key Performance Indicators
• Key Performance Indicator Examples for Operations
• Project Management Key Performance Indicators

https://www.investopedia.com/terms/k/kpi.asp

https://clickup.com/blog/kpi-examples/

In this list of key performance indicators examples, we’re going to look into four different categories of business metrics:

• Sales
• Financial
• Project Management
• Marketing

REMEMBER: Only measure the KPIs that are relevant to YOUR company and business goals. You’ll probably only need 20 KPIs out of this entire list.

Examples of sales key performance indicators:

1. Monthly sales growth

2. Monthly sales/new customers

3. Monthly new leads/prospects

4. Number of qualified leads

5. Resources spent on one non-paying client

6. Resources spent on one paying client

7. Customer lifetime value/customer profitability

8. Lead-to-sale conversion rate

9. Cost per lead by each channel

10. Cost of a new client by each channel

11. Hourly, daily, weekly, monthly, quarterly, and annual sales

12. Average conversion time

13. Lead-to-close rate: all channels

14. Customer turnover rate

15. Number of monthly sales demos

16. Customer engagement level

17. Number of abandoned shopping carts

18. Shopping cart abandonment rate

19. Number of monthly quotes/orders

20. Average purchase value

21. Average order value

22. Sales per representative

23. Sales by lead source

24. Inbound calls handled per representative

25. Outbound calls handled per representative

26. Average annual sales volume per customer

27. Average monthly sales volume per customer

28. Relative market share

29. Product/service usage every day

30. Value of returned goods and warranties

31. Asset turnover ratio (sales to assets)

32. Percentage of total sales from existing customers

33. Sales reps per $100k in revenue

34. Monthly sales quota attainment

35. Sales quota attainment by the sales representative

36. Number of client accounts per account manager

37. Days sales outstanding

Examples of financial key performance indicators:

38. Net profit margin

39. Operating cash flow (OCF)

40. Current ratio

41. Quick ratio / Acid test

42. Net profit margin

43. Working capital

44. Current accounts receivable

45. Current accounts payable

46. Accounts payable turnover

47. Accounts receivable turnover

48. Accounts payable process cost

49. Accounts receivable turnover

50. Budget variance

51. Budget creation cycle time

52. Line items in the budget

53. Number of budget iterations

54. Payroll headcount ratio

55. Vendor expenses

56. Payment error rate

57. Internal audit cycle time

58. Finance error report

59. Debt to equity ratio

60. Return on equity

61. Cost of managing business

62. Resource utilization

63. Total cost of the finance function

Read more about each financial KPI

Examples of project management key performance indicators:

64. Planned value (PV)

65. Actual cost (AC)

66. Earned value (EV)

67. Cost variance (CV) (planned budget vs. actual budget)

68. Schedule variance (SV)

69. Schedule performance index (SPI)

70. Cost performance index (CPI)

71. Planned hours of work vs. actual situation

72. Overdue project tasks / crossed deadlines

73. % of overdue project tasks

74. Missed milestones

75. Percentage of projects completed on time

76. Percentage of cancelled projects

77. Percentage of projects on budget

78. Number of budget iterations

79. Percentage of tasks completed

80. Project resource utilization

81. Cost of managing processes

82. Return on investment (ROI)

Here’s a guide that explains 16 project KPIs

Examples of marketing key performance indicators:

83. Monthly new leads/prospects

84. Qualified leads per month

85. Marketing qualified leads (MQL)

86. Sales-accepted leads (SAL)

87. Sales qualified leads (SQL)

88. Cost per lead generated

89. Net promoter score

90. Cost per conversion

91. Cost per conversion by channel

92. Average time of conversion

93. Retention rate

94. Attrition rate

95. Monthly website traffic

96. Traffic from organic search

97. Returning vs. new visitors

98. Visits per channel

99. Average time on page

100. Click-through rate on web pages

101. Pages per visit

102. Conversion rate for call-to-action content

103. Inbound links to website

104. Traffic from organic search

105. New leads from organic search

106. New leads from organic search

107. Number of unique keywords that drive traffic

108. Keywords in top 10 SERP

109. Rank increase of target keywords

110. Conversion rate per keyword

111. Page authority

112. Google PageRank

113. Volume of traffic from video content

114. Leads & conversions from paid advertising

115. Number of monthly PPC campaigns

116. Cost per acquisition (CPA) & cost per conversion (CPC)

117. Click-through rate on PPC advertising

118. Traffic from social media

119. Number of leads from social media

120. Number of conversions from social media

121. Conversion rate for social media leads

122. Managed audience size

123. Engagement rate

124. Social media mentions

125. Social media ROI

126. Content quality on blog

127. Number of monthly blog visits

128. Blog articles published this month

129. E-books published this month

130. Infographics published this month

131. ROI per content type

132. Web traffic from PR campaigns

133. Number of clippings

134. Calls from PR campaigns

135. Media impressions from PR campaigns

Star schema and Snowflake schema - Differences ?

 

S.NO	Star Schema	Snowflake Schema
1.	In star schema, The fact tables and the dimension tables are contained.	While in snowflake schema, The fact tables, dimension tables as well as sub dimension tables are contained.
2.	Star schema is a top-down model.	While it is a bottom-up model.
3.	Star schema uses more space.	While it uses less space.
4.	It takes less time for the execution of queries.	While it takes more time than star schema for the execution of queries.
5.	In star schema, Normalization is not used.	While in this, Both normalization and denormalization are used.
6.	It’s design is very simple.	While it’s design is complex.
7.	The query complexity of star schema is low.	While the query complexity of snowflake schema is higher than star schema.
8.	It’s understanding is very simple.	While it’s understanding is difficult.
9.	It has less number of foreign keys.	While it has more number of foreign keys.
10.	It has high data redundancy.	While it has low data redundancy.

Data modeling interview FAQs list

 

1. what are 3 levels of Data modeling 
1. Conceptual 
2. logical
3. physical model
   
2. Types of Fact tables 
1. 
   
3. Logical and physical model - Differences ?
4. Star schema & Snowflake schema - Differences ?
5. Granularity and Granule
6. Types of Dimension tables
7. What is factless fact table
8. What is Data Mart
9. Steps in Datawarehouse life cycle steps
   
   
10. ERWIN qsns
1. Erwin - Data modeler workspace
2. Erwin - Domains
3. Erwin - types of relationships
4. Erwin - indexes how
5. Erwin - Forward and reverse engineering steps
6. Erwin - Complete compare feature
7. Erwin - Naming standards
8. Erwin - Name hardening
9. Erwin - querying tool
10. Erwin - subject areas
11. Erwin - identifying and non identifying relationships differences
11. Fact and dimension table - differences
12. What is Datawarehouse
13. what is ODS( operational data store)
14.  OLTP & OLAP- differences (pg 17) 53
15. What are SQL performance tuning measures & tips
16. what are bottlenecks which effects database performance
17. how do you tune a query using explain plan
18. what is summary advisor
19. what is most likely reason that sql query may run slowly
20. what is latch free event
21. what is proactive tuning and reactive tuning
22. what is Rule based optimiser and cost based optimizer
23. list Perf tuning enhancements in oracle
24. how do you confirm that table has disproportionate fragmentation
25. what can you do to optimize %xyz" queries 
26. where does input output statistics of table exist in oracle
27. when is right time to rebuiled index
28. what information does STATSPACK report give 
29.  redo,rollback , undo- differences
30. Aggregate and summarized tables  - differences
31. what operations can be done on OLAP cube
32. data quality checks -pg 28
33. types of indexes 53
34. types of fact tables 51
35. types of dimension tables(30, 46,48)
36. surrogate key (33)
37. Normalization (34)
38. sql query : find nth highest salary - 54
39. sql query : find and delete duplicate rows
40. What are roles of data modeler 61
41. Most common KPI list 
42. Which Data profiling tools did you use
1. Informatica data profiling
2. Open source data quality profiling
43.