Banks (raw table)

Data license: CC-BY-4.0 · Data source: bankproducts.info

421 rows sorted by name descending

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: country, bank_type, gleif_registration_status, gleif_last_update (date)

Next page

CREATE TABLE bank (
    id          TEXT PRIMARY KEY,      -- zkb, postfinance, ubs, lgt
    name        TEXT NOT NULL,         -- Zürcher Kantonalbank
    country     TEXT NOT NULL,         -- CH, LI
    bank_type   TEXT,                  -- cantonal_bank, universal_bank, cooperative_bank, private_bank, state_bank, neobank
    website     TEXT,
    bic         TEXT,                  -- SWIFT/BIC code (e.g. ZHSZCHZZ80A)
    lei         TEXT,                  -- Legal Entity Identifier (20 chars)
    sic_iid     TEXT,                  -- SIX Interbank Clearing participant number
    uid         TEXT,                  -- Swiss UID (CHE-xxx.xxx.xxx) or national equivalent
    -- GLEIF-sourced fields (atom #336, v2.14). Populated from
    -- attributes.entity.status / attributes.registration.status /
    -- attributes.entity.successorEntity.lei / attributes.registration.lastUpdateDate
    -- via scripts/generate_gleif_backfill.py. NULL = no LEI or GLEIF didn't return.
    gleif_entity_status        TEXT,   -- ACTIVE, INACTIVE, NULL
    gleif_registration_status  TEXT,   -- ISSUED, LAPSED, RETIRED, PENDING_ARCHIVAL, DUPLICATE, MERGED, ANNULLED, NULL
    gleif_successor_lei        CHAR(20), -- successor entity's LEI when current is INACTIVE/RETIRED (e.g. merger)
    gleif_last_update          TEXT,   -- ISO date of GLEIF record's last update — freshness signal
    -- GLEIF Level-2 relationship fields (atom #342, v2.15). Populated by
    -- scripts/generate_parent_lei_backfill.py from GLEIF's
    -- /lei-records/{lei}/direct-parent and /ultimate-parent endpoints.
    -- Enables membership loaders to attribute parent-group memberships to the
    -- subsidiary bank we carry in the DB (e.g. ISDA membership registered at
    -- UBS AG applies to our `ubs` row via direct_parent_lei match).
    -- NULL means the entity is top-of-group in GLEIF's tree or has no
    -- reported parent relationship.
    direct_parent_lei          CHAR(20), -- GLEIF Level-2 direct parent LEI
    ultimate_parent_lei        CHAR(20), -- GLEIF Level-2 ultimate parent LEI
    bpg         CHAR(12)               -- opaque global id, BPG001xxxxxx; populated post-load by bpg_assignments.sql
        CHECK (bpg IS NULL OR bpg GLOB 'BPG001[0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z]')
);
CREATE INDEX idx_bank_country ON bank(country);
CREATE UNIQUE INDEX idx_bank_bic ON bank(bic);
CREATE UNIQUE INDEX idx_bank_bpg ON bank(bpg) WHERE bpg IS NOT NULL;
CREATE INDEX idx_bank_gleif_entity_status ON bank(gleif_entity_status) WHERE gleif_entity_status IS NOT NULL;