{"id":12280,"date":"2026-10-29T09:54:25","date_gmt":"2026-10-29T09:54:25","guid":{"rendered":"https:\/\/i-spark.nl\/?p=12280"},"modified":"2026-10-29T14:26:07","modified_gmt":"2026-10-29T14:26:07","slug":"dbt-models-return-different-numbers","status":"publish","type":"post","link":"https:\/\/i-spark.nl\/en\/blog\/dbt-models-return-different-numbers\/","title":{"rendered":"Why do your dbt models return different numbers across environments?"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Same input, same code, different result<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When a dbt pipeline returns different numbers across environments, one of four things is causing it: different input data, different code, different run timing, or non-deterministic SQL. This article walks through each check in order, starting with the most common and easiest to rule out. The diagnostic applies to any warehouse: Snowflake, BigQuery, Databricks, or Redshift.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s dig in!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose you\u2019ve built a dbt project with a whole set of models, running in both TEST and UAT. In TEST, everything checks out. In UAT, the client asks why there were 1,204 orders yesterday and not 1,207. Same model and code, but still a different number.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This happens more often than you\u2019d think. Especially early on, it\u2019s tempting to dive straight into SQL or simply rerun the pipeline. The latter sometimes even fixes it, but then you still don\u2019t know why.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Check whether both environments read from the same source<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The first question is always: do both environments receive the same data? If the input differs, the output may differ too. In that case, nothing is actually wrong.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Usually, all environments read from the same source and the same raw layer; only the layers after that differ per environment. Then this can\u2019t really be the cause. If an environment does have its own source, it\u2019s definitely a candidate. And even if the source should be the same, check that your pipeline really reads from it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Where you read from is determined by your configuration. TEST and UAT writing to different target schemas is normal; that\u2019s exactly what makes them TEST and UAT. On the reading side, all environments usually point to the same place, so that\u2019s rarely where the problem is. Still, it\u2019s worth checking in these three places:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Connection string<\/strong>: how and where you connect, such as server, database and user. Check not only the connection to your target, but also the one to your source.<\/li>\n\n\n\n<li><strong>Environment variables<\/strong>: values that aren\u2019t in your code, but are set per environment. In dbt, you retrieve them with env_var(), for example in your profiles.yml or in your source definitions. The value itself is set per environment in dbt Cloud, or in your CI or terminal. This way, the same code runs with different values.<\/li>\n\n\n\n<li><strong>Parameters<\/strong>: values you pass per run or per model, such as a start date, a lookback window or a filter: same source, but a different slice of it.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">An example in dbt:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">sources:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp;&nbsp;&#8211; name: erp<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp;&nbsp;&nbsp;&nbsp;database: &#8220;{{ env_var(&#8216;SOURCE_DB&#8217;, &#8216;raw_prod&#8217;) }}&#8221;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Both TEST and UAT are meant to read raw_test. In TEST, SOURCE_DB is set to raw_test. In UAT, the variable was forgotten, so dbt falls back to the default raw_prod. Same code, same operational source, and yet UAT reads different data. Even at a client where \u201cthe source is always the same\u201d.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You prevent this by not giving env_var() a default for anything that determines where you read from. So write env_var(&#8216;SOURCE_DB&#8217;) instead of env_var(&#8216;SOURCE_DB&#8217;, &#8216;raw_prod&#8217;). If the variable is missing, dbt fails immediately with an error, instead of silently reading from somewhere else. Want to see the values per environment side by side? In dbt Cloud, you can do that on the environment variables page.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Tip:<\/strong> start by comparing a few simple counts on the source tables, such as the number of rows per day. That\u2019s often enough to tick off this step.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Confirm both environments run the same code version<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If the input is the same, I look at the code. Is the same version running in both environments? Here\u2019s how you check:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Look up which commit last ran in each environment. Your deployment or CI logs show this, and in dbt Cloud the commit is listed with every run.<\/li>\n\n\n\n<li>Compare those two commits with git diff &lt;commit-test&gt; &lt;commit-uat&gt; &#8212; models\/ macros\/. If the output is empty, your code is the same.<\/li>\n\n\n\n<li>In doubt about one specific model? Then compare the compiled SQL in target\/compiled\/ for both environments. If everything looks good, only the schema names differ.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Also take a quick look at the dbt version and your package versions per environment. If your package-lock.yml is in Git, you\u2019re usually fine.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Check whether both environments ran at the same time<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If the input and code don\u2019t explain it, I check the timing. Did both environments run at a comparable moment? If not, you may simply be looking at a timing difference, and nothing is wrong.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is trickier than it seems. A few examples:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Incremental loads that are one run further along in one environment than in the other.<\/li>\n\n\n\n<li>Filters on \u201cyesterday\u201d, such as CURRENT_DATE &#8211; 1. Run just before and just after midnight, and you\u2019re looking at a different day.<\/li>\n\n\n\n<li>Late-arriving data. An order from 23:58 that only lands in the source at 00:15 is in one run, but not in the other.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">A variation on this: in one environment you only run part of your models, for example, just what you changed. Or your incremental models build on a history that was built up differently in TEST than in UAT. To compare the two fairly, run a full dbt build &#8211;full-refresh in both environments. Then both start from the same point. On large tables, that takes a while, but it saves you a lot of guesswork.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This, too, is usually fairly easy to check. Look in the logs at when each run happened, and when the source data was loaded.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Haven&#8217;t found the answer yet? Here is what might actually be going on:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Look for non-deterministic SQL, usually in a deduplication<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The most common cause of non-deterministic results in dbt: a ROW_NUMBER() deduplication where the ORDER BY does not fully break ties.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If input, code and timing are the same, the outcome should be the same too. If it isn\u2019t, it\u2019s likely that something in your code is non-deterministic. In other words: the same query on the same data can give a different result.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In practice, this is almost always in a deduplication. Pay close attention to the PARTITION BY and the ORDER BY.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose you have a customer table where every change creates a new row. You only want the most recent row per customer:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT *<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">FROM customers<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">QUALIFY ROW_NUMBER() OVER (<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp;&nbsp;&nbsp;&nbsp;PARTITION BY customer_id<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY updated_at DESC<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">) = 1<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That looks fine. But take a look at this data:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">record_id&nbsp; customer_id&nbsp; updated_at &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; segment<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">88412&nbsp; &nbsp; &nbsp; 1001 &nbsp; &nbsp; &nbsp; &nbsp; 2026-10-12 09:14:00&nbsp; Gold<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">88413&nbsp; &nbsp; &nbsp; 1001 &nbsp; &nbsp; &nbsp; &nbsp; 2026-10-12 09:14:00&nbsp; Silver<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">87950&nbsp; &nbsp; &nbsp; 1002 &nbsp; &nbsp; &nbsp; &nbsp; 2026-10-03 16:40:00&nbsp; Gold<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">88104&nbsp; &nbsp; &nbsp; 1003 &nbsp; &nbsp; &nbsp; &nbsp; 2026-10-08 11:05:00&nbsp; Silver<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Customer 1001 has two rows with the same updated_at. Which one gets number 1? You don\u2019t decide that; the database does. And it may pick one in TEST and the other in UAT. Sometimes it even differs per run, within the same environment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">See what that does to a simple question: how many Gold customers do we have?<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Environment<\/strong><\/td><td><strong>Customer 1001 becomes<\/strong><\/td><td><strong>Number of Gold customers<\/strong><\/td><\/tr><tr><td>TEST<\/td><td>Gold<\/td><td>2<\/td><\/tr><tr><td>UAT<\/td><td>Silver<\/td><td>1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Same data, same code, and still a different answer. If a discount, a campaign or a revenue report per segment depends on this field, that difference ripples through everywhere.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The solution is a tiebreaker: an extra column in the ORDER BY that always makes the order unambiguous.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">ORDER BY updated_at DESC, record_id DESC<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now record 88413 always wins for customer 1001, in every environment and on every run. Whether Silver is the right choice content-wise is a second question. But at least the answer is the same everywhere.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It\u2019s important to realise that the combination of PARTITION BY and ORDER BY has to be unique. If it isn\u2019t, your outcome depends on chance. The same applies to DISTINCT ON, FIRST_VALUE and LIMIT without a complete ORDER BY.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">QUALIFY works in Snowflake, BigQuery and Databricks, among others. Elsewhere, you use a subquery with the same idea.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>In short<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When environments differ, I go through these questions, in this order:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Is the input the same? Source data, but also connection strings, environment variables and parameters.<\/li>\n\n\n\n<li>Is the code the same?<\/li>\n\n\n\n<li>Did both run at the same moment?<\/li>\n\n\n\n<li>Is there something non-deterministic, usually a deduplication?<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The order isn\u2019t set in stone. I start with what\u2019s most likely and easiest to check. If you know for sure that the source is the same everywhere in a project, feel free to skip step 1.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Tip:<\/strong> write a test that checks whether the combination of your PARTITION BY and ORDER BY columns is unique. In dbt, you can do that with dbt_utils.unique_combination_of_columns. That way, you find step 4 before someone stumbles over it in UAT.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Can\u2019t quite figure it out? Feel free to contact us. We\u2019re always happy to take a look with you, no strings attached.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Same input, same code, different result When a dbt pipeline returns different numbers across environments, one of four things is causing it: different input data, different code, different run timing, or non-deterministic SQL. This article walks through each check in order, starting with the most common and easiest to rule out. The diagnostic applies to [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":12281,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_templately_pack_id":"","_templately_imported_at":"","_templately_source":"","_templately_import_session_id":"","_thinkrank_schema_form_data":"","_thinkrank_selected_schema_type":"","_thinkrank_additional_schemas":"","_thinkrank_canonical_url":"","_thinkrank_og_title":"","_thinkrank_og_description":"","_thinkrank_og_image":"","_thinkrank_twitter_title":"","_thinkrank_twitter_description":"","_thinkrank_twitter_image":"","_thinkrank_imported_from":"","_thinkrank_focus_keywords":["dbt","dbt different results environments","dbt test vs production discrepancy","dbt non-deterministic results","dbt ROW_NUMBER tiebreaker"],"_thinkrank_focus_keyword":"dbt","_thinkrank_robots_meta_enabled":0,"_thinkrank_robots_meta":"[]","_thinkrank_advanced_robots_meta":"[]","_thinkrank_exclude_from_search":0,"_thinkrank_exclude_from_archives":0,"_thinkrank_primary_category":0,"footnotes":""},"categories":[8,434,444],"tags":[],"class_list":["post-12280","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-data-architecture-engineering"],"acf":[],"_links":{"self":[{"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/posts\/12280","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/comments?post=12280"}],"version-history":[{"count":2,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/posts\/12280\/revisions"}],"predecessor-version":[{"id":12285,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/posts\/12280\/revisions\/12285"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/media\/12281"}],"wp:attachment":[{"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/media?parent=12280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/categories?post=12280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/i-spark.nl\/en\/wp-json\/wp\/v2\/tags?post=12280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}