How to Build Customer-Facing Analytics Pipelines with CDC

Customer-facing analytics is the reporting a SaaS product shows its own customers: usage dashboards, billing summaries, operational reports inside the app. The data starts in the production database, and the customer expects current numbers that show only their own account.

The pipeline behind it has five stages. Log-based change data capture (CDC) reads every insert, update, and delete from the production database's log. The changes land as a full history and as a current-state copy. Transformations turn them into reporting tables. A serving layer built for many concurrent queries answers the dashboards and enforces tenant access. The application queries that serving layer and never sends dashboard queries to production.

What Is Customer-Facing Analytics, and How Is It Different from Internal BI?

Customer-facing reporting makes freshness, access control, and correctness part of how the product behaves. Deel's success story describes the difference from its internal reporting this way: "Internal dashboards can tolerate a delay. Customer-facing ones cannot."

Four requirements follow when customers see the numbers:

• Freshness becomes a service level. A customer who updates a record in the product expects the report to reflect it within the time the product promises.
• Query volume follows customer activity, which runs around the clock for customers in every time zone and grows with every account. The serving layer has to answer many small queries at once without queuing them.
• Every query has to return one customer's rows and nothing else. A missing filter exposes one customer's data to another.
• Customers compare dashboards with invoices and their own records, and they report the discrepancies they find.

Why Shouldn't Customer-Facing Dashboards Query Production Databases?

Production databases are built for short transactions on individual rows. Analytical queries scan many rows and aggregate them, and customer dashboards issue them in bursts that follow user activity. MotherDuck's guide to customer-facing analytics describes teams that run analytical queries "directly in a multi-tenant transactional database, which works until it fails at scale." Each dashboard query competes with application traffic for CPU, memory, and I/O on the database the product depends on.

A read replica is a common first step, and it moves the scans off the primary. It doesn't remove the conflict between long queries and the changes the replica is applying. PostgreSQL's hot standby documentation sets out the trade:

• When cleanup on the primary removes row versions that a standby query still needs, the standby cancels the query once max_standby_streaming_delay is exceeded (30 seconds by default). The docs note that heavily updated tables "will quickly cause cancellation of longer running queries on the standby."
• Raising the delay keeps queries running but lets the replica fall further behind the primary.
• hot_standby_feedback avoids the cancellations by holding back cleanup on the primary, which "can cause database bloat on the primary for some workloads."

Amazon Aurora replicas share the cluster's storage volume and usually lag the writer by much less than 100 milliseconds, but the query conflicts remain. Aurora PostgreSQL readers cancel queries on recovery conflicts, and they can restart when replication is blocked in some circumstances.

A replica remains a reasonable choice for a small product with modest reporting. A separate serving layer becomes necessary when dashboard concurrency, query length, tenant access rules, or freshness targets outgrow what the replica can provide without affecting production.

Customer-Facing Analytics Architecture: From CDC to Dashboards

The architecture has five stages, and each has its own freshness and correctness check:

1. Capture reads committed changes from the database's log, such as PostgreSQL's write-ahead log or MySQL's binlog. After the initial backfill (the first copy of existing rows), it reads changes from the log without re-querying application tables. Adding a table normally starts a backfill of that table. If a replication slot is lost, the capture fails and needs manual recovery, including a new backfill. Log-based capture adds some load of its own on the source. Check capture lag, the time between a commit and its capture.
2. The captured changes are kept in two forms. Both can live in the serving layer itself, or in a warehouse whose transformed output is copied to the serving layer. The history holds every captured change to every row, for audit, replay, and validation. The current state holds one row per key at its latest value, which most reports read. Depending on the pipeline's settings, a delete either removes the row or marks it as deleted (a soft delete) that reports have to filter out. Incorrect handling of primary key updates can also leave stale or orphan rows in downstream tables, as shown in our CDC primary key changes tests comparing Debezium and Estuary. Validate that the current state matches the history once every change has been applied.
3. Transformations, built with dbt or SQL, turn the current state, or the history where a report needs it, into reporting tables: per-customer aggregates, denormalized views, metric definitions. Check keys, relationships, and accepted values with tests, and check the freshness of each model's inputs.
4. The serving layer holds the reporting tables, enforces tenant access, and answers concurrent queries. Check the freshness of the served tables.
5. The customer application shows dashboards, embedded charts, exports, and APIs. Show the customer the timestamp of the data on screen.

End-to-end freshness is the sum of the stages:

• Capture lag, often seconds or less for log-based CDC;
• The destination's apply schedule, often a configurable interval for warehouse loaders (Estuary's materializations for warehouses, ClickHouse, and MotherDuck default to 30 minutes and can run continuously);
• The transformation schedule, often the largest term when models run hourly;
• Any refresh between the transformed tables and the serving layer;
• Caching in the application.

Write the freshness target down for each report, then size each term to fit inside it. A report promised within two hours can run on a 30-minute warehouse apply and an hourly dbt job. Those schedules leave roughly 30 minutes of the budget for capture, processing time, serving refresh, and caching. Base the promise on completion times measured under expected load. A report promised within minutes needs a short apply interval, incremental models, and a serving layer that takes changes without a full rebuild.

Best Databases for Serving Customer-Facing Analytics

The serving layer decides how fast dashboards answer, how many run at once, how updates and deletes land, and what serving costs. Snowflake, ClickHouse, PostgreSQL, MotherDuck, and Iceberg are common choices for CDC-fed customer reporting.

Some details behind the table:

• CDC sends updates and deletes, not only inserts. Snowflake, PostgreSQL, and MotherDuck update and delete rows in place. In ClickHouse, ReplacingMergeTree removes older versions only when parts (the data files each insert writes) merge in the background, "at an unknown time." A dashboard that needs the exact current value adds the FINAL query modifier, which merges versions at read time and makes each query slightly slower, and deleted rows drop out only when the table carries a deleted-row flag.
• Snowflake's interactive warehouses, generally available since December 2025, target "real-time dashboards, data-powered APIs, and high-concurrency serving workloads." An interactive warehouse can query the standard table that CDC merges into. An interactive table refreshed from that table has a minimum configurable target lag of 60 seconds, and its actual lag varies.
• A shared table with a tenant column keeps every customer in one table. A database per customer suits stronger isolation and per-customer compute, and MotherDuck's customer-facing analytics model gives each customer a dedicated DuckDB instance.
• Iceberg lets several engines read the same tables and supports row-level deletes from its version 2 specification. It records deletes in delete files or deletion vectors that readers apply at query time, or rewrites the affected data files (copy-on-write). Query speed and concurrency come from the engine on top. Frequent CDC commits make compaction (merging small files) and snapshot expiry (removing old table versions) routine maintenance.

Multi-Tenant Analytics: Tenant Isolation and Data Security

Tenant identity has to travel with every row from capture to the serving layer. When customers share tables in the production database, the tenant column comes along. When each customer has its own database, the pipeline adds a tenant identifier at capture, as the multi-tenant CDC guide describes. Different customers' databases can hold the same primary key values. Key shared current-state tables on tenant plus source primary key, keep each change's identity or version in history tables, and include the tenant in each reporting table's grouping keys.

A tenant column labels rows but doesn't restrict access. The serving layer enforces access, and each query has to carry the customer's identity: the application connects with a role or user per customer, or the policy maps the connected user to a tenant through a lookup table. Each engine enforces it differently:

• Snowflake row access policies (Enterprise Edition) filter rows by role or through a mapping table of users to tenants. Policies with simple expressions perform better than ones that look up a mapping table. A policy filters what a role reads and doesn't stop it inserting or updating rows. Give the application's role read access only.
• ClickHouse row policies filter reads, and ClickHouse says they "make sense only for users with readonly access." By default, a user with no policy on a table sees every row in it. Every application user needs a policy.
• PostgreSQL row-level security returns no rows when it's enabled with no policy. Superusers and roles with BYPASSRLS always bypass it, and so does the table owner unless the table uses FORCE ROW LEVEL SECURITY. The application shouldn't connect as the owner.
• MotherDuck recommends per-customer service accounts and databases as "the strongest isolation model." A single writer account that owns every customer database is simpler and gives up that isolation.

Choosing between shared and isolated tables also depends on scale. ClickHouse's multi-tenancy guide recommends a shared table with the tenant in the primary key as the simplest to manage when tenants share a schema and data volumes are moderate, and notes that separate tables or databases per tenant don't scale to thousands of tenants. Isolated databases fit a smaller number of large customers or customers with their own schemas, and a separate deployment fits data that has to stay in a region.

How to Monitor Data Freshness and Accuracy in Customer-Facing Analytics

Freshness has two parts: how recently data arrived, and how far each stage has processed. dbt's source freshness checks set warn_after and error_after against a loaded-at column. Snowflake's data metric functions (Enterprise Edition) run hourly by default and include FRESHNESS, which reports the time since a table was last modified or since the newest value in a timestamp column. Those checks measure recent activity and can't show whether every change has arrived. A quiet tenant's newest row can be old while its pipeline is fully caught up, and one new row can make a delayed table look current. Track processing progress too: each capture's position in the source log and each destination's last applied point. For tenants with little activity, a heartbeat row written on a schedule in each source database shows that the pipeline is still moving. Alert when a stage stops moving, as well as when it falls behind.

Test the data itself. dbt's generic tests (unique, not_null, accepted_values, relationships) catch duplicate keys and broken references in reporting models, and model contracts fail a build whose output doesn't match the declared columns and types. To validate downstream processing, compare the current state with the change history at a point both have fully applied, and compare values and deletes as well as keys and counts. Comparing them while changes are still landing produces false discrepancies. In Deel's pipeline, the team "validates downstream state against the change log rather than against the source database." That check covers what happens after capture. If capture misses a change, the history and the current state agree with each other and are both wrong. Capture completeness needs its own comparison against the source, run at a quiet time or against a replica, at a point the pipeline has fully applied and allowing for any replica lag.

Run checks per tenant as well as in total. Totals that reconcile overall can hide one customer's missing rows. Check row counts and keys per tenant for the largest customers and for any customer that reports a discrepancy. Snowflake's ROW_COUNT and NULL_COUNT data metric functions can be segmented by a tenant column; FRESHNESS can't. Measure per-tenant freshness from each tenant's processing progress or heartbeat.

Show an "as of" time on every report, taken from the latest point that every input to the report has fully processed, not from the newest row in one table or from the page load.

Real-World Customer-Facing Analytics Examples

Deel, a global HR and payroll platform, serves reporting back to its own customers. Its success story calls customer-facing analytics the "most critical workload" on Deel's data platform. Before Estuary, Deel "ingested data from production databases every two to six hours."

Deel's pipeline follows the reference architecture. According to the success story, Estuary captures changes from dozens of Aurora PostgreSQL databases across Deel's services, with SaaS sources syncing hourly into the same pipeline, and materializes them into Snowflake with Snowpipe Streaming. A landing layer keeps the full CDC history, and a dbt pipeline carries it into Deel's analytical layer, internal APIs, and applications. A RAW layer keeps one row per record at its latest value, and a reconciliation job collapses landing into RAW every 30 minutes.

Deel's customers see reporting built on that data in Snowflake, and an Iceberg-based serving layer is in development "for even lower latency at lower cost."

Deel's success story reports that reporting latency fell "from 2-6 hours to under 1 hour," that integration costs fell 50%, and that new integrations now go live in minutes. The story adds that Snowpipe Streaming cut warehouse costs further.

Connect&GO gives the museums, amusement parks, and festivals it serves reports and data feeds they use to direct staff and resources. After it moved from batch loads every 45 minutes to Estuary, its MySQL data typically landed in Snowflake within 15 seconds, a figure for capture and loading before any transformation.

How Estuary Supports Customer-Facing Analytics Pipelines

Building a pipeline like this in Estuary uses a few mechanisms:

• Captures read changes from PostgreSQL, MySQL, SQL Server, and other databases. Where customers have separate databases, each capture can add a source tag that identifies the tenant.
• Collections store the captured changes as JSON documents, either in your own cloud storage bucket or in Estuary's, which keeps them for 20 days. A destination table can be rebuilt from the documents a collection still holds. Past Estuary's 20 days, a backfill from the source restores the current state but not the earlier versions of rows or rows already deleted at the source. Keeping a full change history needs your own bucket or another archive.
• Materializations write collections to the serving layer, with one binding per destination table. Standard bindings merge each key to its latest value, and delta-updates bindings append changes. A delta-updates binding still combines documents within each transaction, and several changes to one key can arrive as one row. To keep every change, pair it with a capture in History Mode. Deletes are soft by default, marked in a _meta/op column, and Hard Delete removes them.
• Materializations to warehouses, ClickHouse, and MotherDuck run on a sync schedule that defaults to 30 minutes and can run continuously, and transactional destinations such as PostgreSQL apply changes as they arrive.
• Serving-layer destinations include Snowflake, ClickHouse, MotherDuck, PostgreSQL, and Apache Iceberg. Estuary's ClickHouse materialization creates ReplacingMergeTree tables; with Hard Delete on, FINAL queries exclude deleted rows.
• An OpenMetrics endpoint reports how far each binding is behind, in bytes, and the publication time of the last document it materialized, and alerts fire when a task stops receiving data within a set window.

Practical checklist

• Write down the freshness target for each customer-facing report.
• Size every stage of the latency budget (capture, apply, transformation, serving refresh, application cache) to fit that target.
• Keep dashboard queries off the production database.
• Capture with log-based CDC, and confirm the load the capture adds to the source.
• Keep a full change history and a current-state copy, and know which one each report reads.
• Decide whether deletes remove rows or mark them, and filter marked rows out of reports.
• Carry a tenant identifier on every row. Key current-state tables on tenant plus source primary key, and group reporting tables by tenant.
• Choose the serving layer by concurrency, update and delete handling, tenant model, and cost shape.
• Enforce tenant access in the serving layer, and test it with a user that has no policy.
• Track processing progress as well as the newest row at every stage, and alert when data stops moving.
• Validate current state against the change history at a fully applied point, check capture completeness against the source separately, and run per-tenant checks for the largest customers.
• Show customers an "as of" time on every report.

Building Customer-Facing Analytics with CDC?

Estuary helps teams capture database changes and deliver them to analytics destinations such as Snowflake, ClickHouse, PostgreSQL, and MotherDuck.

Book a demo to discuss your architecture and freshness requirements, or explore how Deel built customer-facing analytics with Estuary.

Final Takeaway

Reliable customer-facing analytics depends on more than moving data quickly. A production-ready architecture must balance CDC freshness, serving-layer performance, tenant isolation, and data correctness.

Log-based CDC makes it possible to feed analytical systems without running dashboard queries against production, while the serving layer and validation strategy determine whether customers can trust the results.