International Journal of Science, Engineering and TechnologyAuthors: Amey Shinde, Ashmi Anavkar, Dr. Jasbir Kaur, Ifrah Kampoo
Abstract: Modern data engineering solutions increasingly rely on cloud-based analytical platforms to support both transactional reporting and large-scale analytical workloads. Microsoft Azure offers two prominent services for this purpose, Azure Synapse Analytics and Azure SQL Database, each suited to different cost and performance profiles. This paper presents a comparative evaluation of these two platforms within the context of a representative data engineering pipeline that ingests data through Azure Data Factory, stages it in Azure Data Lake Storage Gen2 using the Parquet format, and applies incremental loading strategies before serving curated datasets to a reporting layer. The study evaluates query performance, data ingestion speed, ETL/ELT execution efficiency, scalability behaviour, resource utilization, compute and storage cost, maintenance overhead, and suitability for data warehousing versus real-time analytics. Cost–performance optimization techniques, including dedicated and serverless SQL pools, the DTU and vCore pricing models, auto-pause configuration, partitioning, indexing, materialized views, workload management, data compression, Parquet file optimization, and PolyBase-based loading, are examined and discussed. Results, derived from representative benchmark scenarios, indicate that Azure Synapse Analytics provides superior throughput and scalability for large analytical workloads, whereas Azure SQL Database offers a more economical and operationally simpler option for moderate-scale transactional and reporting workloads. The paper concludes with practical recommendations for data engineers seeking to balance cost and performance when designing Azure-based analytical pipelines.
DOI: http://doi.org/10.5281/zenodo.20829245
