Ask HN: How do you version your data?

Do you mean schema, not data?

I've seen three schemes used in production:

- single number versioning (V1->V2->V3->V4) - this is the scheme Android's sqlite helper insists on https://developer.android.com/reference/android/database/sql...

- full version numbers, but well defined upgrade tracks enforced in code.

- token based (e.g. a list of upgrades that have been applied)

I suggest you look into schema migration tools.

What I have done in the past is to prefer lazy migrations (migrate once you access an old record), rather than migrating everything in batch.

Also a good idea to archive data that hasn't been used, moving it outside your primary database.

I wrote[0] about a method I've used with success in the past. Essentially you use the previous version's hash as the name for the next version. The benefit is that merging the work of multiple developers is easier. I ran the process manually when I did it and would love to hear if someone writes a script that makes the process easier.

[0]: https://medium.com/@clord/for-migration-of-schemas-use-versi...

Our migrations have unique datetime stamps. Stamps and migration names go into a table.

We do have a hash of all of the contents of the table, but the schema "version" is just the list of migrations that have been run. We run many different instances of our applications on several different "versions" and from time to time pull individual migrations back into earlier releases for hotfixes etc. The overall version is the instance, environment, and this hash.

Practically speaking, when we're handling the files ourselves though, it's instance, environment and backup date.

There are edge cases and it's not perfect (around ordering mostly), but it's incredibly rare for us to run into issues with this schema / data migration approach. We've found that even with a bunch of environments and a couple thousand migrations, data versioning isn't that serious a problem for us.

I sometimes do it within the same table. It's a bit of a hack. What you want is a column called revision_id (something to point back to the master record). You'd want a state column to track if it's a draft, revision, active, etc. You might also want a sequence column too, so that when you apply drafts, you can compare if the draft can modify the master record (if sequence column doesn't match, you prevent the draft from overwriting master record data). Upon applying a draft to the master record, you do a deep clone of the existing record, insert the existing data as a new row pointing back to the master row via revision_id.

ALL migrations insert major.minor.patch to the database. Migrations are committed to the database, and can be run anytime; they each have check code at the beginning to compare against what's in the database before executing the payload. Migrations won't execute if the version is semantically less or too far ahead from what's in the database.

It's worked reasonably well so far.

Check out Pachyderm [0]. It supports distributed, version-controlled data storage. The API is very Git-like: you modify data by making commits.

[0] https://github.com/pachyderm/pachyderm

In Data warehouse context, it is often managed by one of the Slowly Changing Dimension management techniques.

https://en.m.wikipedia.org/wiki/Slowly_changing_dimension

Have seen two ways of doing in large organizations 1. Use tools like Optim (i think ibm) 2. Export CSV data, and export schema and drop it to Hadoop data store

Option 2 is preferred for very long time restore say after 15 years as there may not be a way to restore to Oracle/MS Products which may not have run time ecosystem after 15 years.

This project seems to be making progress for WordPress: https://versionpress.net/

On a somewhat related note, I have an API that returns time series data. The API itself is versioned but not the data when it is added to since the endpoint is the same. How would I go about notifying consumers of the API that the data has been updated and they should call the API to get the latest data? Would versioning the data help with this?

My hack way is to dump the tables to text and commit that. That was done by cron and not coupled with committing the code.

Granted, I was interested in the data within the table not the structure and the size was manageable.

The most RESTFul way to do this would be to use content negotiation using the `content-type` header set to something like `application/com.vendor.product+json; data-version=2.1; api-version=3.4` where the minor version indicates data changes and the major version indicates schema changes to the data. You can club together the API and data versions into one version like '3.4.2.1' if you can define what a major/minor change to the API means. Exact details on how the client is exposed to the versioning will depend on the product requirements.

In terms of storing the data, we had a system where the content would be zipped after the content developers were done with the authoring and sent to a place which would convert it into appropriate JSON documents with the metadata and versioning information stored in the DB while the the document content could be stored in the cloud or a document database like Mongo or just Postgres. The content authors only knew excel who were trained to follow a schema while writing the content. That was like a low cost CMS. You can update only the content that has a diff or the entire content depending on how well you can identify a diff for the content. The entire content makes it simple.

I consider data to be a first class object. I break it into data in motion and data at rest.

Data in motion - messages - always look something like this:

    message := version timesent field1 field2 ... fieldn
    version := INTEGER
    time-sent := INTEGER

and the parsers know to reject messages with versions greater than what they can parse; depending on the system, they can also be backwards compatible. Time-sent turns out to be a lifesaver in debugging. You might also need TZ of time-sent, depends on the domain.

Versioning data at rest tends to be a little squirrely depending on the domain. Do you migrate data or do you not? what's your uptime? streaming or batch? Sometimes I version the actual table names, sometimes I migrate.. it depends. My preference is for migration to keep a consistent system, but that is not always feasible.

I'm a huge fan of SQL - it defines the data shape and structures the transforms possible on it, along with allowing a strong separation of data and computation. Postgres is my friend; I heavily use foreign keys and constraints on the schema. That way the data is reliable. (if your data isn't reliable, your schema should reflect that too of course). If I need to have multiple versions of data running at the same time, multiple tables or migrating is cleaner than versioning the specific rows. Otherwise you wind up with nulls and driving schema logic out into your code.

Typically I tack a unix time of insert into the rows for later analysis. You might also care to insert the current application name+version into the rows to catch any bugaboos when that changes.

Edit: I realized that your question is just about the versioning scheme (the "what"), and my answer is more about the "how". I hope you still find it useful.

After being inspired by Hans Werner's answer here (several terabytes of binary data, 50000+ revisions), I chose Subversion. It's not conventional, but works very well in practice.

https://stackoverflow.com/questions/127692/svn-performance-a...

You get:

a) Natural audit trail & notes on data modification

b) Managed central dump of data => multiple, distributed local copies that you don't need to worry about keeping in sync. Just delete the cache and the data access API (see below) will check it out automatically again when you request the file.

Data access is encapsulated via an API that manages a /home/datadump/ of cached, revisioned files. You refer to the file using it's name + revision number (see below). I guess tags and branches can be used for more natural revison numbers, but I need to investigate whether Subversion's cheap copy works well in practice for this. They might be the perfect solution for you major.minor.patch needs?

User Code --> get_data("<file_path/file_name>", "r=38") --> API checks the cache for file_path/file_name_r=38. If it's not there it checks it out using a read-only user id and puts it in the cache and returns the path to /home/datadump/file_path/file_name_r=38.

Unusual, but works just fine for our purposes of mostly-read-only large files that need a revision history.

An idea that I did not explore was using ZFS or other revisioned file systems. Another "crazy" idea that works just fine for some folks is using Binary blobs in a database; not sure about size limits, though.

You might want to check Datomic out. They have a solution for your problem: http://www.datomic.com

Depends on what it is, either semver for things where backward compatibility is necessary or just "version 1, version 2 etc." where a new version is a big enough change you should consult the documentation to know what is going on.

I use protobuf schema evolution. It's the same approach that Google use (naturally), and Facebook (with Thrift), among others.

Great question - we invented a system for this at Snowplow, called SchemaVer:

http://snowplowanalytics.com/blog/2014/05/13/introducing-sch...

SemVer doesn't work for data - for one thing, there is no concept of a "bug" in your data (so patches are meaningless).

We have hundreds of companies actively using SchemaVer via the Snowplow (https://github.com/snowplow/snowplow/) and Iglu (https://github.com/snowplow/iglu/) projects.

There are several tools out there for doing this, if you're storing your data in databases.

SQL Server has Master Data Services. There's also Talend. There are other master data management tools available.

Depending on how you use your data, there are tools for improving data quality that tie in with those as well.

CQRS and event sourcing is an approach where all data changes are versioned. You can re-create earlier states of the data by processing a portion of the event log.