have some general queries

Hi faisalf, you would likely get more replies here if these questions were separated into their own threads, however I'll attempt some answers:

Ques 1: At what phase Import Customization File are clubbed while performing Compare and Deploy (Not Manual)?Because I did not see any options for the same as I can see for objects/database scripts and pug-ins.

Import Customization File dependancy is noted during the Inspect Package step, and downloadable/required on the Review Details step of package deployments.

Ques 2: When a stored procedure is used or when views are used or when Materialized Views are used in Appian and why they are used?
Need scenario Based examples!.

Views are commonly used when you need to join data in multiple tables, a view handles this cleanly and you can connect a CDT to the view to retrieve the joined data. For example, when we store Employee ID with a process instance and a reporting view will join this employee ID to a Users table to retrieve their name, location, etc - without having to duplicate data points and create stale data.

Materialized views are not something I've had to implement in over a decade, I can't think of any current use cases off the top of my head, but it would be in the realm of having some use case where the view should only be updated with new data at certain intervals. I don't anticipate ever having to use these with Appian.

For an example of stored procedures, one we use is to convert Appian-stored datetime values as these are in GMT in some situations. For say an eastern timezone report which needs to be aggregated on day/month, we will call the conversion procedure over the datetime field to use appropriately within a SQL view under DATEPART(). We have another procedure which is used in Round Robin task assignments which updates the user group within the DB and determins previous/next assignees. Task Assignment and escalation expressions I've found to not be as friendly with complex SAIL expressions such as a!localVariables, with() and load().

Ques 3: What is main reason we need Reports and Records separately in Appia (Because can display data)?

Records are your underlying data elements within Appian. Reports are esseltially open-ended interfaces built on top of records to add enhanced display capabilities. The "Report" element is also required to use for customized Site page that is not a Record or Action.

Ques 4: When we have duplicate rows in a DB except their Primary Ids, then how can we return a complete Unique Row using a!queryEntity() function or any other functions.

In this scenario, you can either utilize a!queryEntity() and apply a!queryAggregation() on each column separately with isGrouping set to true for all, without referencing the unique ID column, or apply this as a view such as below where the entire row is returned including the ID with the addition of a count() column which returns the quantity of rows with the same values (except for the ID) - this can be utilized in further display processing. Note the queryEntity() approach will have restrictions as to where you can implement this as you will not be able to return a unique ID if you want a single row.

select u1.id,u1.firstname,u1.lastname,
(select count(*) from tblusers u2 where u1.firstname=u2.firstname and u1.lastname=u2.lastname) 'countRecords'
from tblusers u1

Ques 5: What happens to the pending Process instances if the current Process Model version is changed to newer version?

Active process instances will continue to operate as a snapshot of the process model configuration at it's initiation time, with a few exceptions. If this running process calls a sub process that has been updated, the updated subprocess will be used in the legacy parent process - this should be considered during updates. Interfaces also use the updated version, within legacy instances, but are sent the legacy CDT configuration.

Ques 6: What are the differences we will experience or when the system migrated to cloud from in-premises?

Less server maintenance :). We're still on-prem at this time so I would let others with more Appian cloud experience chime in here.

Ques 7: What impact will be on running processes if we made changes to the structure of CDT which is used by these processes and also on sub-processes?

Any active process will retain the CDT definition as it was at initiation time. A sub process acts the same (if it was initiated prior to the update), however if your parent process is utilizing a legacy CDT, when it calls a new sub process that also references the CDT, the sub process will have the updated CDT version. Also for interfaces, if you design for a new data point to be collected, legacy process instances will not pass in the new CDT field but the new interface code will be utilized. This should be considered in your upgrades. For this reason, I always store a "version" integer flag in my CDT's - when a change is made that requires new fields to be referenced, the version flag is incremented (stored at process initiation time) and then say, if version is less than 3, don't attempt to show or access the field implemented in version 3. Additionally the property() function can be used to retrieve CDT values which may not be present as a default can be set to prevent errors.

Ques 8: What general issues we see between DB and Appian and what could be solution?

In generally I haven't seen really any issues between our Appian primary data store and Appian itself. The only issues I've seen between DB's and Appian are only when externally-controlled databases may have an unexpected outage. In this case there's not much you can do about the external DB, but with one of our needy DBs that has some outages and requests us to halt all DB writes during some periods, we have rule nodes implemented prior to these DB calls, the rule nodes reference a constant say cons!IS_DB_ONLINE true/false, which we can flag in one area to suspend activities with the DB - and release all at once as the activities end.