topic Re: system.access.table_lineage table missing data in Data Engineering

system.access.table_lineage table missing data

aranjan99 — Fri, 21 Jun 2024 17:16:44 GMT

I am using the system.access.table_lineage table to figure out the tables accessed by sql queries and the corresponding SQL queries. However I am noticing this table missing data or values very often.

For eg for sql queries executed by our DBT jobs, the table system.access.table_lineage has an entry but the entity run id (which should be the query id in this case) is NULL even though the queries history API and the UI have the corresponding queries. Why is the entity run id not populated in such case?

I am also noticing this table missing entries for some reads entirely. Our DBT jobs read from a few tables every hour once, but system.access.table_lineage table often only has 20-22 entries for those tables as opposed to 24 even though the queries history API and the UI have all the corresponding 24 queries.

This looks like a bug to me, can someone help on why would this be the case?

Re: system.access.table_lineage table missing data

aranjan99 — Mon, 24 Jun 2024 23:44:23 GMT

@Retired_mod I have access to the system.access.table_lineage table. I can see some data in there. My questions is specifically asking about in correct data and missing data in this table.

Re: system.access.table_lineage table missing data

jacovangelder — Tue, 25 Jun 2024 07:22:06 GMT

Is all your ETL querying/referencing the full table name (i.e. catalog.schema.table)? If you query delta files for example, metadata for data lineage will not be captured.

Re: system.access.table_lineage table missing data

aranjan99 — Thu, 27 Jun 2024 22:04:10 GMT

Yes it is referencing full table name and these are all SQL tables and not query delta files.
If I run the exact same query via a Databricks jobs, the entity run ids are populated. But if I run them via DBT jobs the entity run ids are always NULL

Re: system.access.table_lineage table missing data

goldenmountain — Tue, 03 Dec 2024 15:03:57 GMT

@aranjan99 did you ever get an answer or conclusion to the limitations of Unity Catalog in regards to tracking access via SQL?