ETL开发面试题及详细答案（实战版）

ETL开发面试题及详细答案（实战版）本次面试题贴合企业真实ETL开发岗位招聘需求，覆盖基础原理、SQL实操、数据抽取、转换加载、性能优化、报错排查、项目实战核心内容，答案均为一线工作实操总结，贴合面试应答逻辑，无理论化空泛内容。一、基础原理类（入门必问）1、简单说明什么是ETL，核心流程分别是什么？参考答案：ETL是数据仓库、数据中台开发的核心流程，三个字母分别对应抽取、转换、加载，是将分散、杂乱的原始业务数据整理为标准、可用数据的全过程。核心流程：E（Extract抽取）：从各类业务数据源抓取原始数据，常见数据源有MySQL、Oracle、SQLServer业务库，还有日志文件、CSV、Excel、接口数据等，只做数据采集，不做修改。T（Transform转换）：对抽取的原始数据进行清洗和加工，是最核心、工作量最大的环节。主要包括去重、空值处理、字段类型统一、脏数据过滤、字段拆分拼接、维度关联、指标计算、数据脱敏等，把不规范的原始数据转换成统一标准的结构化数据。L（Load加载）：将加工完成的标准数据，批量写入目标存储介质，一般是数据仓库（Hive、Greenplum、ClickHouse）、数据集市、业务分析库等，供后续报表、数据分析、可视化使用。2、ETL和ELT的区别是什么？工作中什么场景用哪种？参考答案：核心区别在于数据转换的时机和位置。ETL：先抽取数据，在独立的调度工具、脚本中完成转换清洗，最后加载到数仓。转换过程在入库前完成，对目标数仓性能压力小，适合传统关系型数仓、数据量偏小、清洗规则复杂的场景。ELT：先把原始数据完整加载到数仓，再利用数仓自身的计算能力做转换清洗。入库只做全量同步，转换后置，流程更简单，运维成本低。实际工作场景：传统数仓、金融、政务等对数据准确性、安全性要求高，脏数据必须前置过滤的场景，用ETL；大数据量、实时同步、Hive、ClickHouse等新型数仓场景，基本都用ELT，提升同步效率。3、数仓分层是什么？每层的作用和数据特点是什么？参考答案：行业通用四层分层架构：ODS、DWD、DWS、ADS，部分公司会新增DIM维度层。1、ODS层（原始数据层）：同步业务系统原始数据，和源库数据结构、内容完全一致，不做任何加工，仅做简单同步。作用是备份原始数据、保留数据快照，方便数据回溯、问题排查。数据特点：数据量大、存在脏数据、数据冗余、格式不统一。2、DWD层（明细数据层）：对ODS层数据做清洗、去重、空值处理、字段标准化、脏数据过滤，保留最细粒度的业务明细数据。作用：统一数据口径，为上层聚合计算提供干净的基础明细数据。3、DWS层（聚合数据层）：基于DWD明细数据，按照业务维度（时间、地区、用户、门店等）做轻度聚合、指标汇总。作用：减少上层计算压力，提升查询速度，是数仓核心中间层。4、ADS层（应用数据层）：面向业务需求、报表、可视化、接口输出最终指标数据，高度聚合，粒度最粗。作用：直接对外提供数据服务，适配业务查询、数据分析需求。5、DIM维度层：存储用户、商品、地区、部门等固定维度信息，供各层关联使用，统一全局维度口径。二、SQL核心实操类（高频必考）1、ETL开发中常用的SQL函数有哪些？分别说下使用场景参考答案：日常开发高频使用的主要分为五类，都是数据清洗、转换、统计的核心函数：1、空值处理函数：IFNULL、COALESCE。场景：统一空值格式，将NULL替换为0、空字符串或默认值，避免聚合计算出错。2、字符串处理函数：SUBSTRING、CONCAT、TRIM、SPLIT。场景：字段拆分、拼接、去除首尾空格、处理不规则字符串数据。3、时间函数：DATE_FORMAT、DATEDIFF、FROM_UNIXTIME、DATE_ADD。场景：时间格式统一、时间差计算、时间维度拆分（年/月/日/周）。4、聚合函数：SUM、COUNT、MAX、MIN、AVG，搭配GROUPBY。场景：DWS层指标聚合、数据统计。5、窗口函数：ROW_NUMBER、RANK、LAG、LEAD。场景：数据去重、取最新一条数据、同比环比计算、排名统计，是ETL数据清洗的高频函数。2、怎么用SQL实现重复数据去重？说说常用两种方案参考答案：工作中根据业务场景主要用两种方式，适配大部分去重需求：1、窗口函数去重（最常用）：根据业务唯一键分组，用ROW_NUMBER()OVER(PARTITIONBY重复字段ORDERBY更新时间DESC)排序，取序号为1的数据，保留最新一条有效数据，删除重复旧数据。适合明细数据、有时间戳的业务表去重。2、DISTINCT去重：直接使用SELECTDISTINCT字段FROM表，剔除完全重复的整条数据。适合无时间戳、全字段重复的简单去重场景，缺点是大数据量下性能较差。3、LEFTJOIN、INNERJOIN、RIGHTJOIN的区别，工作中怎么选用？参考答案：1、INNERJOIN：只返回两张表中匹配成功的数据，匹配失败的数据全部过滤。适用于需要精准关联、过滤无效数据的场景，比如统计已匹配用户的订单数据。2、LEFTJOIN：以左表数据为基准，左表所有数据全部保留，右表匹配不到的字段填充NULL。是ETL开发中使用最多的关联方式，适合主表数据必须完整保留的场景，比如统计所有订单的用户信息，无对应用户的订单也要保留。3、RIGHTJOIN：以右表为基准，和LEFTJOIN相反，日常开发使用极少，基本可以用LEFTJOIN替代。三、ETL开发实操类（核心工作内容）1、全量同步和增量同步的区别？分别适用什么场景？参考答案：1、全量同步：每次同步都将源表所有数据全部抽取、覆盖写入目标表，不管数据是否更新、是否新增。适用场景：数据量小、表数据变动频繁、无更新时间戳、维度表、配置表；或者首次初始化同步所有历史数据。优点是逻辑简单、无数据遗漏，缺点是大数据量同步耗时、资源占用高。2、增量同步：只同步源表新增、修改的数据，通过更新时间、自增ID、日志位点作为增量标识，只抓取变动数据。适用场景：业务大明细表（订单、支付、日志表）、每日定时同步的业务表。优点是同步速度快、资源消耗低，缺点是需要精准的增量标识，规则配置不当容易丢数据。2、ETL数据清洗过程中，主要处理哪些脏数据？参考答案：工作中核心清洗五类脏数据，也是线上最容易出问题的数据：1、空值数据：字段NULL、空字符串、空格数据，统一替换为默认值，避免聚合计算报错、报表展示异常。2、重复数据：业务主键重复、整条数据重复，通过窗口函数、DISTINCT去重，保留有效数据。3、异常数据：数值超出业务范围（比如年龄负数、订单金额为负、时间为未来无效时间），直接过滤或标记为异常数据。4、格式不统一数据：时间格式混乱、字符串大小写不统一、字段长度不一致、编码不一致，统一为全局标准格式。5、无效冗余数据：测试数据、作废数据、业务删除的冗余数据，直接过滤剔除，减少数仓存储压力。3、说说你日常ETL开发的完整工作流程参考答案：我日常开发遵循需求对接、调研、开发、测试、上线、运维的完整流程：1、需求对接：对接数据分析师、业务方，确认指标口径、同步频率、数据粒度、输出形式、落地表结构。2、数据源调研：梳理源库表结构、字段含义、数据更新规则、增量标识、是否存在脏数据、数据量大小。3、脚本开发：根据数仓分层规范，开发ODS层同步脚本、DWD层清洗脚本、DWS层聚合脚本、ADS层指标脚本，配置同步规则（全量/增量）。4、数据校验：自测数据完整性、准确性、一致性，核对字段口径、数据量波动、指标差值，排查脏数据、丢数据、重复数据问题。5、调度配置：在调度工具中配置任务依赖、运行时间、重试机制、超时规则，设置告警策略。6、上线运维：提交上线、监控任务运行状态，日常巡检数据质量，处理任务失败、数据异常、指标波动问题，迭代优化脚本性能。四、性能优化与问题排查类（进阶高频）1、ETL任务运行慢、超时，常用的优化手段有哪些？参考答案：日常工作中从五个维度做优化，效果最明显：1、抽取优化：大表优先使用增量同步，禁止全量重复同步；抽取时只同步需要的字段，禁止SELECT*；根据自增ID、时间戳分片并行抽取，提升抽取速度。2、SQL优化：避免大表全表扫描，合理建立分区、索引；减少多表笛卡尔积关联；大表关联小表优先小表驱动大表；避免子查询嵌套过深；提前过滤无效数据，缩小计算数据量。3、表结构优化：数仓表合理做分区（按天、按月分区）、分桶；字段类型按需设置，避免超大字段占用资源；冷热数据分离，归档历史冷数据。4、调度优化：错开任务高峰，避免多任务同时抢占资源；配置任务并行执行，无依赖的任务拆分并行运行；优化任务依赖关系，减少无效等待。5、资源优化：根据任务数据量调整内存、CPU资源配置；大数据量任务开启批量提交、批量写入。2、ETL任务运行失败，你的排查思路是什么？参考答案：我会按照先看报错、再查数据、最后查资源的思路快速定位问题：1、查看日志：优先查看任务运行日志、脚本报错日志，定位具体报错类型，常见的有SQL语法错误、字段不匹配、数据类型转换失败、空值报错。2、检查数据：排查源数据是否异常，比如源表字段变更、数据为空、脏数据超标、主键冲突、重复数据导致写入失败。3、检查资源与调度：查看服务器CPU、内存、磁盘是否爆满；任务是否超时、依赖任务是否失败、调度时间冲突、网络中断。4、复现验证：手动执行脚本，复现报错问题，修改脚本规则、清洗逻辑，修复后重试，同时补充校验规则，避免重复报错。3、如何保证ETL数据的准确性和完整性？参考答案：通过事前规则、事中校验、事后监控三层保障：1、事前规范：梳理清晰的业务口径，统一字段定义、计算规则；合理设置增量标识，避免丢数据；脚本开发时做好字段校验、脏数据过滤。2、事中校验：数据同步完成后，做数据量校验（源表和目标表数据量对比）、关键字段值校验、指标差值校验；关联字段一致性校验，避免关联丢数。3、事后监控：配置数据质量监控规则，监控空值率、重复率、数据波动、数据延迟；配置任务失败、数据异常告警，及时发现并修复问题；定期做数据回溯和全量校验。五、项目实战与场景问答（面试压轴）1、工作中遇到过最棘手的ETL问题是什么？怎么解决的？参考答案：之前做电商订单数仓项目时，遇到过增量同步丢数据、指标每日波动不一致的问题。问题原因：业务库订单表的更新时间戳存在重复，部分订单多次更新后，增量抓取区间覆盖不全，导致少量更新数据遗漏；同时部分测试脏数据、重复订单导致统计指标偏差。解决方案：1、优化增量抓取规则，不再单一依赖更新时间，结合自增ID+更新时间双维度抓取，避免区间漏数；2、新增数据校验脚本，每日对比源库和数仓的订单总量、核心金额指标，监控数据差异；3、完善清洗规则，过滤测试数据、作废订单，通过窗口函数对当日更新数据去重，保留最新状态数据；4、新增补数机制，针对遗漏数据配置自动补数脚本，定时修复历史数据。最终解决了数据丢失、指标波动问题，保障了报表数据的稳定性。2、数据发生偏差、报表指标对不上，排查思路是什么？参考答案：按照从后往前、层层溯源的方式排查：1、先确认口径：核对业务方指标统计口径、时间范围、筛选条件是否一致，排除人为口径理解错误。2、核查ADS层：检查最终指标脚本的聚合逻辑、筛选条件、分组规则是否出错。3、溯源DWS/DWD层：查看上层依赖的中间层数据是否准确，排查聚合过程是否丢数、错算。4、核对ODS层与源库：对比原始同步数据和业务库数据，确认是否同步丢数、重复数、脏数据未清洗。5、定位问题根源：区分是脚本逻辑问题、数据同步问题、脏数据问题还是口径问题，针对性修复数据、优化脚本。3、如何处理业务源表字段变更的情况？参考答案：业务表增删字段是日常常见场景，我会按规范流程处理，避免任务报错、