5 ms·

To me, data pipelines are about moving data from one place to another. and that's not really covered by the SQL spec. The article doesn't specify what constitut

by tremon 4y ago

To me, data pipelines are about moving data from one place to another. and that's not really covered by the SQL spec. The article doesn't specify what constitutes a "data engineering pipeline", but for me there's three components to it: - orchestration, or what happens when - data manipulation, aka the T in ETL - data movement, getting data from A to B Orchestration? Please do not use dynamic SQL, you're digging a hole you'll never get out of. Moreover, SQL has zero support for time-based triggers so you'll need a secondary system anyway. Manipulating the data? Sure, use SQL, as long as you're operating on data from one database. Trying to collate data from multiple databases may have unpredictable performance issues even in the engines that support it. Moving data? There are much better options. And the same caveat as for orchestration applies here: every RDBMS vendor has its own idea of what external query's should look like (openrowset, external tables, linked servers) with their own performance considerations.

kasey_junk 4y ago
Even on the manipulation side I tend to disfavor sql. Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. And sql types are wholly dependent on the sql engine, which can lead to wacky transforms. Sql is great for adhoc analysis. If something isn’t adhoc, there is almost always a better tool.
- marcyb5st 4y ago
  Agreed. I would also point out maintainability. How do you test some SQL logic in isolation? Additionally, in this day and age where enriching the data by running it through some ML model isn't that rare, doing it in SQL by exposing it through an API and invoking some UDF on a per-row basis is extremely inefficient due to network RTT. In my opinion it is much better to use something like Apache Beam and load the model in memory of your workers and run predictions "locally" on batches of data at the time. On the other hand I see the value in expressing "simple" logic in SQL, especially when joining a series of tabular sources. That's why I am super happy with Apache beam SQL extensions (https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.transforms.sql.html https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.tr...) which, IMHO, has the benefits of both worlds.
  - dangwhy 4y ago
    > How do you test some SQL logic in isolation? I do this using sql 1. Extracting an 'ephemeral model' to different model file 2. Mock out this model in upstream model in unit tests https://github.com/EqualExperts/dbt-unit-testing https://github.com/EqualExperts/dbt-unit-testing 3. Write unit tests for this model. This is not different than regular software development in a language like java. I would argue its even better better because unit tests are always in tabular format and pretty easy to understand. Java unit tests on other hand are never read by devs in practice. > in this day and age where enriching the data by running it through some ML model isn't that rare, Still pretty rare, This constitutes a very minor percentage of ETL in an typical enterprise.
    - alphabetam 4y ago
      > Java unit tests on other hand are never read by devs in practice. You should get to know better java developers. :-)
      - dangwhy 4y ago
        haha same!. Ppl saying sql is unreadable need to know better sql developers. sql is so pretty and so very readable and maintable if done by people who know what they are doing.
- dangwhy 4y ago
  > Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. See my comment here https://news.ycombinator.com/item?id=34580675 https://news.ycombinator.com/item?id=34580675 I don't use nested cte/stored procedures . I simply extract extract it to a new file and mark it 'ephemeral' in dbt, no different than what you'd do in a regular programming language.
RobinL 4y ago
Author here: All good points - I should have been more specific: I'm really talking here about the data transformation logic. I live in a world where raw data generally gets loaded into a data lake, and data engineering proceeds from that point - but appreciate that's not the general case. I'm really contrasting SQL to other commonly used data manipulation APIs and dataframe libraries like dplyr, pandas, polars etc., but again, appreciate this should be clearer in the blog
- mcdonje 4y ago
  In a datalake, PySpark should be the default for large operations with multiple transformations. Since you're essentially working with jupyter notebooks, it allows you to lay out your transformation steps in a way that's more clear to the reader what each step does than with SQL. The PySpark API has methods for SQL-like operations, so it's familiar to people like you and me who know and love SQL. There is no point to Pandas on a datalake since PySpark has dataframes. I'm a huge proponent of SQL. I'm dubious of ORMs and think a lot of websites would be more performant if people just learned SQL, or whatever the idiomatic querying language for their chosen DB is. But with datalakes, pyspark is great.
  - simonw 4y ago
    I worry that if I write significant amounts of code in pyspark it will have fallen out of fashion in ten year's time and someone will end up either rewriting it or running an ancient, unmaintained version of pyspark forever just to keep my stuff working. I'm much less worried about that happening to SQL scripts.
    - mritchie712 4y ago
      In addition to being good at what it does, SQL has Lindy on it's side like no other language used today.
      - rnk 4y ago
        Lindy?
CHSbeachbum420 4y ago
SQL Agent = time triggers lol
onlyrealcuzzo 4y ago
> Manipulating the data? Sure, use SQL, as long as you're operating on data from one database. Trying to collate data from multiple databases may have unpredictable performance issues even in the engines that support it. Depending on how big your data & org is - orgs solve this by writing their own query engines that can query many different data formats. It is much more practical to optimize an engine, than to expect every data engineer to know how to optimally manipulate data from many different formats.
- crest 4y ago
  And why shouldn't this engine accept SQL queries as well if it's accessing existing SQL databases (probably just with different weights, retries, and batching/spooling)?
  - onlyrealcuzzo 4y ago
    It usually does! If you want to use the same logic / language to query some exotic dataset for specific use cases - it can often be worth it to write a custom engine that can do it - rather than expect all end-users to learn the ins & outs of the other databases & datasets (not to mention, to learn something beside SQL). Instead, a single team (the query engine owners) can optimize the query engine - rather than individual users trying to optimize every script individually. Your users can become masters of their engine / language (SQL) - because it can be used for the vast majority of cases. For many reasons - you might want to store data in a format that MySQL / Postgres does not support natively (see Google Spanner). But, ideally, you'd still be able to leverage the fact that almost every programmer in the world can write SQL.
ttfkam 4y ago
FYI: MySQL has CREATE EVENT. Postgres has pg_cron. DB2, Oracle, and MS SQL all have built in schedulers (aka time-based triggers) as well.
lucid_thinker 4y ago
What solutions exist to manage data movement efficiently and effectively across multiple databases?
- tremon 4y ago
  There's AWS Glue and Azure Data Factory, integration tools like Qlik Replicate or FiveTran Replication, or ConnX solutions if you have enterprise-scale money to burn. We don't use any of them though, we have custom C# ingestion code that can talk to most databases and Odata API's. .Net's lazy collections make it quite easy to create a consumer/producer pipeline for streaming large datasets without too much overhead.
dangwhy 4y ago
> Trying to collate data from multiple databases may have unpredictable performance issues Why would this be more predictable in something you handroll vs using something like trino.
pletnes 4y ago
What is then, in your opinion, an actually good ETL stack? Many use Spark/Databricks/Synapse, which has all 3, but these seem bulky and needlessly complicated for many smaller projects. Even TB sized datasets don’t need Spark, in my opinion.
- RobinL 4y ago
  I'd advocate starting as simple as possible. It's very context specific, but one very simple architecture to consider if seeing if duckdb can handle your data (maybe on quite a large machine). For larger data, i'm a fan of writing everything in SQL, and executing it using AWS Athena (a Presto engine), which is extremely cheap. If you have even larger data, you can consider AWS Glue (steering clear of any aws specific stuff and literally using it as spark SQL as a service). For many simple cases you don't really need to know any Spark, and you're just submitting SQL to AWS for execution. But all of this assumes you're storing your data as files on disk (e.g. parquet). I'm less experienced in running everything in more traditional SQL engines like MS SQL server or postgres.
jonnio 4y ago
The simplicity and (relative) consistency of SQL makes it such a great choice. Sure, there are language variations but those can be avoided if that is a goal of the team.
thesz 4y ago
Orchestration and data movement can be expressed with SQL. Assign shards however you need, run queries. I am working on the (parallel, distributed) SQL engine and you can't even fathom how often I see SQL queries disguised-as/hand-compiled-into for loops in C++. Essentially, whatever SQL engine works on can and should be expressed as data tables and their processing and transformation should also be SQL-expressed.
swyx 4y ago
> Moving data? There are much better options. shameless plug but if you want an open source/YC backed solution for data movement pipelines try Airbyte https://news.ycombinator.com/item?id=34533429 https://news.ycombinator.com/item?id=34533429 :)

by tremon 4y ago

kasey_junk 4y ago

Even on the manipulation side I tend to disfavor sql. Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. And sql types are wholly dependent on the sql engine, which can lead to wacky transforms. Sql is great for adhoc analysis. If something isn’t adhoc, there is almost always a better tool.

marcyb5st 4y ago
Agreed. I would also point out maintainability. How do you test some SQL logic in isolation? Additionally, in this day and age where enriching the data by running it through some ML model isn't that rare, doing it in SQL by exposing it through an API and invoking some UDF on a per-row basis is extremely inefficient due to network RTT. In my opinion it is much better to use something like Apache Beam and load the model in memory of your workers and run predictions "locally" on batches of data at the time. On the other hand I see the value in expressing "simple" logic in SQL, especially when joining a series of tabular sources. That's why I am super happy with Apache beam SQL extensions (https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.transforms.sql.html https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.tr...) which, IMHO, has the benefits of both worlds.
- dangwhy 4y ago
  > How do you test some SQL logic in isolation? I do this using sql 1. Extracting an 'ephemeral model' to different model file 2. Mock out this model in upstream model in unit tests https://github.com/EqualExperts/dbt-unit-testing https://github.com/EqualExperts/dbt-unit-testing 3. Write unit tests for this model. This is not different than regular software development in a language like java. I would argue its even better better because unit tests are always in tabular format and pretty easy to understand. Java unit tests on other hand are never read by devs in practice. > in this day and age where enriching the data by running it through some ML model isn't that rare, Still pretty rare, This constitutes a very minor percentage of ETL in an typical enterprise.
  - alphabetam 4y ago
    > Java unit tests on other hand are never read by devs in practice. You should get to know better java developers. :-)
    - dangwhy 4y ago
      haha same!. Ppl saying sql is unreadable need to know better sql developers. sql is so pretty and so very readable and maintable if done by people who know what they are doing.
dangwhy 4y ago
> Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. See my comment here https://news.ycombinator.com/item?id=34580675 https://news.ycombinator.com/item?id=34580675 I don't use nested cte/stored procedures . I simply extract extract it to a new file and mark it 'ephemeral' in dbt, no different than what you'd do in a regular programming language.

RobinL 4y ago

Author here: All good points - I should have been more specific: I'm really talking here about the data transformation logic. I live in a world where raw data generally gets loaded into a data lake, and data engineering proceeds from that point - but appreciate that's not the general case. I'm really contrasting SQL to other commonly used data manipulation APIs and dataframe libraries like dplyr, pandas, polars etc., but again, appreciate this should be clearer in the blog

mcdonje 4y ago
In a datalake, PySpark should be the default for large operations with multiple transformations. Since you're essentially working with jupyter notebooks, it allows you to lay out your transformation steps in a way that's more clear to the reader what each step does than with SQL. The PySpark API has methods for SQL-like operations, so it's familiar to people like you and me who know and love SQL. There is no point to Pandas on a datalake since PySpark has dataframes. I'm a huge proponent of SQL. I'm dubious of ORMs and think a lot of websites would be more performant if people just learned SQL, or whatever the idiomatic querying language for their chosen DB is. But with datalakes, pyspark is great.
- simonw 4y ago
  I worry that if I write significant amounts of code in pyspark it will have fallen out of fashion in ten year's time and someone will end up either rewriting it or running an ancient, unmaintained version of pyspark forever just to keep my stuff working. I'm much less worried about that happening to SQL scripts.
  - mritchie712 4y ago
    In addition to being good at what it does, SQL has Lindy on it's side like no other language used today.
    - rnk 4y ago
      Lindy?

CHSbeachbum420 4y ago

SQL Agent = time triggers lol

onlyrealcuzzo 4y ago

> Manipulating the data? Sure, use SQL, as long as you're operating on data from one database. Trying to collate data from multiple databases may have unpredictable performance issues even in the engines that support it. Depending on how big your data & org is - orgs solve this by writing their own query engines that can query many different data formats. It is much more practical to optimize an engine, than to expect every data engineer to know how to optimally manipulate data from many different formats.

crest 4y ago
And why shouldn't this engine accept SQL queries as well if it's accessing existing SQL databases (probably just with different weights, retries, and batching/spooling)?
- onlyrealcuzzo 4y ago
  It usually does! If you want to use the same logic / language to query some exotic dataset for specific use cases - it can often be worth it to write a custom engine that can do it - rather than expect all end-users to learn the ins & outs of the other databases & datasets (not to mention, to learn something beside SQL). Instead, a single team (the query engine owners) can optimize the query engine - rather than individual users trying to optimize every script individually. Your users can become masters of their engine / language (SQL) - because it can be used for the vast majority of cases. For many reasons - you might want to store data in a format that MySQL / Postgres does not support natively (see Google Spanner). But, ideally, you'd still be able to leverage the fact that almost every programmer in the world can write SQL.

ttfkam 4y ago

FYI: MySQL has CREATE EVENT. Postgres has pg_cron. DB2, Oracle, and MS SQL all have built in schedulers (aka time-based triggers) as well.

lucid_thinker 4y ago

What solutions exist to manage data movement efficiently and effectively across multiple databases?

tremon 4y ago
There's AWS Glue and Azure Data Factory, integration tools like Qlik Replicate or FiveTran Replication, or ConnX solutions if you have enterprise-scale money to burn. We don't use any of them though, we have custom C# ingestion code that can talk to most databases and Odata API's. .Net's lazy collections make it quite easy to create a consumer/producer pipeline for streaming large datasets without too much overhead.

dangwhy 4y ago

> Trying to collate data from multiple databases may have unpredictable performance issues Why would this be more predictable in something you handroll vs using something like trino.

pletnes 4y ago

What is then, in your opinion, an actually good ETL stack? Many use Spark/Databricks/Synapse, which has all 3, but these seem bulky and needlessly complicated for many smaller projects. Even TB sized datasets don’t need Spark, in my opinion.

RobinL 4y ago
I'd advocate starting as simple as possible. It's very context specific, but one very simple architecture to consider if seeing if duckdb can handle your data (maybe on quite a large machine). For larger data, i'm a fan of writing everything in SQL, and executing it using AWS Athena (a Presto engine), which is extremely cheap. If you have even larger data, you can consider AWS Glue (steering clear of any aws specific stuff and literally using it as spark SQL as a service). For many simple cases you don't really need to know any Spark, and you're just submitting SQL to AWS for execution. But all of this assumes you're storing your data as files on disk (e.g. parquet). I'm less experienced in running everything in more traditional SQL engines like MS SQL server or postgres.

jonnio 4y ago

The simplicity and (relative) consistency of SQL makes it such a great choice. Sure, there are language variations but those can be avoided if that is a goal of the team.

thesz 4y ago

Orchestration and data movement can be expressed with SQL. Assign shards however you need, run queries. I am working on the (parallel, distributed) SQL engine and you can't even fathom how often I see SQL queries disguised-as/hand-compiled-into for loops in C++. Essentially, whatever SQL engine works on can and should be expressed as data tables and their processing and transformation should also be SQL-expressed.

swyx 4y ago

> Moving data? There are much better options. shameless plug but if you want an open source/YC backed solution for data movement pipelines try Airbyte https://news.ycombinator.com/item?id=34533429 https://news.ycombinator.com/item?id=34533429 :)