SDOH datasets in lakehouses
After you run the pipeline to ingest a social determinants of health (SDOH) dataset, you can explore the bronze and silver lakehouses data.
Bronze lakehouse
The bronze lakehouse is staging for the ingestion process. You should only need to use this lakehouse directly if troubleshooting an ingestion problem. When the ingest process is complete, the system copies the file that you placed in the Ingest folder to the Process folder and then organizes it by the date it was processed. The system copies the file that you ingested and then prefixes it with a unique value to keep it from conflicting with multiple runs. If your pipeline failed, the system places the file in the Failed folder.
The SdohDatasets table contains the data staged from your SDOH dataset.
The following image shows the SdohDatasets table in the bronze lakehouse.
This table multiple rows that represent the data from your dataset ingested. Your dataset data is stored in the datasetRowContent column. The datasetName, datasetMetadata and datasetLayout columns are repeated on each row and represent the metadata for your dataset. All of these fields are represented as JSON columns as they are intended for internal use by the pipeline logic to populate the silver lakehouse.
Note
While you reference and check datasets in the bronze lakehouse to troubleshoot or validate your SDOH dataset ingestion, you must not use it for analytical purposes. You must use the silver lakehouse.
Silver lakehouse
The silver lakehouse contains the enriched data that's refined by transforming the data that you ingest into the bronze lakehouse. The tables for SDOH in the silver lakehouse tables are a normalized model that Microsoft creates. These tables represent SDOH datasets in the healthcare data solutions data model.
The following image represents the tables that make up the SDOH data model.
These tables contain rows that represent all SDOH datasets that you ingest. By adopting a category, subcategory, and harmonization key strategy when preparing your data, you can use these three items to help you navigate the SocialDeterminant table as you build queries to combine with your other healthcare data. For example, querying all social determinants with the same harmonization key can give you multiple determinants from different SDOH datasets.
In the next unit, you run queries to use the silver lakehouse to analyze your patient population by using the social determinants that you loaded in the previous exercise.