Hi, 

Streaming Tables & Materialized Views supported in SDP are a form of Managed Tables. More details here

create_streaming_table() has a path parameter but specifying it creates a managed table with a custom storage location (not an external table). It uses the managed storage location for the schema containing the table if its not set in the code.

SDP is designed exclusively for managed table output. You need a post-processing step to get external tables or any of below.

• Downstream Job with CREATE EXTERNAL TABLE - You can create an external table on top of the gold table at the designated external location
• Schema Level Managed Storage - You can use pre configured schema level paths if the goal is predictable storage location. Its still managed tables you can set at catalog/schema level but stored at designated location
• SDP to Staging with External Final Write - You can use SDP to create managed staging tables and use a separate job with create external tables to read from staging with appropriate changes

Gold SCD 2 Dimension

You can stay with managed tables unless you have specific requirements as its the best option. It gives automatic optimization, faster queries & uc managed maintenance.
You can use external tables only if you have legacy systems requiring direct file access from cloud storage paths or data shared with non-UC systems. You can use Downstream Job approach if external tables are required.