I have a guilty confession - I don't use KEEP. I don't even know the syntax. I guess this is as good a time to learn as any.
...several minutes pass...
OK, that's easy. So why haven't I been using that for the past 10 years?
I guess if I needed a left or right keep, I just loaded my tables in the correct order and did a where exists, which doesn't take any more code, and I'm guessing doesn't take any more time. For inner keep, well, I guess I must just inner join, as I can't remember ever wanting the functionality of an inner keep, even if it seems clever. Heck, I almost never use right or outer join. Just inner and left join. That's almost all I do in practice. I'm not saying that's the right way to go, just that it's the habit I'm in.
As far as when I merge tables into one vs. leaving them separate, it's often as simple as "how many fields from this table do I need?" If it's just one, join or map. If it's lots, leave them separate. Two or three, eh, I'll decide based on if it "feels" like the data deserves a separate table or not.
One thing I don't like in my data models is a whole bunch of tables that just hold a little bit of dimensional data. I hate seeing description tables, say. Like I would never create a Customers table if all that was on it was a Customer ID and a Customer Name. I'd join or map the customer name back onto the main table. But if I had all sorts of customer data - address, customer sales representative, billing preferences, who knows what else - then I would almost certainly create a separate Customers table. I almost always create a separate Calendar table if I'm dealing with dates, but I have no good justification for doing so vs. joining it onto the main table. Maybe just so that I'm always handling dates the same way and there are no surprises.
I've also never dealt with real time data. All of our loads are periodic, and I don't think any load more often than hourly, but a lot load hourly, or at least hourly during work hours.
