Your post is quite open-ended and there are many sub-questions. At this point, I will mainly address this point:
Is it possible to derive a column based off another derived column without encasing another select extremely lengthy select statement?
The principle in SQL is all-at-once. This means that the entire SELECT list is defined as a single operation. This means that, you cannot say:
SELECT A, A+9 AS B, B+C AS D FROM tbl
With B referring back to A+9. B refers to the column tbl.B, and if there isn't one. you get an error about a missing column. You need to write this as one of:
SELECT A, A+9 AS B, A+9+C AS D FROM tbl
; WITH CTE AS (
SELECT A, A+9 AS B, C FROM tbl
SELECT A, B, B + C AS D FROM tbl
There are SQL products that violate the SQL standard in this regard, but SQL Server is not one of them.
Furthermore, you cannot use a column alias in FROM-JOIN, WHERE, GROUP BY or HAVING, but you can use it in ORDER BY. This may seem inconsistent, but this is due to that logically a query is evaluated in the order FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY.
In most cases, you want to work with your data in SQL, because that is the most efficient. Keep in mind that when you write a query, you don't have to think about in which order to process the tables; the optimizer figures that out. If you would join four tables in Python, you would have to code the join yourself. The exception to this rule is when you need to do advanced string processing, like regular expressions, as T-SQL is not well equipped for this.