So, I can't switch, say, four partitions out into a staging table, do inserts and updates against data in those partitions and switch them back into the fact table?
You can do that. It's technically possible, though more complex than what you described.
Whether it's a good idea is entirely another matter. Whether it'll gain you anything other than massive complexity and a chance of something going wrong is still another.
The point of partition switching is to let you do the initial loads from a staging table into the fact table quickly, and to allow you to remove large numbers of rows, either to an archive table or to be deleted quickly, not so you can play musical chairs with portions of the table.
OK, I see where you are coming from but, like I said, I got the idea to do this from many different articles that discussed not having time to bulk load and index the whole table from an ETL staging area into a data warehouse. They would somehow switch out the partitions they needed to process, run their ETL against only those partitions and switch them back into the warehouse. I just haven't found anything that would automatically read our source data to figure out what months we needed to process and then do the actual switching in of the partitions and then switch them back to the mart. I just need to keep looking and was hoping perhaps you might have heard of a way to do it. Maybe when I figure it out then I'll share it here if anyone has interest in doing it.
Sounds like a bad idea.
Like Gail said, partitioning is not a performance feature and is aimed at easier maintenance. Sometimes, in very targeted scenarios, it can bring in some performance improvements as a side effect. Datawarehousing is definitely one of those scenarios, with faster partition loading.
Queries will hardly see any benefit from partitioning, especially when free querying is allowed: the reports will include predicates on the partitioning key in very few cases compared to baked reports.
Anyway, I don't think that the solution you are looking for is a smart use of partition switching. What will users see while you're updating the partition? No data for that partition?
Moreover, if you're strictly working on one partition at a time, any DML that contain a predicate on the partitioning key will perform partition elimination anyway, so I think it's totally pointless to have a process that requires switching the partition out before working on it.
Going down this path will also complicate your data loading process, forcing you to loop on the partitions rather than updating data in bulk when it spans over multiple partitions. In my opinion it's a totally unneeded complication.
BTW, if you have any articles that support this idea, I would be happy to read them.
Just because there's an article out there stating a fact, it doesn't necessarily mean that it's a good idea. You're fortunate enough to have the advice of Gail on this subject and, trust me, you couldn't be luckier.
That said, we're not behind your monitor and we can't see the whole project you're working on: it might also be a good strategy, but based on what you posted on this discussion, I agree with Gail that it doesn't sound like a good idea.