ELT/ETL管道搭建
ELT/ETL pipeline construction
Configure connectors, transformations and schedules in ELT tools and orchestrators.
20 条本项目编写的提案 · 分类 v1.1 · 新增基准运行 0 次。下方验证方式与部分得分规则是设计，不是执行结果。
- 典型应用
- Airbyte · dbt · Airflow · Fivetran
- 小类 GUI 需求标签
HY· 混合
任务提案 · 20
展开查看完整原文与建议检查方式；两种界面均保留提案英文原文。
R03.01.02.01-T01 Configure an incremental Airbyte connection with dedup
原始指令
In the self-hosted Airbyte UI, create a connection from source Postgres 'shop' to destination DuckDB at /data/warehouse.duckdb syncing orders, customers and order_items with Incremental | Append + Deduped (cursor updated_at, primary keys per docs/keys.md), schedule every 6 hours, and run the first sync.
- 平台
web· 任意系统（网页）- 应用
- Airbyte
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader reads the connection config via Airbyte's internal API: streams, sync modes, cursors, PKs and schedule exact. After grader mutates 50 source rows and triggers a sync, the destination final tables equal source (no duplicates).
建议部分得分
35% stream config; 15% schedule; 20% first sync succeeded; 30% incremental dedup correct after mutation
R03.01.02.01-T02 Build tested dbt staging and mart models for orders
原始指令
In the dbt project fixtures/jaffle_dbt (DuckDB adapter), add staging models for raw orders/payments/customers and a mart fct_orders with order amount by payment method, plus unique/not_null/relationships/accepted_values tests and column docs per spec/models.md.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- dbt · DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader runs dbt build: all models and tests pass; fct_orders equals gold (EXCEPT both ways empty). Hidden dirty-data seed must make the relevant tests fail (tests are real). Docs YAML covers all columns.
建议部分得分
40% fct_orders correct; 30% tests pass and catch dirty seed; 15% staging models; 15% docs
R03.01.02.01-T03 Write an Airflow DAG with sensors, retries and a 7-day backfill
原始指令
Write dags/daily_sales.py for Airflow 2.9: wait for s3://landing/sales/{{ds}}.csv in MinIO, load it into Postgres staging, run a SQL transform into sales_daily, with 3 retries and 5-minute backoff, catchup on, and a 2-hour SLA. Backfill 2025-10-01 to 2025-10-07.
- 平台
lin· Linux- 应用
- Airflow · PostgreSQL · MinIO
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader parses the DAG (DagBag no import errors, task graph and params), checks Airflow metadata: 7 successful dag_runs for the dates; sales_daily rows match gold per date. A grader-removed file causes the sensor to wait, not fail.
建议部分得分
20% DAG parses with required settings; 20% sensor behavior; 50% 7 backfilled dates correct; 10% SLA set
R03.01.02.01-T04 Rerun failed Airflow tasks for specific dates from the UI
原始指令
In the Airflow web UI, DAG 'marketing_attribution' failed on 3 of the last 10 runs after an upstream fix was deployed. Clear only the failed tasks and their downstream tasks for those 3 runs so they rerun, and mark the 'notify_slack' task as skipped-success for all 3 without running it.
- 平台
web· 任意系统（网页）- 应用
- Airflow
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader reads task_instance states: target runs now success for all transform tasks with try_number incremented; notify_slack state success with no execution log; the 7 good runs untouched (try_number unchanged). REST API credentials are not provided.
建议部分得分
50% correct tasks rerun; 25% notify_slack marked without running; 25% other runs untouched
R03.01.02.01-T05 Configure a Fivetran sandbox connector with hashed PII columns
原始指令
In the Fivetran sandbox account, set up the HubSpot test connector to the provided destination: sync only contacts, companies and deals; hash columns email and phone; block column notes; 1-hour sync frequency; and start the initial sync.
- 平台
web· 任意系统（网页）- 应用
- Fivetran
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader reads connector schema config via a read-only API key: enabled tables exact, hashed and blocked columns exact, frequency 60. Destination tables contain hashed (64-hex) email values and no notes column after sync.
建议部分得分
35% table selection; 35% column hashing/blocking; 15% frequency; 15% sync results
R03.01.02.01-T06 Build a copy and mapping data flow pipeline in Azure Data Factory
原始指令
In ADF Studio (sandbox factory), build pipeline pl_ingest_returns: Copy activity from the blob container 'landing/returns/*.csv' to staging SQL table, then a Mapping Data Flow that dedups by return_id, derives refund_usd with the FX lookup table, and upserts into dbo.Returns. Parameterize the run date and trigger it for 2025-10-14.
- 平台
web· 任意系统（网页）- 应用
- Azure Data Factory
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
drag· 拖放click· 指向与点击type· 文字与表单- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader exports the factory ARM template: activities, data flow transformations (aggregate/derive/lookup/alter row) and parameters present; pipeline run for the date succeeded; dbo.Returns equals gold (dedup + USD ±0.01).
建议部分得分
25% pipeline structure; 30% data flow logic; 15% parameterization; 30% output table correct
R03.01.02.01-T07 Build an SSIS package with lookups and conditional splits
原始指令
In Visual Studio with SSIS, create package LoadCustomers.dtsx: read fixtures/customers.csv, derive full_name, look up region_id from dbo.Regions (no-match rows to an error table), split VIP (lifetime_value > 10000) to dbo.VipCustomers and others to dbo.Customers. Deploy to the SSIS catalog and execute it.
- 平台
win· Windows- 应用
- Visual Studio · SSIS · SQL Server
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
drag· 拖放click· 指向与点击type· 文字与表单- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader parses the .dtsx XML for the data-flow components and paths, checks SSISDB execution status succeeded, and compares the 3 output tables to gold. The package must be saved by the Visual Studio SSIS designer (DTS:CreatorName, valid layout metadata); Biml tools are absent and .dtsx writes by other processes are detected and fail.
建议部分得分
25% components and paths; 25% lookup + error routing; 30% outputs correct; 20% deployed and executed
R03.01.02.01-T08 Build a NiFi flow that routes and loads JSON events
原始指令
On the Apache NiFi canvas, build a flow: ListenHTTP on port 9090 → validate against schemas/event.avsc → route by attribute event_type (click/purchase/other) → PutDatabaseRecord into Postgres tables clicks and purchases, sending invalid records to a failure folder. Start it and leave it running.
- 平台
web· 任意系统（网页）- 应用
- Apache NiFi · PostgreSQL
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
drag· 拖放click· 指向与点击type· 文字与表单- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader posts 1,000 mixed events (40 invalid): tables hold exactly the valid click/purchase rows; 40 invalid files in failure dir; 'other' events dropped or logged per spec. Flow read via NiFi REST shows processors connected as required. NiFi Toolkit CLI and curl are absent from the agent VM, and flow history must show the components added in the UI session, not a flow-definition upload.
建议部分得分
25% processors and connections; 25% validation path; 35% table contents; 15% flow running
R03.01.02.01-T09 Make a dbt incremental model handle late data and add a snapshot
原始指令
In fixtures/events_dbt, convert fct_sessions to an incremental model with the merge strategy and a 3-day lookback for late-arriving events, and add a snapshot of dim_accounts (check strategy on plan and status) to track history.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- dbt · PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader runs dbt build, injects late events (2 days old) and account changes, runs again: fct_sessions equals a full-refresh rebuild; snapshot has dbt_valid_from/to rows for each change. No duplicates in fct_sessions.
建议部分得分
40% incremental equals full refresh; 20% lookback works; 30% snapshot history; 10% no duplicates
R03.01.02.01-T10 Materialize a partitioned Dagster asset from the UI
原始指令
In the Dagster UI for project fixtures/dagster_sales, the daily-partitioned asset 'sales_daily' and its downstream 'sales_weekly' are missing partitions for 2025-10-01..07. Launch a backfill of exactly those partitions with downstream assets, and report the row count of sales_weekly for week 2025-W40 in output/answer.txt.
- 平台
web· 任意系统（网页）lin· Linux- 应用
- Dagster
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader reads the Dagster instance event log: materializations exist for the 7 partitions and the downstream week; no other partitions rematerialized. answer.txt equals the actual table count.
建议部分得分
50% correct partitions materialized; 20% downstream included; 15% no extra runs; 15% answer
R03.01.02.01-T11 Add caching, retries and a schedule to a Prefect flow
原始指令
Turn fixtures/prefect_etl/flow.py into a Prefect 3 deployment: tasks cache by input hash for 1 day, the API extract retries 3 times with exponential backoff, and the deployment runs daily at 06:00 Europe/Berlin with a parameter for region.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- Prefect
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader inspects the deployment via Prefect API (schedule, timezone, parameters), runs it twice with the flaky mock API: second run hits cache (task state Cached); retries visible in task run states.
建议部分得分
30% schedule and timezone; 30% caching; 30% retries; 10% parameter
R03.01.02.01-T12 Build a refreshable folder-of-CSVs query in Power Query
原始指令
In Excel for Windows, use Power Query to combine all monthly CSVs in fixtures/branch_sales/ (some with an extra column and different header casing), unpivot the product columns, change types, merge with Branches.xlsx for region, and load to a table 'SalesLong' that refreshes when a new month file is added.
- 平台
win· Windows- 应用
- Excel · Power Query
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击drag· 拖放- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader adds a new CSV, triggers Refresh All via COM, reads SalesLong: equals gold including the new month. Query M code (from workbook DataMashup) must reference the folder, not individual files. VBA macros and COM automation are blocked for the agent account (COM is grader-only), so Workbook.Queries.Add cannot script the M; it must be built in the Power Query editor.
建议部分得分
30% combine folder robust to schema drift; 25% unpivot/types; 20% merge; 25% refresh with new file
R03.01.02.01-T13 Build a custom REST source in the Airbyte Connector Builder
原始指令
Using Airbyte's Connector Builder UI, create a source for the sandbox Tickets API (docs in docs/tickets_api.md): bearer-token auth, cursor pagination, incremental sync on updated_at, and streams tickets and comments (parent-child). Publish it and sync to the local Postgres destination.
- 平台
web· 任意系统（网页）- 应用
- Airbyte
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP06· 代码 / 工程- 条件
tutorial· 阅读文档 / 教程- 提案难度
- 4 / 5
建议验证方式
Grader reads the published manifest: auth, paginator, incremental cursor, parent stream partition router configured. After sync, destination has all 2,340 tickets and 9,812 comments; second sync after 15 API updates adds only changed records.
建议部分得分
25% auth+pagination; 25% parent-child streams; 30% full sync counts; 20% incremental behavior
R03.01.02.01-T14 Stream database changes with Debezium into a warehouse table
原始指令
Set up CDC from sandbox MySQL 'orders' to Postgres 'analytics': Debezium MySQL connector on Kafka Connect, then a JDBC sink with upsert and delete handling so analytics.orders mirrors the source within 10 s. Handle a schema change (new column) without restarting.
- 平台
lin· Linux- 应用
- Debezium · Kafka Connect · MySQL · PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP08· 应用 / 系统状态- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
Grader runs a mutation script (inserts, updates, deletes, ALTER ADD COLUMN): after each phase analytics.orders equals source within 10 s; deletes propagated; new column appears with values. Connector status RUNNING.
建议部分得分
25% initial snapshot; 30% updates/deletes; 25% schema change; 20% latency target
R03.01.02.01-T15 Gate an Airflow pipeline on Great Expectations checks
原始指令
Add a Great Expectations checkpoint to the 'customer_ingest' Airflow DAG between load and publish: email format, non-null ids, unique ids, and signup_date not in the future. A failed checkpoint must stop publish and store the validation result.
- 平台
lin· Linux- 应用
- Airflow · Great Expectations
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader runs the DAG with a clean file (publish succeeds) and a bad file with 4 violation types (publish skipped/failed, GE result JSON lists each expectation failure). Expectation suite contents checked.
建议部分得分
30% suite correct; 30% clean path; 30% bad file blocks publish; 10% results stored
R03.01.02.01-T16 Repair a dbt project after a source column rename
原始指令
The nightly dbt run in fixtures/fin_dbt fails because the source table renamed amt to amount_cents and changed units. Fix the staging layer so downstream marts produce identical values to before, then backfill the last 30 days.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- dbt · DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader: dbt build passes; marts for the 30 days equal gold (unit conversion correct); downstream models unchanged except staging (git diff scope). Tests still pass.
建议部分得分
30% builds; 50% values match after unit change; 20% change confined to staging
R03.01.02.01-T17 Fan out per-customer exports with Airflow dynamic task mapping
原始指令
In Airflow 2.9, write dags/customer_exports.py that queries the active customer list, maps one export task per customer writing s3://exports/{customer}/{{ds}}.parquet in MinIO, and a final task that writes a manifest. Max 8 concurrent mapped tasks.
- 平台
lin· Linux- 应用
- Airflow · MinIO
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader triggers the DAG: mapped task count equals active customers (37); files exist with correct row counts; manifest lists all 37; max_active_tis_per_dagrun ≤8 observed in task timing.
建议部分得分
30% dynamic mapping; 40% exports correct; 20% manifest; 10% concurrency
R03.01.02.01-T18 Build an ETL workflow on the KNIME canvas
原始指令
In KNIME Analytics Platform, build workflow output/claims_etl.knwf: read fixtures/claims.xlsx and providers.csv, clean dates and codes, join, compute claim age buckets, flag duplicates, and write to Postgres table claims_clean plus a summary CSV. Execute it end to end.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- KNIME Analytics Platform
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
drag· 拖放click· 指向与点击- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader opens the .knwf (workflow.knime XML): required node types connected; runs it in KNIME batch mode on a hidden variant of the inputs; claims_clean and summary equal gold for both the original and variant. KNIME batch mode is blocked for the agent (grader-only), and workflow.knime must be saved by the KNIME UI with node layout bounds; outside writes are logged.
建议部分得分
25% workflow structure; 45% outputs correct; 30% generalizes to hidden variant
R03.01.02.01-T19 Orchestrate Airbyte, dbt and a BI refresh end to end
原始指令
Build an Airflow DAG that triggers the existing Airbyte connection 'crm_to_wh', waits for completion, runs dbt build for the 'crm' selector, then refreshes the Metabase dashboard cache 'Pipeline KPIs'. Failures must alert via the mock webhook, and the whole chain must succeed for today.
- 平台
web· 任意系统（网页）lin· Linux- 应用
- Airflow · Airbyte · dbt · Metabase
- 交互表面
brow· 浏览器term· 终端- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
HY· 混合- 交互原语
code· 代码与命令click· 指向与点击multi· 跨应用- 工作产物
WP08· 应用 / 系统状态- 条件
multiapp· 多应用long· 长流程- 提案难度
- 5 / 5
建议验证方式
Grader: Airflow run success; Airbyte job id linked in XCom; dbt run_results.json success for selector; Metabase cache timestamp updated after dbt end time; injected Airbyte failure produces webhook call and stops dbt.
建议部分得分
25% Airbyte trigger + wait; 25% dbt step; 20% Metabase refresh; 30% failure handling
R03.01.02.01-T20 Make a daily load pipeline idempotent and re-runnable
原始指令
The Python+SQL pipeline in fixtures/ledger_etl appends duplicates when rerun and loses rows when a run dies midway. Make it idempotent per run date (staging + atomic swap or MERGE) so any rerun or retry yields identical warehouse tables.
- 平台
lin· Linuxmac· macOS- 应用
- Python · PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader runs dates in random order, reruns 3 dates twice, and kills one run mid-load: final tables equal a single clean run (row hashes). No duplicates; partial-run rows never visible to concurrent readers.
建议部分得分
40% reruns idempotent; 30% crash safety; 20% atomic visibility; 10% order independence
已有研究映射
以下沿用研究目录中的基准与小类映射，不是逐题标注、执行记录或产品背书。