ETL vs ELT Cage Fight: Combining RudderStack and ClickHouse® to Build Real-Time Data Pipelines – Altinity/RudderStack Joint-Webinar

Recorded: April 20 @ 10:00 am PT
Presenters: Eric Dodds – Head of Product Marketing @RudderStack and Robert Hodges – CEO @ Altinity

In this joint Altinity and RudderStack webinar, Robert Hodges and Eric Dodds argue that the ETL vs. ELT debate is a false choice: modern pipelines need both, applied at the right stage. RudderStack handles in-flight transformations before data lands, while ClickHouse handles at-rest transformations inside the database using materialized views, and the two products are designed to work together.

Eric opens with RudderStack’s architecture: a warehouse-native customer data platform that collects real-time event streams from web, mobile, and server SDKs and routes them to destinations including ClickHouse, with no data stored on RudderStack’s own servers. He walks through RudderStack’s streaming transformation feature, which lets teams write JavaScript or Python to modify payloads in-flight, enforce PII policies per destination, enrich events with external API calls, and fix field mapping errors in minutes rather than re-instrumenting a website.

Robert follows with a ClickHouse performance demo on a trillion-row dataset and an explanation of the Null table engine plus the materialized view pattern: raw JSON events land in a non-storing input table, a materialized view fires on each arriving block and converts, cleans, and compresses the data into an optimized target table. The combined pipeline was set up in under 90 minutes using RudderStack Cloud and Altinity.Cloud, demonstrates end-to-end data movement from a webhook source through credit-card zeroing in RudderStack to automatic schema creation in ClickHouse. The session closes with Eric covering reverse ETL: taking enriched data computed in ClickHouse, such as first-time purchaser flags or lifetime value segments, and pushing it back out to marketing and advertising tools.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:12 – Introduction: Robert Hodges and Eric Dodds
• 1:36 – Speaker introductions: Robert Hodges (Altinity CEO) and Eric Dodds (RudderStack)
• 2:22 – RudderStack overview: warehouse-native CDP, event streaming, ETL, reverse ETL
• 3:18 – Altinity overview: enterprise ClickHouse provider, Altinity.Cloud, Kubernetes Operator
• 4:07 – ETL vs. ELT: the debate and why the answer is “use both”
• 5:23 – Where transformation should happen: before load or in the database
• 7:04 – The path from data to enlightenment: funnel analysis use case
• 8:19 – Types of data transformations: cleaning, enrichment, privacy, aggregation
• 10:13 – ETL vs. ELT side-by-side comparison
• 11:02 – How RudderStack works: SDK collection, real-time event streaming
• 13:22 – RudderStack customer examples and architecture overview
• 14:54 – RudderStack architecture: no data store, pipelines only
• 16:14 – RudderStack streaming transformations: fixing field mapping in minutes
• 19:33 – Use cases for RudderStack Transformations: shipping faster
• 20:04 – Enriching events with external APIs (reverse geolocation example)
• 21:18 – PII security with Transformations: hashing, masking, encryption per destination
• 22:27 – Customization and flexibility: destination-specific payloads, cost control
• 23:17 – ClickHouse introduction: open-source, SQL, analytic capabilities
• 25:13 – Demo: trillion-row ClickHouse dataset on Altinity.Cloud
• 27:40 – Demo: querying max temperature, and the effect of materialized views (17ms)
• 29:43 – Loading JSON data into ClickHouse: 50+ supported formats
• 30:04 – Null table engine + materialized view pattern for on-the-fly ELT
• 32:16 – Security transformations in ClickHouse: hashing, zeroing, AES encryption
• 33:36 – Joins in materialized views: enriching arriving data with dimension tables
• 34:32 – RudderStack + ClickHouse integration demo
• 38:20 – Live events view in RudderStack UI
• 39:12 – Transformation code: zeroing credit card numbers
• 39:45 – Auto-schema creation in ClickHouse: tracks table and product_sale table
• 41:28 – End-to-end result: credit card number zeroed in ClickHouse
• 41:54 – Schema management and moving to production
• 43:54 – Summary: which transformations belong where
• 44:18 – Reverse ETL: sending enriched ClickHouse data back to marketing tools
• 48:00 – Reverse ETL: audiences, advertising destinations
• 49:39 – Why reverse ETL matters for data warehouses
• 51:21 – Summary and final recommendations
• 54:22 – Open source and cloud approach of both companies

Webinar Transcript

[0:12] — Introduction and Housekeeping

Robert: Hello, everyone, and welcome to ETL versus ELT cage fight: using RudderStack and ClickHouse to build real-time data pipelines. My name is Robert Hodges and I’m here today with Eric Dodds from RudderStack.

Before we dive in, let me tell you a little about this webinar. It is being recorded and we will send a link to the recording as well as the slides to everyone who registered within the next 24 hours, so you don’t need to take frantic notes. You can also ask questions. We have plenty of time for them. Put your questions in the Q&A box on the Zoom menu bar or in the chat. If questions are relevant to what we’re talking about, we may take them on the slide, otherwise, we’ll queue them up and answer them at the end.

Eric, how does the sound check?

Eric: Sounds good from my end. How do I sound?

Robert: You sound great. Let’s go.

[1:36] — Speaker Introductions

Robert: My name is Robert Hodges. I’m a database geek. If you can see my video, you can see the books from 30 or 40 years of working on databases behind me. I’ve worked on everything from pre-relational systems to analytic databases. My day job is I’m CEO of Altinity: I run the business but I’m also pretty deeply involved in database technology.

Eric: Hi, I’m Eric Dodds and I’m the Head of Product Marketing at RudderStack. I’ve been building data stacks for about 10 years, primarily dealing with customer data for use cases around marketing attribution, advanced analytics, and so on. Very excited to chat today.

[2:22] — RudderStack Overview

Eric: RudderStack is a warehouse-native customer data platform. We provide tools that make it easy for data teams to collect their data, unify it in a central store, and then activate that customer data once you’ve unified and modeled it. Practically, what you get is real-time event streaming, ETL pipelines that can pull batch data in from your cloud tools, reverse ETL pipelines that pull data back out of the data store, streaming transformations, and some neat things like automating deterministic ID resolution.

[3:18] — Altinity Overview

Robert: I’d like to introduce Altinity. We’re an enterprise ClickHouse provider. We have a unique value proposition: we allow you to run ClickHouse anywhere. You could be in the cloud, on Kubernetes, or on-prem. We run Altinity.Cloud, which was the first cloud offering for ClickHouse on both Amazon and GCP. We’re also the designers of the Altinity Kubernetes Operator for ClickHouse, and we have well over 100 on-prem customers doing all kinds of things with ClickHouse, from embedding it in software appliances to running clusters with hundreds of nodes.

[4:07] — ETL vs. ELT: The Debate

Robert: Let’s talk about ETL versus ELT. I’ll let Eric set the stage.

Eric: This is a topic a lot of people are familiar with, but when you boil it down it’s really a question of where transformation is happening in the data pipeline flow. You’re collecting data from a source, moving it through a pipeline, and delivering it to some final destination, which is very often a data store. The age-old question is: do you transform the data on the way there (ETL), or do you load it first and then transform it inside the data store (ELT)?

Originally, ETL was dominant due to limitations of the downstream destination. Systems had very particular needs, so you’d transform the data to fit before loading it. Today we live in a world where a lot of data transformation can happen in the data store itself, with more flexible schemas and data types. Many data teams also want to keep an original copy of their data. So the question of where to run transformation is genuinely open.

The preview of the answer we’re going to give today is: you should really do both. ETL, where you transform before loading, is really useful for things like cleaning structured data, enrichment, and privacy. ELT is great when you want an original copy and want to do modeling and transformations in the target system, particularly for semi-structured or unstructured data.

[7:04] — The Path from Data to Enlightenment

Robert: Let’s talk about a specific example. For anybody running a digitally focused business, e-commerce for example, you’re very focused on what’s happening on your website: users visiting, what they’re doing there. You’ll typically collect that information, put it in an analytic database, and use it to do what’s called funnel analysis. People arrive at your site at the top of the funnel, and you want them to convert: buy something or take some other action. Funnel analysis tracks their path from different pages toward that desired goal. This is a fundamental operation that virtually every website does, and it’s enabled by ETL or ELT processes moving data into the data warehouse, where it becomes available to analytic tools.

One of the key things Eric pointed out is the transformations that occur along the way. The general types include: cleaning (normalizing addresses, IP addresses, meeting interface requirements so downstream systems don’t deal with dirty or misshapen data), privacy and security, enrichment, and all the way down to aggregation, where you’re summarizing data to allow very quick insight. To go from data on the website to something useful to an analyst, you need to do some of these transformations, sometimes all of them.

[10:13] — ETL vs. ELT Side-by-Side

Robert: In the traditional ETL approach, we collect events from the source, transform the data in flight, and put the fully formed, clean, securitized data into the database for tools to operate on. In the ELT approach, which began to arise a couple of decades ago, you collect the data, load it straight into the database, and use SQL to transform it there. This caused a surprising amount of controversy. I have never personally seen anybody get into a fist fight about this, but you get some pretty serious arguments. Vendors have often contributed to the debate by pushing one or the other.

The real answer: you should use both, in the appropriate way.

[11:02] — How RudderStack Works

Eric: The main initial use case for RudderStack is collecting streaming event data, primarily from your website, mobile application, or server-side software. The main type of data we collect is user behavior: someone browsing a website, clicking on a product, submitting a form, making a purchase. RudderStack provides SDKs for web, mobile, server-side, and other platforms that make it easy to instrument and collect real-time event data with a standardized schema.

The standardized schema does a couple of things. First, it makes it easy to send the data to any other tool in your stack. We have 200-plus out-of-the-box integrations so you can send it to a CRM, marketing automation tool, or a downstream data store like ClickHouse. Because all the events have a standardized schema, it’s easy to perform analytics on them because the data is flattened in the warehouse the same way every time.

[13:22] — RudderStack Architecture: No Data Store

Eric: One really significant thing about RudderStack’s architecture is that we don’t store any data. Our opinion as a company is that we want to be the pipelines that help you collect clean data, make sure it’s trustworthy, and deliver it where it needs to go. Your source of truth should be your own data store.

We have powerful features that allow you to transform data in flight, and because you use your data store as the source of truth we provide pipelines that allow you to pull data back out of that data store and send it to the rest of your stack. A quick example: you collect website events, build a model around whether certain browsing behavior leads to a purchase, and push that data back out of the data store into your marketing tool so that if someone exhibits that behavior you can segment them and send them a promotion to help convert.

[16:14] — RudderStack Streaming Transformations

Eric: Let’s say your marketing team changes a field in Salesforce and the form submits from your website are no longer mapping correctly. Leads aren’t getting delivered, which always creates a massive fire drill. Traditionally you’d have to go back and re-instrument the website, redeploy, and wait to fix it.

With RudderStack Transformations you can modify the incoming payload in-flight. Someone raises an alarm, you go in and just update the key in the payload that’s causing the failure, and all the payloads going to that destination will now have the correct field name. You fix it in a matter of minutes by writing a couple of lines of JavaScript or Python, rather than having to re-instrument the website. You can also hit external APIs to enrich payloads from within a transformation.

[19:33] — RudderStack Transformation Use Cases

Eric: Our customers use transformations in three main categories. First, shipping data projects faster: instead of having to re-instrument your website or app to achieve some downstream use case, you can write the transformation once. A good example is reverse geolocation: collect an event that includes an IP address by default, hit a reverse geolocation API from within a simple JavaScript transformation, and append the human-readable location to the payload. That use case would usually take weeks or months and a ton of development tickets. Data teams can ship it in hours.

Second, securing data and building trust. Privacy and compliance is critical. With RudderStack Transformations you can enforce different policies on a destination-by-destination basis. You might want to hash PII going to a data science data store but retain it in plaintext for a destination that legitimately needs it. You can strip PII, mask it, block events entirely, or encrypt it. It’s almost a suite of tools for customizing compliance per destination.

Third, customization and flexibility: mapping to different destination schemas, filtering or sampling events to control costs, sending to webhook destinations with completely custom payloads. Those are the main use cases for RudderStack Transformations.

[23:17] — ClickHouse Introduction

Robert: For many people, ClickHouse is something new. It’s an analytic database that is open source, and a way to think about it is that it’s a lot like the open-source databases you’ve been using, like MySQL or PostgreSQL, in that it understands SQL, runs virtually anywhere, and has very few license restrictions. You can use it in practically any way you want and people have built enormous systems on it.

Where it differs is in the analytic capabilities: columnar storage with very high compression, parallel and vectorized execution so that queries that might take hours on MySQL will complete in seconds or less in ClickHouse, and the ability to scale to enormous data volumes. We have customers running hundreds of nodes with databases in the tens of petabytes. As a result it’s become a very popular low-latency query engine for SaaS analytics and many other applications where people need cost-efficient, very fast, stable response.

[25:13] — Demo: Trillion-Row ClickHouse Dataset

Robert: Let me prove this rather than just talk about it.

I have a database set up in Altinity.Cloud containing a trillion rows. Let’s count them. There’s a trillion rows plus sixteen thousand six hundred and eighty. That came back essentially instantly. What’s more interesting is asking a real question that requires reading some data. This is temperature sensor data. I’m going to scan it to find the maximum temperature recorded by device number 2555.

There it is: we had to read 1.56 million rows but got done in four hundredths of a second. If we run it again there’s some cache heating and it goes down a little further. That’s pretty fast.

Let’s ask a more complex question: what was the day that we actually hit that maximum temperature?

This query takes a bit longer, about three quarters of a second, because we’re scanning more information. In ClickHouse, if you run a query like this frequently you might regard that performance as unsatisfactory. Fortunately there’s an answer.

[27:40] — Demo: Materialized Views in Action

Robert: The answer is to put the data in what’s called a materialized view. I’ll explain what they are in a minute, but let me show the effect first. This view pre-aggregates the maximum temperature and the day it occurred. If I ask the view the same question, I get an answer back in 17 milliseconds. If I run it again because of cache heating effects it goes down to less than a hundredth of a second.

This is the kind of thing that can give you a response back well under the time it takes to render a page, which means your applications can be asking the data warehouse about information that’s then populated directly onto the page. This isn’t just fast, it’s the kind of thing that enables new businesses.

[29:43] — Loading JSON Data and the Null Table Engine Pattern

Robert: When loading data, events will typically show up as JSON. ClickHouse can read JSON data. It supports about 50 or 60 formats, from CSV to Parquet and ORC. You simply insert the data, and ClickHouse pulls the values out of JSON and puts them in the right columns. That’s pretty simple.

But when loading large amounts of data we may want to do arbitrary transformations on it for performance, cost efficiency, and other reasons. So we take advantage of the ability to read raw data very quickly and put it into an input table using what’s called a Null table engine. We don’t store the data in that table, but we have a materialized view that watches it. Every time a block of data is placed into this input table, the materialized view fires a query over that block and sticks the results into another table. This allows on-the-fly conversion as each block of data arrives, small blocks or large blocks running into millions of rows. As a result, loading data is as simple as having this input table where we read the raw event, with a target table that has a bunch of optimizations: specialized codecs that can reduce data by up to 99 percent before compression. The materialized view itself is just a SQL query that picks values out of the event and converts them to the right data types, handles dirty dates, and so on.

We’ll even keep a copy of that original event data as a raw JSON string in the target table, just in case we need to go back and pull more fields out of it later.

You can also do security transformations in this same materialized view: zero out Social Security numbers, hash email addresses, even encrypt things using AES encryption so the data is transformed on the fly to something highly secure. Moreover, as the data arrives we can join it with data from other tables to supplement the information and add denormalized dimension data, making later queries that much more efficient because the supplementary data needed to make sense of these temperature readings is already on the records. This is, as I mentioned before, where the ETL versus materialized views and projections distinction gets interesting: you can build what you might even call mini data pipelines inside the database that run extremely fast across millions and millions of rows.

[34:32] — RudderStack + ClickHouse Integration Demo

Robert: Let’s show how to use RudderStack and ClickHouse together. I have a simple demo that takes the use case we showed at the beginning, reading event data off a website and pushing it into the analytic database. Inside RudderStack Cloud I define a source, in this case a webhook, which is basically a URL where I can post event data that gets automatically moved to a destination. The destination is a ClickHouse instance. The only transformation I apply is zeroing out credit card information, and the result gets placed into ClickHouse. I put this together in about 90 minutes with no prior background in RudderStack.

Here we have my simple data pipeline: Sales Data is the name of my webhook, and ClickHouse is the destination.

One of the cool things RudderStack allows is watching live events as they pass through. Let me fire up the demo and generate some events. Here’s a simple script posting three events representative of product sales. You’ll notice they even have a dummy credit card number in there, which we’ll show RudderStack can remove.

Here are the events being posted into RudderStack at one-second intervals. You can see them appearing live in the UI. This is a really cool feature: you can watch live events in real time, diagnose problems, and assure yourself that data is being transformed correctly. For example, I posted a sales message and RudderStack supplemented it with additional information that allows it to trace back to the source and understand the event type.

Let’s look at the destination and the transformation. Here you go: a very simple piece of code. It says if you see a ccnum property on this event, go ahead and zero it out. It could be as complex as I want it.

Now let’s synchronize, which is where the data is actually applied to ClickHouse. One really key thing that RudderStack does is automatically create tables corresponding to your events. You don’t have to do anything. They just show up. Two key tables get generated: one is tracks, a list of all events of any type that have arrived, and the other is product_sale with the actual event data.

Looking at the data loaded from RudderStack into ClickHouse, the credit card number has been successfully zeroed out. This demo shows the end-to-end movement: running the database itself in Altinity.Cloud meant I didn’t have to set that up. RudderStack Cloud created the tables automatically. The whole thing, from nothing to a working data pipeline, took about half a day.

[41:54] — Schema Management and Moving to Production

Robert: One of the cool things as I mentioned is that RudderStack will look at arriving events, and if the table is missing it will create it automatically. If additional columns show up in messages they’ll be added automatically as well.

As you move to a production system, one thing you do with ClickHouse is apply transformations for compression codecs, data types, and similar optimizations. You can take this in two directions: go in and change the product_sale table directly to add your own schema, or you can build a materialized view that takes the data coming in from RudderStack and applies these transformations as I showed, then sticks the results into a final table where your apps run from. This gives you very efficient disk storage and very high performance. That’s one way to take this demo and move it onto a production footing.

Eric: Robert, that was a wonderful demo. In 90 minutes you were able to get a simple pipeline set up. Keep in mind that our SDKs handle a lot of stuff for you: if you install the SDK on your website or app we collect a lot of context and information. And if you wanted to send that sales data to an email marketing tool or CRM, you simply add a destination. We have hundreds of different destinations. Think of us as infrastructure that removes the need to run an event stream pipeline, build an SDK, or build integrations. We’re really good at that stuff, so you can get the right data to where it needs to go.

Robert: And on the other hand, we run Altinity.Cloud and remove the pain out of running these very large databases.

[42:57] — Which Transformations Belong Where

Robert: Just to summarize: the cage fight question, ETL versus ELT, you can still argue about it if you want, but it’s not necessary. The best thing is to use these tools together. They fit hand in glove. You’ll need both for real systems and you should use the transformations in each part of the system that are appropriate.

RudderStack has a rich set of tools to move and convert data in-flight, and the ability to detect interesting things happening in the data warehouse and propagate them back out to applications that need them. ClickHouse has an enormously rich set of functions and a comprehensive SQL language that allow you to do very powerful conversions on data at rest. We showed you the principle of materialized views, but your imagination is really the only limiting factor. ClickHouse can do practically anything inside SQL.

Specifically: if I were building this pipeline, cleaning data and ensuring a consistent interface with no junk in it, that’s something I’d definitely do in RudderStack. It has a general-purpose programming language and built-in transformations for that. I would not do cleaning in ClickHouse. On the flip side, when it comes to aggregating data, ClickHouse was born to do this. Finding the maximum temperature on what day for a particular sensor: that’s what ClickHouse is designed for. Everything else in the middle, paid your money and take your choice. Often they both work fine and there’s not much difference in the finished system.

[44:18] — Reverse ETL: Sending Enriched Data Back Out

Eric: Reverse ETL is kind of a funny name for this pipeline. Those of us who work with data every day would just call it sending enriched data out of your data store to the tools that need it.

Let’s think about the product sale data that Robert talked about. You have a bunch of different purchases. When you send that data to ClickHouse you’re going to run complex analytics on it to understand things like user lifetime value. You might group users into a segment of first-time purchasers: someone who visited for the first time today and made a purchase. You may want to engage them to make sure they come back. All those metrics are computed in ClickHouse, and how you move the needle is by getting that data back into your marketing tool so the marketing team can send them a thank-you email with a coupon to keep them engaged.

That’s exactly what reverse ETL does. Within RudderStack, you can take a table, often a materialized view or an output table from some computation in ClickHouse, and it will turn each row into a payload that it sends to downstream destinations. For a first-time purchaser there’s one row per user in that table, and you send that updated user record to your marketing tool with a flag for “first-time purchaser” that drives segmentation.

You can also create pipelines by writing SQL in RudderStack: write a simple join to produce some additional table that combines data from two tables in ClickHouse and send each row as an individual payload. We also have a feature called audiences: build an audience of users who haven’t made a purchase in the last 30 days and send that audience to an advertising tool like Google Ads or Meta to serve them a re-engagement promotion.

The punch line is that you can do powerful enrichments and computations in ClickHouse through Altinity and then syndicate that value to the rest of your stack in the business tools that need it to improve customer experience and business performance.

[49:39] — Why Reverse ETL Matters for Data Warehouses

Robert: Reverse ETL capability is really interesting because it’s something data warehouses don’t do natively very well, especially when you need to send notifications to a diversity of applications. The most ClickHouse can do in this area natively is pop something into a Kafka queue or show windows on arriving data. By combining ClickHouse with RudderStack’s reverse ETL, you get a much richer set of capabilities for delivering data as it changes in the data warehouse to the people who actually need it.

[51:21] — Summary

Robert: So: the ETL versus ELT cage fight, you can still do it if you want, but it’s not necessary. The best thing is to use these tools together. They fit hand in glove. You’re going to need both for real systems, and you should use the transformations in each part of the system that are appropriate to it.

If you want to get off the ground quickly, use RudderStack Cloud and Altinity.Cloud together. You can bring up your database quickly without starting pipelines from scratch. The built-in features of RudderStack allow you to build a chunk of your application with very little effort in a very small amount of time.

[54:22] — Open Source and Closing

Eric: If you are putting a lot of manual labor or low-value engineering effort into things like customer data pipelines, SDKs, streaming events, Integrations work, or difficult-to-scale and expensive pipelines, we can make your life way way easier by getting clean standardized data not only to a data store like ClickHouse but also to the rest of your stack. Stop by rudderstack.com. You can sign up for free and use all the features just like Robert did.

Robert: The reason you should visit us at Altinity is pretty similar. Anybody can use ClickHouse. It downloads on a laptop in about 60 seconds. But if you find that you need help using it effectively to build applications that are highly performant, we can do that. We also run Altinity.Cloud, which is designed for SaaS applications delivering real-time analytics 24/7. We can manage it both in our own cloud and in your Kubernetes clusters, and we have the ability to support you in your own self-managed environments. And we have the best ClickHouse support team on the planet. They are just wonderful people who can smooth out that learning curve and allow your teams to pivot away from the ClickHouse application once it’s running and still have somebody covering the infrastructure for you.

Most importantly, both of us build on and contribute to 100 percent open-source technology. What we’re automating for you is open-source ClickHouse, and our goal is to make ClickHouse as open as possible and give you as much freedom as possible. That’s essential if you’re building systems that need to be robust and continue operating as you navigate new business problems and economic conditions.

Eric: Thank you for having us.

Robert: Thank you everybody. It’s been a pleasure. If you have further questions feel free to contact us at Altinity or Eric’s team at RudderStack. Thank you very much and have a great day.

FAQ

What is the difference between ETL and ELT, and which one should you use?

ETL (Extract, Transform, Load) transforms data before it reaches the data store, which is ideal for cleaning, enrichment, and PII masking that benefit from a general-purpose programming language and destination-specific rules. ELT (Extract, Load, Transform) loads raw data first and transforms it inside the data store using SQL, which is ideal for aggregation, modeling, and semi-structured data where keeping an original copy is valuable. The correct answer for most real pipelines is to use both: apply in-flight transformations for cleaning and security, and use the data store’s own capabilities for aggregation and modeling.

What is a RudderStack streaming transformation and when should I use one?

A streaming transformation is a JavaScript or Python function that modifies event payloads in-flight, before they reach any destination. Use them to fix field mapping mismatches without re-instrumenting your website, enrich events with external API data such as reverse geolocation, enforce PII policies like hashing or stripping sensitive fields on a per-destination basis, filter or sample events for cost control, or customize payloads for destinations where no native integration exists. Changes take effect in minutes without a redeployment.

What is the Null table engine plus materialized view pattern in ClickHouse?

This pattern uses a Null table engine as an input stage: data is inserted into it but not stored, making it a trigger rather than a data store. A materialized view attached to that table fires on every arriving block and runs a SQL query over the block, converting types, applying codecs, masking PII, joining with dimension tables, or doing any other transformation. The results go into an optimized target table. This allows on-the-fly ELT transformations inside ClickHouse at full ingest speed, without a separate transformation service, while still optionally preserving a copy of the original raw event for future reprocessing.

How quickly can you set up a RudderStack to ClickHouse pipeline?

With RudderStack Cloud and Altinity.Cloud, Robert Hodges set up a working end-to-end pipeline including a webhook event source, a PII transformation to zero out credit card numbers, and automatic schema creation in ClickHouse in about 90 minutes with no prior RudderStack experience. RudderStack automatically creates tables in ClickHouse corresponding to your event types, so no manual schema definition is needed to get started.

What is reverse ETL and how does it work with ClickHouse?

Reverse ETL takes data computed inside a data warehouse and pushes it back out to operational tools like CRMs, marketing automation platforms, and advertising networks. For example, you might compute a first-time purchaser flag or a customer lifetime value segment in ClickHouse, then use RudderStack’s reverse ETL to turn each row in that output table into a payload and deliver it to a marketing tool, which uses it to trigger personalized campaigns. RudderStack also supports SQL-based pipelines that join tables in ClickHouse before sending, and audience sync to advertising platforms.

What kinds of security transformations can ClickHouse perform natively?

ClickHouse can apply security transformations on arriving data using materialized views. These include zeroing out specific fields such as Social Security numbers, hashing email addresses or other identifiers, and encrypting data using AES encryption so that the values stored in the target table are already transformed before any downstream tool can access them. These transformations can be applied selectively by column and run at full ingest speed without any external transformation service.

© 2023 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.