3 ms·

Is there any good data movement/transformation software for on-prem? Specifically integration of other databases and Excel into MSSQL (<100GB of data). I have

by RGamma 3y ago

Is there any good data movement/transformation software for on-prem? Specifically integration of other databases and Excel into MSSQL (<100GB of data). I have looked at SSDT, which looks really clunky... Was going to use a similar approach to OP, especially because it's cheap and ressource-efficient, but lack of support infrastructure might come back to bite me.

mmcgaha 3y ago
I like Pentaho Data Integration. The community edition does everything I need but there is also a paid version.
__mharrison__ 3y ago
Pandas? Polars?
- wredue 3y ago
  SAS. Just kidding. Fuck SAS. I’ve use some absolutely dogshit tools in my day, and of all those, SAS is downright the most time wasting, incredibly inconsistent piles of shit ever. Never had a worse experience. Useless. Run far away.
tomnipotent 3y ago
Checkout Airbyte and singer.io for data movement. They're tailored for moving offsite data local, but also have support for moving data between different database vendors. I'd recommend starting with Airbyte. For transformations I'm a sucker for dbt-core, which executes transformations as SQL against your data warehouse vs. a separate process. This keeps everything simple and (generally) snappy. It uses the Python Jinja2 template library to compose SQL templates, and the process that parses templates to SQL also implicitly creates an execution DAG so you could have hundreds of transformations running in the exact order they need to. There are also many commercial offerings (Fivetran, Dataform) that don't tick self-hosted, but could be easier to deploy if getting internal hardware support is an issue. https://github.com/airbytehq/airbyte https://github.com/airbytehq/airbyte https://airbyte.com/connectors https://airbyte.com/connectors https://www.singer.io/#taps https://www.singer.io/#taps https://github.com/dbt-labs/dbt-core https://github.com/dbt-labs/dbt-core

by RGamma 3y ago

mmcgaha 3y ago

I like Pentaho Data Integration. The community edition does everything I need but there is also a paid version.

__mharrison__ 3y ago

Pandas? Polars?

wredue 3y ago
SAS. Just kidding. Fuck SAS. I’ve use some absolutely dogshit tools in my day, and of all those, SAS is downright the most time wasting, incredibly inconsistent piles of shit ever. Never had a worse experience. Useless. Run far away.

tomnipotent 3y ago

Checkout Airbyte and singer.io for data movement. They're tailored for moving offsite data local, but also have support for moving data between different database vendors. I'd recommend starting with Airbyte. For transformations I'm a sucker for dbt-core, which executes transformations as SQL against your data warehouse vs. a separate process. This keeps everything simple and (generally) snappy. It uses the Python Jinja2 template library to compose SQL templates, and the process that parses templates to SQL also implicitly creates an execution DAG so you could have hundreds of transformations running in the exact order they need to. There are also many commercial offerings (Fivetran, Dataform) that don't tick self-hosted, but could be easier to deploy if getting internal hardware support is an issue. https://github.com/airbytehq/airbyte https://github.com/airbytehq/airbyte https://airbyte.com/connectors https://airbyte.com/connectors https://www.singer.io/#taps https://www.singer.io/#taps https://github.com/dbt-labs/dbt-core https://github.com/dbt-labs/dbt-core