Ergest Xheblati Profile picture
Data Architect. Author of Minimum Viable SQL Patterns. Where others teach tactics I teach timeless principles and patterns
Erwan Colson Profile picture 1 added to My Authors
May 17 10 tweets 4 min read
I didn’t really get Kimball’s dimensional modeling (DM) until I read an early article by him comparing DM to the ER model.

In the process I answered the question of whether dimensional modeling is still valid today.

kimballgroup.com/1997/08/a-dime… Early in the article Kimball states that ERs are nearly impossible for business users to query and use, hinting that DMs are used as the report delivery mechanism.

The equivalent of a DM these days are wide tables which are still valid for reporting/ BI tools to use.
May 17 4 tweets 1 min read
Finally started reading Bill Kent’s Data and Reality. It’s a shame this book is out of print and not very popular.

These are some of my favorite quotes: At its core, modeling is representing / mapping reality into the digital world therefore it is not very precise. Our maps of reality are by design incomplete and all models are wrong but some are useful.
May 15 4 tweets 1 min read
Data modeling is easy to understand but really hard to put into practice. Why?

Because it’s fundamentally an opinionated design process. The fundamental purpose of data modeling is to reduce redundancy in data so that updates, inserts and deletes are easy to and cause no anomalies or inconsistencies.

That seems easy right?

Let’s take a very basic example.
May 11 6 tweets 2 min read
Here's my attempt at calculating an approximate cost per query in Snowflake:
github.com/ergest/sql4fpl…

It assumes that compute was spread out evenly amongst the queries that ran during that hour.

Explanations below: The spine table is one of my favorite patterns. You lay out the "tracks" for the query and conform the data to fit them.

Here I'm creating one row per day per hour because warehouse_metering is only available hourly
May 5 10 tweets 2 min read
When modeling data in the warehouse, choosing the right concepts and entities to model is the hardest part.

Here’s a quick introduction to the process Start with the business processes first.

At the heart of any organization lie the processes that enable people to create and deliver value in the form of products and services.

Optimizing these processes requires you to measure their effectiveness.
Apr 22 9 tweets 2 min read
I’ve been an analytics engineer for the majority of my career. Even if the title is relatively new, the role has always existed in one way shape or form.

This is why I believe it’s here to stay: As an analytics engineer your job is to design and build usable, well-documented data models for analysts and data scientists to use.

These models serve as the building blocks on top of which metrics and KPIs are defined and the core business dashboards are built.
Jan 8 10 tweets 2 min read
I’ve been writing SQL for ~15 years. I’ve seen hundreds of thousands of lines of code.

Over time I developed a set of patterns and best practices I always come back to when writing queries.

This is my attempt to decode them 👇👇👇 Rule 1: Always use CTEs

When writing a complex query it’s a good idea to break it down into smaller components. As tempting as it might be to solve the query in one step don’t.

CTEs make your query easier to write and maintain in the future.