We have a lot of data that we would like to know the state of at a particular time. For instance, model A has dimensions X and Y, and I would like to know what X,Y were yesterday, two days ago, now, etc. Has anyone built a schema/lookml model that works well for these types of queries? I’d love to hear what strategies have been used.
Already have an account? Login
Login to the community
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
@Kallin_Nagelberg - great question. I’d encourage you to check out this block to deal with state data? Hopefully this helps answer some questions.
@Dillon_Morrison, that’s pretty interesting! Have you tried accomplishing the same thing using only one date column? What if you had just a ‘state_changed_time’ column, and then you only have to filter on the one value instead of filtering both start and end time on each query?
[edit:deleted in favor of Kevin’s comment below]
Include Start and End date for dimension row states in your dimension tables table (standard Type 2 SCD approach) - use a required
As Atfilter to bring back the dimension in the state at that time
You need both the “start” and “end” timestamps for two purposes:
Hope that helps! This pattern is incredibly powerful and is also a lot of fun 🙂