Ellucian Ethos to Salesforce EDA (Reference Data)

#   _____           _              _____        __      
#  |  __ \         (_)            |_   _|      / _|     
#  | |__) |___  ___ _ _ __   ___    | |  _ __ | |_ ___  
#  |  _  // _ \/ __| | '_ \ / _ \   | | | '_ \|  _/ _ \ 
#  | | \ \  __/ (__| | |_) |  __/  _| |_| | | | || (_) |
#  |_|  \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ 
#                    | |                                
#                    |_|

# Project Name -  ELLUCIAN ETHOS TO SALESFORCE EDA (REFERENCE DATA)

# Recipe URL - https://app.lingk.io/a/10932/tf/17459

# Description - This recipe retrieves Ellucian Ethos Reference Data and writes back to Salesforce
              #1. Pulls all reference data models from ethos eg:- course title types, email types, countries.
              #2. Insert reference info in ethos object matching with ethos id.

# Industry - Higher Ed
# Business Process - Graduate Reporting

# Systems - Ethos, Salesforce
# Connectors - Ethos, Salesforce
# Data Flows - Single Direction
# Connection Type - API to API

# Data Models
## Ethos Models Used:
#1. subjects
#2. aptitude-assessment-types
#3. academic-credentials
#4. sources
#5. privacy-statuses
#6. course-title-types
#7. email-types
#8. phone-types
#9. religions
#10. citizenship-statuses
#11. countries
#12. country-iso-codes
#13. ethnicities
#14. races
#15. veteran-statuses

## Salesforce Objects Used:
#1. RecordType

# Add Recipe notes / Change log information here!

#    _____                            _                 
#   / ____|                          | |                
#  | |     ___  _ __  _ __   ___  ___| |_ ___  _ __ ___ 
#  | |    / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __|
#  | |___| (_) | | | | | | |  __/ (__| || (_) | |  \__ \
#   \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_|  |___/
#

# CONNECTORS specify what data will be pulled into the in-memory database during processing

connectors:

# Configure Ethos & Salesforce credentials in your Environment before running this recipe
  # Ethos Setup - https://help.lingk.io/en/articles/292-ellucian-ethos-connector-setup-guide
  # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide

###### Start - Ethos Reader connectors ######

-
    name: ethosDataReader
    type: ethosReader
    delayedRead: true
    properties:
      path: "{{executionContext.insertMetadata.ethosDataModelResource}}" 
      dataModelVersion: "{{executionContext.insertMetadata.ethosDataModelVersion}}" 
    schema:
      fields:
        - name: id
          type: String 
        - name: title
          type: String 
        - name: code
          type: String 
        - name: type
          type: String 
        - name: isoCode
          type: String 
        - name: abbreviation
          type: String

###### End - Ethos Reader connectors ######

###### Start - Salesforce Reader connectors ######

- 
    name: sfRecordTypes
    type: salesforceReader
    properties: 
      useBearerToken: true
      isSandbox: false
      query: Select Id, Name From RecordType where sobjecttype = 'Ethos__c' 
    schema:
      fields:
        - name: Id
          type: String

- 
    name: sfEthosDataReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      isSandbox: false
      query: "{{executionContext.insertMetadata.soqlQuery}}"  
    schema:
      fields:
        - name: ID__c
          type: String

###### End - Salesforce Reader connectors ######

###### Start - Salesforce Writer connectors ######

-
    name: sfWriter
    type: salesforceBulkWriter
    properties:
      onError: continue
      sfObject: "{{executionContext.insertMetadata.apiName}}"   
      batchSize: 200
      useBearerToken: true

###### End - Salesforce Reader connectors ######

#    _____ _        _                            _       
#   / ____| |      | |                          | |      
#  | (___ | |_ __ _| |_ ___ _ __ ___   ___ _ __ | |_ ___ 
#   \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __|
#   ____) | || (_| | ||  __/ | | | | |  __/ | | | |_\__ \
#  |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/

# STATEMENTS specify how the data should be processed while in memory

statements:

#******************************************************************** D I S C L A I M E R ***********************************************************************************************
#                                                                                                                                                                                       * 
# Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe.              *
# For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform      *
#                                                                                                                                                                                       * 
#******************************************************************** D I S C L A I M E R ***********************************************************************************************

- statement: (getRecordTypes) => SELECT * FROM sfRecordTypes
  #- statement: print getRecordTypes

##### BUNDLE: SUBJECTS #####

# this statement reads data into an in-memory staging table

# **************************************************************** N O T E ************************************************************

# The statement below is pulling Ethos metadata from Salesforce per recordType, in this scenario = 'Subjects'
 # this metadata will be used to fulfill the executionContext collection accordingly during the process

# **************************************************************** N O T E ************************************************************

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/subjects'   ethosDataModelResource,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Subjects'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# ************************************************************* * N O T E ****************************************************************

#                         With the metadata retrieved using the two statements above, the executionContext collection is being fulfilled
 #                         and the ethosDataReader & sfEthosDataReader connectors that are using it are being REFRESHED to pull the required data

# **************************************************************** N O T E ************************************************************

# this statement reads data into an in-memory staging table
  # the temp table executionContext is a collection that saves a value that will be used in the next statement
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader
  # this statement reads data into an in-memory staging table

# The recordType is being provided dynamically by the executionContext collection - Described on the {executionContext.insertMetadata.recordType} statements section below
  - statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              RecordTypeId,
            id                ID__c,
            title             Name,
            abbreviation      Code__c
          FROM ethosDataReader

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)
  - statement: |
        (transformedToInsert) => SELECT       
            transformed.RecordTypeId,
            transformed.ID__c,
            transformed.Name,
            transformed.Code__c
          FROM transformed
            LEFT ANTI JOIN sfEthosDataReader ON transformed.ID__c = sfEthosDataReader.ID__c

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: SUBJECTS #####

############################################################################### N O T E ##############################################################################

#       From this point until the end of the recipe the patterns that are explained in the NOTES above are being repeated for each of the Ethos RecordTypes in Salesforce

############################################################################### N O T E ##############################################################################

##### BUNDLE: APTITUDE ASSESSMENT TYPES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/aptitude-assessment-types'   ethosDataModelResource,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Aptitude Assessment Types'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader
  # this statement reads data into an in-memory staging table
  - statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              RecordTypeId,
            id                ID__c,
            title             Name,
            code              Code__c
          FROM ethosDataReader

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)  
  - statement: |
        (transformedToInsert) => SELECT       
            transformed.RecordTypeId,
            transformed.ID__c,
            transformed.Name,
            transformed.Code__c
          FROM transformed
            LEFT ANTI JOIN sfEthosDataReader ON transformed.ID__c = sfEthosDataReader.ID__c

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: APTITUDE ASSESSMENT TYPES #####

##### BUNDLE: ACADEMIC CREDENTIALS #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/academic-credentials'   ethosDataModelResource,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Academic Credentials'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader
  # this statement reads data into an in-memory staging table
  - statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              RecordTypeId,
            id                ID__c,
            title             Name,
            abbreviation      Code__c
          FROM ethosDataReader

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfAcademicCredentials) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: ACADEMIC CREDENTIALS #####

##### BUNDLE: SOURCES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/sources'   ethosDataModelResource,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Sources'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfSources) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: SOURCES #####

##### START BUNDLE: PRIVACY STATUSES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/privacy-statuses'  ethosDataModelResource ,
                     '10.0'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Privacy Statuses'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfPrivacyStatuses) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: PRIVACY STATUSES #####

##### BUNDLE: COURSE TITLE TYPES  #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/course-title-types'         `ethosDataModelResource`,
                     '1'                               `ethosDataModelVersion`,                     
                     Name                              `recordType`, 
                     'Ethos__c'                        `apiName`,
                     CONCAT("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid IN (SELECT Id FROM RecordType WHERE sobjecttype = 'Ethos__c' AND name IN ('",Name,"'))" )
                                                       `soqlQuery`
              FROM getRecordTypes
                  WHERE Name = 'Course Title Types'
  #- statement: print insertMetadata  
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader

- statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              `RecordTypeId`,
            id                `ID__c`,
            title             `Name`,
            code              `Code__c`,
            description       `Description__c`
          FROM ethosDataReader

#- statement: print transformed

# The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)
  - statement: |
        (transformedToInsert) => SELECT       
            transformed.RecordTypeId,
            transformed.ID__c,
            transformed.Name,
            transformed.Code__c,
            transformed.Description__c
          FROM transformed
            LEFT ANTI JOIN sfEthosDataReader ON transformed.ID__c = sfEthosDataReader.ID__c

#- statement: print transformedToInsert
  - statement: (transformedToInsertCount) => SELECT COUNT(*) FROM transformedToInsert
  #- statement: print transformedToInsertCount
  - statement: INSERT transformedToInsert INTO sfWriter

##### END BUNDLER: COURSE TITLE TYPES #####

##### BUNDLE: EMAIL TYPES ##### 
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/email-types'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Email Types'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata  
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: EMAIL TYPES #####

##### BUNDLE: PHONE TYPES #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/phone-types'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Phone Types'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: PHONE TYPES #####

##### BUNDLE: RELIGIONS #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/religions'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Religions'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: RELIGIONS #####

##### BUNDLE: CITIZENSHIP STATUSES #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/citizenship-statuses'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Citizenship Statuses'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

##### END BUNDLER: CITIZENSHIP STATUSES #####

##### BUNDLE: COUNTRIES #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/countries'  ethosDataModelResource ,
                     '1.0.0'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Countries'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader
  # this statement reads data into an in-memory staging table
  - statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              RecordTypeId,
            id                ID__c,
            title             Name,
            isoCode           Code__c
          FROM ethosDataReader

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfCountries) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: COUNTRIES #####

##### BUNDLE: COUNTRY-ISO-CODES #####

- statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/country-iso-codes'  ethosDataModelResource ,
                     '1.0.0'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Country ISO Code'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

- statement: REFRESH ethosDataReader
  - statement: REFRESH sfEthosDataReader
  # this statement reads data into an in-memory staging table
  - statement: |
        (transformed) => SELECT
            (SELECT id FROM getRecordTypes WHERE NAME = '{{executionContext.insertMetadata.recordType}}')
                              RecordTypeId,
            id                ID__c,
            title             Name,
            isoCode           Code__c
          FROM ethosDataReader

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfCountryIsoCode) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: COUNTRY-ISO-CODES #####

##### START BUNDLE: ETHNICITIES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/ethnicities'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Ethnicities'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfEthnicities) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: ETHNICITIES #####

##### START BUNDLE: RACES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/races'  ethosDataModelResource ,
                     '6'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Races'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformedToInsert

- statement: insert transformedToInsert INTO sfWriter

- statement: REFRESH sfEthosDataReader

- statement: (sfRaces) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: RACES #####

##### START BUNDLE: VETERAN STATUSES #####

# this statement reads data into an in-memory staging table
  - statement: |
          (insertMetadata) => 
              SELECT 
                     '/api/veteran-statuses'  ethosDataModelResource ,
                     '9.0.0'              ethosDataModelVersion,                     
                     name             recordType, 
                     'Ethos__c'       apiName,
                     concat("SELECT ID__c, Name FROM Ethos__c WHERE recordtypeid in (Select Id From RecordType where sobjecttype = 'Ethos__c' and name in ('",Name,"'))" )
                                      soqlQuery
              FROM getRecordTypes
                  WHERE Name = 'Veteran Statuses'
  # this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print insertMetadata

# this statement reads data into an in-memory staging table
  - statement: INSERT insertMetadata INTO executionContext

# this statement prints the table for debug purposes. Production doesn't need it.
  #- statement: print transformed

#- statement: print transformedToInsert  
  - statement: insert transformedToInsert INTO sfWriter
  - statement: REFRESH sfEthosDataReader
  - statement: (sfVeteranStatuses) => SELECT * FROM sfEthosDataReader

##### END BUNDLER: VETERAN STATUSES #####

# Add more statements to convert, join, aggregrate, transform, and integrate your data