In general the Dataiku connections are ways to abstract various kinds of data stores so that Dataiku can deal with them in similar ways. For example file system connection locally behave much as an SFTP connection in Dataiku. SQL connections to PostgreSQL and Microsoft SQL server behave roughly the same, and switching between connections one data store and the other may be more easily done. Connections than recoding.
Some of our colleagues her may want to jump in here about the value when it comes to Application Nodes and API nodes in a Dataiku cluster.