Document pre_exec and post_exec support for duckdb models

[k-anshul]

• Document pre_exec and post_exec support for duckdb models
• Add some examples on using pre_exec like using native duckdb connectors
• Add some examples on using post_exec like changing datatype of ingested data from String to Enum.

[royendo]

@k-anshul
can you give some clarity on the differences between

• init_sql (split into db and conn)

• db_init

• conn_init

• pre_exec

• post_exec

It sounds like the order is:

1. db_init
2. conn_init
3. pre_exec
4. sql
5. post_exec

Any reasons to use one or the other?

[k-anshul]

Hey @royendo

So the db_init and conn_init is an internal construct and not exposed to user yet. The use case is to separate global duckdb configuration from local duckdb configurations.
Following properties are exposed to the user:

1. init_sql : Defined in connector.yaml. Executed after any new duckdb connection is created.
2. pre_exec: Defined in model.yaml. Executed before model is created.
3. sql: The model will be created using the results of this SQL.
4. post_exec: Executed after model is created.

The init_sql was created before we had support for pre_exec and post_exec to create secrets. Ideally users should use pre_exec to configure secrets to keep the changes limited to a single model. Maybe you can keep init_sql as undocumented feature.

[royendo]

thanks!
making the changes to the docs here:
#6917