Inconsistent Column Level Lineage

[phalcon22]

Hi!

I can't give a small example of how to reproduce the bug so I won't create an issue yet.

I have this kind of BigQuery code where I created temporary tables, each using the previous one:

CREATE OR REPLACE TEMP TABLE `tmp_1` AS
SELECT * FROM `dataset_y.table_x`
;

CREATE OR REPLACE TEMP TABLE `tmp_2` AS
SELECT * FROM `tmp_1`
WHERE...

etc...

CREATE OR REPLACE TABLE `dataset_8.table_50` AS
SELECT * FROM `tmp_6`
;

I wrote a program in Python to get the column lineage and provide metadata with the DummyMetadataProvider.

PROBLEM: About half the time, when I run the code, the lineage outputs this:

...
dataset_8.table_50.* <- <default>.tmp_6.*

Meanwhile the other half is working correctly (provides the full column lineage).

My theory based on nothing:
Maybe it is a concurrency problem where the parsing is parallelized or something and the last statement is processed before the previous one could finish so it does not have the temporary tables metadata yet ?

[reata]

By concurrency, did you mean concurrency within sqllineage, or with your code in concurrently calling sqllineage.

sqllineage itself does parsing and lineage analysis sequentially. If it's your code where concurrency happens, can you try multi-process instead of multi-thread to see if the same issue still happens? Some class in sqllineage might not be well-designed with thread-safety in mind.

[phalcon22]

There is no concurrency in my code.

I'll try to find to find the smallest example of this behaviour that is reproducible.

[phalcon22]

Here is the smallest example of how to reproduce the problem:

metadata.json:

{
  "dataset.table_A": [
    "Column_1"
  ],
  "dataset.table_B": [
    "Column_W"
  ]
}

test.sql:

CREATE OR REPLACE TEMP TABLE `tmp_table` AS
SELECT
    Column_1
FROM `dataset.table_A`
;

CREATE OR REPLACE TABLE `dataset.table_C` AS
SELECT *
FROM `tmp_table`
WHERE
    Column_1 IN (
        SELECT Column_W
        FROM `dataset.table_B`
    )
;

test.py:

import json
from sqllineage.core.metadata.dummy import DummyMetaDataProvider
from sqllineage.runner import LineageRunner

with open('metadata_test.json', 'r') as file:
    metadata = json.load(file)

with open('test.sql', 'r') as f:
    body = f.read()

provider = DummyMetaDataProvider(metadata)
lineage = LineageRunner(body, dialect="bigquery", metadata_provider=provider, verbose=True)
lineage.print_column_lineage()

Now executing the following command multiple will output something different often:
python3 test.py

Out of 12 tries I got 9 times this:

<default>.tmp_table.column_1 <- dataset.table_A.column_1
dataset.table_C.* <- <default>.tmp_table.*

and 3 times this:
dataset.table_C.column_1 <- <default>.tmp_table.column_1 <- dataset.table_A.column_1

From my testings, the problem seems heavily relying on the subquery + temp table. Delete one or the other and the result is consistent.

Edit: Interestingly enough, if I put all the Python code in a loop instead of executing the code multiple time from the console then the output is always the same at each iteration of the loop.

[reata]

Thanks for the code sharing. I can reproduce with v1.5.0.

At the same time, it seems the inconsistency goes away with latest master branch. I'll take some time to dig deep.

[rubytobi]

I noticed a similar thing and traced it back to the wildcard resolution logic. I didn't fix it properly, but made it consistent at least: #661