Voice of the DBA

Author: way0utwest

  • OLTP Star, Snowflake, and Galaxy Schemas

    This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.

    There are types of schemas the exist in data warehouses. This topic is definitely on the exam.

    OLTP/Relational

    The type of schema that many of us work with is the standard OLTP or relational model. We have lots of transaction tables, most should have a PK, some of which have PKs. The schema expands to meet different needs and can have lots of entities.

    As an example, here is a view of the AdventureWorks database.

    adw_er

    This isn’t fundamentally different from the schema types below, but there isn’t a central, or two central, tables here. Instead, we have a lot of different groups of tables. The structure is designed for normalization, and usually has lots of tables compared to a data warehouse.

    Star

    The star schema is often used in data warehouses. The name comes from the fact that the table arrangement looks similar to a star. There is a central fact table, which has some details of the main data, often something like sales, and a lot of foreign keys (FK).  The fact_sales_order is the fact table below.

    Then there are supporting tables around the fact table, linked by the FKs. These are the dimension tables, and contain details about a specific dimension or area. In the image below, we have date, employee, store, and other dimensions tables.

    5690_create-star-schema-data-model-using-microsoft-toolset.028

    This is a somewhat de-normalized structure, as the primary purpose is to report on a set of facts.

    Snowflake

    This schema builds on the star schema. Here there is still one fact table (Sales below), but the dimension tables have their own dimension tables, providing more details. Essentially, the dimension tables are normalized. An example is the Employee dimension, which has a linked Department dimension.

    Capture-163

    Galaxy

    The galaxy schema expands with a second fact table. In the image below, we have the sales and purchase as fact tables. There are dimension tables, which can be linked to one or more fact tables.

    Galaxy-Schema

    It isn’t that important you know how to build these schemas or design the entities for the DP-900 exam, but you do need to recognize the structures.

  • The Cloud as a Foundation

    The cloud for computing is a fascinating structure. I know there are plenty of jokes about the cloud just being someone else’s computer, and there are good reasons not to use the cloud. However, there are also lots of good reasons to use the cloud. Whether you choose to embrace it or avoid it, cloud computing is going to be a part of our careers for a long time. The use is growing, and more and more companies are shifting workloads to cloud services.

    How that will evolve, especially for data-intensive systems, will be fascinating. One of the interesting changes that seems to be taking places is the growth of database and data stores that fulfill specialized roles for customers. Snowflake might be one of the most well-known examples, but there are plenty more. The number of offerings is growing, and perhaps this is another evolution of how the cloud will integrate into more businesses.

    To date, most of the large cloud providers (Azure, AWS, GCP) are offering a full stack of different systems that you use to deploy code and run applications. These cobbled-together services and platforms often lock customers into a particular cloud, though that isn’t what many organizations would like. Especially in some regulated industries that mandate multiple clouds be used for redundancy. This article talks about the cloud as a foundation on which other customers can build services or platforms, especially data platforms.

    This is something I would like to see. Snowflake is something that a company can run on AWS, Azure, or GCP.  CockroachDB is another that allows customers to work with the platform on the provider of their choice. I like the idea of a wider set of platforms built upon cloud providers, but in a way that allows customers to move if need be, and also pressures cloud providers to keep pricing in line with each other.

    In my mind, the more we find innovative companies building cloud-native data stores and other products, the more pressure on existing companies to improve their offerings. Competition is good, and it brings us new tools, while also forcing existing companies to update their offerings. It’s not perfect, as sometimes we get new features without quality improvements in the base product, but without competition, we might not get that anyway.

    I am fascinated and pleased by the cloud. The more I learn and work with it, the more I appreciate this as a truly new way of approaching the building and operating of software.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Daily Coping 9 Feb 2022

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to show an active interest by asking questions about others when talking with them.

    I like trying to engage with people in conversation. I don’t do it a lot, as I tend to be fairly introverted, but I have learned that when I decide to talk or listen to someone, I need to pay attention and focus.

    I did this with one of the kids I coach recently. We had a few minutes, and I asked her how a school thing was going. She had said something the week before about a challenging time, so I followed up, asked how it went, and a few other questions based on her answers.

    Reminds me of them times when my kids had really worked hard on something. This young lady detailed some challenges and how hard things were, but the enthusiasm and passion in her voice showed that she was proud of her work, despite the complaints she voiced.

  • Boost Your Career with #SQLNewBlogger

    A few years ago Ed Leighton-Dick started the #SQLNewBlogger challenge. He asked people to start writing about their career and building their own brand. I thought it was a great idea and I’ve been continuing the challenge on my own blog, writing posts and also including a short note on the effort for me to produce posts.

    It’s early 2022, and if you haven’t been blogging, and you want to grow your career, start writing. Set up a blog and produce some posts. You will build communication skills, you will showcase your knowledge, and provide potential employers with some due diligence about why you would be a good addition to their team.

    Think about tagging these posts with #SQLNewBlogger as well. You’ll promote these alongside other people’s posts and perhaps will find yourself a new, better position this year.