Introduce transforms in dozer config unifying all transformations in one place.

[v3g42]

• Introduce transforms block in dozer config which is a series of SQL statements
• Remove SQL from endpoint configuration
• Users have to explicitly space INTO table_name in queries\

Motivation

• This allows us to reuse tables created between different endpoints.
• Allow majority of the pipeline configuration happen through SQL
  • For example, creating secondary indexes on cache can be create index operation. This might offer better experience
• With a little bit of work, DAG visualisation is much better done. We can use this in product UI as well as CLI.

Sample configuration looks like this

app_name: 1-hypercharge-postgres-sample
connections:
  - db_type: Postgres
    authentication: !Postgres
      user: postgres
      password: postgres
      host: 0.0.0.0
      port: 5434
      database: stocks
    name: stocks
transforms: |
  select id, ticker, date, open, high, low, close, adj_close, volume 
  INTO stocks_sink
  from stocks where 1=1;


  select symbol, security_name 
  INTO stocks_meta_sink
  from stocks_meta 
  where 1=1;

sources:
  - name: stocks
    table_name: stocks
    columns:
      - id
      - ticker
      - date
      - open
      - high
      - low
      - close
      - adj_close
      - volume
    connection: !Ref stocks
  - name: stocks_meta
    table_name: stocks_meta
    columns:
      - nasdaq_traded
      - symbol
      - security_name
      - listing_exchange
      - market_category
      - etf
      - round_lot_size
      - test_issue
      - financial_status
      - cqs_symbol
      - nasdaq_symbol
      - next_shares
    connection: !Ref stocks

endpoints:
  - name: stocks
    path: /stocks
    table_name: stocks_sink
    index:
      primary_key:
        - id
  - id: null
    name: stocks_meta
    path: /stocks-meta
    table_name: stocks_meta_sink
    index:
      primary_key:
        - symbol

[chubei]

I have no comment regarding using sql to define indexes as I'm not familiar with sql.

The idea of defineing sink as a table sounds great to me. This opens the possibility of using sinks as sources. All we have to do is to implement a DozerCacheConnector or CacheSource. I believe it enables many more use cases and strongly advocate this design.