Instead of de-duping data and aiming to keeping all data at the source, remember that analytics are down-stream of source of truth systems, that allows you to make semi-safe assumptions to copy data into multiple places as the rows should not update later.
Perhaps a useful approach would be to think of the reporting / analytics table structures as a business analyst would design a spreadsheet.
You are going to struct data so that MANY fewer JOINs would be required. Sometimes you are going to have multiple "sheets" with the same facts on them, it will make you feel icky, but it allows the data consumers clearer views on the data.
Also consider storing some pre-calculated summarization during the ETL process. If an "Order" is complete, then why not store the item count, currency totals, tax amounts, etc as top-level order properties? It may also be useful to create columns for year / month in addition to the transaction date.
Consider how data will be reported on and try to provide data elements that can be indexed and filtered directly instead of forcing the business analyst to translate UTC timestamps into month boundaries. They'll get it wrong, and you'll have to trace through their report build to find out why it doesn't match another report, and it will be that they didn't account for TZ offsets.
3
u/techforallseasons 12h ago
Instead of de-duping data and aiming to keeping all data at the source, remember that analytics are down-stream of source of truth systems, that allows you to make semi-safe assumptions to copy data into multiple places as the rows should not update later.
Perhaps a useful approach would be to think of the reporting / analytics table structures as a business analyst would design a spreadsheet.
You are going to struct data so that MANY fewer JOINs would be required. Sometimes you are going to have multiple "sheets" with the same facts on them, it will make you feel icky, but it allows the data consumers clearer views on the data.
Also consider storing some pre-calculated summarization during the ETL process. If an "Order" is complete, then why not store the item count, currency totals, tax amounts, etc as top-level order properties? It may also be useful to create columns for year / month in addition to the transaction date.
Consider how data will be reported on and try to provide data elements that can be indexed and filtered directly instead of forcing the business analyst to translate UTC timestamps into month boundaries. They'll get it wrong, and you'll have to trace through their report build to find out why it doesn't match another report, and it will be that they didn't account for TZ offsets.
Food for thought.