(updated 8/15/2019)

I am sometimes asked to compare Azure SQL Database (SQL DB) to Azure SQL Data Warehouse (SQL DW).  The most important thing to remember is SQL DB is for OLTP (i.e. applications with individual updates, inserts, and deletes) and SQL DW is not as it’s strictly for OLAP (i.e. data warehouses).  So if your going to build a OLTP solution, you would choose SQL DB.  However, both products can be used for building a data warehouse (OLAP).  With that in mind, here is a list of the differences:

• SQL DB has a limit of a 8TB database size (General Purpose tier) or 4TB (Business Critical tier).  There is also a 100TB max database size for Hyperscale.  If you have a database that will exceed that, you will have to use sharding (via Elastic Database Tools) or use cross-database queries (see Scaling Azure SQL Database) with row-level security (see Multi-tenant applications with elastic database tools and row-level security).  SQL DW has a max 240TB database limit for rowstore (1PB with 5x compression) and unlimited storage for columnstore tables, making it easier to use for larger databases
• SQL DW is a MPP solution and queries can be many times faster (20x-100x) than SQL DB, a SMP solution.  See What is Azure SQL Data Warehouse?
• You can pause SQL DW to save costs.  SQL DB does not have the ability to pause it
• SQL DW has a slider bar to increase or decrease performance (twelve options).  SQL DB has a set number of service tiers (fourteen for Gen5 vCore) to choose, and those options also include features (i.e. max database size, max concurrent workers, etc).  So both are easy to scale and don’t require and down time, but SQL DW is a bit easier to scale and a bit more flexible and also allows you to scale compute independently from storage
• SQL DB can support up to 8,000 concurrent queries/requests and 30k active connections/sessions (vCore), where SQL DW can only support up to 128 concurrent queries and 1,200 active connections/concurrent slots.  So SQL DB is a much better solution if you are using something like a dashboard with thousands of users
• SQL DB supports active geo-replication.  SQL DW does not, only geo-restore
• SQL DB support in-memory OLTP.  SQL DW does not
• SQL DB supports elastic queries.  SQL DW does not
• SQL DW supports Polybase.  SQL DB does not
• SQL DB supports always encrypted, SQL DW does not
• SQL DB supports replication (must be a push subscriber).  SQL DW does not
• Migrating schema from an on-prem SMP solution to SQL DW has some challenges (foreign keys, primary keys).  See Design decisions and coding techniques for SQL Data Warehouse
• SQL DW does not support all T-SQL: Unsupported table features, Workarounds for unsupported data types, T-SQL statements supported in Azure SQL Data Warehouse
• SQL DW has distributed tables: Guidance for designing distributed tables in Azure SQL Data Warehouse

I have other blogs that cover SQL DB and SQL DW.

More info:

Comparing Azure SQL Database to SQL Data Warehouse

IS AZURE SQL DATA WAREHOUSE A GOOD FIT?