I don't understand why would you use duckdb as an intermediate step to fill Ms SQL DW. Surely you could just go to Ms SQL directly?
There are plenty of arguments and reasons why you would use DuckDB to do this esp if you're preparing the data for Analytical/OLAP use-cases.
Perhaps a more relevant question might be why they didn't use DataFactory or some other ETL tool/service. DuckDB is rising the occasion for these kinds of use-cases though.
- DF is quite hard to operationalize, the logging is not so good, Python stack traces are easier to debug and logging can get as detailed as needed
- DF lacks connectors for data ingestion, this is easy in Python as on average a custom connector takes a week or two to develop
- DF is not data pipelines as code and it is becoming really hard to manage governance and change management on UI based ETL tools
- It is hard to enforce best practices on DF. We are finding it is easy to enforce standard ways of writing and managing SQL models and metadata with a combo of dbt and a dbt-ready data catalog