Data Warehouse Schema | Different Types of Data Warehouse Schema (2023)

Data Warehouse Schema | Different Types of Data Warehouse Schema (1)

Introduction to Data warehouse Schema

The Data Warehouse Schema is a structure that rationally defines the contents of the Data Warehouse, by facilitating the operations performed on the Data Warehouse and the maintenance activities of the Data Warehouse system, which usually includes the detailed description of the databases, tables, views, indexes, and the Data, that are regularly structured using predefined design types such as Star Schema, Snowflake Schema, Galaxy Schema (also known as Fact Constellation Schema).

All in One Data Science Bundle (360+ Courses, 50+ projects) 360+ Online Courses | 1500+ Hours | Verifiable Certificates | Lifetime Access 4.7 (21 ratings) View Course

A schema is a logical description that describes the entire database. In the data warehouse there includes the name and description of records. It has all data items and also different aggregates associated with the data. Like a database has a schema, it is required to maintain a schema for a data warehouse as well. There are different schemas based on the setup and data which are maintained in a data warehouse.

Types of Data Warehouse Schema

Following are the three major types of schemas:

  • Star Schema
  • Snowflake Schema
  • Galaxy Schema

There are fact tables and dimension tables that form the basis of any schema in the data warehouse that are important to be understood. The fact tables should have data corresponding data to any business process. Every row represents any event that can be associated with any process. It stores quantitative information for analysis. A dimension table stores data about how the data in fact table is being analyzed. They facilitate the fact table in gathering different dimensions on the measures which are to be taken.

Let us have a look at all these in detail.

(Video) Understanding Schemas in Datawarehousing | Edureka

1. Star Schema

Here are some of the basic points of star schema which are as follows:

  • In a star schema, as the structure of a star, there is one fact table in the middle and a number of associated dimension tables. This structure resembles a star and hence it is known as a star schema.
  • The fact table here consists of primary information in the data warehouse. It surrounds the smaller dimension lookup tables which will have details for different fact tables. The primary key which is present in each dimension is related to a foreign key which is present in the fact table.
  • This infers that fact table has two types of columns having foreign keys to dimension tables and measures which contain numeric facts. At the center of the star, there is a fact table and the points of the star are the dimension tables.
  • The fact tables are in 3NF form and the dimension tables are in denormalized form. Every dimension in star schema should be represented by the only one-dimensional table. The dimension table should be joined to a fact table. The fact table should have a key and measure.

2. Snowflake Schema

Here are some of the basic points of snowflake schema which are as follows:

  • Snowflake schema acts like an extended version of a star schema. There are additional dimensions added to Star schema. This schema is known as snowflake due to its structure.
  • In this schema, the centralized fact table will be connected to different multiple dimensions. The dimensions present are in normalized form from the multiple related tables which are present. The snowflake structure is detailed and structured when compared to star schema.
  • There are multiple levels of relationships and child tables involved that have multiple parent tables. In snowflake schema, the affected tables are only the dimension tables and not the fact tables.
  • The difference between star and snowflake schema is that the dimensions of snowflake schema are maintained in such a way that they reduce the redundancy of data. The tables are easy to manage and maintain. They also save storage space.
  • However, due to this, it is needed to have more joins in the query in order to execute the query. The further expansion of the tables leads to snowflaking. When a dimension table has a low cardinality attribute of dimensions then it is said to be snowflaked.
  • The dimension tables have been divided into segregated normalized tables. Once they are segregated they are further joined with the original dimension table which has a referential constraint. This schema may hamper the performance as the number of tables that are required are more so that the joins are satisfied.
  • The advantage of snowflake schema is that it uses small disk space. The implementation of dimensions is easy when they are added to this schema. The same set of attributes are published by different sources.

3. Fact Constellation Schema or Galaxy Schema

Here are some of the basic points offact constellation schema which are as follows:

  • A fact constellation can consist of multiple fact tables. These are more than two tables that share the same dimension tables. This schema is also known as galaxy schema.
  • It is viewed as a collection of stars and hence the name galaxy.The shared dimensions in this schema are known as conformed dimensions. The dimensions in this schema are separated into segregated dimensions which are having different levels of hierarchy.
  • As an example, we can consider the four levels of hierarchy taking geography into consideration as region, country, state, and city. This galaxy schema has four dimensions. Another way of creating a galaxy schema is by splitting one-star schema into more star schemas.
  • The dimensions created as large and built on the basis of hierarchy. This schema is useful when aggregation of fact tables is necessary. Fact constellations are considered to be more complex than star and snowflake schemas. These are considered to be more flexible but hard to implement and maintain.
  • This type of schema is usually used for sophisticated applications. The multiple number of tables present in this schema makes it difficult and complex. Implementing this schema is hence difficult. The architecture is thus more complex when compared to star and snowflake schema.

Conclusion

Like the databases have relational schemas where all data is saved and maintained in the form of schemas, the data warehouse also uses the same concept to maintain the data. Having schemas makes it easier to maintain the data. There are three main types of schemas as discussed above. They mainly operate on fact tables and dimension tables. The star schema is the easiest of all schemas. It consists of one fact table surrounded by multiple dimension tables. The snowflake schema has multiple dimension tables that are in normalized form. The last type consists of multiple fact tables. All three schemas segregate data and help in filtering and managing data in an efficient way. These schemas thus play a major role in setting up any environment. The performance of queries can also be enhanced by using these schemas.

Recommended Articles

This is a guide to Data Warehouse Schema. Here we discuss the different types ofdata warehouse schema such as star, snowflake, and factconstellation schema in detail. You may also look at the following articles to learn more-

Popular Course in this category

All in One Data Science Bundle (360+ Courses, 50+ projects)360+ Online Courses|1500+ Hours|Verifiable Certificates|Lifetime Access 4.7 Course Price View Course
(Video) DATA WAREHOUSE SCHEMA| Star | Snowflake | Fact Constellation
  1. Why We Need Data Analysis?
  2. How to generate Test Data?
  3. Why Should You Work on GDS?
  4. Types of Data Mart
  5. Guide to Examples of Primary Key in SQL
All in One Data Science Bundle (360+ Courses, 50+ projects) 360+ Online Courses 1500+ Hours Verifiable Certificates Lifetime AccessLearn More

0 Shares

(Video) Data Warehouse Schema

FAQs

What are the types of data warehouse schema? ›

Following are the three major types of schemas: Star Schema. Snowflake Schema. Galaxy Schema.

What is schema and types of schemas? ›

In computer programming, a schema (pronounced SKEE-mah) is the organization or structure for a database, while in artificial intelligence (AI) a schema is a formal expression of an inference rule.

How many types of schemas are there? ›

There are four basic types of schemas that help to understand and interpret the world around us.

What is data warehouse schema explain with example? ›

A schema is defined as a logical description of database where fact and dimension tables are joined in a logical manner. Data Warehouse is maintained in the form of Star, Snow flakes, and Fact Constellation schema.

What are the 3 types of database schema? ›

Schema is of three types: Logical Schema, Physical Schema and view Schema. Logical Schema – It describes the database designed at logical level. Physical Schema – It describes the database designed at physical level. View Schema – It defines the design of the database at the view level.

What type of data is a schema? ›

There are two main kinds of database schema: A logical database schema conveys the logical constraints that apply to the stored data. It may define integrity constraints, views, and tables. A physical database schema lays out how data is stored physically on a storage system in terms of files and indices.

What are the 5 schemas? ›

The Five Schema Domains Defined
  • Abandonment/Instability.
  • Mistrust/Abuse.
  • Emotional Deprivation.
  • Defectiveness/Shame.
  • Social Isolation/Alienation.

What are the 8 types of schemas? ›

How many schemas are there?
  • Connecting.
  • Orientation.
  • Transporting.
  • Trajectory.
  • Positioning.
  • Enveloping.
  • Enclosing.
  • Rotation.
30 May 2022

What are the 6 types of schemas? ›

There are many types of schemas, including object, person, social, event, role, and self schemas. Schemas are modified as we gain more information.

What schema means? ›

plural schemata ˈskē-mə-tə also schemas. : a diagrammatic presentation. broadly : a structured framework or plan : outline. : a mental codification of experience that includes a particular organized way of perceiving cognitively and responding to a complex situation or set of stimuli.

What are schemas used for? ›

schema, in social science, mental structures that an individual uses to organize knowledge and guide cognitive processes and behaviour. People use schemata (the plural of schema) to categorize objects and events based on common elements and characteristics and thus interpret and predict the world.

What is schema short answer? ›

The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database.

Which data warehouse schema is best? ›

#1) Star Schema

This is the simplest and most effective schema in a data warehouse. A fact table in the center surrounded by multiple dimension tables resembles a star in the Star Schema model.

What is a data warehouse used for? ›

A data warehouse is a central repository of information that can be analyzed to make more informed decisions. Data flows into a data warehouse from transactional systems, relational databases, and other sources, typically on a regular cadence.

What are the 5 components of data warehouse? ›

What are the key components of a data warehouse? A typical data warehouse has four main components: a central database, ETL (extract, transform, load) tools, metadata, and access tools. All of these components are engineered for speed so that you can get results quickly and analyze data on the fly.

What are the 4 databases? ›

What Are The 4 Types Of Database Management Systems?
  • Relational database.
  • Object-oriented database.
  • Hierarchical database.
  • Network database.
9 May 2022

What are 3 database examples? ›

Some examples of popular database software or DBMSs include MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database, and dBASE.

What is a schema example? ›

Schemas (or schemata) are units of understanding that can be hierarchically categorized as well as webbed into complex relationships with one another. For example, think of a house. You probably get an immediate mental image of something out of a kid's storybook: four windows, front door, suburban setting, chimney.

What is a schema design? ›

Database schema design organizes the data into separate entities, determines how to create relationships between organized entities, and how to apply the constraints on the data. Designers create database schemas to give other database users, such as programmers and analysts, a logical understanding of the data.

Is a schema a data model? ›

A Data Model Schema defines how data points are organized and connected within a Relational Database, including logical constraints like table names, fields, data types, and the relationships between these entities.

What are the 9 schemas? ›

There are nine most common play schemas: Connection, Enclosure, Enveloping, Orientation, Positioning, Rotation, Trajectory, Transforming, and Transporting.

What are the four types of schema? ›

Four schemata help us makes sense of experiences: prototypes, personal constructs, stereotypes and scripts.

How schemas are formed? ›

Schemas are acquired and constructed through experiences with specific instances. Physiologically speaking, they start as simple networks and develop into more complex structures.

What are the 12 schemas? ›

List of Schemas
  • Emotional Deprivation: The belief and expectation that your primary needs will never be met. ...
  • Abandonment: ...
  • Mistrust/Abuse: ...
  • Defectiveness: ...
  • Vulnerability: ...
  • Dependence/Incompetence: ...
  • Enmeshment/Undeveloped Self: ...
  • Failure:

What is a database schema? ›

A database schema is considered the “blueprint” of a database which describes how the data may relate to other tables or other data models. However, the schema does not actually contain data. A sample of data from a database at a single moment in time is known as a database instance.

What are the 3 domains of schema theory? ›

Other-Directedness includes 3 schemas: Subjugation. Self-Sacrifice. Approval-Seeking/Recognition-Seeking.

What are the 3 levels of schema architecture? ›

Explain the three level schema architecture in DBMS?
  • External level.
  • Conceptual level.
  • Internal level.
3 Jul 2021

Why is schema so important? ›

Schema is a mental structure to help us understand how things work. It has to do with how we organize knowledge. As we take in new information, we connect it to other things we know, believe, or have experienced. And those connections form a sort of structure in the brain.

What is scheme in SQL? ›

What is Schema in SQL? In a SQL database, a schema is a list of logical structures of data. A database user owns the schema, which has the same name as the database manager. As of SQL Server 2005, a schema is an individual entity (container of objects) distinct from the user who constructs the object.

Why schema is used in SQL? ›

A SQL schema is a useful database concept. It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.

What is schema change? ›

What Does Schema Change Mean? A schema change is an alteration made to a collection of logical structures (or schema objects) in a database. Schema changes are generally made using structured query language (SQL) and are typically implemented during maintenance windows.

What is schema and their properties? ›

A schema defines a set of types or classes. Each type has a set of properties. A property either has an associated property type or is a group of properties. Properties with a property type have a structure according to that type's definition, they may have a value and properties of themselves.

Which schema has better performance? ›

The Star schema is in a more de-normalized form and hence tends to be better for performance. Along the same lines the Star schema uses less foreign keys so the query execution time is limited.

What is the most common type of schema in data analysis? ›

Recently, the most popular way of adding schema has been through JSON schema where Schema is created through a <script> tag and then added to a site, app, etc.

Which of the following schema is mostly used in the data warehouse? ›

Star schema is the fundamental schema among the data mart schema and it is simplest. This schema is widely used to develop or build a data warehouse and dimensional data marts. It includes one or more fact tables indexing any number of dimensional tables. The star schema is a necessary cause of the snowflake schema.

Why is warehouse important? ›

Warehousing enables you to store, ship, and distribute your goods from one single location. This makes it easy for you to track and manage your inventory efficiently. It can additionally reduce your transportation costs, increase your flexibility and reduce your staffing needs.

What is data warehouse Mcq? ›

A system that is used to run the business in real time and is based on historical data.

What is data warehouse design? ›

A data warehouse is a single data repository where a record from multiple data sources is integrated for online business analytical processing (OLAP). This implies a data warehouse needs to meet the requirements from all the business stages within the entire organization.

What are the 3 characteristics of data warehouse? ›

The Key Characteristics of a Data Warehouse

Large amounts of historical data are used. Queries often retrieve large amounts of data. Both planned and ad hoc queries are common.

What is the basic 4 features about data warehousing? ›

Data warehouses are characterized by being:

These may include a cloud, relational databases, flat files, structured and semi-structured data, metadata, and master data. The sources are combined in a manner that's consistent, relatable, and ideally certifiable, providing a business with confidence in the data's quality.

What are the three main types of data warehouse? ›

The three main types of data warehouses are enterprise data warehouse (EDW), operational data store (ODS), and data mart.

What are the 4 different types of data models? ›

The three primary data model types are relational, dimensional, and entity-relationship (E-R). There are also several others that are not in general use, including hierarchical, network, object-oriented, and multi-value.

What is the 3 schema architecture? ›

A framework for managing access to data that involves three layers or schemas: the external or programming view, the conceptual or data administration view, and the internal or database administration view.

What is called a schema? ›

The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database.

What are the 7 types of data? ›

And there you have the 7 Data Types.
  • Useless.
  • Nominal.
  • Binary.
  • Ordinal.
  • Count.
  • Time.
  • Interval.
29 Aug 2018

What are the 3 types of data types? ›

There are Three Types of Data
  • Short-term data. This is typically transactional data. ...
  • Long-term data. One of the best examples of this type of data is certification or accreditation data. ...
  • Useless data. Alas, too much of our databases are filled with truly useless data.

What is schema level? ›

The Schema is the Structure of the database and it represents logical constraints such as Table, key, etc. To break direct contact of user and the database, Three Schema Architecture was introduced and it consists of three levels: View, Logical and Physical Level.

What is a 3 layer database architecture? ›

A three-tier architecture is a client-server architecture in which the functional process logic, data access, computer data storage and user interface are developed and maintained as independent modules on separate platforms.

Why is the three schema important? ›

Three-schema architecture allows each person in an SMB to access the same database with a different personalized view of the data contained within it. This architecture also allows an IT administrator to change the database's structure without affecting other users on the system.

What are the 3 levels of data abstraction? ›

Levels of abstraction for DBMS
  • Physical or Internal Level.
  • Logical or Conceptual Level.
  • View or External Level.
8 Jul 2021

Videos

1. star schema and snowflake schema in data warehouse
(ETL-SQL)
2. Data ware house schemas
(IMSUC FLIP)
3. Star and SnowFlake Schema in Data Warehouse
(Informatica Interview)
4. Data Warehouse Schemas- Star, Snowflake, Fact Constellation | Lecture #11 | Data Warehouse Tutorial
(AmpCode)
5. Datawarehouse Schemas - Star and Snowflake
(Learn at Knowstar)
6. Understanding Schemas in Data warehouse | Dimensional Modelling
(DataEngineering, BI and Analytics with Sapna)
Top Articles
Latest Posts
Article information

Author: Neely Ledner

Last Updated: 12/07/2022

Views: 6308

Rating: 4.1 / 5 (62 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Neely Ledner

Birthday: 1998-06-09

Address: 443 Barrows Terrace, New Jodyberg, CO 57462-5329

Phone: +2433516856029

Job: Central Legal Facilitator

Hobby: Backpacking, Jogging, Magic, Driving, Macrame, Embroidery, Foraging

Introduction: My name is Neely Ledner, I am a bright, determined, beautiful, adventurous, adventurous, spotless, calm person who loves writing and wants to share my knowledge and understanding with you.