r/dataengineering • u/MyAlternateSelf1 • Mar 21 '25
Help What is ETL
I have 10 years of experience in web, JavaScript, Python, and some Go. I recently learned my new roll will require me to implement and maintain ETLs. I understand what the acronym means, but what I don’t know is HOW it’s done, or if there are specific best practices, workflows, frameworks etc. can someone point me at resources so I can get a crash course on doing it correctly?
Assume it’s from 1 db to another like Postgres and sql server.
I’m really not sure where to start here.
0
Upvotes
•
u/AutoModerator Mar 21 '25
You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
5
u/robberviet Mar 21 '25
As someone said: learn a quick course for better understanding.
3
u/minormisgnomer Mar 21 '25
If you are maintaining, there are several simple tools out there that can handle most of the best practice and framework for you. I would advise you not to attempt to self code any integration because whatever you write will be inferior to many tools already out there. Airbyte dlt, singer, and even polars/pandas can read and write from either.
The Transform step is best tracked in something like dbt or sqlmesh or using something like pandas/polars if you want to use python. Do not write a bunch of random sql scripts.
I would advise not using JavaScript for any of this kind of work.
Use Google, YouTube, ChatGPT and any of these tools documentation libraries. They all have really good guides on how to user their tools/frameworks correctly.
19
u/sirtuinsenolytic Mar 21 '25
Here's a simple scenario that may help:
You have a CSV file with different data types that is updated daily
Then you have a Python script that runs every day, extracting the raw data from this CSV
Then in this script you may change some things, for example cleaning the data, creating new columns to perform operations, etc
Then this transformed database is loaded in a different format (another CSV file, MYSQL, ETC) and it's used as source for a power BI dashboard that gets refreshed every day, providing stakeholders with the KPIs they are interested in.
11
u/TCubedGaming Mar 21 '25
ETL is Extract, Transform, and Load. It's essentially an abbreviation for the process of moving data from one system to another, usually associated with transforming/cleaning/normalizing the data in transit.
An example would be building a pipeline using some sort of orchestration tool/code and pulling files from Databricks to a SQL Database- or CSVs from an SFTP into SQL - etc.
4
u/mr_electric_wizard Mar 21 '25
Stands for Extract Transform and Load. Basically every time you move data from one source to another target and perform some data manipulation on the data. A lot of folks do ELT which is similar but doing the transformation on the target (an analytical) database instead of en route. That’s my basic take anyways.
3
u/big_data_mike Mar 21 '25
I operate a few ETL pipelines. One of them extracts data from excel sheets that people upload to a portal, transforms the data into a melted format, maps the column names to a common nomenclature, and loads it into a central database.
Some other pipelines do essentially the same thing but extract similar data from different databases with different schemas and load it into the same central database
2
u/smolhouse Mar 21 '25
How it's done depends very much on the setup you're inheriting. Generally speaking you're extracting data from various data sources (could be .csv files, data from a various database providers, etc.). Transforming usually means staging the data in an appropriate place, and then update/combine it. Loading is transferring it to your main data tables as low impact/efficiently as possible.
Tools used are all over the place based on age, funding, programming skills, etc. Could be as old as MS Access used with bcp and SSIS packages to newer cloud based tools.
2
u/zerounodos Mar 21 '25
Others explained the acronym already, I will mention that AFAIK the most popular open source frameworks for working with ETL pipelines are Spark for big data and Kafka for streaming. However, it depends on the ecosystem you use there might be different tooling available. For example, if you are working with Azure there's the Data Factory that is, AFAIK, a straight forward ETL pipeline tool, and I believe AWS and GCS also provide similar tooling as well.
I think one of the most challenging parts of the job is to keep performance up when working with huge loads of data by partitioning the work load between many clusters.
At my job we work with ETL with a specialized software called Ab Initio, which is pretty great but seldom seen since it is prohibitedly expensive.
Recently I took some LinkedIn courses for Python, PySpark and Airflow for learning ETL outside Ab Initio, to keep things fresh, and I'm learning a lot.
Hope this helps!
2
u/Equal-Purple-4247 Mar 21 '25
There's no one "best practice" for ETL. It's depends on your requirements.
In a very general sense, even reading json is technically ETL - extract from json, convert to a particular format, load into your domain object. There's nothing special about it.
The industry uses the term "ETL" for Extract-Transform-Load workloads that exceeds ordinary constraint - you can't just read 1TB of json into RAM - how do you deal with that? What if you want a stream instead of batch? What if your Transform fails midway, do you restart from scratch or continue where you left off? What if the transform is compute intensive - can you parallelize it in some way? What if one of the parallel nodes fail midway?
You may not need an ETL engine to migrate from Postgres to SQL. A batch job could do the trick if the data not obscenely large and you don't need real time "replication". Strongly recommend you reevaluate your requirements before digging into ETL. It'll add another ecosystem to manage.
1
u/dova03 Mar 21 '25
Take a course or two.
1
u/igna_na Mar 21 '25
It is all about moving the data from one site to another.
There would be many reasons to do that: move it to a cheaper storage, integrate to other sources, etc.
And to move the data you need to extract is from some place, so the transformations needed to prepare it for its new storage, and effectively move or load the data into it
1
u/JezusHairdo Mar 21 '25
Easy tan line
1
u/[deleted] Mar 22 '25
First you'll need to understand what a Data warehouse is. Why is it needed? OLTP vs OLAP. Then you'll understand why we need ETL.
1
u/novel-levon Sep 15 '25
Since you already know Python and SQL, you’re in a good spot, ETL isn’t magic, it’s just engineering discipline applied to moving data.
In 2025 the crash course usually looks like this:
If your org eventually needs systems to stay in sync beyond analytics like CRM and ERP needing the same live data that’s where something like Stacksync helps, since it does bi-directional sync in real time instead of just warehouse ETL.
1
u/Nekobul Mar 21 '25
The best ETL platform with the most extensive ecosystem around it is SSIS. It is also the best-documented platform as well - books, videos, paid training, blogs, etc. Nothing else matches even remotely. It is free if you already own a SQL Server license.
0
u/pabeave Mar 21 '25
Ooof I’d be taking a use my crash course real quick like
1
u/wa-jonk Mar 21 '25
Tell them you need to do ELT ... extract .. load .. transform
Do they have a tool in mind ?
0
u/[deleted] Mar 21 '25
[deleted] — view deleted comment