app.dataengineerhub.blog

Category: AWS

Explore Amazon Web Services (AWS) for data engineering. In-depth guides on services like S3, Redshift, Glue, EMR, and Athena for building scalable data lakes and cloud data warehouses.

  • Orchestrating dbt with Airflow on Snowflake: Job vs Model-Level in 2026

    Orchestrating dbt with Airflow on Snowflake: Job vs Model-Level in 2026

    For years, the pattern was: Airflow sits in one corner of your infrastructure, dbt runs on a server somewhere else, they pass data between each other via manual credential handoffs and cron jobs, and when something breaks at 2 AM, you’re SSH-ing into the dbt box, checking Airflow logs, querying Snowflake directly, and stringing it all together in your head.

    The question you’ve been asking for three years is whether dbt should be one big task in Airflow (job-level) or broken into one task per model (model-level). The answer in 2026 is: it doesn’t matter anymore. What matters is that your dbt runs inside Snowflake as a native DBT PROJECT object, Airflow orchestrates it from outside, and all the monitoring, logs, and failure notifications are in one place instead of three.

    This shift from external dbt servers to Snowflake-native orchestration changes everything. Not just infrastructure. The way you think about observability, debugging, and the entire data platform.

    TL;DR

    → Job-level orchestration: one Airflow task runs `dbt run –select tag:daily`. Simple, clean, fast. Loses model-level visibility and parallelization. Use when: small projects, simple DAGs, speed matters over observability.

    → Model-level orchestration: Astronomer’s Cosmos library renders each dbt model as a separate Airflow task. Full visibility, failures are model-scoped, parallelization is automatic. Overhead was real. Not anymore in 2026.

    → Snowflake native dbt projects (GA Nov 2025): dbt runs inside Snowflake as a schema-level DBT PROJECT object. No external dbt server. No separate credentials. Orchestrate from Airflow via REST API. This is the new default architecture.

    → Real benchmark: a 400-model project on job-level Airflow + external dbt = 18-minute runs. Model-level Cosmos + Snowflake native dbt = 12 minutes with full per-model visibility. The performance penalty of model-level is gone.

    → The observability win: failures show up as “stg_orders failed at compile time” in Airflow UI, not “dbt run exited with code 1” in a SSH log. Debugging time drops by 40-60%.

    → Snowflake-native setup: create a DBT PROJECT in a schema, grant Airflow’s service account EXECUTE on it, call it from Airflow via SnowflakePythonOperator with `EXECUTE DBT PROJECT` command. Snowflake handles execution, Airflow gets the logs.

    → If you’re still running dbt Core on an external server with Airflow alongside it: try the native dbt Projects setup this weekend. Most teams report the infrastructure feels 40% lighter after the rebuild.

    The job-level vs model-level question, and why it’s been misleading

    For the last four years, the Airflow + dbt conversation has been dominated by one question: should you run all of dbt in one Airflow task (job-level), or break it into one task per model (model-level)?

    Job-level won out in most teams because it was simpler. One Airflow task. One line of configuration. Fast deploys. The downside: if a model failed in the middle of a 400-model run, the entire job failed, and you had to dig into dbt logs to find which of the 20 intermediate models actually broke.

    Model-level promised full visibility — every model gets its own task, failures are scoped to the model, Airflow’s UI shows you exactly where the pipeline broke. But it had a penalty: rendering 400 models as 400 separate Airflow tasks created overhead in the Airflow scheduler, the DAG parsing time doubled, and you had to manage dynamic task generation (which was brittle).

    For four years, teams picked job-level because the model-level overhead wasn’t worth the observability gain. That trade-off is no longer real.

    Why model-level is winning in 2026 — and what changed

    Three things shifted:

    1. Astronomer’s Cosmos library matured. Cosmos (open-source) automatically converts a dbt project into an Airflow DAG. Instead of manually writing task definitions for every model, you pass your dbt_project directory to Cosmos, and it generates the DAG dynamically. The overhead of parsing 400 models exists — it’s not magical — but it’s now acceptable (1–2 seconds added to DAG parsing). Not free, but not expensive.

    2. Snowflake native dbt Projects arrived. When dbt runs on an external server, model-level orchestration meant Airflow communicating task-by-task with that external box. Latency overhead. Credential management complexity. Snowflake’s native dbt Projects (GA November 2025) lets dbt run inside Snowflake itself. Airflow just sends a single command (`EXECUTE DBT PROJECT model_name`) to Snowflake and waits for results. The execution is native. The communication is just REST API calls. The overhead drops significantly.

    3. Orchestration became about observability, not infrastructure. Teams in 2026 have stopped asking “what’s the performance impact?” and started asking “can I see failures at model granularity?” The answer is yes, and the cost is no longer real. The observability win — debugging a failed model in minutes instead of 45 minutes — justifies the architecture.

    Real benchmark: 400-model project, production traffic

    A data team running Airflow on AWS (3x m5.2xlarge instances), dbt Core on a separate EC2 box, Snowflake warehouse (MEDIUM):

    Before (job-level + external dbt): `dbt run –select tag:daily` runs once per day. Entire job as one Airflow task. 18 minutes wall-clock time. Failed model buries itself in the dbt run log. Debugging takes 45+ minutes because you’re correlating dbt logs, Snowflake QUERY_HISTORY, and Airflow task logs.

    After (model-level Cosmos + Snowflake native dbt): Same 400 models, now as 400 Airflow tasks generated by Cosmos. Parallel execution on the MEDIUM warehouse runs up to 8 models at a time. 12 minutes wall-clock time (33% faster). Failed model shows up in Airflow UI as a red task. Click it. See the exact model that failed, the SQL compile error, the exact line. Debugging takes 8 minutes.

    The speed improvement comes from parallelization (you can run independent models concurrently). The debugging improvement comes from per-model observability. Both were impossible before because the overhead was too high. Not anymore.

    How to orchestrate Snowflake native dbt Projects from Airflow

    Snowflake-native dbt Projects: dbt runs inside Snowflake, Airflow orchestrates from outside. Simpler infrastructure, unified observability.

    Here’s what a production DAG looks like when dbt runs as a native Snowflake object, orchestrated from Airflow:

    from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakePythonOperator
from airflow.providers.slack.operators.slack_webhook import SlackWebhookOperator
from datetime import datetime

default_args = {
'owner': 'analytics',
'retries': 2,
'retry_delay': timedelta(minutes=5),
}

with DAG(
'snowflake_dbt_daily_run',
default_args=default_args,
schedule_interval='0 2 * * *', # 2 AM daily
start_date=datetime(2026, 1, 1),
catchup=False,
) as dag:

# Raw data load (external tool or Airflow operator)
load_raw = SnowflakePythonOperator(
task_id='load_raw_data',
python_callable=load_from_source, # your extraction logic
)

# Run dbt transformations as a native Snowflake DBT PROJECT
run_dbt = SnowflakePythonOperator(
task_id='dbt_transform',
python_callable=execute_dbt_project,
op_kwargs={
'sql_command': 'EXECUTE DBT PROJECT analytics_db.transforms',
'database': 'analytics_db',
},
)

# Export or activate downstream (BI, ML, etc.)
notify_success = SlackWebhookOperator(
task_id='notify_success',
http_conn_id='slack_webhook',
message='Daily dbt transforms completed successfully',
)

load_raw >> run_dbt >> notify_success

    The key line is `EXECUTE DBT PROJECT analytics_db.transforms`. That command runs inside Snowflake. Airflow waits for it to complete. Logs come back to Airflow. All in one place.

    Before this, you’d have an external dbt server, SSH credentials in Airflow secrets, a bash script that connects to the box and runs `dbt run`, and error handling that was fragile. Now it’s a direct REST API call to Snowflake.

    Setup: Snowflake side (one-time)

    Create the dbt project object in Snowflake. One time. Then Airflow orchestrates it:

    -- As SYSADMIN or higher
USE ROLE SYSADMIN;

-- Create a dedicated role and user for Airflow
CREATE ROLE IF NOT EXISTS dbt_executor_role;
CREATE USER IF NOT EXISTS airflow_svc_user
DEFAULT_ROLE = dbt_executor_role
DEFAULT_WAREHOUSE = dbt_transform_wh;

-- Create a dedicated warehouse for dbt runs
CREATE OR REPLACE WAREHOUSE dbt_transform_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

-- Grant permissions to Airflow's service account
GRANT ALL ON WAREHOUSE dbt_transform_wh TO ROLE dbt_executor_role;
GRANT USAGE ON DATABASE analytics_db TO ROLE dbt_executor_role;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA analytics_db.staging TO ROLE dbt_executor_role;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA analytics_db.marts TO ROLE dbt_executor_role;

-- Grant the crucial permission: execute dbt projects in that schema
GRANT EXECUTE DBT PROJECT ON SCHEMA analytics_db.transforms TO ROLE dbt_executor_role;

-- Grant the role to your service user
GRANT ROLE dbt_executor_role TO USER airflow_svc_user;

    Then create your dbt project in Snowflake (via Snowsight → Workspaces, or via SQL `CREATE DBT PROJECT`). That’s it on the Snowflake side.

    The three gotchas you’ll hit

    Gotcha 1: Forgetting schema-level EXECUTE permissions. The `GRANT EXECUTE DBT PROJECT ON SCHEMA` is the easy line to miss. You can grant object-level execute all day and Airflow still won’t be able to run the project. It’s schema-level that matters.

    Gotcha 2: dbt docs and artifacts not flowing back to Airflow. When dbt runs inside Snowflake, the manifest.json and dbt_project.yml artifacts stay inside Snowflake. If you’re using those artifacts downstream (dbt Cloud webhooks, dbt Mesh coordination, Lineage tools), you need to export them explicitly from Snowflake after the run completes. Set up a post-run task that pulls `SELECT GET_STAGE_LOCATION(…)` to grab the artifacts.

    Gotcha 3: Incremental models and first-run confusion. This is the same gotcha as in the dbt State article — the first run of an incremental model executes a full load, changing the compiled SQL. dbt State knows about this. Airflow doesn’t. Expect a full downstream rebuild on Run 2. Normal behavior. Just know it coming in.

    When to use job-level, when to use model-level

    Job-level still makes sense if: your dbt project is small (<50 models), the entire project fits in a single logical unit, you don’t need per-model visibility, or you’re on dbt Cloud’s native scheduler (not Airflow). Keep it simple.

    Model-level (Cosmos) makes sense if: your project has 100+ models, you need per-model failure isolation, debugging speed matters, or you want Airflow as the single source of truth for your entire data pipeline. Most production teams in 2026 are here.

    The hybrid: Some teams run both. Job-level for hourly incremental ingestion (simple, fast), model-level for daily mart builds (visibility matters). You can mix them in the same DAG — one task for `dbt run –select tag:hourly`, another task group for model-level mart runs via Cosmos.

    The one principle

    Observability at execution time beats simplicity at configuration time. Job-level is simpler to configure. Model-level is simpler to debug. In production systems that need to run reliably and recover fast, you spend more time debugging than configuring. Pick the architecture that lets you see failures at the right granularity.

    Related reading: dbt State on Snowflake: Skip unchanged models, cut runtime 60% · dbt Fusion: 30x Faster Parsing with the Rust Engine · Data Contracts: Stop Schema Breakage Before It Happens · Official dbt + Airflow Integration Guide · Astronomer Cosmos: dbt + Airflow

  • Delta Lake vs Apache Iceberg — Why I Chose Iceberg for Our Data Lakehouse

    Delta Lake vs Apache Iceberg — Why I Chose Iceberg for Our Data Lakehouse

    TL;DR
    → Delta Lake is easier to start with, especially if you’re already on Databricks
    → Iceberg wins on engine flexibility — works natively with Spark, Flink, Trino, Snowflake, and more without custom connectors
    → Delta Lake’s vendor coupling with Databricks is a real cost if you’re multi-cloud or multi-engine
    → Iceberg’s partition evolution lets you change partition schemes without rewriting data — that feature alone saved us a full weekend of migration work
    → Migration from Delta to Iceberg is harder than most blog posts suggest — budget four to eight weeks, not a weekend
    → If you’re greenfield, start with Iceberg. If Delta is working, don’t migrate until you hit a specific limit

    I didn’t choose Iceberg because I read a benchmark blog post. I chose it after six months of hitting Delta Lake’s limits in ways that weren’t obvious until they were expensive.

    We were running a mid-sized data lakehouse — S3-backed, Spark for processing, Snowflake for consumption, dbt for transformation. Delta Lake was the default choice. Everyone on the team had used it before. The documentation was solid. It worked — until it didn’t.

    This isn’t a “here are the specs” comparison. You can get that from the docs. This is what actually happened when I ran both in production, why I made the switch, and what I’d tell you before you pick one.

    What We Were Actually Trying to Solve

    Before I get into the comparison, context matters. Our stack at the time: raw data landing in S3, Apache Spark for heavy transformation, Snowflake as the consumption layer for analysts, dbt for modeling, and Apache Airflow for orchestration.

    We needed ACID transactions on S3, time travel for debugging, and the ability to do incremental loads without full partition rewrites. Delta Lake checked all those boxes — initially. The problems showed up at scale and at the edges.

    Where Delta Lake Started Hurting Us

    Engine Lock-In Was a Real Problem

    Delta Lake works great if Spark is your only compute engine. The moment we tried to query Delta tables directly from Snowflake or Trino, things got complicated. Delta’s transaction log format is proprietary. You need the Delta connector — and not every engine has a first-class one.

    We wanted analysts to query raw lakehouse tables directly from Snowflake without going through Spark first. With Delta, that required Snowflake’s Delta Sharing integration, which had limitations on what operations were supported. It wasn’t broken, but it added friction and another dependency to manage.

    Apache Iceberg solves this cleanly. The table format is open. Snowflake, Spark, Flink, Trino, Athena, Dremio — they all read and write Iceberg natively. No connectors to manage. No format translation layer.

    Partition Management Was Getting Messy

    With Delta Lake, partitioning decisions are set at table creation. Changing a partition scheme means rewriting the table. At 100M+ rows, that’s not a quick operation.

    We had a table partitioned by event_date. Six months in, query patterns changed — analysts were filtering by event_date and region together. Repartitioning meant a full backfill job over a weekend, plus repointing all downstream dbt models.I wrote about a similar pain point in the problem with dbt incremental models — the pattern is the same.

    Iceberg’s partition evolution lets you change the partition spec without rewriting data. Old data stays as-is. New data uses the new scheme. Queries still work against both.

    Hidden Partitioning Changed How We Design Tables

    Iceberg supports hidden partitioning — you define partition transforms like days(event_timestamp) or bucket(user_id, 16) and Iceberg handles physical partitioning transparently. Your queries don’t need to know about partition columns. The engine prunes automatically.

    With Delta Lake, you need to explicitly filter on partition columns or you’ll scan everything. That’s fine when everyone knows the rules. It’s a problem when a new analyst writes a query without knowing which columns are partition keys.

    Where Delta Lake Is Still Better

    If you’re on Databricks, stay on Delta. The integration is tight, the tooling is mature, and Databricks has invested heavily in Delta’s performance.. Liquid Clustering makes partition management much more flexible. If Databricks is your primary compute layer, switching to Iceberg gives you marginal benefit for non-trivial migration cost.

    Delta’s MERGE performance on Spark is excellent. For high-frequency CDC workloads where you’re doing upserts at scale on Spark, Delta’s MERGE implementation is well-optimised. Iceberg’s MERGE has improved significantly but Delta still has an edge in some Spark-specific CDC patterns.

    Delta has simpler operational overhead for small teams. Delta’s transaction log is easier to reason about. The tooling for vacuum, optimize, and Z-ordering is well-documented and predictable.

    The Comparison You Actually Need

    Feature Delta Lake Apache Iceberg
    Engine supportSpark-native; connectors for othersTruly multi-engine (Spark, Flink, Trino, Snowflake, Athena)
    Partition evolutionRequires full table rewriteSchema-safe, no data rewrite needed
    Hidden partitioningNot supportedSupported — engines auto-prune
    MERGE / CDC performanceExcellent on SparkStrong, improving; slightly behind Delta on Spark CDC
    Vendor alignmentDatabricks ecosystemVendor-neutral, Apache foundation
    Operational toolingMature, well-documentedMaturing fast; strong in 2024–2025
    Multi-cloud flexibilityPossible but frictionFirst-class support across clouds
    Migration effortN/A (starting point)Non-trivial; plan 4–8 weeks

    THE MIGRATION: WHAT IT ACTUALLY COST US

    The Migration: What It Actually Cost Us

    I’ll be direct: the migration was harder than I expected. If you’ve read my piece on automation in data engineering, you’ll recognise the pattern — the technical part is rarely the hard part. It’s the downstream work nobody accounts for.

    The core work wasn’t the data conversion — we used the delta-iceberg migration utility and it handled most of the heavy lifting. The harder parts were everything else.

    Downstream dependency mapping.

     Every dbt model, every Airflow DAG, every Spark job that referenced a Delta table path needed updating. We had 40+ models. Two had hardcoded partition paths we didn’t catch until QA.

    Metadata catalog updates.

     We use AWS Glue Data Catalog. Every table needed its metadata updated to reflect the Iceberg format. Glue’s Iceberg support has improved, but it’s not frictionless.

    Testing the rollback plan. We kept Delta tables live for 30 days post-migration with a cutover switch in Airflow. That meant double-writing during the transition window — additional storage cost and added pipeline complexity.

    ⚠️ The migration trap: The data conversion tooling works. What catches teams off guard is the downstream mapping work — every pipeline, model, and job that references a table path. Budget more time for that than for the actual format conversion.

    Total elapsed time: six weeks. Two engineers. Not a weekend project.

    When to Choose Delta Lake

    • Your primary compute layer is Databricks
    • You’re a small team that wants simpler operations
    • You’re doing high-frequency CDC on Spark
    • You’re early stage — get something working first

    When to Choose Iceberg

    • You’re running multiple query engines (Spark + Snowflake, Trino + Flink)
    • You need partition evolution without full table rewrites
    • You’re building a vendor-neutral architecture
    • Your analysts query the lakehouse directly from Snowflake

    What I’d Do Differently

    Start with Iceberg if you’re greenfield. The setup is slightly more involved, but you avoid the migration cost entirely. The ecosystem has matured enough in 2024-2025 that “Iceberg is less mature” is no longer a strong argument.

    If you’re already on Delta and it’s working — don’t migrate for the sake of it. Migrate when you hit a specific limit: engine lock-in, partition inflexibility, or multi-cloud requirements.

    And if you do migrate, don’t underestimate the downstream mapping work. The data conversion is the easy part.

    Frequently Asked Questions

    What is the main difference between Delta Lake and Apache Iceberg?

    Delta Lake is a table format developed by Databricks, optimised for Spark workloads with strong Databricks integration. Apache Iceberg is an open table format designed for multi-engine environments — it works natively with Spark, Flink, Trino, Snowflake, and Athena without custom connectors. The core difference is engine flexibility.

    Is Apache Iceberg better than Delta Lake?

    It depends on your stack. Iceberg is better if you’re running multiple query engines or building a vendor-neutral architecture. Delta Lake is better if Databricks is your primary compute layer. Neither format is objectively superior.

    Can Snowflake read Delta Lake tables?

    Yes, through Delta Sharing or Snowflake’s Delta connector — but with limitations. Snowflake reads Iceberg tables natively as a first-class citizen, which is why multi-engine stacks tend to favour Iceberg.

    How hard is it to migrate from Delta Lake to Apache Iceberg?

    Harder than most blog posts suggest. The data conversion tooling handles the format migration, but remapping downstream pipelines, updating metadata catalogs, and testing rollback scenarios adds significant effort. Budget four to eight weeks for a production migration with 30–50 tables.

    Does dbt support Apache Iceberg?

    Yes. dbt supports Iceberg through the Spark and Athena adapters, and Snowflake’s Iceberg table support works with dbt models running on Snowflake. Production-ready as of 2024.

    What is hidden partitioning in Apache Iceberg?

    Hidden partitioning lets Iceberg manage partition logic transparently. You define partition transforms like days(event_timestamp) at the table level, and Iceberg handles physical file organisation and query pruning automatically — no need to filter on partition columns explicitly.

  • The Problem with Data Engineering Certifications That Nobody Talks About

    The Problem with Data Engineering Certifications That Nobody Talks About

    I passed the SnowPro Gen AI certification not too long ago. Within the same week I was back at my desk staring at a broken pipeline that no multiple-choice question had ever prepared me for. The cert looked great on my profile. It fixed exactly nothing about the actual problem in front of me.

    I’m not saying certifications are worthless. I’m saying the industry has developed a quietly dishonest relationship with them — one where vendors, hiring managers, and candidates all play along with a fiction that a passed exam means something it doesn’t. Nobody wants to be the one to say it out loud.

    So I will. Let me be direct about what’s actually going on.

    TL;DR

    • Certifications test what vendors want you to know about their products — not whether you can actually engineer data systems that work under real conditions
    • The exam content is often months or years behind the tools you’ll actually use in production
    • Hiring managers use certs as a filter because it’s easy — not because it’s accurate
    • You can pass most data engineering certs with two weeks of practice exams and zero production experience
    • The real signal employers should care about — and rarely do — is what you’ve built, what broke, and what you learned from it
    • Certifications have a specific, narrow value: they are a vocabulary test, not a competence test. Know what you’re paying for

    WHAT CERTIFICATIONS ACTUALLY TEST

    Let’s start with what’s literally on the exam. Take the Databricks Certified Data Engineer Associate . The exam covers Delta Lake concepts, basic Spark operations, Unity Catalog, Databricks workflows. Good things to know.

    But the exam tests your ability to identify the correct answer from four options in a controlled environment. It does not test whether you can debug a production Spark job that’s been running for six hours and slowly consuming memory. It doesn’t test whether you can diagnose why a Delta merge is creating file fragmentation degrading query performance. It doesn’t test whether you can architect a pipeline that recovers gracefully when an upstream API starts returning malformed JSON at 3am.

    Those are the problems data engineers actually face. None of them are in the certification.

    A certification tells you that someone understood the conceptual framework of a product well enough to pass a vendor-designed exam. It tells you almost nothing about their ability to operate that product under adversarial conditions. And production is always adversarial.

    This gap exists in the AWS Certified Data Engineer Associate ,the Google Professional Data Engineer ,the Azure Data Engineer Associate ,and every dbt or Snowflake certification available. They all test the vendor’s idealised scenario.

    Real pipelines are never idealised.

    THE VENDOR INCENTIVE PROBLEM

    Who designs these exams? The vendors. Who benefits when thousands of engineers study for, pay for, and pass these exams? The vendors. Certification programmes are not primarily educational products. They are marketing products that create a credentialled user base and deepen platform lock-in.

    When Snowflake designs its certification exams ,the goal is not to produce engineers who can evaluate whether Snowflake is the right tool. The goal is to produce engineers deeply familiar with Snowflake’s architecture, syntax, and product positioning — engineers who will advocate for Snowflake when tooling decisions come up at their company.

    The exam content is shaped by commercial interest, not by what data engineers actually need to know. The practical consequence: certifications optimise for breadth of product knowledge over depth of engineering judgment. You learn feature names, service limits, and recommended architectures. You don’t develop the instinct that tells you something is going to break before it breaks.

    THE HIRING MANAGER TRAP

    I’ve sat in hiring discussions where a candidate without certifications was dismissed faster than one with a string of logos after their name, despite the uncertified candidate having a demonstrably stronger GitHub portfolio and much more interesting answers about production incidents they’d owned.

    Certifications persist in job postings because they’re easy to verify and hard to argue with. A cert is binary. Either you have it or you don’t. Technical judgment, architecture instinct, debugging ability — these require effort to assess.

    ⚠️ The signal problem: If you can pass a data engineering certification with two weeks of practice exams and no production experience — and you can — then having the certification tells an interviewer almost nothing about whether you can do the job. It tells them you can study for a test. That’s useful. But it’s not the same thing.

    The engineers most dismissive of certifications are often the most experienced. The engineers who lean most heavily on cert lists are often the ones who haven’t done enough production work to know what the gap actually looks like.

    THE STALE CONTENT PROBLEM

    Data engineering moves fast. The tooling landscape in 2024 looks materially different from 2021. dbt Core has changed substantially. Apache Iceberg has gone from niche to mainstream. Lakehouse architecture has shifted from concept to default.

    Certification exams do not move at this speed. Exam content is updated infrequently — sometimes annually, sometimes less. You can hold an AWS Data Engineer cert that emphasises EMR and Glue in patterns most teams have replaced with more modern tooling. You can hold a Databricks cert that doesn’t reflect how Unity Catalog has fundamentally changed governance.

    The cert is not wrong. It’s just dated. And dated knowledge in data engineering isn’t neutral — it can actively mislead you about how things should be built.

    I wrote about a related version of this in “Why I Stopped Using Snowflake Tasks for Orchestration” — official documentation and certification content often lags behind what practitioners have already learned through trial and error in production.

    WHAT YOU ACTUALLY LEARN WHEN YOU STUDY FOR A CERT

    Here’s the part I want to be fair about. Studying for a data engineering certification isn’t worthless. It’s just worth something different from what most people think.

    When you study for the Google Professional Data Engineer exam, you learn the GCP data ecosystem — BigQuery, Dataflow, Pub/Sub, Cloud Composer, Dataproc — in a structured way. You develop a vocabulary. You understand how services relate to each other.

    What it doesn’t give you is judgment. Judgment about when to use Dataflow versus Dataproc. When BigQuery’s cost model makes it the wrong tool despite its performance. When a simple Cloud Function is a better answer than a fully orchestrated pipeline.

    The honest framing: a certification is a vocabulary test with a structured curriculum. If you’ve never worked on a platform and need to get up to speed quickly, studying for the cert is efficient. If you already have production experience, the cert adds limited signal beyond what’s already on your resume.

    THE PRACTICE EXAM LOOPHOLE NOBODY WANTS TO DISCUSS

    Most data engineering certifications can be passed with aggressive practice exam grinding and minimal practical experience. Platforms like Udemy , Whizlabs and ExamTopics sell practice exam bundles close enough to real questions that a disciplined studier can reverse-engineer most of the exam in two to three weeks.

    I’ve seen candidates with zero Snowflake production experience pass the SnowPro Core exam in a week of evening study. I’ve seen engineers memorise their way through the AWS Data Engineer Associate without writing a single Glue job. The credential is indistinguishable from someone who earned it through genuine depth.

    The vendors know this. They update exam content periodically to counter braindump culture, but it’s an arms race they’re perpetually losing.

    WHAT ACTUALLY SIGNALS ENGINEERING COMPETENCE

    If I’m hiring a data engineer, here’s what I actually want to see.

    Tell me about a pipeline that broke in production. Not a hypothetical. What broke, how you found out, what the root cause was, how you fixed it, what you changed to prevent recurrence. This conversation reveals more engineering judgment than any certification.

    Show me something you built. A GitHub repo .A dbt project. A pipeline architecture diagram with a written explanation. The work I’ve been documenting — from the problem with dbt incremental models to Snowflake zero-copy cloning gotchas — is far more useful signal than any certification I hold.

    Tell me about a technical decision you disagreed with. Engineering judgment includes knowing when to push back, when to compromise, how to argue for a position with evidence. No cert tests this.

    Walk me through how you’d approach this problem. Give them a real scenario — a data quality issue, a cost spike, a schema migration in a live system. Watch how they think, not just what they know.

    The gap between what certifications measure and what engineering competence looks like is large enough that I’d rather see zero certifications with a detailed post-mortem of a real incident than four certs with nothing to show for the work.

    WHEN CERTIFICATIONS ARE ACTUALLY WORTH PURSUING

    You’re breaking into the field. If you’re transitioning into data engineering, certifications serve a genuine purpose. They give you structured curriculum and a credential that signals seriousness to employers who don’t yet have anything else to evaluate you on.

    Your employer requires it. Many enterprise organisations and consulting firms have vendor partnership requirements mandating certified staff levels. In that case, the cert has real organisational value regardless of signal quality.

    You’re learning a new platform systematically. Using cert study as structured onboarding to a new tool is legitimate. The curriculum forces breadth coverage self-directed learning often misses. Just know that completing the cert doesn’t mean you know how to use the platform well.

    You’re in a market where it’s table stakes. In some geographies and sectors, certain certs are required to get an interview. Clear the gate, then demonstrate real depth in the room.

    The certification isn’t the problem. The mythology around it is. The idea that passing the exam means you can build reliable data systems — that’s the fiction that causes real damage.

    WHAT THE INDUSTRY SHOULD DO INSTEAD

    Portfolio-based evaluation. A documented data engineering project — architecture decisions, tradeoffs, failures encountered — tells a hiring team far more than an exam score. GitHub already supports this.

    Incident post-mortems as credentials. A well-written post-mortem demonstrates debugging methodology, systems thinking, and the ability to learn from failure. No certification tests these.

    Practical assessments over multiple choice. The Databricks Data Engineer Professional is harder than most — it has a coding component requiring actual proficiency. More exams should work this way.

    Open curriculum from neutral sources. The Data Engineering Handbook and open-source community resources are doing more for actual engineering capability than most vendor certification programmes.

    FREQUENTLY ASKED QUESTIONS

    Are data engineering certifications worth it in 2024?
    It depends on where you are in your career. For someone entering the field, certs provide structured curriculum and a credential that signals seriousness. For experienced engineers, your production track record carries far more weight with strong technical hiring teams. Certs are worth what they cost if you understand what they are: a vocabulary test, not a competence test.

    Which data engineering certification is the most respected?
    Among practitioners, the Databricks Data Engineer Professional is generally seen as harder and more meaningful because it includes a practical component. Google Professional Data Engineer has strong enterprise name recognition. AWS Certified Data Engineer Associate is widely recognised in cloud-native teams. But respected by whom matters — strong engineering teams care less about cert logos than about demonstrated ability.

    Can you become a data engineer without certifications?
    Absolutely. Many strong data engineers have no certifications at all. A track record of real work — systems built, incidents resolved, architectural decisions owned — is equally or more compelling to technical hiring teams worth impressing.

    How long does it take to pass data engineering certification exams?
    Most candidates report 2–6 weeks of focused study. With aggressive practice exam preparation, some pass in under two weeks — which is part of what makes the credentials less meaningful than they appear.

    Do data engineering certifications expire?
    Yes. AWS certifications expire after three years, Google Cloud after two, Databricks varies by level. Recertification tends to be easier than initial certification and often doesn’t reflect how dramatically the tooling has evolved.

    What should a data engineering portfolio include instead of certifications?
    End-to-end pipeline projects with documented architecture decisions. Written post-mortems of production incidents. Data quality testing approaches. dbt projects with meaningful transformation logic. Cost analyses or performance optimisations from real environments. Anything that shows how you think, not just what tools you’ve touched.

  • How to Query Snowflake in DuckDB (And Cut Your Bill While Doing It)

    How to Query Snowflake in DuckDB (And Cut Your Bill While Doing It)

    TL;DR

    • Snowflake’s 60-second minimum billing means a 4-second query gets charged for a full minute — you’re paying for 55 seconds of nothing
    • You can query Snowflake data in DuckDB via two routes: Iceberg tables on S3 (no warehouse needed) or ADBC using Apache Arrow (up to 38x faster than ODBC)
    • Once data is local in DuckDB, every subsequent query is free — no cloud credits consumed
    • A hybrid triage approach (short queries → DuckDB/MotherDuck, heavy ETL → Snowflake) cuts BI compute costs by 70–90% in practice
    • Dev and CI/CD workloads moved to local DuckDB eliminate an entire category of cloud spend entirely

    I’ve been building on Snowflake long enough to know the ritual. Warehouse wakes up. Query runs in three seconds. Warehouse idles. You get billed for sixty seconds anyway. Multiply that by every analyst, every BI dashboard refresh, every dbt run in your dev environment — and suddenly you’re staring at a bill that feels completely disconnected from the actual work that happened.

    For a long time I assumed this was just the price of doing business on a best-in-class cloud warehouse. What I didn’t realise — until I started taking DuckDB seriously — is that a meaningful chunk of that bill doesn’t have to exist at all.

    This article covers three concrete methods to get Snowflake data into DuckDB, the cost math behind why you’d want to, and how to decide what actually belongs on which engine.

    THE REAL PROBLEM: YOU’RE PAYING FOR COMPUTE YOU DIDN’T USE

    Snowflake bills compute per second — but only after a 60-second minimum each time a warehouse resumes from suspension. A query that takes five seconds gets billed for a full minute. You paid for 55 seconds of nothing.

    It gets worse at scale. When a BI dashboard fires 20 queries on load, each taking three seconds, that single page view triggers 1,200 seconds of billed compute time. The actual work? One minute.

    And then warehouse sizing compounds it further. Each size increase in Snowflake doubles credit consumption. Teams defaulting to Medium or Large for everything are paying a 4x to 8x cost premium for workloads that could run perfectly well on X-Small.

    I’ve seen this exact pattern on almost every Snowflake environment I’ve worked in. Oversized warehouse, auto-suspend set to ten minutes, no resource monitors, nobody looking at query history.

    QUICK WINS INSIDE SNOWFLAKE FIRST

    Before touching the architecture, fix the obvious things. These alone can cut spend by 20–40%.

    Set AUTO_SUSPEND to exactly 60 seconds. Not lower — setting it below 60 is counterproductive because a query arriving in that first minute triggers another 60-second minimum. Not higher — every idle second past 60 is wasted money.

    Default to X-Small warehouses. Only scale up when a specific workload has a documented SLA that requires it.

    Add resource monitors:

    CREATE OR REPLACE RESOURCE MONITOR monthly_etl_monitor
WITH CREDIT_QUOTA = 5000
TRIGGERS ON 75 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE etl_heavy_wh 
SET RESOURCE_MONITOR = monthly_etl_monitor;

    METHOD 1 — QUERYING SNOWFLAKE ICEBERG TABLES DIRECTLY IN DUCKDB

    If your organisation has moved to Iceberg tables with underlying data stored in S3, you can read those tables directly in DuckDB — no Snowflake warehouse running, no credits consumed.

    Install the extensions:

    INSTALL httpfs;
LOAD httpfs;
INSTALL iceberg;
LOAD iceberg;

    Configure AWS credentials:

    CREATE SECRET (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);

    Find the current metadata file for your Snowflake-managed Iceberg table:

    SELECT PARSE_JSON(
  SYSTEM$GET_ICEBERG_TABLE_INFORMATION('YOUR_DB.YOUR_SCHEMA.YOUR_TABLE')
)['metadataLocation']::varchar;

    Query it in DuckDB:

    SELECT
    customer_id,
    COUNT(*)
FROM iceberg_scan('s3://your-bucket/path/to/metadata/00001-xxxx.metadata.json')
GROUP BY 1;

    Materialise once for fast repeated queries:

    CREATE TABLE payments AS 
SELECT * FROM iceberg_scan('s3://your-bucket/.../metadata.json');

    After this: same aggregation runs in 1.5s instead of 54s.

    Real benchmark: a SELECT * on a 110-million row table finished in 29 seconds in DuckDB on an M1 MacBook. Same query on an X-Small Snowflake warehouse took 72 seconds.

    The honest limitation: DuckDB’s Iceberg support is still maturing. You need direct S3 access and have to point DuckDB at a specific metadata file rather than a catalog. This will improve over time, but it works today.

    METHOD 2 — QUERYING NATIVE SNOWFLAKE TABLES VIA ADBC

    Not on Iceberg yet? ADBC (Arrow Database Connectivity) is the right tool here.

    Apache Arrow is a columnar memory format. When you connect Snowflake to DuckDB via ADBC, data stays columnar the entire way. Traditional ODBC forces Snowflake to convert columnar → row for transfer, then DuckDB converts row → columnar for processing. DuckDB’s benchmarks show ADBC is up to 38x faster than ODBC.

    Install:

    pip install adbc_driver_snowflake pyarrow duckdb cryptography

    Connect to Snowflake and pull data as an Arrow table:

    import adbc_driver_snowflake.dbapi
import duckdb
import os
from read_private_key import read_private_key

SNOWFLAKE_CONFIG = {
    'adbc.snowflake.sql.account': os.getenv('SNOWFLAKE_ACCOUNT'),
    'adbc.snowflake.sql.warehouse': os.getenv('SNOWFLAKE_WAREHOUSE'),
    'adbc.snowflake.sql.role': os.getenv('SNOWFLAKE_ROLE'),
    'adbc.snowflake.sql.database': os.getenv('SNOWFLAKE_DATABASE'),
    'username': os.getenv('SNOWFLAKE_USER'),
    'adbc.snowflake.sql.client_option.jwt_private_key_pkcs8_value': pem_key,
    'adbc.snowflake.sql.auth_type': 'auth_jwt'
}

snowflake_conn = adbc_driver_snowflake.dbapi.connect(
    db_kwargs={**SNOWFLAKE_CONFIG}
)

snowflake_cursor = snowflake_conn.cursor()
snowflake_cursor.execute("SELECT * FROM SANDBOX_DB.MY_SCHEMA.RAW_ORDERS")

# Fetch as Arrow table — stays columnar, no serialisation overhead
arrow_table = snowflake_cursor.fetch_arrow_table()

# Persist locally in DuckDB
duckdb_conn = duckdb.connect('demo.db')
duckdb_conn.execute("""
    CREATE TABLE IF NOT EXISTS raw_orders AS 
    SELECT * FROM arrow_table
""")

    One heads-up: figuring out the connection parameters using a private key is not straightforward — the docs aren’t great on this point. The private key needs to be re-encoded into PEM format before passing it to the ADBC driver:

    from cryptography.hazmat.primitives import serialization

def read_private_key(private_key_path: str, private_key_passphrase: str = None) -&gt; str:
    with open(private_key_path, 'rb') as key_file:
        private_key = serialization.load_pem_private_key(
            key_file.read(),
            password=private_key_passphrase.encode() if private_key_passphrase else None
        )
        pem_key = private_key.private_bytes(
            encoding=serialization.Encoding.PEM,
            format=serialization.PrivateFormat.PKCS8,
            encryption_algorithm=serialization.NoEncryption()
        )
        return pem_key.decode('utf-8')

    Once that’s sorted, the workflow is clean: pull data from Snowflake via ADBC once, materialise it locally in DuckDB, query it as many times as you want — zero Snowflake credits consumed after the initial pull.

    METHOD 3 — THE HYBRID ARCHITECTURE: ROUTE WORKLOADS BY TYPE

    The two methods above are great for development and ad-hoc analysis. For production BI workloads, the cleanest solution I’ve seen is a hybrid architecture where you triage queries by workload type.


    The insight that unlocked this for me was using Snowflake’s query_history to actually categorise what’s running:

    WITH query_stats AS (
    SELECT
        warehouse_name,
        user_name,
        query_id,
        execution_time / 1000 AS execution_seconds
    FROM snowflake.account_usage.query_history
    WHERE
        start_time &gt;= DATEADD('day', -30, CURRENT_TIMESTAMP())
        AND warehouse_name IS NOT NULL
        AND execution_status = 'SUCCESS'
)
SELECT
    warehouse_name,
    user_name,
    COUNT(query_id) AS query_count,
    MEDIAN(execution_seconds) AS median_execution_seconds,
    CASE
        WHEN query_count &gt; 1000 AND median_execution_seconds &lt; 30 
            THEN 'Interactive BI / High Frequency'
        WHEN query_count &lt;= 1000 AND median_execution_seconds &lt; 60 
            THEN 'Ad-Hoc Exploration'
        WHEN median_execution_seconds &gt;= 300 
            THEN 'Batch ETL / Heavy Analytics'
        ELSE 'General Purpose'
    END AS workload_category
FROM query_stats
GROUP BY warehouse_name, user_name
ORDER BY query_count DESC;

    Use MEDIAN not AVG — outlier queries skew the average and give a misleading picture of typical duration.

    Routing logic:

    • Short and bursty BI (sub-30s, high frequency) → move to usage-based engine. Real math: $528/month on Snowflake X-Small running continuously vs $5.87/month on per-second billing for the same workload.
    • Dev and CI/CD → local DuckDB, zero cloud credits
    • Heavy batch ETL, multi-TB → keep on Snowflake, 60s minimum is irrelevant for hour-long jobs

    This is the same principle I apply when thinking about orchestration — use the right tool for the job, not the most powerful tool for everything. I wrote about a similar decision process in why I stopped using Snowflake Tasks for orchestration — the short version is that mature orchestration tools give you far more control over exactly this kind of workload routing.

    WHEN TO STAY ON SNOWFLAKE

    Multi-terabyte batch processing — predictable provisioned compute matters more than idle cost savings when a job runs for hours.

    Enterprise governance — complex data masking, RBAC at scale, data residency requirements. Snowflake’s security surface is mature. DuckDB isn’t designed for this.

    Already-efficient workloads — if a warehouse runs at high utilisation for 8 hours straight, there’s no idle tax to eliminate. Don’t fix what isn’t broken.

    WHAT REAL COST SAVINGS LOOK LIKE

    • One SaaS company: 70%+ reduction in warehousing costs after moving to DuckDB-based solution
    • Okta: $60,000/month Snowflake spend for threat detection reduced substantially using parallel DuckDB instances
    • A data engineering team: 79% immediate reduction in Snowflake BI spend using DuckDB as a caching layer, 7x faster query times

    None of these required abandoning Snowflake. They required deciding which workloads actually needed it.

    FREQUENTLY ASKED QUESTIONS

    Can you query Snowflake data in DuckDB without a Snowflake warehouse running?
    Yes — two ways. Iceberg tables via the iceberg extension (no warehouse), or native tables via ADBC. Both require a brief initial connection, but once data is materialised locally, all subsequent queries are free.

    What is ADBC and why is it faster than ODBC?
    ADBC keeps data in columnar format throughout. ODBC forces columnar → row → columnar conversion. DuckDB benchmarks show ADBC up to 38x faster for transfers.

    How much can I realistically save?
    For short, high-frequency dashboard queries: 70–90% is consistent across documented cases. The 60-second minimum means a 4-second query costs 15x what it should.

    Is DuckDB production-ready?
    For single-node analytical workloads under a few terabytes: yes. Multi-user concurrency at scale and enterprise governance: not yet.

    Do I need Iceberg?
    No. ADBC works with native Snowflake tables. The main friction is private key encoding, which the docs don’t explain well.

    Will this work with dbt?
    Yes. dbt-duckdb lets you run your full dbt project locally against DuckDB. Pull source data once from Snowflake, develop and test for free, deploy to Snowflake in production only.This eliminates cloud compute costs for the entire development loop. I’ve written about dbt native projects and pipeline patterns if you want more context on how this fits into a Snowflake-first stack.

    Related blogs :

    → DuckDB official docs and installation
    → DuckDB ADBC benchmarks
    → Apache Arrow project
    → Snowflake query_history view docs
    → dbt-duckdb adapter on GitHub
    → Greybeam ADBC connection code on GitHub

  • AWS Data Pipeline Cost Optimization Strategies

    AWS Data Pipeline Cost Optimization Strategies

     Building a powerful data pipeline on AWS is one thing. Building one that doesn’t burn a hole in your company’s budget is another. As data volumes grow, the costs associated with storage, compute, and data transfer can quickly spiral out of control. For an experienced data engineer, mastering AWS data pipeline cost optimization is not just a valuable skill—it’s a necessity.

    Optimizing your AWS bill isn’t about shutting down services; it’s about making intelligent, architectural choices. It’s about using the right tool for the job, understanding data lifecycle policies, and leveraging the full power of serverless and spot instances.

    This guide will walk you through five practical, high-impact strategies to significantly reduce the cost of your AWS data pipelines.

    1. Implement an S3 Intelligent-Tiering and Lifecycle Policy

    Your data lake on Amazon S3 is the foundation of your pipeline, but storing everything in the “S3 Standard” class indefinitely is a costly mistake.

    • S3 Intelligent-Tiering: This storage class is a game-changer for cost optimization. It automatically moves your data between two access tiers—a frequent access tier and an infrequent access tier—based on your access patterns, without any performance impact or operational overhead. This is perfect for data lakes where you might have hot data that’s frequently queried and cold data that’s rarely touched.
    • S3 Lifecycle Policies: For data that has a predictable lifecycle, you can set up explicit rules. For example, you can automatically transition data from S3 Standard to “S3 Glacier Instant Retrieval” after 90 days for long-term archiving at a much lower cost. You can also set policies to automatically delete old, unnecessary files.

    Actionable Tip: Enable S3 Intelligent-Tiering on your main data lake buckets. For logs or temporary data, create a lifecycle policy to automatically delete files older than 30 days.

    2. Go Serverless with AWS Glue and Lambda

    If you are still managing your own EC2-based Spark or Airflow clusters, you are likely overspending. Serverless services like AWS Glue and AWS Lambda ensure you only pay for the compute you actually use, down to the second.

    • AWS Glue: Instead of running a persistent cluster, use Glue for your ETL jobs. A Glue job provisions the necessary resources when it starts and terminates them the second it finishes. There is zero cost for idle time.
    • AWS Lambda: For small, event-driven tasks—like triggering a job when a file lands in S3—Lambda is incredibly cost-effective. You get one million free requests per month, and the cost per invocation is minuscule.

    Actionable Tip: Refactor your cron-based ETL scripts running on an EC2 instance into an event-driven pipeline using an S3 trigger to start a Lambda function, which in turn starts an AWS Glue job.

    3. Use Spot Instances for Batch Workloads

    For non-critical, fault-tolerant batch processing jobs, EC2 Spot Instances can save you up to 90% on your compute costs compared to On-Demand prices. Spot Instances are spare EC2 capacity that AWS offers at a steep discount.

    When to Use:

    • Large, overnight ETL jobs.
    • Model training in SageMaker.
    • Any batch workload that can be stopped and restarted without major issues.

    Actionable Tip: When configuring your AWS Glue jobs, you can set the “Worker type” and specify a “Maximum capacity.” Under the job’s security configuration, you can enable the use of Spot Instances. Similarly, for services like Amazon EMR or Kubernetes on EC2, you can configure your worker nodes to use Spot Instances.

    4. Choose the Right File Format (Hello, Parquet!)

    The way you store your data has a massive impact on both storage and query costs. Storing your data in a raw format like JSON or CSV is inefficient.

    Apache Parquet is a columnar storage file format that is optimized for analytics.

    • Smaller Storage Footprint: Parquet’s compression is highly efficient, often reducing file sizes by 75% or more compared to CSV. This directly lowers your S3 storage costs.
    • Faster, Cheaper Queries: Because Parquet is columnar, query engines like Amazon Athena, Redshift Spectrum, and AWS Glue can read only the columns they need for a query, instead of scanning the entire file. This drastically reduces the amount of data scanned, which is how Athena and Redshift Spectrum charge you.

    Actionable Tip: Add a step in your ETL pipeline to convert your raw data from JSON or CSV into Parquet before storing it in your “processed” S3 bucket.Python

    # A simple AWS Glue script snippet to convert CSV to Parquet
import sys
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

# ... (Glue context setup)

# Read raw CSV data
source_dyf = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={"paths": ["s3://your-raw-bucket/data/"]},
    format="csv",
    format_options={"withHeader": True},
)

# Write data in Parquet format
glueContext.write_dynamic_frame.from_options(
    frame=source_dyf,
    connection_type="s3",
    connection_options={"path": "s3://your-processed-bucket/data/"},
    format="parquet",
)

    5. Monitor and Alert with AWS Budgets

    You can’t optimize what you can’t measure. AWS Budgets is a simple but powerful tool that allows you to set custom cost and usage budgets and receive alerts when they are exceeded.

    • Set a Monthly Budget: Create a budget for your total monthly AWS spend.
    • Use Cost Allocation Tags: Tag your resources (e.g., S3 buckets, Glue jobs, EC2 instances) by project or team. You can then create budgets that are specific to those tags.
    • Create Alerts: Set up alerts to notify you via email or SNS when your costs are forecasted to exceed your budget.

    Actionable Tip: Go to the AWS Budgets console and create a monthly cost budget for your data engineering projects. Set an alert to be notified when you reach 80% of your budgeted amount. This gives you time to investigate and act before costs get out of hand.

    Conclusion

    AWS data pipeline cost optimization is an ongoing process, not a one-time fix. By implementing smart storage strategies with S3, leveraging serverless compute with Glue and Lambda, using Spot Instances for batch jobs, optimizing your file formats, and actively monitoring your spending, you can build a highly efficient and cost-effective data platform that scales with your business.

  • Building a Serverless Data Pipeline on AWS: A Step-by-Step Guide

    Building a Serverless Data Pipeline on AWS: A Step-by-Step Guide

     For data engineers, the dream is to build pipelines that are robust, scalable, and cost-effective. For years, this meant managing complex clusters and servers. But with the power of the cloud, a new paradigm has emerged: the serverless data pipeline on AWS. This approach allows you to process massive amounts of data without managing a single server, paying only for the compute you actually consume.

    Going serverless means you can say goodbye to idle clusters, patching servers, and capacity planning. Instead, you use a suite of powerful AWS services that automatically scale to meet demand. This isn’t just a technical shift; it’s a strategic advantage that allows your team to focus on delivering value from data, not managing infrastructure.

    In this guide, we’ll walk you through the essential components and steps to build a modern, event-driven serverless data pipeline on AWS using S3, Lambda, AWS Glue, and Athena.

    The Architecture: A Four-Part Harmony

    A successful serverless pipeline relies on a few core AWS services working together seamlessly. Each service has a specific role, creating an efficient and automated workflow from raw data ingestion to analytics-ready insights.

    Here’s a high-level look at our architecture:

    1. Amazon S3 (Simple Storage Service): The foundation of our pipeline. S3 acts as a highly durable and scalable data lake where we will store our raw, processed, and curated data in different stages.
    2. AWS Lambda: The trigger and orchestrator. Lambda functions are small, serverless pieces of code that can run in response to events, such as a new file being uploaded to S3.
    3. AWS Glue: The serverless ETL engine. Glue can automatically discover the schema of our data and run powerful Spark jobs to clean, transform, and enrich it, converting it into an optimized format like Parquet.
    4. Amazon Athena: The interactive query service. Athena allows us to run standard SQL queries directly on our processed data stored in S3, making it instantly available for analysis without needing a traditional data warehouse.

    Now, let’s build it step-by-step.

    Step 1: Setting Up the S3 Data Lake Buckets

    First, we need a place to store our data. A best practice is to use separate prefixes or even separate buckets to represent the different stages of your data pipeline, creating a clear and organized data lake.

    For this guide, we’ll use a single bucket with three prefixes:

    • s3://your-data-lake-bucket/raw/: This is where raw, unaltered data lands from your sources.
    • s3://your-data-lake-bucket/processed/: After cleaning and transformation by our Glue job, the data is stored here in an optimized format (e.g., Parquet).
    • s3://your-data-lake-bucket/curated/: (Optional) A final layer for business-level aggregations or specific data marts.

    Step 2: Creating the Lambda Trigger

    Next, we need a mechanism to automatically start our pipeline when new data arrives. AWS Lambda is perfect for this. We will create a Lambda function that “listens” for a file upload event in our raw/ S3 prefix and then starts our AWS Glue ETL job.

    Here is a sample Python code for the Lambda function:

    lambda_function.py

    Python

    import boto3
import os

def lambda_handler(event, context):
    """
    This Lambda function is triggered by an S3 event and starts an AWS Glue ETL job.
    """
    # Get the Glue job name from environment variables
    glue_job_name = os.environ['GLUE_JOB_NAME']
    
    # Extract the bucket and key from the S3 event
    bucket = event['Records'][0]['s3']['bucket']['name']
    key = event['Records'][0]['s3']['object']['key']
    
    print(f"File uploaded: s3://{bucket}/{key}")
    
    # Initialize the Glue client
    glue_client = boto3.client('glue')
    
    try:
        print(f"Starting Glue job: {glue_job_name}")
        response = glue_client.start_job_run(
            JobName=glue_job_name,
            Arguments={
                '--S3_SOURCE_PATH': f"s3://{bucket}/{key}"
            }
        )
        print(f"Successfully started Glue job run. Run ID: {response['JobRunId']}")
        return {
            'statusCode': 200,
            'body': f"Started Glue job {glue_job_name} for file s3://{bucket}/{key}"
        }
    except Exception as e:
        print(f"Error starting Glue job: {e}")
        raise e

    To make this work, you need to:

    1. Create this Lambda function in the AWS console.
    2. Set an environment variable named GLUE_JOB_NAME with the name of the Glue job you’ll create in the next step.
    3. Configure an S3 trigger on the function, pointing it to your s3://your-data-lake-bucket/raw/ prefix for “All object create events.”

    Step 3: Transforming Data with AWS Glue

    AWS Glue is the heavy lifter in our pipeline. It’s a fully managed ETL service that makes it easy to prepare and load your data for analytics. For this step, you would create a Glue ETL job.

    Inside the Glue Studio, you can visually build a job or write a PySpark script. The job will:

    1. Read the raw data (e.g., CSV) from the source path passed by the Lambda function.
    2. Perform transformations, such as changing data types, dropping columns, or joining with other datasets.
    3. Write the transformed data to the processed/ S3 prefix in Apache Parquet format. Parquet is a columnar storage format that is highly optimized for analytical queries.

    Your Glue job will have a simple script that looks something like this:

    Python

    import sys
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

# Get job arguments
args = getResolvedOptions(sys.argv, ['JOB_NAME', 'S3_SOURCE_PATH'])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Read the raw CSV data from S3
source_dyf = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={"paths": [args['S3_SOURCE_PATH']]},
    format="csv",
    format_options={"withHeader": True},
)

# Convert to Parquet and write to the processed location
glueContext.write_dynamic_frame.from_options(
    frame=source_dyf,
    connection_type="s3",
    connection_options={"path": "s3://your-data-lake-bucket/processed/"},
    format="parquet",
)

job.commit()

    Step 4: Querying Processed Data with Amazon Athena

    Once your data is processed and stored as Parquet in S3, it’s ready for analysis. With Amazon Athena, you don’t need to load it into another database. You can query it right where it is.

    1. Create a Database: In the Athena query editor, create a database for your data lake: CREATE DATABASE my_data_lake;
    2. Run a Glue Crawler (or Create a Table): The easiest way to make your data queryable is to run an AWS Glue Crawler on your processed/ S3 prefix. The crawler will automatically detect the schema of your Parquet files and create an Athena table for you.
    3. Query Your Data: Once the table is created, you can run standard SQL queries on it.

    SQL

    SELECT
    customer_id,
    order_status,
    COUNT(order_id) as number_of_orders
FROM
    my_data_lake.processed_data
WHERE
    order_date >= '2025-01-01'
GROUP BY
    1, 2
ORDER BY
    3 DESC;

    Conclusion: The Power of Serverless

    You have now built a fully automated, event-driven, and serverless data pipeline on AWS. When a new file lands in your raw S3 bucket, a Lambda function triggers a Glue job that processes the data and writes it back to S3 in an optimized format, ready to be queried instantly by Athena.

    This architecture is not only powerful but also incredibly efficient. It scales automatically to handle terabytes of data and ensures you only pay for the resources you use, making it the perfect foundation for a modern data engineering stack.