3 ms·

I’ve found that sql pipelines end up depending heavily on a specific database (ssis packages for sqlserver) so really suck when you don’t want to use that datab

I’ve found that sql pipelines end up depending heavily on a specific database (ssis packages for sqlserver) so really suck when you don’t want to use that database any more, or still need something to coordinate and run all the sql. So if I have to have something to run all the sql and test it , etc etc why not do the pipeline there? You can still include sql in the pipeline for the parts in database. But trying to do everything in database can be unwieldy. For example, pulling a csv from a file system, pulling a json from an api, linking them, and storing the output as a csv somewhere. Doing that in sql is possible, but why? I think having a mixed bag of tools for the pipeline is the right default. And having something highly portable for the top layer of orchestration is probably not going to be sql.

jeltz 4y ago
Isn't that true for all tools which currently exist? I do not think there exists any such highly portable thing.
- JoBrad 4y ago
  Not in my experience. Handling transformation in e.g. Python doesn't require you to know much about the SQL language variant that the db is using.
- prepend 4y ago
  Python scripts are really portable and can run in lots of environments (including in sqlserver).
- throwayyy479087 4y ago
  This is the 1 advantage of ORMs - makes apps db portable.
bruiseralmighty 4y ago
I agree for one-offs and for simple mappings. If I had to do this problem as part of some personal workflow used only by myself, then I would just use `pandas` or some equivalent for the entire thing and have it live in a jupyter notebook. However, if the mapping is even somewhat complicated, or this pipeline has to be shared and productized in some way, then it would be better to load the data using some `pandas` like tool, store it on a `tsql` flavored database or datalake, and then exported as a .csv file using a native tool or another `pandas` equivalent again. Having a pipeline live solely on a notebook that is passed around leaves too much risk for dependency hell and relying on myself to create the csv as needed is too brittle. Either have the pipeline live on its own container that can be started and run as needed by anyone, or dump the relevant data into the datalake and perform all the needed transformations there where the workflow can be stored and used repeatedly.
- prepend 4y ago
  > Either have the pipeline live on its own container that can be started and run as needed by anyone This is what I do. Each pipeline starts in a fresh conda environment, or sometimes entirely fresh container (if running in GitLab CI or GitHub actions, for example) and does a pip install to pinned packages. So every run is a fresh install and there’s no dependency hell.

jeltz 4y ago

Isn't that true for all tools which currently exist? I do not think there exists any such highly portable thing.

JoBrad 4y ago
Not in my experience. Handling transformation in e.g. Python doesn't require you to know much about the SQL language variant that the db is using.
prepend 4y ago
Python scripts are really portable and can run in lots of environments (including in sqlserver).
throwayyy479087 4y ago
This is the 1 advantage of ORMs - makes apps db portable.

bruiseralmighty 4y ago

I agree for one-offs and for simple mappings. If I had to do this problem as part of some personal workflow used only by myself, then I would just use `pandas` or some equivalent for the entire thing and have it live in a jupyter notebook. However, if the mapping is even somewhat complicated, or this pipeline has to be shared and productized in some way, then it would be better to load the data using some `pandas` like tool, store it on a `tsql` flavored database or datalake, and then exported as a .csv file using a native tool or another `pandas` equivalent again. Having a pipeline live solely on a notebook that is passed around leaves too much risk for dependency hell and relying on myself to create the csv as needed is too brittle. Either have the pipeline live on its own container that can be started and run as needed by anyone, or dump the relevant data into the datalake and perform all the needed transformations there where the workflow can be stored and used repeatedly.

prepend 4y ago
> Either have the pipeline live on its own container that can be started and run as needed by anyone This is what I do. Each pipeline starts in a fresh conda environment, or sometimes entirely fresh container (if running in GitLab CI or GitHub actions, for example) and does a pip install to pinned packages. So every run is a fresh install and there’s no dependency hell.