Data modeling

SQL from the engine's point of view, PostgreSQL internals, dbt and Snowflake, dimensional modeling, OLTP vs OLAP.

Tech

All posts (68)

• Building a member-month churn panel in dbt without leaking the future

  11 Nov, 2026

  Turn eligibility, medical and pharmacy claims into a leak-free member-month training table for disenrollment prediction, with dbt tests and Snowflake ML.

• Modeling a patient roster as a FHIR Group (Da Vinci ATR)

  26 Oct, 2026

  Map a roster CSV to FHIR Group, Patient and Coverage using the Da Vinci ATR profile — with a Python converter and namespaced identifiers.

• 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.

• Extracting the last part of a string after a delimiter in SQL Server

  19 Jul, 2024 · Updated 15 Oct, 2026

  Get the text after the last underscore in SQL Server with REVERSE + CHARINDEX, RIGHT or PARSENAME, and avoid the off-by-position and no-delimiter errors.

• 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.

• 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.

• 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.

• 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.

• 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.

• dbt + DuckDB: the good, the bad, and the workarounds

  28 May, 2026

  Running dbt 1.11 with DuckDB 1.10 on a 167k-row dataset is fast and free, but the combination has real rough edges. An honest assessment after a full Tuva Project run.

• 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.

• Batch Means Two Different Things: Why the Term Became Confusing in Data Engineering

  26 Apr, 2026

  In data systems, some of the most common words are also the most overloaded. Few terms illustrate this better than batch . Historically, batch processing described a very specific operating model:

• Window Functions vs JOIN in Spark: A Physical Plan Perspective

  1 Mar, 2026

  When solving analytical queries in Spark SQL, there are often multiple correct formulations. However, they do not produce equivalent execution plans. This article compares two approaches to the same

• From OLTP to OLAP: How Data Moves from 3NF to a Dimensional Data Warehouse

  26 Feb, 2026

  Modern data architectures typically separate operational systems from analytical systems. This separation is not accidental—it reflects fundamentally different workloads, data models, and optimization

• Why Small Tables Can Explode: Understanding JOIN Cardinality in SQL

  20 Feb, 2026

  It is common to assume that joining two small tables will produce a small result set. In practice, this assumption frequently fails. Even tables with only a handful of rows can generate unexpectedly

• The History of Hive and Trino: From Hadoop to Lakehouses

  19 Sep, 2025

  The evolution of Big Data architectures is deeply tied to the history of two projects born at Facebook: Hive and Trino . Both emerged from real engineering pain points, but at different times and for

• Querying JSONB in PostgreSQL Efficiently

  15 Sep, 2025

  In modern applications, it is common to store semi-structured data in JSON format inside a relational database like PostgreSQL. However, to analyze this data properly, you need a way to transform it

• Understanding Window Functions in SQL: Beyond Simple Aggregations

  15 Sep, 2025

  When we think about SQL functions, we often start with scalar functions ( UPPER() , ROUND() , NOW() ) or aggregate functions ( SUM() , AVG() , COUNT() ). But there is a third type that is essential

• he Enduring Relevance of Peter Chen’s Entity-Relationship Model

  8 Jul, 2025

  In the landscape of data modeling, few contributions have had the long-lasting impact of Peter Chen’s Entity-Relationship (E-R) Model , introduced in 1976. More than four decades later, it remains a

• What Is Sharding and Why It Matters

  21 May, 2025

  As our world becomes increasingly digital, the amount of data we create every day is staggering. Think about all the emails, messages, orders, and photos uploaded every second. How do big companies

• How Joins Work in PostgreSQL

  13 Apr, 2025

  Joins are one of the most powerful features in SQL, allowing you to combine data from multiple tables in a single query. PostgreSQL, as a relational database system, provides robust support for

• How to Improve Query Performance in PostgreSQL

  13 Apr, 2025

  PostgreSQL is a powerful relational database, but even the most robust systems can suffer from slow queries without proper tuning. Optimizing query performance is crucial to ensure scalability,

• Optimizing Joins in PostgreSQL: Practical Cases

  11 Apr, 2025

  Joins are essential for querying relational databases, but they can significantly impact performance if not optimized correctly. PostgreSQL provides several ways to improve join efficiency, from

• Benchmarking OLTP vs. OLAP: Measuring Performance Effectively

  8 Apr, 2025

  Understanding the performance differences between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) is crucial for designing efficient database systems. This post outlines a

• OLTP vs. OLAP: How JOINs and Efficiency Shape Their Differences

  7 Apr, 2025

  Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) are two distinct database architectures, each designed for different purposes. One key factor that differentiates them is

• The Origins of OLTP and OLAP: A Brief History

  7 Apr, 2025

  Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) are fundamental concepts in database management, each serving distinct purposes. But when did these terms first appear, and

• Comparison Between Star Schema and Snowflake Schema in PostgreSQL

  6 Apr, 2025

  Comparison Between Star Schema and Snowflake Schema in PostgreSQL When designing a database for analytical workloads, choosing the right schema can significantly impact performance and query

• Visualizing EXPLAIN ANALYZE in PostgreSQL

  4 Apr, 2025

  When working with PostgreSQL, understanding how queries execute can greatly improve performance tuning and optimization. PostgreSQL provides the EXPLAIN ANALYZE command to help developers analyze

• Handling Schema Changes in a Data Warehouse

  31 Mar, 2025

  When building and maintaining a Data Warehouse (DWH) , handling schema changes without breaking existing processes is a crucial challenge for data engineers. As new requirements emerge, we often need

• Why OLTP Systems Don't Retain Historical Changes

  30 Mar, 2025

  Online Transaction Processing (OLTP) systems are designed for high-speed transactions and efficient data management. However, one of their characteristics is that they do not retain historical changes

• Understanding Slowly Changing Dimensions (SCD) in Data Warehousing

  28 Mar, 2025

  When dealing with data warehouses, handling changes in dimension data over time is crucial. Unlike operational databases where updates are straightforward, data warehouses require preserving

• Understanding Surrogate Keys in Databases

  26 Mar, 2025

  When designing relational databases, one crucial decision is how to uniquely identify each record in a table. This is where surrogate keys come into play. Unlike natural keys, which derive from

• Understanding the Relationship Between Database Replication and the CAP Theorem

  26 Mar, 2025

  Introduction Database replication is a fundamental strategy in distributed systems that ensures data is duplicated across multiple nodes. However, when designing a replicated database, one must

• Tracking Daily File Size Changes in SQL

  23 Mar, 2025

  When working with databases that store file metadata, it's often useful to track how file sizes change over time. If you have a table with the following structure: id | timestamp | name_file | size

• Merging Data in PostgreSQL vs. MySQL: How to Handle Upserts

  2 Mar, 2025

  When working with databases, you often need to update existing records or insert new ones based on whether a match is found. In PostgreSQL, this is efficiently handled using the MERGE statement.

• Are Indexes a Good Strategy for Analytical Databases?

  3 Jan, 2025

  Indexes are a well-known optimization technique in database management, often associated with improving query performance. However, whether they are a good strategy for analytical databases depends on

• Understanding the Evolution of Data Warehousing: From Codd's Relational Model to Modern Data Warehouses

  2 Jan, 2025

  Data management has undergone significant transformations since the advent of the relational model by Edgar F. Codd. Today, data warehouses stand as a cornerstone of modern data analytics. This blog

• How to Simulate Column Headers Without Selecting from a Table in SQL

  6 Nov, 2024

  In some cases, you may want to produce a result set with specified column names and values without querying an actual table. This is often used for testing purposes, documentation, or even when

• Comparing Window Functions with Aggregate Functions in SQL

  26 Aug, 2024

  Introduction SQL is a powerful language for querying and manipulating data, and both window functions and aggregate functions are central to its capabilities. While they serve related purposes, they

• Defining Custom Window Frames in SQL Server

  26 Aug, 2024

  Introduction Window functions in SQL Server are powerful tools that allow for advanced data analysis within queries. One of the key features of window functions is the ability to define custom window

• Creating a Running Total in SQL Server with Window Functions

  25 Aug, 2024

  Introduction Calculating a running total is a common requirement in many data analysis tasks, such as tracking cumulative sales, computing cumulative scores, or keeping track of inventory levels. In

• Using the OVER() Clause with Window Functions in SQL Server

  24 Aug, 2024

  Introduction SQL window functions have become an indispensable tool for data analysts and developers. They allow for advanced calculations that go beyond simple aggregates, enabling analysis over a

• Understanding Window Functions in SQL: A Deep Dive

  21 Aug, 2024

  Introduction When working with databases, you'll often need to perform calculations across a set of rows related to the current row in your query. Whether you're calculating a running total, ranking

• How to Check if Two Tables Have the Same Columns in SQL

  16 Aug, 2024

  When working with databases, it's sometimes necessary to compare two tables to ensure they have the same structure. Specifically, you might need to verify that two tables have the same columns before

• Identifying Duplicate Records in SQL Based on Specific Fields

  16 Aug, 2024

  In database management, identifying and handling duplicate records is crucial to ensure data integrity. This post will guide you through a SQL query designed to find duplicates based on a specific

• Debugging SQL Joins: Troubleshooting OR Joins with Multiple Columns in PostgreSQL

  12 Aug, 2024

  Introduction SQL joins are essential for combining data from different tables in a relational database. However, they can sometimes be tricky, especially when dealing with complex joins that involve

• Resolving PostgreSQL Authentication Errors in Docker Compose for Redash

  12 Aug, 2024

  Introduction When setting up Redash with Docker Compose, one of the common errors users might encounter is related to PostgreSQL authentication. Specifically, the psycopg2.OperationalError:

• Understanding the DECIMAL Data Type in SQL: What Happens When You Don't Specify Parameters?

  12 Aug, 2024

  When working with SQL, one of the fundamental aspects of database design and manipulation is understanding the various data types available. Among them, the DECIMAL type is often used for representing

• Understanding Window Functions in SQL with Running Totals

  12 Aug, 2024

  Introduction Window functions in SQL are incredibly powerful, allowing you to perform calculations across a set of table rows related to the current row. They enable tasks like calculating running

• Retrieving the Name of an SQL Script File in Your Query

  9 Aug, 2024

  When working with SQL scripts, there are times when you might want to dynamically retrieve the name of the script that is currently executing. Unfortunately, SQL itself doesn't provide a

• Decrypting Encrypted Data with Subqueries in SQL

  30 Jul, 2024

  When working with encrypted data in SQL, it's essential to ensure that the decryption process is secure and efficient. One effective approach is using subqueries. In this post, we'll demonstrate how

• Leveraging SQL Window Functions with PARTITION BY

  22 Jul, 2024

  SQL window functions are a powerful tool for performing calculations across a set of rows related to the current row. When combined with the PARTITION BY clause, these functions can provide deep

• Running SQL Queries from a Batch File: Retrieving the Server Name

  16 Jul, 2024

  When working with SQL servers, it's often useful to automate routine tasks using batch files. One common task is retrieving the server name where your database is running. In this post, we'll walk

• 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

• William Kent - Data & Reality

  2 Nov, 2023

  Chapter 1 – Entities. The book The Hitchhiker’s Guide to the Galaxy should be required reading for both business and information technology professionals. Although this is a science fiction book. I

• Common table expressions

  10 Oct, 2023

  Specifies a temporary named result set, known as a common table expression (CTE). Microsoft Documentation Although there are some time around us the first time someone asked me about it I was

• 4.6 Data Warehouses

  9 Oct, 2023

  DataWarehouses—large historical databases for decision-support that are loaded with new data on a periodic basis — have evolved to require specialized query processing support, and in the next section

• Importance of a Database System

  9 Oct, 2023

  As should be clear from this paper, modern commercial database systems are grounded both in academic research and in the experiences of developing industrial-strength products for high-end customers.

• Know your String Connection using SQL

  12 Jul, 2023

  I was looking how to know my server on the internet, and I've found this interesting question in Stackoverflow: How to get the connection String from a database . And one question give us an example

• 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.

• 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