This is also the direction a lot of data engineering is going, where systems are going from ETL to ELT (that is loading data with minimal transformations as soon as possible and doing complicated transforms after that). This is e.g. why tools such as dbt are getting popular.
But I suspect OP's point is probably that you do at least impose a schema on import rather than letting it just be featureless JSON blobs or EAV tuples, which makes the job of understanding that data later so much easier.
Interesting. I did this for medical records, mid-aughts.
The phrase I used was "transform & translate at the last possible moment". Ideally in the reporting step (server side rendering the HTML).
Since all of our customers kept changing their minds, totally their prerogative, my strategy minimized cost of change and turnaround time. Prior products would have to "rerun the data" from scratch, for schema changes. We'd make the same query tweaks, but at the end instead of the beginning of the pipeline. (Hardest part was always nailing down the requirements, expectations. Then the code basically wrote itself.)
This strategy made people's heads explode. Old schoolers just couldn't abide by it. Despite our small team running circles around everyone else. Data must be normalized! All the same! We had to be cheating somehow. They did everything possible to destroy us, and eventually succeeded.
Comments
This is also the direction a lot of data engineering is going, where systems are going from ETL to ELT (that is loading data with minimal transformations as soon as possible and doing complicated transforms after that). This is e.g. why tools such as dbt are getting popular.
But I suspect OP's point is probably that you do at least impose a schema on import rather than letting it just be featureless JSON blobs or EAV tuples, which makes the job of understanding that data later so much easier.
+1 to both. "Store things in a schema that reflects the origin" is schema-on-write, just a very particular kind of it.
Interesting. I did this for medical records, mid-aughts.
The phrase I used was "transform & translate at the last possible moment". Ideally in the reporting step (server side rendering the HTML).
Since all of our customers kept changing their minds, totally their prerogative, my strategy minimized cost of change and turnaround time. Prior products would have to "rerun the data" from scratch, for schema changes. We'd make the same query tweaks, but at the end instead of the beginning of the pipeline. (Hardest part was always nailing down the requirements, expectations. Then the code basically wrote itself.)
This strategy made people's heads explode. Old schoolers just couldn't abide by it. Despite our small team running circles around everyone else. Data must be normalized! All the same! We had to be cheating somehow. They did everything possible to destroy us, and eventually succeeded.
I’ve had a lot of success having a Postgres table with a json blob and then a bunch of columns of interest, populated by a trigger.
This works surprisingly well.
I end up just doing both - having one table store the blob, and another with the copied values (shopify_sales, shopify_sales_jsonb).
Databases are generally pretty good at handling large BLOBS/off-page heap values, but I prefer not to tempt the fates of buffer pool management.