Appreciate if anyone can help me on this

Hey @choudary kukkapalli!

1. Can we use custom sql query as source instead of a table, may be I can create a view in source db and use that as source but wanted to confirm if there is a way to use sql query as source , we need custom sql since we call few functions which is hard to re design in snowflake

Custom

SELECT

statements are not currently supported. See the comment in https://github.com/MeltanoLabs/tap-postgres/issues/185#issuecomment-1648546530, for example

2. can we do a

pre sql or post sql

?, we do have some huge tables where we don’t have a primary key , so to minimize the el job run time can extract last 3 months of data (based on creation date since we know older data won’t change) from source and delete the data with same extraction logic in target table prior to starting the target load , is there a way to achieve this ? (Transformer ??)

Not currently supported, but do log an issue in the target repo 🙂

3. also appreciate if someone can help me on overriding the table name in target , let’s say I have a table cust in source and I want it to be loaded into target as cust_source

You might wanna take a look at https://sdk.meltano.com/en/v0.36.0/stream_maps.html#aliasing-a-stream-using-alias

4. For incremental extraction (using a timestamp column last_update_date ) can we override the value we pass in to timestamp column? Ex:- can we override the delta logic to go few hours back from last run time , something like

where last_update_date>= add_hours(-6,LastRunTime)

Not supported, but do log an issue.

I few thoughts on this topic - I have had some of these same challenges that you have outline. Here was how I approached them. 1. We create custom views in the source system if we need to ingest data which isn't a 1:1 mapping to a table. We may also set the _METADATA setting in meltano to define a primary and replication key for the the table. That way we can do an incremental replication from the view. Example:

TAP_SYBASE__METADATA='{"dwh_extract-dwh_admission_source_tab": {"is-view":false, "replication-method": "FULL_TABLE", "table-key-properties": ["admission_source_code"]}}'

This above allows us to trick the tap that the view via the is_view: false - it is handy if you change the ingestion method to incremental and set an ingestion key. 2. See point one above about using the

_METADATA

setting to specify a primary key and/or replication_key. 4. Sometimes you can provide an incremental offset if the tap supports it. For example tap oracle - https://hub.meltano.com/extractors/tap-oracle#offset_value-setting . This is supported by other taps as well e.g. https://hub.meltano.com/extractors/tap-db2#offset_value-setting . This feature has not been built into tap-mssql but could be easily added as a feature.