Data Distiller Query Optimization
12 Jul 2026 » Platform
I was recently working with a customer. They were planning to launch a marketing campaign and one of the steps involved writing a query in Data Distiller. As I watched our data architect provide some specific recommendations, I realized that there was a gap in the knowledge of the data engineers of this customer. This is not the first time I have seen avoidable issues with queries, so I decided to explain some tricks to keep in mind with Data Distiller.
Data Lake vs RDBMS
Although the Adobe Experience Platform (AEP) Data Lake has a SQL interface, it is far from being a Relational DataBase Management System (RDBMS). The technologies behind each are completely different. The fact that you can use a PostgreSQL client to connect to the Data Lake does not make it a PostgreSQL database.
There are two fundamental differences: file structure and data manipulation.
File structure
Each RDBMS engine has its own file structure to store the data: SQLite uses a single file for everything, MariaDB spreads the data over multiple files, and Oracle requires its own partition or block device.
In the case of AEP, you will have already noticed that XDM is based on JSON. Dataset batches in AEP are stored as JSON files. Think of each of them as a JSON array containing multiple rows of data. This applies to both profile and event datasets.
Data manipulation
RDBMS engines support various operations to manipulate data: INSERT, UPDATE or DELETE. When you issue a DELETE, the RDBMS may delete the data from disk, but it is more likely that it just sets a hidden flag to mark the row as deleted, so that it is ignored in subsequent queries. An UPDATE may modify the existing value on disk or create a new row with the new data, marking the previous row as deleted; in both cases, the old data becomes unavailable.
The AEP Data Lake only supports INSERT operations. Once a profile fragment or event is written into a dataset, it is never modified or deleted. The only way to delete data from the Data Lake is to delete a batch or the whole dataset, or when a data retention policy rule determines that some data has to be deleted. In other words, all data that you send to AEP keeps piling up in the Data Lake.
Do not get confused with the Real-Time Customer Profile (RTCP). The RTCP only contains the last version of the profile and the events that have not expired due to TTL. So, when you send a new profile fragment, RTCP will delete the previous fragment and only keep the last one, while the Data Lake will keep all versions.
Querying the Data Lake
I am sure you know where I am going with this, but I want to make sure we are all on the same page. When I run SELECT * FROM my_fancy_dataset, the Query Service will process all rows in the dataset from the beginning of time. Think about it: if you have web events from a website with heavy traffic from the last 2 years, Query Service will process billions of rows. And if you think that you are safe with querying the profile snapshot, you should remember that the dataset has the profile snapshots from the past too.
No wonder that the query that ran smoothly a year ago, when you had just finished your AEP’s implementation, is dragging its feet now. The slowdown is exponential and you are consuming compute hours like crazy.
Query Optimization
I hope that, by now, you are convinced that all queries that need to run regularly must be optimized. This should not be an afterthought; your query should be optimized from the beginning, even if it is not needed initially. Modifying a query in production is a risky and expensive operation. In the long term, it is cheaper to write the query thinking about the future from the get-go.
Here you have a few techniques to optimize the execution of queries.
The WHERE clause
As simple as it seems, you should use time-based WHERE clauses wherever possible. If you are querying the profile snapshot, you only need last day’s data. If you are processing events, I am sure that the events from 2 years ago are not relevant anymore.
Derived tables
Some datasets are huge by nature. My best example is the journey_step_events dataset, which has every step of every journey for all profiles in journeys. Another example could be the web event dataset of a heavy-traffic website, as mentioned above.
If you are going to run complex queries based on this huge dataset, but do not need all the data in it, my strong recommendation is to create a derived dataset using a Create Table As Select (CTAS) query. In this initial query, you only select the rows and the columns that you really need. Then, you run your complex query on this derived dataset.
The SNAPSHOT clause
This is probably the most important feature for query optimization, and the most important section of this post. Data Distiller offers the SNAPSHOT clause to tell the query engine to only process rows between two markers. It is explained in great detail in Experience League: Incremental load in Query Service. However, I am sure that by just looking at the title of that page many will have skipped it, and this is why I am highlighting it here.
I want to clarify that this feature only makes sense if you are scheduling a query to run regularly (every hour, every day, every week…). You do not need it if you are just running a query once or need to process all data in the dataset.
The idea is fairly simple, although the code may look complex. You start by creating and initializing an auxiliary table to keep track of the last marker that has been processed. Then, with every execution:
- Get the previous marker from the auxiliary table.
- Get the last marker in the dataset.
- Run your query between these two markers.
- Insert the last marker into the auxiliary table for the next run.
Since this post is already getting too long, and the SNAPSHOT clause is so important, I will devote my next post to this feature. Stay tuned!
Finally, by no means am I an expert in Data Distiller. If you feel like I am missing an optimization trick, let me know in the comments.