Extract, transform, load? More like extremely tough to load, amirite?

Data integration has been an IT challenge for decades. Long before cloud, even before client server. Back then, though, it was relatively simple – you were working with systems of record, desktop databases and business systems. Today, it's not so simple. We exchange information about virtually everything from science to retail …

[Giovani Tapini]

I've never seen a dummy proof solution for this

the devil is always in the way data is mapped, which a tool can do, but only if you have the rules to apply.

Even internally in my organisation I have found that some data is very, very difficult to parse, and sometimes loses fidelity if the parsing is reversed later.

Tools or not, knowing the data is key, all you do is make the solution less code intensive.

[Lusty]

Eh?

Can't tell if the article is about application integration or ETL? Different things for different purposes being talked about as though they are an either or option here.

[malfeasance]

Re: Eh?

I think the point that Trevor is making that whether it's ETL, or API integration, it's just data integration (though I liked the repurposing of the EDI acronym).

You configure Jira to push its updates to a URL, the listener on that URL takes the Jira JSON data and turns it into a MS Teams MessageCard, and posts it to one or more MS Teams endpoint; what is happening behind the scenes, an extract, a transform, and a load. In this case sure, the extract is a push, but it's not beyond the wit of man to change that listening so that it polls a URL on a schedule etc. etc.

[Lusty]

Re: Eh?

That doesn't explain anything, you're just as confused as the article. This isn't ETL at all it's application integration. ETL is a batch process for processing data in batches. We use it for big data and analytics. App integration doesn't work for scenarios where ETL and ELT are used. Looking through the eyes of a programmer I can see why the world might look that way, but there is more to IT than coding and integration.

[malfeasance]

Re: Eh?

Sure, but the software provided by one of the data integration vendors could work in both an ETL way, and an app integration way. You're then using the same piece of software to achieve 2 very different end-goals with presumably some level of improved supportability/providing more business value. If that's good enough; then excellent. If not, then that's fine too.

Saying that ETL/App integration are instrisnically a different class of problem to each other doesn't seem right, but then I'm just a programmer.

[Anonymous Coward]

Re: Eh?

Disagree and I'm not a programmer, I'm the the thrice-damned engineer. The core of what you said is ye ole streams versus files argument. I stopped making that error back in 1985.

[Kane]

Ahh...

"One does not want to attempt to load data into an SQL server, for example, that would cause the SQL server to delete all of its contents."

....little Bobby Tables!

[Ken Moorhouse]

Bloody Excel

If Excel is in the mix somewhere then there's usually problems. Either with leading zeroes being removed, telephone numbers appearing in scientic notation (having first succumbed to leading zero removal), or dates being dealt with inconsistently (UK dates). One reason I use OpenOffice is that it actually asks you what kind of data you are working with.

Also being a UK philistine we're in our comfort zone with the base ASCII table, so programs that "autocorrect" words like cafe to the accented version are a pain too when trying to feed into a program that doesn't accept them.

[Sam Adams the Dog]

How do the data integration platforms actually work?

@Trevor I thought the article was quite cogent and to the point re. ETL, which conceptually IMO is well used to apply to the problem, regardless of implementation. But I wish you had given as much detail about how the data integration platforms work as you did about the general ETL problems and its other solutions. The moreso because they are now your preferred solution. Perhaps you could do this in a future article.