First of all, thank you for the responses. I'll try to summarize with some Q&A.

1. What is my goal?

Develop a near real-time ODS.

2. What type of incremental loading are we currently using?

When we have a transaction date to work with, we use a date range. We keep it broad, where the date will be between -15 days to current date. If the table doesn't have a date, we use an auto-increment field, where we use the target's max value to then query the source. If neither of these are option, we truncate ODS and reload from source.

3. Why do we want to get away from date-based incremental loads?

Couple reasons. With us only going back 15 days on average, we will miss any data changes that were made beyond then. And with this (data updates) being somewhat common, it can be a real pain for me to manually run ETLs for specific (100 days ago) ranges. Second, we are pulling unchanged data which puts a load on both our source and destination systems.

4. Why not use a last modified date time?

We would love to use this; it'd solve this whole problem. We cannot though because in doing so (adding the column to source) we'd violate our database terms of contract.

5. Isn't Transactional Replication and CDC incremental loading?

Yes, now that I think of it, technically they are. I should have been more clear on this. The idea behind CDC is just so much better than date ranges because it only disperses changed/added data.

6. Do you effectively want to completely re-populate your ODS or Data Warehouse every day/night/applicable period?

Our preference is to add to or modify the ODS. If our only option is truncate an ODS table, we will do it that way. This methodology isn't horrible - the EDW still picks up the changes.

----

Our original plan was to use a distribution/replication server that had CDC enabled, where we would run CDC as frequently as possible. We knew that SQL Standard didn't offer this feature but assumed since the data had been replicated to an Enterprise version, CDC would work. Once we actually did this and attempted the CDC, it failed.

The idea of using a distribution server, where you centralize databases, is appealing for many reasons. Though I'm now wondering what, if any options we have with this configuration. Seems like once it's replicated, you're stuck with few options.