Data Engineering Course

Introduction to SQL

• SQL basics
• Relational databases
• Data manipulation language (DML) and data definition language (DDL)
• SQL data types and constraints

Database Normalization and Entity Relationship Model

• Database normalization
• Entity Relationship (ER) model
• Normal forms (1NF, 2NF, 3NF)
• Entity Relationship Diagrams (ERDs)
• Cardinality and relationships

SQL Operators

• Comparison operators
• Logical operators
• Arithmetic operators
• String operators
• NULL-related operators

Join, Tables, and Variables in SQL

• SQL joins (inner join, outer join, cross join)
• Working with tables (creating, altering, dropping)
• Using variables in SQL queries
• Query optimization and performance tuning

Deep Dive into SQL Functions

• Scalar functions
• Aggregate functions
• String functions
• Date and time functions
• Conversion functions

Subqueries in SQL

• Subquery basics
• Correlated subqueries
• Subquery in the WHERE clause
• Subquery in the FROM clause
• Subquery optimization techniques

SQL Views, Functions, and Stored Procedures

• Creating and using views
• User-defined functions (UDFs)
• Stored procedures
• Parameters and variables in functions and procedures
• Error handling and transactions in stored procedures

User-defined Functions in SQL

• Creating and using UDFs
• Scalar UDFs
• Table-valued UDFs
• Inline UDFs
• Performance considerations for UDFs

SQL Optimization and Performance

• Query optimization techniques
• Indexing strategies
• Query execution plans
• Analyzing query performance
• Performance tuning tips and best practices

SQL Parsing

• Query parsing process
• SQL statement components
• Query optimization and execution plan generation
• Understanding query parsing errors and messages

Managing Database Concurrency

• Concurrency control in databases
• Locking mechanisms
• Transaction isolation levels
• Deadlock detection and prevention
• Managing concurrent database operations specific exceptions

Python Basics

• Introduction to Python programming language
• Variables, data types, and operators

OOPs Concept

• Object-Oriented Programming (OOP)
• Classes, objects, inheritance, encapsulation, abstraction, polymorphism

NumPy

• NumPy library
• Arrays and array operations
• Indexing, slicing, and reshaping arrays
• Mathematical functions in NumPy

Pandas

• Pandas library for data analysis
• Series and DataFrame data structures
• Data cleaning and preprocessing with Pandas

Data Visualization

• Introduction to data visualization
• Matplotlib library for basic plotting
• Seaborn library for statistical data visualization
• Plotly library for interactive visualizations

File Handling

• Reading and writing files in Python
• Text file manipulation and processing
• File manipulation and file formats

Exception Handling

• Introduction to exception handling
• Errors, exceptions, try-except blocks
• Handling specific exceptions

Regular Expressions Fundamentals

• Introduction to regular expressions
• Pattern matching and searching in text
• Matching and replacing patterns

Introduction to Linux

• Overview of Linux and its key features.
• Understanding the Linux file system hierarchy.
• Different distributions of Linux and their characteristics.

File System Navigation

• Navigating the Linux file system using commands like cd, ls, and pwd.
• Working with directories and files.
• Understanding file permissions and ownership.

File and Text Manipulation

• Reading, creating, and modifying text files using commands like cat, echo, and nano.
• Searching for patterns in files using grep.
• Sorting and filtering text using commands like sort and awk.

User and Group Management

• Creating and managing user accounts and groups.
• Assigning permissions and privileges to users and groups.
• Understanding user authentication and password management.

Process Management

• Managing processes using commands like ps, top, and kill.
• Running processes in the background and foreground.
• Monitoring system resources and identifying resource-intensive processes.

System Configuration and Networking

• Configuring network settings and interfaces.
• Managing network connections and troubleshooting network issues.
• Modifying system configurations using configuration files.

System Monitoring and Logging

• Monitoring system performance and resource usage.
• Analyzing system logs for troubleshooting and debugging.
• Understanding log rotation and log file management.

Shell Scripting

• Introduction to shell scripting and shell programming.
• Writing and executing shell scripts.
• Automating repetitive tasks using scripts.

Security and Permissions

• Understanding Linux security mechanisms.
• Setting file and directory permissions.
• Managing user access and implementing security best practices.

Advanced Linux Commands

• Exploring advanced Linux commands and utilities.
• Working with compressed files and archives.
• Performing remote operations using SSH and SCP.

Introduction to Data Warehousing

• Overview of data warehousing concepts
• Benefits and use cases of data warehousing
• Data warehousing architecture and components
• Introduction to ETL (Extract, Transform, Load) processes

Data Cleaning Techniques

• Understanding data quality issues and challenges
• Data cleaning methodologies and best practices
• Handling missing data and outliers
• Data deduplication and data normalization
• Data validation and data cleansing techniques

SQL Connectors and Data Integration

• Introduction to SQL connectors
• Connecting to various data sources (relational databases, flat files)
• Extracting data from different sources using SQL
• Loading data into data warehouse using SQL connectors

Building Data Pipelines

• Introduction to data pipelines in data warehousing
• Overview of data integration and transformation
• Designing and implementing data pipelines using SQL
• Monitoring and managing data pipelines

Data Quality and Governance in Data Warehousing

• Importance of data quality in data warehousing
• Data quality dimensions and metrics
• Data profiling and data quality assessment

Performance Optimization in Data Warehousing

• Performance challenges in data warehousing
• Indexing strategies and optimization techniques
• Query optimization in data warehousing
• Monitoring and troubleshooting performance issues

Emerging Trends in Data Warehousing

• Cloud-based data warehousing solutions (e.g., Amazon Redshift, Google BigQuery)
• Data lakes and data warehouse integration
• Real-time data warehousing and streaming data processing
• Data virtualization and data federation in data warehousing

Basic Concepts of Data Modelling

• Introduction to data modeling
• Conceptual, logical, and physical models
• Best practices in data modeling

Business Data Requirements – Entities and Classes

• Identifying business data requirements
• Entities and classes in data modeling
• Entity-relationship modeling (ER modeling)

Business Data Requirements – Attributes

• Types of attributes in data modeling
• Attribute domains and data types
• Constraints and naming conventions for attributes

How To Link Things Together – Relationships

• Types of relationships in data modeling
• Cardinality and optionality in relationships
• Role names and associative entities

Requirements Analysis

• Gathering and analyzing data requirements
• Functional and non-functional requirements
• Documentation of data requirements

Conceptual Data Modeling

• Creating a conceptual data model
• Entity-relationship diagrams (ERDs)
• Representing business processes and data flows

Logical Data Modeling

• Transforming conceptual model to logical model
• Tables, columns, normalization
• Primary and foreign keys in data modeling

Physical Data Modelling

• Converting logical model to physical model
• Database schema, tables, denormalization
• Indexing and partitioning strategies

Data Modelling Tools and Techniques

• Overview of data modeling tools (e.g., ERwin, PowerDesigner)
• Reverse engineering and forward engineering
• Techniques for data model manipulation

Data Modelling Documentation and Communication

• Documenting data models using notations
• Data dictionaries and model diagrams
• Presenting data models to stakeholders
• Effective communication of data models

Introduction to  Hadoop Ecosystem

• Overview of big data and parallel processing
• Hadoop architecture and components
• Hadoop Distributed File System (HDFS)
• MapReduce and YARN frameworks

HDFS (Hadoop Distributed File System)

• Understanding the Hadoop file system
• HDFS architecture and data storage
• HDFS commands for file operations
• File replication and fault tolerance in HDFS

Apache Hive for Data Warehousing and Querying

• Introduction to Apache Hive
• Hive architecture and components
• HiveQL: SQL-like querying language for Hive
• Creating and managing tables in Hive
• Hive data types and file formats

Apache Impala for High-Performance 

• Introduction to Apache Impala
• Impala architecture and components
• Impala  for interactive querying
• Impala integration with Hadoop ecosystem
• Performance optimizations in Impala

Working with Big Data using Hadoop and Hive

• Data ingestion into Hadoop using Hive
• Data transformation and processing with Hive
• Hive queries for data analysis and reporting
• Joining and aggregating data in Hive
• Advanced Hive features and functions

Integrating Hive with Other Tools and Systems

• Integrating Hive with Apache Spark
• Hive integration with Apache HBase
• Hive integration with data visualization tools
• Real-time data processing with Hive

Working with Cassandra in Hadoop Ecosystem

• Introduction to Apache Cassandra
• Cassandra architecture and data model
• Data ingestion into Cassandra from Hadoop
• Integrating Cassandra with Hive and Impala
• Querying and managing data in Cassandra

Advanced Topics in Hadoop and Hive

• Introduction to Hadoop ecosystem components 
• Working with NoSQL databases in Hadoop
• Monitoring and troubleshooting in Hadoop
• Future trends and advancements in Hadoop and Hive

Introduction to Microsoft Azure

• Introduction to Microsoft Azure
• Introduction to ARM & Azure Storage
• Azure Virtual Machines
• Azure Networking – I
• Azure Networking – II

Authentication, Authorization, and Monitoring

• Authentication and Authorization in Azure using RBAC
• Microsoft Azure Active Directory
• Azure Monitoring

Data Storage and Integration

• Data Storage in Microsoft Azure
• Non-Relational Data Stores and Azure Data Lake Storage
• Data Lake and Azure Cosmos DB
• Relational Data Stores
• Why Azure SQL?
• Azure Data Lake Storage Gen2 and Data Streaming Solution
• Data Integration with Microsoft Azure Data Factory
• Designing Data Flows in Azure
• Using Azure Data Factory Pipelines to Copy Data
• Monitor Azure Data Factory using Azure Monitor

Azure Synapse Analytics and Databricks

• Introduction to Microsoft Azure Synapse Analytics
• Using Azure Synapse Analytics to Query Data Lake
• Optimizing Dedicated SQL Pools in Azure Synapse Analytics
• Data Warehousing with Microsoft Azure Synapse Analytics
• Data Engineering with MS Azure Synapse Apache Spark Pools
• Operational Analytics with Microsoft Azure Synapse Analytics
• Handling Slowly Changing Dimensions With Azure Synapse Analytics Pipelines
• Microsoft Azure Databricks for Data Engineering
• Running Spark on Azure Databricks
• Using Azure Databricks to Import and Analyze Data
• Introduction to Delta Lake on Azure Databricks

Azure Stream Analytics, and Azure Service Bus

• Introduction to Azure Stream Analytics
• Monitoring & Security
• Azure Functions
• Azure Service Bus

Introduction to Apache Spark

• Overview of big data processing and Apache Spark
• Spark architecture and components
• Introduction to Resilient Distributed Datasets (RDDs)
• Understanding Spark’s distributed computing model

PySpark SQL and Data Frames

• Introduction to PySpark SQL module
• Working with structured and semi-structured data
• Data exploration and analysis using DataFrames
• Querying and manipulating data with SQL-like syntax

Apache Kafka and Flume

• Introduction to Apache Kafka and Apache Flume
• Streaming data ingestion using Kafka and Flume
• Integration of Kafka and Flume with PySpark
• Real-time data processing and analysis

PySpark Streaming

• Introduction to PySpark Streaming
• Processing live data streams with PySpark
• Windowed operations and aggregations
• Real-time analytics using PySpark Streaming

Introduction to PySpark Machine Learning

• Overview of PySpark Machine Learning (MLlib)
• Machine learning concepts and algorithms in PySpark
• Building and evaluating machine learning models with PySpark
• Feature engineering and data preprocessing