3 ms·

While I am all for companies and software to expedite data cleaning, I don't think any software can cleanly solve this problem in the near future. 1. Data hygi

by kiyoto 12y ago

While I am all for companies and software to expedite data cleaning, I don't think any software can cleanly solve this problem in the near future. 1. Data hygiene issues show up inconsistently, even within the same data source: I experienced this first-hand as a quant trader: I only had half a dozen, well-structured time series data coming from our trading apps and switches. You would think that I can get reasonably clean data every day, or failing that, being able to automate the data cleaning script. Nope! I experienced every data hygiene issue imaginable: from ntpd being broken, local time/UTC inconsistencies, switch firmware acting up, all the way to human errors in the ETL process. Building a reliable data pipeline has so many moving parts that I am fairly pessimistic about any one-stop solution. 2. Then there is a volume issue. If the data is small enough, humans can correct them fairly reliably, especially with some help from automation scripts/software. That said, doing this at scale is a very hard problem. One thing I learned working at a big data company is that many folks use MapReduce as a data cleanser at scale, and for that, MapReduce is a pretty awkward tool. 3. Anecdotal evidence: I have talked to employees at various big data platform/software companies, and though they have a wide ranging opinions on stream processing, Hadoop, Spark, etc., they all agree data cleaning is a huge pain/deal-slowing/-killing nemesis/unsolved problem that their employers semi-solve with increasing Sales Engineer headcounts. If this issue of solving data cleaning at scale was easy, I feel that someone would have come up with a very effective answer by now (and as an industry insider, I should have heard about it).

rpedela 12y ago
I completely agree. However I think there is also plenty of room for improvement in the ETL process that doesn't necessarily mean a one-stop solution. For example, the common denominator data format for many use cases (not all) is a relational database. Why isn't there something that can grab data from anywhere in any common format and import it into a relational database with only a couple commands or mouse clicks? Right now the situation is that you either have to pay a lot of money for such a tool which usually doesn't work very well or you piece together the various data conversion tools with scripts. There is of course a lot of other steps in the ETL process, but that alone would save a lot of time.
- kiyoto 12y ago
  >However I think there is also plenty of room for improvement in the ETL process that doesn't necessarily mean a one-stop solution. I agree with you here as well. I recently wrote a blog article about this, at least for log data > http://www.fluentd.org/blog/unified-logging-layer http://www.fluentd.org/blog/unified-logging-layer
  - rpedela 12y ago
    Very cool! I guess my particular ETL complaint above is solved for log data. :)
- akbar501 12y ago
  > Why isn't there something that can grab data from anywhere in any common format and import it into a relational database IME, the issue is not tooling so much as dirty data. Even if a tool is a perfect solution, the data is (in the majority of companies) so dirty and inconsistently sparse that automated tooling breaks down.
  - rpedela 12y ago
    Absolutely. I was trying to make a larger point that often the tooling for the stuff that can be easily automated or semi-automated is currently sub-par. If we fixed that tooling as a community then that would reduce the majority of the "janitorial work" to fixing dirty data which is definitely a harder problem. In other words, I think there is still a lot of low-hanging fruit in the ETL process.
capkutay 12y ago
> If this issue of solving data cleaning at scale was easy, I feel that someone would have come up with a very effective answer by now (and as an industry inside, I should have heard about it). Agreed. Even though all these companies in the 'big data' space claim they do everything under the sun because they run on hadoop or spark, data cleansing is still an area people are hesitant to dive into. Rightly so. What surprises me more is that ETL is also widely over-marketed by a lot of new big data companies. I don't think people have paid enough respect to how subtle the 'transform' stage can really be.
- johan_larson 12y ago
  Yes. I work for Informatica on a data integration product -- taking streams of similar but somewhat different data and trying to determine the actual state of things. It's often used for merging customer lists, for example. We have found that determining the best value of truth from incomplete and sometimes conflicting data sources is one of these messy problems with no easy answers. It takes a lot of fiddling with match rules and considerable domain knowledge to get good results. Accordingly, our product is designed to be very flexible and configurable. I suspect improvements will come from making it easier for analysts to see a) the effects of proposed match rule changes and b) what decisions contributed to producing each output record under the current rules.
deepGem 12y ago
Regarding 1 - Can this be solved by some sort of a structured data training set - that can be trained by some variant of a classifier. Building the training set will be a giant PITA but would this be some sort of a first step in the data cleansing problem ?

by kiyoto 12y ago

rpedela 12y ago

I completely agree. However I think there is also plenty of room for improvement in the ETL process that doesn't necessarily mean a one-stop solution. For example, the common denominator data format for many use cases (not all) is a relational database. Why isn't there something that can grab data from anywhere in any common format and import it into a relational database with only a couple commands or mouse clicks? Right now the situation is that you either have to pay a lot of money for such a tool which usually doesn't work very well or you piece together the various data conversion tools with scripts. There is of course a lot of other steps in the ETL process, but that alone would save a lot of time.

kiyoto 12y ago
>However I think there is also plenty of room for improvement in the ETL process that doesn't necessarily mean a one-stop solution. I agree with you here as well. I recently wrote a blog article about this, at least for log data > http://www.fluentd.org/blog/unified-logging-layer http://www.fluentd.org/blog/unified-logging-layer
- rpedela 12y ago
  Very cool! I guess my particular ETL complaint above is solved for log data. :)
akbar501 12y ago
> Why isn't there something that can grab data from anywhere in any common format and import it into a relational database IME, the issue is not tooling so much as dirty data. Even if a tool is a perfect solution, the data is (in the majority of companies) so dirty and inconsistently sparse that automated tooling breaks down.
- rpedela 12y ago
  Absolutely. I was trying to make a larger point that often the tooling for the stuff that can be easily automated or semi-automated is currently sub-par. If we fixed that tooling as a community then that would reduce the majority of the "janitorial work" to fixing dirty data which is definitely a harder problem. In other words, I think there is still a lot of low-hanging fruit in the ETL process.

capkutay 12y ago

> If this issue of solving data cleaning at scale was easy, I feel that someone would have come up with a very effective answer by now (and as an industry inside, I should have heard about it). Agreed. Even though all these companies in the 'big data' space claim they do everything under the sun because they run on hadoop or spark, data cleansing is still an area people are hesitant to dive into. Rightly so. What surprises me more is that ETL is also widely over-marketed by a lot of new big data companies. I don't think people have paid enough respect to how subtle the 'transform' stage can really be.

johan_larson 12y ago
Yes. I work for Informatica on a data integration product -- taking streams of similar but somewhat different data and trying to determine the actual state of things. It's often used for merging customer lists, for example. We have found that determining the best value of truth from incomplete and sometimes conflicting data sources is one of these messy problems with no easy answers. It takes a lot of fiddling with match rules and considerable domain knowledge to get good results. Accordingly, our product is designed to be very flexible and configurable. I suspect improvements will come from making it easier for analysts to see a) the effects of proposed match rule changes and b) what decisions contributed to producing each output record under the current rules.

deepGem 12y ago

Regarding 1 - Can this be solved by some sort of a structured data training set - that can be trained by some variant of a classifier. Building the training set will be a giant PITA but would this be some sort of a first step in the data cleansing problem ?