Reasons to use Azure Data Lake Analytics vs Traditional ETL approach

For me, if the data is highly structured and relational, the right place for it is a relational database. In Azure you have several choices:

1. SQL Server on a VM (IaaS)Ordinary SQL Server running on a VM, you have to install, configure and manage it yourself but you get the full flexibility of the product.
2. Azure SQL DatabasePaaS database option targetted at smaller volumes but now up to 4TB. All of the features of normal SQL Server with potentially lower TCO and the option to scale up or down using tiers.
3. Azure SQL Data Warehouse (ADW)MPP product suitable for large warehouses. For me, the entry criteria is warehouses at least 1TB in size, and probably more like 10TB. It's really not worth having a MPP for small volumes.

For all database options you can use clustered columnstore indexes, (the default in ADW), which can give massive compression, between 5x and 10x.

400MB per day for a year totals ~143GB, which honestly is not that much in modern data warehouse terms, which are normally measured in terabytes (TB).

Where Azure Data Lake Analytics (ADLA) comes in, is doing things you cannot do in ordinary SQL, like:

• combine the power of C# with SQL for powerful queries - example here
• dealing with unstructured files like images, xml or JSON - example here
• using RegEx
• scale out R processing - example here

ADLA also offers federated queries, the ability to "query data where it lives", ie bring together structured data from your database and unstructured data from your lake.

Your decision seems more to do with whether or not you should be using the cloud. If you need the elastic and scalable features of cloud then Azure Data Factory is the tool for moving data from place to place in the cloud.

HTH

Be careful. This question is likely to get closed for being too broad.

There are many arguments for and against. We can't discuss them all here.

ADL isn't a replacement for SSIS. The consultants answer as always will be.. it depends what your doing/trying to do.

A simplistic answer might be. ADL is unlimited and highly scalable. SSIS is not. But, yes, ADL has a high entry point for small files because of that scalability.

Generally I don't think the two technologies are comparable.

If you want SSIS in Azure. Wait for MS to release it as a PaaS. Or use a virtual machine.

I think for simpler transformations it may be a good solution, however if you have complexities, notifications etc. it may be incompatible. A typical scenario would be something like transforming a JSON document to CSV, then taking the CSV and running that through SSIS for further transforms. There is certainly a future state that will enable U-SQL to to be much more powerful, for now I think there are separate and distinct uses for U-SQL/ADLA/ADLS and SSIS.