Aches and pains. What, if anything, is the difference between those? And do you know a “myocardial infarction” from a “heart attack”? What about an “MI”? Is that shorthand, or part of the address of a hospital in Michigan?

For people, it can be easy to understand the distinctions and nuances between similar words, phrases, and concepts, even technical ones, like those found throughout the medical field. Yet even for the most advanced AI, the contextual clues that give humans accurate comprehension of words and images remain an elusive challenge. It’s a challenge well worth solving, though: As much as 80% of all healthcare data is said to be unstructured.

It’s the kind of complicated data management challenge that natural language processing was built to solve.

Healthcare and life sciences organizations are generating vast amounts of unstructured data as part of clinical and operational workflows, which presents an enormous opportunity to derive meaningful insights for medical research, population health, and patient care. For example, clinical notes and lab reports have useful, actionable information that, when unlocked, can help improve the overall quality of patient care, accelerate the discovery of new treatments, and increase the efficiency of healthcare delivery.

This approach is at the heart of Google Cloud’s Healthcare Natural Language API, in enabling healthcare organizations to build open, intelligent systems that unlock value from healthcare data. The open cloud approach enables our partners to innovate more easily, and scale more efficiently. We believe this approach will further advance interoperability—and ultimately lead to healthier and fuller lives.

Unlockinging value from clinical documents and research materials
Over the past two years, we have seen just how powerful AI can be in expediting drug discovery efforts for COVID-19, forecasting and modeling COVID-19 cases, and building better models for a host of public health measures. The opportunities extend well beyond battling the pandemic, too, to helping combat cancers, diabetes, and disabilities, and accelerating drug discovery.

As healthcare and life sciences organizations look to incorporate new data sources in their analytics and AI workflows, Google Cloud has been investing in providing open, flexible, and easy to use API services that customers and partners can integrate into their solutions, to accelerate their development with the power of Google’s AI technology.

The Cloud Healthcare Natural Language API is one such example, and aims to provide fully managed services that deliver the latest advances in natural language processing in an easy to use and easy to integrate manner. Healthcare organizations can then build intelligent systems to improve care and reduce cost while not having to worry about the complexities of the underlying and fast-changing technology, thus enabling more open innovation in the development of healthcare applications.

A number of healthcare innovators are exploring the potential for natural language processing.

“Patients come to Mayo Clinic with a history, and that history is well-documented, but often buried in clinical notes. Extracting information from unstructured healthcare data across thousands of patients is a complex problem,” says Vish Anantraman, M.D., Chief Technology Officer at Mayo Clinic. “Custom natural language processing solutions have a great potential to extract higher quality insights from these notes and to deliver more timely, and holistic patient care.”

The best insights can often be the unexpected ones, and that is precisely what Hackensack Meridian Health, in northern New Jersey, is looking for.

“Doctor’s notes are a rich space to create structured information from their natural workflow,” says Michael Draugelis, vice president for predictive health at Hackensack Meridian Health. “We are designing new AI-powered solutions to connect clinical teams, patients, and the community automatically from these insights—without creating cumbersome screen clicks and prompts. This automation allows our clinical teams to focus on connecting with the patient.”

Hospital leaders there are testing Google’s NLP API to gather information such as social determinants of health and behavioral health signals from large amounts of clinical notes, with approximately 35 million processed. Seeking to achieve the greatest value from natural language processing, the team at Hackensack Meridian Health have specifically focused on extracting information that is inherently not easy to capture in more traditional electronic health records.

“The extracted insights from the Google NLP API creates a foundational component to map clinical protocols, pathways, and outcomes, to better understand and improve patient care,” Draugelis says.

And at the National Institutes of Health and elsewhere, researchers are exploring how natural-language-derived variables could offer an additional predictive value over and above the Veteran Health Administrations’s structured EMR-based suicide prediction model.

To help healthcare organizations achieve goals like the ones above, we at Google draw on the expertise of tens of thousands of data scientists across the company who work every day on building better AI and decades of AI research in language understanding to power the development of services such as the Cloud Healthcare Natural Language API.

According to independent benchmarking of Cloud providers offering fully managed healthcare natural language service by tech analysts GigaOm, the Google Cloud Healthcare Natural Language API was among the most accurate in the industry, outperforming other service providers in terms of correctly classified medical entities and relationships, and with very few misclassifications.

Using AI to connect systems and enhance healthcare interoperability

As an industry, healthcare and life sciences organizations have been talking about the importance of data and data interoperability for a while. But our experiences from the past couple years have demonstrated that we cannot be fully prepared for the next global health crisis without greater connections within and between organizations.

Starting with the Healthcare Data Engine, organizations have been integrating and harmonizing data securely across many of their sources—patients, members, operations, research, and public databases—so they can quickly analyze it to get insights, and then make smarter, faster decisions.

This is the start of a broader vision for a new kind of healthcare and life sciences connected world where enterprises, institutions, and startups will securely collaborate to deliver on the next generation of care. Such a future relies on cloud-based solutions that are as open and flexible as they are user-friendly, compliance-ready, and secure.

We envision a future where healthcare organizations can seamlessly connect data from various systems, unlock the value from data regardless of source or format, and break down barriers in healthcare interoperability and AI to improve healthcare and save lives.

With these goals in mind, we continue to enhance the hybrid data clouds that customers are building to organize and analyze their information, and we and our partners continue to build on our data capabilities. Given the complexities both within the field and within each organization, we believe the greatest value comes from having partners and tools available to build the AI and NLP technologies most relevant to your unique needs.

Editor’s note: Today’s post is by Mike Francomb, Senior Vice President of Technology, RecruitMilitary and a U.S. Army Veteran. RecruitMilitary is a wholly owned subsidiary of Bradley-Morris, Inc. (BMI), the largest military-focused recruiting company in the United States. RecruitMilitary uses Google Cloud Talent Solution to power its job search experience and connect more organizations with veteran talent.

For seven years, I served in the U.S. Army as a Field Artillery Officer, Military Occupation Code 13A. My time in service included a deployment to Operation Desert Shield / Desert Storm with the 24th Infantry Division out of Fort Stewart, GA, and a variety of front line artillery leadership roles, serving as a logistics officer for my unit and as an instructor teaching new officers how to be professional artillerymen. My day-to-day entailed leading teams of highly trained soldiers and managing logistics and materials to help those soldiers perform at a high level in stressful, fast-paced environments. It was my job to ensure we were ready to handle any circumstance. 

The hardest part about transitioning out of the Army in May 1996 as a highly trained artillery veteran was the fact that, though I felt prepared for any challenge ahead, I wasn’t sure I was making the right choice. I made a common mistake of transitioning veterans, I jumped right into an entrepreneurial venture. Looking back, I wish I’d had access to resources that displayed career options that would help translate my skills for the corporate world, it would have helped me be better prepared and know what my options were. I wasn’t ready to jump from the Army into running a business, and it was a long two years. 

Though my first job out of the Army was challenging, it taught me that I loved the start-up environment, and I joined RecruitMilitary in October 1998 when it was five months old. For the past 21 years, I have been fortunate enough to play an important role in helping RecruitMilitary grow to what it is today, the industry leader in connecting military veterans with organizations.  

RecruitMilitary connects organizations with veteran talent through over 30 products and services, all of which are fueled by our job board. Our job board, with over 1,400,000 members, is core to our business. In fact, if we don’t have an active and growing job board population, we don’t have the supply of veteran talent we need to deliver to our clients across our suite of services. 

With veteran unemployment at a 50-year low, it became increasingly challenging for RecruitMilitary to grow our veteran job seeker database and keep those veterans actively applying to client jobs. Being a data-driven company, we saw our existing search functionality was no longer producing the desired results for clients and began to receive client feedback about decreased candidate activity.

It was clear to us that we needed to begin adopting machine learning and more advanced search capabilities into our products and operations. The HR Tech space is shifting that way fast, and we want to be at the forefront. As we researched paths to take and learned of Google’s operating philosophy leading with AI, and that they were developing a tool for veteran job search, it made a lot of sense to go with a leader.

When Grow with Google announced its commitment to support veterans, we learned that we could add their military occupation code (MOS) translation feature to our job board through Cloud Talent Solution. This feature lets transitioning service members enter their military occupation codes (MOS, AFSC, NEC, or rating) directly into our search bar to see relevant civilian jobs available at client companies. We’re also using Cloud Talent Solution’s remote work functionality to provide an improved job search experience that allows our customers to make remote work opportunities in the U.S. more discoverable on their career sites. We’re excited about this feature, as it enhances our ability to deliver meaningful jobs to important members of our military community, military spouses, and veterans with limited mobility. 

The results of Cloud Talent Solution compared to our previous search are tremendous. Our job seekers are getting a much better experience, and to us that means more veterans are connected to jobs with our clients. We know this because our number of daily job applications has grown by 78 percent. And knowing that we now have a tool that’s going to learn and get better as more of our job seekers use it means that we will reap benefit for work done over time, and so will our clients and veterans who use our job board. That’s tremendous ROI to receive for a lean development staff. 

These are just a few of the types of tools I wish I’d had access to when I was considering my transition in 1996. With the help of technology and resources, like those from RecruitMilitary and Grow with Google, people in the military community, including veterans like myself, can prepare for and build meaningful careers.

Advances in artificial intelligence (AI) and machine learning (ML) have led to increased adoption in the financial services sector. A prominent use for this technology is to assist in key compliance and risk functions, including the detection of fraud, money laundering, and other financial crimes and illicit finance, as well as trade manipulation — collectively referred to as “Risk AI/ML.” As the use of these models grows, so do questions about managing risks associated with the models. 

In particular, regulators, financial institutions, and technology service providers have been looking into whether existing Model Risk Management (MRM) guidance — which has traditionally been the regulatory regime applicable to managing model risk in the financial services industry — continues to be relevant for AI/ML models. And, if so, how should the guidance be interpreted and applied to this new technology?

As the financial sector increasingly adopts artificial intelligence and machine learning techniques, it is critical for regulators, financial companies and technology providers to work together to assure that there are clear rules of the road,” says Jo Ann Barefoot, AIR CEO and co-founder. “Updated guidelines on the responsible use of these models can help prevent novel technologies from causing harm, and can also open up better ways to combat risk in areas like money laundering, illicit finance, and fraud.

Our new white paper, written in partnership with the Alliance for Innovative Regulation (AIR), seeks to address that question, with the aim of fostering thought and dialogue among agencies, the financial services industry, risk model vendors, and entities interested in the performance, outputs, and compliance of models used to identify, mitigate, and combat risks in financial services. This white paper does not address issues that may arise with other applications of AI/ML in the financial services industry, such as consumer credit underwriting or models using generative AI or Large Language Models, which are better addressed iteratively. 

The paper argues that MRM guidance, given its broad, principles-based approach, continues to provide an appropriate framework for assessing financial institutions’ management of model risk, even for Risk AI/ML models. Working within an existing framework takes advantage of the knowledge and operational capabilities of institutions that already understand this framework, instead of having to create an entirely new approach, which generally takes longer to implement and make effective. Nonetheless, the paper recognizes that AI/ML models have unique traits and characteristics compared to conventional models, including their potential dynamism and pattern recognition capabilities. These distinctions must be in focus when considering how MRM guidance should be applied to Risk AI/ML models. 

Taking into account those unique aspects of AI/ML models, the paper offers specific observations and recommendations regarding the application of MRM guidance to Risk AI/ML models, including:

• Risk assessment: In assessing risk, it is important to recognize that AI/ML models are not inherently more risky than conventional models. A risk-tiering assessment must consider the targeted business application or process for which a model is used, as well as the model’s complexity and materiality. To assist in these assessments, regulators could clarify that the use of AI/ML alone does not place a model into a high-risk tier and publish further guidance to help set expectations regarding the materiality/risk ratings of AI/ML models as applied to common use cases.
• Safety and soundness: Due to the dynamic nature of Risk AI/ML models, reliance on extensive and ongoing testing focused on outcomes throughout the development and implementation stages of such models should be primary in satisfying regulatory expectations of soundness. To that end, the development of technical metrics and related testing benchmarks should be encouraged. Model “explainability,” while useful for purposes of understanding the specific outputs of AI/ML models, may be less effective or insufficient for establishing whether the model as a whole is sound and fit for purpose.
• Model documentation: The touchstone for the sufficiency of documentation should be what is needed for the bank to use and validate the model, and understand its design, theory, and logic. Disclosure of proprietary details, such as model code, is unnecessary and unhelpful in verifying the sufficiency of a model and would deter model builders from sharing best-in-class technology with financial institutions.
• Industry standards and best practices: Regulators should support the development of global standards and their use across the financial services and regulatory landscape by explicitly recognizing such standards as presumptive evidence of compliance with the MRM guidance and sound AI/ML risk mitigation practices. In addition, regulators should foster industry collaboration and training based on such standards.

Governance controls: Regulators should use guidance to advance the use of governance controls, including incremental rollouts and circuit breakers, as essential tools in mitigating risks associated with Risk AI/ML models.

In an era where AI technology has the potential to revolutionize financial services, we acknowledge the foresight of our regulators in setting a solid foundation and blueprint for navigating the labyrinth of potential risks through the MRM guidance,” says Philip Moyer, Global VP, AI and Business Solutions at Google Cloud. “We believe there is room for greater coherence and precision, enhanced risk-mitigation approaches, and refined best practices surrounding AI and ML risk models. Whether it’s in capacity building or information sharing, our call to action is for greater collaboration between regulators and financial institutions. We’re confident that our collective efforts today will help shape a more robust and resilient future for financial services.

We invite a discussion of additional considerations, including the importance of examiner and industry training and collaboration, as well as openness by regulators to continue to refine the MRM guidance as AI/ML technologies develop and standards emerge. 

Implementing our recommendations would advance several goals. It would help regulators, financial institutions, and technology providers work together to better serve their shared purpose of protecting the safety and soundness of the financial system. At the same time, implementing the recommendations and continuing work in this space would promote the adoption of cutting-edge technologies in the industry, including those that combat such scourges as money laundering, illicit finance, and fraud.

You can read the full white paper here.

Presently, every government agency has to take a hard look at their data capabilities and decide whether their current infrastructure supports their workflow. For many, it doesn’t. Most data systems are developed with a strict set of parameters in mind before implementation, which can limit flexibility and long-term use. Particularly during a crisis, flexible “living systems” offer tremendous advantages as they’re able to change capacity rapidly. Building living data systems with the cloud in mind allows organizations to respond to a changing world with confidence.

Last summer, the Government Business Council conducted a survey of government employees to understand the impacts of data efficiency on government operations. The report Built to Last: A Survey on Organizational Data Efficiency in Times of Crisis offers key insights into organizational efficacy and whether organizations can adapt to a crisis at speed. It also highlights differences between traditional data systems and living data systems. 

Data needs to be readily available

When the pandemic first hit, many agencies needed to create or transition their systems to allow employees to work remotely. This change tested the limits of existing data systems. Even after finding a cloud service provider, agencies encountered the challenges of migrating their data to the cloud. 

Government organizations had decades of data stored in paper records. Most have been working to transfer these records to a digital format, but the process has been slow. They are also faced with collecting sizable amounts of data in real time from their ongoing services,  which involves interfacing with the public, external vendors, or third-party institutions. 

Building the cloud into a flexible data system can solve both issues. Old records can be digitized and given an easy-to-access home for those who need them. Incoming data, both internal and external, can be made accessible as well. Migrating data to the cloud also doubles as a way to create backups of raw data, adding an extra layer of security. Most importantly, building in the cloud unlocked the capacity to scale when demand rises. 

Data should be updated in real-time

One of the key takeaways from the Government Business Council report is the fact that agencies are better able to adapt at speed when data efficiencies are higher. 74% of organizations with pandemic related functions reported a moderate to severe impact to their jobs at the onset of the pandemic. Of those organizations, the ones reporting their data efficiency as “very good” have largely already recovered. That adaptability directly affects an agency’s ability to make informed decisions during a time of a crisis.

Having a real-time data solution in place lets agencies make near real-time decisions. A great example of this from early in the pandemic is vaccine distribution. Google Cloud supported multiple states, such as the State of Wyoming, in distributing vaccines efficiently while handling challenges such as reaching rural populations. Data systems that gathered real-time patient data made a difference in the number of vaccines distributed. Knowing population data and patient risk factors enabled quick and effective decision-making.

A global pandemic is far from the only crisis that needs effective data analytics. Natural disasters, food deserts, public health issues, and more can all be handled more efficiently by having real-time data at hand. Effective data analytics systems are the digital equal of “having your ear to the ground” in each community. They provide valuable insights into what people  need.

Data needs to be accessible and easy to use

Making data easy to work with and understand sets phenomenal data systems apart from functional ones. Having data in the cloud is a great first step, but agencies need to be able to easily access and quickly use the data to accomplish their goals. This is where traditional data systems fail most often. Traditional IT systems and data strategies are designed for a specific purpose, usually identified before development and implementation begin. That means that when the data living in those systems needs to be used differently, adapting to new requirements can be difficult. 

Data can often feel “locked” in traditional systems; the data is there, but there’s no way to get to it or work with it in a way that meets the needs of a crisis. Flexible data systems address this by allowing for greater accessibility. Google Cloud, for example, has customizable tools, such as Contact Center AI and Document AI, which let agencies work with data in ever-changing ways. This also produces greater data transparency since data sets can be worked with and accessed more easily.

Governments need to respond to the changing needs of their constituents in emergencies. While traditional data systems can handle slowly shifting demands on the system, they do not serve agencies well in a crisis. When urgency, accuracy, and accessibility all matter, flexible systems rise to the challenge. The pandemic has pushed agencies to adapt in real time, and many have realized they need a system that adapts with them.

Google Cloud has a suite of tools to create integrated data ecosystems. These ecosystems can scale with increasing demand, meet dynamic development needs, and adapt to a changing landscape. Data-first decision-making is a core tenet of “living data systems.” Google Cloud data systems have handled everything from administering vaccines to detecting fraud. In each of these applications, a core tenet of data-first decision making was implemented at scale. 

For more insights on how flexible data systems help the public sector, download the full report “Built to Last: A Survey on Organizational Data Efficiency in Times of Crisis.”

Geospatial data is a critical component for a comprehensive analytics strategy. Whether you are trying to visualize data using geospatial parameters or do deeper analysis or modeling on customer distribution or proximity, most organizations have some type of geospatial data they would like to use – whether it be customer zipcodes, store locations, or shipping addresses. However, converting geographic data into the correct format for analysis and aggregation at different levels can be difficult. In this post, we’ll walk through some examples of how you can leverage the Google Cloud platform alongside Google Cloud Public Datasets to perform robust analytics on geographic data. The full queries can be accessed from this notebook here. 

Public US Geo Boundaries dataset

BigQuery hosts a slew of public datasets for you to access and integrate into your analytics. Google pays for the storage of these datasets and provides public access to the data via the bigquery-public-data project. You only pay for queries against the data. Plus, the first 1 TB per month is free! These public datasets are valuable on their own, but when joined against your own data they can unlock new analytics use cases and save the team a lot of time. 

Within the Google Cloud Public Datasets Program there are several geographic datasets. Here, we’ll work with the geo_us_boundaries dataset, which contains a set of tables that have the boundaries of different geospatial areas as polygons and coordinates based on the center point (GEOGRAPHY column type in BigQuery), published by the US Census Bureau.

Mapping geospatial points to hierarchical areas

Many times you will find yourself in situations where you have a string representing an address. However, most tools require lat/long coordinates to actually plot points. Using the Google Maps Geocoding API we can convert an address into a lat/long and then store the results in the BigQuery table. 

With a lat/long representation of our point, we can join our initial dataset back onto any of the tables here using the ST_WITHIN function. This allows us to check and see if a point is within the specified polygon. 

ST_WITHIN(geography_1, geography_2)

This can be helpful for ensuring standard nomenclature; for example, metropolitan areas that might be named differently. The query below maps each customers’ address to a given metropolitan area name.

  SELECT 
   cust.id as customer_id, 
   metro.name as metro_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),metro.metdiv_geom)

It can also be useful for converting to designated market area (DMA), which is often used in creating targeted digital marketing campaigns.

  SELECT 
   cust.id as customer_id, 
   dma.dma_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.designated_market_area` as dma
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),dma.dma_geom)

Or for filling in missing information; for example, some addresses may be missing zip code which results in incorrect calculations when aggregating up to the zipcode level. By joining onto the zip_codes table we can ensure all coordinates are mapped appropriately and aggregate up from there.

  SELECT 
   zip.zip_code, 
   count(distinct cust.id) as unique_customers
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.zip_codes` as zip
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),zip.zip_code_geom)
GROUP BY 1

Note that the zip code table isn’t a comprehensive list of all US zip codes, they are zip code tabulation areas (ZCTAs). Details about the differences can be found here. Additionally, the zip code table gives us hierarchical information, which allows us to perform more meaningful analytics. One example is leveraging hierarchical drilling in Looker. I can aggregate my total sales up to the country level, and then drill down to state, city and zipcode to identify where sales are highest. You can also use the BigQuery GeoViz tool to visualize geospatial data!

Aside from simply checking if a point is within an area, we can also use ST_DISTANCE to do something like find the closest city using the centerpoint for the metropolitan area table. 

  SELECT 
cust.id as customer_id, 
ARRAY_AGG(
  metro.name order by ST_DISTANCE(
   ST_GEOGPOINT(cust.longitude, cust.latitude),
  metro.internal_point_geom) asc limit 1)[offset(0)] as metro_name
FROM
`looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
GROUP BY cust.id

This concept doesn’t just hold true for points, we can also leverage other GIS functions to see if a geospatial area is contained within areas that are listed in the boundaries datasets. If your data comes into BigQuery as a GeoJSON string, we can convert it to a GEOGRAPHY type using the ST_GEOGFROMGEOJSON function. Once our data is in a GEOGRAPHY type we can do things like check to see what urban area the geo is within – using either ST_WITHIN or ST_INTERSECTS to account for partial coverage. Here, I am using the customer’s zip code to find all metropolitan divisions where the zip code polygon and the metropolitan polygon intersect. I am then selecting the metropolitan area that has the most overlap (or the intersection has the largest area) to be the customer’s metro that we use for reporting.

  SELECT 
   cust.id as customer_id, 
   ARRAY_AGG(
      metro.name order by ST_AREA(
        ST_INTERSECTION(zip.zip_code_geom,metro.metdiv_geom)
      ) desc limit 1)[offset(0)] as metro_name  
FROM
`looker-private-demo.retail.customers` as cust
JOIN `bigquery-public-data.geo_us_boundaries.zip_codes` as zip on      cust.zip=zip.zip_code
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_INTERSECTS(zip.zip_code_geom,metro.metdiv_geom)
GROUP BY cust.id

The same ideas can be applied to the other tables in the dataset including the county, urban areas and National Weather Service forecast regions (which can also be useful if you want to join your datasets onto weather data).

Correcting for data discrepancy

One problem that we may run into when working with geospatial data is that different data sources may have different representations of the same information. For example, you might have one system that records state as a two letter abbreviation and another using the full name. Here, we can use the state table to join the different datasets.

  SELECT 
   st.state_name, 
   sum(ab.sales+fn.sales) as total_sales 
FROM `bigquery-public-data.geo_us_boundaries.states` as st
LEFT JOIN abbreviated_table as ab on ab.state = st.state
LEFT JOIN fullname_table as fn on fn.state = st.state_name
WHERE COALESCE(ab.state, fn.state) IS NOT NULL
GROUP BY 1

Another example might be using the tables as a source of truth for fuzzy matching. If the address is a manually entered field somewhere in your application, there is a good chance that things will be misspelled. Different representations of the same name may prevent tables from joining with each other or lead to duplicate entries when performing aggregations. Here, I use a simple Soundex algorithm to generate a code for each county name, using helper functions from this blog post. We can see that even though some are misspelled they have the same Soundex code.

Next, we can join back onto our counties table so we make sure to use the correct spelling of the county name. Then, we can simply aggregate our data for more accurate reporting. 

  SELECT
 c.county_name,
 sum(sales) as total_sales
FROM
 table
 JOIN `bigquery-public-data.geo_us_boundaries.counties` as c
 on testing.dq_fm_Soundex(table.county) = testing.dq_fm_Soundex(c.county_name)
WHERE c.state_fips_code = cast(36 as string)
GROUP BY 1

Note that fuzzy matching definitely isn’t perfect and you might need to try different methods or apply certain filters for it to work best depending on the specifics of your data.

The US Geo Boundary datasets allow you to perform meaningful geographic analysis without needing to worry about extracting, transforming or loading additional datasets into BigQuery. These datasets, along with all the other Google Cloud Public Datasets, will be available in the Analytics Hub. Please sign up for the Analytics Hub preview, which is scheduled to be available in the third quarter of 2021, by going to g.co/cloud/analytics-hub.