Create Stage DB

Lesson Purpose

As part of this lesson walks the student through creating the Stage Level 1 tables. The tables will be reverse-engineered into an Entity-Relationship model for review and walk through.

Dan will discuss the purpose of the Stage level 1 tables and that they are intended to be an exact copy of the source system tables or data files which were built during the previous lesson, Create Source Database. During this lesson, you will begin to understand the importance of entity naming conventions like table suffix and table prefix abbreviations. Dan explains what has been used as a best practice in the BASELINE model and why defining your naming conventions is so critical. Additionally, pay attention to the fact that the stage level 1 tables allow NULLS in the table structure.

For VTCP participants, understand that your tool may not create a physical level 1 stage table; however, to pass the BASELINE tests, you must generate the DDL for the level 1 and level 2 staging tables. Dan discusses why the level 1 tables may not be necessary especially when data is arriving in real time. For purposes of the VTCP certification process, you must produce the DDL for these physical tables it is considered a requirement.

Next, Dan discusses the Stage level 2 tables, what the level 2 tables are, and how they are intended to extract the data from the stage level 1 tables into the stage level 2 tables. Pay attention to the fact that Stage level 2 tables include the DV system fields like hash keys, hash differences, record source, and load dates. The naming conventions on these tables are important as part of the repeatable pattern and identity.

After building the physical staging tables, Dan will walk the student through creating the views used to load the stage level 1 tables from the source. Students should pay attention to the naming conventions used for the views. As a standard best practice, Dan discusses why a view should be named according to the source to target nature of the data movement achieved by the view.

BASELINE FILES USED IN THIS LESSON:

• Folder/File: /20_DDL/02_stage_level1.ddl
• Folder/File: /20_DDL/03_stage_level2.ddl
• Folder/File: /30_Views/01_stage1_views.sql
• Folder/File: /50_Insert-Into-Scripts/01_insert_stage1.sql