Redshift Interaction between tables

redshift_lineage.sql

DROP TABLE IF EXISTS target_tables;
CREATE TEMP TABLE target_tables AS (
	SELECT
		DISTINCT tbl AS target_table_id,
		sti.schema AS target_schema,
		sti.table AS target_table,
		sti.database AS cluster,
		query
	FROM stl_insert
	JOIN SVV_TABLE_INFO sti ON sti.table_id = tbl
);


DROP TABLE IF EXISTS source_tables;
CREATE TEMP TABLE source_tables AS (
	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'
);

-- https://docs.aws.amazon.com/redshift/latest/dg/r_STL_SCAN.html
SELECT
    DISTINCT cluster, target_schema, target_table, username, source_schema, source_table
FROM target_tables
JOIN source_tables USING (query)
WHERE scan_type IN (1,2,3)
AND target_schema IN ('{{ params.schemas | join("','") }}')
;