SELECT *, ROW_NUMBER() OVER(PARTITION BY userId ORDER BY timestamp DESC) AS rowNum FROM A LEFT JOIN B ON A.timestamp <= B.timestamp and A.date = B.date
And then write another query to take the top row based on timestamp, and for sure this method may lose some data (e.g. what if userId doesn't have data in table B that day?).
My original answer was perhaps a bit terse and should have been more along the lines of "that is what a good schema design should fix". Your original position that "microservices make things more difficult for analysis" is absolutely true, but I guess my observation is that while you might get lucky and have everything you need in one place in your raw data, for any sufficiently complex system (especially if there are 3rd party components involved) you're going to need to do some real work to transform that data to build a repository with a flexible schema to support your analyses and make yours and your colleagues work easier and more productive. You are 100% right in that every layer of indirection makes this slightly more difficult, but unfortunately that is something you need to design for in all but the most simple systems - this is true today the same as it was true 20-30 years ago.
Hopefully you're already doing that sort of design, but my experience is that disciplined schema design for effective analysis is incredibly rare, despite it being an old, crufty, well established and documented area ala Kimball and old school Enterprise Data Warehousing and BI. Not all of it translates perfectly to the world of Big Data(TM) but the fundamental concepts of designing such that your data can be turned into easily queryable and useful measures and dimensions still very much applies.
For your example, the correct correlation ID completely depends on the context in which you need to do your analysis, and that should be ideally baked into your telemetry schema where applicable (and where it makes sense from an architectural/performance/data volume perspective), but if not, then baked into your intermediate ETL schema such that it is easily accessible for analysis. Perhaps it's a contextual or global incrementing index that more easily allows you to get the "latest" transaction per context (maybe a session or a match in your game, or an encounter, or outside of games, a shopping cart checkout flow), maybe your client passes more contextual information to the microservice to allow you to more easily hierarchically partition into your context. Maybe if you don't have that level of control downstream (like in a 3rd party service for example) you have an ETL or streaming job generating the global or local context from the raw telemetry that makes it easy to join against.
And lack of context for analysis isn't even just a microservices issue, it can even happen in any kind of modular software - in the game I'm working on right now, for example, the crafting system has no need to know about overall state from the player, hence the crafting module has no (easy) access to player state that might be relevant for downstream analysis - so even in what would be considered a monolith, getting access to player health/armor/xp/etc in the context of crafting analyses (e.g. do players craft medkits at low health more often than at high health?) is difficult and really uglies things up, much like you imply. But making available a playerStateIndex that can be used to join back to that state (managed by the player state system) is doable and introduces no real problematic module coupling, and is a repeatable and extensible pattern to use across other modules and even external services.
I've spent the better part of half of my 20+ years in the industry doing some form of data analytics, and my message to the analysts/data scientists I work with is always "if you have to do crazy gymnastics to do your analyses then our schema design needs work". You don't want to be in the (all too common) world of analysts spending 80-90% of their time doing data transformations and cleaning and 10-20% of their time doing analysis.
Yeah I figured out that if the query is over say 50 lines long and has weird marks everywhere (say DISTINCT with some windows function) then it's pretty much a bad database design.
Then I move to the data side and figured out that it actually also has something to do with the original raw data sent by the app.
So eventually _someone_ has to take the burden and do the gymnatics and frankly it totally depends on who is closer to the CXO. In our company the developers are in favor so they can do whatever they want.
>Hopefully you're already doing that sort of design, but my experience is that disciplined schema design for effective analysis is incredibly rare, despite it being an old, crufty, well established and documented area ala Kimball and old school Enterprise Data Warehousing and BI. Not all of it translates perfectly to the world of Big Data(TM) but the fundamental concepts of designing such that your data can be turned into easily queryable and useful measures and dimensions still very much applies.
EXACTLY what I'm feeling now. We are trying to work out a DWH on top of the raw telemetries (basically some simple transformations and routing done by Spark from HQ). However I really think that Kimball doesn't fit very well with the big data thing. I don't know how to say it clearly because I just moved to the data team for a few months so I'm a total newbie and everything I _feel_ could be 100% BS. I think we should start from business requirement (i.e. from our data analytics lords), and then bend the rules (Kimball will require a LOT of extra joins if what I'm reading it correctly) and keep the DWH tables sort of flattened but in a way that makes more sense than the original raw telemetry tables.
I'm not in control so I don't know what it will result into.
>For your example, the correct correlation ID completely depends on the context in which you need to do your analysis, and that should be ideally baked into your telemetry schema where applicable (and where it makes sense from an architectural/performance/data volume perspective), but if not, then baked into your intermediate ETL schema such that it is easily accessible for analysis. Perhaps it's a contextual or global incrementing index that more easily allows you to get the "latest" transaction per context (maybe a session or a match in your game, or an encounter, or outside of games, a shopping cart checkout flow), maybe your client passes more contextual information to the microservice to allow you to more easily hierarchically partition into your context. Maybe if you don't have that level of control downstream (like in a 3rd party service for example) you have an ETL or streaming job generating the global or local context from the raw telemetry that makes it easy to join against.
It's...not really easy to come up with an intermediate "common context" id sometimes. Session Id doesn't work because each session takes a lot of time and nothing else really are real-time. Since I'm the only guy in the company for requirements taking (I really think developers should have their own business analyst), it's pretty painful that each time I have to fight both sides. I have to ask the DA team to relax their requirements (realtime? No it's not possible, how about same session?) and ask the developers to bestow more connection fields (if you have this field DA will be really happy), thus the vent above :D