[HN Gopher] Airbyte: Simple and extensible open-source EL(T)
___________________________________________________________________
Airbyte: Simple and extensible open-source EL(T)
Author : hunvreus
Score : 62 points
Date : 2021-01-16 07:31 UTC (15 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| xupybd wrote:
| 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?
| forgetfulness wrote:
| 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.
| marcinzm wrote:
| 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.
| markmacardle wrote:
| 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.
| specialist wrote:
| 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(tm).
| cgardens wrote:
| 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!
| cgardens wrote:
| (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.
| dal wrote:
| Just to clarify, this is not a fully open source project. More
| like freemium or just enough for the community to contribute and
| maintian free connectors which make it usefull. If you want
| security, you have to pay. There are non-free parts to it that
| you are not allowed to use. And contributing these features would
| probably not be allowed.
| sherifnada wrote:
| Hi, Airbyte engineer here.
|
| All connectors and the core functionality of Airbyte will
| _always_ be open source and free to use. You will always be
| able to download and operate an instance of Airbyte, containing
| all connectors and ELT features (whether for personal projects
| or business operations in your company) for free.
|
| However, there are some things that we might offer as a
| monetized product in the future, such as:
|
| * enterprise compliance requirements (E.g SOC2)
|
| * SSO
|
| * Hosting Management
|
| Hope this clarifies the direction we're taking.
| spacerocking wrote:
| What's EL?
| vlahmot wrote:
| Extract and load. It's a variation of ETL where you push the
| Transformation(T) step into the warehouse as opposed to doing
| the T out of band in code.
|
| This allows you to retain the data as raw as possible and
| empowers your analytics teams to move faster by not having to
| wait for engineers to transform the data into a usable form.
| sdepablos wrote:
| I saw this project a few months ago and I'm really surprised how
| fast they've been adding new connectors [1]. I was initially a
| little doubtful about them implementing their own protocol [2]
| instead of using Singer [3], already OOS, but it looks like it
| panned out for them. In fact I've been using Stitch Data for a
| couple of years and I've find myself "fighting" with their
| support team about bugs I think we're Singer related, mainly with
| DDL commands.
|
| [1] https://docs.airbyte.io/changelog/connectors [2]
| https://airbyte.io/articles/data-engineering-thoughts/why-yo...
| [3] https://www.singer.io/
| glogla wrote:
| If you don't mind me asking, how does Stitch scale?
|
| We're currently using bespoke Spark-based ETL tool because we
| need to move like 2 TB/day from various place, especially
| legacy system that don't make incremental data movement
| practical.
|
| How does Stitch even run, all SaaS in Stich Cloud or something?
|
| Thanks!
| sdepablos wrote:
| Pure SaaS. Check their pricing [1], as is row based and
| depending on how much data you need to move it can easily go
| out of budget ;)
|
| [1] https://www.stitchdata.com/pricing/
| laichzeit0 wrote:
| Is this meant as a free version of say DataFactory/FiveTran? I'm
| always interested in how these products do the change tracking
| and incremental updates. FiveTran is seamless, DF, more manual
| configuration. I see they don't even support incremental updates
| for MSSQL/PostgreSQL so I'm wondering who would be using this
| tool?
| sdepablos wrote:
| Sounds kind of strange not having incremental replication, it
| would make no sense for an ETL tool. In fact the documentation
| for PostgreSQL [1] is somehow contradictory at it both states
| "Incremental sync (copying only the data that has changed) for
| this source is coming soon" but also "Feature: Incremental -
| Append Sync / Supported: Yes"
|
| [1] https://docs.airbyte.io/integrations/sources/postgres
| jeanlaf wrote:
| (Airbyte engineer) Agreed. Thanks for notifying us about
| that. We fixed it. We started by supporting only full refresh
| at the very start, as it was easier to support more
| connectors, but have been ramping up incremental across all
| the sources for a month now. Incremental should be supported
| for all sources within a few weeks.
| sherifnada wrote:
| Hi, Airybte engineer here! We do support incremental updates
| for MSSQL & Postgres -- I updated the docs to make this clear.
|
| More broadly as an ELT tool, Airbyte is focused on supporting
| the long tail of integrations. The number of data silos is
| increasing rapidly, and it's intractable for a single
| engineering team or company to integrate with them all. So as
| an OSS tool, Airbyte provides a core platform for replicating
| data (e.g: a UI, scheduling, logging, state management, etc..)
| and the most popular connectors out of the box, then gets out
| of the way of its users. If you need a connector you need that
| Airbyte doesn't already provide, you can implement a Docker
| image which conforms to the Airbyte protocol, then start using
| it in your Airbyte instance without ever needing to go through
| us (Although many users do end up contributing their connectors
| back to the core codebase).
___________________________________________________________________
(page generated 2021-01-16 23:02 UTC)