Azure Data Factory - Data Engineering With Real Time Project

Explore hands-on activities in the portal by creating and using resources, and follow step-by-step supporting documents and naming conventions in the Azure portal.

Log in to portal.azure.com and learn to provision a resource group for a landing layer using Azure Data Factory and Azure Data Lake Storage Gen two.

Create an Azure Data Factory using both the create resource option and the global search. Configure the subscription, resource group, and region, then review and create.

Navigate the Azure portal to a resource group and create an Azure Data Lake Storage Gen2 account, enabling the hierarchical namespace for folders and subfolders.

Log into the Azure portal and set up the landing layer by creating a Data Factory and a Data Lake storage container, enabling hierarchical namespace, and organizing resources in dashboards.

Demonstrate extracting data from a web service and ingesting it into landing layer using azure data factory and azure data lake storage. Explore data factory studio for integration and orchestration.

Configure a copy data activity by defining a source dataset on an http web server with a delimited text format, then create a linked service and test the connection.

Configure the copy data activity to ingest data from an http web server into Azure Data Lake Storage Gen2, with a sink dataset and a new link service.

Configure source and sync data sets, create linked services to the http web server and Azure Data Lake, and copy data to the data lake using a debug pipeline.

Recap data factory components by creating linked services and datasets to connect http web server with Azure Data Lake Storage Gen2, and copy data into the landing layer.

Explore loading json data in Azure Data Factory by creating json datasets and using the copy data activity to land files in Azure Data Lake Storage Gen2.

Create and manage pipelines, activities, datasets, and linked services in Azure Data Factory, then monitor runs and debug copy data activities moving csv and json data.

Develop a dynamic Azure Data Factory pipeline that ingests daily changes from an http source into the landing layer, using runtime-derived relative URLs and copy data activities.

Switch from static to dynamic configuration in Azure Data Factory using the pipeline expression builder and dataset parameters, dynamic content and built-in functions to map runtime values to connection settings.

Create and configure pipeline parameters to parameterize source and sink datasets for dynamic content. Map pipeline parameters to dataset parameters using pipeline expression builder, avoiding hardcoding values at runtime.

Explore how dataset and pipeline parameters in data factory drive a generic data pipeline that loads http source files into the data lake landing container without user intervention.

Identify static and dynamic parameter values in a data factory pipeline by splitting into four parameters—folder name, file name, date value, and file type—and automate daily date derivation.

Learn to automate file dates in Azure Data Factory using the universal date time format and convert them to the source's date format for error handling in the ingestion pipeline.

convert universal date format to a file-specific format in Azure Data Factory using formatDateTime, applying it to source and sink, and reuse via a pipeline variable.

Derive the file date once with a pipeline variable in Azure Data Factory, using parameters and variables concept, and reuse it across source and sink with set variable.

Explore Azure Data Factory triggers, including scheduling and tumbling window types, and see how a trigger automatically runs pipelines and passes the source file date value and other parameters.

Learn to create and configure a tumbling window trigger in Azure Data Factory, covering start times, recurrence, end dates, historical data loading, and retry and concurrency settings for daily pipelines.

Learn to configure an Azure Data Factory tumbling window trigger to pass pipeline parameters and automate file date handling for each 24-hour run.

Publish changes to enable automated pipeline runs via a tumbling window trigger, and monitor these trigger runs to verify data loads into the landing container.

Learn how metadata driven ingestion in Azure Data Factory uses external parameters and a tumbling window to load daily source files into the data lake landing container.

Move file name parameters to a metadata driven pipeline in Azure Data Factory to support multiple source files with dynamic source and sink values.

Create a metadata JSON file to manage two source file name patterns for an Azure Data Factory pipeline, replacing inline parameters and enabling reading metadata within ADF.

Upload the metadata file to Azure Data Lake storage and configure a lookup dataset in Azure Data Factory to read file names, driving the copy data pipeline's source and sink.

Configure a lookup activity to read a small metadata json file from Data Lake Storage Gen2. Understand the four megabytes and 5000-row limits and create a dataset for the metadata.

Debug a pipeline to configure the lookup activity, enable reading all rows into a value array, and map array elements to copy data activity inputs in Azure Data Factory.

Configure a for each activity in Azure Data Factory to loop through the lookup value array and map the first element's relative URL file name to the copy data activity.

Configure a for each activity to pass metadata from a lookup into dataset parameters, mapping item relative URL file name to source and sink parameters, with optional sequential execution.

Debug the data factory pipeline by passing default values and correcting the metadata file to include the underscore in global pricing data for two source files on 1st January 2023.

Organize the folder structure in Azure Data Factory by creating working labs and ingest folders, and configure pipelines, datasets, and triggers for a streamlined end-to-end ingestion workflow.

Create an end-to-end metadata-driven ingestion pipeline by moving changeable parameters into a metadata file, and configure the relative URL folder name and CSV file type for daily pricing data.

Create and parameterize datasets for the end-to-end ingestion pipeline, mapping relative URLs and file paths from an http web server to data lake storage gen2.

Create a metadata driven ingestion pipeline in Azure Data Factory by configuring parameters, converting the source file date, reading metadata with a lookup, and using for each to copy data.

Navigate end-to-end metadata driven ingestion in Azure Data Factory, using copy data, lookups, and for each loops to build dynamic relative URLs and daily pricing data.

Debug the end-to-end, metadata driven ingestion pipeline by fixing a missing slash and mis-mapped relative URL in the metadata file. Validate the daily pricing folder and readiness to schedule.

Configure a metadata-driven end-to-end ingestion pipeline with a daily tumbling window trigger starting 2023-01-01, loading source files from http into the landing container, with two concurrent runs.

Ingest data from an http web service with a metadata driven, incremental daily pipeline that loads into the data lake landing container, using delta processing and dimensional modeling for pricing.

learn to design a dimensional data model for the reporting layer and build data factory pipelines that transform and load source data into the dimensional model.

Perform dimensional data modeling for the source daily pricing data in the landing layer, defining market, product, and date dimensions to support daily pipelines and the reporting layer.

Identify all columns and split them into business description and business measures in a logical data modeling workflow, capturing textual descriptors and quantifiable metrics with the date attribute.

Identify and group dimension columns with 1-to-1 relationships, analyze state, market, product, and variety constraints, and convert these groups into a dimension table.

Create dimension tables from grouped business description columns, assign surrogate keys (state_id, market_id, product_id, variety_id), and prepare the corresponding fact table.

Learn to design a fact daily pricing table by including business measure columns and linking to dimension tables via surrogate keys, with a date dimension for time reference.

Consolidates physical data modeling for the logical data model output, detailing data types for columns, and aligning dimension and fact tables for implementation in the reporting database.

Identify data types and lengths for columns using sql server types (varchar, integer, decimal, date), based on source values (state name 18 chars, market/product names up to 100).

Apply naming conventions with underscores, convert logical to physical dimension and fact tables, and add auditing columns for created and updated timestamps in a data mart.

Define audit columns with created and updated timestamps to track daily data loads and corrections, then create the reporting schema within an Azure SQL Server database for table storage.

Create the Azure SQL database resource in the Azure cloud. Build the diamond standard fact table and dimension tables using dimensional data modeling, then start the Azure Data Factory pipeline.

Explore the Azure SQL Database query editor in the Azure portal, learning connection methods, authentication, and schema management, including creating and dropping tables and running basic queries.

Download and install SQL Server Management Studio, connect to your database server with SQL Server authentication, view databases, and run a top 1000 rows query on reporting.dbo.state.

Create a reporting database and a dimension table in Azure SQL, and begin developing data factory transformation pipelines to load the reporting dimension tables.

Develop the first dimension table pipeline in azure data factory by applying source-to-target mapping rules, extracting unique state names from landing daily pricing files, and deriving state IDs.

Configure the source dataset in Azure Data Factory from the landing layer in Data Lake Storage Gen2 to load dimension state data into the new SQL Server dim state table.

Create a new Azure SQL linked service and sink dataset for the dim state table, configure server, database, SQL authentication, test connection, import schema, and publish for reuse.

Learn when to use copy data versus data flow in Azure Data Factory to load a dim table, applying column-level transformations and deriving state id and dates.

Learn to use Azure Data Factory data flow to transform and load the dim state dimension table, and compare it with copy activity.

Preview data and select only the state name column, exclude other columns to boost performance, then apply a dataflow select transformation to identify unique state names for the dimension table.

Identify unique state name values and their counts using Azure Data Factory data flow aggregator transformation. Group by state name and count occurrences to produce one row per state.

Learn how to load a dimension table using Azure Data Factory data flow expression builder to count state names, apply aggregates, and derive unique IDs for state values.

Allocate a unique surrogate key for each state name using the surrogate key transformation in dataflow, derive state_id for the dimension table, and preview results.

Derive two audit columns in Azure Data Factory data flow by assigning the current date to create and update date fields, then map to the dim state sink and publish.

Debug and build a data flow to load a dimension table, extracting unique state names, generating surrogate keys and audit columns, and loading to a SQL sink via a pipeline.

Learn dimension table load with change data capture (CDC) to handle daily multi-file inputs, derive surrogate IDs for unique state names, and prevent duplicates by validating against the sync table.

Enable change data capture for the dim state table by using separate source and sync datasets, renaming columns, truncating the target table, and preparing to merge streams.

Apply change data capture for a dimension table in Azure Data Factory by using a lookup transformation to merge source and sink, filter new state names before generating surrogate keys.

Configure a lookup and filter transformation to compare source and sync state name values, filter out null matches, generate surrogate ids and created date and updated date columns, prevent duplicates.

simulate real-time dimension loads by deleting records, reveal surrogate key limitations in change data capture, and ensure new IDs exceed the current maximum by passing the max surrogate key.

Identify the maximum surrogate key from the sink table using a SQL query in Azure Data Factory for change data capture, then generate new surrogate keys for real-time dimension loads.

Merge the maximum state id from the sync table with the mainstream source using a Joyner transformation, applying a custom join on non-null state names to load the target.

Apply a custom join condition in Azure Data Factory to load a dimension table by merging max state IDs with the surrogate key generator transformation, using derived columns.

Create a reusable data flow design in Azure Data Factory by structuring folders for ingest and transform, parameterizing datasets, and building source datasets for a generic, scalable pipeline.

Enable reusable data flows in Azure Data Factory by configuring a generic daily pricing landing source and reusable dim lookups to load the dimension table.

Parameterize the data flow to generate dynamic sql for loading dimension tables, using three parameters: reporting table lookup column name, schema name, and table name; use the expression builder.

Parameterize select statements and import projection in a reusable Azure Data Factory data flow to load multiple dimension tables with dynamic SQL and a generic lookup target column value.

Develop a reusable data flow by parameterizing the reporting table id column and using dynamic sql with concat to generate the maximum surrogate key value across dimension tables.

Configure a reusable dataflow by using select, aggregator, and lookup to identify distinct values, filter records not present in the sink via null lookup results, and load generic dimension tables.

Configure filter to load only new source records not in sink using a null dim lookup, then join with max surrogate key to derive key and created and updated dates.

Create a sync data flow to map derived values into the final target table. Develop a pipeline to run the flow with dataset-level and data-flow-level parameters and monitor run status.

Debug dataflow by managing the dataflow debug cluster and re-running to surface errors, then clone and parameterize the dataflow to load multiple dimension tables without duplicates.

Generate create table scripts for five dimension tables and one fact table within the reporting schema, using if exists drop statements and SQL Server Management Studio.

Clone the reusable data flow and rename it to populate the dim market table, importing the schema and mapping market name to the market id surrogate key.

Reuse data flows in azure data factory to load dim state, dim marker, dim product, dim market tables, then adjust product mappings and publish to verify loading.

Reuse data flows to populate dim_variety and other dimension tables by mapping source columns to variety and variety_id, then run in parallel, validate, and publish dim_market and dim_product.

Four pipelines run in parallel, loading dimension tables without duplicates, validating counts for dim_date, dim_product, and dim_variety, and planning automation of file parsing for daily loads.

Explore the incremental load of a dimension table by automating how source dataset parameters are passed in a metadata-driven ingestion pipeline, handling additional files and triggers without hardcoding.

Learn to use Azure Data Factory's Get Metadata to list files in the landing daily pricing folder and filter by last modified for incremental dimension loading.

Pass start and end dates as pipeline parameters to filter source files by last modified time, validating with defaults to process only files within the date range.

Implement incremental load by using get metadata to check the child items array and an if condition to run the dimension table load only when the array is not empty.

Guard the incremental load using an if condition, set a condition variable, and use get metadata to skip empty results, preventing exceptions before the dimension table load.

Explore how to use get metadata and the far reach activity to traverse files, handle the array output, and implement a multi-pipeline workaround for ADF incremental load.

Explore incremental load in Azure Data Factory by orchestrating pipelines, passing values between parameters, and using sub pipelines and for each to process files in a data lake.

Create an Azure Data Factory pipeline to load reporting tables by cloning datasets, listing metadata, filtering by start and end dates, and invoking the dim table pipeline when files exist.

Execute an incremental load by calling the reporting dimension from the master pipeline. Use a for each loop to process each file from the array parameter with data flows.

Explains incremental load debugging in Azure Data Factory, processing two source files with for each activity and optional sequential execution, and verifying dim table row counts.

Debug incremental dimension table loads in Azure data factory by fixing a path mismatch, updating pipeline parameters, and running data flows sequentially to reduce workload, then validate results in database.

Learn to configure a tumbling window trigger for an incremental load in Azure Data Factory, set start and end dates, run non-concurrently, and tune data flows for performance.

Demonstrate an incremental load with a tumbling window trigger, validating 9–10 files load via the integration runtime and no data discrepancies across dim product, dim market, and variety tables.

Detach the existing trigger and create a daily tumbling window trigger for http source full load, with ten concurrent files to the landing layer, then trigger dimension and fact loads.

Load the fact table through source-to-target mapping and 1-to-1 column mapping, populate surrogate keys via lookups from dim tables, and design the data flow with an incremental pipeline.

Create a new Azure Data Factory data flow to load the fact daily pricing table from the landing source, excluding origin, and perform a dim state lookup using dimension table.

Configure an Azure Data Factory data flow to load a fact table using a dim state lookup to map state names to IDs, while enforcing sink versus source rules.

Configure data flow to load the fact table by cloning and preparing dimension sources (market, product, variety), set up lookups for surrogate IDs, and apply selective projections and naming conventions.

Configure the date dimension in a data flow by mapping to the dim date lookup, convert string pricing to date with a derived column, and ensure a successful join.

Debug and fix dataflow for fact table load by correcting date format, validating lookup date IDs, and trimming spaces to ensure accurate joins with the dim table.

Configure a sink transformation in a data flow to load daily pricing into the fact table, deriving dw created date and dw updated date, and perform manual column mapping.

Set up a new tumbling window trigger to load the fact table end-to-end, disable legacy triggers, and ingest one year of landing data every five minutes from the http server.

Complete loading of fact and dimension tables into the reporting database with 350 days of data and 500k records; variety id nulls stem from the source, ready for end users.