2

I am trying to prepare myself for some interviews at a FAANG, while coming from a much smaller company. Part of the interview process involves data modeling and ETL design. Kimball's "The Data Warehouse Toolkit" describes a dimensional model that represents a company, like Target or Walmart, retail sales and its associated dimensions. However, Kimball seems to suggest that although dimensions (date, store, customer, product, etc) are highly denormalized, fact tables are normalized with FKs to their related dimensions.

I have heard companies like mine using OBT or the "One Big Table" approach to make analysts lives easier and increase query performance by removing the need for JOINs. Also with storage being so cheap these days, and most modern DWs (Redshift, BiqQuery, etc) using a columnar based architecture, I would think we could safely throw out concerns regarding the extra redundancy (of having the same data appear twice in two tables), but is it possible that when architecting for huge applications that redundancy becomes an issue? What is the correct way of designing a data warehouse for each use case?

3 Answers 3

2

Not sure if this is the answer, but I can give you my thoughts based on experience.

If you're starting from scratch, a good way to approach this is use-case -> tool choice -> architecture.

Let's consider a star schemas, beginning with use-case. The traditional, row-oriented database is designed around Online Transaction Processing (OLTP). Transactions are anything that creates, reads, updates, or deletes some data object. Usually these objects correspond with a single entity in your application, like a user or a subscription. Many applications processes data "transactions" one user at a time. For example, your customer success team may pull up customer details one user at a time and make changes to their profile or subscription. These use cases work well for a row-oriented database (e.g. SQL Server) because all the information for one record is grouped together. If you have a Subscriptions table that is indexed by user_id and a Users table that is indexed by user_id, joining on user is a simple matter of pulling that user's record from each table and popping them together. You want to limit the number of records your system has to scan through to find the users; this is where a Kimball model is useful. It reduces the number of records to the bare minimum. That saves on storage and can increase efficiency.

To summarize: transaction-based use-case -> row-oriented database -> Kimball model.

Now let's take the analytics use case. If you're primarily summarizing data into a dashboard or summary statistics, or even if you're using that data in a machine learning model, you don't care as much about the user itself. Mostly you want to just add all the numbers in a column together (or average, or multiply by some weight then sum). By organizing all the data in a column together, you can calculate these things more efficiently (and more easily in parallel). So in this case, you want a column-oriented database like Redshift or BigQuery. The main drawback of these databases is joining. Using our Subscriptions and Users tables example, you can't just pull the record for one user_id from the Users table, the same user_id record from the Subscriptions table, and pop them together. You have to find the value for that user_id in each column in each table then merge them. This is computationally expensive.

Nowadays, storage is cheap and computation is expensive. So joins are the bane of columnar databases. This is where the One Big Table approach comes in. If you don't care about storage redundancy (again, storage is cheap) then you can replicate the all the fields (or just the meaningful ones) from the Users table right into the Subscriptions table. Then there's no need to join anything. This can vastly improve performance of the database.

These days, the problem with storing the same piece of information in multiple tables isn't cost or storage space -- it's managing the complexity and consistency. If something was wrong with a piece of data and you need to change it, you have to update it in all the related tables. There are tools that help greatly with this (e.g. dbt has a lot of useful features). But it's still a concern.

So to summarize: analytics-based use-case -> columnar database -> One Big Table.

Answering your questions.

Also with storage being so cheap these days, and most modern DWs (Redshift, BiqQuery, etc) using a columnar based architecture, I would think we could safely throw out concerns regarding the extra redundancy (of having the same data appear twice in two tables), but is it possible that when architecting for huge applications that redundancy becomes an issue?

You're mostly right here. With cheap, scalable cloud storage we don't worry as much about having too much data. Instead we want to minimize computation costs and latency. But the main drawbacks to redundancy is the complexity it adds, especially if you have to frequently update data.

What is the correct way of designing a data warehouse for each use case?

I think what I wrote above answers this question in an ideal case. In practice it's always more complicated. Data engineers may insist on an ELT approach, which doesn't work as well with a Kimball architecture. Or it's not feasible to have different databases and architecture for your customer success and analytics teams, so they have to share whichever architecture you choose.

But hopefully this helps you understand the benefits and drawbacks of each approach.

Cheers!

1

I do not know what is considered best practice, but to me it does not look like one big table makes the analysts job easier at all.

In my previous job as an analyst we worked closely with dwh developers and used a kimball model. The joining of tables were automatic in our reporting tool /data app tool. 80% of the time we could use existing tables, requesting new feature was very easy, the model was flexible and transparent.

In my current job, the dimensional model is not exposed to the analysts. 80% of the time the one big table you actually need does not exist. As a result, the analysts create their own data sets from source tables with some random script in a random folder or a stored procedure, and store the data set on the same server as our dwh, and there are no routines for testing, documentation, data lineage or governance. Of course the server is not scaled for this, and the query based reporting tool gets very slow and we have thousands of versions of the truth. It is a nightmare.

1

It's not unheard of for a Star Schema to begin life as One Big Table. The advantage of One Big Table is you can get data to Analysts faster, without the development lag of designing and implementing a Dimensional Model. Later on, as your platform evolves, you may iterate towards a proper Star Schema.

As you've noted, you can evaluate a Star Schema in terms of architecture and implementation, e.g., normalization, storage costs, columnar dbs etc. However, Kimball's ideas are wider than implementation and encompass aspects such as requirements gathering, logical and physical design, governance, and end user experience. Dimensional Modelling is a methodology above all.

If done properly, Star Schemas are very intuitive for Analysts. When the Dimensional Model reflects the processes, language, and mental models of the organization Analysts will spend less time data-wrangling and their code will be cleaner and better understood. In my opinion, the wider benefits of Kimball's approach outweigh any performance drawbacks.

Gitlab are a leading example of a Kimball-style Data Warehouse built with newer ELT/Cloud-DB approaches (think dbt). Note that they create OBT from existing fact and dimension tables, called Mart tables:

MART TABLES: mart_<subject> = Join dimension and fact tables together with minimal filters and aggregations. Because they have minimal filters and aggregations, mart tables can be used for a wide variety of reporting purposes.

https://about.gitlab.com/handbook/business-technology/data-team/platform/edw/#naming-standards

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.