Data types from the SyH-DR Data Dictionary?

[chrisgebert]

Since I'm new to this project, I'm curious why it's preferable to pull the data types for the SyH-DR files by parsing the SyH-DR Codebook.pdf file rather than the SyH-DR Data Dictionary.xlsx or say the Person-level Variables csv. I would think the csvs or Excel file would be more simply parsed but maybe that's not true and I'm missing necessary context.

Thanks for any additional background!

[jaanli]

Great call @chrisgebert - indeed, that would be preferable :)

(I think the CSV was released in the past few weeks so haven't had time to switch over; pull requests welcome and appreciated!)

Please let us know if you have any further questions 🙏

[chrisgebert]

@jaanli sounds good, I'll rewrite the script to generate the data models to use the data dictionary sources and then output a sources.yml file for dbt.

[wesleycheung0]

@chrisgebert There is another issue related to how the parsing script is set up that I would like to point out. Most of the variable names with an integer suffix do not have aliases in the output sql file. This issue is related to how the script searches for variables in the data table parsed from the pdf file. I believe this issue will be resolved if we use the data dictionary spreadsheet as the input file.

I have a solution implemented in my local repository and would be happy to share and to pair programming to update the parsing script if needed.

[chrisgebert]

Thanks @wesleycheung0, that should work for me. I wrote a function that will find the data dictionary files that are linked on the SyH-DR page and write a concatenated file to the project directory, but I don't yet have access to the synthetic data files themselves, so I haven't been able to do a full integration test on my changes.

If you want, I can share my function code with you for testing. Let me know what you think.

[jaanli]

Also @chrisgebert feel free to send me an email if you want to test more easily on SyH-DR as well!

[wesleycheung0]

@chrisgebert That would be great, I am happy to test out your function.

[chrisgebert]

@wesleycheung0 thanks, the function I wrote:

1. defines constants for the AHRQ base urls
2. scrapes the SyH-DR page to get a list of the data dictionary urls
3. concatenates those files into a dataframe (and optionally outputs a csv to be stored in the project directory)

I'm imagining this function would be called where the column_definitions dictionary is initialized now but the following logic would need to be updated to use the dataframe (or maybe dict object, whichever is simpler) that is returned by the function while processing each of the csv files.

def create_data_dictionary():
    """
    1. Set CONSTANTS for project page
    2. Get links to data dictionary csv files
    3. Create list of data dictionary urls
    4. Concat all data dictionary files into a dataframe
    """

    AHRQ_BASE_URL='https://www.ahrq.gov'
    SYH_DR_URL='https://www.ahrq.gov/data/innovations/syh-dr.html'

    # Get all links to Data Dictionary csv files
    response = requests.get(SYH_DR_URL)
    soup = BeautifulSoup(response.text)

    # Create list of data dictionary urls (i.e. where attribute is like "Variables")
    data_dictionary_urls = []
    for url in soup.find_all("a", string=re.compile("Variables")):
        csv_url = AHRQ_BASE_URL + url.attrs.get("href")
        data_dictionary_urls.append(csv_url)

    # Concat all data dictionaries into a dataframe and write to a file
    ssl._create_default_https_context = ssl._create_unverified_context
    data_dictionary_df = pd.concat((pd.read_csv(url) for url in data_dictionary_urls), ignore_index=True)
    data_dictionary_df = data_dictionary_df[data_dictionary_df["Variable Name"].notnull()].to_csv("./data_processing/seeds/SyH-DR_data_dictionary.csv", index=False)

    return data_dictionary_df[["Variable Label", "Variable Name", "Variable Type", "Variable Format"]]

[wesleycheung0]

Thanks @chrisgebert, I am gonna use your function and make necessary changes to the model generation logic.

[chrisgebert]

Thanks @wesleycheung0, let me know if you have any questions.

My bigger goal is to generate a sources.yml file from the data dictionary files to be used by dbt that will define:

1. a source (AHRQ, MITRE, etc)
2. each table that comes from that source
3. the columns (and their data types) plus any other metadata that might be important to be documented

https://docs.getdbt.com/reference/resource-properties/columns

Kind of like this:

version: 2

sources:
  - name: <source_name>
    tables:
    - name: <table_name>
      columns:
        - name: <column_name>
          description: <markdown>
          data_type: <string>
          quote: true | false
          tests: ...
          tags: ...
          meta: ...
        - name: <another_column>
          ...

[wesleycheung0]

That's a good point @chrisgebert. I am almost done with changing the source of the data dictionary, and I am facing a blocker where the dbt model is consuming too much RAM with the default settings. I'm unsure where the issue is originating. I will look at the source.yml when I am done. Feel free to suggest any reason for this RAM issue. Please refer to my latest update in this branch.

[chrisgebert]

Are you getting an out-of-memory error from DuckDB? Usually it's best to match the number of threads to the cores on your machine, but sometimes it's best to limit the number of threads to 1.

Is it one particular model that is causing this or are you building all models in the project? It might be beneficial to:

1. Limit the number of threads or
2. Build a single model at a time until we can further optimize the queries

[jaanli]

@wesleycheung0 you might also want to make sure you are using the latest duckdb.

1. Can you send us the command you are running to install duckdb on the command line please?

2. Can you send us a one-liner that replicates the memory error, using ideally only duckdb -c syntax?

That'll help replicate the issue. I ran into something similar and had to make sure it was writing to a temporary buffer file with data larger than RAM :) we'll get this debugged or file a duckdb bug if something is not working right for us.