*Disclaimer - we do not always have control over standards for adopted systems such as SIS or UCPath
Tool: ErWin
Data Lineage Documentation (source to target): ErWin
ETL: Informatica
SQL Server (lunar/solar)
These standards are up for debate - one shared DB w/multiple schemas or different DBs? Need to discuss with the DB team to make a final decision.
Database: dss
Schema ODS: ods_{subject area}, for UCPath the standards is hcm_ods
Schema for Data Marts: dss_{subject area}
Schema for EDW (conformed dimensions, person, time): edw
Database for staging: staging
Schema for staging: stage_{subject area}
Table Naming Standards:
Dimensions: {subject area}_D_{table name} (KFS), {table_name}_D (UCPath), D_{table name} (EDW)
Facts: {subject area}_F_{table name} (KFS), {table_name}_F (UCPath), F_{table name} (EDW)
ODS Tables: If it is a copy of OLTP system table, leave name the same as the source system. If it is a new table, follow the naming standards of the source system.
ODS Views: {table_name}_V, and also should follow the naming standards of the source system.
...
All tables should have a "CREATED_EW_DTTM" and "UPDATED_EW_DTTM" columns to show when the record was created and the data was last updated. Upon initial creation, these two values will be the same until the first update load.
ETL Columns:
SID columns should be named {name}_SID (e.g. ACCOUNT_SID), datatype BigInt Identity, primary key.
...