By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one.
SELECT rando.RandomNumber, d.database_id
FROM
(SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber
FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id
|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))
|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))
Still digging into just why it waits so late to do it, but currently reading this post by Paul White (https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html). Perhaps it has something to do with the fact that NEWID is not deterministic?
There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to. Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies.
In regards to what I've seen in the real world, walking into a company for the first time and seeing what I'm understanding about your design would be genuinely tear-inducing: Tears of joy and relief. From the outset, you are well on your way to beginning what appears to be a well thought-out ( well engineered ) ETL / data warehousing system. As with the implementation of any software product, your mileage may vary as the solution grows and is consumed by the business, but fundamentally, you are on The Right Track™ ( and yes, you know what a natural key is ).
I've found there to be a number of challenges with these type of solutions, which I will touch upon to reinforce some of your decisions and perhaps lend some insight into the road ahead of you. Firstly, the number of times I've found myself in a predicament on account of a developer ( even fellow database administrators / data professionals ) misunderstanding the context of a control column ( using, for example running a process against the DateInserted column, a mere time stamp of insertion, over the DateReceived or similarly named column, intending to relate a row to a particular date of occurance ), that while I agree completely with the cautions @Aaron Bertrand raises, I feel that the prefixes for your control columns could actually be leveraged as a sort of flag to help prevent their misuse. Obvious should be obvious of course, but much like writing code in general, explicit is preferable. That said, I would almost certainly leave such prefixes out of the indexes and such ( probably even keys - PK types can and should stay in my opinion, but unless there's a real threat of DWD_SubCategories and DWF_SubCategories existing in the same schema, they really are just fluff ). I think the concern about the DWD and DWF prefixes is valid, but they'll be living in the [NDS] catalog and would serve to indicate intent, making it completely fine to use the nomenclature in that manner.
The second ( and perhaps most infuriating ) challenge is one of cross-training your coworkers. All of the software engineering, usage flags and design practice rules are completely for naught if your striving-for-paycheque-over-excellence colleagues get involved and do their less than very best ( or to be fair, are even just simply having a bad day ). Do keep in mind that large projects generally have many fingers in the pot, so it is imperative that those fingers are behaving well.
The last thing I'll touch on here is to always keep in mind the actual value of any ETL system to a business. Of the Extract, Transform and Load paradigm, the first and final letters have absolutely no business value, so you will want to work on making the development and maintenance of both the Extract and Load processes as minimal as possible - the "real" work will be done in the Transform phase, so you will want to automate the E and L steps as much as possible so that you can focus on making ( and keeping ) your solution valuable to the business unit by actively working on the transforms.
All of that said, I've only had the opportunity to work on a handful of different warehousing solutions so perhaps a more knowledgeable user could step in and remove my foot from my mouth if I need correcting. As I said initially, this is one of those areas where one can always learn or unlearn something, and I am absolutely no exception.
Oh, one more thing ( and probably the most important ) - Unit Test! Once your E and L are working as intended and you've had the opportunity to put a few domains through your T solution, get somebody to vet the results. If they're good, save the result set somewhere, so that when you make changes ( and you will, without a doubt ) you can ensure you haven't broken something, somewhere else. Again, automate this process as much as you possibly can ( it's another 0-value process to the business, until they go without it at least ;) ). I generally set up a separate schema or catalog for this purpose.
Hopefully some of what I've said will be useful to you!
As an update, @Aaron Bertrand's schema separation seems like it would be quite a good way to avoid unnecessary prefixing as well, so certainly consider that ( I know I will haha ).
Best Answer
You can do this pretty easily using
SUMandCASE:Then you get a number for the WHOLE table/query, and a breakdown of each category, all on the same line. It'll be easy to see if you have a bad query since you'll get a low/high rowcount or something else.
This has the added advantage or processing more efficiently than a bunch of separate queries, since you just evaluate the whole table once.