10个基本的ETL面试问题 *

虽然名单可能会很长, 在ETL过程中执行了一些基本步骤，每个ETL开发人员都应该立即提到:数据转换, aggregation, deduplication, and filtering.

候选人可能会提到的其他选择包括:

• Data cleaning
• Formatting
• Merging/joining
• 计算新字段
• Sorting
• Pivoting
• 查找操作
• Data validation

暂存是ETL进程中的一个可选的中间存储区域.

“进行还是不进行”的决定可以分为四个主要考虑因素:

1. 审计的目的. 多亏了staging区域，我们能够将原始输入文件与结果进行比较. 这是非常有用的，特别是当源系统覆盖历史记录时.g.例如，FTP服务器上的平面文件每天都会被覆盖.)
2. Recovery needs. 尽管个人电脑的速度越来越快——通常几乎每种形式都有更多的带宽——但仍然有一些遗留系统和环境在提取数据时性能不佳. 从源系统提取数据后立即存储数据是一种很好的做法. 这样，暂存对象就可以充当恢复检查点, 避免流程在完成90%失败时需要完全重新运行的情况.
3. Backup. 在发生故障的情况下，暂存区可用于恢复目标系统中的数据.
4. Load performance. 如果必须尽快将数据加载到系统中，则采用分段方式. 开发人员将数据按原样加载到暂存区域, 然后从那里对它执行各种转换. 这比在将数据加载到目标系统之前实时转换数据要高效得多, 但是这里的代价是更高的磁盘空间使用.

准备增量负载的最常见方法是使用有关添加或修改记录的日期和时间的信息. 它可以在初始加载时进行设计，并在后期进行维护, 或者稍后在基于业务逻辑的ETL流程中添加.

确保在此过程中不修改用于此操作的字段并确保这些字段是可信的，这一点非常重要.

下一步是决定如何捕获更改, 但是底层的基本原理总是相同的:将最后修改的日期与目标中已经存在的最大日期进行比较, 然后取所有较大的记录.

另一种选择是为增量加载准备一个进程, 哪一种方法可以将已有记录与新记录进行比较, 只加载差异. 但这并不是最有效的方法.

第三方工具提供更快、更简单的开发. 多亏了他们的gui, 这些工具也可以由不是技术专家但对业务本身有广泛知识的人使用.

ETL工具能够自动生成元数据，并为大多数源提供预定义的连接器. 最重要的特性之一是能够动态地连接来自多个文件的数据.

最常见的方法是使用批处理:将一个大查询拆分为较小的查询.g. 一批10000行:

声明@id_control INT = 0——当前批处理
       ，@batchSize INT = 10000——批处理的大小
       ，@results INT = 1——批处理后的行数

——如果返回0行，退出循环
WHILE (@results > 0)
BEGIN
    UPDATE [table]
    SET [column] = [value]
    WHERE [PrimaryKey column] > @id_control
      AND [PrimaryKey column] <= @id_control + @batchSize
    ——最近的行计数
    设置@results = @ rowcount
    ——开始下一批
    SET @id_control = @id_control + @batchSize
END

如果桌子太大, 更好的选择可能是创建一个新表, 插入更改后的数据, 然后换桌子.

索引允许快速查找, 但是它们会降低负载性能:索引过多的表将不允许有效的DML操作, i.e. 插入和更新.

值得注意的是，索引占用额外的磁盘空间. 更糟糕的是，数据库后端需要在数据更改时更新所有相关索引. 由于索引碎片，它还会产生额外的开销:开发人员或dba必须负责索引维护, reorganization, and rebuilds.

索引碎片会导致严重的性能问题. 当新数据插入索引时，数据库引擎必须为它找到空间. 新的数据插入可能会打乱当前的顺序——SQL引擎可能会将数据从单个数据页中分离出来, 这会产生过多的空闲空间(内部碎片). 它还可能打乱当前的页面顺序, 这迫使SQL引擎在从磁盘读取数据时在页面之间跳转. 所有这些都会给读取数据的过程带来额外的开销，并迫使随机磁盘I/O.

Microsoft, in particular, 当索引碎片在5%到30%之间时，建议重新组织索引, 当它超过30%的时候重建它. SQL Server中的索引重建会在下面创建另一个索引，然后替换之前的索引. 重建可能会阻止整个表读取它(当使用企业版以外的版本时).索引重组基本上是对叶页进行重新排序，并尝试压缩数据页.

最好先过滤数据，然后再将其与其他源连接起来.

提高ETL进程性能的一个好方法是在进程中尽快去除不需要的数据. 它减少了花费在数据传输和/或I/O和内存处理上的时间.

一般规则是减少处理的行数，并避免转换从未到达目标的数据.

日志记录对于跟踪负载期间的所有更改和故障非常重要. 准备日志记录的最常见方法是使用平面文件或日志记录表. That is, 在这个过程中, counts, timestamps, 添加关于源和目标的元数据，然后将其转储到平面文件或表中.

这样就可以检查负载是否存在无效运行. 当这样的表或文件存在时，下一步就是准备通知. 这可以是一个报告，或者一个简单的格式化电子邮件，描述加载完成后的情况.g. 与前一次负载相比处理的记录的数量.)

在ETL过程中实现, 开发人员将添加事件处理程序(SSIS)或使用变量(如系统变量) @@ROWCOUNT 在Transact-SQL中)跟踪插入、更新和删除的记录.

使用SSIS时，我们还可以使用 SSISDB database:

查询TOP 10000.Object_Name[项目名称] 
       ,REPLACE(e.Package_name， '.dtsx'， ")[包名] 
       ,o.start_time[开始时间] 
       ,o.end_time[结束时间] 
       ,e.message_source_name[消息源名称]  
       ,e.event_name[事件名称]                 
       ,e.subcomponent_name[子组件名称] 
       ,e.message_code[消息码] 
       ,m.message_time[事件时间] 
       ,m.message[错误消息] 
       ,m.message_time[错误日期] 
       ,o.caller_name[呼叫者名称] 
       ,o.Stopped_By_Name[被停过的人] 
       ，ROW_NUMBER() /(按m划分).operation_id ORDER BY m.message_source_type DESC[源类型顺序]
FROM   SSISDB.internal.operations o 
JOIN SSISDB.internal.operation_messages米 
ON   o.Operation_id = m.operation_id 
JOIN SSISDB.internal.event_messages e 
ON   m.Operation_id = e.operation_id 
 AND   m.Operation_message_id = e.event_message_id 
WHERE   o.Object_Name LIKE '%[对象名]%'——数据库
 AND event_name类似'%Error%'
ORDER BY o.end_time DESC
       ,o.Object_Name 
       ，[源类型顺序]ASC

除了平面文件和数据库本身, ETL工具提供本地通知和日志记录特性:例如, 指示当前负载状态的仪表板.

数据分析任务有助于维护数据质量. 在此阶段，将检查并解决许多问题. 最重要的是:

1. 一行的键和唯一标识. 要插入的行必须唯一. 企业通常使用一些自然键来识别给定的行, 但是开发人员必须验证这是否足够.
2. Data types. 应该仔细检查暗示某种类型的列名: 指定的类型是否会改变列的含义，或者可能导致数据丢失? 数据类型也会影响etl后的性能:即使在过程中影响不大, 将文本加载到可变长度字符串列中, on some RDBMSes, 当用户开始查询目标器时，会导致性能下降.
3. 数据之间的关系. 了解表之间的关系是很重要的. 它可能需要额外的建模来连接数据的某些部分，以避免丢失重要的结构信息. 另一件事是理解关系的基数性, 因为它决定了所涉及的表将来将如何连接.

维护行历史记录需要实现版本控制策略. 最流行的三种类型是:

1. 插入新记录: In this case, 存储有关该行的更新信息, 但是它没有链接到任何其他信息——它被视为一个新行. Usually, in this case, 另外还有一列(甚至不止一列)可以方便地标识最近的更改. It could be, for example, “当前记录”标志, “更改原因”文本字段, 或“有效的from/until”对 datetimes (or tsrange, perhaps).
2. 额外的列(年代):此处，已更改列的旧值被移动到附加列(e.g. old_amount)，新值将取代原始值(e.g. amount.)
3. History table:首先创建一个历史表，与主表分开. 然后，我们有多种选择如何将数据加载到这个表中. 其中之一是创建DML触发器. 类似于RDBMS供应商提供的功能 更改数据捕获 功能-在这里很方便. 这些功能可能比触发器更有效, 比如他们直接在事务日志中记录更改, 谁负责保存对数据库所做的任何更改的信息. SQL server(特别是2016年及以后)可以使用 系统版本的时态表. 这个特性在最新的历史表旁边维护一个完整的历史表:主时态表只保留数据的最新版本, 但它与历史表相连, 其中包含所有以前的版本.