Press "Enter" to skip to content

Month: December 2021

The Importance of Data Dictionaries

Published 2021-12-16 by Kevin Feasel

John Morehouse takes us through data dictionaries:

Data professionals—whether they’re database administrators (DBAs), developers, or data scientists—work in a wide and varied landscape usually in flux and filled with challenges. These challenges could range from changing business requirements to keeping up with the sheer velocity at which technology evolves.

It’s also critical for these professionals to understand their organization’s data and how it applies to a given application or business unit. Better outcomes usually come from employing data dictionaries throughout the organization. Through many years of experience in IT professions, I’ve seen the utilization of data dictionaries range from “not at all” to “I’m documenting every possible data attribute known to humankind.” In my experience, data-related projects with data dictionaries as part of the process are far more likely to be successful than projects without them (even extremely populated data dictionaries are more useful than nothing at all). Trust me on this.

Click through to understand why you should trust John on this one.

Comments closed

The Continuing Relevance of Feature Engineering

Published 2021-12-15 by Kevin Feasel

Pete Warden points out something which is obvious and still needs to be said:

One of the most exciting aspects of deep learning’s emergence in computer vision a few years ago was that it didn’t appear to require any feature engineering, unlike previous techniques like histograms-of-gradients or Haar cascades. As neural networks ate up other fields like NLP and speech, the hope was that feature engineering would become unnecessary for those domains too. At first I fully bought into this idea, and saw any remaining manually-engineered feature pipelines as legacy code that would soon be subsumed by more advanced models.

Over the last few years of working with product teams to deploy models in production I’ve realized I was wrong. I’m not the first person to raise this idea, but I have some thoughts I haven’t seen widely discussed on exactly why feature engineering isn’t going away anytime soon. One of them is that even the original vision case actually does rely on a *lot* of feature engineering, we just haven’t been paying attention. 

Read the whole thing.

Comments closed

Changing Case in SSMS

Published 2021-12-15 by Kevin Feasel

Steve Jones has a quick tip for us:

I never knew I could change case for objects in SSMS easily. This actually was something that another individual pointed out to me, but once I tried it, I liked it and know I’ll use it at times.

Click through to see how to change your code to lower-case or upper-case in a single command.

Comments closed

Spark SQL Bucketing and Query Tuning

Published 2021-12-15 by Kevin Feasel

Tomaz Kastrun continues a series on Apache Spark. Part 13 looks at bucketing and partitioning in Spark SQL:

Partitioning and Bucketing in Hive are used to improve performance by eliminating table scans when dealing with a large set of data on a Hadoop file system (HDFS). The major difference between them is how they split the data.

Part 14 covers query hints:

This hint instructs Spark to use the hinted strategy on specified relation when joining tables together. When BROADCASTJOIN hint is used on Data1 table with Data2 table and overrides the suggested setting of statistics from configuration spark.sql.autoBroadcastJoinThreshold.

Spark also prioritise the join strategy, and also when different JOIN strategies are used, Spark SQL will always prioritise them.

Be sure to check those out.

Comments closed

Securely Access VMs with Azure Bastion

Published 2021-12-15 by Kevin Feasel

I have a post on Azure Bastion:

Azure Bastion is a service which acts as a managed RDP or SSH host, allowing you to use a web browser securely to connect to a virtual machine, even when that virtual machine does not have a public IP address. If you’re new to Azure networking, it may feel a little complicated, but let’s see how to configure and use Bastion.

Click through for a step-by-step guide on how to use the service.

Comments closed

Query Variation in QDS Toolbox

Published 2021-12-15 by Kevin Feasel

Jared Poche looks at a procedure to track query variation over time:

Since Query Store stores statistics long term, we can use it to look for trends or major changes in performance. The Query Variation component of the QDS Toolbox helps us review changes and has a lot of options to allow us to select the kinds of changes we are interested in.

So, let’s review our options and go through some examples.

Click through for some examples of how to use the query variation procedure.

Comments closed

Hypothetical Indexes in SQL Server

Published 2021-12-15 by Kevin Feasel

Eitan Blumin explains what hypothetical indexes are and why they might be useful:

Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.

Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.

Click through to see how to use them and an important warning if you try it in production.

Comments closed

Preventing Concurrent Pipeline Execution in Azure Data Factory

Published 2021-12-15 by Kevin Feasel

Dave Ruijter and Laura de Bruin want to prevent concurrent runs of a pipeline:

For scheduled triggers, there is nothing out-of-the-box that can help you to prevent concurrent pipeline runs. For tumbling window triggers there is a maxConcurrency property, but keep in mind that this will create a queue/backlog of pipeline runs. It will not cancel any pipeline runs. It depends on your use case if you really want that behavior. 

Instead, the two look at a pair of designs and this post is all about the first one.

Comments closed

Get the Previous Non-NULL Value in DAX

Published 2021-12-15 by Kevin Feasel

Kristyna Hughes calculates the lagged value in DAX:

If you are an avid report designer or user, you may have wanted to see the percent difference between one row and the previous one. Sounds easy right? Well let’s break it down. Percent difference follows a consistent formula: (current value – previous value)/previous value. Current value? Easy peasy. Previous value? That’s where this blog comes into play. It’s fairly simple to get the previous month value or previous date period value because Power BI can tell it exactly what the filter would be (September minus one month is always August ). However, there may not have been any invoices in August, so how can we compare September with the last month that had an invoice?

Click through to see how.

Comments closed