need one small help with recursive data retrieval using snowflake.
Assume we have a table with below structure
| Job_id | Input database | Input table | Output database | Output table |
|---|---|---|---|---|
| 1 | permanent | source_1 | temporary | intermediate_1 |
| 1 | temporary | intermediate_1 | temporary | intermediate_2 |
| 1 | temporary | intermediate_2 | permanent | target_1 |
| 3 | permanent | source_1 | permanent | target_3 |
| 2 | permanent | source_2 | temporary | intermediate_1 |
| 2 | temporary | intermediate_1 | permanent | target_2 |
I want to find data to start with input database = permanent and input table = source
and find all the downstream for that source
| Input database | Input table | Output database | Output table |
|---|---|---|---|
| permanent | source_1 | permanent | target_1 |
| temporary | intermediate_1 | temporary | intermediate_2 |
| temporary | intermediate_2 | permanent | target_1 |
| permanent | source_1 | permanent | target_3 |
I am trying to use CONNECT BY to get data in above way – but since the same table name is used in different job id – its picking records for job_id 2 as well.
How can i use recursion with a conditional field – like add job_id condition also when the prior database is temporary.