Registrar 300 millones de transacciones al mes no es lo difícil; un Postgres bien afinado lo aguanta. Lo difícil llega el 31 de diciembre, cuando alguien pide el balance de comprobación de un año con 3600 millones de transacciones y más de 7200 millones de líneas de asiento, mientras siguen entrando pagos como cualquier otro día.
Aquí cuento cómo lo resolví con ClickHouse Cloud, con el SQL que probé.
Todo el SQL corre en ClickHouse 26.9, PostgreSQL 17 y la extensión pg_clickhouse sobre PostgreSQL 18, y las capturas son de ejecuciones reales. El procedimiento incluye cómo responder a los fallos del CDC (ClickPipes), que reproduje uno por uno.
1. El problema de escala
Trescientos millones de transacciones al mes son unas 115 por segundo. El problema es lo que se acumula durante el año y lo que hay que leer para cerrarlo.
El balance de comprobación agrega todas esas líneas por cuenta. La conciliación compara miles de millones de registros con los extractos de bancos y pasarelas. La diferencia en cambio revalúa cada saldo monetario en moneda extranjera. Y la auditoría pide rastrear cualquier saldo hasta su transacción de origen.
Una base de datos orientada a filas, como PostgreSQL, MySQL u Oracle, lee todas las columnas de cada fila aunque la consulta solo necesite tres. De ahí que los cierres tradicionales vivan de procesos nocturnos, tablas de resumen mantenidas a mano y equipos trabajando el fin de semana de fin de año. ClickHouse es columnar: lee solo las columnas que pide la consulta, las comprime muy bien (cuentas, monedas y centros de costo se repiten muchísimo) y agrega en paralelo.
2. La arquitectura
ostgres registra cada transacción con garantías ACID: valida el saldo disponible, bloquea la cuenta, aplica la clave de idempotencia y confirma. El CDC (captura de cambios) lleva a ClickHouse cada cambio que hace COMMIT, en torno a un minuto con la configuración por defecto. ClickHouse guarda el histórico completo, genera los asientos, mantiene los saldos y responde las consultas del cierre, la conciliación y los reportes.
ClickHouse no reemplaza al motor transaccional: no tiene transacciones de varias sentencias, ni claves foráneas, ni bloqueos de fila. Para leer un saldo, validarlo y descontarlo está Postgres. ClickHouse entra cuando hay que agregar miles de millones de filas en segundos.
3. El modelo de datos
Todo vive en una base de datos llamada finance. Los identificadores y comentarios del código están en inglés, que es lo habitual en equipos mixtos.
3.1 El dinero como enteros en unidades menores
Las APIs de pago modernas representan cada importe como un entero en la unidad más pequeña de la moneda, junto con su código ISO 4217. Diez dólares son 1000, porque el dólar tiene dos decimales. Uso la misma convención: los enteros no tienen errores de redondeo, se suman y comparan sin sorpresas, y quedan alineados con las pasarelas de pago.
Donde más veo errores es en los decimales de cada moneda. Según la lista oficial de ISO 4217, el peso colombiano tiene dos, igual que el dólar. O sea que 1 234 567 pesos se guardan como 123456700. Si guardas 1234567 creyendo que el peso no tiene decimales, cualquier integración con una pasarela cobra cien veces menos.
El yen y el peso chileno no tienen decimales, y el dinar kuwaití tiene tres. Por eso el número de decimales nunca va como un /100 fijo en el código: se lee de una tabla de referencia.
Esa tabla vive en Postgres con el resto de los datos de referencia:
-- PostgreSQL: ISO 4217 reference data
CREATE TABLE currencies (
code char(3) PRIMARY KEY, -- alphabetic code
numeric_code smallint NOT NULL, -- numeric code
minor_units smallint NOT NULL -- decimal places
);
INSERT INTO currencies VALUES
('COP', 170, 2), ('USD', 840, 2), ('EUR', 978, 2),
('JPY', 392, 0), ('CLP', 152, 0), ('KWD', 414, 3);ClickHouse la lee como un diccionario en memoria. Las credenciales de Postgres van en una named collection que crea un administrador una sola vez, y ninguna consulta posterior las repite:
-- ClickHouse: created once by an administrator
CREATE NAMED COLLECTION pg_finance AS
host = 'pg-finance.internal',
port = 5432,
user = 'clickhouse_reader',
password = '***',
database = 'accounting';
CREATE DATABASE IF NOT EXISTS finance;
CREATE DICTIONARY finance.currency_dict
(
code String,
minor_units UInt8
)
PRIMARY KEY code
SOURCE(POSTGRESQL(NAME pg_finance TABLE 'currencies'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(COMPLEX_KEY_HASHED());Las tasas de cambio sí necesitan decimales y las guardo como Decimal(18, 10). Para convertir entre monedas escribí una función que respeta los decimales de cada una, redondea una sola vez al final y falla si la moneda no existe en el diccionario. Esto último importa: un dictGet sobre una clave ausente devuelve 0, que aquí significaría cero decimales, justo el error de cien veces.
-- Converts an amount in minor units between currencies.
-- rate = units of to_ccy per one unit of from_ccy.
CREATE FUNCTION convert_minor AS
(amount_minor, from_ccy, to_ccy, rate) ->
toInt64(round(
toDecimal256(amount_minor, 0) * rate
* intExp10(dictGet('finance.currency_dict',
'minor_units', toString(to_ccy))
+ throwIf(NOT dictHas('finance.currency_dict',
toString(to_ccy)),
'Unknown currency'))
/ intExp10(dictGet('finance.currency_dict',
'minor_units', toString(from_ccy))
+ throwIf(NOT dictHas('finance.currency_dict',
toString(from_ccy)),
'Unknown currency')),
0));Al probarla me encontré con tres comportamientos de ClickHouse que no esperaba:
Multiplicar un
Int64por unDecimal64da unDecimal(18, 10), que solo tiene ocho dígitos enteros; con importes grandes la consulta falla con Decimal math overflow. EnDecimal128yDecimal256pasa lo contrario: ClickHouse no comprueba el desbordamiento, lo dice su documentación y lo vi en una multiplicación deDecimal128que devolvió un número negativo sin error. La función trabaja enDecimal256, cuyo rango cubre cualquierInt64por cualquier tasa, y deja que eltoInt64final falle si el resultado no cabe.roundsobre un decimal redondea alejándose de cero (2,5 pasa a 3 y −2,5 a −3), pero convertir a otra escala contoDecimal128(x, 4)trunca.sum()sobreInt64desborda sin error al pasar de 9,2 × 10¹⁸, unos 92 000 billones de pesos. Ninguna entidad llega ahí en un año, y los controles del cierre lo detectarían.
Imagen 5. Coma flotante frente a enteros en unidades menores. 3.2 La fuente: transacciones en Postgres
Postgres es la fuente de verdad. Cada transacción guarda su importe en moneda de la transacción y en moneda funcional (
amount_lcy_minor, COP en los ejemplos), calculado con la tasa vigente enevent_ts.-- PostgreSQL: monthly period status per entity CREATE TABLE accounting_periods ( entity_id text NOT NULL, period integer NOT NULL, -- e.g. 202612 status text NOT NULL CHECK (status IN ('OPEN', 'CLOSING', 'CLOSED')), closed_by text, closed_at timestamptz, PRIMARY KEY (entity_id, period) ); CREATE TABLE accounts ( id bigint PRIMARY KEY, available_balance_minor bigint NOT NULL ); CREATE TABLE transactions ( tx_id uuid PRIMARY KEY, idempotency_key uuid NOT NULL UNIQUE, entity_id text NOT NULL, channel text NOT NULL, tx_type text NOT NULL, event_ts timestamptz NOT NULL DEFAULT now(), accounting_date date NOT NULL, account_id bigint NOT NULL REFERENCES accounts (id), debit_account bigint NOT NULL, credit_account bigint NOT NULL, cost_center text NOT NULL, counterparty_id bigint NOT NULL, currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'), amount_minor bigint NOT NULL CHECK (amount_minor > 0), amount_lcy_minor bigint NOT NULL CHECK (amount_lcy_minor > 0), -- corrections are new rows with tx_type = 'REVERSAL' status text NOT NULL CHECK (status IN ('PENDING', 'CONFIRMED', 'REJECTED')) ); -- Confirmed or rejected rows are final, only the status of -- a pending row may change, and only OPEN periods accept -- new or newly confirmed operations. CREATE FUNCTION guard_transactions() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP IN ('UPDATE', 'DELETE') AND OLD.status IN ('CONFIRMED', 'REJECTED') THEN RAISE EXCEPTION 'transaction % is final', OLD.tx_id; END IF; IF TG_OP = 'DELETE' THEN RETURN OLD; END IF; IF TG_OP = 'UPDATE' AND (NEW.tx_id, NEW.entity_id, NEW.accounting_date, NEW.debit_account, NEW.credit_account, NEW.currency, NEW.amount_minor, NEW.amount_lcy_minor) IS DISTINCT FROM (OLD.tx_id, OLD.entity_id, OLD.accounting_date, OLD.debit_account, OLD.credit_account, OLD.currency, OLD.amount_minor, OLD.amount_lcy_minor) THEN RAISE EXCEPTION 'only the status can change'; END IF; IF NEW.status <> 'REJECTED' AND NOT EXISTS ( SELECT 1 FROM accounting_periods p WHERE p.entity_id = NEW.entity_id AND p.period = to_char(NEW.accounting_date, 'YYYYMM')::integer AND p.status = 'OPEN') THEN RAISE EXCEPTION 'period of % is not open', NEW.tx_id; END IF; RETURN NEW; END; $$; CREATE TRIGGER transactions_guard BEFORE INSERT OR UPDATE OR DELETE ON transactions FOR EACH ROW EXECUTE FUNCTION guard_transactions();Todo lo que sigue depende de este disparador. Una transacción confirmada o rechazada no se modifica ni se borra; de una pendiente solo cambia el estado; y nada se confirma en un periodo que no esté abierto. Las correcciones son transacciones de reversión nuevas. Para que nadie lo salte, el rol de la aplicación no es dueño de la tabla (no puede desactivar el disparador), no puede cambiar
session_replication_roley no tiene permiso deTRUNCATE.Este es el registro de un débito con control de saldo:
-- $1 account_id, $2 amount_minor (COP), $3 tx_id, -- $4 idempotency_key, $5 entity_id, $6 accounting_date, -- $7 debit_account, $8 credit_account, $9 counterparty_id BEGIN; SELECT available_balance_minor FROM accounts WHERE id = $1 FOR UPDATE; -- the application checks available_balance_minor >= $2 INSERT INTO transactions (tx_id, idempotency_key, entity_id, channel, tx_type, accounting_date, account_id, debit_account, credit_account, cost_center, counterparty_id, currency, amount_minor, amount_lcy_minor, status) VALUES ($3, $4, $5, 'API', 'PAYMENT', $6, $1, $7, $8, 'CC100', $9, 'COP', $2, $2, 'CONFIRMED'); UPDATE accounts SET available_balance_minor = available_balance_minor - $2 WHERE id = $1; COMMIT;Los comprobantes que genera el cierre (diferencia en cambio, cierre de resultados y apertura) se calculan en ClickHouse en una tabla de preparación, se aprueban en Postgres con sus totales a la vista y solo entonces se insertan en el libro. Así tienen consecutivo, quién los preparó, quién los aprobó y cuándo, y el cierre comprueba que ninguna línea manual entró sin aprobación:
-- PostgreSQL: header of every manual voucher CREATE TABLE journal_vouchers ( tx_id uuid PRIMARY KEY, voucher_no bigint GENERATED ALWAYS AS IDENTITY UNIQUE, period integer NOT NULL, kind text NOT NULL CHECK (kind IN ('ADJUSTMENT', 'CLOSING', 'OPENING')), description text NOT NULL, prepared_by text NOT NULL, approved_by text, approved_at timestamptz, CHECK (approved_by IS NULL OR approved_by <> prepared_by) );Si la pasarela de pagos ya envía los importes en unidades menores, se guardan tal cual. Solo hay que normalizar la moneda a mayúsculas, convertir las marcas de tiempo en segundos Unix a
timestamptzy guardar los identificadores de la pasarela como texto opaco.3.3 La tabla de transacciones en ClickHouse
Las transacciones llegan por el CDC de ClickPipes, que escribe en una tabla
ReplacingMergeTreecon tres columnas propias. Yo creo la tabla vacía antes de agregarla a la replicación, con los tipos que corresponden al mapeo documentado de ClickPipes (bigintpasa aInt64,uuidaUUID,textaString,dateaDateytimestamptzaDateTime64(6)), porque las vistas materializadas tienen que existir antes de la carga inicial; si no, el histórico no pasa por ellas. ClickPipes solo rechaza una tabla existente si tiene datos. Después de agregarla, compara el resultado deSHOW CREATE TABLE finance.transactionscon esta definición.-- Destination of the ClickPipes CDC for public.transactions -- (create it empty, then add the table to the pipe) CREATE TABLE finance.transactions ( tx_id UUID, idempotency_key UUID, entity_id String, channel String, tx_type String, event_ts DateTime64(6), accounting_date Date, account_id Int64, debit_account Int64, credit_account Int64, cost_center String, counterparty_id Int64, currency String, amount_minor Int64, amount_lcy_minor Int64, status String, _peerdb_synced_at DateTime64(9) DEFAULT now64(), _peerdb_is_deleted Int8, _peerdb_version Int64 ) ENGINE = ReplacingMergeTree(_peerdb_version) PARTITION BY toYYYYMM(accounting_date) ORDER BY tx_id;ReplacingMergeTreese queda con la versión más reciente de cada fila, pero lo hace durante las fusiones, cuando el motor decide. Toda consulta que necesite exactitud lee conFINAL. La clave de ordenación es la clave primaria de Postgres, la que ClickPipes usa por defecto, así que no hay que tocar laREPLICA IDENTITY. La partición es el mes contable, y como el disparador impide cambiar la fecha contable, todas las versiones de una transacción caen en la misma partición. Eso permite leer condo_not_merge_across_partitions_select_final = 1, que abarata mucho elFINAL. La única excepción son los borrados de filas pendientes, que llegan sin fecha y no afectan a nada porque los filtros piden transacciones confirmadas. Las columnas_peerdb_*son de ClickPipes. Su documentación las declaraInt8eInt64, y el código de PeerDB las crea comoUInt8yUInt64; ninguna consulta de este artículo depende de esa diferencia.Las fechas siguen ISO 8601: los instantes se guardan en UTC y la fecha contable se calcula en la zona horaria de la entidad. Un pago del
2026-10-01T02:00:00Zes del 30 de septiembre en Bogotá, y ese detalle mueve el cierre de mes:SELECT toDate( toDateTime64('2026-10-01 02:00:00', 3, 'UTC'), 'America/Bogota') AS accounting_date; -- 2026-09-303.4 La partida doble con vistas materializadas
Imagen 6. Líneas de asiento deduplicadas y saldos que se reconstruyen antes de cerrar.
Las vistas materializadas de ClickHouse son incrementales: se disparan con cada bloque insertado y procesan solo los datos nuevos. La documentación de ClickPipes las propone como disparadores sobre las tablas del CDC. Una vista se dispara con cada bloque que llega, aunque repita filas, y el CDC repite filas. Por eso guardo las líneas de asiento de forma que una línea repetida se colapse sola:
CREATE TABLE finance.journal_entries
(
tx_id UUID,
line_no UInt32,
entity_id LowCardinality(String),
accounting_date Date,
event_ts DateTime64(3, 'UTC'),
account Int64,
cost_center LowCardinality(String),
counterparty_id Int64,
currency LowCardinality(String),
debit_minor Int64,
credit_minor Int64,
debit_lcy_minor Int64,
credit_lcy_minor Int64,
source LowCardinality(String),
-- source: OPERATION, ADJUSTMENT, CLOSING, OPENING
src_version UInt64, -- CDC version, 0 for manual
posted_by LowCardinality(String)
DEFAULT currentUser(),
posted_at DateTime64(3, 'UTC') DEFAULT now64(3)
)
ENGINE = ReplacingMergeTree(src_version)
PARTITION BY toYYYYMM(accounting_date)
ORDER BY (entity_id, account, accounting_date, tx_id, line_no);
CREATE MATERIALIZED VIEW finance.mv_journal_entries
TO finance.journal_entries AS
SELECT
tx_id,
line.1 AS line_no,
entity_id,
accounting_date,
event_ts,
line.2 AS account,
cost_center,
counterparty_id,
currency,
line.3 AS debit_minor,
line.4 AS credit_minor,
line.5 AS debit_lcy_minor,
line.6 AS credit_lcy_minor,
'OPERATION' AS source,
toUInt64(_peerdb_version) AS src_version
FROM finance.transactions
ARRAY JOIN [
(toUInt32(1), debit_account, amount_minor, toInt64(0),
amount_lcy_minor, toInt64(0)),
(toUInt32(2), credit_account, toInt64(0), amount_minor,
toInt64(0), amount_lcy_minor)
] AS line
WHERE status = 'CONFIRMED'
AND _peerdb_is_deleted = 0;ARRAY JOIN convierte cada transacción en varias líneas dentro del mismo bloque. Si una transacción lleva IVA, retención y comisión, el arreglo tiene cuatro o cinco tuplas y el patrón no cambia. Como todas las columnas de la clave salen de una transacción que no cambia, una línea repetida por el CDC tiene la misma clave que la original y ReplacingMergeTree deja una sola. posted_by y posted_at registran quién insertó cada línea y cuándo; en las líneas del CDC es el usuario de ClickPipes.
Un límite del modelo: cada transacción tiene una sola moneda y un solo importe en moneda funcional. Una compra de dólares con pesos, o el pago de una factura en dólares a una tasa distinta de la de causación, necesita un comprobante de varias líneas con una cuenta puente de posición por moneda y una línea en moneda funcional para la diferencia en cambio realizada.
Los saldos diarios se consolidan con SummingMergeTree:
CREATE TABLE finance.daily_balances
(
entity_id LowCardinality(String),
account Int64,
cost_center LowCardinality(String),
currency LowCardinality(String),
accounting_date Date,
debit_minor Int64,
credit_minor Int64,
debit_lcy_minor Int64,
credit_lcy_minor Int64,
entry_count UInt64
)
ENGINE = SummingMergeTree((debit_minor, credit_minor,
debit_lcy_minor, credit_lcy_minor, entry_count))
PARTITION BY toYYYYMM(accounting_date)
ORDER BY (entity_id, account, cost_center, currency,
accounting_date);
CREATE MATERIALIZED VIEW finance.mv_daily_balances
TO finance.daily_balances AS
SELECT
entity_id, account, cost_center, currency,
accounting_date,
sum(debit_minor) AS debit_minor,
sum(credit_minor) AS credit_minor,
sum(debit_lcy_minor) AS debit_lcy_minor,
sum(credit_lcy_minor) AS credit_lcy_minor,
count() AS entry_count
FROM finance.journal_entries
GROUP BY entity_id, account, cost_center, currency,
accounting_date;Los 7200 millones de líneas del año quedan en una fila por cuenta, centro de costo, moneda y día: decenas de millones de filas al año en lugar de miles de millones.
Ojo: esta tabla es un saldo provisional. Una suma no se puede deduplicar, y si el CDC repite un bloque, la línea repetida se colapsa en
journal_entriespero su importe ya quedó sumado aquí. Antes de usarla para cerrar la reconstruyo desde las líneas deduplicadas. Está particionada por mes para poder reconstruir solo los meses afectados.
Para indicadores que no se pueden sumar, como clientes únicos o percentiles, uso AggregatingMergeTree, que guarda estados parciales combinables entre meses. Por la misma razón que los saldos, es un indicador de monitoreo: un bloque repetido cuenta dos veces su volumen.
CREATE TABLE finance.monthly_kpis
(
entity_id LowCardinality(String),
month Date,
tx_type LowCardinality(String),
volume_lcy AggregateFunction(sum, Int64),
unique_customers AggregateFunction(uniq, Int64),
ticket_quantiles AggregateFunction(
quantiles(0.5, 0.95, 0.99), Int64)
)
ENGINE = AggregatingMergeTree
ORDER BY (entity_id, month, tx_type);
CREATE MATERIALIZED VIEW finance.mv_monthly_kpis
TO finance.monthly_kpis AS
SELECT
entity_id,
toStartOfMonth(accounting_date) AS month,
tx_type,
sumState(amount_lcy_minor) AS volume_lcy,
uniqState(counterparty_id) AS unique_customers,
-- approximate percentiles, enough for monitoring
quantilesState(0.5, 0.95, 0.99)(amount_lcy_minor)
AS ticket_quantiles
FROM finance.transactions
WHERE status = 'CONFIRMED'
AND _peerdb_is_deleted = 0
GROUP BY entity_id, month, tx_type;
-- Read path: merge the partial states
SELECT
month,
tx_type,
sumMerge(volume_lcy) AS volume_lcy_minor,
uniqMerge(unique_customers) AS customers,
quantilesMerge(0.5, 0.95, 0.99)(ticket_quantiles)
AS ticket_p50_p95_p99
FROM finance.monthly_kpis
WHERE entity_id = 'CO01' AND month >= '2026-01-01'
GROUP BY month, tx_type
ORDER BY month, tx_type;3.5 El plan de cuentas como diccionario jerárquico
El plan de cuentas es un árbol: clase, grupo, cuenta, subcuenta y auxiliar. ClickHouse lo carga como diccionario jerárquico desde Postgres, con la misma named collection, y lo refresca solo. La clave de un diccionario jerárquico debe ser UInt64 y las cuentas raíz llevan parent_account = 0. Las cuentas del libro son Int64 (así las crea el CDC desde un bigint) y dictGet las acepta sin conversión; lo comprobé.
Uso códigos del PUC (Decreto 2650 de 1993) como ejemplo. Una entidad que reporta bajo NIIF puede tener su propio catálogo, y las vigiladas por la Superintendencia Financiera reportan con el CUIF, que tiene otros códigos. En producción, las cuentas especiales del cierre (diferencia en cambio, utilidad, pérdida) deberían salir de un atributo del plan de cuentas y no del SQL.
CREATE DICTIONARY finance.chart_of_accounts
(
account UInt64,
parent_account UInt64 HIERARCHICAL,
account_name String,
normal_side String, -- 'D' debit, 'C' credit
account_class UInt8, -- 1 assets ... 6 cost of sales
account_level UInt8
)
PRIMARY KEY account
SOURCE(POSTGRESQL(NAME pg_finance TABLE 'chart_of_accounts'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());Subir por la jerarquía es una búsqueda en memoria, sin consultas recursivas:
SELECT
dictGetHierarchy('finance.chart_of_accounts', 11050501)
AS ancestry,
dictIsIn('finance.chart_of_accounts', 11050501, 1)
AS is_asset;
3.6 Tasas de cambio con ASOF JOIN
Convertir a moneda funcional exige la tasa vigente en el instante de la transacción. ASOF JOIN busca, para cada fila, la tasa más reciente anterior o igual a ese instante. Lo uso como control de auditoría: recalculo el importe en pesos con la tasa de la mesa de cambios, que cambia durante el día, y lo comparo con el que registró el origen. La TRM, que rige un día completo, queda para la revaluación del cierre.
CREATE TABLE finance.fx_rates
(
currency LowCardinality(String),
valid_from DateTime64(3, 'UTC'),
rate Decimal(18, 10), -- COP per unit
rate_source LowCardinality(String) -- 'DESK' or 'TRM'
)
ENGINE = ReplacingMergeTree
ORDER BY (currency, rate_source, valid_from);
SELECT
t.tx_id,
t.event_ts,
t.currency,
t.amount_minor,
r.rate,
convert_minor(t.amount_minor, t.currency, 'COP', r.rate)
AS recalculated_lcy_minor,
t.amount_lcy_minor,
recalculated_lcy_minor - t.amount_lcy_minor
AS difference_minor
FROM finance.transactions AS t FINAL
ASOF LEFT JOIN
(
SELECT currency, valid_from, rate
FROM finance.fx_rates
WHERE rate_source = 'DESK'
) AS r
ON t.currency = r.currency
AND t.event_ts >= r.valid_from
WHERE t.accounting_date = '2026-12-31'
AND t.currency != 'COP'
AND t._peerdb_is_deleted = 0
SETTINGS do_not_merge_across_partitions_select_final = 1;
4. El CDC desde Postgres: ¿qué garantiza y qué no?
El CDC funciona casi siempre, y por eso es fácil suponer que cada fila llega exactamente una vez. ¿No es así?
4.1 ¿Cómo funciona?
ClickPipes lee el WAL de Postgres mediante un slot de replicación lógica y una publicación. Primero hace una carga inicial de cada tabla y luego aplica los cambios en lotes. Cada INSERT o UPDATE llega como una fila nueva con una versión mayor, y cada DELETE como una fila con _peerdb_is_deleted = 1. Cada replicación configurada se llama un pipe.
4.2 Requisitos en Postgres
ClickPipes admite Postgres 12 o superior, autoadministrado o en los servicios administrados más comunes, y se conecta directo al servidor: los poolers de conexiones como PgBouncer no sirven para CDC. En un Postgres propio, la decodificación lógica se activa así:
-- PostgreSQL (self-managed): enable logical decoding
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';
-- restart PostgreSQL for wal_level to take effectEn un Postgres administrado, la replicación lógica se activa con el parámetro que define el proveedor, también con reinicio. Para el WAL retenido, la guía de instalación propone 100 GB y la FAQ pide al menos dos días de WAL o 200 GB; yo dimensiono con dos o tres veces el pico diario.
El usuario de ClickPipes necesita lectura sobre las tablas y permiso de replicación:
-- PostgreSQL: dedicated CDC user (password from a vault)
CREATE USER clickpipes_user PASSWORD '***';
GRANT USAGE ON SCHEMA public TO clickpipes_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public
TO clickpipes_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO clickpipes_user;
ALTER USER clickpipes_user WITH REPLICATION;Cada tabla replicada necesita clave primaria o REPLICA IDENTITY. ClickPipes crea por su cuenta la publicación y el slot para las tablas que eliges. Limita la duración de las transacciones con statement_timeout e idle_in_transaction_session_timeout: una transacción abierta durante horas retiene WAL en el slot.
4.3 Configuración del pipe
En ClickPipes elijo transactions, accounting_periods, journal_vouchers y parties, con la partición toYYYYMM(accounting_date) para transactions y sin las columnas cifradas de parties. Antes de crear el pipe, ten en cuenta:
Después de crearlo puedes agregar o quitar tablas y ajustar el intervalo de sincronización y el tamaño de lote. La clave de ordenación, la partición, el motor y las columnas excluidas de cada tabla se fijan al agregarla; cambiarlos exige resincronizarla.
El intervalo de sincronización es de 60 segundos por defecto y la documentación recomienda no bajarlo de 10. Un lote además espera el
COMMITde cada transacción de Postgres.Si una vista se crea después de la carga inicial, hay que rellenarla con el procedimiento de la sección 4.6.
4.4 Lo que el CDC no garantiza
Esto fue lo que más me costó entender, y de aquí sale el diseño de los asientos.
La entrega se comporta como “al menos una vez”. La FAQ de ClickPipes explica que, si se interrumpe la escritura en la tabla final, la deduplicación queda a cargo de ReplacingMergeTree. En el código de PeerDB, esa escritura es un INSERT ... SELECT por lote que no usa insert_deduplication_token y que marca el lote como terminado en una operación aparte. Si el proceso cae entre las dos, el lote se inserta otra vez con la misma versión. La tabla de transacciones lo resuelve en la siguiente fusión, pero las vistas materializadas se disparan de nuevo.
Una resincronización no pasa por las vistas. Resincronizar carga todo en tablas con sufijo _resync y luego las intercambia con las originales. Las vistas definidas sobre la tabla original no ven esa carga y sus tablas de destino pueden quedar con huecos.
Hay operaciones que no viajan. TRUNCATE se ignora; solo ADD COLUMN se propaga como cambio de esquema, y solo cuando la tabla recibe su siguiente cambio; las columnas generadas no se replican; y por defecto ninguna columna es Nullable, así que un NULL llega como el valor por defecto del tipo (una cadena vacía, por ejemplo).
Pausar no detiene el WAL. Un pipe pausado deja que el slot siga acumulando WAL. Si supera max_slot_wal_keep_size, Postgres invalida el slot, y según la FAQ la única salida es una resincronización completa.
4.5 El diseño que lo absorbe
Transacciones definitivas en Postgres, de modo que cada línea de asiento tiene una clave estable.
Líneas de asiento idempotentes:
journal_entriesusaReplacingMergeTreecon(tx_id, line_no)en la clave. Repetir un lote o volver a derivar un mes nunca deja una línea duplicada después de deduplicar.Saldos reconstruibles:
daily_balancesse reconstruye antes de cerrar.Controles hasta el origen: el cierre compara Postgres con
transactions,transactionsconjournal_entriesyjournal_entriescondaily_balances. Cualquier diferencia detiene el proceso.
4.6 Después de una resincronización
Primero compruebo que las vistas siguen enganchadas a la tabla, porque una resincronización de tabla la reemplaza:
-- Both materialized views must still depend on the table
SELECT dependencies_table
FROM system.tables
WHERE database = 'finance' AND name = 'transactions';
-- expected: ['mv_journal_entries', 'mv_monthly_kpis']Luego vuelvo a derivar las líneas de asiento de los meses afectados desde las transacciones deduplicadas. Como la tabla de asientos es idempotente, las líneas que ya estaban no se duplican:
-- After a ClickPipes resync: re-derive the journal lines
-- of the affected months (idempotent thanks to the RMT)
INSERT INTO finance.journal_entries
(tx_id, line_no, entity_id, accounting_date, event_ts,
account, cost_center, counterparty_id, currency,
debit_minor, credit_minor, debit_lcy_minor,
credit_lcy_minor, source, src_version)
SELECT
tx_id,
line.1 AS line_no,
entity_id,
accounting_date,
event_ts,
line.2 AS account,
cost_center,
counterparty_id,
currency,
line.3 AS debit_minor,
line.4 AS credit_minor,
line.5 AS debit_lcy_minor,
line.6 AS credit_lcy_minor,
'OPERATION' AS source,
toUInt64(_peerdb_version) AS src_version
FROM finance.transactions FINAL
ARRAY JOIN [
(toUInt32(1), debit_account, amount_minor, toInt64(0),
amount_lcy_minor, toInt64(0)),
(toUInt32(2), credit_account, toInt64(0), amount_minor,
toInt64(0), amount_lcy_minor)
] AS line
WHERE status = 'CONFIRMED'
AND _peerdb_is_deleted = 0
AND toYYYYMM(accounting_date) BETWEEN 202611 AND 202612
SETTINGS do_not_merge_across_partitions_select_final = 1;Después reconstruyo los saldos de esos meses y repito los controles del paso 1. Los indicadores de monthly_kpis se reconstruyen igual si se usan para algo más que monitoreo.
4.7 Monitoreo
ClickHouse Cloud expone métricas de ClickPipes en formato Prometheus. Vigilo ClickPipes_SourceReplicationLatency_MiB (retraso del slot en Postgres), la distancia entre ClickPipes_LastFetchedBatchId y ClickPipes_LastSentBatchId, ClickPipes_Errors_Total y el estado del pipe en ClickPipes_Info. En Postgres, el retraso del slot también se ve en pg_replication_slots.
5. Consultas financieras
5.1 Saldo acumulado
Una consulta de operación diaria sobre los saldos provisionales; para cifras de cierre, ejecútala después del paso 0.
SELECT
accounting_date,
account,
sum(debit_lcy_minor - credit_lcy_minor) AS movement,
sum(sum(debit_lcy_minor - credit_lcy_minor)) OVER (
PARTITION BY account
ORDER BY accounting_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance
FROM finance.daily_balances
WHERE entity_id = 'CO01'
AND account = 111005
AND accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY accounting_date, account
ORDER BY accounting_date;Como el rango empieza el 1 de enero, el saldo incluye la apertura del año.
5.2 Antigüedad de cartera en una sola pasada
SELECT
counterparty_id,
sumIf(balance, days <= 30) AS d0_30,
sumIf(balance, days BETWEEN 31 AND 60) AS d31_60,
sumIf(balance, days BETWEEN 61 AND 90) AS d61_90,
sumIf(balance, days BETWEEN 91 AND 180) AS d91_180,
sumIf(balance, days > 180) AS d180_plus,
sum(balance) AS total
FROM
(
SELECT
counterparty_id,
dateDiff('day', accounting_date,
toDate('2026-12-31')) AS days,
debit_lcy_minor - credit_lcy_minor AS balance
FROM finance.journal_entries FINAL
WHERE entity_id = 'CO01'
-- receivables group (13) and every sub-account
AND dictIsIn('finance.chart_of_accounts', account, 13)
AND accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
)
GROUP BY counterparty_id
HAVING total != 0
ORDER BY total DESC
SETTINGS do_not_merge_across_partitions_select_final = 1;El rango es el año, por la razón que explico en la sección 6.4. Es un ejemplo simplificado. La apertura conserva el saldo de cada tercero pero no la fecha de sus documentos, así que un informe de antigüedad real cruza cada pago con su factura y usa la fecha de vencimiento. Lo que me interesa mostrar es el combinador -If, que evita un CASE por columna o varias pasadas sobre los datos.
5.3 VPN y TIR dentro del motor
Desde la versión 25.7, ClickHouse trae funciones financieras nativas, incluidas las versiones con fechas irregulares equivalentes a XNPV y XIRR de las hojas de cálculo. Solo aceptan enteros o Float, no Decimal; convierto los flujos a unidades mayores en Float64, que para valoración alcanza. El libro sigue en enteros.
-- finance.loan_cash_flows(loan_id, payment_date,
-- currency, cash_flow_minor)
SELECT
loan_id,
arraySort(groupArray((
payment_date,
cash_flow_minor / intExp10(dictGet(
'finance.currency_dict', 'minor_units',
toString(currency)))
))) AS flows,
financialInternalRateOfReturnExtended(
arrayMap(f -> f.2, flows),
arrayMap(f -> f.1, flows)) AS irr,
financialNetPresentValueExtended(
0.12,
arrayMap(f -> f.2, flows),
arrayMap(f -> f.1, flows)) AS npv_12
FROM finance.loan_cash_flows
GROUP BY loan_id;5.4 Riesgo de mercado
VaR histórico al 99 %, volatilidad diaria y correlación con el índice de referencia, por portafolio:
-- Historical 99% VaR, volatility and benchmark correlation
SELECT
portfolio_id,
quantileExact(0.01)(daily_return) AS var_99,
stddevSamp(daily_return) AS volatility,
corr(daily_return, benchmark_return) AS correlation
FROM finance.portfolio_returns
WHERE return_date >= today() - 500
GROUP BY portfolio_id;ClickHouse también tiene exponentialMovingAverage, simpleLinearRegression, quantileTDigest, covarSamp, studentTTest y mannWhitneyUTest, entre otras.
5.5 Anomalías en tiempo real
Transacciones del último cuarto de hora que se salen más de seis desviaciones estándar del comportamiento del tercero en los últimos 90 días:
WITH stats AS
(
SELECT
counterparty_id,
avg(amount_lcy_minor) AS mean_minor,
stddevPop(amount_lcy_minor) AS sd_minor
FROM finance.transactions FINAL
WHERE accounting_date >= today() - 90
AND status = 'CONFIRMED'
AND _peerdb_is_deleted = 0
GROUP BY counterparty_id
)
SELECT
t.tx_id,
t.counterparty_id,
t.amount_lcy_minor,
(t.amount_lcy_minor - s.mean_minor)
/ nullIf(s.sd_minor, 0) AS z_score
FROM finance.transactions AS t FINAL
INNER JOIN stats AS s USING (counterparty_id)
WHERE t.accounting_date >= today() - 1
AND t.event_ts >= now() - INTERVAL 15 MINUTE
AND t.status = 'CONFIRMED'
AND t._peerdb_is_deleted = 0
AND z_score > 6
ORDER BY z_score DESC
SETTINGS do_not_merge_across_partitions_select_final = 1;Para secuencias sospechosas, como un cambio de contraseña seguido de un beneficiario nuevo y una transferencia grande en pocos minutos, están sequenceMatch y windowFunnel.
6. El cierre fiscal
6.1 Calendario y periodos
Los periodos son mensuales y los controla Postgres. Diciembre sigue OPEN después del 31, porque las liquidaciones de tarjetas, los archivos bancarios y las compensaciones del 31 llegan el 1 y el 2 de enero y deben quedar en 2026. El corte es una fecha que fija el equipo contable, por ejemplo el 3 de enero a las 23:59. En ese momento diciembre pasa a CLOSING (el disparador ya no deja confirmar operaciones con fecha de diciembre) y espero a que el CDC haya llevado a ClickHouse todo lo anterior:
-- PostgreSQL: freeze December for operations
UPDATE accounting_periods
SET status = 'CLOSING'
WHERE period = 202612;
-- note the current WAL position...
SELECT pg_current_wal_lsn() AS cutoff_lsn;
-- ...and wait until the ClickPipes slot has confirmed it
SELECT slot_name,
confirmed_flush_lsn >= '0/0'::pg_lsn AS caught_up
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- replace '0/0' with the cutoff_lsn returned aboveAntes de anotar la posición espero a que termine cualquier transacción que empezó antes del cambio a CLOSING (xact_start en pg_stat_activity), porque el disparador ya la dejó pasar. Cuando caught_up es verdadero, los cambios anteriores al corte ya salieron de Postgres; ClickPipes los aplica a la tabla final en el siguiente lote. Que diciembre esté completo en ClickHouse no lo prueba el slot sino el control contra Postgres de la sección 6.4. Los meses anteriores pasaron por lo mismo al cerrarse.
6.2 Pre-cierre: el balance preliminar
Durante diciembre uso una vista materializada refrescable. Ejecuta la consulta completa cada hora y reemplaza el resultado de forma atómica:
CREATE MATERIALIZED VIEW finance.preliminary_trial_balance
REFRESH EVERY 1 HOUR
ENGINE = MergeTree
ORDER BY (entity_id, account)
AS
SELECT
entity_id,
account,
dictGet('finance.chart_of_accounts', 'account_name',
account) AS account_name,
sum(debit_lcy_minor) AS total_debit_lcy,
sum(credit_lcy_minor) AS total_credit_lcy,
total_debit_lcy - total_credit_lcy AS balance_lcy,
now() AS computed_at
FROM finance.daily_balances
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY entity_id, account;Sale de los saldos provisionales, así que sirve para ver venir el cierre, no para cerrarlo. Estas vistas admiten dependencias (DEPENDS ON) y se pueden encadenar sin un orquestador externo.
6.3 Conciliación
Comparar fila por fila 300 millones de registros entre dos sistemas es caro. Primero comparo huellas por día, cuenta y moneda, y solo bajo al detalle donde no coinciden. La huella es un XOR de un MD5 por transacción; la misma técnica, calculada en Postgres con bit_xor, es la que uso en la sección 6.4 para comparar contra el origen:
SELECT
accounting_date,
debit_account AS account,
currency,
count() AS tx_count,
sum(amount_minor) AS total_minor,
groupBitXor(reinterpretAsInt64(reverse(unhex(left(hex(
MD5(concat(toString(tx_id), '|',
toString(amount_minor)))), 16)))))
AS fingerprint
FROM finance.transactions FINAL
WHERE entity_id = 'CO01'
AND toYYYYMM(accounting_date) = 202612
AND status = 'CONFIRMED'
AND _peerdb_is_deleted = 0
GROUP BY accounting_date, account, currency
SETTINGS do_not_merge_across_partitions_select_final = 1;El XOR no depende del orden de las filas, pero dos filas idénticas se anulan entre sí; por eso la huella siempre se compara junto con el conteo y el total.
Cuando una combinación no cuadra, cruzo el libro auxiliar de bancos con el extracto. Los bancos y el sistema de pagos inmediatos Bre-B ya usan mensajes ISO 20022, y lo práctico es normalizar los extractos a una estructura tipo camt.053 en archivos Parquet que ClickHouse lee desde el almacenamiento de objetos. Las credenciales del almacenamiento también van en named collections:
-- Created once by an administrator; queries use the names
CREATE NAMED COLLECTION s3_finance AS
url = 'https://storage.example.com/finance-data/',
access_key_id = '***',
secret_access_key = '***';
CREATE NAMED COLLECTION s3_worm AS
url = 'https://storage.example.com/finance-worm/',
access_key_id = '***',
secret_access_key = '***';SELECT
coalesce(l.reference, b.reference) AS reference,
l.amount_minor AS ledger_minor,
b.amount_minor AS bank_minor,
multiIf(
l.reference IS NULL, 'BANK_ONLY',
b.reference IS NULL, 'LEDGER_ONLY',
l.amount_minor != b.amount_minor, 'AMOUNT_MISMATCH',
'MATCHED') AS match_status
FROM
(
SELECT reference, amount_minor
FROM finance.cash_book_entries
WHERE entry_date = '2026-12-31' AND account = 111005
) AS l
FULL OUTER JOIN
(
-- camt.053-style statement, amounts in COP
SELECT
reference,
toInt64(round(toDecimal128(amount, 4)
* intExp10(dictGet('finance.currency_dict',
'minor_units', 'COP')), 0))
AS amount_minor
FROM s3(s3_finance,
filename = 'bank-statements/2026-12-31/*.parquet',
format = 'Parquet')
) AS b
ON l.reference = b.reference
WHERE match_status != 'MATCHED'
SETTINGS join_use_nulls = 1;En ClickHouse Cloud también se puede dar acceso al bucket con un rol del proveedor de nube en lugar de claves.
6.4 Reconstruir y verificar
Todas las consultas del cierre filtran el año, del 1 de enero al 31 de diciembre, y nunca el histórico: cada año parte de su propia apertura, y sumar el histórico contaría dos veces lo ya trasladado. Lo que se suma entre cuentas y monedas es siempre el importe en moneda funcional.
Paso 0. Reconstruir los saldos desde las líneas deduplicadas. Lo hago mes a mes (un script recorre de 202601 a 202612) y lo repito antes del paso 4, que escribe un comprobante a partir de los saldos:
-- 0a. Rebuild one month of balances
CREATE TABLE IF NOT EXISTS finance.daily_balances_rebuild
AS finance.daily_balances;
TRUNCATE TABLE finance.daily_balances_rebuild;
INSERT INTO finance.daily_balances_rebuild
SELECT
entity_id, account, cost_center, currency,
accounting_date,
sum(debit_minor), sum(credit_minor),
sum(debit_lcy_minor), sum(credit_lcy_minor),
count()
FROM finance.journal_entries FINAL
WHERE toYYYYMM(accounting_date) = {period:UInt32}
GROUP BY entity_id, account, cost_center, currency,
accounting_date
SETTINGS do_not_merge_across_partitions_select_final = 1;
ALTER TABLE finance.daily_balances
REPLACE PARTITION {period:UInt32}
FROM finance.daily_balances_rebuild;
-- 0b. Verify the month; if it fails, run 0a again
SELECT throwIf(count() > 0, 'Balances differ from journal')
FROM
(
SELECT entity_id, account, currency,
sum(debit_lcy_minor) AS d,
sum(credit_lcy_minor) AS c
FROM finance.daily_balances
WHERE toYYYYMM(accounting_date) = {period:UInt32}
GROUP BY entity_id, account, currency
) AS b
FULL OUTER JOIN
(
SELECT entity_id, account, currency,
sum(debit_lcy_minor) AS d,
sum(credit_lcy_minor) AS c
FROM finance.journal_entries FINAL
WHERE toYYYYMM(accounting_date) = {period:UInt32}
GROUP BY entity_id, account, currency
) AS j USING (entity_id, account, currency)
WHERE b.d != j.d OR b.c != j.c
SETTINGS do_not_merge_across_partitions_select_final = 1;REPLACE PARTITION cambia la partición de forma atómica y no dispara las vistas materializadas. Si una línea llega a los asientos después de leer y antes del reemplazo, se queda fuera de los saldos; para eso está la verificación 0b.
Paso 1. Comprobar la partida doble, las líneas, el origen y los comprobantes. Cada consulta falla si algo no cuadra:
-- 1a. Debits equal credits per entity and currency
SELECT
entity_id,
currency,
sum(debit_minor) AS total_debit,
sum(credit_minor) AS total_credit,
sum(debit_lcy_minor) AS total_debit_lcy,
sum(credit_lcy_minor) AS total_credit_lcy,
throwIf(
total_debit != total_credit
OR total_debit_lcy != total_credit_lcy,
'Double-entry check failed') AS check_result
FROM finance.daily_balances
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY entity_id, currency;
-- 1b. A line may repeat (CDC retries), but two copies of
-- the same (tx_id, line_no) must never disagree
SELECT throwIf(count() > 0, 'Conflicting journal lines')
FROM
(
SELECT tx_id, line_no
FROM finance.journal_entries
WHERE toYear(accounting_date) = 2026
GROUP BY tx_id, line_no
HAVING uniqExact(account, debit_minor, credit_minor,
debit_lcy_minor, credit_lcy_minor) > 1
);
-- 1c. Journal lines match the confirmed transactions
SELECT throwIf(count() > 0, 'Journal differs from CDC data')
FROM
(
SELECT entity_id, toYYYYMM(accounting_date) AS period,
2 * count() AS lines, sum(amount_lcy_minor) AS lcy
FROM finance.transactions FINAL
WHERE status = 'CONFIRMED' AND _peerdb_is_deleted = 0
AND toYear(accounting_date) = 2026
GROUP BY entity_id, period
) AS t
FULL OUTER JOIN
(
SELECT entity_id, toYYYYMM(accounting_date) AS period,
count() AS lines, sum(debit_lcy_minor) AS lcy
FROM finance.journal_entries FINAL
WHERE source = 'OPERATION'
AND toYear(accounting_date) = 2026
GROUP BY entity_id, period
) AS j USING (entity_id, period)
WHERE t.lines != j.lines OR t.lcy != j.lcy
SETTINGS do_not_merge_across_partitions_select_final = 1;
-- 1d. Every manual voucher was approved in Postgres
SELECT throwIf(count() > 0, 'Voucher without approval')
FROM
(
SELECT DISTINCT tx_id
FROM finance.journal_entries
WHERE source != 'OPERATION'
AND toYear(accounting_date) = 2026
) AS j
LEFT ANTI JOIN
(
SELECT tx_id
FROM finance.journal_vouchers FINAL
WHERE approved_by != '' AND _peerdb_is_deleted = 0
) AS v USING (tx_id);
-- 1e. Every account exists in the chart of accounts
SELECT throwIf(count() > 0, 'Unmapped accounts')
FROM finance.daily_balances
WHERE toYear(accounting_date) = 2026
AND NOT dictHas('finance.chart_of_accounts',
toUInt64(account));El control 1a no basta por sí solo: una línea duplicada también cuadra. Por eso existen 1b y 1c. El control 1c supone dos líneas por transacción, como en este modelo; si las tuyas generan más, compara con el número esperado. El 1e importa porque una cuenta que no está en el diccionario tendría clase 0 y desaparecería en silencio de los pasos 3, 4 y 6.
El último eslabón es Postgres. Al cerrar cada mes comparo conteo, total y huella con una vista de ClickHouse que Postgres consulta a través de pg_clickhouse:
-- ClickHouse: monthly control figures from the CDC table
CREATE VIEW finance.monthly_tx_control AS
SELECT
entity_id,
toYYYYMM(accounting_date) AS period,
count() AS tx_count,
sum(amount_lcy_minor) AS total_lcy,
groupBitXor(reinterpretAsInt64(reverse(unhex(left(hex(
MD5(concat(toString(tx_id), '|',
toString(amount_lcy_minor)))), 16)))))
AS fingerprint
FROM finance.transactions FINAL
WHERE status = 'CONFIRMED' AND _peerdb_is_deleted = 0
GROUP BY entity_id, period
SETTINGS do_not_merge_across_partitions_select_final = 1;-- PostgreSQL: the month in Postgres against ClickHouse
SELECT
coalesce(p.entity_id, c.entity_id) AS entity_id,
p.tx_count, c.tx_count AS ch_tx_count,
p.total_lcy, c.total_lcy AS ch_total_lcy,
p.tx_count = c.tx_count
AND p.total_lcy = c.total_lcy
AND p.fingerprint = c.fingerprint AS matched
FROM
(
SELECT
entity_id,
count(*) AS tx_count,
sum(amount_lcy_minor) AS total_lcy,
bit_xor(('x' || left(md5(tx_id::text || '|'
|| amount_lcy_minor), 16))::bit(64)::bigint)
AS fingerprint
FROM transactions
WHERE status = 'CONFIRMED'
AND accounting_date >= DATE '2026-12-01'
AND accounting_date < DATE '2027-01-01'
GROUP BY entity_id
) AS p
FULL JOIN
(
SELECT entity_id, tx_count, total_lcy, fingerprint
FROM ch.monthly_tx_control
WHERE period = 202612
) AS c
ON c.entity_id = p.entity_id;Si alguna fila devuelve matched falso o vacío, el mes no está completo en ClickHouse y el cierre no sigue.
6.5 Ajustes y cierre de resultados
Paso 2. El balance de comprobación con subtotales.
SELECT
multiIf(
grouping(account_class) = 1, 'TOTAL',
grouping(account_group) = 1, 'CLASS',
grouping(account) = 1, 'GROUP',
'ACCOUNT') AS row_level,
dictGet('finance.chart_of_accounts', 'account_class',
account) AS account_class,
toUInt16(substring(toString(account), 1, 2))
AS account_group,
account,
dictGet('finance.chart_of_accounts', 'account_name',
account) AS account_name,
sum(debit_lcy_minor) AS total_debit_lcy,
sum(credit_lcy_minor) AS total_credit_lcy,
total_debit_lcy - total_credit_lcy AS balance_lcy
FROM finance.daily_balances
WHERE entity_id = 'CO01'
AND accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY ROLLUP(account_class, account_group, account)
ORDER BY account_class, account_group, account;row_level marca cada fila como cuenta, subtotal de grupo, subtotal de clase o total general; el grupo son los dos primeros dígitos del código. Como en todo *MergeTree, nunca doy por hecho una fila por clave: siempre sum() y GROUP BY.
Paso 3. Diferencia en cambio. Revalúo, por tercero, los saldos en moneda extranjera de las cuentas de balance con la última TRM vigente al cierre (las 23:59:59 del 31 de diciembre en Bogotá) y registro la contrapartida en ingresos (421020) o gastos (530525) por diferencia en cambio. Antes registro y apruebo en Postgres el comprobante con su tx_id:
INSERT INTO finance.journal_entries
(tx_id, line_no, entity_id, accounting_date, event_ts,
account, cost_center, counterparty_id, currency,
debit_minor, credit_minor, debit_lcy_minor,
credit_lcy_minor, source)
SELECT
{fx_tx_id:UUID} AS tx_id,
toUInt32(row_number() OVER (
ORDER BY entity_id, currency, line.1, line.2,
line.3, line.4)) AS line_no,
entity_id,
toDate('2026-12-31') AS accounting_date,
toDateTime64('2026-12-31 23:59:59.999', 3,
'America/Bogota') AS event_ts,
line.1 AS account,
line.2 AS cost_center,
line.3 AS counterparty_id,
currency,
toInt64(0) AS debit_minor,
toInt64(0) AS credit_minor,
greatest(line.4, 0) AS debit_lcy_minor,
greatest(-line.4, 0) AS credit_lcy_minor,
'ADJUSTMENT' AS source
FROM
(
SELECT
b.entity_id,
b.account AS revalued_account,
b.cost_center AS revalued_cost_center,
b.counterparty_id AS revalued_counterparty,
b.currency,
convert_minor(b.fc_minor, b.currency, 'COP', r.rate)
- b.lcy_minor AS fx_diff_minor
FROM
(
-- foreign-currency balances of monetary accounts
SELECT
entity_id, account, cost_center, counterparty_id,
currency,
sum(debit_minor) - sum(credit_minor) AS fc_minor,
sum(debit_lcy_minor) - sum(credit_lcy_minor)
AS lcy_minor
FROM finance.journal_entries FINAL
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
AND currency != 'COP'
AND dictGet('finance.chart_of_accounts',
'account_class', account) IN (1, 2)
GROUP BY entity_id, account, cost_center,
counterparty_id, currency
) AS b
INNER JOIN
(
-- latest official rate before the cut-off
SELECT currency, argMax(rate, valid_from) AS rate
FROM finance.fx_rates
WHERE rate_source = 'TRM'
AND valid_from < toDateTime64(
'2027-01-01 00:00:00', 3, 'America/Bogota')
GROUP BY currency
) AS r ON b.currency = r.currency
WHERE fx_diff_minor != 0
)
ARRAY JOIN [
-- revalued account, per counterparty
(revalued_account, revalued_cost_center,
revalued_counterparty, fx_diff_minor),
-- FX gain (421020) or loss (530525)
(if(fx_diff_minor > 0, 421020, 530525), 'CORP',
toInt64(0), -fx_diff_minor)
] AS line
SETTINGS do_not_merge_across_partitions_select_final = 1;Cada ajuste genera dos líneas que se compensan, así que el comprobante cuadra por construcción. Revaluar por tercero mantiene cuadrados los auxiliares de cartera y proveedores. Filtrar las clases 1 y 2 es una simplificación: según la NIC 21 solo se revalúan las partidas monetarias, y no los inventarios, las propiedades, los intangibles, los gastos pagados por anticipado ni los anticipos dados o recibidos. En producción el filtro sale del plan de cuentas. Las horas del corte van en la zona horaria de Bogotá y ClickHouse las convierte a UTC al comparar y al guardar; con 'UTC' el corte quedaría cinco horas antes.
Esta diferencia en cambio es contable (NIIF). En lo fiscal, la diferencia no realizada no tiene efecto hasta su realización (artículo 288 del Estatuto Tributario), genera impuesto diferido y debe quedar en el control de diferencias y en el reporte de conciliación fiscal (formato 2516).
Renombré account, cost_center y counterparty_id en la subconsulta porque en ClickHouse un alias tiene prioridad sobre una columna del mismo nombre y, sin ese cambio, la consulta falla con un error de identificador desconocido. Me pasó en la primera versión. Por lo mismo, cuando un total se reutiliza en el mismo SELECT (control 1a, balance de comprobación) lo llamo total_debit y no debit_minor.
Si la inserción se interrumpe y la repito tal cual, con los mismos saldos de entrada, las líneas repetidas se colapsan como las del CDC: el row_number ordena por la tupla completa y numera igual. Si algo cambió entre los dos intentos, el control 1b lo detecta; en ese caso, y siempre que haya que corregir un comprobante ya registrado, se revierte y se registra uno nuevo con otro tx_id.
Paso 4. Cerrar las cuentas de resultado. Antes de este paso ya están registrados el impuesto de renta corriente y diferido (NIC 12), las provisiones, las depreciaciones y las reclasificaciones, y la clase 7 (costos de producción) ya se trasladó a la 6 o al inventario. Repito el paso 0 y cierro las clases 4, 5 y 6 contra la utilidad (3605) o la pérdida (3610) del ejercicio:
INSERT INTO finance.journal_entries
(tx_id, line_no, entity_id, accounting_date, event_ts,
account, cost_center, counterparty_id, currency,
debit_minor, credit_minor, debit_lcy_minor,
credit_lcy_minor, source)
SELECT
{closing_tx_id:UUID} AS tx_id,
toUInt32(row_number() OVER (
ORDER BY entity_id, currency, is_offset, account,
cost_center)) AS line_no,
entity_id,
toDate('2026-12-31') AS accounting_date,
toDateTime64('2026-12-31 23:59:59.999', 3,
'America/Bogota') AS event_ts,
account,
cost_center,
0 AS counterparty_id,
currency,
-- post the opposite of each balance
greatest(-fc_minor, 0) AS debit_minor,
greatest(fc_minor, 0) AS credit_minor,
greatest(-lcy_minor, 0) AS debit_lcy_minor,
greatest(lcy_minor, 0) AS credit_lcy_minor,
'CLOSING' AS source
FROM
(
-- lines that bring each income-statement account to 0
SELECT
entity_id, account, cost_center, currency,
0 AS is_offset,
sum(debit_minor) - sum(credit_minor) AS fc_minor,
sum(debit_lcy_minor) - sum(credit_lcy_minor)
AS lcy_minor
FROM finance.daily_balances
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
AND dictGet('finance.chart_of_accounts',
'account_class', account) IN (4, 5, 6)
GROUP BY entity_id, account, cost_center, currency
HAVING fc_minor != 0 OR lcy_minor != 0
UNION ALL
-- offset per entity and currency. Profit (3605) or
-- loss (3610) is decided on the entity's total result
-- in functional currency, not per currency.
SELECT
entity_id,
if(sum(sum(debit_lcy_minor) - sum(credit_lcy_minor))
OVER (PARTITION BY entity_id) <= 0,
3605, 3610) AS offset_account,
'CORP' AS offset_cost_center,
currency,
1 AS is_offset,
sum(credit_minor) - sum(debit_minor) AS offset_fc,
sum(credit_lcy_minor) - sum(debit_lcy_minor)
AS offset_lcy
FROM finance.daily_balances
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
AND dictGet('finance.chart_of_accounts',
'account_class', account) IN (4, 5, 6)
GROUP BY entity_id, currency
HAVING offset_fc != 0 OR offset_lcy != 0
);La cuenta de utilidad o pérdida se elige con el resultado total de la entidad en moneda funcional. Las líneas de contrapartida se separan por moneda solo para que el control 1a siga cuadrando por moneda; el resultado del ejercicio solo tiene sentido en moneda funcional, y así se presenta.
Si tu entidad pasa primero por la 5905 (Ganancias y pérdidas), exclúyela del filtro de la clase 5. En el segundo bloque los alias tienen nombres distintos (offset_account) por la misma razón de antes: UNION ALL toma los nombres del primer SELECT, y account en el WHERE debe seguir siendo la columna.
Paso 5. Reconstruir y comprobar. Repito el paso 0 y los controles 1a, 1b y 1d, y compruebo que no quedó ninguna cuenta de resultado con saldo:
-- 5. No income-statement account may keep a balance
SELECT throwIf(count() > 0, 'P&L not closed')
FROM
(
SELECT entity_id, account, currency
FROM finance.daily_balances
WHERE toYear(accounting_date) = 2026
AND dictGet('finance.chart_of_accounts',
'account_class', account) IN (4, 5, 6)
GROUP BY entity_id, account, currency
HAVING sum(debit_lcy_minor) != sum(credit_lcy_minor)
OR sum(debit_minor) != sum(credit_minor)
);6.6 Apertura, sellado y archivo
Paso 6. Saldos de apertura de 2027. Traslado los saldos de las clases 1, 2 y 3 por tercero, para que la cartera y los proveedores conserven su detalle:
INSERT INTO finance.journal_entries
(tx_id, line_no, entity_id, accounting_date, event_ts,
account, cost_center, counterparty_id, currency,
debit_minor, credit_minor, debit_lcy_minor,
credit_lcy_minor, source)
SELECT
{opening_tx_id:UUID} AS tx_id,
toUInt32(row_number() OVER (
ORDER BY entity_id, currency, account, cost_center,
counterparty_id)) AS line_no,
entity_id,
toDate('2027-01-01') AS accounting_date,
toDateTime64('2027-01-01 00:00:00', 3,
'America/Bogota') AS event_ts,
account,
cost_center,
counterparty_id,
currency,
greatest(fc_minor, 0) AS debit_minor,
greatest(-fc_minor, 0) AS credit_minor,
greatest(lcy_minor, 0) AS debit_lcy_minor,
greatest(-lcy_minor, 0) AS credit_lcy_minor,
'OPENING' AS source
FROM
(
SELECT
entity_id, account, cost_center, counterparty_id,
currency,
sum(debit_minor) - sum(credit_minor) AS fc_minor,
sum(debit_lcy_minor) - sum(credit_lcy_minor)
AS lcy_minor
FROM finance.journal_entries FINAL
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
AND dictGet('finance.chart_of_accounts',
'account_class', account) IN (1, 2, 3)
GROUP BY entity_id, account, cost_center,
counterparty_id, currency
HAVING fc_minor != 0 OR lcy_minor != 0
)
SETTINGS do_not_merge_across_partitions_select_final = 1;Con las cuentas de resultado en cero y la clase 7 ya trasladada, los saldos de las clases 1, 2 y 3 suman cero y la apertura cuadra sola. Las cuentas de orden (clases 8 y 9) se trasladan aparte. La utilidad o pérdida del año (3605 o 3610) entra a 2027 tal cual y se reclasifica a utilidades acumuladas o reservas cuando la asamblea decide su destino. Desde aquí, las consultas de 2027 parten de la apertura y no necesitan leer 2026.
Paso 7. Sellar el año. Calculo la huella de cada mes y la guardo en Postgres con el acta de cierre; si dentro de cinco años alguien vuelve a calcularla y no coincide, algo cambió. Después respaldo el año en un bucket con bloqueo de objetos y guardo una copia del plan de cuentas tal como estaba, porque el diccionario se refresca y en diez años los nombres y las clases pueden ser otros:
-- 7a. Closing fingerprint per month
SELECT
toYYYYMM(accounting_date) AS period,
count() AS line_count,
sum(debit_lcy_minor) AS total_debit_lcy,
sum(credit_lcy_minor) AS total_credit_lcy,
groupBitXor(cityHash64(tx_id, line_no, account,
currency, debit_minor, credit_minor,
debit_lcy_minor, credit_lcy_minor)) AS fingerprint
FROM finance.journal_entries FINAL
WHERE accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY period
ORDER BY period
SETTINGS do_not_merge_across_partitions_select_final = 1;
-- 7b. Back up the year to the object-locked bucket
BACKUP TABLE finance.journal_entries
PARTITIONS '202601', '202602', '202603', '202604',
'202605', '202606', '202607', '202608',
'202609', '202610', '202611', '202612'
TO S3(s3_worm, '2026/journal_entries');
-- 7c. Freeze the chart of accounts used for 2026
CREATE TABLE finance.chart_of_accounts_2026
ENGINE = MergeTree ORDER BY account AS
SELECT * FROM dictionary('finance.chart_of_accounts');Los datos de 2026 se quedan en journal_entries. Lo que impide cambiarlos es que solo el rol del proceso de cierre tiene INSERT sobre la tabla, que todo comprobante manual exige aprobación en Postgres (control 1d) y que la huella delataría cualquier cambio. Vale la pena darle a journal_vouchers la misma validación de periodo que tiene el disparador de transactions. ClickHouse Cloud hace además sus propias copias automáticas.
Paso 8. Exportar para la retención legal.
INSERT INTO FUNCTION s3(s3_worm,
filename = 'legal-archive/2026/{_partition_id}.parquet',
format = 'Parquet')
PARTITION BY toYYYYMM(accounting_date)
SELECT *
FROM finance.journal_entries FINAL
WHERE toYear(accounting_date) = 2026
SETTINGS do_not_merge_across_partitions_select_final = 1;Va al bucket con bloqueo, como el respaldo. Elegí Parquet porque dentro de diez años se podrá leer con DuckDB, Spark o pandas, sin depender de ClickHouse.
6.7 Después del cierre
Diciembre se queda en CLOSING hasta que la asamblea aprueba los estados financieros (en marzo, como tarde) y solo entonces pasa a CLOSED. Si el auditor pide un ajuste después de registrar la apertura, registro el ajuste, un comprobante complementario de cierre de resultados (paso 4) y otro de apertura por la diferencia (paso 6), repito los pasos 0, 1 y 5 y vuelvo a sellar y exportar (pasos 7 y 8) con otra ruta de destino. La huella que vale es la del año que aprueba la asamblea. Un error material de años anteriores no se corrige en el resultado del año: la NIC 8 exige reexpresión retroactiva contra las ganancias acumuladas.
Si la moneda funcional de la entidad no es el peso, amount_lcy_minor va en esa moneda y los estados financieros necesitan además una conversión a la moneda de presentación.
7. Datos personales y seguridad
Un libro de pagos está lleno de datos personales: nombres, cédulas, correos, teléfonos, cuentas bancarias, llaves de Bre-B (que pueden ser un celular, un correo o un número de documento) y, si hay tarjetas, números de tarjeta. La Ley 1581 de 2012, el Reglamento General de Protección de Datos europeo (GDPR) si hay clientes en la Unión Europea y PCI DSS si hay tarjetas ponen reglas distintas, pero llevan al mismo diseño: el almacén analítico no necesita saber quién es cada persona.
7.1 Seudónimos en el libro, identidad en Postgres
El libro de ClickHouse solo guarda counterparty_id, una clave sustituta. Los datos personales viven en Postgres, cifrados por la aplicación, y no se replican. Para cruzar con fuentes externas, como listas de sanciones, uso un HMAC de la cédula que calcula la aplicación con un secreto guardado fuera de ambas bases de datos.
-- PostgreSQL 15+: identity data stays here, encrypted
CREATE TABLE parties (
party_id bigint PRIMARY KEY,
party_key char(64), -- hex HMAC-SHA256 of ID
country_code char(2) NOT NULL, -- ISO 3166-1
full_name_enc bytea, -- encrypted by the app
national_id_enc bytea,
email_enc bytea,
phone_enc bytea,
-- address, Bre-B key and bank account: same pattern
processing_blocked boolean NOT NULL DEFAULT false,
retention_until date
);
-- Optional second layer, only if you create the
-- publication yourself and give its name to ClickPipes
CREATE PUBLICATION clickhouse_cdc
FOR TABLE transactions, accounting_periods,
journal_vouchers,
parties (party_id, party_key, country_code,
processing_blocked);La barrera que documenta ClickPipes es la exclusión de columnas al configurar el pipe: en parties excluyo las columnas cifradas. Una columna excluida no se puede volver a incluir sin resincronizar la tabla, que es justo lo que quiero. ClickPipes crea por defecto su propia publicación; la lista de columnas en la publicación (PostgreSQL 15 o superior) solo aplica si la creas tú, y la uso como capa adicional, nunca como la única. La documentación de Postgres advierte además que las listas de columnas no son un mecanismo de seguridad. Evito REPLICA IDENTITY FULL en tablas con datos personales porque escribe la fila completa en el WAL.
Aquí la retención contable choca con el derecho de supresión. Los libros, sus soportes y la información de terceros se conservan diez años contados desde el último asiento, documento o comprobante (artículo 28 de la Ley 962 de 2005, que modificó el artículo 60 del Código de Comercio), y esa identidad también soporta la información exógena y el SARLAFT o el SAGRILAFT, según el supervisor. La Ley 1581, por su parte, da derecho a pedir la supresión.
El artículo 9 del Decreto 1377 de 2013 resuelve el choque: mientras exista el deber legal de conservar, la supresión no procede sobre esos datos. Se le responde al titular con la causal, se bloquean los datos para cualquier otra finalidad y se borran al vencer el plazo. Lo que no tiene deber de conservación, como el correo o el teléfono de contacto, sí se borra de inmediato:
-- PostgreSQL: erasure request for party 42.
-- Contact data has no retention duty: delete it now.
-- Identity data backs accounting, tax and AML records:
-- block it and delete it when the retention period ends.
UPDATE parties
SET email_enc = NULL,
phone_enc = NULL,
processing_blocked = true
WHERE party_id = 42;
-- Scheduled job: purge identity data after retention
UPDATE parties
SET full_name_enc = NULL,
national_id_enc = NULL,
party_key = NULL
WHERE processing_blocked
AND retention_until < current_date;-- ClickHouse: after the purge job, drop the previous
-- version of each row (the one that still holds the HMAC)
OPTIMIZE TABLE finance.parties FINAL;processing_blocked sí se replica: cualquier análisis de mercadeo o perfilamiento en ClickHouse debe excluir a esos terceros. Cuando el trabajo programado borra la identidad, el cambio llega a ClickHouse como una versión nueva de la fila, con party_key vacío. La versión anterior, con el HMAC, sigue en disco hasta la siguiente fusión; OPTIMIZE ... FINAL la adelanta, y en una tabla pequeña como parties es barato. Las copias de seguridad guardan la versión anterior hasta que vencen.
Si hay titulares en la Unión Europea, el GDPR no ofrece la misma salida: la excepción del artículo 17.3.b exige una obligación del derecho de la Unión o de un Estado miembro, y una norma colombiana no basta. Ese caso requiere otro análisis. Y en ambos regímenes, los datos seudonimizados siguen siendo datos personales.
7.2 Controles en ClickHouse
El primer control es el permiso por columna, que funciona en cualquier edición. Un analista consulta las líneas de asiento sin ver el identificador del tercero:
CREATE ROLE analyst_role;
GRANT SELECT(tx_id, line_no, entity_id, accounting_date,
account, cost_center, currency, debit_minor,
credit_minor, debit_lcy_minor, credit_lcy_minor,
source)
ON finance.journal_entries TO analyst_role;Si ese rol pide counterparty_id, ClickHouse responde con un error de acceso denegado, no con filas vacías, y así nadie lo pasa por alto. Lo mismo pasa con un SELECT *, que obliga a listar las columnas permitidas.
En ClickHouse Cloud, desde la versión 25.12, hay además políticas de enmascaramiento que transforman una columna al consultarla. Son exclusivas de Cloud: la versión de código abierto responde “Masking Policies are available only in ClickHouse Cloud”.
-- ClickHouse Cloud 25.12+ only
CREATE MASKING POLICY mask_counterparty
ON finance.journal_entries
UPDATE counterparty_id = 0
TO bi_role;Con las políticas de fila hay una trampa que comprobé: un usuario sin ninguna política sobre una tabla ve todas sus filas, aunque otros roles tengan políticas restrictivas. Por eso concedo acceso solo a través de roles y defino una política para cada uno.
CREATE ROW POLICY entity_co01_only
ON finance.journal_entries
FOR SELECT USING entity_id = 'CO01'
TO co01_accountant_role;Y además:
Las claves escritas como literales en funciones como
encryptoHMACpueden quedar ensystem.query_log; la documentación dequery_masking_rulesdescribe ese riesgo. Las credenciales de Postgres y del almacenamiento van en named collections y los secretos de cifrado se usan antes de que el dato llegue a ClickHouse. El acceso aquery_logtambién se restringe.Solo el rol del proceso de cierre tiene
INSERTsobrejournal_entries; el usuario de ClickPipes escribe en las tablas del CDC y las vistas hacen el resto.En ClickHouse Cloud el cifrado en reposo viene activado por defecto. El cifrado con clave del cliente (CMEK) requiere el plan Enterprise, y borrar esa clave inutiliza el servicio completo; el borrado criptográfico de una persona se hace en la aplicación, con una clave por titular.
DELETE, las mutaciones y el TTL borran al fusionar, y los respaldos guardan rastros.Desde el 31 de marzo de 2025, PCI DSS 4.0.1 exige que, si se usa un hash para hacer ilegible el número de tarjeta, sea un hash con clave (requisito 3.5.1.1), y deja de aceptar el cifrado de disco como única protección salvo en medios extraíbles (3.5.1.2). El truncamiento máximo depende de la longitud y la marca (hasta los primeros 8 y los últimos 4 en tarjetas de 16 dígitos), y las versiones truncada y con hash de una misma tarjeta no deben poder correlacionarse. Lo más simple es guardar solo el token de la pasarela, y con eso el almacén queda fuera del alcance de PCI.
Las claves de idempotencia tampoco llevan datos personales. Uso UUID v4, con
UNIQUEen Postgres como fuente de verdad.
7.3 Normas colombianas
Las regiones de ClickHouse Cloud están fuera de Colombia (la más cercana es São Paulo). Aun con seudónimos hay transmisión internacional, porque counterparty_id y el HMAC siguen siendo datos personales: se necesita un contrato de transmisión con el encargado o las cláusulas contractuales modelo de la Red Iberoamericana de Protección de Datos, que la SIC adoptó como opcionales en la Circular Externa 003 de 2025. La Circular Externa 002 de 2025 aplica además si hay transferencia de tecnología. Las entidades vigiladas por la Superintendencia Financiera tienen también las reglas de computación en la nube de la Circular Externa 005 de 2019 y los requisitos de ciberseguridad de la Circular Externa 007 de 2018.
Si la entidad presta servicios financieros digitales (crédito, depósitos de bajo monto o billeteras), revisa la Circular Externa 001 de 2025 de la SIC, que según los análisis publicados exige minimización, plazos de supresión y consentimientos separados. El registro de bases de datos ante la SIC es obligatorio para las sociedades y entidades sin ánimo de lucro con activos superiores a 100 000 UVT y para las personas jurídicas de naturaleza pública.
No soy abogado: esto es lo que encontré al revisar las normas para diseñar el sistema. Valídalo con tu área jurídica antes de implementarlo.
8. ClickHouse Cloud
Operar un clúster de ClickHouse que recibe 7200 millones de filas al año (réplicas, fragmentos, discos, actualizaciones, respaldos) pide un equipo dedicado. En Cloud eso lo opera ClickHouse: los datos viven en almacenamiento de objetos en una sola copia y el almacenamiento crece sin redimensionar discos.
Para el cierre, lo más útil fue separar el cómputo. Varios servicios leen los mismos datos: uno estable para la ingesta, otro para el cierre que agrando en diciembre y enero y apago el resto del año, uno de solo lectura para BI con escalado automático y otro para auditores, con sus propios usuarios y red, que solo enciendo durante la auditoría. Así el cierre no le quita CPU a la ingesta, y el servicio grande solo lo enciendo, de diciembre a marzo, cuando hay que correr el cierre.
ClickPipes, además del CDC de Postgres, se conecta a Kafka y servicios compatibles, a los servicios de streaming y almacenamiento de objetos de los principales proveedores de nube, y ofrece CDC para MySQL y MongoDB; algunos conectores siguen en vista previa. En cumplimiento, ClickHouse Cloud tiene SOC 2 Tipo II desde 2022, ISO 27001 desde 2023, HIPAA desde 2024 y PCI DSS desde 2025, con los informes en su Trust Center. HIPAA y PCI DSS requieren el plan Enterprise; la conectividad privada está disponible desde el plan Scale.
9. ClickHouse Managed Postgres y pg_clickhouse
ClickHouse Managed Postgres, anunciado al principio como “Postgres managed by ClickHouse”, es un Postgres administrado dentro de ClickHouse Cloud. Corre sobre discos NVMe locales; en su prueba de rendimiento PostgresBench, ClickHouse reporta hasta cinco veces las transacciones por segundo de un Postgres administrado sobre discos de red, en cargas limitadas por disco. Trae integrado el CDC hacia ClickHouse, en beta y construido sobre ClickPipes, así que todo lo de la sección 4 aplica igual. También trae pg_clickhouse, una extensión de código abierto para consultar ClickHouse desde Postgres.
Salió en beta pública en un primer proveedor de nube. Durante la beta se anunció gratis hasta el 15 de junio de 2026 y luego con 50 % de descuento mientras durara esa fase, con el CDC y pg_clickhouse incluidos, y el 9 de septiembre de 2026 se anunció una vista previa privada en un segundo proveedor. Esas condiciones cambian: revisa la página del producto antes de decidir.
Antes, para llevar Postgres a un almacén analítico había que montar Debezium, Kafka y conectores. Desde mayo de 2025, ClickPipes ofrece CDC administrado para cualquier Postgres 12 o superior, y Managed Postgres lo integra en el mismo proveedor.
Con pg_clickhouse, Postgres ve las tablas y vistas de ClickHouse como tablas foráneas:
-- PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_clickhouse;
CREATE SERVER clickhouse_finance
FOREIGN DATA WRAPPER clickhouse_fdw
OPTIONS (driver 'binary',
host 'your-service.clickhouse.cloud',
dbname 'finance');
-- one mapping for the admin who imports the schema,
-- one for the application
CREATE USER MAPPING FOR CURRENT_USER
SERVER clickhouse_finance
OPTIONS (user 'reader', password '***');
CREATE USER MAPPING FOR backoffice_app
SERVER clickhouse_finance
OPTIONS (user 'reader', password '***');
CREATE SCHEMA IF NOT EXISTS ch;
IMPORT FOREIGN SCHEMA "finance"
LIMIT TO (daily_balances, monthly_tx_control)
FROM SERVER clickhouse_finance INTO ch;
GRANT USAGE ON SCHEMA ch TO backoffice_app;
GRANT SELECT ON ch.daily_balances, ch.monthly_tx_control
TO backoffice_app;
-- the aggregation runs inside ClickHouse
SELECT
account,
sum(debit_lcy_minor) - sum(credit_lcy_minor)
AS balance_lcy_minor
FROM ch.daily_balances
WHERE entity_id = 'CO01'
AND accounting_date BETWEEN '2026-01-01'
AND '2026-12-31'
GROUP BY account;En la primera versión de este ejemplo olvidé el mapeo para el administrador y IMPORT FOREIGN SCHEMA falló; con los dos mapeos funciona. EXPLAIN VERBOSE muestra que Postgres envía a ClickHouse el filtro y la agregación y solo recibe el resultado:
Con driver 'binary' y un host de ClickHouse Cloud, pg_clickhouse usa el puerto 9440 y activa TLS automáticamente.
No lo recomiendo cuando el volumen exige fragmentar las escrituras (decenas de miles de transacciones por segundo sostenidas), cuando la regulación obliga a tener el OLTP en una región donde el servicio no existe, o mientras siga en beta si la política de riesgo de la organización no admite servicios beta en producción.
10. Tamaño estimado y riesgos
Lo que más pesa en disco son los identificadores aleatorios: dos UUID por transacción son 32 bytes que casi no se comprimen, a diferencia de las cuentas o las monedas. Si no usas idempotency_key en ClickHouse, exclúyela del pipe.
Para las cargas propias, inserta en lotes o con async_insert (miles de inserciones de una fila generan miles de partes pequeñas), usa insert_deduplication_token para que los reintentos sean idempotentes y no particiones más fino que un mes. ClickPipes ya inserta en lotes; sus reintentos están en la sección 4.4.
11. Lista de verificación del cierre
En noviembre:
Servicio de cómputo para el cierre creado y probado.
Ensayo general del cierre con los datos de noviembre, incluida la reconstrucción del paso 0.
Balance preliminar refrescable activo.
TRM como fuente oficial de tasas y hora de corte definidas.
Bucket con bloqueo de objetos y named collections configurados.
Alertas sobre el retraso del slot y el estado del pipe.
Entre el 31 de diciembre y el corte de operaciones (por ejemplo, el 3 de enero):
202612 sigue
OPENpara las operaciones con fecha de diciembre que llegan tarde.Monitoreo de los pagos tardíos.
En el corte:
202612 en
CLOSINGen Postgres y posición del WAL anotada.Slot de ClickPipes al día con esa posición.
Conciliación:
Saldos reconstruidos y verificados mes a mes (paso 0).
Controles 1a a 1e superados.
Cada mes comparado con Postgres a través de
pg_clickhouse(conteo, total y huella).Conteos y totales iguales a los de bancos y pasarelas; diferencias explicadas partida por partida.
Partidas conciliatorias documentadas.
Ajustes y cierre:
Comprobantes registrados y aprobados en Postgres antes de insertarlos.
Diferencia en cambio con la TRM del corte (paso 3), con su efecto fiscal documentado.
Impuesto de renta corriente y diferido (incluido el de la diferencia en cambio), provisiones, depreciaciones, reclasificaciones y clase 7 contabilizados.
Cuentas de resultado cerradas y sin saldo (pasos 4 y 5).
Apertura de 2027 registrada y cuadrada (paso 6).
Huella mensual guardada con el acta, respaldo en el bucket con bloqueo y plan de cuentas congelado (paso 7).
Exportación a Parquet (paso 8).
Hasta la aprobación de los estados financieros:
Diciembre en
CLOSING; ajustes del auditor con el procedimiento de la sección 6.7, incluido un nuevo sellado y exportación.Servicio de solo lectura para auditores, con permisos por rol y políticas de fila.
Diciembre en
CLOSEDdespués de la aprobación.
Durante todo el año:
Si hubo una resincronización del CDC: comprobar que las vistas siguen enganchadas, volver a derivar las líneas de los meses afectados, reconstruir saldos e indicadores y repetir los controles.
Solicitudes de supresión de datos personales atendidas en Postgres, sin tocar el libro.
Este diseño lo armé para una sola moneda funcional. Si te toca llevar NIIF y fiscal en paralelo, o varias monedas funcionales, cuéntame en los comentarios cómo lo resolviste: es lo siguiente que quiero probar.
12. Referencias
CDC de Postgres con ClickPipes
Postgres CDC connector for ClickPipes is now Generally Available
PeerDB: Data type matrix · ClickHouse data modeling best practices · Código de normalización (normalize.go)
ClickHouse
Materialized views · CREATE VIEW (incremental y refrescables)
Hierarchical dictionaries · Functions for working with dictionaries · PostgreSQL dictionary source
GRANT · CREATE ROW POLICY · CREATE MASKING POLICY · Data masking in ClickHouse
BACKUP and RESTORE · Manipulating partitions · s3 table function
ClickHouse Cloud, Managed Postgres y pg_clickhouse
SharedMergeTree · Warehouses (compute-compute separation) · ClickHouse Cloud tiers · Supported cloud regions
Security and compliance · Trust Center · Data encryption (CMEK)
Postgres managed by ClickHouse is now in beta · Anuncio de la vista previa privada (9 de septiembre de 2026) · PostgresBench
PostgreSQL
CREATE PUBLICATION · Logical replication: column lists · ALTER TABLE (REPLICA IDENTITY) · CREATE TRIGGER
Dinero y estándares
ISO 4217: Agencia de mantenimiento · Lista oficial (XML)
RFC 3339: Date and Time on the Internet · ISO 8601 · ISO 3166
Banco de la República: Bre-B (documento de trabajo, febrero de 2026)
Datos personales y seguridad
Ley 1581 de 2012 · Decreto 1377 de 2013 · Decreto 1074 de 2015 · Decreto 090 de 2018
SIC: Circular Externa 003 de 2025 · Circular Externa 002 de 2025 · Circular Externa 001 de 2025
PCI SSC: PCI DSS v4.0.1 · Formatos de truncamiento aceptados
Contabilidad y tributario