🚀
Introducing Versions: Develop data products using Git. Join the waitlist

ClickHouse tips #7: Forward and backfilling null values

Making use of array functions to do it.
Alejandra Rodriguez
May 18, 2021
 ・ 
  min read

Many times you have data with null values and you’d like to fill them with non-null values. For example, imagine this is your data:

You could replace them by a constant value like this:

But in many cases you’ll want to fill them with the latest (or next) value available. This is a one-liner in libraries like Pandas with the fillna method, and on Postgres this way. On ClickHouse is also possible using array functions:

Here is a full explanation on what happens in each of the subqueries of this last query and of what each of the array functions does.

Alejandra Rodriguez
Share this post