如何解锁业务数据价值：基于云器Lakehouse构建面向未来的ELT现代数据栈

mds_dbt:
  target: clickzetta_uat
  outputs:
    mds_source:
      type: postgres
      host: 172.17.1.220
      port: 5432
      user: metabase
      pass: metasample123
      dbname: mds_source
      schema: public
      threads: 2
      keepalives_idle: 0
    clickzetta_uat:
      type: clickzetta
      service: https://uat-api.clickzetta.com
      workspace: ql_ws
      instance: ***
      vcluster: default
      username: ***
      password: ***
      database: ql_ws
      schema: modern_data_stack
      threads: 8
      keepalives_idle: 0
    mysql_employees:
      type: mysql
      server: 172.17.1.220
      port: 3306
      database: employees
      schema: employees
      username: root
      password: metasample123
      driver: MySQL ODBC 8.0 ANSI Driver
      threads: 2
      keepalives_idle: 0

version: 2
sources:
  - name: ql_ws
    database: ql_ws
    schema: modern_data_stack
    tables:
      - name: orders
        identifier: _airbyte_raw_orders
        description: "订单表，包含所有订单数据。"
      - name: users
        identifier: _airbyte_raw_users
        description: "用户表，包含用户信息。"
      - name: departments
        identifier: departments
        description: "员工信息，departments table"
      - name: dept_emp
        identifier: dept_emp
        description: "员工信息，dept_emp table"
      - name: dept_manager
        identifier: dept_manager
        description: "员工信息，dept_manager table"
      - name: employees
        identifier: employees
        description: "员工信息，employees table"
      - name: salaries
        identifier: salaries
        description: "员工信息，salaries table"
      - name: titles
        identifier: titles
        description: "员工信息，titles table"

select
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,is_bot:boolean>').index as int) as index,
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,is_bot:boolean>').user_id as string) as user_id,
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,is_bot:boolean>').is_bot as boolean) as is_bot
from {{ source('ql_ws', 'users') }}

select
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,order_time:string,order_value:double>').index as int) as index,
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,order_time:string,order_value:double>').user_id as string) as user_id,
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,order_time:string,order_value:double>').order_time as timestamp) as order_time,
        cast(from_json(_airbyte_data,'struct<index:bigint,user_id:bigint,order_time:string,order_value:double>').order_value as double) as order_value,
from {{ source('ql_ws', 'orders') }}

select
        date_trunc('DAY', oc.order_time::timestamp) as order_date,
        sum(oc.order_value) as total_value,
        count(*) as num_orders
from
        {{ ref("orders_cleaned") }} oc
        join
        {{ ref("users_augmented") }} ua
        on oc.user_id = ua.user_id
where not ua.is_bot
group by 1 order by 1

SELECT  e.emp_no,
        e.birth_date,
        e.first_name,
        e.last_name,
        e.gender,
        e.hire_date,
        d.dept_no,
        d.dept_name,
        m.from_date as dept_manager_from_date,
        m.to_date as dept_manager_to_date, 
        de.from_date as dept_emp_from_date,
        de.to_date as dept_emp_to_date,
        t.title,
        t.from_date as title_from_date,
        t.to_date as title_to_date,
        s.salary,
        s.from_date as salary_from_date,
        s.to_date as salary_to_date
FROM {{ source('ql_ws', 'employees') }} e
FULL JOIN {{ source('ql_ws', 'dept_emp') }} de ON e.emp_no = de.emp_no
FULL JOIN {{ source('ql_ws', 'departments') }} d ON de.dept_no = d.dept_no
FULL JOIN {{ source('ql_ws', 'titles') }} t ON e.emp_no = t.emp_no
FULL JOIN {{ source('ql_ws', 'salaries') }} s ON e.emp_no = s.emp_no
FULL JOIN {{ source('ql_ws', 'dept_manager') }} m ON e.emp_no = m.emp_no
order by e.emp_no,d.dept_no,m.from_date desc

version: 2
models:
  - name: daily_order_summary
    description: "Daily metrics for orders placed on this platform."
    columns:
      - name: order_date
        description: "The UTC day for which these orders were aggregated."
        data_type: "date"
      - name: total_value
        description: "The total value of all orders placed on this day."
        data_type: "float"
      - name: num_orders
        description: "The total number of orders placed on this day."
        data_type: "int"
  - name: orders_cleaned
    description: "Filtered version of the raw orders data."
    columns:
      - name: "user_id"
        description: "Platform id of the user that placed this order."
        data_type: "int"
      - name: "order_time"
        description: "The timestamp (in UTC) that this order was placed."
        data_type: "timestamp"
      - name: "order_value"
        description: "The dollar amount that this order was placed for."
        data_type: "float"
  - name: users_augmented
    description: "Raw users data augmented with backend data."
    columns:
      - name: "user_id"
        description: "Platform id for this user."
        data_type: "int"
      - name: "is_spam"
        description: "True if this user has been marked as a fraudulent account."
        data_type: "bool"
  - name: employees_detail_single_view
    description: "将departments, dept_emp,dept_manager,employees,salaries,titles等6张表的数据合并到一张表，形成员工数据统一视图"
    columns:
      - name: "emp_no"
        description: "Unique employee number identifier."
        data_type: "bigint"
        tests:
          - not_null
      - name: "birth_date"
        description: "The birth date of the employee."
        data_type: "date"
        tests:
          - not_null
      - name: "first_name"
        description: "First name of the employee."
        data_type: "string"
        tests:
          - not_null
      - name: "last_name"
        description: "Last name of the employee."
        data_type: "string"
        tests:
          - not_null
      - name: "gender"
        description: "Gender of the employee."
        data_type: "string"
        tests:
          - not_null
      - name: "hire_date"
        description: "The date when the employee was hired."
        data_type: "date"
        tests:
          - not_null
      - name: "dept_no"
        description: "Department number that the employee belongs to."
        data_type: "string"
      - name: "dept_name"
        description: "Name of the department."
        data_type: "string"
      - name: "dept_manager_from_date"
        description: "Start date of the employee's tenure as a department manager."
        data_type: "date"
      - name: "dept_manager_to_date"
        description: "End date of the employee's tenure as a department manager."
        data_type: "date"
      - name: "dept_emp_from_date"
        description: "Start date of the employee's membership in a department."
        data_type: "date"
      - name: "dept_emp_to_date"
        description: "End date of the employee's membership in a department."
        data_type: "date"
      - name: "title"
        description: "Title of the employee's position."
        data_type: "string"
      - name: "title_from_date"
        description: "Start date of the employee's tenure in their current title."
        data_type: "date"
      - name: "title_to_date"
        description: "End date of the employee's tenure in their current title."
        data_type: "date"
      - name: "salary"
        description: "The salary of the employee."
        data_type: "bigint"
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 9999999
      - name: "salary_from_date"
        description: "Start date of the employee's current salary."
        data_type: "date"
      - name: "salary_to_date"
        description: "End date of the employee's current salary period."
        data_type: "date"

dbt debug
dbt compile

dbt docs generate
dbt docs serve