Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
| <html lang=en dir=ltr class="docs-wrapper plugin-docs plugin-id-default docs-version-current docs-doc-page docs-doc-id-deployment/schema-migration" data-has-hydrated=false><head><meta charset=UTF-8><meta name=generator content="Docusaurus v3.10.0"><title data-rh=true>Schema Migration Guide | Open Navigator</title><meta data-rh=true name=viewport content="width=device-width, initial-scale=1.0"/><meta data-rh=true property=og:image content=https://www.communityone.com/img/docusaurus-social-card.jpg /><meta data-rh=true name=twitter:image content=https://www.communityone.com/img/docusaurus-social-card.jpg /><meta data-rh=true property=og:url content=https://www.communityone.com/docs/deployment/schema-migration /><meta data-rh=true property=og:locale content=en /><meta data-rh=true name=docusaurus_locale content=en /><meta data-rh=true name=docsearch:language content=en /><meta data-rh=true name=keywords content="civic engagement, policy tracking, meeting minutes, nonprofit tracking, municipal government, advocacy, open data, local government"/><meta data-rh=true property=og:type content=website /><meta data-rh=true property=og:site_name content="Open Navigator"/><meta data-rh=true name=twitter:card content=summary_large_image /><meta data-rh=true name=docusaurus_version content=current /><meta data-rh=true name=docusaurus_tag content=docs-default-current /><meta data-rh=true name=docsearch:version content=current /><meta data-rh=true name=docsearch:docusaurus_tag content=docs-default-current /><meta data-rh=true property=og:title content="Schema Migration Guide | Open Navigator"/><meta data-rh=true name=description content=Overview /><meta data-rh=true property=og:description content=Overview /><link data-rh=true rel=icon href=/img/favicon.ico /><link data-rh=true rel=canonical href=https://www.communityone.com/docs/deployment/schema-migration /><link data-rh=true rel=alternate href=https://www.communityone.com/docs/deployment/schema-migration hreflang=en /><link data-rh=true rel=alternate href=https://www.communityone.com/docs/deployment/schema-migration hreflang=x-default /><script data-rh=true type=application/ld+json>{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","item":"https://www.communityone.com/docs/for-developers","name":"Developers & Technical Users","position":1},{"@type":"ListItem","item":"https://www.communityone.com/docs/deployment/schema-migration","name":"Schema Migration Guide","position":2}]}</script><link rel=alternate type=application/rss+xml href=/blog/rss.xml title="Open Navigator RSS Feed"><link rel=alternate type=application/atom+xml href=/blog/atom.xml title="Open Navigator Atom Feed"><link rel=preconnect href=https://www.google-analytics.com><link rel=preconnect href=https://www.googletagmanager.com><script async src="https://www.googletagmanager.com/gtag/js?id=G-5EQV815915"></script><script>function gtag(){dataLayer.push(arguments)}window.dataLayer=window.dataLayer||[],gtag("js",new Date),gtag("config","G-5EQV815915",{anonymize_ip:!0})</script><link rel=stylesheet href=/assets/css/styles.c89d6b2d.css /><script src=/assets/js/runtime~main.c8fa085e.js defer></script><script src=/assets/js/main.6e24e536.js defer></script></head><body><svg style="display: none;"><defs> | |
| <symbol id=theme-svg-external-link viewBox="0 0 24 24"><path fill=currentColor d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"/></symbol> | |
| </defs></svg> | |
| <script>!function(){var t=function(){try{return new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}}()||function(){try{return window.localStorage.getItem("theme-7e9")}catch(t){}}();document.documentElement.setAttribute("data-theme",t||(window.matchMedia("(prefers-color-scheme: dark)").matches?"dark":"light")),document.documentElement.setAttribute("data-theme-choice",t||"system")}(),function(){try{for(var[t,e]of new URLSearchParams(window.location.search).entries())if(t.startsWith("docusaurus-data-")){var a=t.replace("docusaurus-data-","data-");document.documentElement.setAttribute(a,e)}}catch(t){}}()</script><div id=__docusaurus><link rel=preload as=image href=/img/communityone_logo.svg /><script type=application/ld+json>{"@context":"https://schema.org","@type":"Organization","address":{"@type":"PostalAddress","addressCountry":"US","addressLocality":"Tuscaloosa","addressRegion":"AL","postalCode":"35406","streetAddress":"5617 Lakeridge Court"},"contactPoint":{"@type":"ContactPoint","availableLanguage":["English"],"contactType":"Customer Service","email":"johnbowyer@communityone.com"},"description":"Track 90,000+ jurisdictions, 1.8M nonprofits, and analyze meeting minutes with AI. The open path to everything local.","email":"johnbowyer@communityone.com","legalName":"CommunityOne","logo":"https://www.communityone.com/img/communityone_logo.svg","name":"CommunityOne","sameAs":["https://www.facebook.com/communityone","https://www.instagram.com/communityone","https://twitter.com/communityone","https://www.linkedin.com/company/communityone","https://www.youtube.com/@communityone","https://discord.gg/communityone","https://github.com/getcommunityone/open-navigator"],"url":"https://www.communityone.com"}</script><script type=application/ld+json>{"@context":"https://schema.org","@type":"WebSite","alternateName":"CommunityOne Open Navigator","description":"AI-powered civic engagement platform tracking jurisdictions, nonprofits, and government meetings","name":"Open Navigator","potentialAction":{"@type":"SearchAction","query-input":"required name=search_term_string","target":{"@type":"EntryPoint","urlTemplate":"https://www.communityone.com/search?q={search_term_string}"}},"url":"https://www.communityone.com"}</script><script type=application/ld+json>{"@context":"https://schema.org","@type":"SoftwareApplication","aggregateRating":{"@type":"AggregateRating","ratingCount":"1","ratingValue":"5"},"applicationCategory":"BusinessApplication","description":"Track 90,000+ jurisdictions, 1.8M nonprofits, and analyze meeting minutes with AI","featureList":["Track 90,000+ jurisdictions","Monitor 1.8M nonprofits","Analyze meeting minutes","Legislative bill tracking","Campaign finance data"],"name":"Open Navigator","offers":{"@type":"Offer","price":"0","priceCurrency":"USD"},"operatingSystem":"Web","screenshot":"https://www.communityone.com/img/docusaurus-social-card.jpg","softwareVersion":"1.0.0"}</script><div role=region aria-label="Skip to main content"><a class=skipToContent_fXgn href=#__docusaurus_skipToContent_fallback>Skip to main content</a></div><nav aria-label=Main class="theme-layout-navbar navbar navbar--fixed-top"><div class=navbar__inner><div class="theme-layout-navbar-left navbar__items"><button aria-label="Toggle navigation bar" aria-expanded=false class="navbar__toggle clean-btn" type=button><svg width=30 height=30 viewBox="0 0 30 30" aria-hidden=true><path stroke=currentColor stroke-linecap=round stroke-miterlimit=10 stroke-width=2 d="M4 7h22M4 15h22M4 23h22"/></svg></button><a href=https://www.communityone.com target=_self rel="noopener noreferrer" class=navbar__brand><div class=navbar__logo><img src=/img/communityone_logo.svg alt="CommunityOne Logo" class="themedComponent_mlkZ themedComponent--light_NVdE"/><img src=/img/communityone_logo.svg alt="CommunityOne Logo" class="themedComponent_mlkZ themedComponent--dark_xIcU"/></div><b class="navbar__title text--truncate">Open Navigator Home</b></a><a class="navbar__item navbar__link" href=/docs/intro>Getting Started</a><a class="navbar__item navbar__link" href=/docs/for-families>Families & Individuals</a><a class="navbar__item navbar__link" href=/docs/for-advocates>Policy Makers</a><a class="navbar__item navbar__link" href=/docs/for-developers>Developers</a><a class="navbar__item navbar__link" href=/docs/data-sources/citations>Data and Terms</a><a class="navbar__item navbar__link" href=/blog>Blog</a></div><div class="theme-layout-navbar-right navbar__items navbar__items--right"><a href=https://github.com/getcommunityone/open-navigator-for-engagement target=_blank rel="noopener noreferrer" class="navbar__item navbar__link">GitHub<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><div class="toggle_vylO colorModeToggle_DEke"><button class="clean-btn toggleButton_gllP toggleButtonDisabled_aARS" type=button disabled title="system mode" aria-label="Switch between dark and light mode (currently system mode)"><svg viewBox="0 0 24 24" width=24 height=24 aria-hidden=true class="toggleIcon_g3eP lightToggleIcon_pyhR"><path fill=currentColor d="M12,9c1.65,0,3,1.35,3,3s-1.35,3-3,3s-3-1.35-3-3S10.35,9,12,9 M12,7c-2.76,0-5,2.24-5,5s2.24,5,5,5s5-2.24,5-5 S14.76,7,12,7L12,7z M2,13l2,0c0.55,0,1-0.45,1-1s-0.45-1-1-1l-2,0c-0.55,0-1,0.45-1,1S1.45,13,2,13z M20,13l2,0c0.55,0,1-0.45,1-1 s-0.45-1-1-1l-2,0c-0.55,0-1,0.45-1,1S19.45,13,20,13z M11,2v2c0,0.55,0.45,1,1,1s1-0.45,1-1V2c0-0.55-0.45-1-1-1S11,1.45,11,2z M11,20v2c0,0.55,0.45,1,1,1s1-0.45,1-1v-2c0-0.55-0.45-1-1-1C11.45,19,11,19.45,11,20z M5.99,4.58c-0.39-0.39-1.03-0.39-1.41,0 c-0.39,0.39-0.39,1.03,0,1.41l1.06,1.06c0.39,0.39,1.03,0.39,1.41,0s0.39-1.03,0-1.41L5.99,4.58z M18.36,16.95 c-0.39-0.39-1.03-0.39-1.41,0c-0.39,0.39-0.39,1.03,0,1.41l1.06,1.06c0.39,0.39,1.03,0.39,1.41,0c0.39-0.39,0.39-1.03,0-1.41 L18.36,16.95z M19.42,5.99c0.39-0.39,0.39-1.03,0-1.41c-0.39-0.39-1.03-0.39-1.41,0l-1.06,1.06c-0.39,0.39-0.39,1.03,0,1.41 s1.03,0.39,1.41,0L19.42,5.99z M7.05,18.36c0.39-0.39,0.39-1.03,0-1.41c-0.39-0.39-1.03-0.39-1.41,0l-1.06,1.06 c-0.39,0.39-0.39,1.03,0,1.41s1.03,0.39,1.41,0L7.05,18.36z"/></svg><svg viewBox="0 0 24 24" width=24 height=24 aria-hidden=true class="toggleIcon_g3eP darkToggleIcon_wfgR"><path fill=currentColor d="M9.37,5.51C9.19,6.15,9.1,6.82,9.1,7.5c0,4.08,3.32,7.4,7.4,7.4c0.68,0,1.35-0.09,1.99-0.27C17.45,17.19,14.93,19,12,19 c-3.86,0-7-3.14-7-7C5,9.07,6.81,6.55,9.37,5.51z M12,3c-4.97,0-9,4.03-9,9s4.03,9,9,9s9-4.03,9-9c0-0.46-0.04-0.92-0.1-1.36 c-0.98,1.37-2.58,2.26-4.4,2.26c-2.98,0-5.4-2.42-5.4-5.4c0-1.81,0.89-3.42,2.26-4.4C12.92,3.04,12.46,3,12,3L12,3z"/></svg><svg viewBox="0 0 24 24" width=24 height=24 aria-hidden=true class="toggleIcon_g3eP systemToggleIcon_QzmC"><path fill=currentColor d="m12 21c4.971 0 9-4.029 9-9s-4.029-9-9-9-9 4.029-9 9 4.029 9 9 9zm4.95-13.95c1.313 1.313 2.05 3.093 2.05 4.95s-0.738 3.637-2.05 4.95c-1.313 1.313-3.093 2.05-4.95 2.05v-14c1.857 0 3.637 0.737 4.95 2.05z"/></svg></button></div><div class=navbarSearchContainer_Bca1></div></div></div><div role=presentation class=navbar-sidebar__backdrop></div></nav><div id=__docusaurus_skipToContent_fallback class="theme-layout-main main-wrapper mainWrapper_z2l0"><div class=docsWrapper_hBAB><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type=button></button><div class=docRoot_UBD9><aside class="theme-doc-sidebar-container docSidebarContainer_YfHR"><div class=sidebarViewport_aRkj><div class=sidebar_njMd><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=true href=/docs/intro><span title="Getting Started" class=categoryLinkLabel_W154>Getting Started</span></a></div><ul class=menu__list><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class=menu__link tabindex=0 href=/docs/intro><span title=Introduction class=linkLabel_WmDU>Introduction</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class=menu__link tabindex=0 href=/docs/open-navigator><span title="Open Navigator" class=linkLabel_WmDU>Open Navigator</span></a></ul><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist" href=/docs/for-families><span title="Families & Individuals" class=categoryLinkLabel_W154>Families & Individuals</span></a><button aria-label="Collapse sidebar category 'Families & Individuals'" aria-expanded=true type=button class="clean-btn menu__caret"></button></div><ul class=menu__list><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/families/community-events><span title="Resources for Families" class=categoryLinkLabel_W154>Resources for Families</span></a></div><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class=menu__link tabindex=0 href=/docs/open-navigator><span title="Getting Started with Open Navigator" class=linkLabel_WmDU>Getting Started with Open Navigator</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class=menu__link tabindex=0 href=/docs/data-sources/citations><span title="Data and Citations" class=linkLabel_WmDU>Data and Citations</span></a></ul><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist" href=/docs/for-advocates><span title="Policy Makers & Advocates" class=categoryLinkLabel_W154>Policy Makers & Advocates</span></a><button aria-label="Collapse sidebar category 'Policy Makers & Advocates'" aria-expanded=true type=button class="clean-btn menu__caret"></button></div><ul class=menu__list><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/data-sources/overview><span title="Understanding the Data" class=categoryLinkLabel_W154>Understanding the Data</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/guides/political-economy><span title="Analysis & Strategy" class=categoryLinkLabel_W154>Analysis & Strategy</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/case-studies/tuscaloosa-complete><span title="Real-World Examples" class=categoryLinkLabel_W154>Real-World Examples</span></a></div></ul><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--active" href=/docs/for-developers><span title="Developers & Technical Users" class=categoryLinkLabel_W154>Developers & Technical Users</span></a><button aria-label="Collapse sidebar category 'Developers & Technical Users'" aria-expanded=true type=button class="clean-btn menu__caret"></button></div><ul class=menu__list><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/quickstart><span title="Setup & Installation" class=categoryLinkLabel_W154>Setup & Installation</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/data-sources/citations><span title="Data Sources (Technical)" class=categoryLinkLabel_W154>Data Sources (Technical)</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/guides/jurisdiction-setup><span title="How-To Guides" class=categoryLinkLabel_W154>How-To Guides</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/integrations/mcp-server><span title=Integrations class=categoryLinkLabel_W154>Integrations</span></a></div><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" role=button aria-expanded=true tabindex=0 href=/docs/deployment/databricks-apps><span title=Deployment class=categoryLinkLabel_W154>Deployment</span></a></div><ul class=menu__list><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/databricks-apps><span title="Databricks Apps Deployment Guide" class=linkLabel_WmDU>Databricks Apps Deployment Guide</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/databricks-migration><span title="Databricks Agent Bricks Refactoring - Summary" class=linkLabel_WmDU>Databricks Agent Bricks Refactoring - Summary</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/quickstart-databricks><span title="Quick Start Guide - React + FastAPI Databricks App" class=linkLabel_WmDU>Quick Start Guide - React + FastAPI Databricks App</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/huggingface-spaces><span title="Hugging Face Spaces Deployment" class=linkLabel_WmDU>Hugging Face Spaces Deployment</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/oauth-providers-setup><span title="OAuth Providers Setup" class=linkLabel_WmDU>OAuth Providers Setup</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/authentication-setup><span title="Authentication Setup Guide" class=linkLabel_WmDU>Authentication Setup Guide</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link menu__link--active" aria-current=page tabindex=0 href=/docs/deployment/schema-migration><span title="Schema Migration Guide" class=linkLabel_WmDU>Schema Migration Guide</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/build-verification><span title="Build Verification & CI/CD" class=linkLabel_WmDU>Build Verification & CI/CD</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/variable-migration><span title="🔄 Variable Name Migration Guide" class=linkLabel_WmDU>🔄 Variable Name Migration Guide</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/d-drive-configuration><span title="D Drive Configuration for Large Datasets" class=linkLabel_WmDU>D Drive Configuration for Large Datasets</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/docker-troubleshooting><span title="🐛 Docker Build Troubleshooting Guide" class=linkLabel_WmDU>🐛 Docker Build Troubleshooting Guide</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/build-protection><span title="Build Protection & CI/CD" class=linkLabel_WmDU>Build Protection & CI/CD</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/rename-repository><span title="Rename Repository & Make Public" class=linkLabel_WmDU>Rename Repository & Make Public</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/costs><span title="💰 Cost Breakdown: $0 for Data Access" class=linkLabel_WmDU>💰 Cost Breakdown: $0 for Data Access</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/jurisdiction-discovery><span title="Jurisdiction Discovery - Deployment Options" class=linkLabel_WmDU>Jurisdiction Discovery - Deployment Options</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/scale><span title="🚀 RUNNING DISCOVERY FOR ALL U.S. CITIES AND COUNTIES" class=linkLabel_WmDU>🚀 RUNNING DISCOVERY FOR ALL U.S. CITIES AND COUNTIES</span></a><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class=menu__link tabindex=0 href=/docs/deployment/storage><span title="💰 COST-EFFECTIVE STORAGE STRATEGY (Personal Budget)" class=linkLabel_WmDU>💰 COST-EFFECTIVE STORAGE STRATEGY (Personal Budget)</span></a></ul><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class=menu__list-item-collapsible><a class="categoryLink_byQd menu__link menu__link--sublist menu__link--sublist-caret" role=button aria-expanded=false tabindex=0 href=/docs/development/database-setup><span title=Development class=categoryLinkLabel_W154>Development</span></a></div></ul></ul></nav></div></div></aside><main class=docMainContainer_TBSr><div class="container padding-top--md padding-bottom--lg"><div class=row><div class="col docItemCol_VOVn"><div class=docItemContainer_Djhp><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Z_bl" aria-label=Breadcrumbs><ul class=breadcrumbs><li class=breadcrumbs__item><a aria-label="Home page" class=breadcrumbs__link href=/><svg viewBox="0 0 24 24" class=breadcrumbHomeIcon_YNFT><path d="M10 19v-5h4v5c0 .55.45 1 1 1h3c.55 0 1-.45 1-1v-7h1.7c.46 0 .68-.57.33-.87L12.67 3.6c-.38-.34-.96-.34-1.34 0l-8.36 7.53c-.34.3-.13.87.33.87H5v7c0 .55.45 1 1 1h3c.55 0 1-.45 1-1z" fill=currentColor /></svg></a><li class=breadcrumbs__item><a class=breadcrumbs__link href=/docs/for-developers><span>Developers & Technical Users</span></a><li class=breadcrumbs__item><span class=breadcrumbs__link>Deployment</span><li class="breadcrumbs__item breadcrumbs__item--active"><span class=breadcrumbs__link>Schema Migration Guide</span></ul></nav><div class="tocCollapsible_ETCw theme-doc-toc-mobile tocMobile_ITEo"><button type=button class="clean-btn tocCollapsibleButton_TO0P">On this page</button></div><div class="theme-doc-markdown markdown"><header><h1>Schema Migration Guide</h1></header> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=overview>Overview<a href=#overview class=hash-link aria-label="Direct link to Overview" title="Direct link to Overview" translate=no></a></h2> | |
| <p>CommunityOne has migrated from a domain-specific oral health schema to a <strong>generic community engagement data platform</strong>. This enables broader civic tech applications beyond health policy.</p> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=what-changed>What Changed<a href=#what-changed class=hash-link aria-label="Direct link to What Changed" title="Direct link to What Changed" translate=no></a></h2> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=file-rename>File Rename<a href=#file-rename class=hash-link aria-label="Direct link to File Rename" title="Direct link to File Rename" translate=no></a></h3> | |
| <ul> | |
| <li class=""><strong>Old:</strong> <code>databricks/oral_health_schema.sql</code></li> | |
| <li class=""><strong>New:</strong> <code>databricks/communityone_schema.sql</code></li> | |
| <li class=""><strong>Status:</strong> Legacy file renamed to <code>.deprecated</code> suffix</li> | |
| </ul> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=table-renames>Table Renames<a href=#table-renames class=hash-link aria-label="Direct link to Table Renames" title="Direct link to Table Renames" translate=no></a></h3> | |
| <table><thead><tr><th>Old Name<th>New Name<th>Purpose<tbody><tr><td><code>fact_oral_health_observation</code><td><code>fact_communityone_observation</code><td>Generic community outcome measurements<tr><td><em>(no oral health prefix in other tables)</em><td>All dimensions remain the same<td>Geography, date, measure, etc.</table> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=dimension-table-updates>Dimension Table Updates<a href=#dimension-table-updates class=hash-link aria-label="Direct link to Dimension Table Updates" title="Direct link to Dimension Table Updates" translate=no></a></h3> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=new-dim_jurisdiction>New: <code>dim_jurisdiction</code><a href=#new-dim_jurisdiction class=hash-link aria-label="Direct link to new-dim_jurisdiction" title="Direct link to new-dim_jurisdiction" translate=no></a></h4> | |
| <p>Replaces inline jurisdiction data with proper dimension table:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> dim_jurisdiction </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_id string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- OCD-ID format</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_name string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_type string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- city, county, state, district</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> geography_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> ocd_id string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> website_url string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> population </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=new-dim_organization>New: <code>dim_organization</code><a href=#new-dim_organization class=hash-link aria-label="Direct link to new-dim_organization" title="Direct link to new-dim_organization" translate=no></a></h4> | |
| <p>Nonprofit and foundation master dimension (IRS EO-BMF):</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> dim_organization </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> organization_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> ein string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> organization_name string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> ntee_code string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> foundation_code string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- 10-13=Foundation, 15=Public Charity</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> is_private_foundation </span><span class="token keyword" style=color:#00009f>boolean</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- 990-PF filers</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> asset_amount </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> income_amount </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=new-fact-tables-previously-missing>New Fact Tables (Previously Missing)<a href=#new-fact-tables-previously-missing class=hash-link aria-label="Direct link to New Fact Tables (Previously Missing)" title="Direct link to New Fact Tables (Previously Missing)" translate=no></a></h3> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=1-fact_grant---grant-transactions>1. <code>fact_grant</code> - Grant Transactions<a href=#1-fact_grant---grant-transactions class=hash-link aria-label="Direct link to 1-fact_grant---grant-transactions" title="Direct link to 1-fact_grant---grant-transactions" translate=no></a></h4> | |
| <p><strong>Purpose:</strong> Track individual grants between funders and recipients</p> | |
| <p><strong>Data Sources:</strong></p> | |
| <ul> | |
| <li class="">IRS Form 990 Schedule I (grants paid by nonprofits)</li> | |
| <li class="">IRS Form 990-PF (private foundation giving)</li> | |
| <li class="">USASpending.gov API (federal grants)</li> | |
| <li class="">State grant databases</li> | |
| </ul> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> fact_grant </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> grant_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> recipient_org_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- FK to dim_organization</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> recipient_jurisdiction_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- FK to dim_jurisdiction</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> funder_org_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- FK to dim_organization</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> funder_jurisdiction_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- FK to dim_jurisdiction</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> grant_amount </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> grant_purpose string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> program_area string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> award_date_key </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> start_date_key </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> end_date_key </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> is_multi_year </span><span class="token keyword" style=color:#00009f>boolean</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> funding_source string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- federal, state, foundation, corporate</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <p><strong>Example Queries:</strong></p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Find all federal grants to dental nonprofits in Alabama</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_purpose</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> j</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">jurisdiction_name</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_grant g</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization o </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">recipient_org_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_jurisdiction j </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> j</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">jurisdiction_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">recipient_jurisdiction_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code </span><span class="token operator" style=color:#393A34>LIKE</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'E%'</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Health services</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> j</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">state_code </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'AL'</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funding_source </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'federal'</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_purpose </span><span class="token operator" style=color:#393A34>LIKE</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'%dental%'</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain" style=display:inline-block></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic>-- Track foundation giving patterns (990-PF data)</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>COUNT</span><span class="token punctuation" style=color:#393A34>(</span><span class="token operator" style=color:#393A34>*</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> grant_count</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>SUM</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> total_giving</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> avg_grant_size</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_grant g</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization funder </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funder_org_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">is_private_foundation </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>TRUE</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>ORDER</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> total_giving </span><span class="token keyword" style=color:#00009f>DESC</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=2-fact_nonprofit_finance---annual-990-filings>2. <code>fact_nonprofit_finance</code> - Annual 990 Filings<a href=#2-fact_nonprofit_finance---annual-990-filings class=hash-link aria-label="Direct link to 2-fact_nonprofit_finance---annual-990-filings" title="Direct link to 2-fact_nonprofit_finance---annual-990-filings" translate=no></a></h4> | |
| <p><strong>Purpose:</strong> Detailed nonprofit financial health and revenue sources</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> fact_nonprofit_finance </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> filing_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> organization_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> ein string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> tax_year </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> total_revenue </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> total_expenses </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> grants_paid </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> government_grants </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Revenue source</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> foundation_grants </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Revenue source</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> corporate_donations </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Revenue source</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> individual_donations </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Revenue source</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> program_service_revenue </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Earned income</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> overhead_ratio </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>8</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>4</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Calculated metric</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> fundraising_efficiency </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>8</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>4</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Calculated metric</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <p><strong>Example Queries:</strong></p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Compare revenue sources for health vs education nonprofits</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> SUBSTR</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> sector</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">government_grants </span><span class="token operator" style=color:#393A34>/</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue </span><span class="token operator" style=color:#393A34>*</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>100</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> govt_pct</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">foundation_grants </span><span class="token operator" style=color:#393A34>/</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue </span><span class="token operator" style=color:#393A34>*</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>100</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> foundation_pct</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">individual_donations </span><span class="token operator" style=color:#393A34>/</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue </span><span class="token operator" style=color:#393A34>*</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>100</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> individual_pct</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_nonprofit_finance f</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization o </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code </span><span class="token operator" style=color:#393A34>IN</span><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>(</span><span class="token string" style=color:#e3116c>'E'</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'B'</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Health, Education</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue </span><span class="token operator" style=color:#393A34>></span><span class="token plain"> </span><span class="token number" style=color:#36acaa>0</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> SUBSTR</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain" style=display:inline-block></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic>-- Find most efficient nonprofits</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">overhead_ratio</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fundraising_efficiency</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_nonprofit_finance f</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization o </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">tax_year </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2023</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">overhead_ratio </span><span class="token operator" style=color:#393A34><</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>0.25</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- Less than 25% overhead</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fundraising_efficiency </span><span class="token operator" style=color:#393A34>></span><span class="token plain"> </span><span class="token number" style=color:#36acaa>4.0</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- $4+ raised per $1 spent</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>ORDER</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">total_revenue </span><span class="token keyword" style=color:#00009f>DESC</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=3-fact_jurisdiction_budget---government-finances>3. <code>fact_jurisdiction_budget</code> - Government Finances<a href=#3-fact_jurisdiction_budget---government-finances class=hash-link aria-label="Direct link to 3-fact_jurisdiction_budget---government-finances" title="Direct link to 3-fact_jurisdiction_budget---government-finances" translate=no></a></h4> | |
| <p><strong>Purpose:</strong> Track government budgets and spending priorities</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> fact_jurisdiction_budget </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> budget_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> fiscal_year </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> total_revenue </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> total_expenditures </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> federal_grants </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> state_grants </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> property_tax_revenue </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>18</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=4-fact_meeting---meetings--public-hearings>4. <code>fact_meeting</code> - Meetings & Public Hearings<a href=#4-fact_meeting---meetings--public-hearings class=hash-link aria-label="Direct link to 4-fact_meeting---meetings--public-hearings" title="Direct link to 4-fact_meeting---meetings--public-hearings" translate=no></a></h4> | |
| <p><strong>Purpose:</strong> Track government transparency and public engagement</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> fact_meeting </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> meeting_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> jurisdiction_key string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> meeting_date_key </span><span class="token keyword" style=color:#00009f>int</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> meeting_type string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> has_agenda </span><span class="token keyword" style=color:#00009f>boolean</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> has_minutes </span><span class="token keyword" style=color:#00009f>boolean</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> has_video </span><span class="token keyword" style=color:#00009f>boolean</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> topic_tags array</span><span class="token operator" style=color:#393A34><</span><span class="token plain">string</span><span class="token operator" style=color:#393A34>></span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <h4 class="anchor anchorTargetStickyNavbar_Vzrq" id=5-bridge_grant_program_area---grant-multi-purpose-support>5. <code>bridge_grant_program_area</code> - Grant Multi-Purpose Support<a href=#5-bridge_grant_program_area---grant-multi-purpose-support class=hash-link aria-label="Direct link to 5-bridge_grant_program_area---grant-multi-purpose-support" title="Direct link to 5-bridge_grant_program_area---grant-multi-purpose-support" translate=no></a></h4> | |
| <p><strong>Purpose:</strong> Handle grants supporting multiple program areas</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> bridge_grant_program_area </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> grant_key string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> program_area_code string </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> program_area_desc string</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> allocation_pct </span><span class="token keyword" style=color:#00009f>decimal</span><span class="token punctuation" style=color:#393A34>(</span><span class="token number" style=color:#36acaa>5</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2</span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- % of grant to this area</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=migration-steps>Migration Steps<a href=#migration-steps class=hash-link aria-label="Direct link to Migration Steps" title="Direct link to Migration Steps" translate=no></a></h2> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=1-for-databricks-users>1. For Databricks Users<a href=#1-for-databricks-users class=hash-link aria-label="Direct link to 1. For Databricks Users" title="Direct link to 1. For Databricks Users" translate=no></a></h3> | |
| <p>Update your Unity Catalog schema creation scripts:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Old approach (DEPRECATED)</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic>-- CREATE TABLE catalog.schema.fact_oral_health_observation ...</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain" style=display:inline-block></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic>-- New approach</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fact_communityone_observation </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fact_grant </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fact_nonprofit_finance </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">dim_organization </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">dim_jurisdiction </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=2-for-existing-data>2. For Existing Data<a href=#2-for-existing-data class=hash-link aria-label="Direct link to 2. For Existing Data" title="Direct link to 2. For Existing Data" translate=no></a></h3> | |
| <p>If you have data in <code>fact_oral_health_observation</code>:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Rename table</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>ALTER</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TABLE</span><span class="token plain"> catalog</span><span class="token punctuation" style=color:#393A34>.</span><span class="token keyword" style=color:#00009f>schema</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fact_oral_health_observation </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>RENAME</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>TO</span><span class="token plain"> fact_communityone_observation</span><span class="token punctuation" style=color:#393A34>;</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain" style=display:inline-block></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic>-- Or migrate data</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>INSERT</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>INTO</span><span class="token plain"> fact_communityone_observation</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> observation_key</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> measure_key</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> geography_key</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> jurisdiction_key</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token comment" style=color:#999988;font-style:italic>-- NEW column</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> stratification_key</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_oral_health_observation</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=3-update-application-code>3. Update Application Code<a href=#3-update-application-code class=hash-link aria-label="Direct link to 3. Update Application Code" title="Direct link to 3. Update Application Code" translate=no></a></h3> | |
| <p><strong>Python/SQL queries:</strong></p> | |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-python codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic># Old</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain">df </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> spark</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">table</span><span class="token punctuation" style=color:#393A34>(</span><span class="token string" style=color:#e3116c>"fact_oral_health_observation"</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain" style=display:inline-block></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token comment" style=color:#999988;font-style:italic># New</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain">df </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> spark</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">table</span><span class="token punctuation" style=color:#393A34>(</span><span class="token string" style=color:#e3116c>"fact_communityone_observation"</span><span class="token punctuation" style=color:#393A34>)</span><br/></div></code></pre></div></div> | |
| <p><strong>Documentation references:</strong></p> | |
| <ul> | |
| <li class="">Update ERD diagrams</li> | |
| <li class="">Update API documentation</li> | |
| <li class="">Update data dictionary</li> | |
| </ul> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=new-capabilities-enabled>New Capabilities Enabled<a href=#new-capabilities-enabled class=hash-link aria-label="Direct link to New Capabilities Enabled" title="Direct link to New Capabilities Enabled" translate=no></a></h2> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=1-grant-flow-analysis>1. Grant Flow Analysis<a href=#1-grant-flow-analysis class=hash-link aria-label="Direct link to 1. Grant Flow Analysis" title="Direct link to 1. Grant Flow Analysis" translate=no></a></h3> | |
| <p>Track money flow from funders to recipients:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> recipient</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> recipient</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>SUM</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> total_grants</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>COUNT</span><span class="token punctuation" style=color:#393A34>(</span><span class="token operator" style=color:#393A34>*</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> grant_count</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_grant g</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization funder </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funder_org_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization recipient </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">recipient_org_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> recipient</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">is_private_foundation </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>TRUE</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> recipient</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=2-nonprofit-government-relationships>2. Nonprofit-Government Relationships<a href=#2-nonprofit-government-relationships class=hash-link aria-label="Direct link to 2. Nonprofit-Government Relationships" title="Direct link to 2. Nonprofit-Government Relationships" translate=no></a></h3> | |
| <p>Which nonprofits receive the most government funding?</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>SUM</span><span class="token punctuation" style=color:#393A34>(</span><span class="token keyword" style=color:#00009f>CASE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>WHEN</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funding_source </span><span class="token operator" style=color:#393A34>IN</span><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>(</span><span class="token string" style=color:#e3116c>'federal'</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'state'</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>THEN</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount </span><span class="token keyword" style=color:#00009f>ELSE</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>0</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>END</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> govt_grants</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>COUNT</span><span class="token punctuation" style=color:#393A34>(</span><span class="token keyword" style=color:#00009f>CASE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>WHEN</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funding_source </span><span class="token operator" style=color:#393A34>IN</span><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>(</span><span class="token string" style=color:#e3116c>'federal'</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'state'</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>THEN</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>END</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> govt_grant_count</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> dim_organization o</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>LEFT</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> fact_grant g </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">recipient_org_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>HAVING</span><span class="token plain"> govt_grants </span><span class="token operator" style=color:#393A34>></span><span class="token plain"> </span><span class="token number" style=color:#36acaa>0</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>ORDER</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> govt_grants </span><span class="token keyword" style=color:#00009f>DESC</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=3-foundation-investment-patterns>3. Foundation Investment Patterns<a href=#3-foundation-investment-patterns class=hash-link aria-label="Direct link to 3. Foundation Investment Patterns" title="Direct link to 3. Foundation Investment Patterns" translate=no></a></h3> | |
| <p>990-PF Schedule I analysis:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Where are private foundations investing?</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">program_area</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>COUNT</span><span class="token punctuation" style=color:#393A34>(</span><span class="token keyword" style=color:#00009f>DISTINCT</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> foundation_count</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>SUM</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> total_investment</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">grant_amount</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> avg_grant_size</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_grant g</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization funder </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">funder_org_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> funder</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">is_private_foundation </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>TRUE</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">program_area </span><span class="token operator" style=color:#393A34>IS</span><span class="token plain"> </span><span class="token operator" style=color:#393A34>NOT</span><span class="token plain"> </span><span class="token boolean" style=color:#36acaa>NULL</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> g</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">program_area</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>ORDER</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> total_investment </span><span class="token keyword" style=color:#00009f>DESC</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=4-financial-health-benchmarking>4. Financial Health Benchmarking<a href=#4-financial-health-benchmarking class=hash-link aria-label="Direct link to 4. Financial Health Benchmarking" title="Direct link to 4. Financial Health Benchmarking" translate=no></a></h3> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token comment" style=color:#999988;font-style:italic>-- Compare your nonprofit to sector averages</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WITH</span><span class="token plain"> sector_avg </span><span class="token keyword" style=color:#00009f>AS</span><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> SUBSTR</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> sector</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">overhead_ratio</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> avg_overhead</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token function" style=color:#d73a49>AVG</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fundraising_efficiency</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> avg_efficiency</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_nonprofit_finance f</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization o </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">tax_year </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2023</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>GROUP</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>BY</span><span class="token plain"> SUBSTR</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_name</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">overhead_ratio</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> s</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">avg_overhead </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> sector_avg_overhead</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">fundraising_efficiency</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> s</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">avg_efficiency </span><span class="token keyword" style=color:#00009f>as</span><span class="token plain"> sector_avg_efficiency</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_nonprofit_finance f</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> dim_organization o </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">organization_key</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>JOIN</span><span class="token plain"> sector_avg s </span><span class="token keyword" style=color:#00009f>ON</span><span class="token plain"> SUBSTR</span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain">o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ntee_code</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>,</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>1</span><span class="token punctuation" style=color:#393A34>)</span><span class="token plain"> </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> s</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">sector</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> f</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">tax_year </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token number" style=color:#36acaa>2023</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token operator" style=color:#393A34>AND</span><span class="token plain"> o</span><span class="token punctuation" style=color:#393A34>.</span><span class="token plain">ein </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'YOUR-EIN-HERE'</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=backward-compatibility>Backward Compatibility<a href=#backward-compatibility class=hash-link aria-label="Direct link to Backward Compatibility" title="Direct link to Backward Compatibility" translate=no></a></h2> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=deprecated-fields>Deprecated Fields<a href=#deprecated-fields class=hash-link aria-label="Direct link to Deprecated Fields" title="Direct link to Deprecated Fields" translate=no></a></h3> | |
| <p>The following fields in <code>dim_measure</code> are renamed for generic use:</p> | |
| <table><thead><tr><th>Old Field<th>New Field<th>Notes<tbody><tr><td><code>nohss_indicator_nbr</code><td><code>indicator_nbr</code><td>Generic indicator number<tr><td><code>nohss_indicator_group_type</code><td><code>indicator_group_type</code><td>Generic grouping<tr><td><code>nohss_indicator_desc</code><td><code>indicator_desc</code><td>Generic description</table> | |
| <h3 class="anchor anchorTargetStickyNavbar_Vzrq" id=views-for-compatibility>Views for Compatibility<a href=#views-for-compatibility class=hash-link aria-label="Direct link to Views for Compatibility" title="Direct link to Views for Compatibility" translate=no></a></h3> | |
| <p>Create views to maintain old query compatibility:</p> | |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style=--prism-color:#393A34;--prism-background-color:#f6f8fa><div class=codeBlockContent_QJqH><pre tabindex=0 class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style=color:#393A34;background-color:#f6f8fa><code class=codeBlockLines_e6Vv><div class=token-line style=color:#393A34><span class="token keyword" style=color:#00009f>CREATE</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>VIEW</span><span class="token plain"> fact_oral_health_observation </span><span class="token keyword" style=color:#00009f>AS</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> </span><span class="token operator" style=color:#393A34>*</span><span class="token plain"> </span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> fact_communityone_observation</span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> measure_key </span><span class="token operator" style=color:#393A34>IN</span><span class="token plain"> </span><span class="token punctuation" style=color:#393A34>(</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>SELECT</span><span class="token plain"> measure_key </span><span class="token keyword" style=color:#00009f>FROM</span><span class="token plain"> dim_measure </span><br/></div><div class=token-line style=color:#393A34><span class="token plain"> </span><span class="token keyword" style=color:#00009f>WHERE</span><span class="token plain"> indicator_group_type </span><span class="token operator" style=color:#393A34>=</span><span class="token plain"> </span><span class="token string" style=color:#e3116c>'oral_health'</span><span class="token plain"></span><br/></div><div class=token-line style=color:#393A34><span class="token plain"></span><span class="token punctuation" style=color:#393A34>)</span><span class="token punctuation" style=color:#393A34>;</span><br/></div></code></pre></div></div> | |
| <h2 class="anchor anchorTargetStickyNavbar_Vzrq" id=questions>Questions?<a href=#questions class=hash-link aria-label="Direct link to Questions?" title="Direct link to Questions?" translate=no></a></h2> | |
| <ul> | |
| <li class=""><strong>Schema issues:</strong> See <a class="" href=/docs/data-sources/data-model-erd>Data Model ERD</a></li> | |
| <li class=""><strong>Grant data sources:</strong> See <a class="" href=/docs/data-sources/nonprofit-sources>Nonprofit Data Sources</a></li> | |
| <li class=""><strong>990-PF parsing:</strong> See <a class="" href=/docs/data-sources/form-990-xml>Form 990 XML Guide</a></li> | |
| </ul></div><footer class="theme-doc-footer docusaurus-mt-lg"><div class="row margin-top--sm theme-doc-footer-edit-meta-row"><div class="col noPrint_WFHX"><a href=https://github.com/getcommunityone/open-navigator-for-engagement/tree/main/website/docs/deployment/schema-migration.md target=_blank rel="noopener noreferrer" class=theme-edit-this-page><svg fill=currentColor height=20 width=20 viewBox="0 0 40 40" class=iconEdit_Z9Sw aria-hidden=true><g><path d="m34.5 11.7l-3 3.1-6.3-6.3 3.1-3q0.5-0.5 1.2-0.5t1.1 0.5l3.9 3.9q0.5 0.4 0.5 1.1t-0.5 1.2z m-29.5 17.1l18.4-18.5 6.3 6.3-18.4 18.4h-6.3v-6.2z"/></g></svg>Edit this page</a></div><div class="col lastUpdated_JAkA"></div></div></footer></article><nav class="docusaurus-mt-lg pagination-nav" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href=/docs/deployment/authentication-setup><div class=pagination-nav__sublabel>Previous</div><div class=pagination-nav__label>Authentication Setup Guide</div></a><a class="pagination-nav__link pagination-nav__link--next" href=/docs/deployment/build-verification><div class=pagination-nav__sublabel>Next</div><div class=pagination-nav__label>Build Verification & CI/CD</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href=#overview class="table-of-contents__link toc-highlight">Overview</a><li><a href=#what-changed class="table-of-contents__link toc-highlight">What Changed</a><ul><li><a href=#file-rename class="table-of-contents__link toc-highlight">File Rename</a><li><a href=#table-renames class="table-of-contents__link toc-highlight">Table Renames</a><li><a href=#dimension-table-updates class="table-of-contents__link toc-highlight">Dimension Table Updates</a><ul><li><a href=#new-dim_jurisdiction class="table-of-contents__link toc-highlight">New: <code>dim_jurisdiction</code></a><li><a href=#new-dim_organization class="table-of-contents__link toc-highlight">New: <code>dim_organization</code></a></ul><li><a href=#new-fact-tables-previously-missing class="table-of-contents__link toc-highlight">New Fact Tables (Previously Missing)</a><ul><li><a href=#1-fact_grant---grant-transactions class="table-of-contents__link toc-highlight">1. <code>fact_grant</code> - Grant Transactions</a><li><a href=#2-fact_nonprofit_finance---annual-990-filings class="table-of-contents__link toc-highlight">2. <code>fact_nonprofit_finance</code> - Annual 990 Filings</a><li><a href=#3-fact_jurisdiction_budget---government-finances class="table-of-contents__link toc-highlight">3. <code>fact_jurisdiction_budget</code> - Government Finances</a><li><a href=#4-fact_meeting---meetings--public-hearings class="table-of-contents__link toc-highlight">4. <code>fact_meeting</code> - Meetings & Public Hearings</a><li><a href=#5-bridge_grant_program_area---grant-multi-purpose-support class="table-of-contents__link toc-highlight">5. <code>bridge_grant_program_area</code> - Grant Multi-Purpose Support</a></ul></ul><li><a href=#migration-steps class="table-of-contents__link toc-highlight">Migration Steps</a><ul><li><a href=#1-for-databricks-users class="table-of-contents__link toc-highlight">1. For Databricks Users</a><li><a href=#2-for-existing-data class="table-of-contents__link toc-highlight">2. For Existing Data</a><li><a href=#3-update-application-code class="table-of-contents__link toc-highlight">3. Update Application Code</a></ul><li><a href=#new-capabilities-enabled class="table-of-contents__link toc-highlight">New Capabilities Enabled</a><ul><li><a href=#1-grant-flow-analysis class="table-of-contents__link toc-highlight">1. Grant Flow Analysis</a><li><a href=#2-nonprofit-government-relationships class="table-of-contents__link toc-highlight">2. Nonprofit-Government Relationships</a><li><a href=#3-foundation-investment-patterns class="table-of-contents__link toc-highlight">3. Foundation Investment Patterns</a><li><a href=#4-financial-health-benchmarking class="table-of-contents__link toc-highlight">4. Financial Health Benchmarking</a></ul><li><a href=#backward-compatibility class="table-of-contents__link toc-highlight">Backward Compatibility</a><ul><li><a href=#deprecated-fields class="table-of-contents__link toc-highlight">Deprecated Fields</a><li><a href=#views-for-compatibility class="table-of-contents__link toc-highlight">Views for Compatibility</a></ul><li><a href=#questions class="table-of-contents__link toc-highlight">Questions?</a></ul></div></div></div></div></main></div></div></div><footer class="theme-layout-footer footer footer--dark"><div class="container container-fluid"><div class="row footer__links"><div class="theme-layout-footer-column col footer__col"><div class=footer__title>Documentation</div><ul class="footer__items clean-list"><li class=footer__item><a class=footer__link-item href=/docs/intro>Getting Started</a><li class=footer__item><a class=footer__link-item href=/docs/data-sources/citations>Citations & Data Sources</a><li class=footer__item><a class=footer__link-item href=/docs/data-sources/overview>Data Sources</a><li class=footer__item><a class=footer__link-item href=/docs/for-developers>For Developers</a></ul></div><div class="theme-layout-footer-column col footer__col"><div class=footer__title>Resources</div><ul class="footer__items clean-list"><li class=footer__item><a href=https://www.communityone.com target=_blank rel="noopener noreferrer" class=footer__link-item>Launch Open Navigator<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://github.com/getcommunityone/open-navigator-for-engagement target=_blank rel="noopener noreferrer" class=footer__link-item>GitHub<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://www.groundvue.org/ target=_blank rel="noopener noreferrer" class=footer__link-item>GroundVue (Partner)<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a></ul></div><div class="theme-layout-footer-column col footer__col"><div class=footer__title>Community</div><ul class="footer__items clean-list"><li class=footer__item><a href=https://www.instagram.com/getcommunityone/ target=_blank rel="noopener noreferrer" class=footer__link-item>Instagram<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://www.facebook.com/getcommunityone target=_blank rel="noopener noreferrer" class=footer__link-item>Facebook<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://x.com/getcommunityone/ target=_blank rel="noopener noreferrer" class=footer__link-item>X (Twitter)<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://www.linkedin.com/company/getcommunityone target=_blank rel="noopener noreferrer" class=footer__link-item>LinkedIn<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://www.youtube.com/@getcommunityone target=_blank rel="noopener noreferrer" class=footer__link-item>YouTube<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a><li class=footer__item><a href=https://discord.gg/uH6Dytek target=_blank rel="noopener noreferrer" class=footer__link-item>Discord<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a></ul></div><div class="theme-layout-footer-column col footer__col"><div class=footer__title>Legal</div><ul class="footer__items clean-list"><li class=footer__item><a class=footer__link-item href=/docs/legal/privacy-policy>Privacy Policy</a><li class=footer__item><a class=footer__link-item href=/docs/legal/terms-of-service>Terms of Service</a><li class=footer__item><a class=footer__link-item href=/docs/legal/data-provider-terms>Data Provider Terms</a></ul></div><div class="theme-layout-footer-column col footer__col"><div class=footer__title>More</div><ul class="footer__items clean-list"><li class=footer__item><a class=footer__link-item href=/blog>Blog</a><li class=footer__item><a href=https://github.com/getcommunityone/open-navigator-for-engagement/blob/main/LICENSE target=_blank rel="noopener noreferrer" class=footer__link-item>License (MIT)<svg width=13.5 height=13.5 aria-label="(opens in new tab)" class=iconExternalLink_nPIU><use href=#theme-svg-external-link /></svg></a></ul></div></div><div class="footer__bottom text--center"><div class=footer__copyright>Copyright © 2026 Community One. Built with Docusaurus.</div></div></div></footer></div></body> |