Data Warehouse / ETL Testing

Streamline user interaction by having the operating system serve as the interface between users and computer hardware, managing memory, processes, files, and input and output for convenience and efficiency.

Learn about Windows, Mac OS, and Linux, their versions and popularity, and explore Linux distributions like Ubuntu, Fedora, and Debian, along with Linux’s free, open source, secure, and flexible advantages.

Learn essential Linux directory commands: pwd, mkdir, cd, cd dot dot, dir, and the -r option for recursive deletion, with hands-on Ubuntu terminal demos.

Explore essential Linux file commands for creation, editing, viewing, searching, and sorting files, including vi, gedit, cat, head, tail, sort, touch, find, locate, and pwd.

Learn to use the ls command in Ubuntu to list files, view details with -l, include hidden files with -la, sort by modification time with -lt, and filter by extension.

Master copying and moving files and directories in linux with cp and mv, including recursive directory copies using cp -r and essential commands like pwd and ls.

Learn how the linux more command lets you view text files in the terminal, navigate with space, quit with q, and use options like -d and -p to clear screen.

Explore regular expressions in Linux and use grep to match patterns with symbols like dot, carat, dollar, star, backslash, braces, and question mark. Learn grep syntax and recursive search options.

Learn grep hands-on techniques to search patterns in files, using anchors like ^ and $, apply case-insensitive matches with -i, display line numbers with -n, and recursively search directories.

Explore linux diff command theory and hands-on techniques to compare files line by line, view add, change, and delete indicators, and use context or unified modes with the -i option.

Explore Linux pipes as unidirectional inter-process communication that passes command output to another command, enabling combined tasks with sort, uniq, head, tail, grep, and wc in hands-on examples.

explore the linux man command, a pager for viewing command manuals and documentation, with options -f and -k across sections like executables and system calls.

Learn how to monitor Linux processes using the ps command, distinguish foreground and background jobs, and read process details like pid, tty, time, and cmd.

Explore hands-on process monitoring on Ubuntu by distinguishing foreground and background processes, suspending with Ctrl Z, and using top, jobs, fg, and ps variations including PID filtering and root processes.

Explore Linux file permissions, covering owner, group, and others, and the read, write, and execute rights that control access to files and directories, with inspection via ls -l.

Explore Linux file permissions and how to view and change them using chmod with octal values for owner, group, and others.

Apply hands-on linux networking with essential commands such as whoami, ip, w, ping, wget, dns lookup, host, and netstat to inspect and troubleshoot network connections.

Explore essential Linux admin commands and their usage, from uname and pwd to who, uptime, and sudo, including privilege management and apt-get update.

Identify data as raw facts and figures, a set of values about subjects with qualitative or quantitative variables. Compare information as interpreted data—processed within context to give it meaning.

Explore how to store data using lists, hierarchical folders, and tabular databases, and learn how table organization enables efficient filtering by country, state, city, and date for data warehousing.

Explore the evolution of data storage, from text files and spreadsheets to semi-structured JSON and XML, and finally relational database management systems (RDBMS) for data warehousing.

Trace the evolution from dbms and hierarchical models to the relational dbms, highlighting edgar f card's relational model, structural independence, data independence, and powerful query capabilities.

Explore the core database components, including tables (relations), columns (domains), and rows (tuples), and learn how entities, attributes, and records define the data structure.

In this hands-on session, learn to set up MySQL on Ubuntu, create a database called university, build a persons table, and insert and query records using basic SQL commands.

Learn how to define data types in MySQL by creating a student_details table with int, char, date, and varchar columns, inserting rows, and describing the table schema.

Explore database objects in a relational database management system, including tables, indexes, sequences, views, and synonyms, and understand how they store or reference data across vendors.

Explore how indexes map to physical data locations to speed up row retrieval and reduce disk I/O, with MySQL syntax for creating indexes on table columns.

Understand how sequences generate unique numeric values as database objects to create automatic primary keys, with ascending or descending orders and defined intervals, including autoincrement in MySQL.

Explore views as virtual tables that derive data from one or more tables, selecting only essential columns with zero storage usage and access-control benefits.

Learn to create and query MySQL views on Ubuntu within the university database, including car view and actor car company, where views reference data from base tables.

Explore entity relationship modeling to build a conceptual design visible through diagrams, independent of any RDBMS, and learn how entities, attributes, relationships, and cardinality reveal design problems.

Explore er diagram components, mapping entities like students and lecturers to tables with attributes, and learn how relationships and cardinalities—one-to-one, one-to-many, many-to-many—define participation.

Analyze requirements to draft an ER diagram by identifying entities, attributes, relationships, and cardinalities. For a university database, map departments, courses, modules, lecturers, and students with key relations.

Explore keys in SQL and learn how primary, candidate, super, unique, alternate, surrogate, foreign, and composite keys identify rows and establish relationships between tables.

Identify super keys as attributes that uniquely identify records, then derive candidate keys and the primary key through a foolproof process, noting super keys may include extra attributes.

Understand how candidate keys uniquely identify a tuple by selecting minimal super keys without redundant attributes, with ID and phone forming candidate keys and a primary key chosen from them.

A university student id uniquely identifies each row, demonstrating the primary key concept, which cannot be null or duplicate and is defined when creating a table.

Understand how foreign keys connect tables in a relational database management system by linking primary keys to corresponding foreign keys, using student and course examples, including nullable options.

Discover how data integrity ensures accuracy, completeness, and consistency, enforced by data typing and constraints such as default, check, unique and key, with data validation supporting regulatory compliance.

Explore domain integrity and other data integrity types—entity, referential, and user defined—plus check, default, not null, and unique constraints that govern valid column values.

Explore the check constraint in MySQL with a hands-on employee table example, enforcing salary values greater than zero through inserts and validation in a university database.

Learn how a default constraint automatically inserts a default value into a column, such as current timestamp, ensuring complete data when records lack fields, via create table or alter statements.

Define the not null constraint and see how it prevents inserting null values. The lecture uses an employee table to illustrate enforcing not null on id and name.

Understand how the unique constraint enforces domain integrity—no two rows share the same value, with nulls treated as unique—and can apply to multiple columns, unlike the primary key.

Explore entity integrity with primary keys and composite primary keys. Create student tables and insert records to observe nulls and duplicates being rejected.

Understand referential integrity constraints that link tables via primary and foreign keys, ensuring synchronized, valid relationships between parent and child tables like department and student.

Decompose tables to eliminate data redundancy and anomalies, reinforcing data integrity in database design through first normal form, second normal form, third normal form, and Boyce-Codd normal form.

Discover how normalization reduces redundancy by decomposing a monolithic employee table into separate employee, address, and subordinates tables linked by IDs, and how joins retrieve complete data.

Explore functional, transitive, and partial dependencies and how they guide normalization in data models, illustrated with employee and invoice examples.

Learn how to achieve first normal form by ensuring atomic, single-valued data in a table, decomposing addresses, and enforcing uniform column data types for reliable data normalization.

Learn how third normal form follows second normal form and eliminates transitive dependencies by splitting tables, as shown with a student and subject example.

Learn how acid properties guarantee the validity of a database transaction during errors or power failures. Explore atomicity, consistency, isolation, and durability.

Explore atomicity, one of the acid properties, ensuring a transaction is all or nothing, with read and write operations and commit or abort actions.

Consistency means the database stays correct before and after a transaction, enforced by integrity constraints. The transfer example shows totals remaining the same across accounts to prevent inconsistency.

Understand the isolation property in database transactions, ensuring concurrent transfers remain independent and prevent inconsistent balances through concrete account examples.

Durability ensures that once a transaction completes, updates are written to disk permanently and stored in non-volatile memory, remaining safe even in system failures.

Explore the five sql command types—ddl, dml, dql, tcl, and dkl—and how they define, manipulate, and query data with select while controlling access.

Rename database objects with alter table to change table names or columns. Apply the syntax alter table <table> rename to <newname> and alter table <table> rename column <old> to <new>.

Explore drop and truncate in SQL, learning how drop removes a table and its structure and data, while truncate clears only the data keeping the schema intact.

Master data manipulation language commands by learning insert, update, and delete operations, and see how conditions and primary keys control changes to table data.

Explore the three core data insertion methods for tables: standalone inserts, loading from files, and inserting from other tables, with syntax tips and column selection.

Learn multiple data insertion techniques in sql, including standalone inserts with or without column lists, and loading data from files or from other tables with conditions.

Explore data control language (DCL) commands to manage user access, including grant and revoke privileges on databases and tables, and control permissions across hosts.

Explore data control language (dcl) commands in MySQL, creating users, granting and revoking privileges, and enforcing selective access across databases and tables in hands-on scenarios.

learn to use TCL commands such as start transaction, commit, rollback, and savepoint to manage database transactions, save changes permanently, and roll back to a chosen save point when needed.

Explore data query language (dql) concepts and master the select statement to retrieve data from schema objects, using all or specific columns, rows, and conditions with where and limit.

Practice hands-on dql commands by creating an employee table, inserting data, and selecting all or specific columns from the university database, including limiting rows.

Understand how SQL statements work: they are not case sensitive, may span single or multiple lines, require keywords without abbreviations, and treat database objects as case sensitive; indentation improves readability.

Master alias theory and hands-on techniques in SQL, creating temporary table and column aliases to improve readability during joins and subqueries, with practical employee-table examples.

Explore arithmetic operators that perform operations on numerical values in databases. Master operator precedence, including multiplication before addition, and use parentheses to override order in SQL salary examples, including modulo.

Practice arithmetic operators in SQL on a sample employee table, applying addition, multiplication, and modulus, exploring operator precedence, parentheses to override, and odd/even checks.

Explore SQL comparison operators, including equal to, greater than, less than, and not equal to, using an employee table to filter by salary and age.

Explore how logical operators like and and between combine conditions to form complex queries in data warehousing. See examples using age, location, and range filters.

Manipulate data with in and not in operators in sql, using where clauses to filter employee records by id, excluding certain ids, and exploring not between variations.

Explore the like and or logical operators in data querying, using wildcard characters % and _ to match patterns in columns, with practical MySQL and R examples.

Explore how exists and not exists operators test subquery results, returning true or false, using employee and department tables to filter by department_id.

Learn how to use SQL aggregate functions—sum, average, count, max, and min—on an employee table, with group by, order by, conditions, and handling nulls.

Explore how the order by clause sorts records by a specified column, with ascending or descending order, including multiple columns and handling null values.

Discover how to use order by to sort data in ascending order, with examples for age, column numbers, multiple columns, and conditions like salary > 30000 on an employee table.

Master sorting data with order by in a descending order, using column names or numbers, including multiple columns and where salary greater than 30,000 in MySQL.

Learn to sort query results by multiple columns using ascending and descending orders, with the first column taking priority and secondary columns breaking ties, demonstrated on an employee table.

Learn how to sort a column in ascending order when null values appear, using order by and coalesce in MySQL with the employee table's commission column.

Sort the commission column in descending order, with null values pushed to the end. Use commission is not null or coalesce to bring nulls to the beginning.

Group by department_id aggregates salaries across employees using sum, showing department totals. Apply a where clause, such as age > 25, and optionally order by results.

Apply the group by clause with hands-on examples, grouping by one or two columns (department id and location), sum salaries, and handle errors with fixes and column-number grouping.

Learn how the having clause filters groups after a group by and aggregation, how it differs from where, and see examples using department, salary sums, and counts.

Explore null values in databases by defining null as an absence of a value, distinguishing it from zero and spaces, and understanding how missing data appears in tables.

Learn to insert and update null values in SQL tables, using explicit nulls or leaving columns blank, with examples from the employee six table.

Differentiate null from empty space: null means no value, empty space is a real string of zero characters; count(*) includes nulls, while count(column) excludes nulls but includes empty spaces.

Use is null and is not null to find null values in a table, such as in the salary column, and count nulls by comparing total rows to non-null counts.

Explore hands-on techniques to identify and count null values in a salary column using is null, is not null, and case statements in SQL.

Understand how null values behave in arithmetic expressions, where any null operand yields null, and see how comparison operators ignore nulls when filtering salaries.

Explore how null values propagate in arithmetic expressions and how comparisons ignore nulls in SQL, by hands-on examples using the Employees salary data.

Explore subqueries in SQL, including the main query and nested inner queries. Apply subqueries with select, insert, update, delete, and group by, noting parentheses and null outcomes.

Master subqueries in MySQL by calculating max and min salaries, retrieving employee names, and performing insert, update, delete, and group by having operations across the emp and department tables.

Explore row-level sub queries (single row and multi-row), multiple column sub queries, and correlated subqueries in SQL, demonstrated with employee and department data.

Understand sub queries and correlated subqueries, where inner queries depend on outer queries to compute department averages and identify employees whose salary exceeds averages. Compare readability and performance with joins.

Explore row level sub queries, including single row and multi row variants, in MySQL with emp one and department to fetch names and salaries.

Learn row-level and correlated subqueries with a hands-on SQL exercise on a university database, using self-join techniques to compute department averages and identify employees earning above those averages.

Explore how joins link multiple tables in relational databases by common fields, enabling data retrieval with inner, left, right, full, cross, and self joins across select, update, and delete operations.

Explore how joins work in a relational database by combining two tables on a common field such as customer ID, using inner join, left and right tables, and row-by-row matching.

Practice inner joins hands-on by creating customers and orders tables, joining on the customer_id key with aliases C and O. Explore variations using join, join using, and where conditions.

Explore self-joins, where a table joins with itself using aliases, to query related rows within the same table, such as finding customers sharing an address.

Explore self joins in MySQL using a customers table to find matching addresses. Practice examples compare self join results with inner join and include cases like Neha's Bangalore address.

Learn how cross joins implement the Cartesian product by pairing every row from one table with every row from another, producing large data sets.

explore cross joins in MySQL by combining meals and drinks to form a Cartesian product, then refine results with select, order by, and limit.

Explore set operators in SQL by understanding sets, combining rows from multiple queries, and using union, union all, minus, and intersect to analyze data.

Learn how to use the union set operator in SQL by combining tables with matching data types, excluding duplicates, and applying column order, filters, and order by.

Explore the union all set operator with hands-on SQL exercises using student one and student two tables, including selecting columns, ordering, filtering by Mysore, and cross-table unions.

Learn how the minus set operator returns rows in the first query not present in the second, with no duplicates, and apply Oracle usage or MySQL workarounds using left joins.

Learn to emulate minus set operations in MySQL with left and right joins, computing student one minus student two and vice versa using null join key checks.

Explore string functions in RDBMS like Oracle and MySQL, covering case manipulation, trimming spaces, and character operations such as substring, locate, replace, reverse, and concat.

Learn to manipulate string cases with lower, upper, and init cap functions, applying them to table columns and literals in MySQL and Oracle.

Learn how to add and remove spaces in strings using length and trim functions, and explore how spaces affect data in a student table with hands-on examples.

Learn to pad strings in ETL workflows using l-pad and pad functions, padding on the left or right to a specified total length, and using the space function for spacing.

Learn how to add and remove spaces in strings using length, trim, left trim, and right trim functions, and pad strings with lpad and rpad to fixed lengths.

Explore substring theory by learning how to extract parts of a string with substring, sub str, or mid, using start position, length, and noting that spaces are considered.

Explore the substring function on table columns, extracting characters by position and length, including negative positions and removing last three characters, and update a column to the first two characters.

Explore MySQL substring techniques with hands-on examples on the university database. Learn to extract and update strings using substring, substr, and length arguments, including positive and negative positions.

Discover how the substring index function extracts string parts by delimiter occurrences, using positive or negative counts to parse left-to-right or right-to-left, with examples on urls and website columns.

Explore the string and locate functions, learn to find first occurrences of substrings with case-insensitive searches, return zero when not found, and apply them to table data with where conditions.

Explore in string and locate functions with hands-on examples using a student marks table, learning substring position, multi-character search, case insensitive, and how to apply these in where clauses.

Explore what a data warehouse is, a subject oriented, integrated, time variant, nonvolatile store of data from diverse sources used to support management decisions and historical analysis.

Trace storage evolution from tapes to solid state drives, note cheaper storage and applications, and see how data from organizations and users birthed the data warehouse, fathered by William Inman.

Differentiate OLTP and OLAP by contrasting day-to-day operational processing with historical data analysis in a data warehouse through extract, transform, and load.

Learn how ETL extracts data from multiple sources into a staging area. Transform that data into a standard, non-redundant form and load it into the data warehouse.

Explore how extraction pulls the expected data from heterogeneous sources—RDBMS, applications, and files—into a staging area, enabling transformation and loading for a data warehouse.

Discover transformation in etl by applying business rules to convert data formats and values. Focus on the staging area and loading into the data warehouse.

Discover the core ETL transformation types—basic conversion, integration, filtering, calculations, sorting, and error handling—and how they prepare data for loading into the data warehouse.

Learn how ETL loads data into the end target, moving extracted and transformed data from various sources into a data warehouse or data mart as the final step.

Introduce data warehouse architecture concepts by explaining how data moves between systems, and outline components such as databases, etl tools, metadata, query tools, and data marts from a layered perspective.

Explore data warehouse architectures by layers: without staging area, with staging area (landing zone), and with data marts. Understand how data sources, staging, and warehouse interact, transformations and quality checks.

Explore data warehouse architectures by tier: single, two-tier, and three-tier designs. Understand data cleansing, ETL, staging, data marts, and OLAP-enabled presentation across bottom, middle, and top layers.

Explain how a data mart is a subject-specific subset of a data warehouse, reducing data volume, improving query performance, and enabling controlled access.

Discover how a data warehouse stores data in tables, contrasting transactional databases with analytical warehousing, using dimension and fact tables.

Discover how data warehouses use dimensional modeling with fact and dimension tables, where numeric measurements in fact tables connect to descriptive dimensions via foreign keys.

Explore how to classify transactional data into dimension and fact tables in a data warehouse, using primary keys to relate store and product dimensions to facts like quantity and price.

Discover the star schema in data warehouses, featuring a central fact table and surrounding denormalized dimension tables that enable fast ad hoc queries.

The snowflake schema extends the star schema by normalizing dimension tables around a fact table, forming multiple related tables that resemble a snowflake with more foreign keys and slower queries.

Explore the fact constellation schema, a galaxy of multiple fact tables sharing common dimension tables, such as sales and shipping, forming interconnected star schemas and increasing query complexity.

Understand slowly changing dimensions (SCD) and how they track current and historical data in a data warehouse using type zero through type six approaches to preserve referential integrity.

Explore SCD0 and SCD1 by contrasting fixed dimension data with no history and overwriting changed values to maintain referential integrity, including examples like date of birth and vendor location.

Explore SCD type 3 by adding a single history column to track limited changes, such as salary and designation, and understand its advantages and drawbacks for infrequent changes.

Learn how SCD type 4 uses a current dimension table plus a history table to track changes with surrogate keys, improving query performance while increasing warehouse size and administration.

Learn how SCD type 6 blends type 1, type 2 and type 3 to track dimension changes in a data warehouse, using replacements, row versioning, and current attribute tracking.

Learn how ETL tools simplify extracting from sources like Oracle, MySQL, and files, transforming data, and loading into a data warehouse, using graphical interfaces for mapping and automation.

Discover how scheduling tools automate ETL pipelines for data warehouse, enabling repeated runs, reduced downtime, and boosted productivity by orchestrating extraction, transformation, and loading with tools like Artosis and Tidal.

Define quality as the collection of features and characteristics that meet customer needs and drive satisfaction. Emphasize its importance for software applications and the user experience.

Learn how software testing evaluates a system, manually or automatically, to verify it satisfies functional and non-functional requirements, executing programs to uncover defects before production.

Explore the software development life cycle, a framework that covers analysis, design, development, testing, deployment, and maintenance to deliver high quality at minimal cost.

Analyze requirements with business analysts and document them as a software requirement specification, then design high-level and low-level architectures, develop code, test, deploy to production, and maintain the system.

Learn the software testing life cycle (stlc) and how it aligns with the sdlc, covering verification and validation, test planning, design, development, execution, defect reporting, regression, and user acceptance testing.

Explore the stages of the software testing life cycle, from test planning and test case design to execution, defect reporting, regression testing, acceptance testing, and test summary analysis.

Explore user expectations from software, including reliability, usability, installability, compatibility, efficiency, and attractiveness, with emphasis on not losing or altering data, graceful failure, clear progress indicators, and consistent design.

Learn to categorize software requirements into functional and non-functional, detailing what the system should do, how it should perform, and test criteria, with examples like login and page load constraints.

Understand test cases as a set of inputs, execution conditions, and expected results used to verify requirements. Identify components and qualities, including test suite id, prerequisites, test script, and traceability.

Explore test case design techniques for data warehouse and ETL testing, including equivalence partitioning, boundary value analysis, and high-level methods like state transitions and decision tables.

Explore high level test case design techniques focused on specification and formal notation to generate scenarios, using state transition, flowchart, use cases, decision tables, and Boolean logic.

Explore low-level test case design techniques, including equivalence partitioning and boundary value analysis. Apply input-output domain testing to partition inputs and optimize test coverage.

Explore low level test case design techniques such as special value testing, error guessing, and cause and effect. Compare application behavior across multiple environments and configurations to ensure consistent results.

Explore the software testing phases from unit to regression testing, and see how each phase verifies components, functionality, interfaces, and user acceptance testing against requirements.

Discover unit testing as validating small pieces of code at the procedure and function level, enabling early defect detection, data quality checks, boundary conditions, and memory leak checks.

Explore functional testing, a black box approach that validates software against functional requirements. Assess mainline functions, usability, accessibility, and error handling to ensure the product meets user stories.

Explore integration testing and how it validates connectivity between modules built in isolation. See how data flows across components, using an email-sending scenario to ensure the final outcome meets requirements.

Conduct end-to-end system testing to verify the fully integrated software from user input to final output, covering functional and non-functional requirements and external behavior.

Regression testing validates that code changes do not affect existing functionality and detects new defects, improving quality and reducing cost through combined testing.

Understand the defect lifecycle from detection to closure, including states such as new, assigned, open, fixed, retest, reopen, and closed, and how duplicates, deferrals, and rejections are handled.

Learn defect parameters used to log defects in tools like Jira, including defect ID, description, source, severity, priority, status, reproduction steps, component, stage of origination, assignment, and attachments.

Explain data warehouse as a subject-oriented, integrated, time-variant, non-volatile store for historical and current data from multiple sources to enable quick reports and strategic decisions.

Discover how business requirements drive data warehouse projects through the mapping document and source-target schema. Learn how ETL mappings define transformations and data movement.

Explore ETL testing in a data warehouse by validating transformations from the EMP table to the target table, including vacation hours over 70 and name concatenation.

Explore the data warehouse and etl testing life cycle, from studying source and target systems to defining a test strategy, planning tests, and executing validations for initial and incremental loads.

Validate metadata by comparing tables to the mapping document using describe table and information_schema queries to verify column names, data types, lengths, and constraints including keys.

Explore data mismatch checks in ETL workflows, validating that transformed source data correctly loads into the data warehouse and data mart, with emphasis on data quality, transformation, and data validation.

Learn hands-on record count validation in data warehouse etl testing: create source and target tables, apply transformations, compare record counts, and log defects when counts mismatch.

Learn data completeness validation with column data profiling by creating logical data sets to compare source and target, focusing on unique values, nulls, and min/max/average statistics, akin to checksums.

Validate data completeness with duplicate checks in ETL. Enforce primary and unique keys, remove duplicates when required, and compare source and target records using identifiers like phone, ssn, or aadhaar.

Perform step by step duplicate validation in the target table by grouping all columns to identify true duplicates, and spot cases where rows seem duplicate but differ in food items.

Learn how ETL testing validates data accuracy, from non-numeric checks like email and phone formats to domain validation of values, ranges, and reference files, ensuring age, gender, and product mappings.

Validate data accuracy in a hands-on ETL workflow by comparing source and target tables, checking email formats and rating ranges, and identifying and correcting data defects.

Analyze data consistency and validation across tables and schemas, ensuring identical customer id values in both customer and payments tables, and flag defects when mismatches occur.

Validate data consistency across warehouse tables by comparing identical columns, such as customer name, using joins on common keys like order ID, and identify mismatches like name variations.

Validate data transformations in ETL pipelines by applying SQL queries to join employee and salary details, aggregate by department, deduplicate names, and compare results to the target table.

Validate data transformation from the mapping document on source data using SQL, then compare transformed results with target tables, via concatenating first and last names into full names.

Learn ETL data validation after loading into the data warehouse by comparing transformed source data with target data. Examine scenarios across file and database sources, different RDBMS, and access permissions.

This lecture presents data validation approaches for scenario two, where source and target are on different Oracle servers, including db links, temp tables, transformation logic, and Excel comparisons.

Validate data across different relational database management systems by loading source data into a temporary target table from a flat file and comparing with the target; alternatively, export to Excel.