142 ) Types of Dimensions

 

 

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

115 ) about my background and exp

 

HI 

I have total of 16 yrs of exp in IT and 
started my career as manual tester then moved to oracle finance 
and last 6 yrs i am into data modeling 

i worked with companies like .. 

i worked with data modeling toolls .. 

i have experience in domains of finance , banking , insurance and healthcare 

databases i have worked with oracle , mysql , sqlserver , snowflake , netezza , 

datawarehousing tools like Snowflake, Google BigQuery, and Amazon Redshift,

and 

141 ) Roles and Responsibilities of a Data Architect

 

Core Roles and Responsibilities of a Data Architect

A Data Architect is a senior technology leader responsible for defining the overall blueprint of an organization's data infrastructure, management strategy, and data flows. While a data modeler focuses heavily on the structural design of individual databases, a data architect designs the entire enterprise data ecosystem, ensuring scalability, security, integration, and alignment with business goals.

Key Responsibilities

1. Enterprise Data Strategy & Blueprint Design

   • Designs the comprehensive, end-to-end data architecture (including big data layers, data lakes, data warehouses, and streaming pipelines) to support current and future business needs.

   • Defines how data is ingested, stored, processed, integrated, and consumed across the entire organization.

2. Technology & Tool Selection

   • Evaluates, selects, and standardizes database engines, cloud platforms (AWS, Azure, GCP), data integration tools, and analytics stack components.

   • Determines when to implement SQL vs. NoSQL, data warehouses, data lakes, or modern lakehouse architectures (e.g., Databricks/Snowflake).

3. Data Integration & Flow Management

   • Architects robust ETL/ELT pipelines, real-time streaming architectures (e.g., Kafka), and APIs to ensure seamless data movement between disparate source systems, operational apps, and analytical platforms.

   • Eliminates data silos by creating unified integration patterns.

4. Data Governance, Security & Compliance

   • Establishes enterprise-wide frameworks for data governance, data lineage, metadata management, and master data management (MDM).

   • Ensures compliance with regulatory standards (such as GDPR, CCPA, HIPAA) by designing security, encryption, anonymization, and role-based access control policies.

5. Performance, Scalability & Cost Optimization

   • Oversees the scalability, disaster recovery, and high-availability frameworks of database systems.

   • Monitors and optimizes cloud data storage and compute costs to prevent runaway infrastructure spending while maintaining high performance.

6. Cross-Functional Leadership & Collaboration

• Acts as the bridge between executive leadership, software engineering teams, data engineers, and data scientists.

• Mentors data modelers, database administrators (DBAs), and data engineers, ensuring technical alignment with architectural standards.

• 
  

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

Differences Between a Data Architect and a Data Modeler

Feature / Dimension	Data Architect	Data Modeler
Primary Focus	Enterprise-wide vision: Designs the overall blueprint, infrastructure, tech stack, and data flows across the entire organization.	Structural design: Focuses deeply on designing the database schemas, tables, relationships, and attributes for specific applications or data warehouses.
Scope	Macro-level: Encompasses cloud storage, big data layers, data lakes, integration pipelines, security frameworks, and cost optimization.	Micro-level: Encompasses entity-relationship diagrams (ERDs), normalization, data types, primary/foreign keys, and indexes.
Tool Selection	Evaluates and selects enterprise platforms (e.g., AWS, Snowflake, Databricks, Kafka, orchestration tools).	Uses data modeling tools (e.g., ERwin, dbdiagram, Dataedo) to map logical and physical designs.
Governance Role	Defines enterprise governance policies, compliance guardrails (GDPR/HIPAA), data lineage strategy, and master data management (MDM) architecture.	Applies governance standards locally by enforcing naming conventions, data types, constraints, and data integrity rules within models.
Core Deliverables	Architecture diagrams, technology roadmaps, data flow specs, integration patterns, and infrastructure cost models.	Conceptual, logical, and physical data models, DDL scripts, and database dictionaries.
Level of Technical Interaction	High-level strategy combined with systems integration; collaborates with enterprise leadership and software engineers.	Deep database-level technical execution; collaborates closely with data engineers and DBAs.

140 ) Roles and Responsibilities of a Data Modeler

 Roles and Responsibilities of a Data Modeler

A Data Modeler is responsible for designing, structuring, and organizing an organization's data assets so they can be easily, securely, and efficiently stored, retrieved, and analyzed. They act as a bridge between business requirements and technical database architecture.

Key Responsibilities

1. Requirements Gathering & Analysis

   • Translates complex business processes and rules into conceptual and logical data structures.

   • Collaborates with business stakeholders, product managers, and software engineers to understand what data needs to be collected and how it will be used.

2. Database Design (Conceptual, Logical, Physical)

   • Conceptual Design: Maps high-level business entities and relationships (independent of any technology).

   • Logical Design: Defines attributes, primary/foreign keys, and relationships (using normalization rules) without worrying about specific database vendor syntax.

   • Physical Design: Tailors the model to a specific database platform (e.g., PostgreSQL, Snowflake, Oracle), optimizing it for performance, indexing, partitioning, and storage constraints.

3. Data Governance & Standardization

   • Establishes and enforces enterprise-wide data standards, naming conventions, data types, and metadata definitions.

   • Ensures data integrity by implementing constraints, rules, and relationships (like surrogate keys and foreign keys).

4. Performance Tuning & Optimization

   • Works alongside DBAs and data engineers to optimize data structures for either OLTP (fast transactional inserts/updates) or OLAP (fast analytical aggregations, star schemas, dimensional modeling).

   • Determines when to use indexing strategies, denormalization, or partitioning based on query workloads.

5. Schema Evolution & Migration Management

   • Manages changes to existing database structures as business requirements evolve.

   • Utilizes schema comparison and migration tools to ensure smooth version control and deployment across development, staging, and production environments.