• Dynamic Data Masking
• Row-level Security using Secure View
• Row Access Policies etc.

Problem with Traditional Data Masking Approach:

Let’s take a simple Column-Level Masking Policy as an example. The policy is applied directly to a specific column in a table and determines how the data is displayed based on the accessing user’s role. For instance, a column containing Social Security Numbers(SSNs) could be fully visible to an administrator while being partially masked for a support analyst.

While this works well at an individual table level, it becomes difficult to scale when the same policies need to be configured and maintained manually across hundreds or thousands of tables.

The real goal should be to move from table-level configuration to centralized, data-driven governance. Governance teams should be able to define the rules once, classify sensitive data, and have the appropriate protection applied automatically across the entire data estate as data is created, ingested, and classified.

Solution:

Tag-Based Masking addresses this scalability challenge by decoupling data protection policies from individual tables and columns. Instead of manually applying a masking policy to every column, governance teams can define a policy once and associate it with a tag. From that point onwards, whenever a column is assigned the corresponding tag, Snowflake automatically applies the designated masking policy.

For example, consider all columns across your Snowflake account that contain phone numbers. By classifying them with a tag such as PII = 'Phone Number' and associating a masking policy with that tag, Snowflake automatically enforces the defined masking rules across all matching columns. This also applies to newly created columns as they get tagged, without requiring governance teams to configure the policy again for every table.

That’s what makes this approach scalable: define the governance rules once, classify data consistently, and let protection follow the data across the entire account —

Steps we used in implementing Tag Based Masking:

The major steps involved in implementing Tag-Based Masking in Snowflake are:

1. Create Tags: Define tags to classify sensitive data, such as PII_EMAIL, PII_PHONE, PII_NAME, PII_ADDRESS, etc.
2. Associate Masking Policies with Tags: Assign appropriate masking policies to each tag to define how sensitive data should be displayed. For example, completely mask the data (*****) or partially mask it by displaying only the first four characters/digits followed by asterisks (1234****).
3. Apply Tags to Columns: Apply the relevant tags to table columns containing sensitive information. For example, PII_EMAIL to the EMAIL column, PII_PHONE to PHONE_NUMBER, and PII_NAME to EMPLOYEE_NAME.
4. Generate Data: Create and populate tables with data containing confidential information, such as employee names, email addresses, phone numbers, and salaries.
5. Securely Consume the Data: Users can view sensitive information only if they have the required permissions, while other users see masked values as configured in step 2

Separation of Duties:

Separation of duties is a design principle where more than one person’s approval or involvement is required to conclude a critical task, and it is an important part of the Well-Architected Framework —

As we move towards implementing Tag-Based Masking to make data privacy scalable across the Data Platform, maintaining a clear separation of duties becomes equally important.

For implementing this Data Governance Feature in Snowflake, we can split the responsibilities across different roles so that no single team has complete control over the entire process.

Let’s understand the roles first which gets involved in different steps in this implementation & then in the next step, we will understand their responsibilities —

• Data Governance Committee or Council: The Data Governance Council is a cross-functional group of senior stakeholders responsible for defining the organization's data governance strategy, establishing key principles, and ensuring their effective implementation across the organization. Typically this Council include representatives typically from 1–2 levels below the C-suite member.

Note: Nothing astonishing, but now a days Data Governance a C-Suite Priority, in a well organized team, we have Chief Data Officer role reporting to the CEO & a cross-functional Data Governance Council — with integrated governance metrics into corporate scorecards and executive evaluations and allocate a dedicated budget as a strategic investment

• Data Steward: Let me explain the importance of a Data Steward through a simple scenario. Imagine you are working on a regulatory report and need a specific piece of data. You search for it but cannot find a proper definition or any information about who owns it. You start reaching out to different people across the organization, hoping someone can help. One person directs you to another, who then suggests contacting someone else. Nobody seems sure about the meaning of the data, its quality, or who is responsible for it. After spending days trying to get an answer, you may eventually use whatever information is available and submit the report without being fully confident in its accuracy. This situation highlights why every important data domain needs a clearly identified person who takes responsibility for its definition, quality, and proper use. This person is known as a Data Steward. A Data Steward acts as a point of contact for business users, helps them understand the data, clarifies its meaning, and works with relevant teams to maintain its quality and consistency.

• Platform Enablement Team: Let’s first understand how platform teams differ from other technical teams. Application development teams build products and internal applications for business units. Platform teams, on the other hand, build and maintain foundational platforms that these teams can use to develop and deliver their products for example, standard process for reusable CI/CD, Cloud Infrastructure Setup using Terraform etc. or defining the way-of-working. In simple terms, Application development teams are the customers of platform teams. Unlike application development teams, platform teams are typically centralized and support multiple Application development teams across different business units.
• Data Producer: They produce the data kind off like Data Product Developer, typically Data Engineers or Analytics Engineer.
• Data Consumer: A data consumer is anyone who uses the data to perform their work or make decisions. This could include business users, dashboard developers, ML engineers, and other data users —

Responsibility Segregation:

Now, let’s look at how the different steps involved in Tag-Based Masking can be divided among the roles discussed above —

• Define the Tag Taxonomy: Before implementing Tag-Based Masking, the organisation must decide which tags are required to classify data and what values each tag can have. For example, a sensitivity tag could have values such as public, internal, confidential, and restricted, while a pii tag could have values such as email, phone_number, and ssn. This step should be owned by the Data Governance Council, as it requires organization-wide agreement on data classification standards. Having the Council define these tags and allowed values ensures consistency across teams and prevents different teams from introducing their own naming conventions or classifications.
• Identify Data Assets to be tagged: Once the tag taxonomy is defined, the next step is to identify which tags should be applied to which data assets. This includes specifying the tables and columns that need to be tagged and the appropriate tag values. The Data Steward plays an important role here by understanding the data and identifying, for example, that a particular column contains an SSN and should therefore be tagged as pii : ssn. The steward can also provide the necessary context on how sensitive data should be handled, such as whether the column should be masked and what level of masking is appropriate like full masking or partial masking etc.
• Creating Masking Policy: Platform Enablement Team can step in this step to create various masking policies & adding them in Tags like —

CREATE MASKING POLICY account_name_mask_tag_string
AS (val string) RETURNS string ->
  CASE
 WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('tags.accounting_col_string') = 'visible' THEN val
 ELSE '***MASKED***'
  END;

CREATE MASKING POLICY account_number_mask_tag_string
AS (val number) RETURNS number ->
  CASE
 WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('tags.accounting_col_string') = 'visible' THEN val
 ELSE -1
  END;

ALTER TAG pii_classifier SET
  MASKING POLICY string_mask,
  MASKING POLICY number_mask;

• Applying Tags: The Data Product Developer, based on the inputs and guidance provided by the Data Steward, can identify the relevant columns and apply the required tags using simple ALTER commands —

ALTER TABLE DEMO_DB.PUBLIC.customer_details 
    MODIFY COLUMN credit_score SET TAG pii_classifier = 'pii_number';

This way the important steps for Tag Based Masking can be segregated into different roles —

That’s all for this blog! For more such interesting and practical content, follow me on YouTube, LinkedIn, Medium & Instagram!

Separation of Duties in Snowflake Tag Based Masking was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

I’ve been working in Pune, India since April 2024, and unfortunately, I missed celebrating Durga Puja in Kolkata in both 2024 & 2025. Work commitments made it difficult to take enough time off and travel home. My situation was pretty much like this —

It was only Pune’s Khadki Kali Bari , which was my little escape during Durga Puja. Whenever I missed the celebrations back home in Kolkata, I would visit the pandal to catch a glimpse of Ma Durga, soak in whatever festive vibes I could, and find a little peace of mind. ❤️

It wasn’t Kolkata, of course, but it gave me a small sense of connection to the celebrations I was missing back home —

Even this year, I wasn’t really planning to travel to Kolkata for Durga Puja, mainly because of the workload and all the PI-related activities at work. But then, one day, I paused and asked myself a simple question: What’s the point of working so hard if I can’t even make time to celebrate the festivals that matter to me with my family?

That thought was enough. Without overthinking it any further, I went ahead and booked my flight tickets from Pune to Kolkata✈️❤️

4th October I booked the flight —

And on 5th October, I had the flight —

Sometimes, you just need to remind yourself that work will always be there, but moments with family and the joy of celebrating together are something else entirely!

And ya, I never miss to have Geela bhel from Ganesh Bhel in Pune Airport —

Family Surprise:

I landed in Kolkata at around 2:25 AM, and the best part? Neither my mom nor my dad had any idea that I was coming home! 😄

By 3:05 AM, I had reached home from the airport via Rapido. I called my mom and casually asked, “Hello, Ma, have you gone to sleep?” Hearing me call at such an unusual hour, she sounded worried and asked if everything was okay with me. I could sense the tension in her voice, so I simply said, “Ma, can you open the door?”

And then came the moment I’ll never forget! ❤️

The moment she realised I was standing outside, she was literally shaking with excitement, shouting, “You came home! You came home!” 😂❤️ My dad woke up too, and together they rushed to open the door.

Seeing their faces, their happiness, and almost-tears-of-joy expressions made the entire journey worth it. There is something truly special about surprising your parents like this. No grand plans, no announcements — just showing up at their doorstep and watching their faces light up just before the festival.

That moment alone was worth every bit of the effort it took to come home❤️

Mahalaya:

I had planned to celebrate Mahalaya in our village in Burdwan with my grandma. Mahalaya holds a special place in Bengali culture as it marks the end of Pitri Paksha, the fortnight dedicated to remembering our ancestors, and the beginning of Devi Paksha, heralding the arrival of Maa Durga. For me, celebrating this special occasion with my grandma in our village would make it even more meaningful. And guess what? I had another surprise planned!!

My father had called my grandma beforehand and told her that he would be visiting, so she could prepare lunch for one extra person. But little did she know that I would be going instead of my father!

When I showed up, her happiness was priceless. She was just as excited as my parents had been when I surprised them at home —

Sometimes, the best gifts aren’t things you buy — they’re the unexpected moments you get to spend with the people you love.

Here is a glimpse of my Mahalaya Journey —

Meanwhile, across West Bengal, the excitement was building as Maa Durga began her journey from Kumartuli, i.e. potters’ locality, to puja committees, societies, and pandals across the state. The countdown to the biggest festival of the year had officially begun! 🙏

Maha Panchami:

While Mahalaya marks the formal invitation to the Goddess Durga, the actual cultural celebration, pandal hopping, and public festivities in Bengal kick off in full swing on Maha Panchami.

The pandal marks the 150th anniversary of the national song Vande Mataram, written by Bankim Chandra Chattopadhyay in 1875 to inspire the freedom movement —

Also we visited Muhammad Ali Park & College Square Durga Puja which is famous for its massive, sparkling central jhumar —

After a long time, I ate Bhel Puri & Patty with my mom there :-)

By Panchami, Maa Durga had arrived at our society pandal too🙏

Maha Shashti:

Our Association shared the Puja and Pushpanjali schedule in advance, which helped us plan each day better and make the most of the celebrations —

I started the day with a 6 km run(couldn’t skip my daily dose of fitness! 🏃‍♂️) —

After getting back and taking a bath, I headed straight to the Durga Puja pandal to offer my first Pushpanjali of the year —

There’s a different kind of peace in standing before Maa Durga, offering flowers with folded hands, and feeling the festive energy all around you. The wait was finally over — Durga Puja was here! 🙏❤️

At noon, I went with my family to Sovabazar Rajbari(one of Kolkata’s oldest and most prestigious traditional aristocratic family celebrations) to have the traditional Durga Puja bhog prasad —

Here is a glimpse of the same —

At evening, I enjoyed a wonderful lineup of cultural performances, including dancing, singing, and Shruti Natak(a form of audio drama).

And to wrap up the day? A large cup of tea and some pizza! ☕🍕

Maha Saptami:

Started the day with a 7 KM run as it was Saptami(7th Day of Durga Puja) —

After taking a bath and putting on a new Panjabi, I headed to the puja mandap to witness Maa Durga’s beautiful Aarti and offer my Pushpanjali. There’s something special about dressing up in traditional attire and beginning the day with Maa’s blessings — a feeling that’s hard to put into words —

Something special happened after the Pushpanjali! ❤️ One of the puja committee members approached me and asked if I could help distribute khichuri bhog to around 280 people. I was more than happy to help!

Along with the other volunteers, I joined in distributing the bhog to everyone. It felt wonderful to be part of something so meaningful — not just celebrating the festival, but also serving others and sharing the blessings of Maa Durga —

In the evening, my youngest maternal aunt(Mashi) came over with her family, making the festive evening even more special —

We wrapped up the day with some comfort food, ordering momos from Wow Momo, pizza & Garlic Bread from Domino’s —

Maha Ashtami:

Maha Ashtami is one of the most significant days of Durga Puja, when devotees worship Maa Durga in her powerful and fierce form. ❤️🙏

And this year, we had an extra reason to celebrate! With Maha Ashtami festivities spread across two days as part of an 11-day Navaratri calendar, the celebrations lasted a little longer, giving us even more time to soak in the festive spirit and enjoy every moment with our loved ones.

As usual, started my day with 8 KM run as it was 8th day of Durga Puja —

Day was going pretty much same, after taking a bath and putting on a new Panjabi, I headed to the puja mandap to witness Maa Durga’s beautiful Aarti and offer my Pushpanjali —

The best part of the day was that my Chotodidi came over to celebrate Durga Ashtami with us! She joined us for lunch and had the bhog prasad at our society pandal, making the occasion even more special —

She also brought along a book that my Mama(her son), who lives in California, had sent for me —

Sandhi pujo:

Sandhi Pujo is the most sacred and spiritually powerful ritual of Durga Puja, performed during the exact 48-minute transition window between Maha Ashtami and Maha Navami.

During this juncture or the ‘Sandhikhan’ , Durga is worshipped in her Chamunda form. Devi Durga killed, Chando and Mundo, the two asuras at ‘Sandhikhan’ and thus acquired the name of ‘Chamunda’ at this point in time.

As officially this day also falling under Ashtami, so I had to run 8 KM, but this day, instead of running on treadmill, I ran in our society & here are my Strava stats —

After freshening up, I headed back to the puja mandap to offer my Sandhi Puja Anjali. 🙏❤️ Offering anjali during this auspicious moment made the day even more special.

After the puja, I joined the others in distributing bhog prasad to the devotees —

In the evening, we visited our Chotodidi’s home, where we spent some lovely time together, catching up, sharing conversations, and enjoying some delicious food —

Maha Navami

Though on Maha Navami, the beats of the dhak still bring happiness, but somewhere in our hearts, we know there’s only one day left :-/

As usual, my day started with 9 KM run as it was the ninth day of Navaratri —

After the morning run, I took bath & went to mandap for Pushpanjali & Aroti & to distribute bhog..

Navami havan is a central ritual — Ghee, wood, grains, and sweet offerings (bhog) are fed into the sacred fire while chanting Vedic and Puranic mantras. Devotees use bilva(bel) leaves — typically a 108 leaves for Goddess Durga —

In the evening, we had an open-floor dance session in our society, when Bollywood’s iconic Durandar entry meets the Garba circle🔥

Bijoya Dashami

The heavy, bittersweet feeling of sadness on Bijoya Dashami is a shared emotion for every Bengali as Maa Durga leaves her maternal home to return to Kailash.

Started my day with 10 KM run because it’s 10th day —

After Pushpanjali, Ma’s Bisorjon began, followed by Mayer Boron(Devi Boron), the traditional farewell ritual where we bid Ma Durga goodbye on Bijoya Dashami —

Then there was Sindur Khela, where married women celebrate by applying sindur to one another, marking the end of the festivities with joy, emotions, and prayers for Ma’s return next year —

Conclusion:

In the evening, we went to a nearby pond to witness Ma Durga’s Protima Niranjan —

The local municipality had arranged a dedicated spot for the immersion, with plenty of police personnel and volunteers managing the crowd. All the immersions were taking place at a single designated location to help prevent water pollution at multiple sites.

And then we had sweet-sharing custom, where the sadness of the Goddess’s departure is countered by spreading sweetness, joy, and goodwill among family, friends, and the community.

The next day, with Navaratri finally over, I enjoyed fish again after many days of staying away from non-vegetarian food!

And with this, the biggest celebration of the year came to an end. We returned to our daily routines —

But we were carrying all the beautiful memories with us and already started counting the days until Ma Durga returns next year! ❤️

For this kind of incremental data processing, MERGE INTO is often the first option that comes to mind.

MERGE INTO statement combines data from one or more source tables with a target table, allowing you to perform updates and inserts in a single operation. Based on conditions you define, it determines whether to update existing rows or insert new ones in the target table. This makes it more efficient than using separate INSERT, UPDATE, and DELETE statements.

Depending on the join conditions, the engine may choose different join strategies behind the scenes. I’ve covered this in detail in this blog. While the examples focus on Snowflake, the underlying concepts are relevant to Databricks as well.

In this blog, we’ll look at a specific scenario where a custom INSERT INTO statement can be preferable to a MERGE INTO statement in Databricks: insert-only merges.

Let’s dig in:

We’ll start with two tables: a large target table and a relatively small source table. This reflects a common MERGE INTO scenario, where the target table is significantly larger than the source —

-- Large target: 1 BILLION rows
CREATE OR REPLACE TABLE large_target_benchmark
USING DELTA
AS
SELECT
    id AS transaction_id,
    MOD(id, 10000000) AS customer_id,
    RAND() * 1000 AS amount,
    CURRENT_DATE() AS created_date
FROM RANGE(1000000000);


-- Small source: 100K rows
-- First 50K will match existing records
-- Next 50K will be new records
CREATE OR REPLACE TABLE small_source_benchmark
USING DELTA
AS
SELECT
    id AS transaction_id,
    MOD(id, 10000000) AS customer_id,
    RAND() * 5000 AS amount,
    CURRENT_DATE() AS created_date
FROM RANGE(999950000, 1000050000);

Now we are going to execute a simple insert-only merge statement in it —

MERGE INTO large_target_benchmark AS tgt
USING small_source_benchmark AS src
ON tgt.transaction_id = src.transaction_id
WHEN NOT MATCHED THEN
    INSERT *;

Before checking the query profile, let’s recall how an insert-only MERGE INTO works — it performs a Left Anti Join, with the source table on the left and the target table on the right, to identify rows that exist in the source but have no matching records in the target. These are the rows that need to be inserted —

As we can see in the query profile above, the source table appears on the left and the larger target table on the right. A Left Anti Join identifies the source rows that don’t exist in the target, and the resulting rows are then written to the target table.

Now, let’s look at this from a query optimization perspective — when the source table is very small compared to the target table, a Broadcast Join is often worth considering & we would broadcast the smaller source table to avoid shuffling the much larger target table.

But there’s a catch — an insert-only MERGE INTO uses a Left Anti Join, with the source table on the left and the target table on the right. As we discussed earlier, Spark’s broadcast join strategy does not support broadcasting the left side of a Left Join. This means we can’t simply broadcast the smaller source table in this case — i.e. though we have a clear scope of optimization!

And if we click on Join step, we can confirm that, it’s Hash join where the Hash Table is built on Source Table as it’s smaller —

Now, let’s take a moment to think about whether we can achieve the same output while leveraging the power of Broadcast Join for better performance.

Can we use Broadcast Join with a Left Anti Join? Absolutely! The key is to make the right-side dataset significantly smaller than the left-side dataset, because Spark can broadcast the right side of the join in left join case.

So, the idea is simple: keep the larger dataset on the left and the smaller dataset on the right to take advantage of broadcast optimization during Left Anti Join & that we can easily achieve using below approach —

Step 1: Identify the intersection or matching pks between the source and target tables — Use an Inner Join to extract the records common to both tables. Since an Inner Join supports broadcasting either side, Databricks can broadcast the source table as mostly it is sufficiently smaller that target table in general —

WITH matching AS (
    SELECT src.transaction_id
    FROM large_target_benchmark AS tgt
    INNER JOIN small_source_benchmark AS src
        ON tgt.transaction_id = src.transaction_id
)

Step 2: Perform a Left Anti Join between the source and the intersection data —

Use the source table as the left side and the intersection dataset as the right side. Since the intersection is generally smaller than the complete source dataset, it becomes a potential candidate for broadcasting. Moreover, the intersection pks sits on the right side of the Left join, aligning with the broadcast join requirement—

SELECT
    src.*
FROM small_source_benchmark AS src
LEFT ANTI JOIN matching
    ON src.transaction_id = matching.transaction_id;

This way, we can potentially leverage Broadcast Join in both cases for optimization while preserving the left-side records needed for the subsequent processing —

WITH matching AS (
    SELECT src.transaction_id
    FROM large_target_benchmark AS tgt
    INNER JOIN small_source_benchmark AS src
        ON tgt.transaction_id = src.transaction_id
)
SELECT
    src.*
FROM small_source_benchmark AS src
LEFT ANTI JOIN matching
    ON src.transaction_id = matching.transaction_id;

Step 3: Insert those records in target table —

INSERT INTO large_target_benchmark
WITH matching AS (
    SELECT src.transaction_id
    FROM large_target_benchmark AS tgt
    INNER JOIN small_source_benchmark AS src
        ON tgt.transaction_id = src.transaction_id
)
SELECT
    src.*
FROM small_source_benchmark AS src
LEFT ANTI JOIN matching
    ON src.transaction_id = matching.transaction_id;

As you can see in the Query Profile above, Databricks is leveraging Broadcast Joins in both join steps.

To validate that our custom INSERT approach produces the same result as the standard MERGE statement, we can run a simple experiment: clone the initial tables, apply the MERGE approach to one set and the custom INSERT approach to the other, and finally compare the results to confirm that both approaches produce identical data —

-- Large target: 1 BILLION rows
CREATE OR REPLACE TABLE large_target_benchmark
USING DELTA
AS
SELECT
    id AS transaction_id,
    MOD(id, 10000000) AS customer_id,
    RAND() * 1000 AS amount,
    CURRENT_DATE() AS created_date
FROM RANGE(1000000000);


-- Small source: 100K rows
-- First 50K will match existing records
-- Next 50K will be new records
CREATE OR REPLACE TABLE small_source_benchmark
USING DELTA
AS
SELECT
    id AS transaction_id,
    MOD(id, 10000000) AS customer_id,
    RAND() * 5000 AS amount,
    CURRENT_DATE() AS created_date
FROM RANGE(999950000, 1000050000);

-- Deep clone large target table
CREATE OR REPLACE TABLE large_target_benchmark_2
DEEP CLONE large_target_benchmark;

-- Deep clone small source table
CREATE OR REPLACE TABLE small_source_benchmark_2
DEEP CLONE small_source_benchmark;

MERGE INTO large_target_benchmark AS tgt
USING small_source_benchmark AS src
ON tgt.transaction_id = src.transaction_id
WHEN NOT MATCHED THEN
    INSERT *;


INSERT INTO large_target_benchmark_2
WITH matching AS (
    SELECT src.transaction_id
    FROM large_target_benchmark_2 AS tgt
    INNER JOIN small_source_benchmark_2 AS src
        ON tgt.transaction_id = src.transaction_id
)
SELECT
    src.*
FROM small_source_benchmark_2 AS src
LEFT ANTI JOIN matching
    ON src.transaction_id = matching.transaction_id;

(
    SELECT * FROM large_target_benchmark_2
    EXCEPT
    SELECT * FROM large_target_benchmark
)
UNION
(
    SELECT * FROM large_target_benchmark
    EXCEPT
    SELECT * FROM large_target_benchmark_2
);

Since the comparison query returns no rows, we can conclude that both approaches produce the same final target state.

This confirms that our custom INSERT approach is functionally equivalent to the insert-only MERGE for this test case, while leveraging Broadcast Joins to potentially improve performance and make it more optimized than the standard MERGE approach!

Conclusion:

So the moral of the story is — When a MERGE operation only inserts records that don’t already exist in the target, an alternative join-based INSERT strategy can sometimes deliver better performance by taking advantage of Broadcast Joins.

That’s all for this blog! For more such interesting and practical content, follow me on YouTube, LinkedIn, Medium & Instagram!

When you should prefer INSERT INTO over MERGE INTO! was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

Predicate Pushdown?

At a high level, Predicate Pushdown is about applying filters as close to the data source as possible instead of waiting until the data has already travelled through the query execution pipeline.

The reasoning is pretty straightforward:

If we already know that a row isn’t needed, why spend resources processing it?

Filtering data early can reduce the number of rows that need to be shuffled, processed, or kept in memory, which ultimately helps improve both performance and resource utilization.

Consider a simple query:

SELECT col1
FROM tab1
WHERE location = 'New York';

A naive execution strategy could look something like this:

1. Read the data from tab1.
2. Bring the rows into memory.
3. Evaluate the WHERE condition and discard rows that aren't from New York.
4. Return col1 for the remaining rows.

In other words, the system essentially loads the data first and filters it afterward. While this approach is perfectly valid, it can become expensive when the underlying table contains millions or even billions of records —

In the example query above, it would be more efficient to tell the table-scanning code not to load records that don’t match the WHERE clause. This doesn’t save filtering time(every row’s location must still be read once), but the biggest advantage is that fewer rows have to make their way through the rest of the execution plan. That can mean less memory consumption, less data to shuffle, and less work for downstream operators.

Sounds great, right?

Well, not always!! 👀

Pushdown is a powerful optimization, but it isn’t something we should blindly assume will happen in every query. Certain operations can prevent a predicate from being pushed further down the execution plan, and in some cases, the position of the filter can make a surprisingly significant difference.

And that’s where things start getting interesting. Let’s check that practically first —

Practical Implementation:

Step 1: Create a fresh Database for P.O.C. —

create database demotest;

use demotest;

Step 2: Create a table for experimentation —

CREATE OR REPLACE TABLE employees (
    department_id INT,
    employee_id INT,
    employee_name STRING,
    salary INT
);

INSERT INTO employees VALUES
(10, 101, 'Alice', 60000),
(10, 102, 'Bob', 70000),
(10, 103, 'Charlie', 80000),
(20, 201, 'David', 50000),
(20, 202, 'Eva', 55000),
(20, 203, 'Frank', 90000);

Step 3: Now, let’s test this intuition with a simple example:

SELECT
    department_id,
    max(salary) AS salaries
FROM employees
GROUP BY department_id
HAVING max(salary) > 60000;

At first glance, this query looks like a perfect candidate for predicate pushdown.

Think about what the query is doing. We first perform a GROUP BY on department_id and calculate the maximum salary for each department. Only after that do we apply the HAVING condition.

But GROUP BY can be an expensive operation, particularly when we're dealing with a large dataset, as it involves sorting or hashing, and potentially shuffling a significant amount of data across the compute nodes.

So, intuitively, wouldn’t it be better if Snowflake could reduce the amount of data before performing the aggregation?

For example, if a row has a salary of less than or equal to60,000, it can never be responsible for making MAX(salary) > 60,000.

That naturally leads us to think:

Why process those rows through the GROUP BY at all?

If Snowflake could push the predicate down and eliminate those rows before the aggregation, the execution could conceptually become:

Scan employees
      ↓
Filter salary > 60,000
      ↓
GROUP BY department_id
      ↓
MAX(salary)

Instead of —

Scan employees
      ↓
GROUP BY department_id
      ↓
MAX(salary)
      ↓
HAVING MAX(salary) > 60,000

Let’s check the Query Profile for the above query in Snowflake —

As we can see, Snowflake has pushed the filter of salary>60K towards the source!

So far so good; now let’s try out another aggregation query, for example, array_agg & observe whether the filter is also getting pushed down towards the source or not —

SELECT
    department_id,
    ARRAY_AGG(salary) AS salaries
FROM employees
GROUP BY department_id
HAVING ARRAY_SIZE(ARRAY_AGG(salary)) > 2;

From the above query profile, we can understand that this time the filter is not pushed down towards the source!

Reason: The array_agg function is non-deterministic. A nondeterministic function in SQL is a function that can return different results even when called multiple times with the exact same input values. For example, if you have ever worked with Snowflake Cortex AI functions, you know it relies on underlying large language models(LLMs) that generate probabilistic text responses rather than fixed rule-based outcomes, so we can call it non-deterministic!

And this is my observation —

Filters for aggregations are not pushed through nondeterministic expressions like array_agg, ANY_VALUE, listagg, etc.

So make sure your filters are positioned in the right place to have optimized runs!

Let’s see what’s going on with Databricks:

First, we will try with a Databricks Serverless Cluster —

CREATE OR REPLACE TABLE employees (
    department_id INT,
    employee_id INT,
    employee_name STRING,
    salary INT
);

INSERT INTO employees VALUES
(10, 101, 'Alice', 60000),
(10, 102, 'Bob', 70000),
(10, 103, 'Charlie', 80000),
(20, 201, 'David', 50000),
(20, 202, 'Eva', 55000),
(20, 203, 'Frank', 90000);


SELECT
    department_id,
    max(salary) AS salaries
FROM employees
GROUP BY department_id
HAVING max(salary) > 60000;

For some reason, while running the code in a Databricks Serverless Cluster, the pushdown, even for a deterministic function, is not working :-/

Even if we run the same query in Databricks Classic Compute —

Even for Classic compute, the filter is happening after the HashAggregate step, so in these kinds of cases, the Databricks Optimizer is not pushing the filter before aggregation. Make sure your filters are positioned in the right place to have optimized runs for all cases when using Databricks!

That’s all for this blog! For more such interesting and practical content, follow me on YouTube, LinkedIn, Medium & Instagram!

Pushdown Optimization: Don’t Take It for Granted was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

Snowflake & Databricks, both have consistently invested in making its query engine smarter, enabling it to execute SQL more efficiently without requiring developers to rewrite their queries. It’s these behind-the-scenes engineering decisions that make the platform remarkably powerful at scale.

Over the past few weeks, I’ve been exploring how both Platform’s Query optimizer transforms SQL behind the scenes. Some of the fascinating optimizations I’ve already covered include —

• Aggregate Pushdown
• Join Optimization using Bloom Filter
• New Filter generation & New Filter on “Other” side of the Join
• Optimizing Not Exists Clause
• Intelligent Join selection depending on Join Clause in Merge statement etc.

In today’s blog, we’ll uncover another subtle yet impressive optimization that Snowflake performs automatically for inefficient Correlated subqueries like below —

SELECT
    ot.a,
    ot.b
FROM ot
WHERE ot.b < (
    SELECT AVG(it.b)
    FROM it
    WHERE it.a = ot.a
);

At first glance, the SQL looks perfectly ordinary. But once we inspect the Query Profile, we’ll discover that Snowflake quietly rewrites the execution strategy into something significantly more efficient.

Let’s start with practical —

First, we’ll create two tables — ot(outer table) and it(inner table)—and populate them with some sample data —

create database sql_query_test;
use sql_query_test;
-- Create tables
CREATE OR REPLACE TABLE ot (
    a STRING,
    b INT
);

CREATE OR REPLACE TABLE it (
    a STRING,
    b INT
);

-- Insert data into ot
INSERT INTO ot (a, b) VALUES
('A', 10),
('A', 30),
('A', 50),
('B', 20),
('B', 40),
('C', 60);

-- Insert data into it
INSERT INTO it (a, b) VALUES
('A', 20),
('A', 40),
('B', 10),
('B', 30),
('B', 50),
('C', 80);

Now, suppose our objective is to retrieve all rows from the outer table (ot) where the value of b is less than the average b value of the corresponding group in the inner table (it). Since the average depends on the current row's value of a, we may opt for correlated subquery —

-- Correlated subquery
SELECT
    ot.a,
    ot.b
FROM ot
WHERE ot.b < (
    SELECT AVG(it.b)
    FROM it
    WHERE it.a = ot.a
);

But here’s the catch…

While the query is logically correct, the naive execution strategy can become expensive.

Conceptually, the database would have to:

1. Fetch a row from the outer table (ot).
2. Execute the correlated subquery using the current value of ot.a.
3. Compare ot.b with the returned average.
4. Return the row if the condition is satisfied.
5. Repeat the same process for every remaining row in the outer table.

In other words, if the outer table contains millions of rows, it may appear that the correlated subquery also has to be evaluated millions of times — making correlated subqueries seem inherently expensive.

Now the 1000$ question is —

Does Snowflake really execute the subquery once for every row in the outer table? Or is something much smarter happening behind the scenes?

Let’s inspect the Query Profile —

The first thing that stands out is the complete absence of any row-by-row execution or “for-loop” style processing that we would typically associate with a correlated subquery. Instead, the execution plan is composed of familiar relational operators such as Aggregations, Filters, and Joins.

Let’s intuitively understand what’s going on — the optimization mantra working in this case is: Correlated subquery of above type = Group By + Join

Step 1: Aggregate the inner table

The correlated condition is:

WHERE it.a = ot.a

This tells us that for every value of a in ot, we need the average b from it having the same a.

Rather than calculating that average separately for every row of ot, the optimizer can calculate all of those averages together:

SELECT
    a,
    AVG(b) AS avg_b
FROM it
GROUP BY a;

This is what Snowflake is doing in left side of the above query profile(SQL_QUERY_TEST​.​PUBLIC​.​IT side), the only difference is, instead of calculating avg directly, it’s computing sum and count agregate value from which we can easily calculate the avg whenever needed —

SELECT
a,
SUM(IT.B),
COUNT(IT.B)
FROM it
GROUP BY a;

At this point, we have transformed it into a much smaller relation containing the average b for each value of a.

Step 2: Join the aggregated result with the outer table

We still need to bring this information back to the rows of ot so that we can perform the original comparison:

ot.b < AVG(it.b)

The original correlation condition was:

it.a = ot.a

So we can simply use the same condition as the join key. This associates each row from ot with the corresponding average calculated from it.

The rewritten query now becomes:

SELECT ot.a, ot.b FROM ot JOIN 
( SELECT a, AVG(b) AS avg_b FROM it GROUP BY a ) 
agg ON ot.a = agg.a WHERE ot.b < agg.avg_b;

So the original query which was like:

For each row in OT 
      Find AVG(B) from IT where IT.A = OT.A 
      Compare OT.B with that average

But the relational execution can instead look more like:

If you cross check, both pattern will return same result:

WITH original AS (
    SELECT ot.a, ot.b
    FROM ot
    WHERE ot.b < (
        SELECT AVG(it.b)
        FROM it
        WHERE it.a = ot.a
    )
),
rewritten AS (
    SELECT ot.a, ot.b
    FROM ot
    JOIN (
        SELECT a, AVG(b) AS avg_b
        FROM it
        GROUP BY a
    ) agg
        ON ot.a = agg.a
    WHERE ot.b < agg.avg_b
)

SELECT * FROM original
EXCEPT
SELECT * FROM rewritten

UNION

SELECT * FROM rewritten
EXCEPT
SELECT * FROM original;

In other words, the correlated dependency between ot and it has now been converted into a regular JOIN!

And that is the key idea behind decorrelation!

Let’s check what’s going on with Databricks:

-- Create tables
CREATE OR REPLACE TABLE ot (
    a STRING,
    b INT
);

CREATE OR REPLACE TABLE it (
    a STRING,
    b INT
);

-- Insert data into ot
INSERT INTO ot (a, b) VALUES
('A', 10),
('A', 30),
('A', 50),
('B', 20),
('B', 40),
('C', 60);

-- Insert data into it
INSERT INTO it (a, b) VALUES
('A', 20),
('A', 40),
('B', 10),
('B', 30),
('B', 50),
('C', 80);


SELECT
    ot.a,
    ot.b
FROM ot
WHERE ot.b < (
    SELECT AVG(it.b)
    FROM it
    WHERE it.a = ot.a
);

As we can see, Databricks also convert this unoptimised Correlated Subquery to Group By & Inner join! The only difference with Snowflake is, Databricks perform 2 aggregations when we execute any group by operation(and there is an interesting story behind that, which I have covered this this blog)

Conclusion:

Correlated subqueries can become increasingly expensive as data volumes grow, especially when they appear to require repeated execution. But as we saw, Snowflake or Databricks can optimize certain correlated subqueries by transforming them into efficient relational operations such as Aggregation + Join.

So, before rewriting a complex query, it’s worth checking the Snowflake or Databricks’ Query Profile to see what is actually happening under the hood.

The SQL may look correlated, but the execution doesn’t necessarily have to be.

That’s all for this blog! For more such interesting and practical contents, follow me on YouTube, LinkedIn, Medium & Instagram!

This Correlated Subquery SQL Optimization in Snowflake & Databricks has blown up my mind!! was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

Without the MERGE command, you needed to test the new dataset against the existing dataset using a business key. When that didn’t match, you inserted new rows in the existing dataset; otherwise, you updated existing dataset rows with new dataset values.

The MERGE command conditionally merges rows from a source table into a target table. Traditionally, this could only be achieved by using multiple insert, update, or delete statements separately. When using multiple statements to update or insert data, there is a risk of inconsistencies between the different operations. Merge operation reduces this risk by ensuring that all operations are performed together in a single transaction —

In general, a Merge statement typically looks like —

MERGE INTO {target_table}
USING {source_table}
ON {join_expr}
WHEN MATCHED THEN UPDATE 
WHEN NOT MATCHED THEN INSERT;

Now, in this blog, we won’t dive deep into the syntax or the basic working of the MERGE statement, assuming we’re already familiar with those. Instead, I want to explore an interesting observation I recently came across while digging into the Query Profile of a MERGE statement in Snowflake.

Let’s start with creating dummy source & target table —

create or replace DATABASE DEMO_TEST;
use demo_test;
use schema public;

CREATE OR REPLACE TABLE source_employee (
    employee_id NUMBER,
    employee_name VARCHAR,
    department VARCHAR,
    salary NUMBER,
    status VARCHAR
);
INSERT INTO source_employee VALUES
(1, 'John',  'IT',      75000, 'ACTIVE'), 
(2, 'Alice', 'HR',      65000, 'ACTIVE'), 
(5, 'Emma',  'Marketing', 60000, 'ACTIVE'),
(4, 'David', 'IT',      75000, 'DELETED');


CREATE OR REPLACE TABLE target_employee (
    employee_id NUMBER,
    employee_name VARCHAR,
    department VARCHAR,
    salary NUMBER,
    status VARCHAR
);
INSERT INTO target_employee VALUES
(1, 'John',  'IT',    70000, 'ACTIVE'),
(2, 'Alice', 'HR',    65000, 'ACTIVE'),
(3, 'Bob',   'Finance', 80000, 'ACTIVE'),
(4, 'David', 'IT',    75000, 'ACTIVE');

From the above dataset, we can clearly understand that if we apply the MERGE statement, Emma (ID 5) will be inserted because she exists in the source but not in the target, John’s salary will be updated from 70,000 to 75,000, Alice and Bob will remain unchanged as their records already match, and David’s status will change from ACTIVE to DELETED.

Let’s execute the merge statement now, but different scenarios 1-by-1:

Insert Only merge:

MERGE INTO target_employee t
USING source_employee s
ON t.employee_id = s.employee_id

WHEN NOT MATCHED THEN
    INSERT (
        employee_id,
        employee_name,
        department,
        salary,
        status
    )
    VALUES (
        s.employee_id,
        s.employee_name,
        s.department,
        s.salary,
        s.status
    );

As expected from the data, only 1 row is impacted!

Now let’s intuitively think how this might have been done for a sec…

So we want to insert those rows which are present in source table but not in target table, kind off this kind off venn diagram(if you consider Table A as source Table & Table B as target table)—

And this is nothing but venn diagram of Left Anti Join! Now if you refer the Query Profile, you will observe, Snowflake is doing exactly the same thing —

Update Only merge:

Now, let’s start with a simple UPDATE-only MERGE statement. Here, we will update the target table only when a matching employee_id is found in the source table —

MERGE INTO target_employee t
USING source_employee s
ON t.employee_id = s.employee_id

WHEN MATCHED THEN
    UPDATE SET
        t.employee_name = s.employee_name,
        t.department    = s.department,
        t.salary        = s.salary,
        t.status        = s.status;

Now, let’s intuitively think about how this might have been implemented in the backend for a moment..

We want to update only those rows that are present in both the source and target tables. If we represent the source as Table A and the target as Table B, we are essentially interested in the intersection of the two tables —

And this is nothing but venn diagram of Inner Join!!

So, intuitively, we might expect Snowflake to perform an INNER JOIN between the source and target tables behind the scenes. But is that actually what Snowflake does?

Let’s look at the Query Profile and find out —

And here our intuition is matching with Snowflake Query Profile, it’s doing inner join for Update Only Merge!

Both Insert & Update merge:

Now, let’s execute the MERGE statement with both INSERT and UPDATE —

MERGE INTO target_employee t
USING source_employee s
ON t.employee_id = s.employee_id

WHEN MATCHED THEN
    UPDATE SET
        t.employee_name = s.employee_name,
        t.department    = s.department,
        t.salary        = s.salary,
        t.status        = s.status

WHEN NOT MATCHED THEN
    INSERT (
        employee_id,
        employee_name,
        department,
        salary,
        status
    )
    VALUES (
        s.employee_id,
        s.employee_name,
        s.department,
        s.salary,
        s.status
    );

Let’s think about what we actually need here. For the UPDATE clause, we need the rows that are present in both the source and target. For the INSERT clause, we need the rows that are present in the source but not in the target. If we visualize this using a Venn diagram, we essentially need the entire Source table, including both the matching rows and the source-only rows —

And intuitively, this looks very similar to what a LEFT OUTER JOIN would produce, with the source table on the left.

So, could Snowflake be using a LEFT OUTER JOIN internally to execute this MERGE?

Let’s look at the Query Profile and find out whether Snowflake actually does the same thing —

And yes, this time as well, our intuition matches Snowflake’s actual execution! The Query Profile shows that Snowflake is indeed using a LEFT OUTER JOIN, exactly as we anticipated!

Conclusion

From this POC, one thing is clear: although we are executing the same MERGE statement, the join strategy used at the backend can vary depending on the clauses and conditions we specify.

The bigger question is: why does Snowflake do this? One possible reason is that different join strategies can have very different execution costs. An ANTI JOIN is generally much cheaper than a LEFT OUTER JOIN because it only needs to determine whether a matching row exists in the other table and can discard the matched rows, rather than carrying them forward. Similarly, an INNER JOIN can be cheaper than a LEFT OUTER JOIN because it only needs to retain matching rows, whereas a LEFT OUTER JOIN must preserve all rows from the left side, including those that have no match on the right side.

So, even though a LEFT OUTER JOIN could theoretically handle all these scenarios, Snowflake doesn’t necessarily use it everywhere. Instead, it can choose a more targeted join strategy based on what the MERGE actually requires—potentially making the execution more efficient!

That’s all for this blog! For more such interesting and practical contents, follow me on YouTube, LinkedIn, Medium & Instagram!

The Hidden Joins Behind a Snowflake MERGE command was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

Subquery operators

A subquery is a query within another query as simple as that! An example can be like, if I have to find out all those employee who’s salary is greater than average salary of all employees across the organization, I can write a query like below —

SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

How It Works —

• Inner Query First: The database runs the query inside the parentheses first, like in this case, first it calculate the average salary of all employees.
• Outer Query Next: The main query uses the result of the inner query i.e. avg_salary from inner query to finish its job i.e. in this case to filter all records where salary > avg_salary!

EXISTS condition

EXISTS conditions test for the existence of rows in a subquery, and return true if a subquery returns at least one row. If NOT is specified(i.e. Not Exists clause), the condition returns true if a subquery returns no rows.

For example, let’s say I have 2 tables — employees, which stores all employee records, and terminated_employees, which stores the emp_id of employees who have left the company —

CREATE OR REPLACE TABLE employees (
    emp_id INT,
    emp_name STRING
);

INSERT INTO employees VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eva');

CREATE OR REPLACE TABLE terminated_employees (
    emp_id INT
);

INSERT INTO terminated_employees VALUES
(2),
(4),
(4);

If I want to fetch only the employees who are currently active(i.e., not terminated), I can use a NOT EXISTS clause —

SELECT
    e.emp_id,
    e.emp_name
FROM employees e
WHERE NOT EXISTS (
    SELECT t.emp_id
    FROM terminated_employees t
    WHERE e.emp_id = t.emp_id
);

Now, this is an usecase of Correlated Subquery as well where the inner query is evaluated once for every row returned by the outer query!

The performance challenge:

Traditional execution of correlated subqueries follows this pattern:

1. Fetch one row from the outer query(i.e. employees table)
2. Execute the subquery with correlated values fetched from step 1
3. Return the subquery result
4. Repeat for the next outer row

So, if table employees has 1 million rows, the subquery needs to be evaluated 1 million times😢

And this kind off Row-by-row evaluation breaks parallel, distributed execution paradigm as well in a way, so we can clearly understand this will have a performance impacts!

Subquery transformation in Snowflake

Instead of running the same subquery repeatedly for each row in your outer table, Snowflake converts this into equivalent joins & then apply parallel, distributed execution(I have already covered how Join works in Distributed System in this blog)

For example, in above case, we are trying to find out the employees available in set “employees” that are not in set “terminated_employees” which is a typical “Anti Join” use-case —

Let’s see the query profile by clicking on Query ID in Snowflake —

The Query Profile looks like this —

Observe in the Query Profile, there is no NOT EXISTS operation available, rather, there is a Join step, click on that & you will able to see ANTI join is performed between 2 tables instead of our written clause —

Note: One aspect of the Snowflake Query Profile that can be a bit confusing is identifying which input represents the left and right side of the ANTI Join, as the visual layout doesn’t explicitly label them.

However, we can infer this from the flow of rows. If Snowflake is performing a LEFT ANTI JOIN, then the Employees table(which appears on the right side of the Query Profile) must actually be the left input, while Terminated_Employees is the right input.

The row counts make this evident:

• The Employees table outputs 5 rows(Employees table has 5 rows, we can confirm from our initial table creation query).
• During the LEFT ANTI JOIN, 2 matching rows(employees found in terminated_employees) are eliminated.
• The join therefore returns the remaining 3 rows, which are employees that do not exist in the terminated_employees table.

Although the visual placement in the Query Profile may suggest otherwise, the row flow clearly indicates that Snowflake is treating Employees as the left side of the anti join and Terminated_Employees as the right side.

So overall we can conclude, Snowflake optimizes the correlated subquery having Not Exists clause with ANTI Join!

Another important optimization what we can observe is there is an aggregate step in the terminated_employees side, let’s intuitively try to understand what’s going on there —

Although it is called Aggregate, it is not calculating any SUM, COUNT, or AVG. Instead, it is simply removing duplicate EMP_IDs from the TERMINATED_EMPLOYEES table.

In this example:

• The table scan reads 3 rows.
• Two of those rows have the same EMP_ID.
• The Aggregate step keeps only the 2 unique EMP_IDs.

As a result, the LEFT ANTI JOIN only has to compare 2 unique employee IDs instead of 3 rows. Fewer rows entering the join means less work for the join operator, making the execution more efficient.

So, before performing the LEFT ANTI JOIN, Snowflake first reduces the number of rows that need to participate in the join and hence the aggregate step which is doing distinct operation on ANTI JOIN Key!

Two Cents for the Snowflake Database Engineering Team 💙

• If Snowflake is already smart enough to execute NOT EXISTS as an ANTI JOIN under the hood, why not expose LEFT ANTI JOIN as a first-class SQL construct as well?

• One more small request regarding the Query Profile: it would be incredibly helpful if it explicitly displayed the type of ANTI JOIN being executed (e.g., LEFT ANTI JOIN) and clearly identified which input is the left table and which is the right table. Today, we have to infer this from the row flow, which isn’t always obvious.

These small enhancements would make the Query Profile much easier to interpret, especially for engineers trying to understand how Snowflake optimizes and executes SQL queries.

Let’s see how Databricks execute this

CREATE OR REPLACE TABLE employees (
    emp_id INT,
    emp_name STRING
);

INSERT INTO employees VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eva');

CREATE OR REPLACE TABLE terminated_employees (
    emp_id INT
);

INSERT INTO terminated_employees VALUES
(2),
(4),
(4);

SELECT
    e.emp_id,
    e.emp_name
FROM employees e
WHERE NOT EXISTS (
    SELECT t.emp_id
    FROM terminated_employees t
    WHERE e.emp_id = t.emp_id
);

The Query Profile in Databricks looks like this:

As we can clearly see, Databricks also executes the query as a LEFT ANTI JOIN instead of evaluating the NOT EXISTS clause directly, just like Snowflake.

One notable difference, however, is that Databricks makes the execution plan much easier to interpret. It explicitly labels the join as a Left Anti Join and clearly places the Employees table on the left and Terminated_Employees table on the right —

This makes it immediately obvious which table is the left input and which is the right input — something that was a bit confusing in Snowflake’s Query Profile.

On the other hand, notice one interesting optimization that Snowflake performs but Databricks doesn’t appear to —

Before executing the LEFT ANTI JOIN, Snowflake applies a DISTINCT operation on the join keys from the right-hand table. This is a smart optimization because, for an anti join, it doesn’t matter whether a key appears once or multiple times in the right table. As long as a matching key exists, the corresponding row from the left table will be eliminated.

By removing duplicate keys upfront, Snowflake reduces the number of rows flowing into the join operator. This means the join has less data to process, potentially improving execution efficiency without changing the query’s result.

Also it’s worth highlighting, Databricks support Left Anti Join Syntax as well which currently Snowflake don’t —

Conclusion:

In this post, you have seen how the correlated subquery optimizations available in Snowflake & Databricks can significantly improve performance through the technique: Automatic Subquery Transformations

That’s all for this blog! For more such interesting and practical contents, follow me on YouTube, LinkedIn, and Medium!

Snowflake & Databricks both didn’t execute your NOT EXISTS clause!! was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

To keep things simple, let’s assume there are three departments: HR, Marketing, and Technology.

The source system continuously ingesting documents into dedicated folders based on the owning department:

• HR-related documents were ingested into the HR folder.
• Marketing-related documents were ingested into the Marketing folder.
• Technology-related documents were ingested into the Tech folder.

The requirement is to implement a secure file-sharing mechanism where each department can access only its own documents.

For example:

• The HR team should be able to access only the HR folder.
• The Marketing team should be able to access only the Marketing folder.
• The Technology team should be able to access only the Tech folder.

In other words, users must be prevented from viewing or accessing documents belonging to any other department. The solution should enforce this access centrally while remaining scalable as new departments, users, and documents are added over time.

There’s one more important constraint. Since the organization’s vision is to build solutions around Snowflake, the architect prefers a native Snowflake-based approach rather than introducing external services such as SFTP servers etc.

One obvious solution — but not the one we want — is to create a separate Snowflake External Stage for each department folder and grant access to the corresponding role. While this approach works, it quickly becomes difficult to manage. As the number of departments (or folders) grows, you’ll have to continuously create and maintain new External Stages, making the solution operationally cumbersome and far from scalable.

Let’s start with the second most intuitive solution that would naturally come to mind…

Step 1: First thing is, we need external stage to work with Snowflake & S3, so let’s build it(and to play with file-level metadata in better way, let’s opt for Directory Table)—

DROP DATABASE IF EXISTS snowflake_file_sharing_poc;
CREATE Database snowflake_file_sharing_poc;
use snowflake_file_sharing_poc;

create or replace stage snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory url="s3://filesharingsystemdemo/" 
credentials=(aws_key_id='{Put your AWS Access Key ID here}'
aws_secret_key='{Put your AWS Secret Access Key here}')
Directory=(ENABLE=TRUE);

ALTER STAGE snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory REFRESH;

Let’s query the Directory Table —

select * from DIRECTORY(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory);

Step 2: Now, if users need to access the raw unstructured files from Snowflake — whether they are PDFs, text files, images, videos, or any other file type — a common solution is to generate pre-signed URLs—

select RELATIVE_PATH, FILE_URL, GET_PRESIGNED_URL(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory, relative_path, 60) AS presigned_url, LAST_MODIFIED
from DIRECTORY(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory)

Step 3: Now, as we all know, pre-signed URLs have a limited validity period. The requirement is to ensure that this validity period starts when the user executes the query, rather than when the URL is generated beforehand.

For example, if the URL is configured to be valid for 10 minutes and a user queries Snowflake at 5:00 AM, the generated URL should remain valid until 5:10 AM — regardless of when the underlying metadata was ingested.

This is where SQL Views become particularly useful. Since a view is evaluated at query execution time rather than being precomputed, the pre-signed URL can be generated on demand, ensuring that every user receives a fresh URL with the full validity period —

CREATE OR REPLACE  VIEW snowflake_file_sharing_poc.PUBLIC.file_sharing_view AS
select RELATIVE_PATH, FILE_URL, GET_PRESIGNED_URL(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory, relative_path, 60) AS presigned_url, LAST_MODIFIED
from DIRECTORY(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory);

select * from snowflake_file_sharing_poc.PUBLIC.file_sharing_view;

Step 4: At this point, only one challenge remains: fine-grained access control. If we simply grant users access to the view shown above, every user — regardless of whether they belong to the HR, Marketing, or Technology team — will receive pre-signed URLs for all the files stored in S3. Since a pre-signed URL grants direct access to the underlying object, users would effectively be able to access documents belonging to every department, completely bypassing the intended security model.

This is exactly what we want to avoid!

Solution? Simple — Row-Level Access Control using a Secure View 🙂

The idea is actually quite straightforward. We can create dedicated Snowflake roles for each department — for example, HR, MARKETING, and TECH — mirroring the folder structure in the data lake.

Then, instead of exposing all the records through the view, we can leverage a Secure View that dynamically filters the data based on the role of the user executing the query. The filtering logic simply ensures that users only receive records where the department(or folder name) matches their current Snowflake role.

For example, a user with the HR role will only receive rows corresponding to the HR folder, a MARKETING user will only see Marketing documents, and a TECH user will only receive records for the Tech folder. Since the pre-signed URLs are generated only for the rows returned by the Secure View, users can never obtain URLs for documents belonging to other departments —

CREATE OR REPLACE SECURE VIEW snowflake_file_sharing_poc.PUBLIC.file_sharing_view AS
select RELATIVE_PATH, FILE_URL, GET_PRESIGNED_URL(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory, relative_path, 60) AS presigned_url, LAST_MODIFIED
from DIRECTORY(@snowflake_file_sharing_poc.PUBLIC.snwoflake_parent_directory)
where upper(SPLIT_PART(RELATIVE_PATH, '/',  0)) = upper(current_role());

Step 5: With the implementation in place, it’s time to validate the solution..

To simulate a real-world scenario, let’s create three department-specific roles — HR, MARKETING, and TECH — grant them access to the warehouse, database, schema, and the Secure View, and then create one user for each department. This setup allows us to verify that users only receive pre-signed URLs for documents belonging to their own department.

The following script creates the required roles, grants the necessary permissions, and provisions three test users:

create or replace role HR;
create or replace role TECH;
create or replace role MARKETING;

grant usage on warehouse compute_Wh to role HR;
grant usage on warehouse compute_Wh to role TECH;
grant usage on warehouse compute_Wh to role MARKETING;

grant usage on database snowflake_file_sharing_poc to role HR;
grant usage on database snowflake_file_sharing_poc to role TECH;
grant usage on database snowflake_file_sharing_poc to role MARKETING;

grant usage on schema snowflake_file_sharing_poc.public to role HR;
grant usage on schema snowflake_file_sharing_poc.public to role TECH;
grant usage on schema snowflake_file_sharing_poc.public to role MARKETING;

grant select on view snowflake_file_sharing_poc.PUBLIC.file_sharing_view to role HR;
grant select on view snowflake_file_sharing_poc.PUBLIC.file_sharing_view to role TECH;
grant select on view snowflake_file_sharing_poc.PUBLIC.file_sharing_view to role MARKETING;


create or replace user jadu_hr password = '123456';
grant role HR to user jadu_hr;

create or replace user mimo_marketing password = '456789';
grant role MARKETING to user mimo_marketing;

create or replace user jimo_tech password = '147258';
grant role TECH to user jimo_tech;

Step 6: Open the same Snowflake account in an Incognito window(or a different browser) and sign in as the HR user using the credentials below:

Username: jadu_hr
Password: 123456

Execute the below queries:

use role HR;
use warehouse COMPUTE_WH;

Now the HR Employee with username jadu_hr is ready to query our Snowflake Secure view —

select * from snowflake_file_sharing_poc.PUBLIC.file_sharing_view;

Observe the beauty of this approach — because the Secure View applies a row-level filter based on the user’s role, the HR user receives pre-signed URLs only for documents stored in the HR folder.

This gives us true row-level access control. Any number of users can be assigned the HR role, and they will only be able to view or download HR documents through the generated pre-signed URLs. They will never receive URLs for documents belonging to the Marketing or Technology departments, ensuring secure and scalable data sharing.

Another major advantage of this approach is that Secure Views prevent users from inspecting their underlying definition. Even if users have access to query the view, they cannot view its DDL or the SQL logic used to define it. This means there’s no risk of someone discovering — or attempting to bypass — the row-level filtering logic, such as:

WHERE UPPER(SPLIT_PART(RELATIVE_PATH, '/', 0)) = UPPER(CURRENT_ROLE())

Since the Secure View definition is hidden from consumers, the access control logic remains protected, making the solution both secure and governance-friendly —

You can verify the same behavior by logging in with the other two users — mimo_marketing and jimo_tech. In each case, you’ll notice that the Secure View returns only the rows belonging to the user’s respective department, and consequently, only the corresponding pre-signed URLs are generated.

From a scalability perspective, this approach is equally elegant. Once the initial setup is in place, onboarding new users becomes extremely simple —

• If a user needs access to an existing department, the administrator only needs to grant the corresponding Snowflake role.
• Similarly, onboarding a new department requires minimal effort. Simply create a new role with the same name as the department’s folder in the data lake, assign that role to the relevant users, and the existing Secure View logic automatically enforces the correct access control. No changes to the application logic or the view definition are required.

In other words, day-to-day administration is reduced to role management, making the solution easy to operate and highly scalable as the organization grows —

Conclusion

In this blog, we explored how to build a scalable and secure file-sharing solution for unstructured data using native Snowflake capabilities. By combining pre-signed URLs, Secure Views, and role-based access control, we ensured that users can securely access only the documents they are authorized to view, while keeping the solution simple to govern and easy to scale as new users and departments are onboarded.

And if the same files need to be shared across multiple Snowflake accounts, you can simply create a Snowflake Data Share on top of the Secure View, extending the same security model seamlessly across accounts.

That’s all for this blog! For more such interesting and practical contents, follow me on YouTube, LinkedIn, and Medium!

Architecting secure & compliant Unstructured Data(File) Sharing solution in minutes with Snowflake was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

But if you’re reading this blog, I’m pretty sure you already have some doubts about it. So, instead of keeping you in suspense until the end, let’s start with the answer itself.

Now, rather than simply accepting that answer, we’ll try to understand why it’s true. Throughout this blog, we’ll practically explore this with real scenarios, inspect the query execution plans, and build an intuition around whether the order in which we write our JOINs really matters!

Theoretical Intuition:

Let’s say we have three tables:

• Table_A → 50 million rows
• Table_B → 30 million rows
• Table_C → 10 thousand rows

And we’ve written the following query:

Table_A INNER JOIN Table_B INNER JOIN Table_C

So the default query engine will first join Table_A with Table_B, and then join the resulting dataset with Table_C, simply because that’s the order in which the joins are written —

In other words, many developers expect the execution flow to look like this:

(Table_A ⋈ Table_B) ⋈ Table_C

Now, one of the most important factors in query performance is the size of the intermediate data produced during joins.

In our example, the query engine starts by joining Table_A(50 million rows) with Table_B(30 million rows). Since both are large tables, there’s a good chance that a significant number of rows will match. Let’s assume this join produces an intermediate result of around 25 million rows.

Next, this 25 million-row intermediate dataset is joined with Table_C, which contains only 10 thousand rows. After applying the final join condition, suppose the query eventually returns just 50 thousand rows.

So, the execution flow would look something like this:

Table_A (50M)
      ⋈
Table_B (30M)
      │
      ▼
Intermediate Result (25M)
      ⋈
Table_C (10K)
      │
      ▼
Final Output (50K)

Notice that although the final result contains only 50 thousand rows, the engine had to process an intermediate dataset of 25 million rows before reaching that output. Processing, shuffling, and joining such a large intermediate result can significantly increase the query execution time.

Now, let’s consider a different execution plan for the same query —

Instead of joining Table_A with Table_B first, imagine the optimizer decides to join Table_A with Table_C. Since Table_C contains only 10 thousand rows, let’s assume this join produces an intermediate result of just 80 thousand rows because only a small subset of rows from Table_A matches with Table_C!

Next, this 80 thousand-row intermediate dataset is joined with Table_B(30 million rows). As expected, the final output is still 50 thousand rows.

So, the execution flow becomes:

Table_A (50M)
      ⋈
Table_C (10K)
      │
      ▼
Intermediate Result (80K)
      ⋈
Table_B (30M)
      │
      ▼
Final Output (50K)

Notice that the final result is exactly the same, which is expected because we’re executing the same SQL query. However, the amount of intermediate data that the engine needs to process has been reduced dramatically — from 25 million rows to just 80 thousand rows.

This reduction can have a significant impact on performance. Since one side of the second join is now very small, the optimizer may even choose a Broadcast Join instead of a more expensive Sort Merge Join or Shuffle Hash Join. A Broadcast Join avoids shuffling the large table across the cluster by broadcasting the smaller dataset to all executors, which can substantially reduce network I/O and overall query execution time.

This is precisely why modern cost-based optimizers don’t blindly follow the order in which joins are written. Instead, they look for a join order that minimizes intermediate data and enables more efficient join strategies whenever possible!

From this example, it may seem like we can draw two straightforward conclusions:

1. Join order matters!
2. When writing queries with multiple joins, always join the smallest tables first and then join the larger tables.

The intuition behind the second point is simple. If you join two smaller tables first, there’s a good chance that the resulting intermediate dataset will also remain relatively small. A smaller intermediate result means less data to process in subsequent joins and also increases the likelihood that the optimizer can choose a Broadcast Join instead of a more expensive Sort Merge Join or Shuffle Hash Join, leading to better overall performance.

Let’s Validate It Practically

Theory is great, but as data engineers, we should never stop there. Let’s validate whether the optimizer actually changes the join order in practice.

For this demonstration, we’ll use Snowflake and create three tables with significantly different sizes:

• fact_sales – 50 million rows (large fact table)
• dim_store – 100 thousand rows (medium-sized dimension table)
• dim_city – 100 rows (small dimension table)

This setup closely resembles a typical star schema that we often encounter in real-world data warehouses.

Let’s create the sample data first —

CREATE OR REPLACE DATABASE demo_test;

USE demo_test;

-- City DIMENSION with 100 rows
CREATE OR REPLACE TABLE dim_city AS
SELECT
    seq4()+1 AS city_id,
    CASE
        WHEN seq4()=0 THEN 'Mumbai'
        WHEN seq4()=1 THEN 'Delhi'
        WHEN seq4()=2 THEN 'Bengaluru'
        WHEN seq4()=3 THEN 'Hyderabad'
        WHEN seq4()=4 THEN 'Chennai'
        ELSE CONCAT('City_', seq4()+1)
    END AS city_name,
    CASE
        WHEN seq4()<5 THEN 'Y'
        ELSE 'N'
    END AS is_metro
FROM TABLE(GENERATOR(ROWCOUNT=>100));

--Store DIMENSION with 1 rows
CREATE OR REPLACE TABLE dim_store AS
SELECT
    seq4()+1 AS store_id,
    CONCAT('Store_', seq4()+1) AS store_name,
    UNIFORM(1,101,RANDOM()) AS city_id
FROM TABLE(GENERATOR(ROWCOUNT=>100000));

--Sales Fact with 5 Millions rows
CREATE OR REPLACE TABLE fact_sales AS
SELECT
    seq4()+1 AS sale_id,
    UNIFORM(1,100001,RANDOM()) AS store_id,
    ROUND(UNIFORM(500,50000,RANDOM()),2) AS amount,
    UNIFORM(1,6,RANDOM()) AS quantity,
    DATEADD(
        day,
        -UNIFORM(0,365,RANDOM()),
        CURRENT_DATE()
    ) AS sale_date
FROM TABLE(GENERATOR(ROWCOUNT=>50000000));

To establish a baseline, let’s force Snowflake to follow the join order exactly as we’ve written it. We’ll do this using the DIRECTED keyword, which prevents the optimizer from reordering the joins.

This allows us to observe the execution plan without any join-order optimization, making it much easier to compare with the optimizer-driven plan later —

SELECT *
FROM fact_sales s
inner directed JOIN dim_store st
    ON s.store_id = st.store_id
inner directed JOIN dim_city c
    ON st.city_id = c.city_id
WHERE c.is_metro='Y';

Check the Query Profile for the query —

From Query Profile, we can have 2 important observations —

• Total Query duration: 5.4 sec
• The first join took 50.3% of total time as both side data is huge, so most possibly there was no scope of Broadcast Join & Snowflake compute had to go with huge data shuffle which took such time…

Execute below queries to make sure cached data is not used —

ALTER WAREHOUSE COMPUTE_WH SUSPEND;
ALTER WAREHOUSE COMPUTE_WH RESUME;
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Now let’s execute the same query without directed keyword —

SELECT *
FROM fact_sales s
JOIN dim_store st
    ON s.store_id = st.store_id
JOIN dim_city c
    ON st.city_id = c.city_id
WHERE c.is_metro='Y';

From Query Profile, we can have 2 important observations —

• Total Query duration: 1.2 sec
• Snowflake Compute has changed the Join Order itself & performed join between smaller DIM_CITY & DIM_STORE together first & then joined with bigger FACT_SALES Table(which is aligned with our theoretical intuition that, when writing queries with multiple joins, always join the smallest tables first and then join the larger tables!
• Due to very small data in 1 side of both the Join, Snowflake Compute might have got the chance to execute Broadcast Join eliminating the complex Shuffle Operation of Big Data, so this time, both join ran pretty fast!
• Also we can not ignore the contribution of Bloom Filter in eliminating huge number of mismatching rows from large Sales Table in this case!!
• Last but not the least, from the explanation itself, you can intuitively understand, Join reordering will be useful mostly for INNER and CROSS joins.

Conclusion:

So, in short, join order does matter! The good news is that modern query engines are smart enough to reorder joins and choose the most efficient execution plan whenever possible. However, the optimization strategy differs across compute platforms, and each engine has its own approach to join reordering.

If you’d like to dive deeper, I highly recommend reading the Snowflake engineering blog on Adaptive Join Strategy, where the Snowflake team explains their optimization approach in detail.

That’s all for this blog! For more such interesting and practical contents, follow me on YouTube, LinkedIn, and Medium!

Join Order Matters! Understanding how Modern Query Optimizers Think was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.

Fortunately, modern query engines such as Snowflake don’t simply execute the SQL exactly as it is written. Before execution, the optimizer analyzes the query and performs a series of transformations to generate a more efficient execution plan. Many of these optimizations happen automatically and are completely transparent to the user.

In this article, we’ll take a simple SQL query and inspect its Query Profile to understand what Snowflake is doing behind the scenes. We’ll see how the optimizer rewrites parts of the execution plan, why those transformations improve performance, and how understanding them can help us write more efficient SQL and better interpret Query Profiles.

Initial Setup:

Let’s create a sample DB & few Tables to explore automatic Query Optimization done by Snowflake compute —

CREATE OR REPLACE DATABASE demo_test;

USE demo_test;

-- departments
CREATE OR REPLACE TABLE departments (
    dept_id INT,
    dept_name STRING
);

INSERT INTO departments VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Finance');

-- employees
CREATE OR REPLACE TABLE employees (
    emp_id INT,
    dept_id INT,
    salary NUMBER(10,2)
);

INSERT INTO employees VALUES
(101, 1, 60000),
(102, 1, 75000),
(103, 1, 82000),
(104, 1, 91000),
(105, 2, 55000),
(106, 2, 62000),
(107, 2, 68000),
(108, 3, 70000),
(109, 3, 76000),
(110, 3, 83000),
(111, NULL, 50000);

Now let’s execute the below query & observe the Query Profile:

SELECT
    e.emp_id,
    d.dept_name,
    e.salary
FROM
(
    SELECT
        emp_id,
        dept_id,
        dept_id AS dept_id_new,
        salary
    FROM employees
    WHERE dept_id > 1
) e
JOIN departments d
ON e.dept_id_new = d.dept_id and 
e.dept_id > 1;

Observation 1: Join Filter

Since the Departments table is significantly smaller than the Employees table, Snowflake can construct a Bloom filter using the DEPT_ID values from the Departments table. This Bloom filter is then applied while scanning the Employees table, allowing Snowflake to quickly eliminate rows whose DEPT_ID definitely does not exist in the Departments table. As a result, fewer rows participate in the join, reducing the amount of data processed and improving query performance! (This concept we explored in detail in this blog)

Observation 2: New Filter generation

If you observe our query, we explicitly applied only one filter on the Employees table:

dept_id > 1

However, if you inspect the Query Profile, you’ll notice that Snowflake automatically introduces another predicate:

EMPLOYEES.DEPT_ID IS NOT NULL

This optimization is perfectly logical. Since we’re performing an INNER JOIN on DEPT_ID, any row where EMPLOYEES.DEPT_ID is NULL can never find a matching row in the Departments table. In SQL, NULL is not equal to any value—not even another NULL—so such rows are guaranteed to be excluded from the final result.

Recognizing this, Snowflake pushes the IS NOT NULL predicate down to the table scan itself. By filtering out these rows as early as possible, the optimizer reduces the amount of data that flows into the join operator, resulting in less work during query execution and improved overall performance!

Observation 3: New Filter on “Other” side of the Join

In our query, we applied the filter dept_id > 1 only on the Employees table before joining it with the Departments table. At first glance, it may seem that this filter affects only the Employees table. However, the optimizer recognizes an important fact: since the join condition is Employees.dept_id = Departments.dept_id, any department with dept_id <= 1 can never find a matching employee because those employee rows have already been filtered out.

Based on this observation, Snowflake automatically propagates the filter to the other side of the join by applying an equivalent predicate on the Departments table. As a result, rows with dept_id <= 1 are discarded from the Departments table before the join takes place.

This optimization reduces the amount of data flowing into the join operator, allowing Snowflake to perform the join more efficiently. By eliminating rows that are guaranteed not to participate in the join, the optimizer minimizes unnecessary processing, leading to lower compute usage and faster query execution — all without requiring any changes to the SQL written by the user —

Observation 4: Remove Redundant Filter

If you look closely at the query, the predicate dept_id > 1 is already applied inside the inner subquery on the Employees table. Therefore, by the time the rows reach the outer query, every record already satisfies this condition. Reapplying the same filter (e.dept_id > 1) in the outer query serves no purpose. Snowflake's optimizer detects this redundancy and intelligently removes the duplicate predicate from the execution plan, resulting in a simpler and more efficient execution strategy while preserving the query semantics.

Conclusion:

Although we typically focus on writing correct SQL, there’s an equally important component working behind the scenes — the query optimizer. As we’ve seen throughout this article, Snowflake doesn’t simply execute the SQL exactly as it’s written. Instead, it analyzes the query, identifies opportunities to simplify execution, propagates predicates where possible, removes redundant filters, and eliminates unnecessary work before the query even starts running.

These optimizations may seem small in isolation, but together they can significantly reduce the amount of data processed, lower compute consumption, and improve query performance — all without requiring any changes to your SQL.

The next time you’re troubleshooting a slow query or exploring the Query Profile, don’t just look at the execution time. Take a moment to understand how Snowflake has transformed your query behind the scenes. You’ll often discover that the optimizer is doing far more work than meets the eye, and understanding these transformations can help you become a better SQL developer and a more effective Snowflake practitioner.

For more such interesting and practical contents, follow me on YouTube, LinkedIn, and Medium!

Snowflake Query Optimizations You Hardly Notice - but They Make a Big Difference was originally published in Data Engineer Things on Medium, where people are continuing the conversation by highlighting and responding to this story.