Database Naming Convention and Data Warehouse Design Principles

# Database Naming Convention and Data Warehouse Design Principles - hackmd version: https://hackmd.io/1eeNAS1oQuSvMA0q6y_QuA?view - gist version: https://gist.github.com/bluet/23e7697b86144561c4a3d804903d059d [TOC] ## ETL: From Data Lake to Data Warehouse 1. Extract 部份：取出要的資料、去雜訊、資料標準化、parsing... 2. Transform：aggregation、mapping 、combined、Change Data Types 3. Load： to dimensional model. ### Example of Data Lake schema ![](https://i.imgur.com/SKulloM.png) ### Build One Truth by merging multiple data sources ![](https://i.imgur.com/EsYdwj4.png) ### De-normalize, and identify types and meanings of columns and values ![](https://i.imgur.com/LoyCpXa.png) ### Cleanup ![](https://i.imgur.com/1thxOzW.png) ### Single Source of Truth ![](https://i.imgur.com/zTFFGn8.png) ### Example Result Table | id | name | display_name | email | location | access_level | status | | -------- | -------- | -------- | --- | --- | --- | --- | | 1 | Matthew Lien | BlueT | bluet@bluet.org | Taichung | owner | active | ### Data Pipeline Data Source -ETL-> Data Warehouse -ETL-> Data Mart ![](https://i.imgur.com/j7RX0e5.png) ## Data Warehouse Schema Model and Design ### De-normalized * In a Data Lake, the schema reflects in transactional logic of an application and follows best practices (such as a 3rd normal form) so that updating values will not produce errors. But this type of schema can be difficult to navigate and many tables will never be used in an analysis. In the past, books recommended using dimensional modeling to reduce the schema complexity, make it easier to run queries, and enhance performance. Today, due to advances in BI tools such as Chartio and Data Warehouse technologies, **dimensional modeling is no longer worth the effort**. ### Dimensional Modeling But, if required, for analytical processing without BI tools, two major types of dimensional modeling are used commonly. But remember that dimensional modeling should be designed from **business aspect**, instead of technical point of view by engineers. #### Star Model Star schema model are deliberatly denormalized to speed up the process. ![](https://i.imgur.com/6CNy6dj.png) - Characteristics of Star Schema - Dimensional Tables are **not normalized**. - The dimension table should contain the set of attributes. - Dimension table is joined to only Fact tables. They are not joined to each other. - Fact table stores keys from dimension tables and measure. - The Star schema is easy to understand and provides optimal disk usage. * Pros - Star schema model are deliberatly denormalized to speed up the process. * Simple queries: Join conditions are simple joins in this schema * Simplified business logics: This model simplifies common reporting business logics * Performance: This model provides performance enhancements for reporting applications * Fast aggregation * Feeding cubes: This model is generally used by OLAP systems to build cubes. Building cube is very fast process * Cons * The main disadvantages is, that data integrity is not enforced as in OLTP databases * This is not flexible inters of analytical application as normalized databases. * This model don’t support many to many relationship between business entities. These types of relationships are simplified in star schema #### Snowflake Model Snowflake model is in same family as the star schema. In fact, it is a special case of star schema. ![](https://i.imgur.com/Wsp3aWN.png) * Pros * Some OLAP multidimensional model tools are optimized to use snowflake schema * Normalizing table saves the storage * Improvement in query performance due to minimized disk storage and joining smaller lookup tables * Cons * Additional maintenance efforts needed due to the increase number of lookup tables * SQL queries would have more joins in order to fetch the records * Data loads into the snowflake model must be highly controlled and managed to avoid update and insert anomalies ## Style guide When going through and recreating the schema with views of the relevant tables you should also clean up what’s in each table. Exclude irrelevant columns and rename any columns that are confusing. Naming conventions help people analyze data consistently because they clarify what each column is and how it can be used. ### Simplify It’s quite common for raw data to be extremely complex. Data was typically meant to be consumed by applications and not directly by business users. By taking some time to simplify data, we can greatly improve business user success when querying. #### Only include fields with obvious analytical purpose It's best to start modeling with only the most relevant columns, excluding any columns that has no immediate or obvious analytical purpose. #### Extract relevant data from complex data types Application data sources may contain JSON, arrays, hstore and other complex data types. These are typically hard to query from business intelligence tooling and should have relevant data extracted into new columns. Example: Supposed a table books contains an id column and the following JSON column. ``` { title: "Moby Dick", author: "Herman Melville", genres: \["novel", "fiction"\] } ``` The resulting modeled books table would contain an id, title, and author columns. Genres could be modeled as an independent table, reduced to a single genre based on custom rules, or some other method. #### Change flags and cryptic abbreviations to meaningful values It's common for application databases to have flags or cryptic abbreviations in columns that work well for the application and terrible for a business user. It's important to transform these values into easy, human readable values. Some examples: - Boolean values 0 and 1 should be transformed to relevant strings, such as true and false or on and off. - Flag values should be transformed into relevant strings. If a column billing_status has three n_meric values (i.e. 0, 1, 2) that represent some status, they should be transformed into a relevant business concept such as Active, Not Active, Delinquent. - Cryptic values should also be transformed into easy to understand business concepts. #### De-normalize where possible Applications typically have highly normalized tables to prevent duplicates, reduce space, and make modification easier. This typically makes it harder for business users to browser the schema however because the complexity of the joins may be hard to follow. Build wider tables where appropriate, collapsing common concepts into a single table. Some examples could be: - Combine the sources, sources_redshift, sources_postgres, and sources_myself tables into a single sources table with the lowest common denominator of values that make sense for a business user. - Combine the users and addresses tables into a single users table since addresses are meaningless on their own. This simplification requires trial and error and you may not always get it r_ight. ### Cleaning Data is messy and requires some cleaning to ensure accurate results. Cleaning prevents common problems that might cause a query to produce incorrect results. #### Attempt to eliminate NULLs NULL values have unexpected consequences in SQL (is "string" <> NULL?). It's best to replace all nulls with values. Some examples: - Change all NULL values in the first_name column to the string Blank. - Change all NULL values in the last_login_type column to the string Never Logged In for customers that have never logged in. #### Fix common data inconsistencies Bad data always makes its way into raw data sources. Whether it is misspellings or just junk data, it is important to clean up the data as much as possible. Some examples: - State names that have a mix of abbreviations, full names, and junk data should be transformed into a single, consistent format such as the full state name. - Phone numbers might be garbage text entered by users to avoid getting phone calls. #### Follow Naming Conventions Schemas, tables, and columns should all be named in accordance with naming conventions listed below. At a minimum, names should be human readable and be representative of the data type and values stored. #### Remove irrelevant data Rows that are irrelevant for various reasons should be removed entirely from the data set. Some examples could be: - Employee testing - Fraud or spam - Inactive Obviously, if analysis is being done on fraud or spam, that data should not be removed but in most causes, if a row would always be excluded from a query, go ahead and remove it in modeling. #### Change Data Types Modeling is a great time to change data types to more appropriate types. Unix timestamps could be converted from int columns to datetime for example. ## Naming Conventions Initially there will be a variety of naming conventions used for tables, columns, and values. Creating a standard for all of these makes it easier for others to find and understand the data they are looking for. - names should be in American English ### Table Names #### Plural A table of Leads should be titled "Leads" not Lead. When there are more than two words on the last needs to be pluralized: opportunity_histories | Leads | | | | -------- | -------- | -------- | | Column 1 | Column 2 | Column 3 | | opportunity_histories | | | | -------- | -------- | -------- | | Column 1 | Column 2 | Column 3 | | Opportunity_Histories | | | | -------- | -------- | -------- | | Column 1 | Column 2 | Column 3 | #### Prefix and Suffix Fact Table or OLAP-ized tables should not have prefix. The advantage of this method is, given most database tools sort tables alphabetically, all tables, which are related to a business object, will be grouped together. Some exceptions (including but not limited to): - `[tablename]_dim`: dimension table - `ref_[tablename]`: reference table e.g. 國定假日列表 - `tmp_[tablename]`: temporary table pipeline 中間產生的中繼表格，如果有跨不同 DB 的操作可能會出現 e.g. from postgres to bigquery (bigquery 上面可能就會有些中繼表格) - `test_[tablename]`: testing table 測試用表格，通常應該要設 expired date 或是會有 script 定期清掉 test table ### Field / Column Names #### id as primary key A simple numeric primary key labeled id should be standard for all tables. | id (PK) | Column 2 | Column 3 | | -------- | -------- | -------- | | 1234 (autoincrement or manually assigned) | Text | Text | #### Name abbreviation and acronyms For well known name abbreviations among software developers, such as config and spec, the abbreviations should be used instead of the full spelling. This will make the source code easy to read and write. In formal documentations, the full spelling should be used. Examples: - config (configuration) - id (identifier) - spec (specification) - stats (statistics) Some systems enforce character limit on object names, e.g. Oracle 12.1 and below only allows for a maximum object name length of 30 bytes. Therefore, abbreviations and acronyms may be taken into consideration during the object naming process, despite the fact that they can often lead to misinterpretation. To combat this, it is suggested to compile a document containing a list of the abbreviations being used with detailed description of their meanings. However, to limit any possible confusion avoid excessive use of abbreviations and acronyms. In logical models, it is advisable, that object names are as self-explanatory as possible, i.e. most words should be fully spelled out, except common abbreviations for longer words such as “dept” for “department” or “org” for “organisation”. However, abbreviations and acronyms are typically used in physical models, to keep object names short. #### foreign keys follow \[tablename\]\_\[id\] format ForeignKeys should follow this format to make it very clear on where the table is linking to. If there are two foreign keys to the same table you can preopend a name to them following the format: * \[uniquename\]\_\[tablename\]\_\[id\]. An accounts table linking to a users table with both a billing contact and a main owner would look like this: | Users | | | | | -------- | -------- | -------- | -------- | | id | fullname | address | phone_home | | Bills | | | -------- | -------- | | id | account_id (FK) | | Accounts | | | | -------- | -------- | -------- | | id | owner_user_id (FK) | billing_contact_user_id (FK) | #### Start columns with a _ if they are needed but should be hidden for Visual mode. If there are columns you need in the model for joining or other purposes but don’t want visible by default in visual mode you can prefix them. They will otherwise be treated just as any other column. Let’s say you didn’t think the foreign keys in the accounts table above needed to be shown in Visual mode. You can simply prefix them as shown below. The relationships will still be detected. It’s a best practice not to show the foreign keys visually. | Users | | | | | -------- | -------- | -------- | -------- | | id | fullname | address | phone_home | | Accounts | | | | | -------- | -------- | -------- | -------- | | id | name | \_owner_user_id (FK) | \_billing_contact_user_id (FK) | This should not be used for columns you're on the fence about needing. Those just shouldn't be included. These are for columns that are needed for querying purposes but have no use in a Visual setting - primarily foreign keys. #### Lower case, underscored naming These names will be mapped to the native naming convention in generated code for each programming language. Our data model needs to be easily editable in SQL mode so we should follow conventions that make editing raw SQL easier. Therefore, we should attempt to have column names like id, first_name, last_name, and last_login_type instead of more human readable forms in the model. Chartio will handle that conversion. | IWonderIfYouReallyLikeThis | this-has-comatilibity-issue | this_looks_cleaner | | -------------------------- | --------------------------- | ------------------ | | Value | Value | Value | Field names should not include prepositions (e.g. "for", "during", "at"), for example: - reason_for_error should instead be error_reason - cpu_usage_at_time_of_failure should instead be failure_time_cpu_usage | reason_for_error | error_reason | cpu_usage_at_time_of_failure | failure_time_cpu_usage | | -------- | -------- | -------- | -------- | | X | Good | X | Good | Field names should not use postpositive adjectives (modifiers placed after the noun), for example: - items_collected should instead be collected_items - objects_imported should instead be imported_objects | items_collected | collected_items | objects_imported | imported_objects | | -------- | -------- | -------- | -------- | | X | Good | X | Good | #### Plural field names A filed which has one-to-many mapping, must use proper plural forms. This matches the convention and the common expectation of external developers. | pending_salary | pending_salaries | | -------- | -------- | | only 1 unpaid salary? | OK now I get it, there are more than one | #### Quantities Quantities represented by an integer type must include the unit of measurement. `xxx_{bytes|width_pixels|meters}` If the quantity is a number of items, then the field should have the suffix `_count`, for example `node_count`. #### Time and Duration To represent a point in time independent of any time zone or calendar, **TIMESTAMP** (UTC) should be used, and the field name should end with time, such as `start_time` and `end_time`. If the time refers to an activity, the field name should have the form of `verb_time`, such as `create_time`, `update_time`. Avoid using past tense for the verb, such as created_time or last_updated_time. - Always use UNIX Timestamp (UTC) - `[verb]_time` To represent a span of time between two points in time independent of any calendar and concepts like "day" or "month", `_duration` should be used. * `_duration` If you have to represent time-related fields using an integer type for legacy or compatibility reasons, including wall-clock time, duration, delay and latency, the field names must have the following form: - `xxx_{time|duration|delay|latency}_{seconds|millis|micros|nanos}` ```sql mysql> CREATE TABLE ts ( -> id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -> takeoff_time TIMESTAMP NOT NULL, -> flight_duration INT NOT NULL, -> receive_time_millis INT NOT NULL -> ) AUTO_INCREMENT = 1; ``` **If and only if** you **have to** represent timestamp using string type for **legacy** or compatibility reasons, the field names should not include any unit suffix. The string representation should use RFC 3339 format, e.g. "2014-07-30T10:43:17Z". Or use INT to store Epoch. > MySQL **converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval**. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. As of MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table. The offset is appended to the date part of a datetime literal, with no intravening spaces, and uses the same format used for setting the time_zone system variable, with the following exceptions: - For hour values less than than 10, a leading zero is required. - The value '-00:00' is rejected. - Time zone names such as 'EET' and 'Asia/Shanghai' cannot be used; 'SYSTEM' also cannot be used in this context. ```sql SET @@time_zone = 'SYSTEM'; INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'), ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00'); SET @@time_zone = '+00:00'; INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'), ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00'); ``` #### Time with more precisions MySQL has Fractional Seconds support for TIMESTAMP values, with up to microseconds (6 digits) precision: ```sql SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0; ``` ``` -> '2003-08-14 18:08:04', 20030814180804.000000 ``` ```sql CREATE TABLE t1 (ts TIMESTAMP(6); ``` Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding. Consider a table created and populated as follows: ```sql CREATE TABLE fractest(c2 DATETIME(2), c3 TIMESTAMP(2) ); INSERT INTO fractest VALUES (2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777'); ``` The temporal values are inserted into the table with rounding: ```sql SELECT * FROM fractest; ``` ``` +-------------+------------------------+------------------------+ | c1 | c2 | c3 | +-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | +-------------+------------------------+------------------------+ ``` #### Date and Time of Day For dates that are independent of time zone and time of day, DATE() should be used and it should have the suffix `_date`. If a date must be represented as a string, it should be in the ISO 8601 date format YYYY-MM-DD, e.g. 2014-07-30. For times of day that are independent of time zone and date, TIME() should be used and should have the suffix `_time`. If a time of day must be represented as a string, it should be in the ISO 8601 24-hour time format HH:MM:SS[.FFF], e.g. 14:55:01.672. | opening_date | opening_time | | ---- | ---- | | 2014-07-30 | 14:55:01.672 | > 通常只接受 timestamp 轉 unix timestamp 而不接受 datetime 轉 timestamp，因為 Datetime 沒時區轉了會爆炸 XDDDDD ## References - KYC Right DB table list with desc https://docs.google.com/spreadsheets/d/1ZnFJCrOtJnpdyS5vmmvbn8WP-cq1_7RA2QOiupxPiHg/edit#gid=0 - Naming Conventions | Cloud API | Google Cloud https://cloud.google.com/apis/design/naming_convention - styleguide | Style guides for Google-originated open-source projects http://google.github.io/styleguide/pyguide.html#s3.16-naming - GNU Coding Standards https://www.gnu.org/prep/standards/standards.html#Names - What to consider for naming conventions in Data Warehousing – Scalefree Blog https://blog.scalefree.com/2019/05/07/naming-conventions/ - Data Warehouse Naming Conventions – Eckhard Zemp https://www.zemp.ch/2019/08/10/data-warehouse-naming-conventions/ - Oracle Business Analytics Warehouse Naming Conventions - 11g Release 1 (11.1.1) https://docs.oracle.com/cd/E25054_01/fusionapps.1111/e16816/namingconv.htm - Naming Conventions [DWH Wiki] http://en.dwhwiki.info/templates/process/naming_conventions - Recipes 8 of Data Warehouse and Business Intelligence - Naming conven… https://www.slideshare.net/mobile/jackbim/recipes-8-the-naming-convention-part-2 - Five principles that will keep your data warehouse organized https://blog.getdbt.com/five-principles-that-will-keep-your-data-warehouse-organized/ - corp/dbt_coding_conventions.md at master · fishtown-analytics/corp https://github.com/fishtown-analytics/corp/blob/master/dbt_coding_conventions.md#naming-and-field-conventions - Star-schema naming conventions - Stack Overflow https://stackoverflow.com/questions/1716220/star-schema-naming-conventions - Dimension Tables and Columns - Fighting Bad Data Modeling http://kejser.org/database-naming-conventions/data-warehouse-naming-conventions/naming-dimension-tables-column/ - Data Warehouse Architecture Principles https://dataschool.com/data-governance/single-source-of-truth/ - https://learndatamodeling.com/blog/dimensional-data-modeling/ - https://www.guru99.com/dimensional-model-data-warehouse.html - https://dwgeek.com/guide-dimensional-modeling.html/ - https://dwgeek.com/star-schema-model-data-darehouse.html/ - https://kknews.cc/zh-tw/tech/p4oz26z.html - https://dwgeek.com/snowflake-schema-model-data-warehouse.html/ - https://dwgeek.com/data-warehouse-fact-constellation-schema-design.html/ - https://dwgeek.com/slowly-changing-dimensions-scd.html/ - https://dwgeek.com/rapidly-changing-dimension-data-warehouse.html/ - https://www.softwaretestinghelp.com/dimensional-data-model-in-data-warehouse/#Example_Of_Dimensional_Data_Modeling - https://towardsdatascience.com/data-warehouse-dimensional-modelling-use-case-study-ewallet-d9d16f559181 - https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html - https://dev.mysql.com/doc/refman/8.0/en/datetime.html - https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html - https://www.guru99.com/etl-extract-load-process.html#5 - https://www.stitchdata.com/etldatabase/etl-transform/ - https://panoply.io/data-warehouse-guide/data-mart-vs-data-warehouse/

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.