In short, source_concept_id represents the concept from source data which can be found in OMOP Vocabularies. For example, source data has ICD10 code ‘M05.04’ (“Felty syndrome, hand”). Looking this code up in OMOP Vocabularies, we get concept_id=42616532 – this is the source_concept_id, as it’s obtained from source data. This concept, however, is non-standard, but Concept Relationship table provides the mapping to a standard concept 81097, which can be put in concept_id field.
For this example, source_value=‘M05.04’, source_concept_id=42616532, concept_id=81097.
A brief description of different field types in CDM can be found here on GitHub.