Hi @Louis_Frolio  ,
thank you for all the thorough details you provided, but reality in our org existing setup (UC,WHs/Computes, etc.) is not so bright as you are describing 😕

I've prepared some PoC/test case to check your provided approaches:
- (never cached before) code units:
-- [PROCEDURE]+[JOB]+[TABLE] for ServerLess run
--- [TASK]+[QUERY] invoke the above
--- [TASK]+[FILE] invoke the above
- (never cached before) code units:
-- [PROCEDURE]+[JOB]+[TABLE] for WH-SQL-Pro run
--- [TASK]+[QUERY] invoke the above 
--- [TASK]+[FILE] invoke the above

After test runs of Jobs, we have:
- both [TASK]+[FILE] invocations went OK, but the traces been left in "programmatically accessible logs" - are the same, as were for [TASK]+[NOTEBOOK] in my initial question:
-- only initial [CALL...] statement invocations are recorded, no Subqueries/Sub-statements, regardless that I used all 3 suggested approaches for trying chaining/joining the parent "visible-QueryHistory-record" with "invisibly-expected-Subqueries-records" - via [statement_id], via [session_id], via [query_source.job_info.*], please see the screen:

- both [TASK]+[QUERY] failed, so, I've created a post:
-- https://community.databricks.com/t5/data-engineering/code-unit-1-line-1-statement-stored-as-query-co...

- subqueries/sub-statements got actually executed, both of [TABLE] units were filled up, as seen on the screen:

-- JFYI (off-topic observation) - if a part of code from the underlying [CALL...] is been run manually from NOTEBOOK on SERVERLESS, in comparison to the above run from TASK[SQL\File] (ServerLess/WH-SQL-Pro) - then *_version() struct components (.dbr_* & .dbsql_*) will produce different results:

So, @Louis_Frolio  if you could be so kind, to try such a POC/test code units setup on your ADBricks environment, to show the resulting screens of how it "works today (supported and durable)" - please do, I would appreciate your help, to understand what are differences in our org environment preventing to use your suggested approaches...
providing below a code template for fast "deployment":

/*
use catalog user_org_com_sandbox;use schema migration_test;
create or replace table srvless_nocache_table (time timestamp, col string, ver string);
create or replace table wrhouse_nocache_table (time timestamp, col string, ver string);
*/
/*
create or replace procedure user_org_com_sandbox.migration_test.srvless_nocache_procedure() LANGUAGE SQL SQL SECURITY INVOKER as begin declare _srvless_nocache_value string='srvless_nocache_record'; use catalog user_org_com_sandbox;use schema migration_test; insert into srvless_nocache_table values (current_timestamp(),_srvless_nocache_value,array_join(array(current_version().dbr_version,current_version().dbsql_version,current_version().u_build_hash,current_version().r_build_hash),'|','')); end;
*/
/*
create or replace procedure user_org_com_sandbox.migration_test.wrhouse_nocache_procedure() LANGUAGE SQL SQL SECURITY INVOKER as begin declare _wrhouse_nocache_value string='wrhouse_nocache_record'; use catalog user_org_com_sandbox;use schema migration_test; insert into wrhouse_nocache_table values (current_timestamp(),_wrhouse_nocache_value,array_join(array(current_version().dbr_version,current_version().dbsql_version,current_version().u_build_hash,current_version().r_build_hash),'|','')); end;
*/
/*
/Workspace/Users/user@org.com/Query-SrvLess-Log-Subquery-Test.dbquery.ipynb
CALL user_org_com_sandbox.migration_test.srvless_nocache_procedure();
/Workspace/Users/user@org.com/Query-WrHouse-Log-Subquery-Test.dbquery.ipynb
CALL user_org_com_sandbox.migration_test.wrhouse_nocache_procedure();
*/

/*
/Workspace/Users/user@org.com/File-SrvLess-Log-Subquery-Test.sql
CALL user_org_com_sandbox.migration_test.srvless_nocache_procedure();
/Workspace/Users/user@org.com/File-WrHouse-Log-Subquery-Test.sql
CALL user_org_com_sandbox.migration_test.wrhouse_nocache_procedure();
*/

/*Job_SrvLess_Log_Subquery_Test = {
name:"Job-SrvLess-Log-Subquery-Test",
tasks:[
  {task_key:"Task-SrvLess-Log-Subquery-Test",
  sql_task:{query:{
      query_id:"GuidGuid-Guid-Guid-Guid-GuidGuidGuid"} --[Query-SrvLess-Log-Subquery-Test.dbquery.ipynb]
    warehouse_id:"GuidGuidGuidGuid"}},  --[wh serverless 2xs]
  {task_key:Task-File-SrvLess-Log-Subquery-Test",
  sql_task:{file:{
      path:"/Workspace/Users/user@org.com/File-SrvLess-Log-Subquery-Test.sql",
      source:"WORKSPACE"},
    warehouse_id:"GuidGuidGuidGuid"}}  --[wh serverless 2xs]
]}*/
/*Job-WrHouse-Log-Subquery-Test = {
name:"Job-WrHouse-Log-Subquery-Test",
tasks:[
  {task_key:"Task-WrHouse-Log-Subquery-Test",
  sql_task:{query:{
      query_id:"GuidGuid-Guid-Guid-Guid-GuidGuidGuid"} --[Query-WrHouse-Log-Subquery-Test.dbquery.ipynb]
    warehouse_id:"GuidGuidGuidGuid"}},  --[wh 2xs pro test]
  {task_key:Task-File-WrHouse-Log-Subquery-Test",
  sql_task:{file:{
      path:"/Workspace/Users/user@org.com/File-WrHouse-Log-Subquery-Test.sql",
      source:"WORKSPACE"},
    warehouse_id:"GuidGuidGuidGuid"}}  --[wh 2xs pro test]
]}*/