ETL_Exercise Excel.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	The questions below are designed to introduce you to the type of work and thinking that is required of ETL developers. You will find from these sample questions that this that the work requires paying attention and having a good attention to detail. It's also a great introduction to common data structures and formats that are used in many other jobs and contexts. Hopefully you find these exercises fun and rewarding!
3
4
5	Question #1	From Data Set 1 to Data Set 2 (below) notice how the Patient_ID has changed to the MRN (Medical Record Number).
6		What do you think has been added to the Patient_ID to form a MRN?
7
8
9
10	Data Set 1
11	Patient_ID	Patient_LN	Patient_MN	Patient_FN	DOB	SSN	Street_Address	City	State	Zip_Code	Phone	Phone Type
12	40923	Johns	Lynn	Janice	1/25/1967	182786543	782 Hancock Street	Clinton	NJ	18809	908-553-9012	Cell
13	34058	Smith	Nicholas	Warren	7/31/1976	209875436	82-10 Queens Blvd., Apt 2G	New York	NY	11120	917-785-8766	Home
14	78643	Alexander	Shaun	Peter	9/30/1980	106705423	56 Humboldt St., Apt. 1C	Brooklyn	NY	11211	212-396-6700	Work
15	98723	Parsons	Michelle	Juanita	2/25/1992	150908765	72 Orion Court	Hempstead	NY	11550	212-598-9866	Work
16	10298	Butts	Rachel	Dionne	1/27/1959	115209742	89 JFK Bldv., Apt 34C	Jersey City	NJ	07399	908-553-9123	Cell
17	67402	Keller	Nicole	Susan	3/18/1964	430198360	9 Jones Ave.	Wycoff	NJ	07481	908-781-7651	Cell
18	87205	Fox	Thomas	John	8/3/2005	309674365	777 Orient Way, Unit 7D	Rutherford	NJ	07070	201-735-7946	Home
19	12944	Jimenez	Lionel	Allejandro	10/10/2003	192786734	321 East 125th St., #8D	New York	NY	10020	646-921-0090	Work
20	55309	O'Kelley	Paul	Mark	7/11/1997	981036455	89 Grand Parkway South., Apt. 67	Staten Island	NY	10301	646-324-6548	Cell
21	21307	Toole	Myrna	Janette	4/19/1942	784012376	239 Millers Circle	Jamaica	NY	11405	929-407-4002	Cell
22
23	Data Set 2
24	MRN	Last_Name	Middle_Initial	First_Name	DOB	SSN	Street_Address_1	Street_Address_2	City	State	Zip+4	Home Phone	Work Phone	Cell Phone
25	J-40923	JOHNS	L	JANICE	25-Jan-67	182-78-6543	782 Hancock Street		Clinton	NJ	18809-0000			9085539012
26	S-34058	SMITH	N	WARREN	31-Jul-76	209-87-5436	82-10 Queens Boulevard	Apt. 2G	New York	NY	11120-0000	9177858766
27	A-78643	ALEXANDER	S	PETER	30-Sep-80	106-70-5423	56 Humboldt Street	Apt. 1C	Brooklyn	NY	11211-0000		2123966700
28	P-98723	PARSONS	M	JUANITA	25-Feb-92	150-90-8765	72 Orion Court		Hempstead	NY	11550-0000		2125989866
29	B-10298	BUTTS	R	DIONNE	27-Jan-59	115-20-9742	89 JFK Boulevard	Apt. 34C	Jersey City	NJ	07399-0000			9085539123
30	K-67402	KELLER	N	SUSAN	18-Mar-64	430-19-8360	9 Jones Avenue		Wycoff	NJ	07481-0000			9087817651
31	F-87205	FOX	T	JOHN	03-Aug-05	309-67-4365	777 Orient Way	Unit 7D	Rutherford	NJ	07070-0000	2017357946
32	J-12944	JIMINEZ	L	ALLEJANDRO	10-Oct-03	192-78-6734	321 East 125th Street	#8D	New York	NY	10020-0000		6469210090
33	O-55309	OKELLEY	P	MARK	11-Jul-97	981-03-6455	89 Grand Parkway South	Apt. 67	Staten Island	NY	10301-0000			9294074002
34	T-21307	TOOLE	M	JANETTE	19-Apr-42	784-01-2376	239 Millers Circle		Jamaica	NY	11405-0000
35
36	Question #2.	Why do you think the format has been changed in the target system (represented by Data Set 2)?
37		A.	Having more characters allows for a greater number of unique values.
38		B.	Using an alpha-numeric format allows for more combinations for Medical Record numbers.
39		C.	The target system used an easy convention so it's easy to derive the new Medical Record number.
40		D.	All of the above.
41
42	Question #3	From Data Set 1 to Data Set 2 (below) notice how the patient's name has been changed.
43		Based on the sample for patient Janice Lynn Johns in Data Set 2, update the name for Warren Nicolas Smith
44		in the space highlighted in yellow.
45
46	Data Set 1
47	Patient_ID	Patient_LN	Patient_MN	Patient_FN
48	40923	Johns	Lynn	Janice
49	34058	Smith	Nicholas	Warren
50
51	Data Set 2
52	MRN	Last_Name	Middle_Initial	First_Name
53	J-40923	JOHNS	L	JANICE
54	34058
55
56	Question #4	Now try it for Peter Shaun Alexander.
57
58	Data Set 1
59	Patient_ID	Patient_LN	Patient_MN	Patient_FN
60	40923	Johns	Lynn	Janice
61	34058	Smith	Nicholas	Warren
62	78643	Alexander	Shaun	Peter
63
64	Data Set 2
65	MRN	Last_Name	Middle_Initial	First_Name
66	J-40923	JOHNS	L	JANICE
67	S-34058	SMITH	N	WARREN
68	78643
69
70	Question #5.	Now try it for the next patient record.
71
72	Data Set 1
73	MRN	Last_Name	Middle_Initial	First_Name
74	J-40923	JOHNS	L	JANICE
75	S-34058	SMITH	N	WARREN
76	A-78643	ALEXANDER	S	PETER
77	P-98723	PARSONS	M	JUANITA
78	B-10298	BUTTS	R	DIONNE
79	K-67402	KELLER	N	SUSAN
80	F-87205	FOX	T	JOHN
81
82	Data Set 2
83	MRN	Last_Name	Middle_Initial	First_Name
84	J-40923	JOHNS	L	JANICE
85	S-34058	SMITH	N	WARREN
86	K-67402
87
88
89	Question #6	From Data Set 1 to Data Set 2 (below) notice how the DOB has been changed.
90		Based on the sample for patient Juanita Michelle Parsons, update the DOB Dionne Rachel Butts
91		in the space highlighted in yellow.
92
93	Data Set 1
94	Patient_ID	Patient_LN	Patient_MN	Patient_FN	DOB
95	98723	Parsons	Michelle	Juanita	2/25/1992
96	10298	Butts	Rachel	Dionne	1/27/1959
97
98	Data Set 2
99	MRN	Last_Name	Middle_Initial	First_Name	DOB
100	P-98723	PARSONS	M	JUANITA	25-Feb-92