Feature table

# Project name
name: sample_id_stitching
# Project's yaml schema version
schema_version: 42
# Warehouse connection
connection: test
# Allow inputs without timestamps
include_untimed: true
# Folder containing models
model_folders:
  - models
# Entities in this project and their ids.
entities:
  - name: user
    id_stitcher: models/sample_id_stitcher # name of ID stitcher model, prefixed with relative path of models folder where it's defined
    id_types:
      - main_id # You need to add ``main_id`` to the list only if you have defined ``main_id_type: main_id`` in the id stitcher buildspec.
      - user_id # one of the identifier from your data source.
      - email
id_types:
  - name: main_id
  - name: user_id
    filters: # Multiple filters like exclude, include can be added using value or regex match.
    - type: exclude
      value: ""
  - name: email
    filters: # An include filter example to consider values matching provided regex. Note that this will automatically be anchored by sql to include the beginning and ending regex special chars (^ and $).
    - type: include
      regex: ".+@.+" # Automatically anchored, so equivalent to "^.+@.+$"

inputs:
- name: rsIdentifies
  contract: # constraints that a model adheres to
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
      - name: email
  app_defaults:
    table: rudder_events_production.web.identifies # one of the WH table RudderStack generates when processing identify or track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id" # kind of identity sql to pick this column from above table.
        type: user_id
        entity: user # as defined in project file
        to_default_stitcher: true
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true
      - select: "lower(email)" # can use sql.
        type: email
        entity: user
        to_default_stitcher: true
- name: rsTracks
  contract:
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
  app_defaults:
    table: rudder_events_production.web.tracks # another table in WH maintained by RudderStack processing track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id"
        type: user_id
        entity: user
        to_default_stitcher: true
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true

models:
  - name: user_profile
    model_type: feature_table_model
    model_spec:
      validity_time: 24h # 1 day
      entity_key: user
      vars:
        - entity_var:
            name: first_seen
            select: min(timestamp::date)
            from: inputs/rsTracks
            where: properties_country is not null and properties_country != ''
        - entity_var:
            name: last_seen
            select: max(timestamp::date)
            from: inputs/rsTracks
        - entity_var:
            name: user_lifespan
            select: last_seen - first_seen
            description: Life Time Value of a customer
        - entity_var:
            name: days_active
            select: count(distinct timestamp::date)
            from: inputs/rsTracks
            description: No. of days a customer was active
        - entity_var:
            name: campaign_source
            default: "'organic'"
        - entity_var:
            name: user_rank
            default: -1
        - entity_var:
            name: campaign_source_first_touch
            select: first_value(context_campaign_source)
            window:
                order_by:
                    - timestamp asc
                partition_by:
                    - main_id
            from: inputs/rsIdentifies
            where: context_campaign_source is not null and context_campaign_source != ''
        - input_var:
            name: num_c_rank_num_b_partition
            select: rank()
            from: inputs/tbl_c
            default: -1
            window:
              partition_by:
                - '{{tbl_c}}.num_b'
              order_by:
                - '{{tbl_c}}.num_c asc'
            where: '{{tbl_c}}.num_b >= 10'
        - entity_var:
            name: min_num_c_rank_num_b_partition
            select: min(num_c_rank_num_b_partition)
            from: inputs/tbl_c
      features:
        - user_lifespan
        - days_active
        - min_num_c_rank_num_b_partition

- entity_var:
    name: first_num_b_order_num_b
    select: first_value(tbl_c.num_b) # Specify frame clause as aggregate window function is used
    from: inputs/tbl_c
    default: -1
    where: tbl_c.num_b >= 10
    window:
        order_by:
        - tbl_c.num_b desc
        frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- entity_var:
    name: first_num_b_order_num_b_rank
    select: rank() # DO NOT specify frame clause as ranking window function is used
    window:
        partition_by:
        - first_num_b_order_num_b > 0
        order_by:
        - first_num_b_order_num_b asc