Documentação aprofundada (YAML-fonte)
Conteúdo bruto do arquivo-fonte para referência — a documentação curada está nas páginas de conjuntos de dados e tabelas.
# Curated deep documentation for the visual catalog.
#
# Every entry MUST reference a tabela_id that resolves in the loaded catalog
# (validated hard by the snapshot builder — a typo fails the publication,
# like any other catalog mistake). Facts here are grounded in the referenced
# evidence; unknowns stay null (absent fields render as "unknown" on the
# page). Prose from the catalog comments is NOT duplicated here — the table
# pages already render those blocks verbatim; this file adds only what the
# catalog does not structure: grain, units, safe-join guidance, curated
# lineage edges (each with its evidence), and a one-paragraph summary.
#
# An analyst-facing invariant: a page may show a safe-join warning only when
# it is stated here with evidence; Bruin `depends` edges are never rendered
# as lineage.
- tabela_id: cvm.fundos.cad_fi/cad_fi
summary: >
Fund registry (snapshot, daily). The grain is one row per fund per
manager assignment — the manager identifier is part of the declared key —
so this table is NOT one row per fund, and the silver current-fund view
is the only safe join target on cnpj_fundo alone.
grain: one row per fund per manager assignment (plus re-registration events)
units: null
safe_joins:
- "NEVER join on cnpj_fundo alone against bronze cad_fi: it multiplies rows
(manager fan-out). Join on the silver current-fund view (one row per
cnpj_fundo, tie-break documented) — ciano_lake/silver/fundos/cad_fi.py."
- "cpf_cnpj_gestor is NULL/empty in ~58% of rows and is whitelisted in the
key's null-rate gate; NULL manager rows still participate in the grain."
lineage_edges:
- from: cvm.fundos.cad_fi/cad_fi
to: silver current-fund view (cnpj_fundo unique)
kind: curated
evidence: "ciano_lake/silver/fundos/cad_fi.py (dedup policy keep_first applied there, never in bronze)"
evidence:
- "catalogo/cvm.yml — cad_fi entry (GRAIN decision + measured residual categories)"
- "ciano_lake/silver/fundos/cad_fi.py"
- "ARCHITECTURE.md §6 (the cad_fi fan-out rule)"
- tabela_id: cvm.fundos.cda_fi/fiim
summary: >
Fund-of-funds and derivative lot-level positions. Duplicates are the
expected shape of lot data: measured exact duplicate groups = 0 across
every competência probed; residual groups are genuinely contradictory
lot rows (same lot, two published quantities) — CVM's contradiction, not
a key design gap. Rows are per lot, so summing positions is only safe
with the lot semantics in mind.
grain: one row per position lot (tp_aplic/tp_ativo/cd_ativo + lot attributes)
units: >
Quantities in shares/lots (QT_*); values in BRL (VL_*). Position identity
for public bonds carries CD_ATIVO + DT_INI_VIGENCIA (series), CD_SELIC
and ID_DOC (declaration document).
safe_joins:
- "Join only on the declared 14-column key or a declared subset; there is
no single position identifier. Summing VL_MERC_POS_FINAL without the lot
dimension double-counts contradictory lot rows collapsed by silver
keep_first."
- "Identification columns (cd_ativo/ds_ativo/dt_venc/emissor/...) are
structurally NULL for illiquid/private assets (whitelisted per era);
NULL there is not a data error."
lineage_edges: []
evidence:
- "catalogo/cvm.yml — fiim entries (corrente + historico_1822, measured 2026-08-25)"
- "Traycer artifact design-tolerada-com-residuo (VL_PATRIM_LIQ-sum verification method)"
- tabela_id: cvm.fundos.cda_fi/pl
summary: >
Fund net equity (PL) — one row per fund per competência by construction.
Re-measured 2026-09-04: status is `tolerada` with a small measured,
capped exception (201104: excess=1, byte-identical — a CVM republication,
the same pattern as blc_8); the historical probes closed at 0 excess
(200812, 202212 through the DT_COMPTC row split). Silver collapses the
byte-identical repeat with keep_first. Still the cleanest CVM
fund-level table: safe to join on (tp_fundo, cnpj_fundo, dt_comptc)
knowing the capped, documented exception.
grain: >
one row per fund per competência (measured capped exception: one
byte-identical republication in 201104, cap 5)
units: values in BRL (VL_* columns)
safe_joins:
- "Safe to join/aggregate on (tp_fundo, cnpj_fundo, dt_comptc) — the key
closes with a single measured byte-identical republication in 201104
(tolerada, cap 5); silver keep_first collapses the repeat."
lineage_edges: []
evidence:
- "catalogo/cvm.yml — pl entry historico_0717 (re-measured 2026-09-04, tolerada cap 5)"
- "catalogo/cvm.yml — pl entries (historico_0506 and historico_1822, probes with filtro_linha split)"
- tabela_id: cvm.cia_aberta.cia_aberta_dfp/dmpl_con
summary: >
DFP consolidated statement of changes in equity (DMPL), annual. The key
carries the equity-column dimension coluna_df — a real CVM column,
confirmed by reading the raw zip header (not an invented dimension) —
plus ds_conta (the account is the pair cd_conta+ds_conta) and
ordem_exerc (current vs prior period). Residual groups are CVM
publishing two different VL_CONTA for the same account/period; silver
collapses them with keep_first and bronze's _residuos.json records the
discarded values.
grain: one row per (company, reference date, version, account pair, period column, equity column)
units: values in BRL (VL_CONTA); amounts as reported by the company
safe_joins:
- "Join only with the full declared key. Dropping coluna_df re-introduces
~197k excess rows (the 2025 measurement that exposed the missing
dimension); dropping ds_conta re-introduces label divergence (100% of
divergent-label groups are non-fixed, company-defined accounts)."
- "A row is current-period or prior-period per ordem_exerc — filter
before comparing periods or you compare a company against itself."
lineage_edges: []
evidence:
- "catalogo/cvm.yml — dmpl_con entry (measured 2026-09-08, caps 7000/80)"
- "ARCHITECTURE.md §6 — DFP/ITR keys (coluna_df, ds_conta, ordem_exerc rationale)"
- tabela_id: bacen.instituicoes.ifdata_cadastro/cadastro
summary: >
BACEN IF.DATA institution registry, quarterly — one row per institution
per consolidation variant per quarter. Fully verified: zero duplicate
keys and zero NULL key columns across the full 45-quarter series
(201503–202603, 199,988 rows).
grain: one row per (cod_inst, tipo_inst, periodo) as declared
units: null
safe_joins:
- "Join facts on (cod_inst, periodo, tipo_inst) — the exact gold join
keys. institution names are labels; identifiers are the join keys."
lineage_edges:
- from: bacen.instituicoes.ifdata_cadastro/cadastro
to: publish.gold_bacen/v_institution_search
kind: curated
evidence: "ciano_lake/gold/instituicoes/fcts.py (silver cadastro passthrough)"
- tabela_id: bacen.instituicoes.ifdata_valores/valores
summary: >
BACEN IF.DATA reported values, long format — one row per
(institution, quarter, report, column). Fully verified across the full
45-quarter series (28,013,428 rows). BCB sentinel values are preserved
as NULL (declared decision; never silently zeroed downstream).
grain: one row per (periodo, tipo_inst, cod_inst, report_type, col_name)
units: >
Per-column units vary (BRL amounts, counts, percentages) — units live in
the IF.DATA report metadata, not in the table; treat saldo values as
report-scoped, not universally comparable.
safe_joins:
- "Join cadastro on (cod_inst, periodo, tipo_inst) exactly — the gold fact
tables use these keys."
- "Never compare saldo across report_type/col_name without the report
metadata scope; a silently wrong comparison is the failure mode this
platform exists to prevent."
lineage_edges:
- from: bacen.instituicoes.ifdata_valores/valores
to: publish.gold_bacen/fct_balance_sheet
kind: curated
evidence: "ciano_lake/gold/instituicoes/fcts.py (JOIN + report_type filter Resumo/Ativo/Passivo)"
- from: bacen.instituicoes.ifdata_valores/valores
to: publish.gold_bacen/fct_income_statement
kind: curated
evidence: "ciano_lake/gold/instituicoes/fcts.py (JOIN + report_type filter Resultado/Demonstração de Resultado)"
- tabela_id: publish.gold_bacen/fct_balance_sheet
summary: >
Long-format balance-sheet facts (report_type Resumo/Ativo/Passivo):
silver valores joined to silver cadastro on (cod_inst, periodo,
tipo_inst). Published to Postgres schema gold_bacen by
publish-gold (TRUNCATE + COPY per table, per-table commit — a failed
multi-table refresh can leave mixed vintages; check the publication
record).
grain: one row per (institution, quarter, report, column) for balance-sheet reports
units: inherited from valores (per-column, report-scoped; see valores entry)
safe_joins:
- "cadastro_* columns come from the same-period cadastro row — already
denormalized; do not re-join cadastro unless you need a different
consolidation variant than the one carried in the fact row."
lineage_edges:
- from: bacen.instituicoes.ifdata_valores/valores
to: publish.gold_bacen/fct_balance_sheet
kind: curated
evidence: "ciano_lake/gold/instituicoes/fcts.py::_materialize_fct"
evidence:
- "ciano_lake/gold/instituicoes/fcts.py"
- "ciano_lake/publish/to_postgres.py (GOLD_TABLES, per-table commit)"
- tabela_id: publish.gold_bacen/fct_income_statement
summary: >
Long-format income-statement facts (report_type Resultado /
Demonstração de Resultado): same join and publication path as
fct_balance_sheet.
grain: one row per (institution, quarter, report, column) for income-statement reports
units: inherited from valores (per-column, report-scoped; see valores entry)
safe_joins:
- "Income-statement rows are period flows (cumulative within the year in
BACEN's presentation); comparing across quarters requires the report's
own period semantics — verified per report_type, not assumed."
lineage_edges:
- from: bacen.instituicoes.ifdata_valores/valores
to: publish.gold_bacen/fct_income_statement
kind: curated
evidence: "ciano_lake/gold/instituicoes/fcts.py::_materialize_fct"
evidence:
- "ciano_lake/gold/instituicoes/fcts.py"
- "ciano_lake/publish/to_postgres.py (GOLD_TABLES, per-table commit)"
# --------------------------------------------------------------------------- #
# ClickHouse gold surface (lake schema) — analytical IF.DATA + credit scorecard
# --------------------------------------------------------------------------- #
- tabela_id: clickhouse.lake/if_financeiros_periodo
summary: >
Analytical IF.DATA financials, one row per institution and quarter. Identity
+ 81 curated metrics x 2 blocks (own, unsuffixed; `_grupo` = prudential
conglomerate via cod_conglomerado_prudencial with per-metric own fallback) +
`_reportado` for de-cumulated flows. Each metric is resolved PER METRIC
across escopos (prudencial > financeiro > individual) so the AA..H history
(financeiro) sits beside C1..C5 (prudencial). Served as a ClickHouse view
over Parquet.
grain: one row per (instituicao_id, periodo)
units: BRL reais for monetary columns (NOT R$ mil); ratios are fractions (0.15 = 15%);
perda_esperada_operacoes_credito is a contra-asset (negative); NULL = absent, never zero
safe_joins:
- "Join if_indicadores_periodo / if_cobertura_periodo on (instituicao_id, periodo)."
- "A `_grupo` column falls back to the own value per metric — check fonte_grupo /
tem_grupo before reading it as a group fact; do not sum `_grupo` across siblings."
lineage_edges:
- from: bacen.instituicoes.ifdata_valores/valores
to: clickhouse.lake/if_financeiros_periodo
kind: curated
evidence: "ciano_lake/gold/instituicoes/analitico.py::_materialize_financeiros"
evidence:
- "ciano_lake/gold/instituicoes/analitico.py"
- "ciano_lake/gold/instituicoes/kpi_dictionary.yml (metric -> report_type/col_name)"
- tabela_id: clickhouse.lake/if_indicadores_periodo
summary: >
Derived, explainable ratios per institution and quarter (own + `_grupo`):
pl_sobre_ativo, alavancagem, roa, roe, credito_sobre_depositos,
liquidez_sobre_depositos, capital_principal_sobre_rwa,
perda_esperada_sobre_carteira, participacao_c1..c5, etc. No score/ranking.
grain: one row per (instituicao_id, periodo)
units: ratios are fractions; numerator and denominator always from the same block
safe_joins:
- "A NULL or zero denominator yields NULL (never zero) — do not COALESCE to 0."
lineage_edges:
- from: clickhouse.lake/if_financeiros_periodo
to: clickhouse.lake/if_indicadores_periodo
kind: derived
evidence: "ciano_lake/gold/instituicoes/analitico.py::_ratio_sql"
evidence:
- "ciano_lake/gold/instituicoes/analitico.py::_materialize_indicadores"
- tabela_id: clickhouse.lake/if_cobertura_periodo
summary: >
Data-availability / provenance flags per institution and quarter, 1:1 with
if_financeiros_periodo. tem_individual/financeiro/prudencial say which escopo
the code reported under; fonte_grupo/tem_grupo describe the `_grupo` block;
tem_*/fam_* explain why a metric is NULL. The gate for any scoring: never
infer quality from an absent metric.
grain: one row per (instituicao_id, periodo)
units: booleans / provenance labels
safe_joins:
- "Use before scoring: an institution with insufficient coverage must not be
graded (see if_confiabilidade_periodo.cobertura_score)."
lineage_edges:
- from: bacen.instituicoes.ifdata_valores/valores
to: clickhouse.lake/if_cobertura_periodo
kind: curated
evidence: "ciano_lake/gold/instituicoes/analitico.py::_build_escopo_presence"
evidence:
- "ciano_lake/gold/instituicoes/analitico.py::_materialize_cobertura"
- tabela_id: clickhouse.lake/if_confiabilidade_periodo
summary: >
Credit-reliability scorecard — the analyst "goto" table for how trustworthy an
institution is to honour its credit (CDB/LF/LCI/debentures; institutional
funds, so FGC is out of scope). Interpretable and decomposable: peer
percentiles (q_*, 100=best) within a familia x porte peer group + absolute
regulatory caps (Res. CMN 4.958/4.955/4.957/4.615, Res. BCB 352). Levels +
trends (d_*, the "video") + pillar subscores + score_confiabilidade (0-100) +
faixa_confiabilidade (A..E) + bank_zscore. NOT a black-box model. Insufficient
coverage publishes score=NULL + flag, never a false band. Capital/quality
default to the `_grupo` prudential block so issuers are gradable.
grain: one row per (instituicao_id, periodo), 1:1 with if_financeiros_periodo
units: ratios fractions; d_* in % (stocks) or pp (ratios); q_* and score 0-100;
faixa_confiabilidade A..E (NULL = insufficient coverage, not "worst")
safe_joins:
- "Read faixa_confiabilidade with cobertura_score; a NULL band means 'no data',
not 'E'. Cross-check if_observacoes_periodo for flags before acting on a score."
- "cet1_gap (basileia - CET1) signals reliance on non-core capital; a `_grupo`
value may back the issuer — see fonte_capital/fonte_qualidade."
lineage_edges:
- from: clickhouse.lake/if_financeiros_periodo
to: clickhouse.lake/if_confiabilidade_periodo
kind: derived
evidence: "ciano_lake/gold/instituicoes/confiabilidade.py::_build_base"
- from: clickhouse.lake/if_indicadores_periodo
to: clickhouse.lake/if_confiabilidade_periodo
kind: derived
evidence: "ciano_lake/gold/instituicoes/confiabilidade.py (roa/roe/pl_sobre_ativo)"
- from: clickhouse.lake/if_cobertura_periodo
to: clickhouse.lake/if_confiabilidade_periodo
kind: curated
evidence: "ciano_lake/gold/instituicoes/confiabilidade.py (tem_*/fam_*)"
evidence:
- "ciano_lake/gold/instituicoes/confiabilidade.py"
- "ciano_lake/gold/instituicoes/scoring.yml (weights, bands, caps, peer groups)"
- "docs/methodology/if-confiabilidade-scoring.md (methodology + references)"
- tabela_id: clickhouse.lake/if_observacoes_periodo
summary: >
Rule-based flags and manual-review observations for the credit scorecard, one
row per (instituicao_id, periodo, flag_code). Kept separate from the fact
tables on purpose: it carries "verify manually", missing-data and risk alerts
WITHOUT contaminating the score. status: aberto | revisado | ignorado, with
revisado_por / revisado_em / observacao_manual for human double-check (e.g. the
cooperative near-zero-NPL cases flagged QUALIDADE_SUSPEITA).
grain: one row per (instituicao_id, periodo, flag_code)
units: flag_code / severidade (alta|media|informativa) / status labels
safe_joins:
- "Join to if_confiabilidade_periodo on (instituicao_id, periodo); multiple
flags per row are expected. A flag is a prompt for review, not a verdict."
lineage_edges:
- from: clickhouse.lake/if_confiabilidade_periodo
to: clickhouse.lake/if_observacoes_periodo
kind: derived
evidence: "ciano_lake/gold/instituicoes/confiabilidade.py::_build_flags"
evidence:
- "ciano_lake/gold/instituicoes/confiabilidade.py"
- "ciano_lake/gold/instituicoes/scoring.yml (flags)"