GitLab Duo Agent平台如何彻底改变DataOps

手动创建dbt模型的挑战

手动创建dbt模型是一个繁琐的过程，可能消耗数据工程师数小时的时间。特别是当没有（重大）业务转换时，这并非工程师处理数据工作中最有吸引力的部分。

但是，如果您能自动化整个流程呢？在本演练中，我将展示GitLab Duo Agent平台如何在短短几分钟内生成完整的dbt模型，包括适当的结构、测试和文档。

我们要构建什么

我们的营销团队希望有效管理和优化广告投资。其中一个广告平台是Reddit，因此我们从Reddit Ads API提取数据到我们的企业数据平台Snowflake。在GitLab，我们有三层存储：

原始层 - 来自外部来源的未处理数据的首次着陆点；尚未准备好用于业务
准备层 - 具有源模型的第一个转换层；仍未准备好用于一般业务
生产层 - 最终转换的数据，准备好用于业务和Tableau报告

在此演练中，数据已通过我们的提取解决方案Fivetran进入原始层，我们将生成dbt模型，处理从准备层到生产层的数据。

无需我们自己编写一行dbt代码，在演练结束时我们将拥有：

准备层中的源模型
生产层中的工作区模型
Reddit Ads数据集中所有13个表（包含112列）的完整dbt配置
验证结果的测试查询

整个过程将花费不到10分钟，而手动操作通常需要数小时。以下是需要遵循的步骤：

1. 准备数据结构

在GitLab Duo生成我们的模型之前，它需要了解完整的表结构。关键是对Snowflake的信息模式运行查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


SELECT 
    table_name,
    column_name,
    data_type,
    is_nullable,
    CASE 
        WHEN is_nullable = 'NO' THEN 'PRIMARY_KEY'
        ELSE NULL 
    END as key_type
FROM raw.information_schema.columns
WHERE table_schema = 'REDDIT_ADS'
ORDER BY table_name, ordinal_position;

此查询捕获：

所有表和列名
用于适当模型结构的数据类型
可空约束
主键标识（此数据集中的非空列）

专业提示：在Reddit Ads数据集中，所有非空列都用作主键——这是一个模式。我通过检查像ad_group这样的表进行了验证，该表有两个非空列（account_id和id），都被标记为主键。运行此查询返回了112行元数据，我将其导出为CSV文件用于模型生成。

2. 设置GitLab Duo

有两种与GitLab Duo交互的方式：

Web UI聊天功能
Visual Studio Code插件

我选择了VS Code插件，因为我可以在本地运行dbt模型来测试它们。

3. 输入"魔法"提示

这是我用于生成所有dbt代码的确切提示：

1
2
3
4
5
6
7
8


Create dbt models for all the tables in the file structure.csv.

I want to have the source models created, with a filter that dedupes the data based on the primary key. Create these in a new folder reddit_ads.
I want to have workspace models created and store these in the workspace_marketing schema.

Take this MR as example: [I've referenced to previous source implementation]. Here is the same done for Source A, but now it needs to be done for Reddit Ads. 

Please check the dbt style guide when creating the code: https://handbook.gitlab.com/handbook/enterprise-data/platform/dbt-guide/

使此提示有效的关键要素：

源模型和工作区模型的清晰规范
参考先前类似合并请求的示例
样式指南参考以确保代码质量和一致性
适当组织的特定模式定位

4. GitLab Duo的处理过程

提交提示后，GitLab Duo开始工作。整个生成过程花费了几分钟，在此期间GitLab Duo：

读取并分析CSV输入文件
检查元数据中的表结构
参考我们的dbt样式指南以获取编码标准
考虑类似的合并请求以正确构建结构
为所有13个表生成源模型
为所有13个表创建工作区模型
生成支持的dbt文件：
- sources.yml配置
- 包含测试和文档的schema.yml文件
- 更新了dbt_project.yml与模式引用

结果

输出结果令人印象深刻：

1个修改的文件：dbt_project.yml（添加了reddit_ads模式配置）
29个新文件：
- 26个dbt模型（13个源 + 13个工作区）
- 3个YAML文件
自动生成近900行代码
内置数据测试，包括主键列的唯一约束
所有模型和列的通用描述
源模型中的适当去重逻辑
遵循GitLab dbt样式指南的清晰、一致的代码结构

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36


transform/snowflake-dbt/
├── dbt_project.yml                                                    [MODIFIED]
└── models/
    ├── sources/
    │   └── reddit_ads/
    │       ├── reddit_ads_ad_group_source.sql                        [NEW]
    │       ├── reddit_ads_ad_source.sql                              [NEW]
    │       ├── reddit_ads_business_account_source.sql                [NEW]
    │       ├── reddit_ads_campaign_source.sql                        [NEW]
    │       ├── reddit_ads_custom_audience_history_source.sql         [NEW]
    │       ├── reddit_ads_geolocation_source.sql                     [NEW]
    │       ├── reddit_ads_interest_source.sql                        [NEW]
    │       ├── reddit_ads_targeting_community_source.sql             [NEW]
    │       ├── reddit_ads_targeting_custom_audience_source.sql       [NEW]
    │       ├── reddit_ads_targeting_device_source.sql                [NEW]
    │       ├── reddit_ads_targeting_geolocation_source.sql           [NEW]
    │       ├── reddit_ads_targeting_interest_source.sql              [NEW]
    │       ├── reddit_ads_time_zone_source.sql                       [NEW]
    │       ├── schema.yml                                            [NEW]
    │       └── sources.yml                                           [NEW]
    └── workspaces/
        └── workspace_marketing/
            └── reddit_ads/
                ├── schema.yml                                        [NEW]
                ├── wk_reddit_ads_ad.sql                              [NEW]
                ├── wk_reddit_ads_ad_group.sql                        [NEW]
                ├── wk_reddit_ads_business_account.sql                [NEW]
                ├── wk_reddit_ads_campaign.sql                        [NEW]
                ├── wk_reddit_ads_custom_audience_history.sql         [NEW]
                ├── wk_reddit_ads_geolocation.sql                     [NEW]
                ├── wk_reddit_ads_interest.sql                        [NEW]
                ├── wk_reddit_ads_targeting_community.sql             [NEW]
                ├── wk_reddit_ads_targeting_custom_audience.sql       [NEW]
                ├── wk_reddit_ads_targeting_device.sql                [NEW]
                ├── wk_reddit_ads_targeting_geolocation.sql           [NEW]
                └── wk_reddit_ads_time_zone.sql                       [NEW]

生成的代码示例

以下是生成的代码质量示例。对于time_zone表，GitLab Duo创建了：

准备层源模型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


WITH source AS (
  SELECT *
  FROM {{ source('reddit_ads','time_zone') }}
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _fivetran_synced DESC) = 1
),

renamed AS (
  SELECT
    id::VARCHAR                               AS time_zone_id,
    code::VARCHAR                             AS time_zone_code,
    dst_offset::NUMBER                        AS time_zone_dst_offset,
    is_dst_active::BOOLEAN                    AS is_time_zone_dst_active,
    name::VARCHAR                             AS time_zone_name,
    offset::NUMBER                            AS time_zone_offset,
    _fivetran_synced::TIMESTAMP               AS fivetran_synced_at
  FROM source
)

SELECT * FROM renamed

Schema.yml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


models:
  - name: reddit_ads_time_zone_source
    description: Time zone data from Reddit Ads system
    columns:
      - name: time_zone_id
        description: Unique identifier for time zone records
        data_tests:
          - unique
          - not_null
      - name: time_zone_code
        description: Code for the time zone
      - name: time_zone_dst_offset
        description: Daylight saving time offset for the time zone
      - name: is_time_zone_dst_active
        description: Flag indicating if daylight saving time is active
      - name: time_zone_name
        description: Name of the time zone
      - name: time_zone_offset
        description: Offset for the time zone
      - name: fivetran_synced_at
        description: Timestamp when the record was last synced by Fivetran

Source.yml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


sources:
  - name: reddit_ads
    database: RAW
    schema: reddit_ads
    loaded_at_field: _fivetran_synced
    loader: fivetran
    description: Reddit Ads data

    quoting:
      database: true
      schema: false
      identifier: false

    tables:
      - name: time_zone

工作区模型

1
2
3
4
5
6


WITH source AS (
  SELECT *
  FROM {{ ref('reddit_ads_time_zone_source') }}
)

SELECT * FROM source

5. 质量验证

现在代码看起来不错，我将其推送到MR并执行CI测试管道来测试代码并验证结果。我要求GitLab Duo创建一个验证查询：

1

Create a test query to test the row counts between the raw layer and the workspace layer. Keep in mind that we do deduplication, so we can compare both using distinct on the primary keys.

AI生成了一个全面的验证查询，该查询：

比较原始层和工作区层之间的行数
考虑了去重逻辑
测试所有13个表
计算数据保留百分比

生成的SQL测试查询

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153


-- Reddit Ads Row Count Validation Test
-- Compares distinct counts between RAW layer and WORKSPACE_MARKETING layer
-- Accounts for deduplication logic in source models

WITH raw_counts AS (
  -- Single primary key tables
  SELECT 'ad' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.AD
  
  UNION ALL
  
  SELECT 'business_account' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.BUSINESS_ACCOUNT
  
  UNION ALL
  
  SELECT 'campaign' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.CAMPAIGN
  
  UNION ALL
  
  SELECT 'custom_audience_history' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.CUSTOM_AUDIENCE_HISTORY
  
  UNION ALL
  
  SELECT 'geolocation' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.GEOLOCATION
  
  UNION ALL
  
  SELECT 'interest' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.INTEREST
  
  UNION ALL
  
  SELECT 'time_zone' AS table_name, COUNT(DISTINCT id) AS raw_count
  FROM RAW.REDDIT_ADS.TIME_ZONE
  
  -- Composite primary key tables
  UNION ALL
  
  SELECT 'ad_group' AS table_name, COUNT(DISTINCT CONCAT(account_id, '|', id)) AS raw_count
  FROM RAW.REDDIT_ADS.AD_GROUP
  
  UNION ALL
  
  SELECT 'targeting_community' AS table_name, COUNT(DISTINCT CONCAT(ad_group_id, '|', community_id)) AS raw_count
  FROM RAW.REDDIT_ADS.TARGETING_COMMUNITY
  
  UNION ALL
  
  SELECT 'targeting_custom_audience' AS table_name, COUNT(DISTINCT CONCAT(ad_group_id, '|', custom_audience_id)) AS raw_count
  FROM RAW.REDDIT_ADS.TARGETING_CUSTOM_AUDIENCE
  
  UNION ALL
  
  SELECT 'targeting_device' AS table_name, COUNT(DISTINCT _fivetran_id) AS raw_count
  FROM RAW.REDDIT_ADS.TARGETING_DEVICE
  
  UNION ALL
  
  SELECT 'targeting_geolocation' AS table_name, COUNT(DISTINCT CONCAT(ad_group_id, '|', geolocation_id)) AS raw_count
  FROM RAW.REDDIT_ADS.TARGETING_GEOLOCATION
  
  UNION ALL
  
  SELECT 'targeting_interest' AS table_name, COUNT(DISTINCT CONCAT(ad_group_id, '|', interest_id)) AS raw_count
  FROM RAW.REDDIT_ADS.TARGETING_INTEREST
),

workspace_counts AS (
  -- Workspace layer counts using primary keys from schema.yml
  SELECT 'ad' AS table_name, COUNT(DISTINCT ad_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_AD
  
  UNION ALL
  
  SELECT 'business_account' AS table_name, COUNT(DISTINCT business_account_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_BUSINESS_ACCOUNT
  
  UNION ALL
  
  SELECT 'campaign' AS table_name, COUNT(DISTINCT campaign_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_CAMPAIGN
  
  UNION ALL
  
  SELECT 'custom_audience_history' AS table_name, COUNT(DISTINCT custom_audience_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_CUSTOM_AUDIENCE_HISTORY
  
  UNION ALL
  
  SELECT 'geolocation' AS table_name, COUNT(DISTINCT geolocation_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_GEOLOCATION
  
  UNION ALL
  
  SELECT 'interest' AS table_name, COUNT(DISTINCT interest_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_INTEREST
  
  UNION ALL
  
  SELECT 'time_zone' AS table_name, COUNT(DISTINCT time_zone_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TIME_ZONE
  
  -- Composite primary key tables
  UNION ALL
  
  SELECT 'ad_group' AS table_name, COUNT(DISTINCT CONCAT(ad_group_account_id, '|', ad_group_id)) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_AD_GROUP
  
  UNION ALL
  
  SELECT 'targeting_community' AS table_name, COUNT(DISTINCT CONCAT(targeting_community_ad_group_id, '|', targeting_community_id)) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TARGETING_COMMUNITY
  
  UNION ALL
  
  SELECT 'targeting_custom_audience' AS table_name, COUNT(DISTINCT CONCAT(targeting_custom_audience_ad_group_id, '|', targeting_custom_audience_id)) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TARGETING_CUSTOM_AUDIENCE
  
  UNION ALL
  
  SELECT 'targeting_device' AS table_name, COUNT(DISTINCT targeting_device_fivetran_id) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TARGETING_DEVICE
  
  UNION ALL
  
  SELECT 'targeting_geolocation' AS table_name, COUNT(DISTINCT CONCAT(targeting_geolocation_ad_group_id, '|', targeting_geolocation_id)) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TARGETING_GEOLOCATION
  
  UNION ALL
  
  SELECT 'targeting_interest' AS table_name, COUNT(DISTINCT CONCAT(targeting_interest_ad_group_id, '|', targeting_interest_id)) AS workspace_count
  FROM REDDIT_DBT_MODEL_GENERATION_PROD.WORKSPACE_MARKETING.WK_REDDIT_ADS_TARGETING_INTEREST
)

-- Final comparison with validation results
SELECT 
  r.table_name,
  r.raw_count,
  w.workspace_count,
  r.raw_count - w.workspace_count AS count_difference,
  CASE 
    WHEN r.raw_count = w.workspace_count THEN '✅ PASS'
    WHEN r.raw_count > w.workspace_count THEN '⚠️ RAW > WORKSPACE (Expected due to deduplication)'
    ELSE '❌ FAIL - WORKSPACE > RAW (Unexpected)'
  END AS validation_status,
  ROUND((w.workspace_count::FLOAT / r.raw_count::FLOAT) * 100, 2) AS data_retention_percentage
FROM raw_counts r
JOIN workspace_counts w ON r.table_name = w.table_name
ORDER BY r.table_name;

运行此查询显示：

去重后行数零差异
所有表的数据保留率为100%
所有测试成功通过

最终结果：巨大的时间节省

传统方法：6-8小时的手动编码、测试和调试
GitLab Duo方法：6-8分钟的生成 + 审查时间

这代表了开发效率提高了60倍（从6-8小时减少到6-8分钟），同时保持了高代码质量。

成功的最佳实践

基于此经验，以下是关键建议：

准备元数据

提取完整的表结构，包括数据类型和约束
预先识别主键和关系
导出干净、格式良好的CSV输入文件

注意：通过MCP将GitLab Duo连接到您的（元）数据，您可以排除此手动步骤。

提供清晰的上下文

尽可能参考现有的示例MR
指定您的编码标准和样式指南
明确文件夹结构和命名约定

彻底验证

始终为数据完整性创建验证查询
在合并前进行本地测试
运行CI/CD管道以捕获任何问题

利用AI进行后续任务

自动生成测试查询
创建文档模板
构建验证脚本

下一步

此演示展示了像GitLab Duo这样的AI驱动开发工具如何改变数据工程工作流。在几分钟内生成数百行生产就绪代码的能力——包括测试、文档和适当结构——代表了我们在处理重复开发任务方式上的根本转变。

通过利用AI处理dbt模型创建的重复方面，数据工程师可以专注于更高价值的活动，如数据建模策略、性能优化和业务逻辑实现。

准备好自己尝试了吗？从一个小数据集开始，仔细准备元数据，并观察GitLab Duo如何将数小时的工作转变为几分钟的自动生成。