資料倉庫工程師面試題目（不定期更新）

1 緩慢變化維的設計？（真心常問，标準答案必備）

三種：直接覆寫，增加新行，增加心屬性列

Type 1:覆寫：直接用新值代替舊值。

Type 2:增加新行。将目前行的狀态設定為off，并設定一個endtime時間戳，将目前時間标記上。

同時新增1行，将其狀态标記為on，設定begintime時間戳為上一個記錄的endtime+1。

Type 3:增加新列：給表增加一個新列，來存儲新值，同時保留原來的值不變。

2 拉連結清單使用場景和實作方式？

【yy總結】

拉連結清單使用場景：需要檢視曆史某一時間節點的狀态，同時考慮到存儲空間。

實作方式：

首先是拉連結清單dw_order_his的設定，有start_date和end_date兩個字段；

其次在ods層建立一個ods_order_update表，儲存當變化資料（包括insert和update的資料）

源表（order）

ods_order_update表和dw_order_his表的交集進行封鍊操作，end_date=current_date

ods_oder_update資料插入到his表中，對于記錄的end_date=9999-12-31,start_date=current_date

【使用場景】

在資料倉庫的資料模型設計過程中，經常會遇到下面這種表的設計：

 有一些表的資料量很大，比如一張使用者表，大約10億條記錄，50個字段，這種表，即使使用ORC壓縮，單張表的存儲也會超過100G，在HDFS使用雙備份或者三備份的話就更大一些。

 表中的部分字段會被update更新操作，如使用者聯系方式，産品的描述資訊，訂單的狀态等等。

 需要檢視某一個時間點或者時間段的曆史快照資訊，比如，檢視某一個訂單在曆史某一個時間點的狀态。

 表中的記錄變化的比例和頻率不是很大，比如，總共有10億的使用者，每天新增和發生變化的有200萬左右，變化的比例占的很小。

【實作方式】

全量主要資料表加載的政策為每次加載時需要根據主鍵将目标表的資料與源表資料進行比對，删除目标表中在源資料表中的相關記錄，然後将源表資料全部插入目标表。表現在腳本上為先delete相關記錄，然後insert所有記錄。主表加載政策主要用于大部分主表的加載，比如客戶資訊等主要資料表。

增量拉鍊是指每次加載時，将源表資料視為增量抽取後的結果，加載到目标表時需要考慮資料曆史情況。一般資料發生變化時關閉舊資料鍊，然後開新資料鍊。增量拉鍊針對的是曆史表情況，由于資料倉庫中記錄了大部分資料曆史表變化情況，是以增量拉鍊加載政策在資料倉庫中是使用比較廣泛的一種加載政策。通常這種曆史表都含有start_date和end_date字段，首先全字段對比源資料和目标表得出真正的增量資料，這裡的全字段不包含start_date和end_date字段，然後根據主鍵對目标表進行關舊鍊操作，然後對新增資料開新鍊，這種拉鍊政策同樣可以處理全量資料。

3 拉連結清單性能優化

拉連結清單當然也會遇到查詢性能的問題，比如說我們存放了5年的拉鍊資料，那麼這張表勢必會比較大，當查詢的時候性能就比較低了，個人認為兩個思路來解決：

1. 在一些查詢引擎中，我們對start_date和end_date做索引，這樣能提高不少性能。
2. 保留部分曆史資料，比如說我們一張表裡面存放全量的拉連結清單資料，然後再對外暴露一張隻提供近3個月資料的拉連結清單。

4 星型模型和雪花模型差別

星形模型(Star Schema):

Ø 事實被次元所包圍，且次元沒有被新的表連接配接

Ø 星形模型是一個比較折中的的模組化方式(BIAPPS中都是用的是星形的模組化方式)

雪花模型(Snowflake Schema):

} 事實表被多個維表或一個或多個層次所包圍

} 雪花模型一般在處理大的且相對靜态的層次的時候使用

根據事實表和次元表的關系，又可将常見的模型分為星型模型和雪花型模型。

　　星形模型：當所有次元表連接配接到事實表上的時候，整個圖就像一個星星，故稱之為星型模型。星型架構是一種非正規化的結構，多元資料集的每一個次元都直接與事實表相連，不存在漸變次元，是以資料有一定備援。因為有備援，是以很多統計不需要做外部的關聯查詢，是以一般情況下效率比雪花模型高。

　　雪花模型：當有多個次元表沒有直接連接配接到事實表上，而是通過其他次元表連接配接到事實表上時，其圖形就像雪花，故稱雪花模型。雪花模型的優點是減少了資料備援，是以一般情況下查詢需要關聯其他表。在備援可接受的前提下使用星型模型。

星型模型和雪花模型的差別在于：次元表是直接連接配接到事實表還是其他次元表。

5 簡述資料倉庫中的表的基本類型，以及為了保證引用完整性該以什麼樣的順序對它們進行加載。

答：資料倉庫中的表的基本類型有次元表、事實表、子次元表、橋接表等幾類。其中子次元表即雪花模型由支架次元技術處理，橋接表用來處理多值次元或層級結構。

資料倉庫中需要加載的各類表之間有互相依賴的關系，是以加載時需要以一定的順序進行加載。下面是一些加載的基本原則：

子次元表加載成功後，再加載次元表。

次元表加載成功後，再加載橋接表。

子次元表、次元表和橋接表都加載成功後，再加載事實表。

這個加載順序可以通過主外鍵的關系來确定。（注意，此回答為總線架構的資料倉庫的表的加載順序。）

6 事實表和次元表的概念及類型

6.1 事實表的概念

Ø 每個資料倉庫都包含一個或者多個事實資料表。

Ø 事實資料表可能包含業務銷售資料，如現金登記事務所産生的資料，事實資料表通常包含大量的行

Ø 一般事實表中隻存放數字或者一些Flag用來統計(Count)，如收益、數量、支出等

6.2 事實的類型

} 粒度事實表(Additive Fact)

} 周期快照事實表(Semi-Additive Fact)

} 聚合快照事實表(Non-Additive Fact)

} 非事實事實表(Factless Fact Table)

粒度事實表(AdditiveFact):

} 表示的是在特定時間、空間點上的一次瞬間的測量。與粒度同層次的事實表，可以直接将事實字段進行Sum,Count等聚合操作

} 在事實表的設計時，一定要注意一個事實表隻能有一個粒度，不能将不同粒度的事實建立在同一張事實表中。

} 交易粒度事實表的來源伴随交易事件成生的資料，例如銷售單。在ETL過程中，以原子粒度直接進行遷移。

周期快照事實表(Semi-AdditiveFact)

} 周期快照事實表表現的是一個時間段，或者規律性的重複。這類表非常适合跟蹤長期的過程，例如銀行賬戶和其他形式的财務報表。最常用的财務上的周期快照事實表通常有一個月粒度。在周期快照事實表中的資料必須符合該粒度（就是說，他們必須量測的是同一個時間段中的活動）。對于一個好的周期快照事實表來說就是在粒度上有更多的事實。

} 在ETL過程中，以固定的時間間隔生成累計資料。

聚合快照事實表(Non-AdditiveFact):

} 聚合快照事實表用于描述那些有明确開始和結束的過程，例如合同履行，保單受理以及常見的工作流。聚合快照不适合長期連續的處理，如跟蹤銀行賬戶或者描述連續的生産制造過程，如造紙。

} 聚合快照事實表的粒度是一個實體從其建立到目前狀态的完整的曆史。

} 在ETL過程中，随着業務處理過程的步驟逐漸完善該表中的記錄。

非事實事實表(FactlessFact Table):

} 每個事實表的粒度是一個事件量測。用來描述資料或事件。事件可以發生，但是沒有具體的測量值。

6.3 次元表

Ø 次元表可以看作是使用者來分析資料的視窗，

Ø 次元表中包含事實資料表中事實記錄的特性，有些特性提供描述性資訊，有些特性指定如何彙總事實資料表資料，以便為分析者提供有用的資訊，

Ø 次元表包含幫助彙總資料的特性的層次結構。

6.4 次元分類

次元的類型:

} 緩慢變化維(Slowly Changing Dimension)

} 快速變化維(Rapidly Changing Dimension)

} 大維(Huge Dimension)和迷你維(Mini-Dimension)

} 退化維(Degenerate Dimension)

緩慢變化維(SCD):

} 大多數的次元的内容都會有不同程度的改變。比如：

} 雇員的升職

} 客戶更改了他的名稱或位址

} 我們如何去處理這些次元中的變化呢？

} 下面提供了三個處理緩慢變化維的方式

} 直接更新到原先記錄中

} 标記記錄有效時間的開始日期和結束日期，加入版本控制

} 在記錄中添加一個字段來記錄曆史

快速變化維(FCD):

} 當某個次元的變化是非常快的時候，我們認定他為快速變化維(具體要看實際的變化頻率)，比如：

} 産品的價格，地産的價格等

} 例如在一個分析使用者如何使用搜尋引擎的DW項目中，将使用者搜尋的關鍵字作為一個次元。由于使用者使用的關鍵字會快速變化，是以關鍵字次元中的資料量會迅速增加。

} 另外一個例子就是精度為秒的時間次元，每秒就會增加一個次元值

通常RCD的處理可以分為3類：

Rapidly Changing Small Dimensions – 即次元表字段并不多，表的資料量也不大的情況。這種情形應用SCD中的Type2就可以了。(即：新增一行，舊行置過期)

Rapidly Changing Large Dimensions – 即次元表字段較多，表的資料量較大的情況。這種情形Type2會增加過多的行并導緻效率降低，是以通常采用Type3.（新增列：僅儲存上一個值previous_value,current_value）

Rapidly Changing Monster Dimensions – 最糟糕的情況，即次元表字段較多，表的資料量很大，且次元表中的一部分字段頻繁變化的情況。此時應将相對穩定的字段和頻繁變化的字段分割開，頻繁變化的字段獨立出來形成新的次元表與事實表相連或形成新的雪花關系。（維表分離）

大次元(HugeDimension):

} 資料倉庫中最有意思的次元是一些非常大的次元，比如客戶，産品等等。一個大的企業客戶次元往往有上百萬記錄，每條記錄又有上百個字段。而大的個人客戶次元則會超過千萬條記錄，這些個人客戶次元有時也會有十多個字段，但大多數時候比較少見的次元也隻有不多的幾個屬性。

} 大次元需要特殊的處理。由于資料量大，很多涉及大次元資料倉庫功能可能會很慢，效率很低。你需要采用高效率的設計方法、選擇正确的索引、或者采用其它優化技術來處理以下問題，包括：

向大次元表填充資料

非限制次元的浏覽性能，尤其是那些屬性較少的次元

多限制的次元屬性值的浏覽時間

涉及大次元表的對事實表查詢的低效率問題

為處理第二類修改所需要增加的額外的記錄

迷你維(MiniDimension):

} 将常用的大次元中的少數字段提取出來，形成一個字段少的次元，在查詢的時候便可以使用迷你維中的字段

} 這樣的設計明顯提高查詢效率

普通維:

普通維是基于一個維表的次元，由維表中的不同列來表示次元中的不同級别。

雪花維：

雪花維是基于多個維表的次元，各個維表間以外鍵關聯，分别存儲在同一次元中不同級别的成員列值。

父子維：

父子維是基于兩個維表列的次元，由維表中的兩列來共同定義各個成員的隸屬關系，一列稱為成員鍵列，辨別每個成員，另一列稱為父鍵列，辨別每個成員的父代。

父子次元通俗的話來講，這個表是自反 的，即外鍵本身就是引用的主鍵；類似這樣的關系，如公司組織結構，分公司是總公司的一部分，部門是分公司的一部分，當然如果定義得好的話員工是部門的一部 分；通常公司的組織架構并非處在等層次上的，例如總公司下面的部門看起來就和分公司是一樣的層次。是以父子維的層次通常不固定的。

因為父子維的複雜的自引用關系，如果按照緩慢次元的全曆史記錄方式來處理，必然導緻邏輯關系混亂，處理起來比較棘手；任何一個組織的變動 (修改名稱，更改引用，新增等等操作 )将會引起其下屬節點相應的變動；任何一個意外都會導緻整個結構的變化，同時發生意外後所帶來的邏輯關系很難理順。而 SQLServer2000中 Analysis Service對于這種急劇的變化處理并不穩定。

是以建議按照緩慢變化維的覆寫方式解決，即隻根據主鍵這個唯一标志進行判斷是否是新增還是修改。

索引：

與在其他關系資料庫中一樣，索引對資料倉庫的性能具有重要作用。每個次元表都必須在主鍵上建立索引。在其他列如辨別層次結構級别的列上，索引對于某些專用查詢的性能也很遊泳。事實資料表必須在由次元表外鍵構成的元件主鍵上建立索引。

粒度(Grain) 層次(Hierarchy)：

Ø 粒度是指資料倉庫的資料機關中儲存資料的細化或綜合程度的級别。細化程度越高，粒度級就越小；相反，細化程度越低，粒度級就越大。設計粒度是設計資料倉庫中的一個重要的前提

Ø 層次指描述明細資料的層次

一些影響次元模組化的因素:

Ø 資料或展現的安全性

Ø 複雜的查詢和分析

畢業4年，從應屆生到BI資料分析師老油條，不定期将過去自己求職積累經驗和資料分析學習相關的一些筆記分享給大家，對網際網路資料分析、機器學習有興趣的朋友也可以關注我的工重号：python資料分析和機器學習，專注BI、資料分析和機器學習的學習和實踐 .