You have "data warehouse" in quotes, which could mean one of two things: you're either looking for an actual data warehouse or you aren't (yay, truisms!).
If you're looking for an actual data warehouse, there's an entire Microsoft BI stack available to you as part of your already-purchased SQL Server licensing: SQL Server Integration Services for ETL, SQL Server Analysis Services for creating cubes, and SQL Server Reporting Services and PowerPivot for analyzing data. Doing a data warehouse right is not a trivial activity, even for somebody with a great deal of experience designing and developing transactional databases. The big problem is not so much learning the tools and languages (though MDX is so different from T-SQL that it's in its own world), but getting the right mindset. If you do go down this route, start with Ralph Kimball's Data Warehouse Toolkit. It's a fairly long book and was not written for any particular technology stack, but instead tries to give you an understanding of what makes for a successful data warehousing project. Doing it right could possibly take years even if you already know the tools, because the single most important thing about data warehousing is understanding the end user. You really have to know what the end users (the business people) want, how they want it, how they can understand what they're seeing, and how what data and systems you have can get translated into what they want in a single, consistent manner.
If you're trying to do something "on the cheap," there's a much higher likelihood of failure, to be honest. This is why I'm a little concerned about why you put "data warehouse" in quotes. Yeah, there are easy-to-develop non-warehouse models you could come up with (such as possibly replicating all of the tables over to another server), but they're typically going to fall into one (or more) of a few categories:
- Too complex for end users. End users typically don't understand normalized data and shouldn't be expected to know SQL or the details of a GROUP BY clause. Your best outcome in that scenario is that IT people continue writing the reports against this new system; the more likely outcome is that IT people continue writing the reports against the old systems and everybody forgets that the warehouse exists at all...
- Inconsistent data. As you go through this, you might find System 1 says that Order 37 includes an order of a dozen ink pens, but System 2 says it was 14 pens. And System 3 had a free-form text field where instead of putting in a number, somebody typed in "dog." If they are all supposed to represent the same thing, which is correct? Finding (and working with the business on creating rules to solve) these data inconsistencies is a key part of building a good warehouse model.
- Inconsistent grain. If one system has order lines and a second only has orders, there are ways to reconcile this, but if you simply throw the tables out there and leave it to the end user, they may come up with the wrong method of reconciliation (i.e., one which gives incorrect results) without knowing it.
These are among the many problems you'll run into in the world of business intelligence. Good data warehousing (be it Kimball-style, Inmon-style, some hybrid, or something else) doesn't avoid these problems, but instead gives you a mindset where you can understand and solve them. Rolling a cheap-and-easy solution may let you paper over the problems in development, but we don't measure success simply through getting out of development; people have to use it for a warehouse of any type to have value.
Hourly updates isn't necessarily that big of a problem for a good warehouse, as long as you have the hardware necessary to do the job. You can create SSIS packages to run on SQL Agent jobs (or through any other kind of scheduler, honestly) which perform the necessary tasks (pulling data from source systems, doing any transformations, doing any necessary staging, and building Analysis Services cubes), so scheduling isn't a problem if the individual processes are fast enough.
Best Answer
A few questions. 20 million rows isn't necessarily that large.
What process are you using to perform your updates and deletes right now?
Is the dimension a CLUSTERED COLUMNSTORE INDEX, CLUSTERED INDEX or HEAP?
Are you saying that there is movement whilst you update and delete this table or did you just see movement when you changed the table from replicated to distributed?
If it is the latter that is not surprising. You are unlikely to be join and aggregation compatible. If you are doing something to trigger the movement through your update / delete then we could look at that - although a concrete example would be helpful.
In general terms I would start by trying to keep the ETL simple.
Use CTAS against the dimension selecting only the rows you want to keep, union in any new rows and use CASE to pick up any changes (converting the UPDATE into a transform within the CTAS). Once complete you can then use a pair of RENAME OBJECT commands to switch from the current table to the new table. This gives you the added benefit of having a historic view of your table - which you can drop at your leisure.