Showing posts with label ETL. Show all posts
Showing posts with label ETL. Show all posts

Wednesday, September 2, 2026

Keeping Redshift System Table History Past 7 Days with S3 Tables

Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine until an auditor or an incident review asks for something older. Until now the usual fix was a custom export job to S3 and a Glue crawler to keep it queryable. AWS just added a native system table integration with S3 Tables that skips that pipeline entirely: Redshift can write system table data straight into Amazon S3 Tables, in Apache Iceberg format, with no ETL to maintain.

What it runs on

This applies to Redshift Provisioned on RA3 and RG instance types, and to Redshift Serverless. Older provisioned instance families are not supported. AWS launched it across a wide set of commercial regions rather than a single preview region, but region coverage is the kind of detail that shifts, so confirm your region against the current availability list before you enable it.

Permissions you need

The principal that enables the feature needs:

  • redshift:EnableLogging for a provisioned cluster, or redshift-serverless:UpdateNamespace for Serverless
  • s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy, to stand up the aws-redshift table bucket

That is the whole permission set. Once the bucket exists, Redshift creates namespaces and tables inside it through a service trust relationship with S3 Tables, so nobody needs standing permission to create namespaces or tables themselves.

1. Decide what you are retaining and how it should be organized

You choose which SYS_* views to publish, from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to SYS_CONNECTION_LOG and SYS_VACUUM_HISTORY, or all of them at once. You also pick a deployment model. Per-warehouse keeps each cluster's data in its own set of tables, which matters if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES could contain sensitive literal values. Consolidated writes every warehouse in the account and Region into shared tables, distinguished by a warehouse_name column, which is better for cross-warehouse observability.

Supported system tables

You can select any of these SYS_* views.

2. Enable delivery on the cluster

For a provisioned cluster, this reuses the existing logging API with a new destination type. The principal running this needs redshift:EnableLogging plus permission to create and configure an S3 table bucket named aws-redshift in the account.

-- publish selected system tables to S3 Tables, consolidated across the account
aws redshift enable-logging \
    --cluster-identifier my-redshift-cluster \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text sys_connection_log \
    --s3-table-granularity account
 
-- Redshift Serverless uses update-namespace instead
aws redshift-serverless update-namespace \
    --namespace-name my-namespace \
    --log-destination-type s3table \
    --s3-table-action Enable \
    --s3-table-names all \
    --s3-table-granularity namespace
  

3. Confirm data is actually flowing

Delivery runs in batches at a fixed frequency and only includes completed activity, so a query still running will not show up until it finishes. Check the last ingestion time per view with describe-logging-status on a provisioned cluster or get-namespace on Serverless.

aws redshift describe-logging-status \
    --cluster-identifier my-redshift-cluster
  

4. Register the table bucket with Glue Data Catalog

Querying the retained data from Redshift, Athena, or any other Iceberg-compatible engine requires the aws-redshift S3 table bucket to be integrated with AWS Glue Data Catalog first. This is a one-time step per account and Region, not something you repeat per cluster.

5. Query the history from Redshift

Once the catalog integration is in place, point an external schema at it and query the historical view like any other table. The rows carry warehouse_name, warehouse_namespace_arn, and s3_tables_ingestion_time alongside the original SYS_QUERY_HISTORY columns, which is what lets a consolidated deployment separate one cluster's activity from another's.

-- replace <glue_database> with the database created by the S3 Tables / Glue integration
CREATE EXTERNAL SCHEMA redshift_history
FROM DATA CATALOG
DATABASE '<glue_database>'
IAM_ROLE 'arn:aws:iam::111122223333:role/RedshiftHistoryReadRole';
 
-- queries older than the 7-day in-cluster window
SELECT warehouse_name, query_id, start_time, elapsed_time
FROM redshift_history.sys_query_history
WHERE start_time < dateadd(day, -7, getdate())
ORDER BY elapsed_time DESC
LIMIT 20;
  

What it costs

Writing the data out of Redshift into S3 Tables is free. What you pay for is standard S3 Tables storage and maintenance, meaning compaction and snapshot upkeep, on whatever you retain, plus normal usage pricing for the engine you query it with, whether that is Redshift Spectrum, Athena, or something else. Since data without an expiration policy is kept forever, an unset retention policy is really a storage cost decision, not just a compliance one.

Gotchas worth knowing before you enable this

  • Disabling and re-enabling, or switching between per-warehouse and consolidated, never backfills. Whatever happened during the gap is gone for good.
  • Delivered rows are immutable. You cannot update or delete individual rows through Redshift, only through S3 Tables record expiration.
  • Dropping the S3 Tables permanently deletes everything retained in them and Redshift does not recreate them automatically. Re-enabling starts a fresh table with no history.
  • A customer managed KMS key has to be set the first time you enable the feature. Changing it later means dropping the tables, and the retained data, and starting over.
  • The views marked with an asterisk above need patch P203 or later. On an older patch the tables get created but stay empty.
  • Everything is scoped to a single account and a single region. Cross-account or cross-region analysis means combining results at query time, not a single unified table.
  • Only completed activity is delivered. A query that is still running will not show up until it finishes, aborts, or is canceled.

Before you run this in production

Set a record expiration policy directly in S3 Tables once delivery is running, since without one the data is kept indefinitely and keeps accruing storage cost. Start with the per-warehouse model if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES might carry sensitive literal values, and only move to consolidated once you have confirmed what those views actually capture in your environment. 


If you are pulling SYS_QUERY_EXPLAIN history into this pipeline for performance work, PlanTrace is a free tool for turning those query plans into something you can actually read.

Friday, August 7, 2026

Narrow Task Decomposition in AI Pipeline Agents

A nightly load into sales_fact fails. Ask an orchestrating agent to break down the diagnosis and it will usually stop at the first two subtasks that come to mind: check the logs, check the schema. That decomposition is narrow, and a fix built on it can miss the real cause. The problem is not the model, it is that the coordinator never questioned its own first list before delegating.

1. See what narrow decomposition misses

A first-pass breakdown of "why did sales_fact fail" often lands on log-analyzer and schema-inspector and stops there. Both are reasonable, and both are incomplete. Neither one checks whether the upstream extract delivered bad or late data, whether another job holds a lock on the same table, or which downstream dashboards are now stale because the load never completed. A fix based on two subtasks treats the visible half of the failure as the whole failure.

2. Add a self-critique step to the coordinator prompt

The fix is a coordinator prompt that forces a second pass before any delegation happens: generate an initial subtask list, ask what is missing, add subtasks to cover the gap, and only then hand work to subagents.

You are a coordinator diagnosing an ETL failure. When
decomposing the task:

1. Generate an initial list of subtasks.
2. Ask yourself: what systems, data sources, or failure
   modes are missing from that list?
3. Add subtasks to cover those gaps.
4. Only then begin delegating to subagents.

For an ETL failure specifically, consider:
- the failing job AND its upstream dependencies
- a structural cause (schema) AND a data cause (quality)
- this run AND whether it is a recurring pattern
- the technical root cause AND the downstream impact
  

Run the sales_fact example through that loop and the initial two-item list grows to four: log-analyzer and schema-inspector, plus a source-data-validator that checks the upstream extract for null spikes or row-count drops, and a downstream-impact-checker that lists which dashboards or scheduled jobs depend on the table. The self-critique step costs one extra generation and catches the two subtasks that a narrow first pass always skips.

3. Define each subtask as a subagent

With Claude's Agent SDK, each subtask becomes an AgentDefinition, with a description Claude matches against the task and a prompt scoped to that one job.

from claude_agent_sdk import AgentDefinition

log_analyzer = AgentDefinition(
    description="Diagnoses ETL failures from pipeline logs. "
                "Use when a nightly load job fails and the "
                "cause is unclear.",
    prompt="Find the failing step and report the exact "
           "error message and line number. Do not propose "
           "a fix.",
    tools=["Read", "Grep", "Bash"],
    model="haiku",
)

schema_inspector = AgentDefinition(
    description="Checks table structure for drift against "
                "the last known good schema.",
    prompt="Compare current columns, types, and "
           "constraints to the last successful load.",
    tools=["Read", "Bash"],
)

source_data_validator = AgentDefinition(
    description="Checks the upstream extract for the failed "
                "load. Use for row-count drops or null spikes.",
    prompt="Compare today's source row count and null "
           "rate to the 7-day average.",
    tools=["Read", "Bash"],
    model="haiku",
)

downstream_impact_checker = AgentDefinition(
    description="Lists dashboards and jobs that depend on "
                "the table that failed to load.",
    prompt="Find consumers of sales_fact and flag which "
           "are now stale.",
    tools=["Read", "Grep"],
)
  

AgentDefinition also takes a model field, so a subtask that is closer to pattern matching, like reading a log file or comparing two row counts, can run on a cheaper model while one that needs judgment, like assessing schema drift or downstream impact, stays on the default. That is the same idea covered in matching the model to the task, not the app, applied at the subagent level instead of a single orchestrator.

4. Wire the coordinator to call the agents

The coordinator prompt from step 2 becomes the system_prompt, and the four AgentDefinition objects from step 3 go into agents, keyed by the name Claude will use to call each one. Claude decides on its own which of the four to invoke, based on how the task matches each description.

from claude_agent_sdk import query, ClaudeAgentOptions

AGENTS = {
    "log-analyzer": log_analyzer,
    "schema-inspector": schema_inspector,
    "source-data-validator": source_data_validator,
    "downstream-impact-checker": downstream_impact_checker,
}

async for message in query(
    prompt="The sales_fact load failed last night, find out why.",
    options=ClaudeAgentOptions(
        system_prompt=COORDINATOR_PROMPT,
        # "Agent" auto-approves each subagent call
        allowed_tools=["Read", "Grep", "Bash", "Agent"],
        agents=AGENTS,
    ),
):
    for block in getattr(message, "content", None) or []:
        if getattr(block, "name", None) == "Agent":
            print("routed to:", block.input.get("subagent_type"))
  

Claude does not call a Python function directly, it emits a tool_use block named Agent, and block.input["subagent_type"] holds the name of the agent it picked, one of the four keys in AGENTS. Logging that value is how you confirm the coordinator actually routed to source-data-validator or downstream-impact-checker instead of quietly falling back to the two obvious subtasks. To force a specific agent instead of letting Claude choose, name it in the prompt: "use the downstream-impact-checker agent to list what depends on sales_fact."

5. The same routing in OpenAI's Agents SDK

OpenAI's Agents SDK expresses the same coordinator through handoffs. The triage agent's instructions carry the same four-step self-critique, and each of the four specialists is passed in its handoffs list, exposed to the model as a callable tool named transfer_to_<agent_name>. When Runner.run finishes, result.new_items contains a HandoffOutputItem for any handoff that fired, and its target_agent.name is the concrete signal, the OpenAI equivalent of reading subagent_type off Claude's Agent tool call.

6. One failure, traced end to end

Take one run instead of a list of what each agent can do. At 02:14, the nightly load into sales_fact fails, and the engineer on call prompts the coordinator with "the sales_fact load failed last night, find out why." The coordinator's first move is always the same: call log-analyzer, since nothing else can be scoped correctly before the actual error is known.

routed to: log-analyzer
  MERGE INTO sales_fact failed at 02:14:07 with:
  duplicate key value violates unique constraint
  "sales_fact_pkey" (order_id, sale_date)
  

A duplicate key on a merge has two plausible explanations: something changed about the table that now lets duplicates in, or the source data itself contains rows that collide on that key. Both are live possibilities under the self-critique step from earlier, so the coordinator calls schema-inspector and source-data-validator next. It does not call downstream-impact-checker yet, since nothing so far says anything about who reads this table.

routed to: schema-inspector
  no DDL changes on sales_fact in the last 14 days

routed to: source-data-validator
  source row count: 41,209 (7-day avg: 68,450, -40%)
  null rate on customer_id: 6.2% (7-day avg: 0.1%)
  

schema-inspector rules out a structural cause. source-data-validator explains the collision: a partial extract with a null spike on customer_id delivered rows that collapsed onto the same order_id and sale_date, tripping the unique constraint on merge. The coordinator stops here and never calls downstream-impact-checker, because the merge failed and rolled back, so nothing downstream ever read the bad batch. That agent earns its place in a run where a bad load succeeds silently instead of failing loudly, a different failure mode from this one.

Three of the four candidate agents ran, in two rounds instead of one batch, and the proposed fix follows directly from what they found: quarantine the extract that arrived overnight rather than retrying the load as is, and add a row-count and null-rate check ahead of the merge so a batch shaped like this one gets rejected before it reaches sales_fact, not after.

Before You Trust the Decomposition

Test the coordinator prompt against a case where you already know the full list of causes, and check whether its self-critique step actually surfaces the ones a naive first pass would miss. If it keeps landing on the same two or three subtasks regardless of the failure, the gap-checking questions in the prompt are too generic and need to name the specific systems in your pipeline.

Not every failure needs any of this. A connection timeout, a transient throttling error, a job that fails because another job it depends on is still running, these have deterministic fixes: retry with backoff, wait and requeue, alert and stop. Route the alert through a cheap classifier first, or a plain if-statement on the error code, and reserve the coordinator and its agents for failures that pattern-matching cannot already explain. Calling four agents to conclude "retry it" is not decomposition, it is waste.

Friday, June 12, 2026

5 Starter Projects for Your AI and Data Engineering Portfolio

Reading tutorials is fine. Shipping something is better. If you are trying to break into data engineering or AI, nothing on your resume carries more weight than a GitHub repo with working code and a problem you actually solved. These five projects are designed to give you hands-on experience with real tools while producing portfolio artifacts you can point to in an interview.


Project 1 — Automated ETL Pipeline with Scheduling

Tech Stack: Python, PostgreSQL, Apache Airflow, Docker

Build a pipeline that pulls data from a public API (weather, exchange rates, or any open dataset), transforms it with Python, and loads it into PostgreSQL on a schedule. Airflow handles the orchestration, and Docker keeps the environment reproducible. This project teaches you the core ETL loop and gives you a DAG you can walk through in any data engineering interview. The scheduling angle forces you to think about idempotency and failure handling from day one.

# Minimal Airflow DAG skeleton
from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime
 
def extract(): ...
def transform(): ...
def load(): ...
 
with DAG("etl_pipeline", start_date=datetime(2025, 1, 1), schedule="@daily") as dag:
    t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="transform", python_callable=transform)
    t3 = PythonOperator(task_id="load", python_callable=load)
    t1 >> t2 >> t3
  

Project 2 — Cloud Data Warehouse on AWS

Tech Stack: AWS S3, Amazon Redshift Serverless, Python (boto3), SQL

Upload a dataset to S3, provision a Redshift Serverless workgroup, and load the data using the COPY command. Write a set of analytical queries against it. The goal is not just to run queries — it is to understand how a cloud warehouse differs from a local database: columnar storage, distribution keys, and the cost model. This project demonstrates cloud data skills that show up in virtually every modern data engineering job description. Use the AWS Free Tier to keep costs at zero while learning.

-- Load from S3 into Redshift
COPY sales
FROM 's3://your-bucket/sales.csv'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftRole'
FORMAT AS CSV
IGNOREHEADER 1;
  

Project 3 — ETL Pipeline with Data Factory in Microsoft Fabric

Tech Stack: Data Factory in Microsoft Fabric, Microsoft Fabric Lakehouse, OneLake, SQL Server, Python (Fabric Notebook)

Microsoft has consolidated its data integration story into Microsoft Fabric, and Data Factory in Microsoft Fabric is its next-generation replacement for Azure Data Factory. Build a pipeline that ingests data from SQL Server or a flat file, transforms it using a Fabric Notebook (Python/PySpark), and lands the result in a Fabric Lakehouse backed by OneLake. This project exposes you to the unified Fabric workspace model — one platform for pipelines, notebooks, warehouses, and Power BI — which is exactly where Microsoft data engineering is heading. A free Fabric trial requires no credit card and gives you full access to build this end to end.


Project 4 — RAG Chatbot over Your Own Documents

Tech Stack: Amazon Bedrock, Claude or Llama 3 (via Bedrock), pgvector (PostgreSQL), Python, LangChain

Take a set of PDF or text documents you own, chunk them, generate embeddings using a Bedrock foundation model, store the vectors in PostgreSQL with the pgvector extension, and build a simple question-answering interface on top. This is Retrieval Augmented Generation (RAG) in its simplest form. It teaches you the full AI data pipeline: ingestion, embedding, vector search, and prompt construction. RAG is one of the most in-demand AI engineering patterns in production today.

# Store a document embedding in pgvector
INSERT INTO documents (content, embedding)
VALUES (%s, %s::vector);
 
-- Semantic similarity search
SELECT content
FROM   documents
ORDER BY embedding <-> '[query_vector]'::vector
LIMIT 5;
  

Project 5 — End-to-End ML Pipeline with Feature Engineering

Tech Stack: Python, pandas, scikit-learn, SQL Server or PostgreSQL, MLflow

Pick a structured dataset with a clear prediction target (churn, sales forecast, classification). Build the full cycle in Python: data extraction from a relational database, feature engineering, model training, and experiment tracking with MLflow. The data engineering side is the extraction and feature pipeline; the AI side is the model and tracking. Publishing your MLflow experiment results in a public repo gives reviewers something concrete to evaluate. This project bridges the two disciplines in a single deliverable.

import mlflow
from sklearn.ensemble import RandomForestClassifier
 
with mlflow.start_run():
    model = RandomForestClassifier(n_estimators=100)
    model.fit(X_train, y_train)
    mlflow.log_metric("accuracy", model.score(X_test, y_test))
    mlflow.sklearn.log_model(model, "model")
  

Five Projects. Two Cloud Platforms. One GitHub Profile Worth Showing.

Together these projects cover the full surface area hiring managers scan for: pipeline orchestration, cloud warehousing on AWS and Azure, AI integration, and end-to-end ML tracking. Pick the stack you are least afraid of, break it, fix it, and commit the mess. The best time to start was last month. The second best time is now.

If you are curious about what a working portfolio looks like in practice, you can take a look at my own projects.



Monday, June 1, 2026

Data Skew in Data Engineering: What It Is and How to Fix It

You kick off a distributed job expecting it to finish in minutes — but one task keeps running while all others have long since completed. The culprit is almost always data skew. It is one of the most common performance killers in distributed data engineering, yet it is often misdiagnosed or ignored until it starts breaking SLAs.

Data skew happens when data is unevenly distributed across partitions or compute nodes. In systems like Apache Spark, Amazon Redshift, or Azure Synapse, the workload is designed to run in parallel. When one partition holds a disproportionate share of the data — because of a high-frequency key or a poor distribution choice — that one node does all the heavy lifting while the rest sit idle. The job cannot complete until every partition finishes, so your entire pipeline is only as fast as the slowest, most overloaded worker.


1. What causes data skew

The root cause is almost always a skewed join or grouping key. If 60% of your orders belong to a single customer_id, or your event log has millions of rows for one status = 'active' value, Spark or your MPP engine will route all of those rows to the same partition during a shuffle. The result: one executor handles millions of rows while ten others process a few hundred.

In Amazon Redshift, the problem manifests at the storage layer. If your DISTKEY column has low cardinality or a heavily concentrated value distribution, Redshift routes too many rows to the same compute node slice, forcing it to do more work than the others during every query that touches that table.


Types of data skew

Data skew is not a single pattern — it shows up in different shapes depending on the root cause. The four most common types in distributed pipelines:

Key skew — hot partition key

One key (e.g. a single customer_id) floods one partition while all others stay near-empty.

Partition skew — uneven data split

Wide size variation across partitions due to a low-cardinality key like status or country.

Join skew — NULL key explosion

A large share of rows with NULL or a default value all land in the same executor during shuffle.

Temporal skew — time-based spike

Event data clusters at specific timestamps (e.g. end-of-day batch), overloading those time-aligned partitions.

Normal partition Skewed / hot partition Ideal even distribution / spike window

2. Issues data skew causes in your pipelines

The consequences go beyond slow queries. Here are the most damaging effects data skew causes in practice:

  • Pipeline bottlenecks: A single overloaded partition stalls the entire stage. Every downstream task waits on it.
  • Out-of-memory errors: The skewed executor receives far more data than it can fit in memory, causing spills to disk or outright failures.
  • Wasted cluster resources: Most nodes finish quickly and remain idle while one struggles. You are paying for compute that does nothing.
  • Unpredictable SLA breaches: The job duration becomes tied to the size of the hot partition rather than total data volume, making runtime estimates unreliable.
  • Cascading failures in multi-step ETL: A skew-induced timeout in one stage can trigger retries, delay downstream jobs, and corrupt incremental watermarks.

3. Detecting skew before it becomes an issue

In Spark (including Azure Synapse Analytics and Databricks), open the Spark UI and look at the task duration histogram under a shuffle stage. A long tail of one task while all others finish quickly is the signature of skew. You can also profile the key distribution directly before running a heavy join:

# Quick skew check in PySpark
df.groupBy("customer_id")
  .count()
  .orderBy(col("count").desc())
  .show(20)
  

In Redshift, the SVV_TABLE_INFO view exposes a skew_rows column that reports the ratio between the most-populated and least-populated node slices. AWS documentation recommends reviewing the distribution key on any table with a skew_rows value of 4.0 or higher — values below that are generally acceptable natural imbalance. The Identifying tables with data skew guide in the AWS docs walks through how to interpret this output.

-- Check skew_rows ratio in Redshift
SELECT "table", diststyle, skew_rows
FROM   svv_table_info
WHERE  skew_rows >= 4.0
ORDER BY skew_rows DESC;
  

4. Technique: Salting to break hot keys

Salting is the most widely used fix for skewed joins and aggregations. The idea is to append a random integer suffix to the skewed key, artificially spreading one large partition across multiple smaller ones. On the smaller (lookup) side of the join, you replicate each row once per salt value so that both sides still match correctly. After the join, you strip the salt column.

The Azure Synapse Spark performance guide explicitly recommends salting the entire key — or only the subset of skewed keys — as the primary remedy for shuffle-induced slowdowns.

from pyspark.sql import functions as F

SALT_BUCKETS = 10

# Salt the large (skewed) table
df_large = df_large.withColumn(
    "salt", (F.rand() * SALT_BUCKETS).cast("int")
)

# Explode the small (lookup) table to match all salt values
df_small = df_small.withColumn(
    "salt", F.explode(F.array([F.lit(i) for i in range(SALT_BUCKETS)]))
)

# Join on both the original key and the salt
result = df_large.join(df_small, ["customer_id", "salt"]).drop("salt")
  

5. Technique: Broadcast joins for small dimension tables

When one side of a join is small enough to fit in executor memory, a broadcast join eliminates the shuffle entirely. Instead of moving data between nodes, Spark sends a full copy of the small table to every executor, so each worker can join locally. This is the fastest fix when the skewed side is the larger table and the lookup side is compact.

from pyspark.sql.functions import broadcast

result = df_large.join(
    broadcast(df_small), "customer_id"
)
  

In Redshift, the equivalent approach is setting DISTSTYLE ALL on small dimension tables. This stores a full copy of the table on every compute node, so join colocation is guaranteed without any redistribution at query time. The Redshift distribution styles documentation recommends this for slow-moving reference tables under a few hundred megabytes.


6. Technique: Adaptive Query Execution (Spark 3.x)

Since Spark 3.0, Adaptive Query Execution (AQE) can detect and automatically split skewed partitions at runtime without any manual intervention. When AQE identifies a task whose partition size significantly exceeds the median, it breaks that partition into smaller sub-tasks and replicates the corresponding data from the other join side to match. You can enable it with two configuration properties:

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
  

AQE is a strong first line of defence on modern Spark environments. If it does not resolve the skew fully — for example when the hot key is extreme — fall back to manual salting as described in the previous section.


7. Fixing skew in Amazon Redshift: choosing the right DISTKEY

In Redshift, skew is a storage-level problem. The fix is selecting a distribution key with high cardinality and uniform value distribution. Columns like order_id or event_id make far better distribution keys than status or country_code. The AWS prescriptive guidance on Redshift table design recommends validating cardinality before committing to a key column — if you notice skewness, change the distribution key.

For tables used heavily in joins, align the distribution key with the join column used most often. This ensures that matching rows already reside on the same node slice, eliminating costly data redistribution at query time. If automatic table optimization is enabled, Redshift will manage distribution style changes in the background as table sizes evolve.


Wrapping Up

Data skew is a structural problem that compounds over time: as tables grow, hot keys attract more rows, and pipelines that once ran in minutes begin taking hours. The pattern to follow is consistent — profile key distributions early, detect skew via the Spark UI or system views like SVV_TABLE_INFO in Redshift, and apply the right fix for the context: salting for heavy shuffle joins, broadcast joins when the lookup side is small, AQE for automatic handling in Spark 3.x, and a well-chosen DISTKEY for Redshift table design. The most reliable rule: never use a column with low cardinality as a partition or distribution key.


Thursday, May 21, 2026

Stop Using Pandas for Aggregations — Try DuckDB Instead

If you've ever loaded a 2 GB CSV into pandas just to run a few aggregations — and watched your machine struggle — there's a better tool for the job. DuckDB is a free, open-source, in-process OLAP database that runs SQL directly inside your application, with no server to set up and no external dependencies. Think of it as SQLite for analytics: install it in seconds, query Parquet files, CSVs, JSON, or even pandas DataFrames, and get columnar-speed performance on your laptop.


1. Installing DuckDB

DuckDB is available for Python, R, the CLI, and several other runtimes. The most common way to get started is via pip or the CLI binary from the official installation page.

# Python
pip install duckdb
 
# R
install.packages("duckdb")
 
# macOS CLI via Homebrew
brew install duckdb
 
# Windows CLI via winget
winget install DuckDB.cli
  

2. What "in-process" actually means

Traditional databases run as a separate server process. Your application connects over a network socket, serializes the query, waits for the server to deserialize and execute it, and then serializes the result back. That overhead adds up quickly, especially for analytical workloads. DuckDB runs inside your process — no server, no network round-trip. When you use the Python package, for example, DuckDB can run queries directly on an existing pandas DataFrame without ever copying the data. This makes it extremely fast for ad-hoc analytics and ETL transformations.

# Install
pip install duckdb
 
# Query a pandas DataFrame directly — no data copy
import duckdb, pandas as pd
 
df = pd.read_csv("sales.csv")
 
result = duckdb.sql("""
    SELECT region, SUM(amount) AS total
    FROM df
    GROUP BY region
    ORDER BY total DESC
""").df()
  

3. Querying files directly — no table creation needed

One of DuckDB's most practical features is its ability to query files directly from the FROM clause. You can point it at a local Parquet file, a remote CSV over HTTPS, or even an S3 bucket — without defining a schema or creating a table first. DuckDB auto-detects column names and types, so exploratory work is fast. This is documented in the official DuckDB Guides.

-- Query a local Parquet file
SELECT customer_id, COUNT(*) AS orders
FROM   'orders_2024.parquet'
WHERE  status = 'completed'
GROUP BY customer_id
ORDER BY orders DESC
LIMIT  10;
 
-- Or query a remote CSV over HTTPS (httpfs extension auto-loads)
SELECT COUNT(*) FROM 'https://data.example.com/events.csv';
  

4. Where DuckDB fits in a data engineering workflow

DuckDB is not a replacement for SQL Server, Redshift, or PostgreSQL in transactional or multi-user systems. Its strength is in local analytical processing — the kind of work that typically lands in a Python script or Jupyter notebook. Common patterns include:

  • ETL pre-processing: filter, join, and reshape raw CSV or Parquet files before loading into a data warehouse.
  • Format conversion: read JSON or CSV and write out clean Parquet with a single SQL statement using COPY ... TO.
  • Ad-hoc analytics on exports: query a data warehouse export locally without spinning up another cloud service.
  • Privacy-first analysis: process sensitive data (health records, financial statements) entirely on-premises, with data never leaving the machine.
-- Convert a CSV to Parquet in one shot
COPY (
    SELECT *
    FROM   'raw_data.csv'
    WHERE  event_date >= '2024-01-01'
) TO 'clean_data.parquet' (FORMAT PARQUET);
  

5. Columnar execution — why it's fast

DuckDB uses a columnar-vectorized query engine. Instead of processing one row at a time (like PostgreSQL or MySQL do internally for OLTP), DuckDB processes large batches of column values together in a single CPU operation. This reduces the per-value overhead dramatically, making aggregations and scans over wide tables significantly faster than row-oriented engines. For a data engineer, this means you can comfortably run GROUP BY queries over tens of millions of rows on a standard laptop in seconds.


Wrapping Up

DuckDB fills a gap that pandas alone struggles with: fast, SQL-native, columnar analytics that run in-process with zero infrastructure. It reads Parquet, CSV, and JSON natively; integrates cleanly into Python and R workflows; and handles files too large for memory by spilling to disk automatically. For data engineers doing local transformations, format conversions, or exploratory analysis on raw exports, DuckDB is often the fastest path from raw data to insight. Start with the official DuckDB documentation — the installation takes under a minute, and the CLI is ready to query immediately.


How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...