Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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

Become a better data developer

Subscribe to the tinytales newsletter for monthly tips on building better data products.