Data pipelines

Moving data in production: AWS Glue, Lambda and Step Functions, Azure Data Factory, Airflow, batch jobs, scheduling and data quality checks — what broke and how it got fixed.

Tech

All posts (93)

• HL7 v2 ADT feeds: from pipe-delimited events to an encounters table

  1 Nov, 2026

  Parse HL7 v2 ADT messages in plain Python, ACK them over MLLP, and fold admits, discharges and cancellations into one row per hospital visit.

• Migrating from SFTP to a roster API: dual-run and reconciliation

  31 Oct, 2026

  Cut over from CSV-over-SFTP to a FHIR roster API without a big bang: phased dual-run, DuckDB reconciliation of CSV vs NDJSON, and exit criteria.

• Why flat-file rosters break: CSV over SFTP in healthcare data pipelines

  25 Oct, 2026

  CSV rosters over SFTP still run value-based care. Here are the failure modes: full replace, fuzzy identity, no acknowledgements, no deltas.

• Snowflake SSO from the terminal: CLI, dbt Core and DBeaver

  20 Oct, 2026

  One Entra ID SSO login, cached for 4 hours and shared by the Snowflake CLI, dbt Core and DBeaver. The errors on the way and the config that fixed them.

• Connecting Clinical and Operational Data to Tuva with dbt

  16 Oct, 2026

  How to map EHR and operational sources into the Tuva Input Layer with dbt, and where to join what Tuva does not model.

• Tuva column extensions: carry a custom column from eligibility to core

  16 Oct, 2026

  How to carry a custom column like contract_type from Tuva's eligibility input layer to core__eligibility using x_ extension columns, without forking.

• Soda Core with SQL Server: connection, Windows auth, SSL errors and checks

  6 Feb, 2023 · Updated 15 Oct, 2026

  Connect Soda Core to SQL Server: ODBC driver, SQL login or Windows auth, fixing SSL: CERTIFICATE_VERIFY_FAILED, and count-distinct checks in SodaCL.

• The limit_zero macro bug: how dbt resolves macro names across packages

  28 May, 2026 · Updated 15 Oct, 2026

  Why a Tuva demo project failed with 'limit_zero' is undefined on DuckDB: bare macro names don't cross packages, dispatch is a second lookup, and the fix.

• Tracking S3 file changes with ETags and LastModified

  16 Sep, 2024 · Updated 15 Oct, 2026

  Detect changed S3 objects without versioning: read ETag and LastModified with boto3, export an ETag CSV per date prefix, and handle multipart ETags.

• Partitioning Strategy in Data Lakes: When It Hurts Performance

  8 Oct, 2026

  Partitioning speeds up queries only when it matches filters and data volume. When over-partitioning backfires, and what to use instead.

• Delta Lake Schema Evolution Explained with Real Examples

  8 Oct, 2026

  Schema enforcement vs. evolution in Delta Lake: mergeSchema, overwriteSchema, MERGE evolution, column mapping for renames and drops, and type widening.

• Small Files Problem in Delta Lake: Detection and Compaction Strategy

  8 Oct, 2026

  How to detect small files in a Delta table from the transaction log, and a compaction strategy using OPTIMIZE, optimized writes and auto compaction.

• Comparing Parquet Compression Codecs: Snappy vs ZSTD Benchmark

  8 Oct, 2026

  A reproducible PyArrow benchmark of Parquet codecs on 5M rows: Snappy, LZ4, GZIP and ZSTD levels 1–19. File size, write and read time.

• S3 Eventual Consistency Edge Cases in Data Pipelines

  8 Oct, 2026

  S3 has been strongly consistent since 2020. The edge cases that still break data pipelines: no atomic rename, concurrent writers, LastModified, events.

• Designing Event Schemas for Product Analytics in ClickHouse

  6 Oct, 2026

  Fixed columns vs Map vs JSON, ORDER BY for funnels, dedup, TTL and materialized views: a practical event schema for product analytics in ClickHouse.

• Why ClickHouse Is Fast: Understanding MergeTree Internals

  6 Oct, 2026

  Parts, granules, sparse primary indexes, marks and background merges: a hands-on look at how MergeTree lets ClickHouse skip most of your data.

• How to Get the Status of an AWS Glue Workflow Using boto3

  30 Sep, 2026

  A Glue workflow can report COMPLETED while its jobs failed. How to poll workflow runs with boto3 and read per-node status from the run graph.

• AWS Glue Job Fails Due to Dependency Version Conflict: How to Fix It

  29 Sep, 2026

  Your Glue job works locally but fails with ImportError or numpy binary incompatibility. Why it happens and how to pin dependencies against the Glue runtime.

• CCLF is not eligibility: where dual status gets lost in Medicare ACO data

  29 Sep, 2026

  How a coalesce and a latest-month rule in Tuva's CCLF + ALR connectors can turn full-dual Medicare beneficiaries into non-duals in CMS-HCC.

• COALESCE doesn't care what your columns mean

  29 Sep, 2026

  COALESCE checks types, not meaning. How merging columns with different semantics silently breaks data, and the dbt guardrails that catch it.

• Triggering AWS Glue Jobs from EventBridge: Workflows, Scheduler, and the IAM You Actually Need

  28 Sep, 2026

  A Glue job isn't a native EventBridge rule target. Three working patterns (workflow, Scheduler, Step Functions) with the exact IAM each one needs.

• Debugging AWS Glue Job Bookmarks Not Working as Expected

  27 Sep, 2026

  Glue job bookmarks fail silently: reprocessed data, skipped files, or no state at all. A checklist of the root causes and how to verify each one.

• Where Agents Schema fits in a dbt → Snowflake → Looker pipeline

  26 Sep, 2026

  Agents Schema doesn't replace dbt, semantic views or LookML. It indexes them inside the warehouse for agents. Where it fits, what it costs, when to skip it.

• Why Athena Returns No Rows on Partitioned Tables (and How to Fix It)

  26 Sep, 2026

  Your data is in S3, the table exists, and Athena returns zero rows. Six root causes behind partition metadata desync and how to diagnose each one.

• Tuva's encounter grouper only groups claims. Here's what happens to your ADT encounters

  23 Sep, 2026

  Tuva's encounter grouper only runs on claims. Clinical ADT/EHR encounters are standardized and stacked in core.encounter, never matched or deduped.

• What's new in cms_hcc v1.0.0: the payer grain fix nobody's docs warned you about

  15 Sep, 2026

  cms_hcc v1.0.0 fixes a silent grain mismatch between int_members and int_disease_factors that dropped every disease factor for members who switched payers mid-year.

• DuckDB concurrency in 2026: why you can't run dbt and DBeaver at the same time

  28 May, 2026

  DuckDB's single-writer model means that opening a .duckdb file in DBeaver blocks dbt from acquiring its write connection. Here's the exact error, why it happens, and the right fix.

• From raw claims to RAF: what the data pipeline actually looks like

  28 May, 2026

  The path from a raw Medicare claim file to a patient's Risk Adjustment Factor score involves five distinct transformation layers. This is what each layer does and where the dbt models fit in.

• HCC suspecting explained from a data engineering perspective

  28 May, 2026

  HCC suspecting is about identifying conditions documented in prior years that haven't appeared in claims yet this year. This is what the data pipeline looks like and what the Tuva mart actually produces.

• Running the Tuva Project on DuckDB — what breaks and how to fix it

  28 May, 2026

  Tuva 0.17.2 on DuckDB 1.10 with dbt 1.11 produces three distinct failure modes. This is what actually broke and how each one was fixed.

• What 167k synthetic Medicare claims taught me about US healthcare data

  28 May, 2026

  Running the Tuva Project on 167k synthetic Medicare claims reveals the structural complexity of US healthcare data — before you've dealt with a single real data quality issue.

• What is the Tuva Project and why should data engineers care

  28 May, 2026

  The Tuva Project is an open-source dbt package that transforms raw healthcare claims into analytics-ready mart tables covering HEDIS, HCC, CCSR, readmissions, and data quality. This is what it actually does and why it matters.

• Why Terraform Does Not Deploy Your Lambda Container Image

  24 Apr, 2026

  When teams start packaging AWS Lambda functions as container images, a common misunderstanding appears quickly: “I created the Lambda with Terraform, so why is AWS saying the image does not exist?”

• Lambda vs n8n: A Simple Explanation for Data Workflows

  23 Mar, 2026

  Introduction When building data systems or integrating APIs, a common question appears: should we use AWS Lambda or n8n? Both tools can automate processes, call APIs, and move data between systems,

• Should You Use AWS Lambda or AWS Glue to Update Records in HubSpot?

  23 Mar, 2026

  When integrating HubSpot with a data platform on AWS, a common architectural decision appears quickly: Should updates to HubSpot be executed from AWS Lambda or AWS Glue? The correct choice depends on

• Daily Failure Reporting in DynamoDB Using Lambda, EventBridge Scheduler, and SES

  1 Mar, 2026

  Operational monitoring requires structured visibility into failures. If your processes write execution logs to DynamoDB and mark failed executions with status = FAILED , you can implement a

• Understanding ip-api Batch Limits and Effective Throughput

  1 Mar, 2026

  When integrating IP geolocation into a data pipeline, understanding rate limits and batching constraints is essential. This post analyzes the practical limits of the ip-api free tier and how to

• AWS Glue + Chargebee: Diagnosing CERTIFICATE_VERIFY_FAILED After TLS Chain Updates

  27 Feb, 2026

  Context An AWS Glue job that consumes the Chargebee API begins failing with: SSLError: SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate The same

• Sending Athena Query Results to Amazon SQS: Architecture, Costs, and Limitations

  27 Jan, 2026

  Introduction Amazon Athena is a serverless query service designed for interactive analysis of data stored in Amazon S3. Amazon SQS (Simple Queue Service), on the other hand, is a fully managed message

• Hiding Personal Information in AWS Glue with Spark

  25 Jan, 2026

  Protecting personal data before analytics consumption is a core requirement in modern data platforms. In AWS-based lake architectures, this is typically achieved through data de-identification during

• Handling Boolean vs IntegerType Mismatches Between MySQL and Spark (Glue JDBC)

  12 Nov, 2025

  When integrating MySQL data into Apache Spark (for example, through AWS Glue), you might encounter schema mismatches caused by how MySQL represents TINYINT(1) fields. This issue often surfaces when

• AWS EventBridge Rules vs EventBridge Scheduler: Which One Should You Use?

  2 Oct, 2025

  In the AWS ecosystem, there are two main ways to schedule and automate tasks: EventBridge Rules (scheduled rules) and the newer EventBridge Scheduler , which introduces Schedule Groups . While both

• Estimating the Cost of an AWS Glue Workflow

  2 Oct, 2025

  When working with AWS Glue, one of the most common questions data engineers ask is: How much will this job cost me? If you have a workflow that runs for 13 minutes, understanding the cost model of AWS

• Trino in Modern Architectures: SQL Queries on S3 and MinIO

  30 Sep, 2025

  The rise of cloud object storage has transformed how organizations build data platforms. Hadoop Distributed File System (HDFS) once dominated, but today services like Amazon S3, Google Cloud Storage

• What Is a Data Lake and What Is a Data Lakehouse?

  19 Sep, 2025

  Over the last decade, the world of data architecture has gone through several transformations. From traditional data warehouses to Hadoop-based data lakes and now to the emerging Lakehouse paradigm,

• Orchestrating Multiple AWS Glue Workflows: A Practical Guide

  16 Sep, 2025

  AWS Glue provides a robust environment for building and managing ETL pipelines, but many data engineers face the challenge of chaining or coordinating multiple workflows . This article explores

• Designing a Semantic Layer for Athena + Power BI

  15 Sep, 2025

  Modern data architectures benefit from a clear separation of layers: Ingesta , Staging , and Semantic (Presentation) . When using Amazon Athena as the query engine and Power BI as the visualization

• How to Disable an AWS Glue Trigger from the CLI

  6 Sep, 2025

  When working with AWS Glue , triggers are an important mechanism to orchestrate jobs or workflows. Sometimes, however, you may need to temporarily disable a trigger without deleting it—for example, to

• Orchestrating Multiple AWS Glue Workflows with Step Functions

  6 Sep, 2025

  In modern data architectures, it is common to manage multiple ETL pipelines that must run in sequence or in parallel. AWS Glue provides a robust framework for building workflows, but when we need to

• Choosing Between saveAsTable and Iceberg’s writeTo in AWS Glue and Athena

  3 Sep, 2025

  When working with Spark on AWS Glue , there are multiple ways to persist DataFrames as tables and make them queryable in Amazon Athena . Two common approaches are: Using Spark’s Hive-style saveAsTable

• Incremental Data Loads: Choosing Between resource_version and created_at/updated_at

  3 Sep, 2025

  Incremental data loading is a cornerstone of modern data engineering pipelines. Instead of re-ingesting entire datasets on each execution, incremental strategies focus on retrieving only records that

• Optimizing Amazon Athena Queries with Partitions: A Practical Example

  1 Sep, 2025

  When working with Amazon Athena, one of the most effective strategies to improve query performance and reduce costs is partitioning your data . Partitions allow Athena to scan only the relevant

• Running Apache Airflow Across Environments

  1 Sep, 2025

  Apache Airflow has become a de facto standard for orchestrating data workflows. However, depending on the environment, the way Airflow runs can change significantly. Many teams get confused when

• AWS Glue Workflow vs Apache Airflow: A Professional Comparison

  12 Jul, 2025

  While both serve the common purpose of managing and automating data workflows, they differ significantly in architecture, flexibility, integration capabilities, and operational control. This article

• EMR vs AWS Glue: Choosing the Right Data Processing Tool on AWS

  6 Jul, 2025

  When working with big data on AWS, two commonly used services for data processing are Amazon EMR and AWS Glue . Although both support scalable data transformation and analytics, they differ

• When Should You Use Iceberg with Athena? Partitioning Strategies and Best Practices

  5 Jul, 2025

  As data lakes grow in size and complexity, tools like Amazon Athena combined with table formats like Apache Iceberg become essential for scalability, data governance, and performance. In this post,

• How Google Changed Big Data: The Story of GFS, MapReduce, and Bigtable

  4 Jul, 2025

  In the early 2000s, Google faced a unique challenge: how to store, process, and query massive amounts of data across thousands of unreliable machines. The traditional systems of the time—designed for

• From Tables to Partitions: Designing NoSQL Databases with Cassandra

  20 May, 2025

  As data professionals transition from relational databases to NoSQL systems like Apache Cassandra, one of the most important mindset shifts is understanding that you don't model data for storage, but

• Apache Cassandra vs Apache Parquet: Understanding the Differences

  14 May, 2025

  In modern data architectures, it's common to encounter both Apache Cassandra and Apache Parquet , particularly when dealing with large-scale, distributed systems. Both technologies are associated with

• How Dynamo Reshaped the Internal Architecture of Amazon S3

  9 May, 2025

  Introduction Amazon S3 launched in 2006 as a scalable, durable object storage system. It avoided hierarchical file systems and used flat key-based addressing from day one. However, early versions of

• What’s Behind Amazon S3?

  9 May, 2025

  When you upload a file to the cloud using an app or service, there's a good chance it's being stored on Amazon S3 (Simple Storage Service). But what powers it under the hood? What is Amazon S3? Amazon

• What Is Serialization?

  30 Apr, 2025

  In the world of data engineering and software systems, serialization is a fundamental concept that allows you to efficiently store, transmit, and reconstruct data structures. If you’ve worked with

• From HDFS to S3: The Evolution of Data Lakes in the Cloud

  29 Apr, 2025

  For years, HDFS (Hadoop Distributed File System) was the default choice for building data lakes in on-premises and Hadoop-based environments. But as cloud computing gained momentum, a new player took

• Is S3 the New HDFS? Comparisons and Use Cases in Big Data

  29 Apr, 2025

  Over the past decade, the way organizations store and manage big data has shifted dramatically. Once dominated by the Hadoop Distributed File System (HDFS) , the field is now led by Amazon S3 and

• The History and Evolution of Amazon S3: Was It Ever Based on HDFS?

  28 Apr, 2025

  When discussing cloud storage today, Amazon S3 is almost synonymous with scalable, reliable object storage. However, a common question among those familiar with big data technologies like Hadoop is:

• Testing Apache Airflow DAGs: A Modular Approach

  5 Apr, 2025

  Introduction Apache Airflow is a powerful workflow automation tool, but testing DAGs can be challenging due to their dependency on the Airflow scheduler and execution environment. In this post, we

• Understanding Pagination vs. Batch Processing in Data Handling

  25 Mar, 2025

  When working with large datasets, developers often face the challenge of efficiently extracting, processing, and managing data. Two commonly used techniques for handling such data efficiently are

• Understanding the Differences Between Parquet, Avro, JSON, and CSV

  9 Feb, 2025

  When working with data, choosing the right file format can significantly impact performance, storage efficiency, and ease of use. In this post, we will compare four widely used data formats: Parquet,

• Exploring Free Resources to Learn AWS and Azure Cloud Platforms

  18 Jan, 2025

  Cloud computing is an essential skill in today’s tech landscape. Among the major players, AWS and Azure stand out as leading cloud platforms, offering a wealth of free resources to help individuals

• What Does an Exploratory Data Analysis (EDA) Evaluate?

  18 Jan, 2025

  An Exploratory Data Analysis (EDA) is a critical step in the data analysis process that focuses on evaluating and examining data to uncover its main characteristics. It is performed before delving

• Automating SQL Script Execution with Cron

  6 Jan, 2025

  In this blog post, we’ll explore how to automate the execution of SQL scripts using cron , a powerful scheduling tool available on Unix-based systems. This approach is ideal for database

• Orchestrating SQL Files: Efficiently Managing Multiple Scripts

  2 Jan, 2025

  When working on database projects, you often find yourself managing and executing multiple SQL files. Whether these files are for creating schemas, seeding data, or running migrations, orchestrating

• Filtering Data in Azure Data Factory: Keeping Only "FileWrite" Operations

  7 Oct, 2024

  In this post, I’ll walk through how to filter rows in Azure Data Factory (ADF) using the Filter activity to retain only the rows where a specific column ( OperationName ) has the value "FileWrite".

• Extracting the Last Element from a Delimited String in Azure Data Factory

  5 Oct, 2024

  When working with data in Azure Data Factory (ADF), it's common to deal with delimited strings. You might need to extract the last element from such strings. For instance, given a string like

• Efficiently Listing and Filtering S3 Objects by Date

  7 Sep, 2024

  When working with AWS S3 buckets, it’s common to have a large number of objects stored, and you might need to filter them based on certain criteria like dates. This blog post will guide you on how to

• Handling Split Errors in Azure Data Factory: A Step-by-Step Guide

  6 Sep, 2024

  In Azure Data Factory (ADF), we often use expressions to manipulate strings and extract specific parts of data. One common operation is splitting strings based on a delimiter. However, this can

• Filtering Items in Azure Data Factory: Excluding Items That Begin with an Underscore

  24 Aug, 2024

  Azure Data Factory (ADF) is a powerful tool for building ETL (Extract, Transform, Load) workflows in the cloud. One common requirement is to filter data or files based on certain conditions. In this

• Extracting Year, Month, and Day from Dates in Azure Data Factory

  23 Aug, 2024

  In Azure Data Factory (ADF), working with dates is a common task, especially when dealing with data transformations and scheduling tasks. ADF allows you to handle dates in different formats, such as

• Splitting Strings and Accessing Elements in Azure Data Factory

  23 Aug, 2024

  Introduction Azure Data Factory (ADF) is a powerful cloud-based data integration service that allows you to create data-driven workflows for orchestrating and automating data movement and data

• Adding SQL Script Filenames to Batch Script Output CSV

  23 Jul, 2024

  When working with batch scripts to execute multiple SQL scripts, it's often helpful to log not only the results but also the filenames of the executed scripts. This can make it easier to track which

• Automating SQL Script Execution and Logging with Batch Scripts

  22 Jul, 2024

  Introduction Automating database tasks can significantly enhance productivity, especially when dealing with multiple SQL scripts. In this tutorial, we will create a batch script to execute SQL scripts

• Root Cause Analysis (RCA) for Data

  2 Jul, 2024

  Introduction In the realm of data management and analysis, problems can range from data quality issues to processing errors and performance bottlenecks. Identifying the root cause of these issues is

• Understanding Data Layout, Files, and Tree Indexes: An Overview

  1 Jul, 2024

  In this post, we'll explore several fundamental concepts related to data storage and indexing: Data Layout, Files, Tree Indexes, and B+ Trees. Understanding these concepts is crucial for anyone

• Effective Knowledge Transfer of Data: Key Elements

  29 Jun, 2024

  Transferring knowledge, especially when it involves data, is a critical proces. Especially between consultants. Whether you're transitioning to a new team, it's crucial to get this process right. Here

• Data Encryption at AWS S3

  17 Apr, 2024

  What is Encryption at rest? Encryption works by using an algorithm to convert plain text into ciphertext. This new ciphertext will be unreadable if it falls into the wrong hands. There are many

• Building a Lucrative Business Model in the Data Economy

  2 Nov, 2023

  Building a Lucrative Business Model in the Data Economy Introduction: In today's data-driven world, information is akin to a gold mine. However, to fully capitalize on this valuable resource, one must

• Principle of Data Wrangling

  10 Oct, 2023

  Data Wrangling involves the process of cleaning and organizing data before any analysis takes place. It typically consumes between 50% and 80% of an analyst's time. Factors to consider include time,

• PostgreSQL on Windows - server error 500 - Ports are not available

  3 Jul, 2023

  I made a mistake the first time when I installed PostgreSQL: I installed it locally. It wouldn't be a problem if I weren't planning to use Docker, but as I want to develop a project in Apache Airflow.

• ASTRONOMER: Install on Windows

  3 Feb, 2023

  Install Astronomer is quite simple In windows you have to follow the following steps: Install docker Install Linux on Windows with WSL. Add the Astro Exe to a location. In general: C\ Change the name

• What is an 'Ephemeral cluster'?

  5 Jan, 2023

  When you create a service to compute, for example in HD-Insight you can create a cluster which remains active once it's created or, in the other hand, stop (will be 'deleted') after some amount of

• Load data from Snowflake to S3

  15 Dec, 2022

  If you want to load data from Snowflake to S3 should try to use the COPY INTO command so, you run something like this command in the snowflake Web App: copy into @my_ext_unload_stage/d1 from mytable;

• Load CSV file from S3 to NEO4J

  9 Dec, 2022

  If you try to load data from S3 to NEO4J you are going to need to presing the file. So you need to expose the data to somebody that have the file. So, first you need to presing the file: aws s3

• Kleppmann - Designing Data Intensive Applications

  17 Oct, 2022

  A data-intensive application is typically built from standard building blocks that provide commonly needed functionality. For example, many applications need to: • Store data so that they, or another