After lots of random trial and error, I found a solution to this problem.

TLDR: The SQL user we were using in the ADF linked service didn't have the cdc_admin user role. When we enabled it, the queries began working as expected.

More details: My best guess why this happened -- the auto-generated cdc.fn_cdc_get_net_changes_my_custom_table function code contains calls to other auto-generated CDC functions and stored procedures, including ones in master database schema. The SQL user had permissions to call the main function, but not the sub-functions in the master schema. And cdc.fn_cdc_get_net_changes_my_custom_table gave a bad response instead of failing.

What I still don't understand: I don't get why the behavior is different when the query is sent from ADF and SSMS. My SQL user can call the table function fine in SSMS. This issue only happens from ADF.