Skip to content

Automatic translation — the Portuguese version is the reference. View in Portuguese

Updated on 2026-10-04

Data pipeline — Bronze, Silver, and Gold

The three layers of the datalake, what each one stores, and how transformations connect one to the next.

On this page (11)

Read first: README.md (vocabulary + template). Incremental/backfill/checkpoint are in ingestao-incremental-backfill.md; scheduling/retry/DLQ in agendamento-retry-dlq.md.


The two live tracks#

The product shows progress in real time with serializable steps (client + server).

Ingesting a source (pipeline detail /sources/[id], "Bronze tables" section → "Ingest live"):

code
validar → landing → bronze → registrar
  • validar — checks the connection and encrypted credentials
  • landing — sends the raw files to the landing zone
  • bronze — loads verbatim into the datalake's Bronze layer
  • registrar — updates the catalog and the last sync (writes lastSyncAt)

Engineering pipeline (page /pipeline):

code
ingest → bronze → silver → gold → publish

What each layer does (deterministic reference implementation)#

The pipeline has a reference implementation (in dev it runs on SQLite; the same contracts apply in the per-workspace datalake). Sample data: a synthetic table vendas with columns pedido_id, data_pedido, cliente_id, uf, categoria, produto, preco_unitario, quantidade, forma_pagamento, status.

Bronze — raw, verbatim, auditable

  • Ingestion in a transaction, with each column's type detected from its values (file) or coming from the source (database), plus technical stamps. A digits-only identifier (CPF, CEP) is read as a number and loses its leading zero — send it formatted. A database date column arrives as date and time (midnight).
  • Guarantees the number of fields (fills missing ones with "") — never drops a row in Bronze.
  • On the SaaS/landing path, raw files go to landing/<prefix>/_saas/<sourceId>/<stamp>/<tabela>/chunk-NNNN.json as NDJSON (one object per line), and then a single load reads all the chunks.

Silver — cleaning, typing, quality, and deduplication

Quality rules applied row by row (one failure = row discarded, counted in rejectedRows):

RuleBehavior
Status canceladodiscarded
Quantityinteger > 0, otherwise discarded
Priceparses Brazilian (1.299,90) and US (199.90) formats; > 0, otherwise discarded
Datevalid YYYY-MM-DD, otherwise discarded; derives mes = YYYY-MM
State (UF)normalized to 2 uppercase letters, otherwise discarded
pedido_idrequired
Empty categorybecomes "Sem categoria" (not discarded)
Deduplicationby pedido_id, keeps the 1st occurrence
receitaderived = preco_unitario × quantidade

rejectedRows = bronzeRows − silverRows — the difference is always visible (nothing disappears silently).

Gold — aggregated datamarts

Marts rebuilt on every run (idempotent): gold_receita_mensal, gold_receita_uf, gold_receita_categoria (with share), gold_kpis (single row: total revenue, orders, average ticket, number of states, period). Each run writes a pipeline_runs record (bronze/silver/rejected/marts/duration/KPIs).

Publish

The gold dashboard upsert republishes the public dashboard (published: true) — running the pipeline makes the gold data visible with no manual action.


Fact sheet (9-field template)#

  1. Status — available. Pipeline covered by automated tests and run in each workspace's datalake.
  2. What it does — takes data from the source to Gold marts ready for dashboards, passing through quality checks and deduplication in Silver.
  3. How it works — Bronze→Silver→Gold medallion; verbatim Bronze, Silver with quality rules + dedupe, aggregated Gold. Per-workspace datasets <prefix>_bronze/_silver/_gold.
  4. Plan · Permission — core · access check · kill switches for ingestion runs (load) and dashboard refresh (republish).
  5. Cost — loads and queries in the datalake are billable; the cost is estimated by the cost engine and capped by a billable-bytes ceiling (see custo-limites-seguranca.md).
  6. Limits — profiling: cap of 50 columns, 5 samples × 60 chars, payload 32 KiB; SaaS load defaults batchSize 5,000 / maxRows 200,000.
  7. Security — datasets/buckets scoped per workspace; column names interpolated only from an allowlist; PII masked in profiling.
  8. How to validate — in the console, run the pipeline of a sample workspace and check, on the run screen, the Bronze/Silver/rejected counters and the generated Gold marts; the gold dashboard is republished automatically at the end.
  9. In short — Bronze→Silver→Gold medallion pipeline with quality checks and deduplication.

Catalog profiling (safe sample)#

Profiling is pure. Given a sample + the schema, it computes per column: declared type × inferred type (a mismatch = a sign of dirty data), % of nulls, number of distinct values (always an estimate), min/max for numbers/dates, average text length, and up to 5 samples.

Built-in security (the reason the module exists separately):

  • PII column → samples become •••; min/max/distinct/length are suppressed (only the % of nulls survives).
  • Hidden column (governance) → does not even appear (the name never travels).
  • Caps: 50 columns, sample 60 chars, total payload ≤ 32 KiB (truncated with a warning).

Schema drift (the mart does not break silently)#

The discovered schema becomes a serializable baseline and is compared baseline × current:

ChangeSeverityEffect
new column/tableinfoadditive — the mart does not break
removed columnatencaoa mart that reads it breaks
changed typeatencaodownstream cast/aggregation breaks
removed tableatencao—

Severity is the worst one found; the mart is flagged as "breaks" when there is an atencao.

Automatic load sanitization (the pipeline does not break over a key name)#

Sanitization is the load's plan B in the datalake: scheduled ingestion pulls files from the customer's systems straight into the landing zone — keys with accents/spaces/empty keys ("preço", "nome do cliente") would bring the load down ("Invalid field name"). When the NDJSON load fails, the file is sanitized (lowercase, no accents, _; empty → coluna; starts with a number → col_) and reloaded. It also unwraps envelopes ({meta..., data:[...]}) and converts arrays/pretty-JSON to NDJSON. Conservative rule: an unparseable line is kept as is (it does not invent data).

Transformations (Silver→Gold as versioned code)#

Business transformations are a versioned IR compiled to deterministic SQL (steps in Portuguese: replace, text, dates, split, conditional; group-by/joins with grain-aware fan-out prevention). Versioning, parallel verification runs, and rollback are covered by automated tests. The outputs are frozen by the reference test suite, with identical results on every run.

Related links