This short article aims at giving insights in this regard, without going into implementation details.
This is knowledge gathered from my recent experience with using it on my project Waves of the Atlantide.
1/ What is it ?
The workshop is at its core a cloud file management system.
It can serve any purpose that involves sharing files not in the base game to or between your players. Typically it is used to allow modding of a game.
Each workshop item can have several tags that you define in the steamworks backend to indicate what kind of content it contains.
A few common examples of workshop items are maps, weapons, skins...
For example, in my game Waves of the Atlantide, the workshop is used to localize the game. At any time now a player can chose to provide the community with his version of the game texts in his native tongue.
A significant bonus perk, is that I can edit the translation I did myself (French) and make it public without having to publish an entire new build of the game !
2/ How it works
There are 3 main steps to using the workshop:
Creators publish content
Players subscribe to content
The game makes the content available
1. Creator publish content
Content creators can post items which contains any sort of file required by the game.
The process of publishing workshop items isn't availabe in the Steam client, so you must provide players with a way to do so using the workshop API.
It is often done directly in the Unity editor for Unity games, this way content can be edited and published from the same place. In this case using Unity's Asset Bundles could be a great way to package things.
It is also a common practice to provide modders with a workshop publishing tool, and for that matter Steam allows the creation of supporting tools apps for free. Another possibility is to make the publishing tool available in the game itself.
You usually have to provide a guide/documentation explaining how to publish content and describe what is expected file-wise.
For example, I decided in my game for workshop items tagged as "Translation" that it needed a folder containing a json and png file with the same name:
2. Players subscribe to content
Players can subscribe to workshop items.
Steam has a fully fledged workshop interface that you can easily give your players a link to in your game.
The workshop API is thorough and allows full workshop control by the game itself, so you can have in game, good looking menus to subscribe/unsubscribe to workshop items.
Once subscribed Steam downloads those files to make it available for the game to use.
In the case of multiplayer games, the API can also be used to automatically download content needed by a specific lobby without requiring all players to subscribe to it.
For example, if a lobby is playing in a modded map, you can have joining players download the map from the workshop if they have not subscribed to it.
3. The game makes the content available
Steam handles downloading files to a local folder on the computer, and the API lets you find out what the player subscribed to and where Steam downloaded it.
This is where the workshop part of the job ends.
At that point it is up to you to read the content of each workshop item folder to fetch its content and integrate it with your game.
For example, in my game I fire a "new localization directory event" with the path for each workshop items tagged as "Translation". The localization system picks up the event and reads the folder, if it finds the proper file architecture it loads it up and without errors coming in the way, a new language is listed as available.
That's all, I hope this overview clarifies what you can do with this amazing tool. Feel free to share your workshop stories and feedback in comments !