Feature Table

# Project name
name: sample_id_stitching
# Project's yaml schema version
schema_version: 54
# 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_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
# lib packages can be imported in project signifying that this project inherits its properties from there
packages:
  - name: corelib
    url: "https://github.com/rudderlabs/profiles-corelib/tag/schema_{{best_schema_version}}"
    # if required then you can extend the package definition such as for ID types.

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
      entity_key: user
      features:
        - user_lifespan
        - days_active
        - min_num_c_rank_num_b_partition
var_groups:
  - name: user_vars
    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: '{{user.Var("last_seen")}} - {{user.Var("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
          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
      - entity_var:
          name: first_bill
          select: min({{tbl_billing.Var("payment")}})
          from: inputs/tbl_billing
          column_data_type: '{{warehouse.DataType("float")}}'

- 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:
        order_by:
        - first_num_b_order_num_b asc