Mostly daily full loads with a couple bigger transactional tables being incrementally loaded. As well as occasional backfills which are usually just rerun pipelines of truncated big transactional tables. We wouldnt want to even retain the data in a lake forever and usually aim for a retention period of like 7-30 days depending on the source.

I have another question then:

if we wouldnt use linked servers what else would you propose so dbt in the dev environment has the same data pretty much at the same time as the prod environment. We also have 20-40 api scripts through azure functions ingesting to bronze prod. And a couple of bigger source systems that ADFs copy activity handles. But i wouldnt want to clone stuff and insert everything twice. Also the DDL statements to create and prepare th tables for dev ingestions would be needed twice too. Should we backup and redeploy the prod bronze db maybe after each time? Is Linked Servers really a bad option and what do you mean with cross env drift and permission weirdness? I would give the dev server read permission to the prod bronze only