Databricks Tip: How to Track Where Each Record Came From, File by File - input_file_name()

Wouldn’t it be a dream to have a column in your Bronze table (or Landing, whatever you prefer to call it) telling you which file each record came from?

Here’s how to do it:

Below we have a set of JSON files to load.

Let’s read the files into a dataframe.

Now, let’s save the dataframe as a Delta table.

Now here’s my question: how do we know which file each person came from?

The secret is the input_file_name() function. You can use it with either SQL or Python, and it returns the name of the file that was read. In the example below, I’m using the function in the select on my Bronze table in Delta format.

However, if we use this function after the table has been created, what we get back are the parquet files, not the original source files.

To solve this, let’s add the “nomeArquivo” (file name) column when we create the Delta table.

Now we’re talking!!

That’s it for today, folks. I hope this was useful.

See you next time.