--- title: "ClickHouse® tips #7: Forward and backfilling null values" excerpt: "Making use of array functions to do it. Part 7." authors: "Alejandra Rodriguez" categories: "The Data Base" createdOn: "2023-09-25 00:00:00" publishedOn: "2021-05-18 00:00:00" updatedOn: "2022-09-08 00:00:00" status: "published" ---

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.