datasets/fdic/pipelines/institutions/pipeline.yaml

# Copyright 2022 Google LLC # # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. --- resources: - type: bigquery_table table_id: institutions description: "FDIC table" dag: airflow_version: 2 initialize: dag_id: institutions default_args: owner: "Google" depends_on_past: False start_date: "2022-09-28" max_active_runs: 1 schedule_interval: "@daily" catchup: False default_view: graph tasks: - operator: "KubernetesPodOperator" description: "Run CSV transform within kubernetes pod" args: task_id: "transform_institutions_to_csv" startup_timeout_seconds: 600 name: "institutions" namespace: "composer-user-workloads" service_account_name: "default" config_file: "/home/airflow/composer_kube_config" image_pull_policy: "Always" image: "{{ var.json.fdic.container_registry.run_csv_transform_kub }}" env_vars: SOURCE_URL: "https://s3-us-gov-west-1.amazonaws.com/cg-2e5c99a6-e282-42bf-9844-35f5430338a5/downloads/institutions.csv" SOURCE_FILE: "files/institutions.csv" TARGET_FILE: "files/data_output.csv" TARGET_GCS_BUCKET: "{{ var.value.composer_bucket }}" TARGET_GCS_PATH: "data/fdic/data_output_institutions.csv" PIPELINE_NAME: "institutions" CSV_HEADERS: >- [ "fdic_certificate_number", "institution_name", "state_name", "fdic_id", "docket", "active", "address", "total_assets", "bank_charter_class", "change_code_1", "change_code_2", "change_code_3", "change_code_4", "change_code_5", "change_code_6", "change_code_7", "change_code_8", "change_code_9", "change_code_10", "change_code_11", "change_code_12", "change_code_13", "change_code_14", "change_code_15", "occ_charter", "chartering_agency", "conservatorship", "city", "category_code", "county_fips_code", "county_name", "established_date", "last_updated", "effective_date", "end_effective_date", "denovo_institute", "total_deposits", "equity_capital", "fdic_geo_region", "fdic_supervisory_region", "fdic_supervisory_region_code", "fed_reserve_district", "fed_reserve_district_id", "fed_reserve_unique_id", "federal_charter", "fdic_field_office", "iba", "inactive_flag", "insurance_fund_membership", "secondary_insurance_fund", "deposit_insurance_date", "credit_card_institution", "bank_insurance_fund_member", "insured_commercial_bank", "deposit_insurance_fund_member", "fdic_insured", "saif_insured", "insured_savings_institute", "new_cert_number", "oakar_institute", "ots_region", "last_structural_change", "qbp_region", "regulator", "report_date", "reporting_period_end_date", "state_chartered", "return_on_assets", "roa_quarterly", "roa_pretax", "row_pretax_quarterly", "return_on_equity", "roe_quarterly", "run_date", "sasser_institute", "law_sasser", "state", "state_fips_code", "trade_name_1", "trade_name_2", "trade_name_3", "trade_name_4", "trade_name_5", "trade_name_6", "zip_code", "occ_district", "ultimate_cert_number", "cfpb_supervisory_flag", "cfpb_supervisory_start_date", "cfpb_supervisory_end_date", "offices_count", "parent_fdic_cert", "parent_parcert", "high_holder_city", "total_domestic_deposits", "ffiec_call_report_filer", "holding_company_flag", "ag_lending_flag", "ownership_type", "top_holder", "net_income", "quarterly_net_income", "office_count_domestic", "office_count_foreign", "office_count_us_territories", "rssd_id", "holding_company_state", "subchap_s_indicator", "trust_powers_status", "asset_concentration_hierarchy", "primary_specialization", "csa_name", "csa_fips_code", "csa_indicator", "cbsa_name", "cbsa_fips_code", "cbsa_metro_flag", "cbsa_micro_flag", "cbsa_division_name", "cbsa_division_fips_code", "cbsa_division_flag" ] RENAME_MAPPINGS: >- { "CERT":"fdic_certificate_number","NAME":"institution_name","STNAME":"state_name","UNINUM":"fdic_id","DOCKET":"docket", "ACTIVE":"active","ADDRESS":"address","ASSET":"total_assets","BKCLASS":"bank_charter_class","CHANGEC1":"change_code_1", "CHANGEC2":"change_code_2","CHANGEC3":"change_code_3","CHANGEC4":"change_code_4","CHANGEC5":"change_code_5", "CHANGEC6":"change_code_6","CHANGEC7":"change_code_7","CHANGEC8":"change_code_8","CHANGEC9":"change_code_9", "CHANGEC10":"change_code_10","CHANGEC11":"change_code_11","CHANGEC12":"change_code_12","CHANGEC13":"change_code_13", "CHANGEC14":"change_code_14","CHANGEC15":"change_code_15","CHARTER":"occ_charter","CHRTAGNT":"chartering_agency", "CONSERVE":"conservatorship","CITY":"city","CLCODE":"category_code","STCNTY":"county_fips_code","COUNTY":"county_name", "ESTYMD":"established_date","DATEUPDT":"last_updated","EFFDATE":"effective_date","ENDEFYMD":"end_effective_date", "DENOVO":"denovo_institute","DEP":"total_deposits","EQ":"equity_capital","FDICDBS":"fdic_geo_region","FDICREGN":"fdic_supervisory_region", "SUPRV_FD":"fdic_supervisory_region_code","FDICSUPV":"fed_reserve_district","FED":"fed_reserve_district_id","FED_RSSD":"fed_reserve_unique_id", "FLDOFF":"fdic_field_office","IBA":"iba","INACTIVE":"inactive_flag","INSAGNT1":"insurance_fund_membership","INSAGNT2":"secondary_insurance_fund", "INSDATE":"deposit_insurance_date","INSTCRCD":"credit_card_institution","INSBIF":"bank_insurance_fund_member","INSCOML":"insured_commercial_bank", "INSDIF":"deposit_insurance_fund_member","INSFDIC":"fdic_insured","INSSAIF":"saif_insured","INSSAVE":"insured_savings_institute", "NEWCERT":"new_cert_number","OAKAR":"oakar_institute","OTSDIST":"ots_district","OTSREGNM":"ots_region","PROCDATE":"last_structural_change", "QBPRCOML":"qbp_region","REGAGNT":"regulator","REPDTE":"report_date","RISDATE":"reporting_period_end_date","STCHRTR":"state_chartered", "ROA":"return_on_assets","ROAQ":"roa_quarterly","ROAPTX":"roa_pretax","ROAPTXQ":"row_pretax_quarterly","ROE":"return_on_equity", "ROEQ":"roe_quarterly","RUNDATE":"run_date","SASSER":"sasser_institute","LAW_SASSER_FLG":"law_sasser","STALP":"state", "STNUM":"state_fips_code","TE01N529":"trade_name_1","TE02N529":"trade_name_2","TE03N529":"trade_name_3","TE04N529":"trade_name_4", "TE05N529":"trade_name_5","TE06N529":"trade_name_6","ZIP":"zip_code","OCCDIST":"occ_district","ULTCERT":"ultimate_cert_number", "CFPBFLAG":"cfpb_supervisory_flag","CFPBEFFDTE":"cfpb_supervisory_start_date","CFPBENDDTE":"cfpb_supervisory_end_date", "OFFICES":"offices_count","CERTCONS":"parent_fdic_cert","PARCERT":"parent_parcert","CITYHCR":"high_holder_city", "DEPDOM":"total_domestic_deposits","FORM31":"ffiec_call_report_filer","HCTMULT":"holding_company_flag","INSTAG":"ag_lending_flag", "MUTUAL":"ownership_type","NAMEHCR":"top_holder","NETINC":"net_income","NETINCQ":"quarterly_net_income", "OFFDOM":"office_count_domestic","OFFFOR":"office_count_foreign","OFFOA":"office_count_us_territories","RSSDHCR":"rssd_id", "STALPHCR":"holding_company_state","SUBCHAPS":"subchap_s_indicator","TRUST":"trust_powers_status","SPECGRP":"asset_concentration_hierarchy", "SPECGRPN":"primary_specialization","CSA":"csa_name","CSA_NO":"csa_fips_code","CSA_FLG":"csa_indicator","CBSA":"cbsa_name", "CBSA_NO":"cbsa_fips_code","CBSA_METRO_FLG":"cbsa_metro_flag","CBSA_MICRO_FLG":"cbsa_micro_flag","CBSA_DIV":"cbsa_division_name", "CBSA_DIV_NO":"cbsa_division_fips_code","CBSA_DIV_FLG":"cbsa_division_flag","FEDCHRTR":"federal_charter" } REPLACE_BOOL_LIST: >- ["active","conservatorship","denovo_institute","federal_charter","iba","inactive_flag","credit_card_institution","bank_insurance_fund_member", "insured_commercial_bank","deposit_insurance_fund_member", "fdic_insured","saif_insured","insured_savings_institute", "oakar_institute","state_chartered","sasser_institute","law_sasser","cfpb_supervisory_flag","ffiec_call_report_filer", "holding_company_flag","ag_lending_flag","ownership_type","csa_indicator","cbsa_metro_flag","cbsa_micro_flag","cbsa_division_flag", "subchap_s_indicator"] REPLACE_DATE_LIST: >- ["last_updated","effective_date","end_effective_date","deposit_insurance_date","last_structural_change","report_date", "reporting_period_end_date","run_date","cfpb_supervisory_start_date","cfpb_supervisory_end_date"] FORMAT_DATE_LIST: >- ["established_date","last_updated","effective_date","end_effective_date","deposit_insurance_date","last_structural_change", "report_date","reporting_period_end_date","run_date"] NULL_LIST: >- ["total_assets","total_deposits","equity_capital","offices_count","total_domestic_deposits","net_income","quarterly_net_income", "office_count_domestic","office_count_foreign","office_count_us_territories"] container_resources: memory: request: "80Gi" cpu: request: "2" ephemeral-storage: request: "10Gi" - operator: "GoogleCloudStorageToBigQueryOperator" description: "Task to load CSV data to a BigQuery table" args: task_id: "load_institutions_to_bq" bucket: "{{ var.value.composer_bucket }}" source_objects: ["data/fdic/data_output_institutions.csv"] source_format: "CSV" destination_project_dataset_table: "fdic.institutions" skip_leading_rows: 1 allow_quoted_newlines: True write_disposition: "WRITE_TRUNCATE" schema_fields: - name: "fdic_certificate_number" type: "string" description: "A unique number assigned by the FDIC used to identify institutions and for the issuance of insurance certificates." mode: "nullable" - name: "institution_name" type: "string" description: "The legal name of the institution." mode: "nullable" - name: "state_name" type: "string" description: "State in which the the institution is physically located. The FDIC Act defines state as any State of the United States, the District of Columbia, and any territory of the United States, Puerto Rico, Guam, American Samoa, the Trust Territory of the Pacific Islands, the Virgin Island, and the Northern Mariana Islands." mode: "nullable" - name: "fdic_id" type: "string" description: "FDIC's unique identifier number for holding companies, banks, branches and nondeposit subsidiaries." mode: "nullable" - name: "docket" type: "string" description: "An identification number assigned to institutions chartered by the office of thrift supervision or members of the federal housing finance board (FHFB) and formerly by the federal home loan bank board. The value is '00000' for institutions not members of the FHFB." mode: "nullable" - name: "active" type: "boolean" description: "Institutions that are currently open and insured by the FDIC" mode: "nullable" - name: "address" type: "string" description: "Street address at which the institution or one of its branches is physically located." mode: "nullable" - name: "total_assets" type: "integer" description: "The sum of all assets owned by the institution including cash, loans, securities, bank premises and other assets. This total does not include off-balance-sheet accounts." mode: "nullable" - name: "bank_charter_class" type: "string" description: "A classification code assigned by the FDIC based on the institution's charter type (commercial bank or savings institution), charter agent (state or federal), Federal Reserve membership status (Fed member, Fed nonmember)and its primary federal regulator (state chartered institutions are subject to both federal and state supervision). N = commercial bank, national (federal) charter and Fed member, supervised by the Office of the Comptroller of the Currency (OCC) SM = commercial bank, state charter and Fed member, supervised by the Federal Reserve (FRB) NM = commercial bank, state charter and Fed nonmember, supervised by the FDIC SB = savings banks, state charter, supervised by the FDIC SA = savings associations, state or federal charter, supervised by the Office of Thrift Supervision (OTS) OI = insured U.S. branch of a foreign chartered institution (IBA)" mode: "nullable" - name: "change_code_1" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_2" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_3" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_4" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_5" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_6" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_7" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_8" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_9" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_10" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_11" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_12" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_13" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_14" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "change_code_15" type: "string" description: "FDIC code used to signify a structural event relating to an institution. The definitions of the codes are available in the `bigquery-public-data.fdic_banks.change_codes`" mode: "nullable" - name: "occ_charter" type: "string" description: "A unique number assigned by the Office of the Comptroller of the Currency (OCC) used to identify institutions that it has chartered and regulates (i.e. national banks)." mode: "nullable" - name: "chartering_agency" type: "string" description: "All Chartering Agencies - State and Federal Comptroller of the Currency - Chartering authority for nationally chartered commercial banks and for federally chartered savings associations (The Office of Thrift Supervision (OTS) before 7/21/11) State (includes U.S. Territories) - Chartering authority for institutions that are not chartered by the OCC or OTS" mode: "nullable" - name: "conservatorship" type: "boolean" description: "A flag that indicates if an institution is being operated in government conservatorship." mode: "nullable" - name: "city" type: "string" description: "City in which an institution's headquarters or one of its branches is physically located." mode: "nullable" - name: "category_code" type: "string" description: "Numeric code which identifies the major and minor categories of an institution. Definitions of these are available in`bigquery-public-data.fdic_banks.category_code`" mode: "nullable" - name: "county_fips_code" type: "string" description: "A five digit number representing the state and county in which the institution is physically located. The first two digits represent the FIPS state numeric code and the last three digits represent the FIPS county numeric code." mode: "nullable" - name: "county_name" type: "string" description: "County where the institution is physically located (abbreviated if the county name exceeds 16 characters)." mode: "nullable" - name: "established_date" type: "date" description: "The date on which the institution began operations." mode: "nullable" - name: "last_updated" type: "date" description: "Date the data was last updated" mode: "nullable" - name: "effective_date" type: "date" description: "Effective Start Date of the data contained in this row." mode: "nullable" - name: "end_effective_date" type: "date" description: "The date that ends or closes out the last structural event relating to an institution. For closed institutions, this date represents the day that the institution became inactive." mode: "nullable" - name: "denovo_institute" type: "boolean" description: "A flag used to indicate whether an institution is a new institution (not a recharter). This flag is set quarterly. For instance, if REPDTE is 3/31/98 and DENOVO equals 1, the institution was a denovo during the first quarter of 1998." mode: "nullable" - name: "total_deposits" type: "integer" description: "The sum of all deposits including demand deposits, money market deposits, other savings deposits, time deposits and deposits in foreign offices." mode: "nullable" - name: "equity_capital" type: "integer" description: "Total equity capital (includes preferred and common stock, surplus and undivided profits)." mode: "nullable" - name: "fdic_geo_region" type: "string" description: "The FDIC Office assigned to the geographic area. The eight FDIC Regions and their respective states are: Boston - Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, Vermont New York - Delaware, District of Columbia, Maryland, New Jersey, New York, Pennsylvania, Puerto Rico, U.S. Virgin Islands Atlanta - Alabama, Florida, Georgia, North Carolina, South Carolina, Virginia, West Virginia Memphis - Arkansas, Kentucky, Louisiana, Mississippi, Tennessee Chicago - Illinois, Indiana, Michigan, Ohio, Wisconsin Kansas City - Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, South Dakota Dallas - Colorado, New Mexico, Oklahoma, Texas San Francisco - Alaska, American Samoa, Arizona, California, Guam, Hawaii, Idaho, Montana, Nevada, Oregon, States of Micronesia, Utah, Washington, Wyoming" mode: "nullable" - name: "fdic_supervisory_region" type: "string" description: "The supervisory FDIC office assigned to the institution. The eight FDIC Supervisory Regions and their respective states are: Boston - Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, Vermont New York - Delaware, District of Columbia, Maryland, New Jersey, New York, Pennsylvania, Puerto Rico, U.S. Virgin Islands Atlanta - Alabama, Florida, Georgia, North Carolina, South Carolina, Virginia, West Virginia Memphis - Arkansas, Kentucky, Louisiana, Mississippi, Tennessee Chicago - Illinois, Indiana, Michigan, Ohio, Wisconsin Kansas City - Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, South Dakota Dallas - Colorado, New Mexico, Oklahoma, Texas San Francisco - Alaska, American Samoa, Arizona, California, Guam, Hawaii, Idaho, Montana, Nevada, Oregon, States of Micronesia, Utah, Washington, Wyoming" mode: "nullable" - name: "fdic_supervisory_region_code" type: "string" description: "A numeric value associated with the name of an FDIC supervisory region 2 - New York - states: Connecticut, Delaware, Maine, Maryland, Massachusetts, New Hampshire, New Jersey, New York, Pennsylvania, Puerto Rico, Rhode Island, Vermont, Virgin Islands 5 - Atlanta - states: Alabama, Florida, Georgia, North Carolina, South Carolina, Virginia 9 - Chicago - states: Illinois, Indiana, Kentucky, Michigan, Ohio 11 - Kansas City - states: Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, South Dakota 13 - Dallas - states: Arkansas, Colorado, Louisiana, Mississippi, New Mexico, Oklahoma, Tennessee, Texas 14 - San Francisco - states: Alaska, American Samoa, Arizona, California, Federated States of Micronesia, Guam, Hawaii, Idaho, Montana, Nevada, Oregon, Utah, Washington, Wyoming 15 - Washington Office - Division of Risk Management Supervision (RMS) 16 - Washington Office - The Office of Complex Financial Institutions (CFI) " mode: "nullable" - name: "fed_reserve_district" type: "string" description: "" mode: "nullable" - name: "fed_reserve_district_id" type: "string" description: "A number used to identify the Federal Reserve district in which the institution is located. 01 = Boston, 02 - New York, 03 = Philadelphia, 04 = Cleveland, 05 = Richmond, 06 = Atlanta, 07 = Chicago, 08 - St. Louis, 09 = Minneapolis, 10 - Kansas city, 11 = Dallas, 12 - San Francisco" mode: "nullable" - name: "fed_reserve_unique_id" type: "string" description: "A unique number assigned by the Federal Reserve board as the entity's unique identifier" mode: "nullable" - name: "federal_charter" type: "boolean" description: "A flag used to indicate whether the institution is chartered by an agent of the federal government." mode: "nullable" - name: "fdic_field_office" type: "string" description: "The FDIC Field Office where an institution is physically located." mode: "nullable" - name: "iba" type: "boolean" description: "Includes Bank Insurance Fund insured branches in the U.S. established by banks chartered and headquartered in foreign countries. These institutions are regulated by one of the three Federal commercial bank regulators and submit financial data to the Federal Reserve." mode: "nullable" - name: "inactive_flag" type: "boolean" description: "Institutions that are currently closed but were once insured by the FDIC." mode: "nullable" - name: "insurance_fund_membership" type: "string" description: "Deposit Insurance Fund (DIF), Bank Insurance Fund (BIF), Savings Association Insurance Fund (SAIF)" mode: "nullable" - name: "secondary_insurance_fund" type: "string" description: "As a result of the establishment of a single Deposit Insurance Fund (DIF) effective April 1, 2006, the Secondary Insurance fund is no longer applicable. previously both bif and saif bank insurance fund - institutions that are members of the bank insurance fund savings association insurance fund - Institutions that are members of the Savings Association Insurance Fund" mode: "nullable" - name: "deposit_insurance_date" type: "date" description: "The date that an institution obtained federal deposit insurance." mode: "nullable" - name: "credit_card_institution" type: "boolean" description: "Institutions with total loans greater than 50% of total assets and credit card loans greater than 50% of total loans, including loans that have been securitized and sold." mode: "nullable" - name: "bank_insurance_fund_member" type: "boolean" description: "Institutions who are members of the Bank Insurance Fund. As of April 1, 2006 BIF was merged together with the Savings Institution Insurance Fund (SAIF) to create a single Deposit Insurance Fund (DIF). All FDIC insured BIF member institutions, that are still active or open, are now insured members of DIF." mode: "nullable" - name: "insured_commercial_bank" type: "boolean" description: "Includes commercial banks insured by the FDIC. These institutions are regulated by one of the three Federal commercial bank regulators (FDIC, Federal Reserve Board, or Office of the Comptroller of the Currency). They submit financial reports to the Federal Reserve (state member banks) or the FDIC (state non-member banks and national banks)." mode: "nullable" - name: "deposit_insurance_fund_member" type: "boolean" description: "A flag used to indicate whether an institution is insured under the Deposit Insurance Fund (DIF). As of April 1, 2006 the Bank Insurance Fund (BIF) was merged together with the Savings Institution Insurance Fund (SAIF) to create a single Deposit Insurance Fund (DIF). All FDIC insured BIF and SAIF member institutions that are still active or open are now insured members of DIF. False = No, not DIF insured and True = Yes, DIF insured. Note that institutions that became inactive prior to April 2006 will also have false value." mode: "nullable" - name: "fdic_insured" type: "boolean" description: "Includes institutions insured by the FDIC." mode: "nullable" - name: "saif_insured" type: "boolean" description: "Institutions who are members of the Savings Association Insurance Fund. As of April 1, 2006 SAIF was merged together with the Bank Insurance Fund (BIF) to create a single Deposit Insurance Fund (DIF). All FDIC insured SAIF member institutions, that are still active or open, are now insured members of DIF." mode: "nullable" - name: "insured_savings_institute" type: "boolean" description: "Includes savings institutions insured by the FDIC that operate under state or federal banking codes applicable to thrift institutions. These institutions are regulated by and submit financial reports to one of two Federal regulators (FDIC or Office of Thrift Supervision)." mode: "nullable" - name: "new_cert_number" type: "string" description: "A new certificate number of an already existing FDIC-insured institution resulting from either a merger or an acquisition." mode: "nullable" - name: "oakar_institute" type: "boolean" description: "A member of one insurance fund that acquired deposits insured by the other fund, where that portion of the buyer's deposits remained insured by, and assessable by, the other fund." mode: "nullable" - name: "ots_region" type: "string" description: "Prior to 7/21/11, the Office of Thrift Supervision (OTS) Region in which the institution is physically located. The five OTS Regions and their respective states are: Northeast - Connecticut, Delaware, Maine, Massachusetts, New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island, Vermont, West Virginia Southeast - Alabama, District of Columbia, Florida, Georgia, Maryland, North Carolina, Puerto Rico, South Carolina, U.S. Virgin Islands, Virginia Central - Illinois, Indiana, Kentucky, Michigan, Ohio, Tennessee, Wisconsin Midwest - Arkansas, Colorado, Iowa, Kansas, Louisiana, Minnesota, Mississippi, Missouri, Nebraska, New Mexico, North Dakota, Oklahoma, South Dakota, Texas West - Alaska, American Samoa, Arizona, California, Guam, Hawaii, Idaho, Montana, Nevada, States of Micronesia, Oregon, Utah, Washington, Wyoming " mode: "nullable" - name: "last_structural_change" type: "date" description: "A date field indicating the date that a change to this record was processed. Standard format = 'CCYYMMDD' (Length = 8) which has been converted to Month, Day, Year format for display purposes." mode: "nullable" - name: "qbp_region" type: "string" description: "The Quarterly Banking Profile (QBP) Commercial Bank Region in which the institution is physically located." mode: "nullable" - name: "regulator" type: "string" description: "" mode: "nullable" - name: "report_date" type: "date" description: "The last day of the financial reporting period selected." mode: "nullable" - name: "reporting_period_end_date" type: "date" description: "The financial reporting period selected in CCYYMM format." mode: "nullable" - name: "state_chartered" type: "boolean" description: "A flag that indicates if an institution is state chartered." mode: "nullable" - name: "return_on_assets" type: "float" description: "Net income after taxes and extraordinary items (annualized) as a percent of average total assets." mode: "nullable" - name: "roa_quarterly" type: "float" description: "Quarterly net income after taxes and extraordinary items as a percent of average total assets." mode: "nullable" - name: "roa_pretax" type: "float" description: "Annualized pre-tax net income as a percent of average assets. Note: Includes extraordinary items and other adjustments, net of taxes." mode: "nullable" - name: "row_pretax_quarterly" type: "float" description: "Quarterly pre-tax net income as a percent of average assets. Note: Includes extraordinary items and other adjustments, net of taxes." mode: "nullable" - name: "return_on_equity" type: "float" description: "Annualized net income as a percent of average equity on a consolidated basis. Note: If retained earnings are negative, the ratio is shown as NA." mode: "nullable" - name: "roe_quarterly" type: "float" description: "Quarterly net income (including gains or losses on securities and extraordinary items) as a percentage of average total equity capital." mode: "nullable" - name: "run_date" type: "date" description: "The day the institution information was updated." mode: "nullable" - name: "sasser_institute" type: "boolean" description: "OTS supervised savings associations that converted their charter to that of a commercial or savings bank. Converted associations remain members of the SAIF, but they become subject to supervision by one of the three federal banking agencies. Not Applicable as of March 31, 2006." mode: "nullable" - name: "law_sasser" type: "boolean" description: "A flag associated with OTS supervised savings associations that converted their charter to that of a commercial or savings bank. Converted associations remain members of the SAIF, but they become subject to supervision by one of the three federal banking agencies. Not Applicable as of March 31, 2006." mode: "nullable" - name: "state" type: "string" description: "2 letter postal abbreviation for the state in which the the headquarters are physically located. The FDIC Act defines state as any State of the United States, the District of Columbia, and any territory of the United States, Puerto Rico, Guam, American Samoa, the Trust Territory of the Pacific Islands, the Virgin Island, and the Northern Mariana Islands." mode: "nullable" - name: "state_fips_code" type: "string" description: "2 digit FIPS code to uniquely identify the state" mode: "nullable" - name: "trade_name_1" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "trade_name_2" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "trade_name_3" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "trade_name_4" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "trade_name_5" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "trade_name_6" type: "string" description: "Trade name other than the institution's legal name used to identify one of the institution's physical offices at which deposits are accepted or solicited from the public" mode: "nullable" - name: "zip_code" type: "string" description: "The first three, four, or five digits of the full postal zip code representing physical location of the institution or its branch office." mode: "nullable" - name: "occ_district" type: "string" description: "The Office of the Comptroller of the Currency (OCC) District in which the institution is physically located. The six OCC Districts and their respective states are: Northeast - Connecticut, Delaware, District of Columbia, Maine, Maryland, Massachusetts, New Hampshire, New Jersey, New York, Pennsylvania, Puerto Rico, Rhode Island, Vermont, U.S. Virgin Islands Southeast - Alabama, Florida, Georgia, Mississippi, North Carolina, South Carolina, Tennessee, Virginia, West Virginia Central - Illinois, Indiana, Kentucky, Michigan, Ohio, Wisconsin Midwest - Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, South Dakota Southwest - Arkansas, Louisiana, New Mexico, Oklahoma, Texas West - Alaska, American Samoa, Arizona, California, Colorado, Guam, Hawaii, Idaho, Montana, Nevada, Oregon, States of Micronesia, Utah, Washington, Wyoming" mode: "nullable" - name: "ultimate_cert_number" type: "string" description: "The cert number of the last successor or acquirer of the institution" mode: "nullable" - name: "cfpb_supervisory_flag" type: "boolean" description: "Indicates secondary supervision by CFPB" mode: "nullable" - name: "cfpb_supervisory_start_date" type: "date" description: "Date the institution began secondary supervision by CFPB" mode: "nullable" - name: "cfpb_supervisory_end_date" type: "date" description: "Date the institution ended supervision by CFPB" mode: "nullable" - name: "offices_count" type: "integer" description: "" mode: "nullable" - name: "parent_fdic_cert" type: "string" description: "FDIC certificate number of the parent bank or savings institution with which the reported institution;s financial data has been consolidated. Beginning in March 1997, both the Thrift Financial Reports and Call Reports are completed on a fully consolidated basis. Previously, the consolidation of subsidiary depository institutions was prohibited. Now, parent institutions are required to file consolidated reports, while their subsidiary financial institutions are still required to file separate reports. Click on the certificate number to identify the parent bank or thrift." mode: "nullable" - name: "parent_parcert" type: "string" description: "The PARCERT number identifies the subsidiary institutions parent certificate number. Beginning in March 1997, both the Thrift Financial Reports and Call Reports are completed on a fully consolidated basis. Previously, the consolidation of subsidiary depository institutions was prohibited. Now, parent institutions are required to file consolidated reports, while their subsidiary financial institutions are still required to file separate reports." mode: "nullable" - name: "high_holder_city" type: "string" description: "City in which the headquarters of the institution's regulatory high holder are physically located." mode: "nullable" - name: "total_domestic_deposits" type: "integer" description: "The sum of all domestic office deposits, including demand deposits, money market deposits, other savings deposits and time deposits." mode: "nullable" - name: "ffiec_call_report_filer" type: "boolean" description: "A flag that indicates whether and institution filed an FFIEC 031 Call Report. Commercial banks with domestic and foreign offices are required to file such a report." mode: "nullable" - name: "holding_company_flag" type: "boolean" description: "A flag used to indicate whether an institution is a member of a multibank holding company" mode: "nullable" - name: "ag_lending_flag" type: "boolean" description: "A flag used to indicate whether an institution is an agricultural lending institution" mode: "nullable" - name: "ownership_type" type: "string" description: "Banking institutions fall into one of two ownership types, stock or non-stock. An institution which sells stock to raise capital is called a stock institution. It is owned by the shareholders who benefit from profits earned by the institution. A non-stock institution, or mutual institution, is owned and controlled solely by its depositors. A mutual does not issue capital stock." mode: "nullable" - name: "top_holder" type: "string" description: "Regulatory top holder is assigned by the Federal Reserve Board based on ownership and control percentages. 'Note: Information on bank holding companies is only as of quarter-end. Regulatory top holder is any company that directly or indirectly owns, controls or has power to vote 25 percent or more of a bank's or direct holding company's shares or controls in any manner the election of a majority of the directors or trustees of a bank or direct holding company or exercises a controlling influence over the management or policies of a bank or direct holding company. Information on Thrift Holding Companies that own Savings Associations but do not own banks is not currently available in the ID System. Source: Federal Reserve Board National Information Center data base.'" mode: "nullable" - name: "net_income" type: "integer" description: "Net interest income plus total noninterest income plus realized gains (losses) on securities and extraordinary items, less total noninterest expense, loan loss provisions and income taxes." mode: "nullable" - name: "quarterly_net_income" type: "integer" description: "Quarterly net interest income plus total noninterest income plus realized gains (losses) on securities and extraordinary items, less total noninterest expense, loan loss provisions and income taxes." mode: "nullable" - name: "office_count_domestic" type: "integer" description: "The number of domestic offices (including headquarters) operated by active institutions in the 50 states of the U.S.A." mode: "nullable" - name: "office_count_foreign" type: "integer" description: "The number of foreign offices (outside the U.S.) operated by the institution." mode: "nullable" - name: "office_count_us_territories" type: "integer" description: "The number of offices operated by an FDIC-insured institution in all commonwealths and terrirtories of the US, along with those in freely associated states under the Compact of Free Association" mode: "nullable" - name: "rssd_id" type: "string" description: "The unique number assigned by the Federal Reserve Board to the regulatory high holding company of the institution." mode: "nullable" - name: "holding_company_state" type: "string" description: "State location of the regulatory high holding company (either direct or indirect owner)." mode: "nullable" - name: "subchap_s_indicator" type: "boolean" description: "" mode: "nullable" - name: "trust_powers_status" type: "string" description: "A flag used to indicate an institution's Trust Powers Granted status. 0 = No Trust Power Granted 1 = Trust Power Granted Where Trust Power has been granted specific codes are: 00 - Trust powers not know 10 - Full trust powers granted 11 - Full trust powers granted, exercised 12 - Full trust powers granted, not exercised 20 - Limited trust powers granted 21 - Limited trust powers granted, exercised 22 - Limited trust powers granted, not exercised 30 - Trust powers not granted 31 - Trust powers not granted, but exercised " mode: "nullable" - name: "asset_concentration_hierarchy" type: "string" description: "Asset Concentration Hierarchy - An indicator of an institution's primary specialization in terms of asset concentration 1 - International Specialization 2 - Agricultural Specialization 3 - Credit-card Specialization 4 - Commercial Lending Specialization 5 - Mortgage Lending Specialization 6 - Consumer Lending SpecializationI 7 - Other Specialized < $1 Billion 8 - All Other < $1 Billion 9 - All Other > $1 Billion" mode: "nullable" - name: "primary_specialization" type: "string" description: " Name associated with the numeric indicator (SPECGRP) of an institution's primary specialization in terms of asset concentration: 1 - International Specialization 2 - Agricultural Specialization 3 - Credit-card Specialization 4 - Commercial Lending Specialization 5 - Mortgage Lending Specialization 6 - Consumer Lending SpecializationI 7 - Other Specialized < $1 Billion 8 - All Other < $1 Billion 9 - All Other > $1 Billion" mode: "nullable" - name: "csa_name" type: "string" description: "The name associated with the numeric code that the U.S. Census Bureau Office of Management and Budget assigns for the combined statistical area (CSA) per the 2000 standards. If an institution is not defined as a CSA, the value of the field will be blank. For more information see: http://www.census.gov/population/www/estimates/metroarea.html . " mode: "nullable" - name: "csa_fips_code" type: "string" description: "The numeric code that the U.S. Census Bureau Office of Management and Budget assigns for the combined statistical area (CSA) per the 2000 standards. If an institution is not defined as a CSA, the value of the field will be zero. For more information see: http://www.census.gov/population/www/estimates/metroarea.html ." mode: "nullable" - name: "csa_indicator" type: "boolean" description: "A flag used to indicate whether an institution is in a Combined Statistical Area." mode: "nullable" - name: "cbsa_name" type: "string" description: "The name associated with the numeric code that the U.S. Census Bureau Office of Management and Budget assigns for the CBSA. The 2000 standards provide that each CBSA must contain at least one urban area of 10,000 or more population. Metropolitan and micropolitan statistical areas are two categories of core based statistical areas. If an institution is not defined as a CBSA, the value of the field will be zero. For more information see: http://www.census.gov/population/www/estimates/metroarea.html ." mode: "nullable" - name: "cbsa_fips_code" type: "string" description: "The numeric code that the U.S. Census Bureaus Office of Management and Budget assigns for the CBSA. The 2000 standards provide that each CBSA must contain at least one urban area of 10,000 or more population. Metropolitan and micropolitan statistical areas are two categories of core based statistical areas. If an institution is not defined as a CBSA, the value of the field will be zero. For more information see: http://www.census.gov/population/www/estimates/metroarea.html ." mode: "nullable" - name: "cbsa_metro_flag" type: "boolean" description: "A flag used to indicate whether an institution is in a metropolitan statistical area. The US Census bureau office of Management and Budget defines the metropolitan statistical area. A core based statistical area associated with at least one urbanized area that has a population of at least 50,000. The metropolitan statistical area comprises the central county or counties containing the core, plus adjacent outlying counties having a high degree of social and economic integration with the central county as measured through commuting." mode: "nullable" - name: "cbsa_micro_flag" type: "boolean" description: "A flag used to indicate whether an institution is in a micropolitan statistical area. The US Census bureau office of Management and Budget defines the micropolitan statistical area. A core based statistical area associated with at least one urbanized area that has a population of at least 50,000. The micropolitan statistical area comprises the central county or counties containing the core, plus adjacent outlying counties having a high degree of social and economic integration with the central county as measured through commuting." mode: "nullable" - name: "cbsa_division_name" type: "string" description: "The name associated with the numeric code given by the US Census Bureau office of Management and Budget (2000 standards) that represents the core based statistical division (CBSADIV). A metropolitan division is a county or group of counties within a core based statistical area that contains a core with a population of at least 2.5 million. A CBSA metropolitan division consists of one or more main/secondary counties that represent an employment center or centers, plus adjacent counties associated with the main county or counties through commuting ties. If an institution is not defined as a CBSA division the value of the field will be zero." mode: "nullable" - name: "cbsa_division_fips_code" type: "string" description: "The numeric code given by the US Census Bureau office of Management and Budget that represents the core based statistical division (CBSADIV) under the year 2000 standards. A metropolitan division is a county or group of counties within a core based statistical area that contains a core with a population of at least 2.5 million. A CBSA metropolitan division consists of one or more main/secondary counties that represent an employment center or centers, plus adjacent counties associated with the main county or counties through commuting ties. If an institution is not defined as a CBSA division the value of the field will be zero." mode: "nullable" - name: "cbsa_division_flag" type: "boolean" description: "A flag used to indicate whether an institution is in a CBSA division" mode: "nullable" graph_paths: - "transform_institutions_to_csv >> load_institutions_to_bq"

datasets/fdic/pipelines/institutions/pipeline.yaml (613 lines of code) (raw):