Why I Stopped Worrying and Discovered to Love Denormalized Tables
Hey, I am Andrew and I am a product supervisor at Glean. Beforehand, I’ve labored on analytics in quite a lot of roles; analyst, knowledge engineer, visualization specialist, and knowledge scientist.
- Normalized tables: Tables designed to keep away from repeating info, conserving knowledge organized and straightforward to take care of.
- Denormalized tables: Tables which have repeated info, however make knowledge retrieval sooner and easier to know. aka One Huge Desk (OBT)
After years of meticulously modeling knowledge in relational databases and avoiding duplication in any respect prices, I’ve come full circle on the ability of huge flat tables for analytics.
On this publish, I will share my journey of how I’ve ended up embracing denormalized tables, how you can use instruments like dbt to bridge normalized and denormalized tables, and the way denormalized knowledge accelerates exploratory knowledge evaluation and self service analytics.
Shortly answering questions with spreadsheets and dataframes
Beginning out as an analyst, I first discovered to investigate knowledge in Excel, then in Jupyter notebooks and R Studio. I spent loads of time in dwell Q&A periods with stakeholders answering questions in regards to the metrics that mattered to them and determining why, why, and why.
Questions like “Are the metrics going up or down?” or “Why did income dip this present day?” or “How does this phase examine to this one?” have been widespread.
I may often begin off with a easy SQL question, however as we layered on enterprise logic, the variety of joins would inevitably explode. I’d must pause the dwell Q&A and comply with up after I untangled the question I had generated.
I rapidly discovered that writing one large question with a bunch of joins and even bunch of Python helper capabilities may get me caught. My transformation capabilities weren’t versatile sufficient, or my joins have been too sophisticated to reply the infinite number of questions thrown my approach whereas conserving the numbers right.
As an alternative, the simplest solution to be quick, nimble, and reply all of the surprising questions was to arrange a large desk or dataframe and restrict myself to it. So long as I understood the desk’s contents, it was tougher to make errors. I may group by and combination on the fly with confidence.
Conveniently, I additionally discovered this made transporting knowledge throughout quite a lot of instruments actually handy. I may play with the identical desk in Excel pivot tables or a Pandas pocket book since I may export to a single CSV.
Studying to mannequin knowledge
As my technical abilities grew, I took on extra duties corresponding to knowledge ingestion, ETL pipelines, and software constructing. Alongside the way in which, I discovered about knowledge modeling and schema design to take care of organized and simply maintainable databases.
choose identify from customers simply is sensible! I used to be a agency believer in the usual practices of normalization and I started my tasks desirous about international key relationships and indexing.
I believed that duplicate knowledge would result in inefficiencies and errors, so I averted it in any respect prices.
Nonetheless, as I gained extra expertise and commenced constructing instruments for exploratory knowledge evaluation, I found a shock: these huge, flat tables I used to make as an analyst have been nonetheless extremely highly effective and priceless for exploring knowledge.
With out the flat desk within the warehouse, I’d find yourself having the identical huge question or sequence of Pandas spaghetti in a dozen totally different locations. Inevitably, the logic would drift and I couldn’t depend on older sources.
I began baking the be part of and filter logic into database views so I may have freshly ready knowledge with a constant construction. I may run ETL right into a normalized schema that made it simple to validate knowledge hygiene and high quality, however nonetheless rapidly question knowledge utilizing the denormalized view. It turned out normalizing my knowledge was serving to me denormalize my knowledge.
The Aha Second: Leveraging dbt and fashionable warehouses to unlock the ability of denormalization
Transformation instruments corresponding to dbt (Information Construct Instrument) have revolutionized the administration and upkeep of denormalized tables. With dbt, we will set up clear relationships between desk abstractions, create denormalized analytics datasets on prime of them, and guarantee knowledge integrity and consistency with assessments.
Trendy warehouses, corresponding to Snowflake and BigQuery, mixed with ELT (extract load remodel) patterns allowed me to simplify my pipeline code right into a bunch of SQL and usually by no means have to fret in regards to the quantity of information I used to be processing. The superb question efficiency of denormalized tables in fashionable warehouses additionally make it faster to run analyses.
I may now safely construct an analytics desk of tens of millions of data and construct many various visualizations and analyses with easy filters and aggregations, no complicated joins or CTE’s required. The various columns ensured I may flexibly create the collection and aggregations I wanted.
If there have been any knowledge high quality points, I may simply rebuild my tables with dbt and the brand new knowledge would circulation into my charts and dashboards.
Conclusion
Denormalized tables prioritize efficiency and ease, permitting knowledge redundancy and duplicate information for sooner queries. By embracing denormalization, we will create environment friendly, maintainable knowledge fashions that promote insightful evaluation.
So, why not give denormalized tables an opportunity? You may simply end up questioning why you ever apprehensive within the first place.
For those who’re searching for a BI software that totally takes benefit of denormalized tables and the remainder of the fashionable knowledge stack, we’re constructing one at Glean! We’re constructed for fast and agile metrics exploration, with highly effective knowledge visualizations and tables. Come test us out!