Basics to Advanced: Azure Synapse Analytics Hands-On Project

Explore how Azure Synapse Analytics overcomes traditional data analytics limitations, enabling efficient and scalable processing and signaling a shift from older analytical systems.

Understand why a separate data warehouse and analytical system are essential for analytics, letting analysts and engineers access historical data with fact and dimension tables without disrupting transactional systems.

Contrast OLTP and OLAP to show how transactional systems manage current operational data with fast reads and writes. OLAP channels historical data through ETL to a data warehouse for analysis.

Explore what a typical data warehouse contains in the Azure Synapse Analytics hands-on project, from transactional databases, CSV, and JSON sources, through ETL to a structured warehouse for BI reports.

Explore data lakes as scalable stores for unstructured, structured, and semi-structured data with ingestion, see how modern data warehouses use cloud tech, artificial intelligence to reveal trends and predict performance.

Examine how modern data warehouses ingest diverse sources into Azure Data Lake, perform transformations, and deliver refined data to SQL Data Warehouse for Power BI visualization, while noting integration complexity.

Discover how Azure Synapse Analytics offers a single workspace that unifies compute, security, and monitoring with Synapse Studio and serverless and dedicated SQL pools, Spark pools, and Data Explorer pools.

Azure Synapse Analytics unifies ingestion, storage, compute, and visualization with Power BI, replacing Azure Data Factory pipelines, ADF data flows, Databricks with Synapse pipelines, Synapse data flows, and Spark pools.

Explore how Azure Synapse Analytics combines ingest, storage, compute, visualize, monitoring, and security into a single service for data engineering projects. Learn the components and configuration in the next section.

Explore the practical aspects of Azure Synapse Analytics, including its compute and storage components, through hands-on setup, dataset exploration, and creating the required services.

Set up an Azure subscription (free trial or pay-as-you-go), then create a resource group named Synapse project to host the Azure Synapse service for the course.

Create an Azure Synapse Analytics workspace in a chosen subscription and resource group, link a data lake storage account, and set up Azure AD authentication for serverless SQL pool.

Explore Azure Synapse Analytics by navigating the workspace and Synapse Studio, examining sql pools, spark pools, and data explorer pools, with serverless and dedicated options, linked storage, and pipelines.

Explore the unemployment dataset from US states, map its 18 columns—line number, year, month, state, labour force, employed, unemployed, unemployment rate, industry, gender, education, date inserted—and outline pyspark transformations.

Explore the serverless SQL pool and use open rowset to create an external data source and external file format for initial data exploration. Transform unemployment.csv to parquet in refine folder.

Explore serverless sql pool as on demand, pay-as-you-go data warehousing that queries Azure Data Lake with Synapse, powered by a distributed query processing engine.

Discover the serverless sql pools architecture in Azure Synapse Analytics, a scale-out, compute-separated design where a control node orchestrates distributed queries across compute nodes.

Discover the serverless sql pool benefits and pricing, paying only for data processed. Use for initial data exploration without managing infrastructure, enabling external tables and views and auto scaling.

Learn to query data in Azure Data Lake with Serverless SQL Pool in Synapse Studio, uploading CSV and pipe separated files to raw container and using open row set function.

Learn how to query data from Azure data lake storage using the open row set function in Azure Synapse serverless SQL pool, including data source concepts, formats, and header handling.

Learn to import SQL scripts and PySpark notebooks into Azure Synapse from the resources tab by downloading and importing them, while using underscores to name files.

Apply collate fixes in Azure Synapse by explicitly setting varchar column collations or altering the project database, then prepare external tables and initial data cleaning in serverless sql pool.

Create an external data source in Azure Synapse Analytics to point to storage paths via a data source name, enabling openrowset queries across dev and prod environments.

Learn to create a database scoped credential using a shared access signature to access an external data source in Azure Synapse Analytics with Azure Data Lake.

Learn to use a system assigned managed identity to access an Azure data lake from Synapse, creating a link service, a database scope credential, and an external data source.

Clean up a messy Azure Synapse workspace by dropping all external data sources and containers, then refresh to reveal the refined storage container for future external tables and file formats.

Create an external file format as a prerequisite for external tables, linking data in Azure Data Lake Storage to metadata in Serverless SQL Pool, detailing CSV, Parquet, and Delta formats.

Create an external file format in Azure Synapse using parquet with snappy compression. Move data from raw to refined folders and prepare for the next lecture on external data source.

Create an external data source pointing to the refined container, replacing the raw path, and note that Excel format and the external data source are used for external table creation.

Create an external table from a select query using an open row set, defining the external data source and parquet format to move data from raw to refined storage.

Select columns from the raw csv to create a parquet file, achieving the initial transformation. Leverage columnar storage and compression to prepare data for PySpark-based transformations in this course.

Explore the tech stack and data processing in big data projects before Spark. Build a foundation by tracing Spark's evolution from roots to core concepts and Pie Spark data transformations.

Explore the evolution from monolithic systems to distributed computing, introducing Hadoop's master–slave architecture, yarn, hdfs, and mapreduce, and trace how spark fits into big data processing.

Understand how Hadoop uses Yarn as the cluster manager to coordinate master and worker nodes, with HDFS as distributed storage and MapReduce for distributed computing.

Understand how hdfs provides distributed storage for hadoop through a master node and multiple data nodes, storing files as 128 mb blocks with replication and metadata management for reliable retrieval.

MapReduce enables distributed computing with a master-slave architecture, performing map, shuffle, and reduce phases to count words across Hdfs blocks.

Explore the drawbacks of the MapReduce framework and explain how Spark evolved and emerged as the preferred tool, contrasting it with Hadoop, Hdfs, and the earlier tech stack.

Explore the drawbacks of the Hadoop MapReduce framework, including disk-based processing and high I/O from repeated reads and writes, and the Java coding complexity that ushered in Spark.

Explore the emergence of Apache Spark as an open source in-memory compute engine that reads from storage, caches data, scales out, and enables streaming and machine learning.

Explore Spark core concepts and the foundational pillars of Apache Spark, including its architecture, RDDs, actions, and transformations (narrow and wide).

Explore the Apache Spark ecosystem from Spark Core to higher level APIs like DataFrame and Dataset, and understand how RDDs enable parallel processing across worker nodes.

Explore how Spark improves over Hadoop with in-memory processing, enabling batch, streaming, and machine learning via Spark SQL and multi-language support.

Explore Spark architecture with a driver (master) node and worker compute nodes that execute in memory, initialize Spark context, connect to YARN, and use DAGs and RTDs for in-memory processing.

Create and configure a spark pool in Azure Synapse Analytics to run code with a driver and worker nodes, covering pricing, isolation, memory optimized, hardware accelerated options, and auto scale.

Explore the resilient distributed dataset concept in Spark, detailing RDD as immutable partitions distributed across a cluster, enabling fault-tolerant, parallel compute via driver and worker nodes.

Get an overview of lambda, map, and filter in programming, including anonymous functions, mapping a function to list elements, and filtering by true conditions to produce new datasets.

Explore how to use Azure Synapse Analytics to run Spark notebooks, attach a Spark pool, and work with RDD transformations like map and filter, including lazy evaluation.

Explore how RDDs use lazy evaluation for transformations like map and filter, and how actions such as collect trigger Spark jobs, revealing lineage and immutability.

Understand how Spark records an RDD lineage as a sequence of lazy transformations, with actions triggering computation and a DAG ensuring fault tolerance through immutability.

Explore how spark core performs a word count on biography.txt by reading the file, splitting lines into words with flatMap, and counting with map and reduce by key.

Learn to build a PySpark word count in an Azure Synapse notebook, reading from data lake, and compare flatMap and map with reduceByKey in the Spark workflow.

Explore the differences between reduceByKey and groupByKey, highlighting partition-level aggregation with combiner in reduceByKey and the higher shuffling in groupByKey. Learn why reduceByKey is typically more efficient for large datasets.

Explore how spark's execution plan forms jobs, stages, and tasks, and learn that the number of actions, like map, collect, filter, and take, directly drives job creation.

Compare narrow and wide transformations in Spark, with map and flatMap in a single partition and reduce by key triggering a shuffle across partitions, creating additional stages.

Explore narrow and wide transformations in Spark, how stages form into jobs and tasks, and how the Spark UI and DAG visualization reveal shuffle read/write and execution planning.

Demonstrate how Spark tasks arise from data partitions, with the maximum tasks equal to partitions, illustrated by two partitions and two tasks per stage in this RDD understanding lesson.

Explore directed acyclic graphs, dag components, and lineage in Spark, learn how actions trigger dag scheduling, fault tolerance, and recomputation of lost partitions.

Learn how higher level APIs like DataFrame and SQL leverage Spark's Catalyst optimizer to optimize queries, replacing lower level RTDs and ADT with schema inference and efficient execution.

Learn to read data frames with the dataframe reader in Azure Synapse notebook, loading csv, json, and parquet files from Azure Data Lake and inspecting schemas.

Apply pyspark transformations in Python to clean data, moving parquet from the refine container to a cleaned dataset in the processed container for reporting in Azure Synapse.

Learn to create and attach a spark pool in Azure Synapse Analytics, then use notebooks with code and markdown cells, headings, lists, images, and comments for collaboration.

Learn to save costs while practicing pyspark notebooks by using Databricks Community Edition and Azure Synapse spark pools, with setup, notebooks, and data import.

Learn view transformations through selection and filtering, the display and show functions, and select expression extensions, while reading parquet data into a dataframe and visualizing charts in a synapse notebook.

Stop the spark session when not in use to avoid charges, since a ready spark pool incurs costs while executing commands; auto stop after 15 minutes idle.

Use select and selectExpr to query and transform a dataframe, alias columns, apply column functions, and perform sql-like expressions and aggregations.

Apply the PySpark filter function to query dataframes, using single and multiple conditions, string operations, and SQL-like expressions to filter, count, and select columns.

Organize large numbers of notebooks by using folders, create subfolders, and drag and drop notebooks, then publish changes to reflect updates for multi-team collaboration.

Learn to handle nulls and missing values in PySpark dataframes by counting rows, filtering nulls with isnull, and replacing them with unknown using fill/na.fill to improve data trustworthiness.

Identify and count nulls across all columns, iterate over column names, and leverage aggregations and group by to detect and handle duplicate rows in your dataset.

Learn to clean duplicates and null rows in Azure Synapse Analytics using drop duplicates and dropna, then fillna and write parquet for a clean dataset.

Organize notebooks into a new folder named handling nulls, drag both notebooks into it, and publish all, preparing for the next lecture.

Identify null values in the data frame, replace them with unknown using fill, and drop nulls or duplicates; use group by and aggregate to identify duplicates.

Learn how to transform data with PySpark's withColumn to create unemployment rate percentage, update education level with init cap, and standardize values using when/otherwise, while reading parquet files with wildcard.

Rename and transform columns in Azure Synapse by using withColumnRenamed, replace values with proper quoting, drop unnecessary columns, and clean column names to produce a parquet file.