4 ms·
Connectivity was the hardest part, we had to write Python connectors for a variety of ERP systems. We have a 1 server setup where we run duckdb and Python scrip
by kfk 3y ago
Connectivity was the hardest part, we had to write Python connectors for a variety of ERP systems. We have a 1 server setup where we run duckdb and Python scripts, we monitor and orchestrate with Prefect but you can use one of the many Python orchestration tools. We load the finished data marts to a MS SQL server and users connect to it via PowerBI or Excel.
- polskibus 3y agoI 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?
- chrisjc 3y agoI'm sure DuckDB was used to transform/prepare the data from the ERP extract format to an MSSQL ingestion format. 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.
- kfk 3y agoLet me give you some bullets on DataFactory (DF) because it is a question I get a lot. - 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