QuintoAndar: customer data, from Lambda architecture to a single path in ClickHouse

01 · CONTEXT

QuintoAndar's customer data platform

QuintoAndar's customer data platform (CDP) team collects, integrates, and unifies data about tenants, landlords, and real estate agents. According to the team's tech lead, the platform collects more than 1 billion events per month from more than 14 million users, with almost 900 million raw events per month going to ClickHouse.

This data flows back to chatbots and internal services in about 300 million API requests per month. It originates in user tracking on mobile devices and in change data capture (CDC) from transactional databases, streamed through Kafka, and reaches an in-house event gateway, where the team applies governance, runs transformations, and enriches the events along the way.

02 · PREVIOUS ARCHITECTURE

Classic Lambda: a hot path and a cold path

The previous architecture was split along the classic lines of the Lambda architecture. The hot path was Datazord, another in-house service built on Postgres, which consumed the gateway's output topics and served user information to chatbots and other internal consumers from its own Postgres database, chosen for low-latency reads.

The cold path used Kafka Connect to dump everything into a landing zone in the data lake, where always-on Databricks workflows processed it into a silver layer. From that layer, the analytical ETLs and Hightouch, the company's user activation tool, could pull data.

1. Mobile tracking and CDC events arrive at the event gateway, which applies governance, transformations, and enrichment.

2. On the hot path, Datazord consumes the output topics and serves chatbots and internal consumers from its own Postgres.

3. On the cold path, Kafka Connect dumps the data into the landing zone and the Databricks workflows build the silver layer.

4. The analytical ETLs and Hightouch read from the cold path's silver layer.

5. The pain points at the time: constant MERGE INTO operations on Delta tables on the cold path and a Postgres that seemed like the wrong tool for the event volume on the hot path.

03 · THE PROBLEM

The cost of maintaining two paths

According to the tech lead, there were three problems. First, the team maintained two separate codebases, a well-known tax for anyone who has operated a Lambda architecture. Second, on the cold path, huge volumes of duplicate events required constant MERGE INTO operations on Delta tables. Third, the Postgres instance in the hot layer was starting to look like the wrong tool for the volume of events passing through it.

In his words, deduplicating all those millions of events every time became expensive, not just in cost but in time.

04 · THE DECISION

A gateway writing directly to ClickHouse

The team's answer was to stop treating the Kafka topics as the pipeline's output and have the event gateway write directly to ClickHouse. In the new architecture, Datazord would serve its API requests from ClickHouse, instead of its own Postgres, still responding in under a second. This would eliminate the landing zone, the constant MERGE INTO operations, and the need for a second codebase.

1. The event gateway writes directly to ClickHouse, and the output topics are no longer the pipeline's destination.

2. Datazord now serves the API from ClickHouse, no longer from its own Postgres.

3. With this design, the landing zone, the constant MERGE INTO operations, and the second codebase go away.

4. Trino and Spark reach the CDP tables through connectors, and ClickHouse reads Delta tables from the data lake.

The team feared isolating itself from the rest of the data organization, which uses Superset on top of Trino. In practice, ClickHouse's connectors let Trino and Spark reach the CDP tables, and ClickHouse also reads Delta tables from the company's data lake.

05 · THE OBSTACLE

Where is ClickHouse?

The marketing team wanted to use Hightouch's Customer Studio to build audiences, segment users, run A/B tests, and build multi-step journeys. But the list of sources the feature supports, with Snowflake, Databricks, BigQuery, Redshift, Athena, Synapse, MS SQL Server, PostgreSQL, Greenplum, and Microsoft Fabric, did not include ClickHouse.

Databricks was the only compatible engine already in the stack, but the central data team had decided to move off Databricks SQL Warehouse because of its high cost, preferring open-source alternatives such as Trino. Trino was not supported either, which left one option: Postgres.

06 · THE BRIDGE

Managed Postgres as a universal interface

The mechanism is the pg_clickhouse extension. According to the lead, the setup is simple: create the extension, create a foreign server pointing to ClickHouse, create a schema, and import the foreign tables. After that, ClickHouse tables can be queried like ordinary Postgres tables, with joins and aggregations pushed down to ClickHouse instead of being pulled back into Postgres.

1. Hightouch did not have ClickHouse among the Customer Studio sources.

2. Since Trino was not supported either, the remaining option was to connect Hightouch to a Postgres.

3. The managed Postgres pushes analytical queries down to ClickHouse, where the gateway already writes the events.

4. Lightning Sync state, with a planning table and an audit table, lives in Postgres itself, on NVMe storage.

5. For the Hightouch case, the lead says the round trip between Postgres and ClickHouse is negligible.

For the lead, the connection does two jobs: Postgres persists data like an OLTP database, while analytical queries run on ClickHouse. This does not open everything up: you still need to create users and roles and grant the desired access.

With this, ClickHouse becomes a plug-and-play database to which any third-party tool that supports Postgres can connect.

07 · LESSONS

What to take into your interview