• To me a full answer depends on how many sources you are pulling from to populate your data warehouse.

    I have four principle sources. Each source has it's own staging database divided into two schema: Landing and Staging. The landing schema has the same table and column names as the source database and to the extent possible the same data type. Each landing table also has a bit field that defaults to 0 and a comments field that defaults to an empty string. It will be set to one once all the data has been checked. This is to prevent one bad row from bringing the process to a halt. If a row is bad, the reason will be noted in the comments section. You will need to a way to identify those if you do that. The Staging schema is used to start any necessary conversions or data break outs (time into it's hour and minute components is common). No table stores information permanently. All the tables are truncated.

    From the Staging schema (or the Landing if the staging is not necessary for a particular process) all imports lead to a common staging database broken out by a different schema name for each source. The data is put into 3NF if that's necessary, which it often is. It's also the start for combining data that is in multiple sources.

    From here it goes to the data warehouse database. It is also broken out into the source schemas. Lately I've been transferring my fact and dimension tables into views. Kimball suggested this in his most recent book. After some experimentation, I think this works well overall. These views are assigned to a schema that matches the cube name. The warehouse contains only data needed for the warehouse

    Finally, there is an ETL database that is broken down by schema to match the four sources. It stores the row counts and exception information to create the feedback reports.

    If you primarily draw from one source, this might be too much. Some would say it's too much anyway, but the ETL is the most crucial part of the DW and you want the design to be as self-explanatory as possible. This isn't the design I started with, but moved to it as the need for greater clarity asserted itself.