Hi Everyone, just a little background about me I have been working with ETL tools like Alteryx & Knime for the past 6 months so I might not know the full potential of these tools hence my question here.I was recently asked to build a client solution to automatically store address’s that are provided in customer information(Current process on client end manually look at address and enter in db). Now the information isn’t clearly structured for example that they should put country name state , city & building name and all that in a particular order. Sometimes information is missing some aspects too. Sometimes a building name is entered in the start which very well could be a country or state name. Some people have even gone above and beyond in this information storing as giving direction till there door(this is junk for me). Is it possible for me to build an Automated solution that can dissect this information accurately for me. If it can’t fully be automated I was thinking of setting criteria that if some levels of information is missing it can be thrown as exception capture which can then be resolved with human intervention (manually). Thank you and let me know your thoughts if it’s possible. If so what tools should I be using(Data privacy is also a concern). If any suggestions/approach I should take.

Hi everyone,

Recently, I came across some challenges with Change Data Capture (CDC) for a project that I am working on, and I needed to find a reliable CDC tool. I stumbled upon this blog that lists the seven best CDC tools, and it really helped me. The article does a very good job of breaking down the pros and cons of each tool so that it is much easier for me to pick the right one for what I need.

If anyone else is looking for a CDC solution, I'd recommend checking this out.
Blog: 7 Best CDC Tools for Change Data Capture in 2024
Hope it helps!

Hey everyone,

If you're working with the Google Cloud environment and looking for the right ETL tools to streamline your data integration process, you know how tricky it can be to choose the right one.

I recently found a guide that breaks down the top GCP ETL tools to help you avoid those headaches. Whether you need simplicity, speed, or flexibility, this guide covers the pros and cons of each tool so you can choose what works best for your setup. If you’re looking to save time and keep your pipelines running smoothly, it’s worth a read!

Hello everyone,

Recently, I found myself in a crunch situation - deciding on the best ETL tool for my data integration needs. It was after much research and I finally found this amazing blog- it went quite a long way in helping me make a final choice.

The articles do not only outline different ETL tools, their features, and use cases but also will make the decision-making process of choosing which one is the best suitable to the requirements easier. If you experience something similar or just curious to know what your options are when it comes to ETL, then you should certainly have a look!

Best ETL Tools

Hope you find it as helpful as I did!

I was trying to figure out the best cloud ETL tools for our data needs. The choices were overwhelming, and my team didn't have the time or expertise to dig into all the technical details for each tool. We needed something that was powerful yet easy to use.

That’s when I discovered this list of the 8 Best Cloud ETL Tools. It was a game-changer! The article breaks down each ETL tool, highlighting their features, strengths, and use cases in a way that's easy to understand. It helped me quickly narrow down my options to find the best fit for our needs.

If you're struggling to find the right ETL tool for your cloud data integration, I highly recommend checking out that guide. It gives a comprehensive overview of the best tools out there and will save you a lot of time in making your decision.

Hey everyone! 👋

I recently ran into an issue while working with Oracle Redo Logs, and I had no clue how to extract or use them for analysis. 😩 I was searching for a way to make sense of it when I stumbled upon this blog: Working With Oracle Redo Logs. It really broke down the concept and gave step-by-step guidance on handling Redo Logs efficiently.

If you’re also struggling with managing Oracle Redo Logs, I’d highly recommend giving it a read! 💡

Hope this helps someone else too. 😊

I am currently using informatica power center in a data management company I am working for. I am tasked with loading more than a 1000 tables from a source (DB2 database) to a target staging area (Oracle database).
I am used to creating independent mappings for each table even though the only column added (modification in target table) is a reference date column. However, are there any shortcuts to do this i.e. 1 single mapping that loops (somehow) over different parameters representing sources and targets.
Moreover, in the workflow manager i will have a 1000+ sessions for each table connected to each other.
Looking for the easiest and less tedious way to do this whole process!

Hi, I have noticed that when working on websites as a team it has been better to use an opinionated framework (we use django and vue) such that there is a ton of documentation on “how” to do something instead of a bespoke solution. The nature of ETL though is to connect to something, do something to it, and put it somewhere else, leading to a lot of bespoke and dissimilar scripts. Any advice? Is there such a thing as an opinionated ETL framework?

Alright, so this is probably a long shot.

My team uses Pentaho Spoon as our ETL tool of choice.
One of the steps we use as part of our process is the Mail step, to send emails to ourselves at certain checkpoints or on failure.

The issue is that bascially every major email vendor (outlook, yahoo, gmail) have all disabled Basic Authentication. So this step no longer works.

Is there another option for sending a very simple email via Spoon that does not use SMTP?

How do you define functions in ETL Code through standardized transformation logic using pyspark?

I am not sure whether this is the right spot to ask this question.

Hello Reddit

I've been looking for FOSS No Code/Low Code tools for a specific sequence of tasks. The tasks are as follows:

1. Perform Get Http Request (returns a zip file)
2. Unzip the zip file. (Returns various excel or csv files)
3. Take all those csv/Excel files and perfom data transformation on them. (Substring, concat, ifs, etc)

I'm no expert at coding or a data engineer. I'm more like a power user.

So far I've always had trouble with the handling of the zip from the http file. Most programs get the zip response as a string that starts with PK and then I cannot seem to convert it to binary. I'm trying to run perform this tasks on a Linux Ubuntu arm server. I've tried the following programs:

1. Knime: works extremely well for my use case. The response correctly returns a binary object which I can turn into a file and then unzip. The I take the excels out. I would continue doing this in knime but it doesn't run on arm (even with box64) and doesn't have a web ui for a server use case. (At least the free version)
2. Nifi: I think this one could work but Crashes on my server everytime I tried to use it. Maybe some arm incompatibility.
3. Apache Hop: Very complicated setup with functions segregated on datapipelines and workflows. Cannot unzip or transform the string response to binary (as far as I've seen).
4. CDAP: Basic Authentication didn't worked very good on Http Request. Would return error when receiving the very long string response with the zip file or some reason.
5. Dataiku: Not compatible with Arm. Has web UI.
6. Node-red - Would be able to transfor the zip string to buffer and unzip it but would return another buffer that I couldn't convert into another excel file.
7. n8n: can handle the use case but has memory leaks and turns unresponsive when handling my workflow.

If anyone has any other software that might think handles the use case or know a solution on to how to get the zip files out of the response with one of these programs I would appreciate it.

If nothing works I still can replace the arm server for a amd64 server and use knime with guacamole for a pseudo web ui. However I was expecting that one of these tools could solve such a simple task.

Thanks

Hey folks,

dlt cofounder here. dlt is a python library for loading data, and we are offering some OSS but also commercial functionality for achieving compliance.

We heard from a large chunk of our community that you hate governance but want to learn how to do it right. Well, it's no data science, so we arranged to have a professional lawyer/data protection officer give a webinar for data professionals, to help them achieve compliance.

Specifically, we will do one run for GDPR and one for HIPAA. There will be space for Q&A and if you need further consulting from the lawyer, she comes highly recommended by other data teams. We will also send you afterwards a compliance checklist and a cheatsheet-notebook-demo you can self explore of the dlt OSS functionality for helping with GDPR.

If you are interested, sign up here: https://dlthub.com/events.

Of course, this learning content is free :) You will see 2 slides about our commercial offering at the end (just being straightforward).

Do you have other learning interests around data ingestion?

Please let me know and I will do my best to make them happen.

Heyah,

I am sick of wasting time cleaning messy Excels of users in my F500 company.
Is there a tool that uses LLMs to clean it automatically? You put an Excel into it and it applies some heuristics (like: duplicate data, puting information from other columns in the comments, something clearly ridiculous (like salary being 10$) etc). I don't want to set it up using OpenRefine, I want an LLM to apply those automatically. I found https://scrub-ai.com/ or https://www.tamr.com/ but both cannot be used without a demo/commitment. Thanks for your help!

Hi, I have a pretty good experience in building ETL pipelines using Jaspersoft ETL (pls don't judge me), and it was just purely drag and drop with next to 0 coding. The only part I did was transform data using SQL. I am quite knowledgable about SQL and using it for data transformation and query optimization. But I need any good tips/starting point to code the whole logic instead of just dragging and dropping items for the ETL pipeline. What is the industry standard and where can I start with this?

I have created "Some code" a workflow automation software which makes life of developers easier. It is very easy to extend and it is free for personal use.

https://www.some-code.com/

https://preview.redd.it/luq8gz5rlsod1.png?width=1584&format=png&auto=webp&s=c71f97b25efc5e75efdffd6c7b2e113cd74e52d5

It was created using React and NodeJs. It works on Windows and Linux and it can be self-hosted if necessary.

I am working on getting off of IBM Datastage, and moving all ETL jobs, but need a way to document all the current datastage transformer code, without doing it manually for each job. I thought there was a way to get the information on the job report, do I need to create a customer template, if so does anyone know what that might look like?

Hi I need some good source and transformed sample data as close to real data with good amount of data and transformation logics applied. For me to practice validation with Python.

Is there any resources or such where I can get it from??

Hi, I'm trying to use the Extractor for Access in ABInitio MHub but I was not provided with any documentation for the .dbc file. Has anyone here worked with this extractor previously?

what changes or features would significantly enhance your workflow and make your data handling tasks more efficient and less cumbersome? hoping for insights from real people in engineering to help paint a clearer picture of where the industry might need to focus its dev efforts

For a banking /Financial company is it better to use any available tool/software in market or develop in house pipeline .Any recommendations what software /tool can be used or how to built this in-house using cloud tech like GCP /Snowflake /ETL tools

Hey folks,

dlt cofounder here.

Previously: We recently ran our first 4 hour workshop "Python ELT zero to hero" on a first cohort of 600 data folks. Overall, both us and the community were happy with the outcomes. The cohort is now working on their homeworks for certification. You can watch it here: https://www.youtube.com/playlist?list=PLoHF48qMMG_SO7s-R7P4uHwEZT_l5bufP We are applying the feedback from the first run, and will do another one this month in US timezone. If you are interested, sign up here: https://dlthub.com/events

Next: Besides ELT, we heard from a large chunk of our community that you hate governance but it's an obstacle to data usage so you want to learn how to do it right. Well, it's no rocket/data science, so we arranged to have a professional lawyer/data protection officer give a webinar for data engineers, to help them achieve compliance. Specifically, we will do one run for GDPR and one for HIPAA. There will be space for Q&A and if you need further consulting from the lawyer, she comes highly recommended by other data teams.

If you are interested, sign up here: https://dlthub.com/events Of course, there will also be a completion certificate that you can present your current or future employer.

This learning content is free :)

Do you have other learning interests? I would love to hear about it. Please let me know and I will do my best to make them happen.

Or is there more to it than that?

Hi, I would like to know your recommendation for ETL tools, as well as your favorite ones.

As I am quite new into the field, during my internship I learnt how to use Talend (free version). Honestly, it was really easy to use with SQL queries, especially with TMaps for transformations. I even got a lot of fun trying to discover everything I could do with Talend (hashing, SCD comparisons, job which check the quality of the data, etc).

But as Talend open studio is now deprecated, I am trying to look for a replacement, if possible using SQL queries.

Any help would be greatly appreciated, I am quite lost with all the ETL tools on the market. Thank you!

I am currently working on a personal project for developing a Healthcare_etl_pipeline. I have a transform.py file for which I have written a test_transform.py.

Below is my code structure

ETL_PIPELINE_STRUCTURE

I ran the unit test cases using

pytest test_scripts/test_transform.py

Here's the error that I am getting

org.apache.spark.SparkException: [TASK_WRITE_FAILED] Task failed while writing rows to file:/D:/Healthcare_ETL_Project/test_intermediate_patient_records.parquet. py4j.protocol.Py4JJavaError: An error occurred while calling o99.parquet.

I have tried ways to deal with this

Schema Comparison: Included schema comparison to ensure that the schema of the DataFrames written to Parquet matches the expected schema.

Data Verification: While checking if the combined file exists is useful, I verified the content of the combined file to ensure that the transformation was performed correctly.

Exception Handling: Consider handling possible exceptions to provide clearer error messages if something goes wrong during the test.

Please help me resolve this error. Currently, I am using spark-3.5.2-bin-hadoop3.tgz , I read somewhere that it's due to this very reason that writing df to parquet is throwing this weird error. Hence it was suggested to use spark-3.3.0-bin-hadoop2.7.tgz

I'm new to data engineering and need to query data from a PostgreSQL database across multiple tables, then insert it into another PostgreSQL database (single table with a "origin_table" field). I'm doing this in Python and have a few questions:

1. Is it more efficient to fetch data from all the tables at once and then insert it (e.g., by appending the values to a list), or should I fetch and insert the data table by table as I go?
2. Should I use psycopg's fetch methods to retrieve the data?
3. If anyone have any suggestion on how I should to this I would be greatful.

Since iPaaS and ETL both deal with data integration, how are they different?