Using an ETL framework vs. writing yet another ETL script
airbyte.io
airbyte.io
I feel like Data mentioning that he just ... loves scanning for life forms.
Before you know it you have super long and super inefficient code just to fit the framework. It takes about the same time to read and understand an ETL framework as it is to write your own python/bash script, and at least with your own code it's easier to see bottlenecks.
The best solution I have found is writing ETL scripts in Laravel which I use for most projects anyway. The framework has built-in scheduling and error reporting.
Founder here - we’re working to solve this exact problem.
When you do visual drag and drop - prophecy is generating high quality code on git that is 100% open source (spark, airflow), you have tests and CI/CD - so you’re visually doing solid data engineering.
You can toggle between code and visual - so if you change the code (some), the visual graph updates - so small edits directly to git don’t break the visual layer.
All visual components are generated from a spec - think a Spark function with a some more info. So the data platform teams will create their own library/framework and roll it out to the wider teams. How it works us that in the visual editor, you start with standard Spark library, but can load visual components for delta, or encryption or data quality.
Our customers are typically fed up of these ETL tools and moving to us. We can also import the ETL formats (AbInitio, Informatica, …) in an automated way (we reverse engineered their formats and created source to source compilers)
Airbyte docs:
> Note that the OAuth2Authenticator currently only supports refresh tokens and not the full OAuth2.0 loop.
But I had this same experience.
First exapmle was connecting Iterable - which looks like Airbyte supports - to bigquery.
In the past I had someone help me setup snowflake which was too complicated for me to maintain / understand myself especially AWS is too complicated for me compared to simpler google cloud.
Have also tried stich and fivetran at different times. Mostly to try to save time setting up non webhook syncs from FB marketing, Front. The volume of iterable data would be way hugely prohibitably expensive for us on those as paid platforms.
In the end I was able to do FB Marketing myself less than 1k lines of python modified from a script I found on github which used google cloud scheduler & function. I don't know python so that felt somewhat satisfying.
Another nuance in favor of a hosted/paid platform is that it looks like airbyte uses an api lookup sync instead of webhooks. That lets Airbyte get some more meta data to join to that I don't collect. That's valuable!
For iterable I ended up making a GAE app to accept incoming webhook data -> push to pubsub -> pushes to function -> which writes to bigquery.
The latency for bq writes was too much to try and do it all at once and i don't think iterable does webhook retries. Also Iterable is MEGA bursty like I've seen our GAE will scale up to somethings 40+ instances within minutes after we hit send on a campaign. That was the hardest problem to figure out getting the latency down for cold starts and scaling, cloud functions didn't work. It's not perfect but it's good enough for our needs. The simpler FB function grabs data 100% correct each day which feels good last I talked to some of the paid ETL it was flat $500 minimum a month not worth it.
From learning all this I've been able to reuse this gae, pubsub, function, bq/spanner pattern for other stuff I build and it has saved a lot of time and headache.
I think some of the points made here about ETL scripts being just 'ETL scripts' are very relevant. Definitely been on the other side of the table arguing for a quick 3-hour script.
Having written plenty of ETL scripts - in Java with Hadoop/Spark, Python with Airflow and pure Bash - that later morphed into tech debt monsters, I think many people underestimate how quickly these can quickly snowball into proper products with actual requirements.
Unless one is extremely confident an ETL script will remain a non-critical good-to-have part of the stack, I believe evaluating and adopting a good ETL framework, especially one with pre-built integrations is good case of 'sharpening the axe before cutting the tree' and well worth the time.
We've been very careful to minimise Airbyte's learning curve. Starting up Airbyte is as easy as checking out the git repo and running 'docker compose up'. A UI allows users to select, configure and schedule jobs from a list of 120+ supported connectors. It's not uncommon to see users successfully using Airbyte within tens of mins.
If a connector is not supported, we offer a Python CDK that lets anyone develop their own connectors in a matter of hours. We have a commitment to supporting community contributed connectors so there is no worry about contributions going to waste.
Everything is open source, so anyone is free to deep as dive as they need or want to.
We also build in the open and have single-digit hour Slack response time on weekdays. Do check us out - https://github.com/airbytehq/airbyte!
But that's probably the time it took to write it, right? 80% of the cost of software is in maintenance, so there's another 12 hours worth of maintenance left to account for. If you know you're going to spend 15 hours on it, then you might as well use a system you know will cost less to extend or scale over time.
"We've been very careful to minimise Airbyte's learning curve."
That's good for quickly onboarding new customers, but not necessarily for the system to be scalable or extensible.
"Starting up Airbyte is as easy as checking out the git repo and running 'docker compose up'."
I'm always curious about this. Docker-compose doesn't run on more than a single host, unless you're using it with the AWS ECS integration (and maybe Swarm?). So sure, the developer can "get it running" quickly to look at it, but to actually deploy something to production they'll have to rewrite the docker-compose thing in something else. If you provide them with Terraform modules or a Helm chart, that would get them into production faster. And maybe even a canned CI/CD pipeline in a container so they can start iterating on it immediately. It's more work for your company, but it shortens the friction for the developers to get to production, and enables businesses to start using your product in production immediately, which I think is a pretty big differentiator of business value.
"If you know you're going to spend 15 hours on it, then you might as well use a system you know will cost less to extend or scale over time."
I wish younger me realised that earlier :)
"And maybe even a canned CI/CD pipeline in a container so they can start iterating on it immediately."
Definitely! Although a good number of users are surprisingly happy with their Airbyte instances on a single node.
We do have a Kubernetes offering for those looking to scale Airbyte beyond a single node. We also have Kustomise/Helm deploys for this, though I'll be the first to admit that the Helm charts are mostly community-maintained and can be improved. This is one of our (my) top priorities going into the next Quarter.
My experience with Dataflow is 1.5 years old, so things might have changed, but I felt it more to be a unified, simplified Hadoop/Spark framework. It unifies the batch/streaming concepts but is still pretty low-level.
Within ELT, or ETL, Airflow/Dataflow can fulfills all 3 components.
Airbyte focuses just on EL (though we have basic T functionality around normalisation). Our intention is to leave T to the warehouse, since warehouses like Redshift/Snowflake/BigQuery are extremely powerful these days, and tools like DBT, give the users more more flexibility to recombine and consume the raw data than a specialised ELT pipeline.
In summary, I would say Airbyte is a specialised subset of Airflow/Dataflow, and it's possible to use Airbyte with either tools, though I'd guide someone towards DBT.
This is the data lake principle: you firstly dump all data verbatim into one coherent data storage. (data ingestion phase)
The transform will eventually be done by the data scientists (data curation phase).
The data curation is really difficult as it requires in-depth knowledge of the data, removing of slack, creation and lookup of reference data, data normalization for the datawarehouse schema, etc.
The data ingestion is relatively easy task, because the dirty data is just collected from upstream databases without any processing/conversion.
I believe data lakes is kind of "cheating" by IT departments.
In that scenario the situation is so nebulous that the original implementers had no way to know at what point the business/use case would end up so why would they immediately jump to a belt and braces solution.
It's the infrastructure version of my go to phrase: don't code for every future.
That's kind of a dealbreaker for us, because for security reasons our Postgres users permissions are granularly configured with column-based security. I hope the Airbyte team solves this eventually because the software is looking great.
Either way if someone told me a ETL supports only literally all the columns or nothing then I'd assume it was a MVP product to demonstrate the idea... not a production system.
The whole article sounds like you target total amateurs in the ETL domain. Every decent ETL engineer knows all of that what is revealed after “a few months”.
That said, I always welcome new competition in the field. Tools still suck.
You shouldn't need to waste time every time you build a report figuring out again how to query Postgres/MySQL/Elastic in Python and how to generate graphs and where to host it or how to set up recurring alerts on changes. The only part you should care about is the actual queries to write, the joins to do between datasets, and the fields to graph. The actual integration code (connecting to the database, copying a file from a remote server, etc.) should be handled for you.
The tool I'm building to solve this is open source if you're curious!
The only ETL framework you need is a general-purpose programming language. Cf. that article "You can't buy integration" from the other day.
Hopefully Elixir and other BEAM compiled languages will gain enough traction, I can't imagine rewriting something that available in Erlang from the box in OOP languages with mutable objects.
Python's speed, or lack thereof, is rarely an issue. Most tasks that I write/run finish in less than a minute, including the time for transformations. Some of those can get fairly hairy, as well. Most of the time is spent querying the DB and uploading to the remote.
For the tasks where the transformations are too complex, you can greatly decrease the time necessary by tweaking the queries (i.e., limiting rows to just changed rows, etc.). And frankly, once you realize most data doesn't need to update in real time, it doesn't matter how long the transformation step takes (as long as it's fewer than about 23 1/2 hours).
pandas otherwise
If it's not a destination warehouse, how transformations are handled depends on the connector implementation.
In an early startup, (< 4 engineers in my case) I can't imagine using anything but a skanky script at the outset. We went broke before we needing a framework. Needed more engineering time on other things.
This means investing timebin learning a workflow orchestrator, but your new hires will be glad they did.
After I wrote the initial application, they handed it off to their South African dev team to maintain it.
> "We are doing a prototype on our new mapping product, we need to write a one-off script to pull in some map data."
... is just that - a one off script. And it can prove to be a lot quicker to write a one-off script than getting involved with an ETL framework. I am not arguing against ETL Frameworks (Airbyte etc). Just that over-engineering carries its own costs, just like under-engineering does.
Say you want to build a new kind of hammer. Normally what you do is you pay a company for access to some facility that has forging process, and you put your effort into designing the hammer, working with the facility to get it forged the way you want, and selling it.
Building ETL pipelines from scratch is like building an ire ore mining facility, building diggers, trucks, shipping containers, trains, boats, iron smelting and forging equipment, and warehouses, on top of designing and selling the hammer. People today are so brainwashed by the idea that they need to be writing software that they go to these extreme lengths to build everything from scratch, when they should almost always be paying somebody else to do most of it, and only spend time working on your business's actual problem. Building factories and logistics chains should not be a problem your business needs to solve if all you need to do is build and sell a hammer.
YT has usually good tutorials and concept explanations. Maybe a good point to start.
We found this approach made it easier to transition from one product to another. As it consistently faster to plug the ETL framework into the supporting framework than to implement everything a new ETL Product offered.
As we move from our on-prem environment to the cloud, we hope we can implement a similar strategy even if we have to switch the support frameworks.
Look at how Prefect does that (I know they are well along the path), but sonething is missing.
If you are thinking about build versus buy for your ETL solution, in this day and age, there are enough great tools out there where buy is almost always the right option. You may think you can write a "simple" little ETL script to solve your problem, but invariably it grows into a monster that will be a reliability liability and engineering time suck. The post goes into more depth on why that is. Enjoy!
Scripts are collection data from external sources Scripts are inserting Data to DB Other Scripts are fetching DB and adding Data to other tables
Sure I mean it was mentioned, that data was missing and so on. But in a mature project you basically have already some monitoring, so you could just use existing solution instead of a new Framework.
Is there something wrong with such an approach?
I never bother to save any of these to source control because the boilerplate is negligible and its usually a one-time deal.
Arguably you could use Kubernetes as a scheduler, or "ETL framework/kit", it supports cron jobs, has a restful api, local and remote secrets storage, native support for cloud storage, support for multiple logging solutions, distributed workloads, supports cron jobs etc.
Years ago I worked for a financial services company and they would run their batch ETL jobs via a product called Tidal that later got bought by Cisco. I really liked using Tidal for the longest time, but 100% of what Tidal does, you can replicate with the scheduler features of Kubernetes.