Why does Power BI hate many-to-many relationships? Despite the title (that did its job in getting your attention) many-to-many relationships *-* are not necessarily evil. However, modeling your data in a manner that avoids many-to-many relationships helps in a lot of scenarios with data consistency, among other things. Let’s dive into this using the ancient…
Searching a certain value in ANY column in a table has never been easier in Snowflake. Imagine you have an order number, and you would like to search for this order number in a whole bunch of tables that you do not know well. If you knew which column to search, this would be easy:…
I have a data modeling conundrum. It concerns records in a dimension that have start- and enddates. I’ve heard people refer to this type of problem as the “events in progress”-problem. My specific version of this problem goes like this: Imagine you have a sales fact table called FactSales with a date, an employee who…
Speed up your SQL development with this one trick! … No but seriously, this little hack makes SQL coding so much more convenient. Most SQL development environments have the possibility to perform so called “Multi Cursor edits” (also called “Box selection”). Meaning, you can edit several lines in your block of code at once. In…
I am rereading The Data Warehouse Toolkit by Kimball and Ross and would like to share something interesting concerning fact tables. According to the book, there are 3 main types of fact tables: A transaction fact table inserts a new record for every transaction that takes place. An obvious example would be a sales fact…
Did you know you can use SQL queries in your Python worksheets in Snowflake? This works as follows: dataframe = session.sql( ”’ YOUR SQL QUERY GOES HERE ”’ ) And voila! Don’t forget to return it in the function in the worksheet if you wish to inspect the resulting dataframe in the results pane. I…
Are you looking for a way to speed up queries in Snowflake without altering the size of the warehouse? One way you might achieve this is by taking the largest table in your query and applying the necessary filters before you start using it in your query. This means that the aforementioned largest table that…
Are looking to give some extra context to your SQL query output before you deliver to business users WITHOUT the use of Excel? A colleague of yours would like to see the sales per person including a GRAND TOTAL row. Using GROUP BY ROLLUP ( ColumnName ) you can easily create this, But this will…
In Snowflake there are useful commands like SHOW USERS (shows the users in your environment) or SHOW GRANTS TO ROLE (shows the privileges given to a certain role). The results of these commands are shown like tables however, you cannot filter them like SHOW USERS WHERE … You can however use the commands TABLE and…
“THE DATABASE MOST USED IN YOUR QUERIES WILL BE DELETED!” “But don’t you worry pal, we are gonna migrate the tables and views you use to another database!” “If you could just give me a list of the objects you use in the soon-to-be-history-database, that would be great.” Migrations are a common animal in any…
