Press "Enter" to skip to content

Author: Kevin Feasel

Countdown Number Puzzle

Published 2021-01-13 by Kevin Feasel

Tomaz Kastrun has a fun puzzle for us:

So the game is (was) known as a TV show where then host would give a random 3-digit number and the contestants would draw 6 random numbers from stack of numbers. Given the time limit, the winner was the one who would create a formula matching the result or being closest.

Many ways, tips, tricks and optimisations were already considered, maybe the most famous was the Reverse Polish notation where operators follow their operands and is a great fit for the game.

With useless functionality, I have decided to use permuteGeneral function from RcppAlgos or same functionality could be achieved with combn function.

Click through to see it in action.

Comments closed

Spelling Cleanup with Power Query

Published 2021-01-13 by Kevin Feasel

Imke Feldmann has some misspellings:

A typical problem with data that has been created by manual entries is that category values are often misspelled or missed. So in this article I’m showing a very powerful technique on how to deal with this problem to clean up dirty category data. It was inspired by the “Preppin’ data” challenge whose instructions you can read here.

Read on for the solution.

Comments closed

Soft Deletes in SQL Server

Published 2021-01-13 by Kevin Feasel

Erik Darling has some thoughts on soft deletes:

Implementing soft deletes for an app that’s been around for a while can be tough. In the same way as implementing Partitioning can be tough to add in later to get data management value from (rebuilding clustered indexes on the scheme, making sure all nonclustered indexes are aligned, and all future indexes are too, and making sure you have sufficient partitions at the beginning and end for data movement).

Read the whole thing. Incidentally, this also ties well into a recent post by Erik about deleting into a different table. It can be easier to implement soft deletes as deleting from the current table and adding to an archive table. That gives you the benefits of keeping deleted data while not running into some of the problems Erik mentions. And if you want to undo a deletion? Delete from the archive table and insert back into the main table.

Comments closed

Using sp_prepare with Plan Guides

Published 2021-01-13 by Kevin Feasel

Aaron Bertrand tries something different:

There are features many of us shy away from, like cursors, triggers, and dynamic SQL. There is no question they each have their use cases, but when we see a trigger with a cursor inside dynamic SQL, it can make us cringe (triple whammy).

Plan guides and sp_prepare are in a similar boat: if you saw me using one of them, you’d raise an eyebrow; if you saw me using them together, you’d probably check my temperature. But, as with cursors, triggers, and dynamic SQL, they have their use cases. And I recently came across a scenario where using them together was beneficial.

Read on to see the method to this madness.

Comments closed

Security Update for SQL Server

Published 2021-01-13 by Kevin Feasel

Randolph West takes a look at a patch:

Microsoft announced updates today for all supported versions of SQL Server, for a privilege escalation vulnerability that leverages Extended Events. For security reasons no further details have been provided, but you can expect more information in the near future, now that this update is public.

Be sure to grab the latest update for your version of SQL Server.

Comments closed

Little Things in Azure Data Factory

Published 2021-01-13 by Kevin Feasel

Rayis Imayev has some kind words about small niceties in Azure Data Factory:

Recently Microsoft team conducted a brief year-end survey about a “one thing” that Azure Data Factory (ADF) “made your day in 2020” – https://twitter.com/weehyong/status/1343709921104183296. There were different responses from the global parameters support to the limit increase of ADF instances per subscription.

I personally like the little things that are not easily detected on a surface, but with a deeper immersion into a data pipeline development, your level of gratefulness increases even more.

Click through for a few examples.

Comments closed

Improving a Graph

Published 2021-01-12 by Kevin Feasel

Elizabeth Ricks has started a series on improving a particular visual:

I empathize with the plight of this anonymous creator. In previous roles, I frequently created visuals that looked like this, and was left frustrated when requests came back for “more data.” I slowly came to realize that I was assigning my audience the tedious task of figuring out for themselves what the takeaways were. My visuals should have been highlighting the interesting things to those seeing them for the first time. The five questions we’ll be discussing in this series will help us to do just that.

The first question in the series is, “What elements can I eliminate?” I think that’s a really good idea—with data visualization, less is more.

Comments closed

Hyperparameter Tuning as Technical Debt

Published 2021-01-12 by Kevin Feasel

John Mount has an interesting take on hyperparameter tuning:

The hyper dance is the venial trick of pushing user facing technical debt and flaws as user controllable features. These controls are usually named “hyper parameters” and they are parameters or arguments that control the behavior of an algorithm. Users think “hyper parameters” must be even better than “regular parameters”, just like “hyper drive” is better than “sub-light drive.” However the etymology of the name isn’t from science fiction, it is just the need in statistical contexts to have a name for controls other than parameter, as parameter is often used to name the fit coefficients of a model (i.e. to name an output, not an input!).

In addition to this, I’d be concerned that heavy hyperparameter tuning could lead to a garden of forking paths problem where we end up accidentally doing the equivalent of p-hacking: modifying hyperparameters until we come up with the “right” answer.

Comments closed

Archival on Delete in SQL Server

Published 2021-01-12 by Kevin Feasel

Erik Darling shows off a pattern:

Well, friends, I have good news for you. This is an easy one to implement.

Let’s say that in Stack Overflow land, when a user deletes their account we also delete all their votes. That’s not how it works, but it’s how I’m going to show you how to condense what can normally be a difficult process to isolate into a single operation.

The one gripe I have with this post is that my annoyingly loud keyboard is buckling spring, not Cherry MX Blue, thank-you-very-much.

Comments closed