Data ingestion and transformation

A Depot can be used further for data ingestion and transformation through Flare Stack. To learn more about the Flare Stack, please refer to this link.

For example:

The below manifest file defines a Flare job, wf-vendor-insights, for ingesting and transforming vendor insights data from poss3 Depot into the Lakehouse Depot. It includes a DAG node, dg-vendor-insights, which processes promotional_data from a CSV dataset in poss3 and outputs transformed_data as an Iceberg table in Lakehouse. The Workflow specifies compute resources for execution, with a driver and executor configuration limiting cores and memory. The transformation logic applies an SQL query that extracts key vendor attributes, including organization details, contact information, certifications, and category identifiers, while also generating a row number using row_number() OVER (ORDER BY id). The resulting dataset is saved in Iceberg format with Parquet storage and gzip compression, ensuring optimized analytics and efficient querying.

version: v1
name: wf-vendor-insights
type: workflow
tags:
    - vendor.insights
    - Tier.Gold
description: The job is to ingest vendor-insights from poss3 into Lakehouse.
workflow:
  title: promotional
  dag:
    - name: dg-vendor-insights
      description: The job is to ingest vendor-insights from poss3 into Lakehouse.
      title: organization  data 
      spec:
      tags:
        - vendor.insights
      stack: flare:7.0
      compute: runnable-default
      stackSpec:
        driver:
          coreLimit: 1200m
          cores: 1
          memory: 1024m
        executor:
          coreLimit: 1200m    
          cores: 1
          instances: 1
          memory: 1024m
        job:
          explain: true
          inputs:
            - name: promotional_data
              dataset: dataos://poss3:promo_effectiveness/target_count02.csv?acl=rw
              format: csv
              options: 
                inferSchema: true

          logLevel: INFO
          outputs:
            - name: transformed_data
              dataset: dataos://lakehouse:promo_effectiveness/vendor_subscription_insight?acl=rw
              format: Iceberg
              description: The job is to ingest vendor data from S3 into lakehouse.
              tags:
                - vendor.insights
              options:
                saveMode: overwrite
                iceberg:
                  properties:
                    write.format.default: parquet
                    write.metadata.compression-codec: gzip
                title: vendor data

          steps:
            - sequence:
                - name: transformed_data
                  sql: >
                    select 
                    messagekey,
                    id,
                    organization_id,
                    unique_entity_id,
                    state_of_incorporation,
                    business_type,
                    no_dbe_designations,
                    emergency_disaster,
                    emergency_health,
                    emergency_other,
                    created_at,
                    updated_at,
                    doing_business_as,
                    doing_business_as_keyword,
                    ein,
                    duns,
                    name,
                    organization_name_keyword,
                    website,
                    address1,
                    address2,
                    city,
                    country_code,
                    state,
                    zip_code,
                    billing_address1,
                    billing_address2,
                    billing_city,
                    billing_country_code,
                    billing_state,
                    billing_zip_code,
                    description,
                    phone_country,
                    phone,
                    phone_ext,
                    fax_phone_country,
                    fax_phone,
                    fax_phone_ext,
                    categories_ids,
                    categories_codes,
                    nigp_categories,
                    naics_categories,
                    unspsc_categories,
                    users_emails,
                    users_ids,
                    subscribed_governments_ids,
                    certification_ids,
                    self_reported_certifications,
                    verified_certifications,
                    vendor_list_ids,
                    private_vendor_lists,
                    public_vendor_lists,
                    ethnicities,
                    row_number() over (order by id) as row_num 
                    from  promotional_data