Often, the absence of data is as important as the available data. This presents a dilemma: How can you address missing data effectively? Introducing the concept of “grain,” a fundamental principle that can help guarantee the accuracy of your data analyses. In this post, we will explore the definition of grain, its importance, and a practical example to incorporate it into your dbt model.
What’s a “grain”?
In its essence, the grain is the collection of all timeline records that should exist. The grain sets the granularity of the analysis, which refers to the level of detail (or precision) of the data. The more granular the data, the more information is available for analysis, but this comes at the cost of increased storage, memory, and required computing resources. In short, more granular is more expensive.
Some examples:
1) if you want statistics about the requests per minute for all your APIs, your grain needs a record per API, per minute, for every minute the corresponding API was in production.
2) if you want to measure the daily activity of your customers on a platform, your grain needs a record per customer, per day, for every day that customer was a customer (see the short example below).
3) if you want to analyse the quarterly revenue of companies, your grain needs a record per year and quarter, per company, for every quarter the corresponding company was into business.
Example of a grain of 2 customers, for 2 days
Why do I need a grain?
Let’s look at a real world application I recently came across. A company was interested in analysing the average quarterly revenues per client. The revenues are determined based on invoices sent, which all have been stored in the data warehouse together, with records of the clients.
The first thing you might do, is running a simple query against the database (note that this is Redshift SQL):
with invoiced_revenue as (
select
client_id,
extract(year from invoice_date) as year,
extract(quarter from invoice_date) as quarter,
sum(total_amount) as revenue
from {{ ref("stg_ref__dates") }}
where audit_isactive = 1
group by 1,2,3
)
select
year,
quarter,
avg(revenue) as average_revenue
from invoiced_revenue
group by 1,2
order by 1,2;
This will yield the results you’re looking for, right? Not always.
This query only works if every client is invoiced at least once a quarter. When they’re not, the missing data is not accounted for. This has impact on your statistics, since the average of [12,24,24] is different than the average of [12,0,24,24]. This means that the averages resulting from the query will probably be too high.
This is a classic example of where a grain is required. By creating a default record per client per quarter, you can correct these averages, and account for missing data.
How to create a grain
To create a grain, you need to know a few things first:
- what is the granularity you need?
- what is the definition of the start of a timeline?
- what is the definition of the end of a timeline?
If we take the user activity as example: when do we consider a user to be an active user? When does this stop being true? When you can answer this question, you are able to create the table below.
The base of your grain table
You can then create the grain through:
grain as (
select
client_id,
dates.date,
row_number() over (
partition by client_id
order by dates.date
) as nr_days_active
from {{ ref("clients") }}
inner join {{ ref("stg_ref__dates") }} dates
on dates.date between clients.client_from and clients.client_to
where dates.date < getdate()::date
)
Note that the stg_ref__datesmodel just contains a list of dates and their properties, ideally you’d use a date spine if that’s supported for your database (looking at you, Redshift 👀).
This creates a table that looks like this:
The resulting grain
So, how do I implement this?
Implementing a grain in a dbt model is simple! You can use a grain CTE like we created in the previous step as your “base” and join on it to get an accurate dataset.
Visualisation of how a grain helps identifying gaps in your timelines
Let’s use a grain to fix the example query we started this post with. This was the original query:
with invoiced_revenue as (
select
client_id,
extract(year from invoice_date) as year,
extract(quarter from invoice_date) as quarter,
sum(total_amount) as revenue
from {{ ref("stg__invoices") }}
where audit_isactive = 1
group by 1,2,3
)
select
year,
quarter,
avg(revenue) as average_revenue
from invoiced_revenue
group by 1,2
order by 1,2;
We know the averages of this query are too high because we do not account for missing data, lets implement a grain to fix this:
with grain as (
select
client_id,
extract(year from dates.date) as year,
extract(quarter from dates.date) as quarter
from {{ ref("clients") }}
inner join {{ ref("stg_ref__dates") }} dates
on dates.date between clients.client_from and clients.client_to
where dates.date < getdate()::date
group by 1,2,3
),
invoiced_revenue as (
select
client_id,
extract(year from invoice_date) as year,
extract(quarter from invoice_date) as quarter,
sum(total_amount) as revenue
from {{ ref("stg__invoices") }}
where audit_isactive = 1
group by 1,2,3
),
client_revenue as (
select
grain.client_id,
grain.year,
grain.quarter,
coalesce(invoiced_revenue.revenue, 0) as revenue
from grain
left join invoiced_revenue
on grain.client_id = invoiced_revenue.client_id
and grain.year = invoiced_revenue.year
and grain.quarter = invoiced_revenue.quarter
)
select
grain.year,
grain.quarter,
avg(client_revenue.revenue) as average_revenue
from client_revenue
group by 1,2
order by 1,2;
Take you time to analyse this model. It has four steps:
1) It constructs the grain, a record for every client, every quarter
*2)*It then constructs the invoiced_revenue, just like we did originally
*3)*Using the grain and invoiced_revenue, we construct the client_revenue that joins them together, and inserts a 0 for every quarter that a client is not invoiced.
*4)*Finally, we calculate the averages, now accounting for missing data.
As you can see, it takes a bit of work, but it makes your analyses accurate. The grain is a pattern I’ve adopted in most of my dbt models.
Another tip for working with grains: If you notice you’re re-using grains in your dbt project, it’s a good idea to create a separate model for them. This increases the performance, readability and maintainability of your code significantly.
Hi, I’m Bastiaan 👋🏼 Data Lead at a scale-up. I write about the Modern Data Workflow, where I explore tools & processes to supercharge your data capabilities. Follow me for more!