In the case of temp table, the redshift Lineage cannot be obtained accurately.

[yingyingqiqi]

Describe the bug
In the case of temp table, the redshift Lineage cannot be obtained accurately.

To Reproduce

DROP TABLE IF EXISTS tmp_table;
create temp table tmp_table as
select
a,
b
from tableAA

insert into tableBB select * from tmp_table

tableBB Lineage，not exist tmp_table、tableAA

Expected behavior
tableBB Lineage，exist tmp_table、tableAA

[yingyingqiqi]

datahub/metadata-ingestion/src/datahub/ingestion/source/sql/redshift.py

Lines 798 to 853 in 075d19e

	stl_scan_based_lineage_query: str = """
	select
	distinct cluster,
	target_schema,
	target_table,
	username,
	source_schema,
	source_table
	from
	(
	select
	distinct tbl as target_table_id,
	sti.schema as target_schema,
	sti.table as target_table,
	sti.database as cluster,
	query,
	starttime
	from
	stl_insert
	join SVV_TABLE_INFO sti on
	sti.table_id = tbl
	where starttime >= '{start_time}'
	and starttime < '{end_time}'
	and cluster = '{db_name}'
	) as target_tables
	join ( (
	select
	pu.usename::varchar(40) as username,
	ss.tbl as source_table_id,
	sti.schema as source_schema,
	sti.table as source_table,
	scan_type,
	sq.query as query
	from
	(
	select
	distinct userid,
	query,
	tbl,
	type as scan_type
	from
	stl_scan
	) ss
	join SVV_TABLE_INFO sti on
	sti.table_id = ss.tbl
	left join pg_user pu on
	pu.usesysid = ss.userid
	left join stl_query sq on
	ss.query = sq.query
	where
	pu.usename <> 'rdsdb')
	) as source_tables
	using (query)
	where
	scan_type in (1, 2, 3)
	order by cluster, target_schema, target_table, starttime asc

The possible reason is that SVV_TABLE_INFO Do not permanently save temporary table information，Only including temporary tables created by a user for the current session. I can't find all the places where the temporary tables are redshift stored, and I may need to parse the consanguinity through ddl.

[treff7es]

Currently, we drop tables from a lineage that does not exist anymore.
Possible solution can be to resolve those connections where A -> TempB -> C to A -> C if TempB does not exists anymore.

[hsheth2]

This was fixed by #9704