Azure Data Engineer Masterclass - The Complete Guide

Master SQL from basics to advanced through practical exercises, covering databases, tables, indexing, stored procedures, complex queries, and joins; install SQL Server Management Studio and practice.

Enforce data integrity with not null and unique constraints by creating an employee table with email and phone, where email cannot be null and phone numbers are unique.

Explore aggregate functions in SQL, including max, min, sum, average, mean, and count, using an employee table to compute salaries and counts.

Learn how to group rows by a column with the group by clause, count employees per location and department, and compute max, min, and sum salaries within each group.

Learn how the having clause filters aggregates, unlike where, and apply sum or average with group by to filter addresses where total or average salaries exceed thresholds.

Compare union and union all to combine multiple tables on a row basis, noting that union removes duplicates and requires identical schemas.

Use the alter command in SQL to change a table’s structure by adding a new salary column, altering a column’s type, or dropping a column.

Explore SQL views as virtual tables created by a query that do not store data, enabling reuse of complex queries, simplified access, and data consistency.

Practice SQL interview questions for data engineers: remove duplicates with distinct, identify duplicates using row_number over partition by, and fetch top three highest and lowest earners by location.

master common sql interview topics, including union vs union all, primary key vs unique key, rank vs dense rank, delete, drop, and truncate, where vs having, and handling null values.

Learn to configure a delete activity in Azure Data Factory to remove source files from the input blob storage after copies to the output container, including linked services and logging.

Learn to set up a SQL database in Azure, create a server and resource group, configure authentication, and use the query editor with sample data.

Explore how triggers in Azure Data Factory automate pipeline execution based on schedules or events, including schedule, tumbling window, event, and manual triggers.

Learn to configure schedule triggers in Azure Data Factory to automate pipelines. Set start time, time zone, and frequency to run pipelines automatically on a schedule.

Learn how data flow transformations in Azure Data Factory enable etl, using filter and sort to extract from Azure SQL and load into Azure Blob Storage.

Use a join transformation in Azure Data Factory to merge employee and department data on employee id, and store the result in the employee department container.

Explore the select transformation in Azure Data Factory to rename, drop, and reorder columns, demonstrated with a csv file in blob storage and a practical data flow.

Explore pivot transformation in Azure Data Factory data flows by turning unique country values into columns, grouping by department, and counting employees to produce a department-country matrix.

Use the get metadata activity in Azure Data Factory to retrieve file and folder metadata, including item name, last modified, and existence, to selectively access department data in blob storage.

Create and set variables in Azure Data Factory using the set variable activity, supporting string, boolean, array, and integer types, and build dynamic file names via concatenation.

Learn to create and call a stored procedure in Azure SQL database via Azure Data Factory, using the stored procedure activity with parameters.

Learn how the switch activity in Azure Data Factory routes a data pipeline using a parameter to copy files into employee or department folders, driven by expressions and multiple cases.

discover how to use the validation activity in Azure Data Factory to validate data set references, pass runtime file name parameters, and conditionally copy data or email on failure.

Learn to apply the filter activity in Azure Data Factory to select files from blob storage. Use get metadata to read file names, filter by department.csv, and copy data.

Explore using the fail activity in Azure Data Factory to throw errors based on conditions, with a switch-based pipeline that copies employee files and alerts on unknown data.

Explore how to use the conditional split transformation in Azure Data Factory to route rows by location, aligning India, US, and UK data into separate containers via a case-statement-like condition.

Explore pivot transformation in Azure Data Factory data flow by turning unique country values into columns and counting employees per department, using group by and aggregate functions.

Explore windows transformation in azure data factory, using over partition by and order by to compute rank, dense rank, lead, lag, and cumulative sums for location-based salary analysis.

Explore the alter row transformation in Azure Data Factory, using insert, update, delete, and upsert on row-level data within a data flow, with practical country and name examples.

Apply rank transformation in Azure Data Factory to generate descending salary rankings, using rank or dense rank, by configuring a sort condition and creating a new rank column.

Troubleshoot the data flow schema import failed error in azure data factory by configuring the source data set and link service, then import projection and preview data.

Create and manage volumes in Databricks, distinguishing between manage and external volumes, upload and organize files, and apply Unity Catalog governance for access control.

Explore the PySpark select transformation to efficiently narrow a dataframe to a subset of columns, rename fields with alias, and display results using df.select and show.

Learn how to use withColumn and withColumnRenamed in PySpark to add or rename columns in a dataframe, including creating derived columns, a default value, and updating existing columns.

Learn how to sort data in PySpark using sort and order by, with ascending and descending options on columns like customer name and order id, including handling null values.

Learn how to handle null values in PySpark dataframes by using fill operations to replace nulls with blank, zero, or unknown, including per-column and multiple-column approaches.

Define the schema for a PySpark data frame using struct type and struct field, specifying column names, data types (integer, string), and nullable values.

Learn how to perform pivot and unpivot operations in PySpark, turning rows into columns and vice versa, using group by, pivot, and stack to reshape data.

Discover how to create and register a UDF in PySpark, extend Spark SQL, and use it to categorize ages into minor, adult, or senior within data frames.

Master PySpark date functions for data analysis, including date_add, date_sub, date_diff, year, month, and current_date, to handle rolling periods and month-end calculations.

Explore how the explode function in PySpark converts array data into multiple rows and supports pos explode. Learn to handle maps and dictionaries and extract key value pairs with explode.

Explore the Databricks file section: create and read files like 1.py and common.py, share variables across notebooks with imports, and use with open to read and write text files.

Databricks SQL provides a SQL-first workspace for business analysts to write queries and build dashboards, connecting to BI tools like Power BI and Tableau with fast, low-latency results.

Discover how to create and reuse query snippets in the sql editor for the sales customer table, including static and dynamic case statements with pass values and dollar syntax.

Master monitoring queries with query history and query profile to filter by user, compute, and duration, identify bottlenecks, and optimize execution details such as memory, reads, and transformations.

Explore configuring alerts in Databricks SQL, set email, Slack, or Microsoft Teams notifications, test conditions, schedule triggers, and customize alert templates to monitor thresholds.

learn to build visualizations and dashboards in Databricks, creating bar, line, and pie charts, and publish interactive dashboards with data sources, filters, and text boxes.

Learn to loop the same notebook multiple times using the for each activity, pass an array input, adjust optional concurrency, and conditionally run true or false branches in a pipeline.

Learn to schedule and trigger Databricks notebooks using schedule, file arrival, or continuous triggers; set frequency, time, time zone, and storage location for automated notebook execution.

Apply a medallion architecture in Databricks to consolidate CSV and JSON data into bronze, silver, and gold layers, then clean with PySpark for dashboard of revenue and average order value.

Explore Databricks medallion project setup in the free edition by creating a catalog, database, and volume, then establish bronze, silver, and gold folders and load csv and json data.

Explore Databricks medallion project setup and the silver layer by cleaning and transforming bronze data with PySpark, creating Delta tables, and preparing for the gold layer.

Explain how a dedicated SQL pool in Azure Synapse Analytics differs from serverless by provisioning compute with data warehouse units and paying for fixed capacity, enabling parallel processing for queries.

Learn to create a dedicated SQL pool in Azure Synapse, set DW units, upload a CSV to data lake, and create an external table to query data.

Explore Apache Spark in Azure Synapse as a general purpose in-memory computation engine that processes data across a cluster in memory for fast analytics and multiple workloads.

Navigate Microsoft Fabric’s workspace, data catalogs, and admin portal to monitor pipelines and real-time analytics. Explore workloads such as data engineering, data factory, data science, and data warehouse.

Create a Microsoft Fabric workspace as a collaborative container for lakehouse, data pipelines, notebooks, warehouse, and Power BI reports, then configure capacity, domain, and access for teams.

Understand how the lake house blends data lake and data warehouse concepts into a single fabric that stores unstructured and structured data and supports delta tables for sql queries.

Upload a csv to the lakehouse fabric, create a new table named EMP with header-based columns, and load the data into a parquet delta table.

Learn to create and use external shortcuts in Fabric to access data stored in Azure Data Lake Storage or other data lakes without copying.

Discover how caching in fabric shortcuts creates a temporary copy of external data, like S3 or Azure Data Lake, to boost read performance and lower costs.

Copy data activity moves data from an Azure SQL database to a lake house, exporting as parquet or json, via a data factory pipeline that validates and runs the job.

Use the delete activity in fabric data factory to clean up outdated data before daily S3 copies, ensuring only the latest files remain in the branch folder.

Learn how the filter activity in Fabric Data Factory selects csv files from blob storage by using get metadata, then loads them into a lakehouse.

Discover how to use the lookup activity in Microsoft Fabric to retrieve data from tables, files, or queries, and drive subsequent pipeline actions such as conditional deletes or data transfers.

Learn how the if condition activity in fabric evaluates file names with get metadata and foreach. Delete files with emp and copy others to lakehouse.

Set up notifications for pipeline failures by linking Office 365 Outlook and Teams, sign in to your organization Outlook account, and enable alerts.

Learn how the wait activity in Microsoft Fabric inserts delays between pipeline steps, pausing for minutes to space data copies and support retries after API failures.

Explore dataflow gen2 in Microsoft Fabric, a low-code, visual etl tool that uses Power Query to connect to diverse sources, transform data, and load into lakehouse or warehouse.

Explore data transformation in fabric by applying mathematical operations in the transform tab, including sum, min, max, average, count of distinct values, and arithmetic like discount, square root, and power.

Demonstrates joining two data sources in Microsoft Fabric using merge queries as new, selecting left or inner joins on customer ID, and loading the merged result to the final destination.

Create a data frame in PySpark from a lakehouse CSV, using spark.read with header and infer schema, then display with df.show.

Explore filtering data in PySpark with df.filter, selecting records where country equals United State or segment equals consumer, and combining conditions with and/or and customer name starts with C.

Master how to group by in PySpark to aggregate by segment, leveraging sum, min, max, and count on a data frame.

learn how to load data into a warehouse with data factory pipelines, data flows, copy into, and create table as select, with schema and credentials for adls delta files.

Apply the copy into command to load data from ADLs into the data warehouse, creating a gold schema and employee table, using a csv url with varchar columns.

Learn to load data into a data warehouse using a CTAS. Create a gold.consumer table from destination lakehouse data filtered by segment equals consumer for analytics.

Learn how views in a DWH fabric act as virtual tables that run a SQL query without storing data, enabling reusable, secure joins across customer, product, sales, and subscription data.

Master the new visual query in dwh fabric, transforming data on a canvas with filter, replace null with zero, and save the result as a view.

Power BI is a Microsoft business intelligence tool that connects to diverse data sources, cleans data, and creates interactive dashboards and reports for stakeholder decisions.

Explore Power BI Desktop overview, connect data from sources such as Excel, SQL Server, and CSV via get data, and navigate report, table, model, and DAX views to build visualizations.

Upload an Excel file in Power BI, load four sheets: customer, order, people, and return; convert order date to date time, and map the order–customer relationship in the model.

Create and format card visuals in a dashboard to display total sales, total profit, and counts of customers and distinct orders using the superstore data.

Learn to create bubble and field maps in Power BI to visualize country-level profit and sales, with gradient coloring by profit and rules coloring sales below or above one lakh.

Discover how tooltips in Power BI reveal extra information on hover, like sum of sales and sum of profit, and even count of order ID.

Explore how to apply filters in Power BI across page, all pages, and visuals, using basic and advanced filtering, contains logic, and top N to reveal country-based sales insights.

Explore the drill concept in Power BI, using drill up and drill down to navigate a date hierarchy (year, quarter, month, day) while analyzing sum of sales across levels.

Learn to apply the DAX format function to format columns, turning numbers and dates into readable values, with examples for sales, currency, and month names.

Learn to use the DAX CALCULATE function with FILTER to derive a sum of sales restricted to a date range. Filter the order date to capture April 1–15, 2019.

Install Anaconda on Windows, verify Python via the Anaconda prompt, and launch Jupyter Notebook through Anaconda Navigator for hands-on practice.

Explore Python, a high level, interpreted, general purpose language created by Guido van Rossum, used for web development, data analysis, artificial intelligence, and machine learning with extensive libraries.

Understand how Python variables act as containers that hold values and explore data types: string, integer, float, complex, list, tuple, set, and dictionary, plus dynamic typing and static typing.

Master Python conditional logic with if, elif, else, and nested ifs using dynamic user input; determine even or odd, pass or fail, and assign grades A, B, C.

Explore the list method and its inbuilt tools such as append, insert, remove, pop, sort, reverse, extend, index, and count to manipulate lists for coding interviews.

Learn to compute the sum and average of a Python list using both the built-in sum and a manual loop, with example list L.

Explore Python sets, an unordered, unindexed, and unchangeable collection that doesn't allow duplicates. Learn creation, iteration, and operations like add, update, remove, discard, pop, clear, delete, and union.

Explore python string tricks for data engineering tasks: palindrome checks, reversing strings and words, counting word occurrences, and removing duplicates using loops and join-split methods.

Explore non-keyword arguments and keyword arguments in Python by creating flexible functions with *args and **kwargs, including summing arbitrary inputs and handling dictionary key-value pairs.

Learn encapsulation in Python by restricting access to variables and methods; use private attributes with underscores, access them via class methods, and distinguish protected versus private access levels.

Explore how the Python os module enables file and directory manipulation, including creating and deleting directories, changing the current directory, listing contents, and iterating over files.

Learn to work with date time in Python, including getting current and custom dates, formatting with strftime, converting strings to dates, and adding or subtracting days with timedelta.

This end-to-end Azure data project ingests API data into lake storage. Implement medallion bronze-silver-gold architecture with data factory and databricks to derive revenue, purchases, store performance, and product category sales.

Execute an end-to-end azure data engineering project using adls, adf, spark, and synapse to implement medallion architecture (bronze to gold) and generate daily purchases and revenue reports.