If we want to answer this question in one sentence:
"A Substance file is a smart texture(s) generator, that can be used directly in many external applications."
We use the word 'smart' because, rather that being static - like a classic texture - the author of the Substance material can decide to expose some parameters in order to give some control to the final user.
For example, it's possible to change the amount of dust on an object, or the color of a soccer team jersey. Your talent and your imagination are the only limits to what you can do.
An example of a brick wall material by Kay Vriend - you can modify parameters to dynamically control the material's appearance.
Another key point is that the Substance material can potentially generate textures, taking into account the geometry of the targeted 3D asset. And if you change the 3D asset, the material will adapt the generated textures.
This is extremely useful in the context of a production, as you carry out the work just once, and then reuse it as much as necessary.
A Substance material can be published, then used in any external application that handles the Substance Engine (such as Unity or UE4). Not only will you get the generated textures, but all the exposed parameters remain available, and modifiable.
Finally, if you create a Substance material that is 100% procedural, the file will be extremely light (just a few kilobytes). This means that you can dramatically reduce the amount of space dedicated to textures in your project.