4 ms·

You're blaming a lot of normal ETL problems on DSVs. Like, specifying date as a type for a field in JSON isn't going to ensure that people format it correctly

by mcdonje 6mo ago

You're blaming a lot of normal ETL problems on DSVs. Like, specifying date as a type for a field in JSON isn't going to ensure that people format it correctly and uniformly. You still have parsing issues, except now you're duplicating the ignored schema for every data point. The benefit you get for all of that overhead is more useful for network issues than ensuring a file is well formed before sending it. The people who send garbage will be more likely to send garbage when the format isn't tabular. There are types and there is a spec WHEN YOU DEFINE IT. You define a spec. You deal with garbage that doesn't match the spec. You adjust your tools if the garbage-sending account is big. You warn or fire them if they're small. You shit-talk the garbage senders after hours to blow off steam. That's what ETL is. DSVs aren't the problem. Or maybe they are for you because you're unable to address problems in your process, so you need a heavy unreadable format that enforces things that could be handled elsewhere.

jcattle 6mo ago
I would kind of disagree. We are talking here in the context of scientific datasets. Of course ETL plays a part here. However here it is really more the interplay of Excel with CSV which is often outputted by scientific instruments or scientific assistants. You get your raw sensor data as a csv, just want to take a look in excel, it understandably mangles the data in attempt to infer column types, because of course it does, its's CSV! Then you mistakenly hit save and boom, all your data on disk is now an unrecoverable mangled mess. Of course this is also the fault of not having good clean data practices, but with CSV and Excel it is just so, so easy to hold it wrong, simply because there is no right. > so you need a heavy unreadable format I prefer human unreadable if it means I get machine readable without any guesswork.
- mcdonje 6mo ago
  That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem. It's not the fault of the file formats Excel reads in.
  - thunderfork 6mo ago
    The file format being ambiguous and underspecified enough to mangle is, though.
    - mcdonje 6mo ago
      No, it's Excel trying to be too clever. It does the same thing with manual imput if you don't proactively change the field type. You can import a DSV into Excel without mangling datatypes in a few different ways. Probably the best way is using Power Query. A DSV generally does have a schema. It's just not in the file format itself. Just because it isn't self-describing doesn't mean it isn't described. It just means the schema is communicated outside of the data interchange.
  - jcattle 6mo ago
    If you get an .xls which doesn't have very esoteric functions, I expect it to open about the same way in any Excel program and any other office suite. With CSV I do not have that expectation. I know that for some random user-submitted CSVs, I will have to fiddle. Even if that means finding the one row in thousand rows which has some null value placeholder, messing up the whole automatic inference.
    - mcdonje 6mo ago
      You're just saying when there's no filetype transfer, you don't have to deal with issues related to filetype transfer.
      - jcattle 6mo ago
        No. That's not at all what I'm saying. I am saying that a fixed CSV file will open differently depending on the program you open it with. Don't even need to transfer it. Opening a csv in pandas can be different than opening with polars, can be different to DuckDB, can be different to Excel. You've got not guarantees. There's no spec, and how edge cases (if you want to call how to serialize and deserialize a float an edge case) are handled is open to the implementation.

by mcdonje 6mo ago

jcattle 6mo ago

I would kind of disagree. We are talking here in the context of scientific datasets. Of course ETL plays a part here. However here it is really more the interplay of Excel with CSV which is often outputted by scientific instruments or scientific assistants. You get your raw sensor data as a csv, just want to take a look in excel, it understandably mangles the data in attempt to infer column types, because of course it does, its's CSV! Then you mistakenly hit save and boom, all your data on disk is now an unrecoverable mangled mess. Of course this is also the fault of not having good clean data practices, but with CSV and Excel it is just so, so easy to hold it wrong, simply because there is no right. > so you need a heavy unreadable format I prefer human unreadable if it means I get machine readable without any guesswork.

mcdonje 6mo ago
That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem. It's not the fault of the file formats Excel reads in.
- thunderfork 6mo ago
  The file format being ambiguous and underspecified enough to mangle is, though.
  - mcdonje 6mo ago
    No, it's Excel trying to be too clever. It does the same thing with manual imput if you don't proactively change the field type. You can import a DSV into Excel without mangling datatypes in a few different ways. Probably the best way is using Power Query. A DSV generally does have a schema. It's just not in the file format itself. Just because it isn't self-describing doesn't mean it isn't described. It just means the schema is communicated outside of the data interchange.
- jcattle 6mo ago
  If you get an .xls which doesn't have very esoteric functions, I expect it to open about the same way in any Excel program and any other office suite. With CSV I do not have that expectation. I know that for some random user-submitted CSVs, I will have to fiddle. Even if that means finding the one row in thousand rows which has some null value placeholder, messing up the whole automatic inference.
  - mcdonje 6mo ago
    You're just saying when there's no filetype transfer, you don't have to deal with issues related to filetype transfer.
    - jcattle 6mo ago
      No. That's not at all what I'm saying. I am saying that a fixed CSV file will open differently depending on the program you open it with. Don't even need to transfer it. Opening a csv in pandas can be different than opening with polars, can be different to DuckDB, can be different to Excel. You've got not guarantees. There's no spec, and how edge cases (if you want to call how to serialize and deserialize a float an edge case) are handled is open to the implementation.