DEA-C02 练习题 — DEA-C02：高级数据工程师

本题考察Snowflake查询性能调优的核心实践，题目中未给出查询逻辑存在冗余、语法缺陷等相关提示，默认慢查询是由计算量、数据扫描量过大导致资源不足引发。根据SnowPro Advanced Data Engineer认证要求的核心知识，针对此类单查询过慢的场景，最直接有效的措施是提升运行该查询的虚拟仓库规模，虚拟仓库的规模直接对应分配给查询的CPU、内存、IO资源，规模每提升一级资源翻倍，可大幅提升大查询的并行处理能力，缩短执行时间，符合题干需求。 各选项分析： A. 添加更多虚拟仓库是错误的。Snowflake中每个查询只能运行在单个虚拟仓库上，新增虚拟仓库的作用是隔离不同工作负载、提升整体并发处理能力，无法为当前的单个慢查询提供更多资源，因此不能提升该查询的性能。 B. 增加虚拟仓库的规模是正确的。Snowflake虚拟仓库采用T-shirt型规格定义，更高规格的仓库拥有更多的计算节点和资源，针对扫描量大、计算复杂的慢查询，可并行处理更多数据，有效缩短执行时间，是Snowflake官方推荐的单慢查询调优核心手段之一。 C. 使用公共表表达式(CTE)重写查询是错误的。Snowflake的查询优化器会对CTE和等效的子查询、临时表逻辑做等同优化，除非原查询存在大量重复计算的冗余逻辑，而题目未给出查询逻辑存在此类问题的提示，因此该措施不具备针对性，无法解决题干中的慢查询问题。 D. 改变连接的顺序，先从较小的表开始是错误的。Snowflake内置基于代价的优化器(CBO)，会自动收集表的统计信息，自动选择最优的连接顺序和连接算法，手动调整连接顺序不仅无法保证提升性能，反而可能干扰优化器的判断，不符合Snowflake的性能调优最佳实践。 关键知识点： 1. Snowflake虚拟仓库规格特性：虚拟仓库的T-shirt规格直接决定其可用计算、内存、IO资源，每提升一级规格资源翻倍，更高规格的仓库可显著提升单条大查询的执行效率。 2. Snowflake虚拟仓库隔离性：不同虚拟仓库之间资源完全隔离，新增虚拟仓库仅能提升整体工作负载的并发处理能力，无法加速单个查询的执行速度。 3. Snowflake查询优化器能力：Snowflake内置基于代价的优化器，会自动根据表元数据、统计信息生成最优执行计划，无需用户手动调整连接顺序等查询语法层面的逻辑。 参考资料： Virtual Warehouse Sizes, https://docs.snowflake.com/en/user-guide/warehouses-overview#warehouse-sizes Query Performance Tuning

本题核心要求有两点，一是将结构化的关系行列数据转换为半结构化数据，二是运营开销最小，即尽量减少不必要的计算步骤、避免额外的序列化与反序列化操作。Snowflake中半结构化数据的原生类型为VARIANT、OBJECT、ARRAY，其中OBJECT是存储键值对的半结构化对象，与关系行的结构天然对应。OBJECT_CONSTRUCT函数可以直接接收关系列的字段名和字段值作为入参，直接生成原生的Snowflake OBJECT类型半结构化数据，整个过程不需要经过字符串序列化、再解析的额外步骤，计算开销最低，完全满足题目要求，是最优方案。 各选项分析： A. TO_JSON函数的作用是将输入数据序列化为JSON格式的字符串，输出为字符串类型而非原生半结构化类型，后续如果需要对半结构化数据进行字段查询等操作，还需要额外执行JSON解析步骤，增加了不必要的运营开销，不符合要求，因此错误。 B. PARSE_JSON函数的作用是将已有的JSON格式字符串解析为VARIANT类型的半结构化数据，其输入要求是JSON字符串，而题目输入是关系型数据，使用该方案需要先将关系数据序列化为JSON字符串再解析，多了两步额外操作，运营开销远高于直接生成半结构化对象的方案，因此错误。 C. OBJECT_CONSTRUCT函数支持直接传入多组键值参数，通常键为关系列名，值为关系列的值，直接生成Snowflake原生的OBJECT类型半结构化数据，无需经过字符串序列化、解析等额外步骤，计算开销最小，完美匹配题目将关系数据转半结构化且开销最小的需求，因此正确。 D. TO_VARIANT函数仅能将单个关系列的值转换为VARIANT类型，无法将整行的多列关系数据拼接为完整的半结构化对象，若要实现整行转换还需要额外的对象拼接操作，不仅功能上不直接满足需求，还会增加额外开销，因此错误。 关键知识点： 1. Snowflake半结构化数据核心类型：Snowflake提供VARIANT、OBJECT、ARRAY三种原生半结构化数据类型，其中OBJECT为键值对结构，可直接存储关系行转换后的半结构化数据，无需额外解析即可访问内部字段。 2. 半结构化转换函数的开销差异：将关系数据转半结构化时，直接生成原生半结构化类型的函数开销远低于先序列化字符串再解析的方案，是SnowPro Advanced Data Engineer认证中半结构化性能优化的核心考点。 3. OBJECT_CONSTRUCT函数的适用场景：该函数专门用于将多组键值对组合为原生OBJECT对象，是关系型行数据转半结构化数据的最优实现方式。 参考资料： OBJECT_CONSTRUCT function, https://docs.snowflake.com/en/sql-reference/functions/object_construct Converting relational data to semistructured data, https://docs.snowflake.com/en/user-guide/semistructured-considerations#converting-relational-data-to-semistructured-data

本题考查Snowflake服务层查询结果缓存的生效条件，Snowflake的查询结果缓存是服务层面的全局缓存，用于在查询完全重复且底层数据无变更时直接返回历史结果，无需重新执行计算，能够大幅降低重复查询的资源消耗和响应时间。正确选项为DEF，这三个条件是触发查询结果缓存的核心必要条件，缺一不可。 各选项分析： A. 错误，Snowflake查询结果缓存的默认有效期为24小时，若缓存结果被持续访问可最长延长至31天，并非要求72小时内复用，该选项时间描述错误。 B. 错误，查询结果缓存属于Snowflake服务层的全局缓存，与执行查询的虚拟仓库无关，只要满足其他条件，使用任意虚拟仓库执行查询都可以命中缓存，不需要使用同一个虚拟仓库。 C. 错误，USE_CACHED_RESULT是账户/会话/查询级的参数，默认值为开启状态，无需在查询语句中手动添加该参数，且选项中参数名拼写错误，因此该选项不成立。 D. 正确，若生成查询结果的表结构发生变更（如增删字段、修改字段类型等），历史查询结果会与现有表结构不兼容，缓存自动失效，因此表结构无变更是命中缓存的必要条件。 E. 正确，新查询必须与历史查询的语法完全一致，Snowflake会忽略无意义的空格、注释及关键字大小写差异，只有语义完全相同的查询才会匹配到对应的历史缓存结果，语法不同的查询无法命中同一份缓存。 F. 正确，Snowflake采用微分区存储表数据，若表数据发生增删改等变更会导致对应微分区的版本更新，旧的查询结果不再符合当前数据状态，缓存自动失效，因此微分区无变更是命中缓存的必要条件。 关键知识点： 1. Snowflake采用三级缓存架构，其中查询结果缓存属于服务层全局缓存，无需依赖计算层虚拟仓库运行，默认开启且有效期为24小时，可延长至31天。 2. Snowflake查询结果缓存生效的核心条件包括查询语法语义一致、底层表结构无变更、底层表的微分区数据无变更、缓存未过期、相关会话参数开启等。 3. Snowflake微分区是表数据的底层存储单元，每次数据变更都会生成新的微分区版本，用于判断查询结果缓存是否有效。 参考资料： 1. Using the Query Result Cache, 2. Micro-partitions, https://docs.snowflake.com/en/user-guide/tables-micro-partitions

本题场景为使用Snowpipe加载JSON格式的半结构化数据，选中的BDE三个选项完全符合Snowflake官方针对Snowpipe加载、半结构化数据处理的最佳实践要求。B选项的文件大小规范可最大化Snowpipe的加载吞吐量，降低元数据开销；D选项的原生数据类型要求可避免加载阶段的类型转换错误，同时提升后续半结构化数据的查询效率；E选项的字段提取操作可优化存储效率，减少后续查询时解析半结构化数据的性能损耗，三者共同保障该场景下加载任务的稳定性和效率。 各选项分析： A. 错误，Snowpipe属于微批量加载服务，加载1GB及以上的大文件会大幅提升加载延迟，无法充分发挥Snowpipe的并行处理能力，官方推荐将大文件拆分后再进行加载。 B. 正确，Snowflake官方明确推荐Snowpipe加载的压缩后文件大小为100-250MB，该区间大小可最优利用Snowpipe的并行加载资源，同时避免大量小文件带来的元数据管理开销，整体加载吞吐量最高。 C. 错误，将包含多条记录的巨大数组加载到单行，会导致后续查询需要解析整个大数组才能获取单条记录，查询性能极差，也会浪费存储资源，正确做法是加载时拆分数组，每条记录对应表的一行。 D. 正确，JSON元素存储为原生的字符串或数字类型，可让Snowflake加载时自动识别正确的数据类型，避免隐式类型转换带来的错误和性能损耗，也能支持后续对VARIANT类型数据的高效查询、过滤操作。 E. 正确，提前将含空值的半结构化元素提取为关系列，一方面可利用Snowflake关系列对空值的高压缩比优化存储，另一方面后续查询无需解析VARIANT列即可访问该字段，大幅提升查询性能，是半结构化数据加载的标准优化手段。 F. 错误，小于100MB的小文件如果每分钟上传超过一次，会导致Snowpipe需要处理的文件数量激增，元数据处理开销大幅升高，加载延迟增加，甚至会触发Snowpipe的限流机制，不符合Snowpipe的最佳实践。 关键知识点： 1. Snowpipe加载文件最佳实践：Snowpipe适合处理压缩后大小为100-250MB的批量数据文件，需避免过多小文件或过大文件导致的加载性能下降。 2. 半结构化JSON数据加载优化：加载JSON数据时，优先将结构稳定、高频访问的字段提取为关系列，剩余部分存入VARIANT列，可平衡数据灵活性和查询性能。 3. 半结构化数据类型规范：JSON元素需使用原生数据类型存储，便于Snowflake进行自动类型推断，减少加载和查询阶段的类型转换开销。 参考资料： Snowpipe最佳实践, 半结构化数据最佳实践

本题要求返回北美地区SALES_REPRESENTATIVE列的平均聚簇深度，Snowflake中专门用于获取平均聚簇深度的系统函数为SYSTEM$CLUSTERING_DEPTH，该函数支持三个入参：第一个为目标表名，第二个为需要计算聚簇深度的列或表达式，第三个为可选的过滤谓词，用于限定参与计算的数据集范围，完全匹配题目中需要过滤北美地区数据的需求，因此正确选项为B。 各选项分析： A. 错误，SYSTEM$CLUSTERING_INFORMATION函数返回的是包含聚簇深度、微分区重叠占比等完整信息的JSON结构化结果，不会直接返回平均聚簇深度的数值，不符合题目要求。 B. 正确，使用了正确的SYSTEM$CLUSTERING_DEPTH函数，三个参数依次为目标表SALES、待计算的列SALES_REPRESENTATIVE、过滤谓词区域='北美'，完全符合题目需求。 C. 错误，聚簇类系统函数的数据集过滤谓词必须作为第三个参数传入函数，不能写在函数外部的WHERE子句中，且该写法未传入过滤参数，无法限定仅计算北美地区的数据，逻辑错误。 D. 错误，首先使用了错误的SYSTEM$CLUSTERING_INFORMATION函数，无法直接返回平均聚簇深度，其次过滤条件写在外部WHERE子句的用法不符合聚簇系统函数的参数规则。 关键知识点： 1. Snowflake系统函数SYSTEM$CLUSTERING_DEPTH的作用与参数规则，该函数用于返回指定表的指定列在指定数据范围内的平均聚簇深度，参数依次为表名、目标列、可选过滤谓词。 2. 聚簇元数据类系统函数的区别，SYSTEM$CLUSTERING_INFORMATION返回完整的聚簇元数据JSON结构，SYSTEM$CLUSTERING_DEPTH仅返回平均聚簇深度的数值。 3. 聚簇系统函数的过滤规则，要限定计算的数据集范围，必须将过滤谓词作为第三个参数传入聚簇函数，不能通过外部WHERE子句过滤原表数据。 参考资料： 1. Snowflake官方文档：SYSTEM$CLUSTERING_DEPTH, https://docs.snowflake.com/en/sql-reference/functions/system_clustering_depth 2. Snowflake官方文档：查看聚簇元数据