Data Principles: Building Bridges with Auxiliary Keys


When designing your data model, especially in data warehousing or analytical systems, you might opt for identity columns as primary keys. This is a common and often advantageous practice due to the inherent benefits of identity columns. However, relying solely on these generated keys can create a disconnect with how the business views and interacts with data. This is where the data principle of utilizing auxiliary keys becomes crucial.

Identity Columns: The Physical Key to Optimization

Before we dive into how the principle of using auxiliary keys works, we have to define what we would use as a primary key.  In the world of data warehousing, it is common practice to use identity columns as the primary key of every table.  These auto-incrementing integer columns offer several advantages in database design, but as we will see, they do have flaws.  Some advantages are:

Compact Storage: Identity columns are generally small, fixed-size data types (like integers), which makes them efficient for storing and indexing.

Faster Joins: Databases are optimized for joining on numerical keys. Using identity columns as primary keys often leads to faster join operations between tables.

Reduced Fragmentation: When new data is inserted, using an auto-incrementing identity column as the primary key often results in new records being appended to the end of the table, minimizing fragmentation and improving overall database performance.

These all sound like superpowers, so what is the kryptonite of an identity column? They are arbitrary numbers with no inherent business meaning.  If you were to ask a business user what value they are looking for in a dimension table that holds a list of colors, they would not say 1001, they would say "Blue".  

The Business Perspective: Beyond the Numbers

The fact is, it does not matter how efficiently the database returns data if the business does not know how to interact with it. Business users typically query and analyze data based on relevant business metrics like customer names, product codes, or dates. Using complex WHERE clauses with these business-oriented columns can lead to slower query performance if there isn't an efficient way to access the data. This is where auxiliary keys come into play.

Auxiliary Keys: The Business-Friendly Access Point

Auxiliary keys, also known as alternate keys, are columns that are important to the business and are frequently used in queries or for data retrieval. By creating indexes on these auxiliary keys, you provide the database optimizer with alternative paths to quickly locate the data requested by business users, even when querying by columns other than the primary key. This allows you to leverage the benefits of identity columns for physical storage and joining while also facilitating efficient business-driven querying.

A Practical Example:

Consider a Date dimension table where the primary key is an identity column (e.g., 10102025 ). While efficient for storage and internal relationships, business users typically filter data by date (e.g., "show me sales data for June 2025"). In this scenario, a column within the Date dimension table can be designated as an auxiliary key and indexed. When users query by the column, the database can use the index on the auxiliary key to quickly find the corresponding primary key values and then join with the fact table, providing efficient query performance.

Prioritizing Auxiliary Keys

It's important that not every column becomes an auxiliary key. Each index on an auxiliary key needs storage space and can affect data insertion and update performance. Therefore, it's crucial to prioritize which business-relevant columns are designated as auxiliary keys based on query patterns and performance requirements.

Auxiliary keys are especially effective in one-to-many relationships, such as the relationship between a dimension table and a fact table in a data warehouse.  For this reason, these types of auxiliary keys should be at the top of your list to create. After these have been made, focus on data attributes that the business cares about the most.  Having a data profiler, which is a great tool we will talk about in the future, will help here.  After profiling your data, you will have a better idea of the unique data values, and added to your understanding of the basic business questions, you will have a very good idea of what other auxiliary keys you will need.

The value of auxiliary keys is clear.  They are a way to bridge how the business interacts with the data and how we need to store the data.  But what does this have to do with your data strategy?  Have you ever gone to a high-end chef store and picked up an apple corer?  It is round and sharp and shiny, but if you don't know how to use it, will it do more than take up space in your silverware drawer?  If your data strategy includes very technical processes and features to make storing data efficient, but your business has no idea what to do with what you build or how to use it, your effort is wasted.

When you build your data strategy with the foundation of understanding what your business needs and how it wants to interact with data, then you will have success.  Implementing the data principle of using auxiliary keys forces you to answer those questions at the beginning of the process, and then you will have solid ground to stand on.

Comments

Post a Comment

Popular posts from this blog

Data Strategy: Guiding Principles

Image
  It has been a long time since my last post, and a lot of things have changed.  I have a new job, I have moved to the other side of the state, and I even have a new dog.  But one thing that has stayed with me is my focus on data and how much potential it has.  All of this change has also solidified my understanding of why a data strategy is critical to any success in the data world.  Without a strategy, your effort will be as useless as trying to strike the wind.  You have to be able to aim your blows if you intend to hit anything.  In our case, we want to "hit" that business objective, the one thing that the business must have to progress to the next phase in its development.   The thing I have come to understand is that when the term "Data Strategy" is used, most people in management jump right to the end and focus on things like cost or "vendor lock-in".  Some people in management focus so much on what they perceive the outcome will ...
Read more

Data Principles: The Power of Naming Standards

Image
In my last blog, I highlighted five data principles that I have used in the past to create a solid data strategy.  The idea behind a principle is that it is flexible to adjust to changing needs but rigid enough to maintain order.   A good example of this is the golden rule, "do unto others as you would have them do to you".  When you apply this principle, you are free to do whatever you want, as long as it is something you want done to yourself.  It is a very flexible principle that can have serious consequences if broken.   One crucial principle that forms a solid foundation for your data initiatives is  naming standards . It might seem like a simple concept, but consistent naming conventions throughout your data environment can significantly impact efficiency, governance, and understanding for everyone involved.  With the right naming standards, looking into the data environment as a DBA, developer, or user, there will be no question about ...
Read more

Data Governance: Building good bones

Image
The next step in our data governance journey will be setting up the data governance framework. In some ways this is just like the frame that your home is built around. In a home the frame lays out the exterior and interior walls, provides a place for electrical and network wiring, a place for insulation, and more importantly exterior and interior wall coverings like brick and sheetrock. The data governance framework works in the same way. By using principles and guidelines it provides structure around your data so that you can implement things like data cleansing rules, roles around data, and the tools that you allow the business to use. The end goal is to make sure that your data is being used in a consistent and compliant way so that everyone using it gets the same answers. You can see why this is a task that is easier said than done. Your data governance framework should include the following components: Data governance roles and responsibilities : This section should de...
Read more