3 ms·

I've found small scripts do a great job with ETL. Run by cron and log to text seems to do the job. What's the advantages in using a large tool like this?

by xupybd 6y ago

markmacardle 6y ago
It's intended for syncing data sources to a data warehouse for analysis. Even if you're only doing one data source, if you want analysis to be easy later you'll likely want to sync all fields available from the api (as you probably don't know what fields are of interest before analysis). If you imagine doing this for Stripe say, there's a huge amount of fields available in different objects (charges, invoices, subscriptions etc) and you need to add these as columns to your relation database of a data warehouse. Unnesting may also be needed. That's very tedious work and on top of it you need to run, monitor and maintain the extraction process. Even a small company could easily have 10+ data sources containing dozens of tables that they want to sync to a warehouse so this quickly becomes unmanageable. Hence companies like Stitchdata, Fivetran and now Airbyte now selling it as a service.
forgetfulness 6y ago
It's a bit unclear on what the scope of this tool is. But there are a couple of new classes of tools for ETL/ELT or data engineering as it's called now. There's the "Data Integration Tools" like Fivetran, Stitch, and this. They are collections of connectors that they have coded to ease ingesting data from lots of different database products and stores to another. That's valuable, I wouldn't start writing my own script to pull changes from my RDBMS' WAL to the data warehouse because it's complicated, and if someone can pull those JSON files from whatever cloud storage for me it's good because it's too simple to waste time on. Then there's DBT and Dataform, those are orchestration tools and development environments all in one. When you start splitting your scripts (SQL queries in this case) in stages either for ease of understanding or efficiency, you'll want to see the dependencies laid out and having them execute in order. They also provide (git) version control, so it makes it a breeze for engineers to manage the pipeline like they would with other software assets, and you can get people from other backgrounds contributing in a more engineering-like workflow, talking about analysts who devise business dashboards and such.
- bleonard 6y ago
  Just for fun and completeness, there's also taking action on this data outside of your warehouse by making your external tools smarter - in some cases, even writing _back_ to the things you EL'd into it. For example, writing product usage to Salesforce. These are the kinds of things we are focusing on at Grouparoo, also an open source project.
- xupybd 6y ago
  Thanks that makes a lot of sense.
marcinzm 6y ago
Let's say you're loading data from Postgres into Snowflake. Pretty simple integration ETL. You need to: * Make sure you're only loading incremental updates * Make sure you don't miss any data while doing incremental updates. This includes deleted rows. * Update the Snowflake table schemas as Postgres schemas change. If this is impossible you need to alert someone. * Keep historical metrics so you know if the job is slow or too much data or whatever. Now do this for twenty different data sources every hour.
specialist 6y ago
Now imagine those self contained scripts are deployed to target servers. Add logging, metrics, remote control, atomic data pipelines. Or what I like to call 'data processing'. That's more or less what I built for healthcare IT. Before the term "serverless" was coined. Just peeked at Airbyte. I'd definitely look longer next time I have to do ETL. -- My only pause for concern is their phrase "optional normalized schemas". What does that mean? In my experience, data processing is best treated like screen or web scrapping. The tools based on schemas, mappings, patch cords (visual programing) always prove useless. All the CASE and workflowy tools like BizTalk and Talend are 800lb angry gorillas siting between you and your work. Super simple self contained scripts, which can easily be ran from the command line or REPL, are The Correct Answer™.
- cgardens 6y ago
  Different Airbyte engineer here! Wanted to help answer your question as to what "optional normalized schemas" means. When writing data into your data warehouse we provide 2 options: 1. write each record as a json blob. 2. infer the schema of the data and write each value in a record to its own column with an appropriate type. We are betting on EL(T), meaning we think that Transform should be considered separately from EL. To give a more a concrete example, if you are already using DBT in your data warehouse to normalize your data, you likely prefer operating on the "raw" (json blob) data than an arbitrarily normalized form of your data that your EL pipeline has decided on for you. I am seeing this trend pretty pervasively where a lot of, nominally, ELT pipelines are outsourcing the Transform to best in breed tools like DBT. Thanks for asking this question btw, we'll do our best to clarify in our docs!
  - specialist 6y ago
    Thank you for replying. Just scanned DBT. Cool. It's reliance on SQL is The Correct Answer™. (For SQL capable systems, of course.) The solutions based on schemas, where the SQL is then somehow code generated, are terrible. I'm skeptical of DBT's inference of dependencies between queries, but I'll keep an open mind until I have direct experience. Happy hunting.
cgardens 6y ago
(Airbyte Engineer) I think what you're saying here is often true until it isn't. For a personal project where you're pulling data from one API? Sure. Once you have an engineering system with multiple engineers relying on the data to be pulled reliably, having a host of individual ELT crons gets brittle really fast. At the past couple companies I've worked out this same narrative has played out: "Oh we need to pull data from X let's build a cron." (3 months later.) "Wait a second why is all of this data 1 month old? Oh the cron hasn't run in a month because of a schema change. Let's add monitoring." (3 months later.) "We need to change the cron to pull fields A,B,C hourly and field D,E,F weekly." (3 months later.) "The amount of data we're pulling is making this too expensive, we need to implement some sort of incremental replication." ... etc It always starts out as a "small" script but they rarely stay that way. In my experience, they end up needing the same features that get rebuilt over and over again on an ad hoc basis. We want an engineer to be able to get these features out of the box. We generally think that for most engineering teams (even pretty small ones) the ad hoc crons for pulling data become nightmarish pretty fast. This problem is compounded if you are already using some other ETL as a service provider but they don't support one of your data sources so you also have a separate set of crons. By taking an OSS approach we're trying to cover that long tail, so that all of your ELT can be managed using one tool.

I've found small scripts do a great job with ETL. Run by cron and log to text seems to do the job. What's the advantages in using a large tool like this?

by xupybd 6y ago

I've found small scripts do a great job with ETL. Run by cron and log to text seems to do the job. What's the advantages in using a large tool like this?

markmacardle 6y ago

It's intended for syncing data sources to a data warehouse for analysis. Even if you're only doing one data source, if you want analysis to be easy later you'll likely want to sync all fields available from the api (as you probably don't know what fields are of interest before analysis). If you imagine doing this for Stripe say, there's a huge amount of fields available in different objects (charges, invoices, subscriptions etc) and you need to add these as columns to your relation database of a data warehouse. Unnesting may also be needed. That's very tedious work and on top of it you need to run, monitor and maintain the extraction process. Even a small company could easily have 10+ data sources containing dozens of tables that they want to sync to a warehouse so this quickly becomes unmanageable. Hence companies like Stitchdata, Fivetran and now Airbyte now selling it as a service.

forgetfulness 6y ago

It's a bit unclear on what the scope of this tool is. But there are a couple of new classes of tools for ETL/ELT or data engineering as it's called now. There's the "Data Integration Tools" like Fivetran, Stitch, and this. They are collections of connectors that they have coded to ease ingesting data from lots of different database products and stores to another. That's valuable, I wouldn't start writing my own script to pull changes from my RDBMS' WAL to the data warehouse because it's complicated, and if someone can pull those JSON files from whatever cloud storage for me it's good because it's too simple to waste time on. Then there's DBT and Dataform, those are orchestration tools and development environments all in one. When you start splitting your scripts (SQL queries in this case) in stages either for ease of understanding or efficiency, you'll want to see the dependencies laid out and having them execute in order. They also provide (git) version control, so it makes it a breeze for engineers to manage the pipeline like they would with other software assets, and you can get people from other backgrounds contributing in a more engineering-like workflow, talking about analysts who devise business dashboards and such.

bleonard 6y ago
Just for fun and completeness, there's also taking action on this data outside of your warehouse by making your external tools smarter - in some cases, even writing _back_ to the things you EL'd into it. For example, writing product usage to Salesforce. These are the kinds of things we are focusing on at Grouparoo, also an open source project.
xupybd 6y ago
Thanks that makes a lot of sense.

marcinzm 6y ago

Let's say you're loading data from Postgres into Snowflake. Pretty simple integration ETL. You need to: * Make sure you're only loading incremental updates * Make sure you don't miss any data while doing incremental updates. This includes deleted rows. * Update the Snowflake table schemas as Postgres schemas change. If this is impossible you need to alert someone. * Keep historical metrics so you know if the job is slow or too much data or whatever. Now do this for twenty different data sources every hour.

specialist 6y ago

Now imagine those self contained scripts are deployed to target servers. Add logging, metrics, remote control, atomic data pipelines. Or what I like to call 'data processing'. That's more or less what I built for healthcare IT. Before the term "serverless" was coined. Just peeked at Airbyte. I'd definitely look longer next time I have to do ETL. -- My only pause for concern is their phrase "optional normalized schemas". What does that mean? In my experience, data processing is best treated like screen or web scrapping. The tools based on schemas, mappings, patch cords (visual programing) always prove useless. All the CASE and workflowy tools like BizTalk and Talend are 800lb angry gorillas siting between you and your work. Super simple self contained scripts, which can easily be ran from the command line or REPL, are The Correct Answer™.

cgardens 6y ago
Different Airbyte engineer here! Wanted to help answer your question as to what "optional normalized schemas" means. When writing data into your data warehouse we provide 2 options: 1. write each record as a json blob. 2. infer the schema of the data and write each value in a record to its own column with an appropriate type. We are betting on EL(T), meaning we think that Transform should be considered separately from EL. To give a more a concrete example, if you are already using DBT in your data warehouse to normalize your data, you likely prefer operating on the "raw" (json blob) data than an arbitrarily normalized form of your data that your EL pipeline has decided on for you. I am seeing this trend pretty pervasively where a lot of, nominally, ELT pipelines are outsourcing the Transform to best in breed tools like DBT. Thanks for asking this question btw, we'll do our best to clarify in our docs!
- specialist 6y ago
  Thank you for replying. Just scanned DBT. Cool. It's reliance on SQL is The Correct Answer™. (For SQL capable systems, of course.) The solutions based on schemas, where the SQL is then somehow code generated, are terrible. I'm skeptical of DBT's inference of dependencies between queries, but I'll keep an open mind until I have direct experience. Happy hunting.

cgardens 6y ago

(Airbyte Engineer) I think what you're saying here is often true until it isn't. For a personal project where you're pulling data from one API? Sure. Once you have an engineering system with multiple engineers relying on the data to be pulled reliably, having a host of individual ELT crons gets brittle really fast. At the past couple companies I've worked out this same narrative has played out: "Oh we need to pull data from X let's build a cron." (3 months later.) "Wait a second why is all of this data 1 month old? Oh the cron hasn't run in a month because of a schema change. Let's add monitoring." (3 months later.) "We need to change the cron to pull fields A,B,C hourly and field D,E,F weekly." (3 months later.) "The amount of data we're pulling is making this too expensive, we need to implement some sort of incremental replication." ... etc It always starts out as a "small" script but they rarely stay that way. In my experience, they end up needing the same features that get rebuilt over and over again on an ad hoc basis. We want an engineer to be able to get these features out of the box. We generally think that for most engineering teams (even pretty small ones) the ad hoc crons for pulling data become nightmarish pretty fast. This problem is compounded if you are already using some other ETL as a service provider but they don't support one of your data sources so you also have a separate set of crons. By taking an OSS approach we're trying to cover that long tail, so that all of your ELT can be managed using one tool.