Not sure if this is the answer, but I can give you my thoughts based on experience.
If you're starting from scratch, a good way to approach this is use-case -> tool choice -> architecture.
Let's consider a star schemas, beginning with use-case. The traditional, row-oriented database is designed around Online Transaction Processing (OLTP). Transactions are anything that creates, reads, updates, or deletes some data object. Usually these objects correspond with a single entity in your application, like a user or a subscription. Many applications processes data "transactions" one user at a time. For example, your customer success team may pull up customer details one user at a time and make changes to their profile or subscription. These use cases work well for a row-oriented database (e.g. SQL Server) because all the information for one record is grouped together. If you have a Subscriptions table that is indexed by user_id and a Users table that is indexed by user_id, joining on user is a simple matter of pulling that user's record from each table and popping them together. You want to limit the number of records your system has to scan through to find the users; this is where a Kimball model is useful. It reduces the number of records to the bare minimum. That saves on storage and can increase efficiency.
To summarize: transaction-based use-case -> row-oriented database -> Kimball model.
Now let's take the analytics use case. If you're primarily summarizing data into a dashboard or summary statistics, or even if you're using that data in a machine learning model, you don't care as much about the user itself. Mostly you want to just add all the numbers in a column together (or average, or multiply by some weight then sum). By organizing all the data in a column together, you can calculate these things more efficiently (and more easily in parallel). So in this case, you want a column-oriented database like Redshift or BigQuery. The main drawback of these databases is joining. Using our Subscriptions and Users tables example, you can't just pull the record for one user_id from the Users table, the same user_id record from the Subscriptions table, and pop them together. You have to find the value for that user_id in each column in each table then merge them. This is computationally expensive.
Nowadays, storage is cheap and computation is expensive. So joins are the bane of columnar databases. This is where the One Big Table approach comes in. If you don't care about storage redundancy (again, storage is cheap) then you can replicate the all the fields (or just the meaningful ones) from the Users table right into the Subscriptions table. Then there's no need to join anything. This can vastly improve performance of the database.
These days, the problem with storing the same piece of information in multiple tables isn't cost or storage space -- it's managing the complexity and consistency. If something was wrong with a piece of data and you need to change it, you have to update it in all the related tables. There are tools that help greatly with this (e.g. dbt has a lot of useful features). But it's still a concern.
So to summarize: analytics-based use-case -> columnar database -> One Big Table.
Answering your questions.
Also with storage being so cheap these days, and most modern DWs (Redshift, BiqQuery, etc) using a columnar based architecture, I would think we could safely throw out concerns regarding the extra redundancy (of having the same data appear twice in two tables), but is it possible that when architecting for huge applications that redundancy becomes an issue?
You're mostly right here. With cheap, scalable cloud storage we don't worry as much about having too much data. Instead we want to minimize computation costs and latency. But the main drawbacks to redundancy is the complexity it adds, especially if you have to frequently update data.
What is the correct way of designing a data warehouse for each use case?
I think what I wrote above answers this question in an ideal case. In practice it's always more complicated. Data engineers may insist on an ELT approach, which doesn't work as well with a Kimball architecture. Or it's not feasible to have different databases and architecture for your customer success and analytics teams, so they have to share whichever architecture you choose.
But hopefully this helps you understand the benefits and drawbacks of each approach.
Cheers!
