There is nothing fundamentally different in the workshop or CFC version of a mod, the content is the same, including art assets.
I think there is a confusion since civ4, when the "SDK" was just the DLL source, since then people assumed SDK = source code, but civ5 SDK was released without the DLL source first, and there is also a SDK for civ6, without the source, but with other additions over civ5, like the art assets editing tool.
There will be a SDK for civ7, with at least a component to upload mods on the workshop, pretty sure there will also be an art asset tool with it.
DLC-like mod were possible with civ6 SDK, mods were never seen as something that would hurt DLC sales. Now it's true that modding capabilities are going down since civ4, but that's a result of the game engine changes, copyright issues, and maybe even modders demands (or lack of, thinking of scripting being kind of replaced by XML/SQL modifiers), and paradoxically they've surely put (a lot) more time in the development of the civ6 SDK than for civ5 and civ4.
That said, at this point, I'm indeed worried for civ7 modding compared to civ6, but that's more because of (what I think are) technical limitations of the base game, not a desire to reduce modding capabilities from the dev.