Version Change Description
1.00 First version of data mapping
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Changed By Changed Date
Vivien Franchesca De Guzman 9-Jun-22
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Reports Field Availability Database
(Column Name in the (Y/N) (Source
default Database)
Report/Extract)
default
default
default
default
default
default
default
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Entity Attribute
(Source Table) (Source Field)
student_id first_name
student_id last_name
student_id age
student_id city_location
student_id total_units
student_id year_level
student_id enrollment_date
student_id section
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Logic Last Updated By
(Transformation
Direct Pull Rules, ex. Direct Pull, Case when, etc.) Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
Direct Pull Vivien Franchesca De Guzman
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Last Updated Date
9-Jun-22
10-Jun-22
11-Jun-22
12-Jun-22
13-Jun-22
14-Jun-22
15-Jun-22
16-Jun-22
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Report Name Granularity
first_name_last_name
COUNT(city_location)
COUNT(city_location)
student_id_section
city_location
total_units_enrollment_date
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Description
the key is to identify names with O & S
the key is to identify number of people living in Antipolo
the key is to identify number of people living in Bulacan
the key is to identify student's sections
the key is to identify student's city or location
the key is to identify students enrolled in the years 2020 and
2021 with the academic units below 20
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Filter Last Updated By
WHERE first_name LIKE 'O%' OR last_name LIKE 'S%' Vivien Franchesca De Guzman
WHERE city_location = 'Antipolo' Vivien Franchesca De Guzman
WHERE city_location = 'Bulacan' Vivien Franchesca De Guzman
GROUP BY section Vivien Franchesca De Guzman
GROUP BY city_location Vivien Franchesca De Guzman
WHERE total_units < 20 AND enrollment_date BETWEEN 2020 AND 2021 Vivien Franchesca De Guzman
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Last Updated Date
9-Jun-22
10-Jun-22
11-Jun-22
12-Jun-22
13-Jun-22
14-Jun-22
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Driving Table Relational Table
students students_information
students students_information
student_information students
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Last Updated By
Join Type Join Condition
FROM students
LEFT JOIN student_information
ON students.student_id =
LEFT JOIN student_information.student_id Vivien Franchesca De Guzman
FROM students
LEFT JOIN student_information
ON student.student_id =
student_information.student_id
LEFT JOIN Order by student_information.total_units DESC Vivien Franchesca De Guzman
FROM students LEFT JOIN student_information
ON student_information.student_id =
students.student_id
WHERE total_units < 20 AND enrollment_date
LEFT JOIN BETWEEN 2020 AND 2021 Vivien Franchesca De Guzman
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Last Updated Date
9-Jun-22
10-Jun-22
11-Jun-22
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
Table Name Column Name Data Type Description Validity
student student_id INT A unique customer identifier. Only number characters are accepted
student year_level INT Identifies year level of recipient
student enrollment_date STRING Determines date of enrollment
student section STRING Determines section of students
student_information student_id INT A unique customer identifier.
student_information first_name STRING First name of the students
student_information last_name STRING Last name of the students
student_information age INT Age age of the students
student_information city_location STRING City location of the students
student_information total_units BIGINT Total units taken by the students
Completeness Last Updated By Last Updated
(Mandatory/Nullable) Date
Mandatory Vivien Franchesca De Guzman 9-Jun-22
Vivien Franchesca De Guzman 10-Jun-22
Vivien Franchesca De Guzman 11-Jun-22
Vivien Franchesca De Guzman 12-Jun-22
Vivien Franchesca De Guzman 13-Jun-22
Vivien Franchesca De Guzman 14-Jun-22
Vivien Franchesca De Guzman 15-Jun-22
Vivien Franchesca De Guzman 16-Jun-22
Vivien Franchesca De Guzman 17-Jun-22
Vivien Franchesca De Guzman 18-Jun-22
Vivien Franchesca De Guzman 19-Jun-22
Create Table Script
CREATE TABLE students(
student_id INT,
year_level INT,
enrollment_date TIMESTAMP,
section STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
;
999999CONFIDENTIAL - PROPRIETARY Unauthorized disclosure internally or externally is prohibited
