ELT Performance: Pushdown, Partitioning and Warehouse-Native SQL

The architectural shift from legacy ETL to cloud-native ELT fundamentally changed how enterprise data pipelines operate. Historically, data extraction and transformation occurred inside specialized, intermediate processing engines or heavy runtime servers before landing in an analytical database. Today, platforms like Snowflake, Databricks, Google BigQuery, and AWS Redshift house raw data directly, transforming it in place using distributed columnar compute.

However, shifting to ELT does not automatically guarantee high performance or cost efficiency. Poorly designed SQL transformations, missing predicate pushdown, and neglected physical data layout frequently result in runaway cloud spend, bloated warehouse uptime, and failing SLAs.

Achieving maximum performance across modern etl & elt workflows requires three foundational engineering disciplines: end-to-end query pushdown, disciplined physical partitioning and clustering, and warehouse-native SQL optimization.

---

1. Full Query Pushdown: Eliminating Intermediary Compute Overhead

In legacy architectures, transformations frequently pulled raw rows out of storage into an external compute cluster—such as a dedicated Apache Spark environment, an Airflow worker node, or an integration server—only to write the aggregate results back to the destination. This pattern incurs massive network egress costs, high memory consumption on orchestration workers, and severe serialization/deserialization penalties.

Modern etl & elt performance hinges on compute pushdown: the orchestration engine (whether dbt, Dagster, or custom orchestration code) should only act as a control plane. It issues DDL and DML commands, while the actual computation happens entirely within the warehouse or lakehouse engine.

How Predicate and Projection Pushdown Work

Pushdown optimization delegates filter criteria and column selection down to the storage layer before data is materialized in memory:

• Projection Pruning: The query engine parses the SQL syntax tree to identify only the columns required downstream. If a source table contains 120 columns but your transformation requires 4, the storage engine reads only the specific micro-partitions or Parquet byte ranges containing those 4 columns.
• Predicate Pushdown: The engine pushes WHERE, HAVING, and join filters directly to the storage metadata layer. Parquet files, Delta logs, and Snowflake micro-partitions maintain min/max file-level statistics. If a query requests records where transaction_date >= '2025-01-01', the engine uses these statistics to skip every file whose maximum date is prior to 2025 without reading a single row of raw data.

When pushdown fails—often caused by wrapping columns in non-deterministic functions (e.g., WHERE TO_CHAR(created_at, 'YYYY-MM') = '2025-01')—the engine must scan the entire dataset. Ensuring predicates remain sargable (Search Argument Able) preserves pushdown and prevents catastrophic full-table scans.

---

2. Partitioning, Clustering, and Micro-Partition Strategies

Even the most intelligent query planner cannot optimize a poorly structured storage layout. When querying multi-terabyte or petabyte tables, scanning unneeded data accounts for the vast majority of execution time and cloud infrastructure expense.

Micro-Partitioning vs. Physical Directory Partitioning

Traditional data lakes on AWS S3 or Azure Data Lake Storage (ADLS) rely on directory-based Hive partitioning (e.g., /year=2025/month=03/). While effective for coarse-grained pruning, this approach introduces severe drawbacks at scale:

• Small File Problem: High-cardinality partition keys generate millions of tiny files, overwhelming object storage metadata APIs and degrading downstream read performance.
• Partition Drift: Queries filtering on non-partitioned keys gain zero pruning benefit, forcing full-lake scans.

Modern cloud data platforms replace manual folder partitioning with metadata-driven clustering:

• Snowflake Micro-Partitions: Snowflake automatically organizes tables into contiguous 50MB to 500MB micro-partitions. It records column min/max values, distinct counts, and null counts in an out-of-band metadata layer. If data arrives chronologically, automatic clustering often handles pruning naturally. When query access patterns diverge (such as querying by tenant_id and event_type), explicit clustering keys are essential to consolidate related rows into adjacent micro-partitions.
• Databricks Liquid Clustering: Moving beyond legacy OPTIMIZE ... Z-ORDER BY commands, Databricks Delta Lake introduced Liquid Clustering (CLUSTER BY (col1, col2)). Liquid clustering removes the rigid hierarchies of Hive partitioning, dynamically adjusting data layout during writes without requiring full-table rewrites.

Targeted Pruning Metrics:
Total Micro-Partitions: 12,450
Partitions Scanned:          42  (99.66% Pruned)
Bytes Scanned:           1.8 GB  (vs. 535 GB Total Table Size)

Achieving this level of partition pruning is the single most impactful lever for accelerating ELT execution and lowering warehouse credit consumption.

---

3. Comparing Optimization Paradigms Across Modern Engines

Each leading warehouse platform processes pushdown, storage clustering, and transformation SQL differently. Selecting the appropriate architectural pattern requires understanding these core engine behaviors:

At Izan Technologies, our engineering teams design data platform implementations to exploit these engine-specific mechanics, ensuring client architectures avoid generic, one-size-fits-all pipelines that inflate operational costs.

---

4. Writing Warehouse-Native SQL: Avoiding Common Performance Traps

SQL is declarative, but how an engine compiles and executes that SQL physically determines transformation performance. Writing warehouse-native SQL requires replacing procedural, row-by-row thinking with set-based, vectorized logic.

1. Leverage the QUALIFY Clause Over Subquery Joins

Deduplicating records is one of the most common tasks in ELT pipelines. The classic pattern involves calculating a row number inside a Common Table Expression (CTE) and filtering it in an outer query:

-- Suboptimal / Verbose Pattern
WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) as rn
    FROM raw_events
)
SELECT * FROM ranked_records WHERE rn = 1;

Engines like Snowflake, Databricks, and BigQuery natively support the QUALIFY clause. This evaluates window functions directly in the filtering pipeline, simplifying execution plans and avoiding intermediate CTE materialization:

-- Warehouse-Native Pattern
SELECT * 
FROM raw_events 
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1;

2. Optimize Incremental MERGE Operations

Incremental models frequently rely on SQL MERGE statements to handle upserts. However, an unconstrained MERGE forces the engine to scan the entire target table to match join keys.

To preserve speed at scale:

• Always include partition/cluster pruning filters in the ON clause of the merge.
• Ensure the join condition matches the physical clustering key of the destination table.
• For append-heavy pipelines, consider dynamic partition overwrites or insert-only staging tables rather than continuous full-table merges.

3. Replace Cross-Joins and Complex Regex with Native Flattening

When processing nested JSON or semi-structured payloads, avoid multi-stage string parsing or cross-joins with external utility tables. Use native array and object functions—such as Snowflake's FLATTEN(), Databricks' EXPLODE(), or BigQuery's UNNEST(). These functions are vectorized and execute directly within memory without spilling intermediate datasets to temporary disk storage.

---

5. Benchmarking and Continuous Execution Governance

High-performance ELT requires proactive observability. Query profile analysis should be integrated into deployment validation:

• Spill to Remote/Local Storage: If a warehouse query profile shows significant "Bytes Spilled to Local/Remote Storage," your warehouse size is insufficient for the working set size, or your join keys contain massive data skew. Resolving the skew or restructuring the query eliminates expensive disk I/O.
• Compilation vs. Execution Time: If compilation time exceeds execution time, your pipeline is likely executing thousands of microscopic queries rather than batched, vectorized transformations.
• Data Skew Detection: Monitor compute nodes during join execution. If 15 out of 16 nodes complete in 5 seconds while 1 node runs for 4 minutes, your join key has excessive cardinality imbalance (such as a disproportionate volume of NULL or default values).

---

How Izan Technologies Helps

Designing high-throughput, cost-efficient ELT pipelines requires deep expertise across cloud platforms, data engineering frameworks, and warehouse architectures. Izan Technologies specializes in architecting custom data platforms, real-time analytics solutions, and advanced machine learning infrastructure on AWS, Azure, Databricks, and Snowflake.

Unlike traditional agencies that lock you into proprietary platforms or reuse brittle templates, Izan Technologies ensures that our clients own 100% of the IP we deliver. Every pipeline, data model, and custom algorithm developed belongs entirely to your organization.

Whether you need to optimize existing pipeline performance, refactor costly data transformations, or build an end-to-end data platform from scratch:

• Try our Smart AI DataStudio to explore how automated engineering accelerators can streamline your data workflows.
• Ready to eliminate data bottlenecks and slash your warehouse compute costs? talk to an expert today to review your current architecture with our engineering team.