In relational databases, a snowflake schema is a variation of the star schema where dimension tables are normalized into multiple related tables. Instead of keeping all dimensional attributes in a single wide table, you break them down into a hierarchy of tables that branch out like (you guessed it) a snowflake.
It’s basically what happens when you apply database normalization principles to a star schema’s dimension tables while keeping the central fact table intact.
In data warehousing and analytics systems, dimension tables provide context for the measurements stored in fact tables. While fact tables contain the numbers you want to analyze, dimension tables tell you what those numbers mean. This could be things like who was involved, what product it was, when it happened, where it occurred, etc.
Basically, dimension tables are the “descriptive” side of dimensional modeling, containing the attributes and characteristics that let you filter, group, and label your data in meaningful ways.
In data warehousing and analytics databases, a fact table is where you store the actual measurements and metrics of your business. It’s the table that contains the numbers you want to analyze. This could include things like sales amounts, quantities, counts, durations, or any other quantifiable data points.
Fact tables are the foundation of dimensional modeling, the approach most commonly used for organizing data in data warehouses and business intelligence systems.
If you’ve ever worked with data warehouses or business intelligence systems, you’ve probably encountered star schemas. Perhaps even without realizing it. Star schemas are one of the most common ways to organize data for analytics and reporting.
Star schemas look exactly like their name suggests. They consist of a central table surrounded by related tables, forming a star shape.
Star schemas are designed specifically for querying and analysis rather than transactional operations. They make it easy to slice and dice data in ways that business users actually care about.
There are a couple of obvious reasons you might be seeing an error that reads something like “The specified schema name “XYZ” either does not exist or you do not have permission to use it” in SQL Server. This typically happens when creating an object on a schema that doesn’t exist or you don’t have permissions.
This article shows an example of the error and a couple of ways to fix it, depending on the underlying cause.
In SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.
One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Sometimes when you run a query in SQL Server, you might want to know what the underlying data type of each column is, its precision, length, whether or not its nullable, etc.
If you’re querying just one table, you can get this sort of data with procedures such as sp_columns. But if your query runs across many tables, this could get unwieldy very quickly.
Fortunately there are several ways you can go about getting such metadata for a result set in SQL Server.
Normalization is the process of organizing a database to reduce redundancy and improve data integrity.
Normalization also simplifies the database design so that it achieves the optimal structure composed of atomic elements (i.e. elements that cannot be broken down into smaller parts).
Also referred to as database normalization or data normalization, normalization is an important part of relational database design, as it helps with the speed, accuracy, and efficiency of the database.
