Where Should Your Calculations/Transformations Live?
One simple rule I followed in every data project, from warehouses to Power BI.
Hey friends, Happy Tuesday!
Here is a pain almost every data project has calculations are everywhere!
Some logic lives in SQL. Some in the pipeline. Some in Power Query, some in DAX, and some in an Excel sheet nobody talks about. Every tool in the chain can calculate, so every tool does. Nobody planned it. Each calculation just landed in whatever tool was open at that moment.
And then a number looks wrong, and the hunt begins. Where is this thing even computed? You find it in three places. With three different formulas.
The fix is not a tool. It is one rule:
🎯 The golden rule: Calculate as early as you can, as close to the source as possible. And calculate on the fly only what must react to the user, as close to the report as possible.
Why early wins
#1: Reusability. Watch what happens to the same calculation as you push it back:
In a visual, it serves one visual.
In a measure, it serves one data model.
In Power Query, it serves one file.
In a SQL view, it serves every report, every tool, every team.
The further back you push it, the more people inherit it for free. Find a bug? You fix it once, and everything downstream gets the fix.
The earlier a calculation lives, the more people it serves.
#2: Stronger tools. To be honest, for heavy transformations, SQL and Python are simply stronger than Power Query and DAX. Full languages, easy to test, easy to reuse, and your whole team can read them.
#3: Performance. An early calculation runs once at refresh. A late calculation runs for every user, on every click, all day long. Why pay a thousand times for something you could pay for once?
#4: One version of the truth. Logic that lives inside one report gets reinvented in the next one. Two slightly different formulas, and the same KPI shows two different numbers. And when users see different numbers, they stop trusting the whole project. And then they go back to Excel.
In the lakehouse I led at Mercedes-Benz, this was the whole game. We built the business logic once, inside the platform, and more than 40 analytics and AI teams consumed it from there. Now imagine every team calculating revenue on their own. Forty teams, forty versions of revenue.
The magic of this rule
And here is the part I really love. Once you know this rule, you start seeing it everywhere, because half of the data world was invented just to follow it.
Why do companies build data warehouses at all? This rule. Prepare the data once, in one central place, and every project consumes it.
Why do lakehouses have silver and gold layers? Same rule. Clean once, build the business logic once, and everyone downstream inherits it.
Materialized views, OLAP cubes, aggregation tables? All the same trick. Pay for the calculation once, early, instead of on every single query.
Feature stores in machine learning? Calculate the features once, and every model consumes them.
So my Friends…
Prepare early, once, close to the source. React late, live, close to the user. That is the whole rule.
We just opened the DAX chapter of the Power BI course with exactly this idea, explained step by step with the sketches.
And if you made it this far, you’re already one of the good ones.
Thanks for reading ❤️
Baraa
Also, here are 4 complete roadmap videos if you’re figuring out where to start:
📌 Data Engineering Roadmap
📌 Data Science Roadmap
📌 Data Analyst Roadmap
📌 AI Engineering Roadmap
Hey friends —
Hey, I’m Baraa, a Data Engineer with over 17 years experience, Ex-Mercedes Benz, where I led and built one of the biggest data platforms for analytics and AI.
Now I’m here to share it all through visually explained courses, real-world projects, and the skills that will get you hired. I’ve helped millions of students transform their careers.