6 ms·

I'd say better to export tables to CSV files, and dump the schema to an SQL file.

seveibar 6y ago
Having worked with a lot of government CSV/XLSX datasets (e.g. IPEDS college reporting), I can say I'd prefer something with proper foreign keys and less ambiguity in structure (people like to put descriptive cells at the top of spreadsheets)
- qwerty456127 6y ago
  Proper foreign keys don't really suit the dumped data case. Tables are usually imported one by one, in virtually random order so dependencies are often missing at the import time. Even if they were not, checking keys on every record import would mean a huge load for big datasets - that's really slow. That's why foreign key control is usually switched off before importing.
  - mulmen 6y ago
    Postgres at least can defer these checks, it's generally a best practice to disable rebuilding indexes while loading as well. It's not hard to simply load the tables in order so the keys are available. I would expect any decent unload utility to also create a script to load the data which should be in the right order. I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.
    - IanCal 6y ago
      Even without checks, there's a benefit to having documented links like this tied to the data release rather than a random website.
- paulfitz 6y ago
  Grist is an sqlite-based spreadsheet format that captures relations well and discourages funky structure https://www.getgrist.com/blog https://www.getgrist.com/blog - it works well for passing relational datasets around (disclosure, I work on it)
  - kroltan 6y ago
    (the poster is associated with the product)
geocar 6y ago
CSV isn't a single format but many incompatible formats that require additional metadata to decode, such as: - whether quotes are ""doubled like SQL92 or backslash-\"escaped like C - whether newlines are quoted or \n like C - whether there are column headings - what the character set of the file is (Excel insists it's the local machine's codepage) - whether that's a BOM or part of a column heading - whether lines terminate with \n or \r\n or \r or \r\n - what to do about ragged rows and so on. Except for the simplest of datasets, CSV is almost certainly going to lose information. The only advantages over SQLite is streaming support and maybe compression, but even then there are better formats.
- mulmen 6y ago
  There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs. I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.
  - arethuza 6y ago
    I saw a problem recently where a file had a .csv extension but was actually colon delimited. The data contained fields with large quantities of text so a CSV parser happily parsed the file splitting lines on commas in the text and didn't complain so the problem wasn't noticed until much later in the processing of the data.
    - dspillett 6y ago
      One of our clients internally uses an odd text file format with ~ as the field delimiter and a fixed-width based header & footer containing metadata. And still sometimes send such files with names ending .csv. ~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.
mulmen 6y ago
For database dumps I prefer escaped TSVs personally but the delimiter doesn't really matter as long as it is consistent. Strict adherence to quoting, escaping and character sets does, along with tracking that metadata. If you aren't comfortable using a capital A as a delimiter your format is not robust enough for the real world. Or you could just take the entire engine with you, which is what SQLite provides.
iagovar 6y ago
CSV is a bad a idea if you work with text or string. I work a lot with data transformation, and CSV with text usually means headache. Not to mention if you treat data from different countries. Excel is too limited, odf formats are typically not very extended outside OO or LOffice..., same with parquet and other file formats. Sadly this is how it is. You can use SQLite as a file for moving data around, as I do, but I guess it's not practical for everyone.

Having worked with a lot of government CSV/XLSX datasets (e.g. IPEDS college reporting), I can say I'd prefer something with proper foreign keys and less ambiguity in structure (people like to put descriptive cells at the top of spreadsheets)

qwerty456127 6y ago
Proper foreign keys don't really suit the dumped data case. Tables are usually imported one by one, in virtually random order so dependencies are often missing at the import time. Even if they were not, checking keys on every record import would mean a huge load for big datasets - that's really slow. That's why foreign key control is usually switched off before importing.
- mulmen 6y ago
  Postgres at least can defer these checks, it's generally a best practice to disable rebuilding indexes while loading as well. It's not hard to simply load the tables in order so the keys are available. I would expect any decent unload utility to also create a script to load the data which should be in the right order. I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.
  - IanCal 6y ago
    Even without checks, there's a benefit to having documented links like this tied to the data release rather than a random website.
paulfitz 6y ago
Grist is an sqlite-based spreadsheet format that captures relations well and discourages funky structure https://www.getgrist.com/blog https://www.getgrist.com/blog - it works well for passing relational datasets around (disclosure, I work on it)
- kroltan 6y ago
  (the poster is associated with the product)

geocar 6y ago

CSV isn't a single format but many incompatible formats that require additional metadata to decode, such as: - whether quotes are ""doubled like SQL92 or backslash-\"escaped like C - whether newlines are quoted or \n like C - whether there are column headings - what the character set of the file is (Excel insists it's the local machine's codepage) - whether that's a BOM or part of a column heading - whether lines terminate with \n or \r\n or \r or \r\n - what to do about ragged rows and so on. Except for the simplest of datasets, CSV is almost certainly going to lose information. The only advantages over SQLite is streaming support and maybe compression, but even then there are better formats.

mulmen 6y ago
There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs. I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.
- arethuza 6y ago
  I saw a problem recently where a file had a .csv extension but was actually colon delimited. The data contained fields with large quantities of text so a CSV parser happily parsed the file splitting lines on commas in the text and didn't complain so the problem wasn't noticed until much later in the processing of the data.
  - dspillett 6y ago
    One of our clients internally uses an odd text file format with ~ as the field delimiter and a fixed-width based header & footer containing metadata. And still sometimes send such files with names ending .csv. ~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.

mulmen 6y ago

For database dumps I prefer escaped TSVs personally but the delimiter doesn't really matter as long as it is consistent. Strict adherence to quoting, escaping and character sets does, along with tracking that metadata. If you aren't comfortable using a capital A as a delimiter your format is not robust enough for the real world. Or you could just take the entire engine with you, which is what SQLite provides.

iagovar 6y ago

CSV is a bad a idea if you work with text or string. I work a lot with data transformation, and CSV with text usually means headache. Not to mention if you treat data from different countries. Excel is too limited, odf formats are typically not very extended outside OO or LOffice..., same with parquet and other file formats. Sadly this is how it is. You can use SQLite as a file for moving data around, as I do, but I guess it's not practical for everyone.