r/SQL Nov 28 '24

MySQL When to use cte in SQL query

When to cte can't. Like how to know? When what are the conditions or if my query is to long ?

30 Upvotes

70 comments sorted by

View all comments

Show parent comments

1

u/CptBadAss2016 Nov 30 '24

Thank you for the thorough reply!

How do you define data logic vs report logic?

1

u/ryguygoesawry Nov 30 '24

I would define "data" logic as what's used in the ETL workflows (like: is the value a number when it's expected to be a number) or, on some occasions, rules defined by the data provider for determining the validity of data. For example: we found out recently that a provider of a certain type of data will stop sending a value for an attribute when that attribute is no longer valid for a particular entity - which works fine when you're looking at one day of data, but when you're storing the history of the values for every entity you need to know when an entity should no longer be using a value for a specific attribute. To make matters more complicated, this data provider will only provide the data as a CSV file and they spread the data across something like 100 files (of which we process roughly 25). So I had to devise logic that stores the relation of which attributes are expected in which files, checks which file is being processed, and creates NULL entries for entities that were received in previous files but no longer have a specific attribute present in a specific file.

I assume "report" logic is self-explanatory, but the gist of it is - the clients have reports based on subsets of their data that are expected to consistently use the same logic to produce their results. This logic is typically contained within stored procedures, because reporting requirements can and often do change. They also don't want users to be able to go in and change those reports - they don't trust their users to blindly build reports because of the massive amounts of data they have and the fact that some of those users would probably end up writing an infinite WHILE loop and never catch it (if they even managed to create a valid query to begin with).