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.

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.

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 and how to view and change them using chmod with octal values for owner, group, and others.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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.

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

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.

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 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 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.

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.

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.

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.

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.

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 such as special value testing, error guessing, and cause and effect. Compare application behavior across multiple environments and configurations to ensure consistent results.

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.

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.

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.

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.

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.

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.

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 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.