It's a difficult one; a question I've considered many times over in the years I've been working with Sitecore.
I can start with some numbers. I've done perhaps 30 or so projects that involved data integration of some kind. 29 of which, I ended up importing the data. I've only ever done 1 project where I've implemented a full Data Provider - and I'll highlight why:
Data is often imperfect
This is especially the case for most e-commerce solutions I've worked. This is usually product data, product meta data, category trees and so on. It's very rare that one can source all of the required information (short descriptions, long descriptions, images, product attributes and so on) from the back-end systems. So what I've ended up doing; is doing multiple integration runs - enriching the product catalogue in each run. Then when complete - publish the enriched data.
Back-end systems are too slow for practical website use
This is especially true for ERP and PIM systems. They are designed with data integrity in mind, and only secondly have an eye on performance. If even that. Add to this; the nature of data in these systems is rarely a tree-like structure (rather; relational) - making it difficult to make them fit into the parent/child nature that a Sitecore Data Provider has to follow.
Lastly; if opening up a Data Provider to a back end system - it needs to be available all the time, or the Content Editor users find themselves out of work. And publishing an empty structure... doesn't really end so well.
Sitecore's data model is richer than most back-end systems
I argued this in Working with multiple content databases in Sitecore 6.
Sitecore offers versioning, language versions, workflows, many types of fields - right out of the box. Writing a Data Provider, one often ends up stubbing most of this functionality out, thus detracting from the overall Sitecore experience. The data looks like it's a native part of the Sitecore content tree, but it's really not.
Well I honestly don't see where I would use one today. Unless writing a very specialized tool - like Kamsar's Unicorn or so. For live use on the website? We have so many other ways of abstracting data - Dependency Injection, Services, Web APIs and so on - I really don't see any added value in feeding this data in via a Data Provider, just so we can use Sitecore Query (brr) to query it. I think Data Providers made sense, when XSLT was still the de-facto standard for rendering Sitecore content.
I think those days are behind us.
The whole point of a data provider is; to bring in content and have it appear as native Sitecore content. The idea being; you then bring in your content from multiple sources, and then have it work just like had it been created natively in Sitecore. And yes; part of that was based on creating XSLT renderings, and they shouldn't need to know what content was native and what was not - and with XSLT renderings there simply is no easy option to render non-Sitecore data.
So how are these used?
Well presumably your data provider is one-way. You can implement a 2 way provider but I've never had a business case for it. Usually one integrates to a business system and we can't just let any editor simply edit content (by mistake - in Experience Editor this could easily happen) and have it written back to the origin system.
So we feed in data from the provider; and we probably feed it directly into "master" and "web" in many instances, as support for publishing of provided data is extra work. Plus - you data provide to have a real time view to the origin system and publishing would sort of ruin that idea. Now you have to decide how to deal with caches; external link to origin system breaking down (and taking your solution down in the fall)... meh the list just goes on.
So what can we do instead?
Lots. Ruling XSLT out again; say you wanted to reach data from a back end CRM database. Your Helix feature would be something like "CRM Integration" (pardon me if not up to speed on any naming conventions in Helix). And go for it; provide the project layers with ICrmIntegration if you want. And if you desperately crave a Sitecore Item tree, nothing is stopping you from mocking one up at this point. Especially not in new Sitecore releases. Something like
var customersRoot = _crmIntegration.GetCustomersRoot();
foreach(Item customer in customersRoot.GetChildren())...
Possible? Sure. But looking at it like this, it becomes obvious it just doesn't make much sense.
If there is a case for a Data Provider, it would only be a fully implemented 2-way provider. To save you having to write a web interface to update some data. Nothing else rings any kind of sense to me. Not today, not post XSLT. Not with models, Dependency Injection, Layered Architecture. We have so many better options available.