[npolet]

I was part of a team developing a travel product which needed huge amounts of data from flights to car rental... My god the travel industry is a mess in terms of accessible data. It was the biggest pain point. API's would randomly change structure, endpoints would stop working with unknown errors, data would be significantly different across companies for the same data etc... It was a shambles. Never again will I develop in the travel industry. Unless you own the data, working with it is terrible.

We ended up building our own little system that merged our own flight data with data from suppliers. The schema some of the suppliers provided was non sensical and it ended up being a really finely tuned system that would break reguarly if one of the data suppliers decided to change it without notice.

I spent my days dreaming of a standardized data pool, and while some of the data was standardized, not enough of it was.

Thank goodness I'm now working in an industry that we can totally own our data and are not reliant on anyone else for it.

[lotyrin]

Reminds me of my time spent being a customer to a real estate MLS. When industries traditionally rented access to information asymmetry, even if those rents have since diminished, there's a lingering cultural momentum not to "show your hand" by playing nice with information (in addition to these not being organizations with the insight and incentives aligned to be attractive or conducive to IT competence).

[i_cant_speel]

I deal with MLSs regularly as part of my current job and this was the first thing that popped into my head when I read the parent comment. They are hell to deal with and we would pay a good amount of money to anyone who could standardize all of them and feed them to us in a consistent format.

Half of the time I spend debugging is on figuring out what is going wrong with different MLSs.

[starik36]

I also deal with MLS data and, on our end, it's in pretty good shape.

Of course, there is a whole department devoted to keeping it clean and consistent, so I don't have to deal with issues that you do.

Now that you mention it, perhaps we should provide an API for MLS data access.

[paulie_a]

Ive worked with MLS data before. It was terrible. I am currently working with ACH, DDF and I am astounded that our financial system even operates. Because if there is one thing you want in payment processing, it's wildly innacurate and inconsistent data that is lacking documentation.

[developer2]

Fuck MLS. It's supposedly a "standard", but every single MLS API endpoint is completely different. As in, there are very few recognizable similarities between any two feeds. The data structures are not actually standardized whatsoever; one MLS will have all listings in a single "table", while another will have 12 different tables that you have to figure out how to parse and/or join. There is some lingo that is fairly standard because of the industry, but the way the data is organized and represented is wholly unique per MLS. And there is no documentation other than an (optional, usually 2-3 word!) description on each field providing little insight.

The protocol used by MLS servers is kind of standardized... expect not really. There's no single way, for example, to accomplish a full download of an initial dataset. Some MLSes let you order by id with an offset, so you can paginate properly. With others you must use date ranges - but you're assuming that a create or update timestamp is actually meaningful, and surprise - they are not. Some let you run multiple concurrent connections so you can increase data throughput, while others only permit one slow connection that makes it takes 6+ hours to download their entire database.

Finally: good luck figuring out when a record is flat out deleted. The MLS may have a separate table where deleted records are supposed to go, but that table is always empty. The MLS nukes data rows with no way for clients to detect the change other than by downloading the entire database from scratch to find the gaps. This requires running hours of queue jobs PER MLS every 24 hours. It's so ridiculously inefficient.

Yeah... fuck MLS. End rant.

[i_cant_speel]

I'm convinced we work at the same company because you just expressed all of the same pain points that I currently have with MLSs at my job. We have to do so many hacky things to make sure that we follow each individual MLSs rules to get them to actually work.

They are absolutely terrible to work with.

[developer2]

I took the liberty of scanning very quickly through your post history. We live and work quite far apart. Seems MLS pain is universal. ;)

[erichurkman]

And for ACH, it's even worse: you assume success only in the absence of failure. It's shockingly bad.

[pishpash]

Not coincidentally these are also industries with the most middlemen.

[jvagner]

Mortgages, too...

[tyingq]

Didn't like OTA[1], eh?

I don't know that other industries are much better though. Ask a healthcare IT person about HL7.

[1]https://en.m.wikipedia.org/wiki/OpenTravel_Alliance

[snuxoll]

FUCK HL7! Holy shit, I’ll never get the time back I spent implementing our standard HL7 data extractor and all the other software needed to get that into our billing system. Our poor EDI team has the worst of it though, they’re the ones responsible for mapping all the edge cases various EMR’s (and their per-hospital customizations) into our standard formats.

HL7 claims to be a standard, but it’s a hell of a lot closer to JSON in terms of standardization (that is, a serialization format) than say ANSI X12 (an ugly but generally standard data interchange system).

[SmirkingRevenge]

> Ask a healthcare IT person about HL7.

Been there, done that. Never again!

[cwilkes]

I liked to say “HL7, standard?” As more of a question.

[Bluestrike2]

The car industry is another industry whose data is a series of nightmares. There are a number of services that aggregate inventory data for dealers using them. Sounds great, right? Maybe, if they had halfway decent APIs. Multiple providers--with zero connection to one another, I checked quite carefully after becoming exasperated--somehow managed to independently come to the decision that the best way to make inventory data available was to push large CSV files to an FTP server you setup.

Whatever. People can come up with crazy solutions and you're stuck working with them. At least you've got the CSV files by that point, right? Well, sort of. Turns out they needed a good bit of work. And by a bit of work, I mean that some of them were quite possible the worst CSV files ever generated. And it was...weird. Normally, if there's a problem with a CSV file it's at least consistent across the entire file. Quotation marks not escaped? Ok, no big deal. They're all like that. Usually, you can normalize the data and move forward.

If only. Some lines had escaped quotes. Some didn't. Some lines were actually multiple records because apparently the magic linebreak decided to go on strike. In one case, I kid you not, the file switched from comma-separated to tab-separated. Huh? How'd that even happen? Some values were perfectly valid, just handled in a way that's guaranteed to annoy the hell out of you. But fine. You do what you can, reject the bad (and log to a file for manual review in the hopes that you'll figure...something...out about it), and move on with your sort-of-normalized data. But that's just referring to the data itself, and not the entries.

When you order a new car, the build sheet for every manufacturer is simple enough. Every option has a code. Every option has a name. New car dealers get their inventory data back from the factory and it's plugged into their inventory management systems. Through whatever accidental acts of magic and chicanery, that data eventually makes its way to the data sources you're busy importing. Unfortunately, at some point in the process all of those beautiful factory codes and names--standardized, constant, etc.--disappear. That beautiful "Cobalt Blue" is somehow transmogrified into a very unhelpful "Blue." And don't even get me started on factory options. At times, you're lucky if you somehow accidentally get the basics like, oh, "has two front seats and possibly four-ish wheels." It's even worse with used cars, because some unlucky salesperson/clerk/receptionist/car washer kid had to sit down and manually enter the car data.

Instead of thinking of that giant CSV file as a wonderful list of thousands of cars just waiting to be discovered and purchased, you start to see it as more of a starting point. It's an incomplete list of cars for sale, with some of the information about each car. You need to use other sources to fill in some of the blanks, fix some of the most obvious errors, etc. Luckily, dealers upload their data to as many services as they can. Unluckily, it's often...different across those same source, and it's up to you to figure out what data to keep the same or change. Depending on the manufacturer, you can decode the VIN and pull up all sorts of useful information about how the car's build options. Maybe. You can then use the manufacturer's pricing guide for that model year to fill in the blanks. Assuming, of course, that you've got a copy of the pricing guide in question. Which isn't guaranteed, since they're generally not publicly available (though they do leak...often).

I'm a huge Porsche fan, and I know more Porsche fans. We're all nuts. Details matter. Do you want to know how many [shades of blue](http://paintref.com/cgi-bin/colorcodedisplay.cgi?manuf=Porsc...) Porsche has used over the years? Many of the paint colors are available across different models and different years, so the number is a lot scarier than it actually is, but there are a total of 641 entries in the linked paint database. Someone searching for a used Porsche wants to make certain that they're looking for a specific color or option. They don't want to just use "blue" for their search. They're looking for the gorgeous [Oslo blue](https://gearpatrol.com/2016/08/18/definitive-ranking-blue-po...) imprinted in their minds during a magical childhood moment, damnit. Which was only used in 1961, except for custom paint-to-sample orders in later years. There was 1 993 Turbo S in Turquoise Blue. Jerry Seinfeld bought it. A PTS color will affect the relative value of the car (new or used), so it's one of those fiddly bits that matters a bit.

You'll get another multi-gigabyte CSV file delivered to by FTP on the morrow. And joy, it's not an incremental update with just the new cars. It's all the cars they have data for. If a car has been sold, it'll be omitted from the file. It's up to you to figure out which. Hopefully, the car won't be "un-sold" in a day or two after the buyer backs out. That can get weird, especially when you're dealing with multiple providers. Finally, your simple diff is further complicated by the inevitable likelihood that random data across random listings has been changed as well. Perhaps they caught a typo in the VIN number, or the annoying fact that the paint color originally listed never actually existed.

Needless to say, I sound a bit crazy at this point. Your only option is to accept that, much as it might annoy the hell out of you, there are going to be serious compromises involved with the data you have access to. Apologies to those with sever OCD and perfectionists. Handily, some manufacturers have sites for their dealers (i.e., http://porsche-dealer.com). Usually, these sources are pristine with everything in order. You just need to scrape it where it's allowed, which is its own set of fun.

And don't even get me started on the photos included with listings. It's rare that you'll see decent photos taken by a dealer. Mostly, you'll find those on eBay Motors with certain sellers. Everyone else does their best to make them as terrible as possible. The data providers then take that as a challenge, and crush the ever-living hell out of the image with another round of JPEG compression and give the resulting monstrosity to you at a nice, small resolution. Want something bigger? Forget it.

Maybe I'm blowing all of this out of proportion. Perhaps the industry has changed in the past few years since my experience. Personally, I doubt it. In any case, dealing with this was annoying as hell. This rambling post was oddly cathartic.

Anyhow, if you ask me, I'm pretty sure all of the car search sites just scrape each other; like Ouroboros, depicting a serpent eating its own tail.

[closeparen]

>somehow managed to independently come to the decision that the best way to make inventory data available was to push large CSV files to an FTP server you setup.

Somehow this is indeed a standard for data transfers between non-tech companies. (The ones that are sophisticated enough not to use fax or email attachments).

[NegativeLatency]

I worked on a team at Microsoft that did the CSV over FTP thing in 2013. So some tech companies do it as well. ;)

[yesiamyourdad]

My employer has a scheduled job that downloads a CSV over FTP thing at 6PM everyday. This is in adtech, with a company that's about 5 years old.

[rokhayakebe]

You know I was thinking as I was reading you guys are crazy for having 641 blues. However I definitely see your point after clicking the links. These are entirely different colors, indeed.

[Bluestrike2]

There are (only) a couple dozen different blues Porsche has used over the years. The 641 total is because of how that site lists them by year and model line even though the paint code is the same across them.

It makes more sense when you realize that site's mainly used by autobody repair shops; they're searching by year and model first as manufacturers often use the same name even when it's not technically the same paint (different paint manufacturers, variations in formulation, etc.). That matters with repair work. But for what I was working on, it wasn't relevant.

[vpribish]

that was an awesome writeup. I've wrangled finance data and know the pain. Those Porsche blue links are wonderful!

[SmirkingRevenge]

I think this is probably the general state of data exchange systems in most industries/sectors where an IT company/startup hasn't come along to successfully disrupt it or none of the major players have become tech-oriented enough to see - much less sort out - these kinds problems well.

That's been my experience anyways.

[sidlls]

Hardly. Most of these things are because the companies in these industries have an interest in having it the way it is. It has little to do with (not) being sophisticated with technology or because some startup hasn't come to save the day. Startups tend to actually have a very small impact on anything generally speaking. It's rare that one blossoms into a Google or Facebook.

[benjarrell]

IT companies are no better from my experience.

[elmalto]

I work in the flight industry and still dream of that standardized data structure...

[sbfeibish]

Seems to me what you describe is a business opportunity. Your business takes on the burden of obtaining the data, and putting it in an easy to use form; for others. There's a company in Pensacola, Fl that's been doing this for years with things like lottery numbers and gas prices.

[madeofpalk]

> Unless you own the data, working with it is terrible.

Heh. Having done work for an airline, I wouldnt even say this.

[dorchadas]

Ain't that the truth! I tried just messing around with it a while ago to make my own airfare searcher to check a few things for me and it was a mess. Ended up doing the Amadeus sandbox, but the cheapest flights aren't necessarily there and it's a real pita.

[new_user224]

How did you integrate all the different schemas? RDF, some kind of rule engine, or plain Java, Python, ... ?

[hchasestevens]

Having worked previously at a company that sounds very similar (maybe even the same one?), our approach was primarily using individually written scrapers and API integrations (when available) in Python, utilizing an underlying scraping framework that predated requests. As you might imagine, these integrations required constant maintenance and were often bug-prone, so much so that the company eventually found itself in the position of outsourcing the work... There were attempts to reduce maintenance through the use of a in-house DSL/rules engine, but ultimately, the range of integrations it was able to support was very limited, and the project was scrapped.