I’ve begun exploration of creating a transformed database (data warehouse) for use with looker. Right now we just have Looker hooked up to a replica of our production DB, which has worked great in the short-term, but is showing some limitations.
In order to not go into this too blindly I’ve been doing some reading on data warehouse best practices, including ‘The Data Warehouse Toolkit’, which from what I can gather is one of the must read books in this space.
In this book they are quite adamant about the creation of a ‘Date’ table, instead of using date fields directly in your fact tables. Benefits include being able to assign dimensions to dates such as ‘holiday’, ‘quarter’, ‘weekend’, etc. that aren’t readily accessible in SQL. Of course, Looker does have some helpful date/time tools allowing easy grouping by month/week etc.
So, as I begin down this road I wonder how important these classical warehouse approaches are when using a tool like looker, as it seems much of what looker does is to make a non-warehouse DB act like an easily explored analytical warehouse.
If anyone has any ideas/opinions on the topic, and specifically if you have a preference about creating a date table, I’d love to hear it.