<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Evgenii Timofeev</title>
    <description>The latest articles on DEV Community by Evgenii Timofeev (@eu_ti_f127c5b5d7535b7174f).</description>
    <link>https://dev.to/eu_ti_f127c5b5d7535b7174f</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4102419%2Fd5567118-12e1-4443-add0-48855e37440c.jpg</url>
      <title>DEV Community: Evgenii Timofeev</title>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/eu_ti_f127c5b5d7535b7174f"/>
    <language>en</language>
    <item>
      <title>5 Is Loud, 0 Is Silent: When the Obvious Fix Is Worse Than the Bug</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Fri, 20 Nov 2026 13:12:57 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/5x-is-loud-0x-is-silent-when-the-obvious-fix-is-worse-than-the-bug-28pd</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/5x-is-loud-0x-is-silent-when-the-obvious-fix-is-worse-than-the-bug-28pd</guid>
      <description>&lt;p&gt;We found a scheduler running in every web process. Four workers, four schedulers, one shared job table, no locking — so every due job got dispatched four times.&lt;/p&gt;

&lt;p&gt;The fix is obvious. Run exactly one scheduler, in its own process.&lt;/p&gt;

&lt;p&gt;That fix, written the obvious way, makes the scheduler dispatch &lt;strong&gt;nothing at all&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why the obvious fix breaks it
&lt;/h2&gt;

&lt;p&gt;APScheduler's &lt;code&gt;BackgroundScheduler&lt;/code&gt; sleeps between jobs. How long it sleeps is decided by the jobs it already knows about, and it is woken by exactly one thing:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;call&lt;/th&gt;
&lt;th&gt;who it wakes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;BaseScheduler.add_job&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;wakeup()&lt;/code&gt; on &lt;strong&gt;its own instance only&lt;/strong&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;_main_loop&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;waits on &lt;code&gt;_process_jobs()&lt;/code&gt;, which returns &lt;code&gt;TIMEOUT_MAX&lt;/code&gt; — &lt;strong&gt;49.7 days&lt;/strong&gt; — when nothing is due&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;While the scheduler lived &lt;em&gt;inside&lt;/em&gt; the web process, saving a schedule called &lt;code&gt;add_job&lt;/code&gt; on the very object that would run it. Move the scheduler into its own process and that call still happens — on a scheduler instance in the &lt;strong&gt;web&lt;/strong&gt; process, which no longer runs anything. The instance that matters never hears about it, and goes back to sleep for seven weeks.&lt;/p&gt;

&lt;p&gt;Measured on APScheduler 3.11.2 against a real Postgres jobstore: a job written by a second process, due in two seconds, produced &lt;strong&gt;0 dispatches at t+3s, t+6s and t+10s&lt;/strong&gt;. A single explicit &lt;code&gt;wakeup()&lt;/code&gt; then ran it &lt;strong&gt;immediately&lt;/strong&gt; — which is the control that matters, because it proves the scheduler was &lt;em&gt;asleep&lt;/em&gt; rather than &lt;em&gt;broken&lt;/em&gt;. Those two look identical from the outside and have completely different fixes.&lt;/p&gt;

&lt;p&gt;The remedy is a heartbeat: the single scheduler re-reads the job table on a short interval, so a write from another process is picked up on the next beat. Measured across real processes, with the discriminating control:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;reconcile interval&lt;/th&gt;
&lt;th&gt;a schedule row written by another process&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;5 s&lt;/strong&gt; (the fix)&lt;/td&gt;
&lt;td&gt;picked up in &lt;strong&gt;5.0 s&lt;/strong&gt;, no restart&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;3600 s&lt;/strong&gt; (control)&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;never&lt;/strong&gt;, across the whole 25-second observation&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  The asymmetry is the whole point
&lt;/h2&gt;

&lt;p&gt;Both states are wrong. They are not equally wrong.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;5× is loud.&lt;/strong&gt; Duplicate rows, duplicate work, duplicate downstream writes. Somebody notices, and the evidence of what went wrong is sitting in a table.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;0× is silent.&lt;/strong&gt; A user creates a schedule. The row is written. The interface shows it active. It never runs, and there is no error anywhere, because &lt;em&gt;nothing failed&lt;/em&gt; — a process that is asleep is behaving exactly as designed.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;So the naive version of this fix converts "create a schedule" into a &lt;strong&gt;silent no-op until the next restart&lt;/strong&gt;, and the only party positioned to discover it is the customer, some time later, asking why a thing did not happen.&lt;/p&gt;

&lt;p&gt;🔑 &lt;strong&gt;A change that moves a failure from loud to silent deserves more scrutiny than the bug it replaces&lt;/strong&gt;, even when it is unambiguously the right architecture. "Correct but quieter" is a trade, and the quiet half needs its own detection before you ship it. We added a monitoring rule for the new single process in the same change, for exactly this reason: a singleton that nothing watches is a component whose death is undetectable — a shape we have &lt;a href="https://datanika.io/blog/alerts-that-could-never-fire/" rel="noopener noreferrer"&gt;written about before&lt;/a&gt;, from the monitoring side.&lt;/p&gt;

&lt;h2&gt;
  
  
  The test that proved the opposite of the truth
&lt;/h2&gt;

&lt;p&gt;Before fixing it, we tried to reproduce the original bug in a test. Five scheduler instances, one process, one shared Postgres jobstore, one due job.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Result: one dispatch.&lt;/strong&gt; Four of the five schedulers did nothing. By the test's own logic, the bug did not exist.&lt;/p&gt;

&lt;p&gt;That green is not weak evidence. It is &lt;strong&gt;anti-evidence&lt;/strong&gt;: it points confidently in the wrong direction.&lt;/p&gt;

&lt;p&gt;It is a sharper version of something we have hit before — &lt;a href="https://datanika.io/blog/github-actions-pipefail-exit-code/" rel="noopener noreferrer"&gt;a nightly job that reported success for eight nights while twelve tests failed&lt;/a&gt;. That one failed to &lt;em&gt;look&lt;/em&gt;. This one looks, and reports the opposite of what is there. Here is why.&lt;/p&gt;

&lt;p&gt;The defect lives in a read-modify-write window: read the due jobs, dispatch, advance &lt;code&gt;next_run_time&lt;/code&gt;. Two schedulers duplicate work only if the second one &lt;em&gt;reads&lt;/em&gt; before the first one &lt;em&gt;writes&lt;/em&gt;. In a single Python process, the GIL serialises exactly that window — five threads wake, whoever holds the interpreter first advances the timestamp, and everyone else reads a job that is no longer due. &lt;strong&gt;The runtime closes the race the test exists to observe.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Five separate processes on four cores genuinely overlap, and the duplication is real.&lt;/p&gt;

&lt;p&gt;So: &lt;strong&gt;no in-process harness can be the regression test for this bug.&lt;/strong&gt; Not a better-written one; not a slower one; not one with more instances. The property under test is destroyed by the environment the test runs in.&lt;/p&gt;

&lt;p&gt;That is worth generalising, because the shape is common and the failure is confident:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;Before trusting a concurrency test, ask whether the runtime you are testing in can even express the interleaving you are testing for.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;A green from a harness that cannot produce the failure is not weak evidence of correctness — it is evidence about the harness, wearing the costume of evidence about the code. The tell is available before you run anything: name the window the bug lives in, then ask what serialises it.&lt;/p&gt;

&lt;h2&gt;
  
  
  What we actually shipped
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;The scheduler moved to a dedicated single-process container, so the process count is a property of the deployment rather than an accident of how many web workers are configured.&lt;/li&gt;
&lt;li&gt;A reconcile heartbeat, because moving it severed the in-process notification path — and the heartbeat is the wakeup and the poll at the same time.&lt;/li&gt;
&lt;li&gt;A monitoring rule that watches the new container, added in the same change rather than afterwards.&lt;/li&gt;
&lt;li&gt;The regression tests assert that &lt;strong&gt;nothing arms a scheduler at import time&lt;/strong&gt;. That property is independent of process count &lt;em&gt;and&lt;/em&gt; of architecture: it stays true under a dedicated container, a leader lock, or a feature flag. A test that encodes the design decision you made last week is a test you will have to rewrite the week after.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The last one is the piece we would keep if we could only keep one. The bug was never really "four schedulers" — it was that starting a scheduler was a side effect of importing the application. Everything else followed from that.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Datanika is an open-source data platform — extraction, loading, transformation and scheduling in one place. If you want to see what the scheduler described here is attached to, the &lt;a href="https://datanika.io/docs/architecture/" rel="noopener noreferrer"&gt;architecture overview&lt;/a&gt; is the place to start.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>python</category>
      <category>scheduling</category>
      <category>concurrency</category>
      <category>testing</category>
    </item>
    <item>
      <title>I Integrated a Payment Provider, Then Ripped It Out and Switched to Another</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Thu, 19 Nov 2026 21:55:59 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/i-integrated-a-payment-provider-then-ripped-it-out-and-switched-to-another-5fe</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/i-integrated-a-payment-provider-then-ripped-it-out-and-switched-to-another-5fe</guid>
      <description>&lt;p&gt;I built the entire billing system on LemonSqueezy. Models, API client, webhook handler, 51 tests. Then I hit a wall: LemonSqueezy's payout coverage didn't fit our business setup.&lt;/p&gt;

&lt;p&gt;Switched to Paddle. The migration was surprisingly manageable — but only because of one architectural decision I made early.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why It Didn't Break Everything
&lt;/h2&gt;

&lt;p&gt;The billing layer was a &lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;separate plugin&lt;/a&gt;, not woven into the core. All I had to replace was:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;API client&lt;/strong&gt; — &lt;code&gt;LemonSqueezyClient&lt;/code&gt; became &lt;code&gt;PaddleClient&lt;/code&gt;. Six async methods wrapping httpx. Same interface shape, different endpoint URLs and auth headers.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Webhook handler&lt;/strong&gt; — HMAC verification logic changed (different header names, different signing scheme), but the handler still processes five subscription events: created, updated, canceled, past_due, paused.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Column names&lt;/strong&gt; — &lt;code&gt;lemon_squeezy_subscription_id&lt;/code&gt; became &lt;code&gt;paddle_subscription_id&lt;/code&gt;. One Alembic migration.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Config keys&lt;/strong&gt; — environment variables renamed from &lt;code&gt;LEMON_SQUEEZY_*&lt;/code&gt; to &lt;code&gt;PADDLE_*&lt;/code&gt;.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The core app didn't change at all. Not one line. The &lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;hooks system&lt;/a&gt; kept firing &lt;code&gt;connection.before_create&lt;/code&gt; and &lt;code&gt;run.models_completed&lt;/code&gt; — it didn't care which billing provider was listening.&lt;/p&gt;

&lt;h2&gt;
  
  
  LemonSqueezy vs Paddle
&lt;/h2&gt;

&lt;p&gt;Both are Merchant of Record — they handle taxes, compliance, and invoicing. The concepts map 1:1:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Concept&lt;/th&gt;
&lt;th&gt;LemonSqueezy&lt;/th&gt;
&lt;th&gt;Paddle&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Payment processing&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Tax compliance (global)&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Subscription management&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;td&gt;✅&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Webhook events&lt;/td&gt;
&lt;td&gt;5 events&lt;/td&gt;
&lt;td&gt;5 events&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Usage-based billing&lt;/td&gt;
&lt;td&gt;❌ (limited)&lt;/td&gt;
&lt;td&gt;✅ (native)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Payout countries&lt;/td&gt;
&lt;td&gt;Limited&lt;/td&gt;
&lt;td&gt;Broader&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Fee structure&lt;/td&gt;
&lt;td&gt;5% + $0.50/txn&lt;/td&gt;
&lt;td&gt;5% + $0.50/txn&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The decisive factor was payout coverage. Everything else was near-identical. Paddle's native usage-based billing support was a bonus — it made overage billing for the Enterprise plan much cleaner than what I'd have built on LemonSqueezy.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Pricing Model Survived Unchanged
&lt;/h2&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Corrected 2026-08-31.&lt;/strong&gt; The table and the run caps below are the &lt;strong&gt;V1&lt;/strong&gt; plan&lt;br&gt;
catalogue as it stood in April 2026, and they are kept because they are what&lt;br&gt;
this migration ran against. They are no longer what the product does. V2 bills&lt;br&gt;
&lt;strong&gt;data volume&lt;/strong&gt;, not runs: Free includes 10 GB/month, Pro 100 GB with $0.50/GB&lt;br&gt;
overage, Enterprise 1 TB with $0.25/GB. The &lt;code&gt;$0.01/run&lt;/code&gt; overage is gone, and&lt;br&gt;
Pro and Enterprise are &lt;strong&gt;no longer hard-capped on runs&lt;/strong&gt; — their run figures&lt;br&gt;
are fair-use, and only Free still stops. &lt;a href="https://datanika.io/pricing/" rel="noopener noreferrer"&gt;/pricing&lt;/a&gt; is current.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;The pricing tiers didn't change at all during the migration:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Plan&lt;/th&gt;
&lt;th&gt;Price&lt;/th&gt;
&lt;th&gt;Members&lt;/th&gt;
&lt;th&gt;Connections&lt;/th&gt;
&lt;th&gt;Schedules&lt;/th&gt;
&lt;th&gt;Runs/month&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Free&lt;/td&gt;
&lt;td&gt;$0&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;500 (hard cap)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Pro&lt;/td&gt;
&lt;td&gt;$79/mo&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;25&lt;/td&gt;
&lt;td&gt;Unlimited&lt;/td&gt;
&lt;td&gt;15,000 (hard cap)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Enterprise&lt;/td&gt;
&lt;td&gt;From $399/mo&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;50&lt;/td&gt;
&lt;td&gt;Unlimited&lt;/td&gt;
&lt;td&gt;50,000 (+$0.01/run overage)&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Annual plans save ~17%: Pro $66/mo ($790/yr), Enterprise from $333/mo ($3,990/yr). See &lt;a href="https://datanika.io/pricing/" rel="noopener noreferrer"&gt;/pricing&lt;/a&gt; for current details.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Metering System
&lt;/h2&gt;

&lt;p&gt;Metering was the most interesting part of the billing implementation, and it survived the migration completely untouched because it sits above the provider layer:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Upload run&lt;/strong&gt; = T model runs (T = tables loaded by dlt)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Transformation run&lt;/strong&gt; = 1 model run&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Pipeline run&lt;/strong&gt; = M + T model runs (M = successful dbt models, T = tests executed)&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;An hourly Celery task syncs overages to Paddle for Enterprise customers. Under V1, Free and Pro were both hard-capped on runs; see the correction above for what V2 does instead.&lt;/p&gt;

&lt;p&gt;The metering hooks (&lt;code&gt;run.models_completed&lt;/code&gt;, &lt;code&gt;run.upload_completed&lt;/code&gt;, &lt;code&gt;run.transformation_completed&lt;/code&gt;) record every unit into a &lt;code&gt;UsageLedger&lt;/code&gt; table. The Celery task reads the ledger and reports to Paddle. Swapping Paddle for any other provider would only touch the reporting step, not the recording step.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Lesson
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Always treat your billing provider as replaceable.&lt;/strong&gt; Abstract the interface, keep it in its own package, and don't let payment logic leak into your core product.&lt;/p&gt;

&lt;p&gt;I was tempted to take shortcuts — calling the LemonSqueezy API directly from service methods, storing LemonSqueezy-specific IDs in core tables, importing billing models from core code. Every one of those shortcuts would have made the migration 10x harder.&lt;/p&gt;

&lt;p&gt;The plugin boundary I describe in the &lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;open-core post&lt;/a&gt; wasn't just about separating open-source from paid. It was about keeping billing replaceable. If Paddle doesn't work out in two years, the next migration will be the same scope: one API client, one webhook handler, a few column renames, a few config keys. Not a rewrite.&lt;/p&gt;

&lt;h2&gt;
  
  
  Building in Public
&lt;/h2&gt;

&lt;p&gt;The billing system has 51 tests and hasn't processed a single real dollar yet. That's fine — the first real transaction will prove the webhooks work in production. Everything before that is architecture insurance.&lt;/p&gt;

&lt;p&gt;Would love to hear how others handle payment provider risk — especially if you've been through a migration yourself.&lt;/p&gt;

&lt;h2&gt;
  
  
  Related
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;How I Split Open-Source Core From Paid Cloud&lt;/a&gt; — the plugin architecture that made this migration survivable&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/blog/saas-12-euros/" rel="noopener noreferrer"&gt;My SaaS Ran on €12 a Month (April 2026)&lt;/a&gt; — the full infrastructure cost (Paddle is $0 while pre-revenue)&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/pricing/" rel="noopener noreferrer"&gt;Pricing&lt;/a&gt; — current plans on Paddle&lt;/li&gt;
&lt;/ul&gt;

</description>
      <category>billing</category>
      <category>paddle</category>
      <category>migration</category>
      <category>opencore</category>
    </item>
    <item>
      <title>Our Guard Checked That We Still Talked About the Fix, Not That We Still Did It</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Wed, 18 Nov 2026 10:35:11 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/our-guard-checked-that-we-still-talked-about-the-fix-not-that-we-still-did-it-1o73</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/our-guard-checked-that-we-still-talked-about-the-fix-not-that-we-still-did-it-1o73</guid>
      <description>&lt;p&gt;A test asserted that a flag was present in a shell script. The flag was deleted. The test stayed green.&lt;/p&gt;

&lt;p&gt;It stayed green because the flag's name also appeared in the comment above the command — eleven lines explaining why the flag was mandatory and what breaks without it. The assertion was a substring search. Prose satisfies a substring search exactly as well as code does.&lt;/p&gt;

&lt;p&gt;We have written four posts now about &lt;a href="https://datanika.io/blog/a-red-that-proves-nothing/" rel="noopener noreferrer"&gt;checks that report the wrong thing&lt;/a&gt;: &lt;a href="https://datanika.io/blog/alerts-that-could-never-fire/" rel="noopener noreferrer"&gt;alerts structurally unable to fire&lt;/a&gt;, &lt;a href="https://datanika.io/blog/github-actions-pipefail-exit-code/" rel="noopener noreferrer"&gt;an exit code swallowed by a pipe&lt;/a&gt;, &lt;a href="https://datanika.io/blog/watchdog-workflow-with-no-file/" rel="noopener noreferrer"&gt;a red that meant "I crashed" and a red that meant "I found a problem"&lt;/a&gt;. This one is different in a way that took us a while to see. The others were checks pointed at the wrong thing. This one was pointed at exactly the right thing, and read the documentation instead of the implementation.&lt;/p&gt;

&lt;p&gt;And on the same day, in the same file, the same guard failed in the opposite direction — going red against code that was correct. That pairing is the actual lesson, so both halves are here.&lt;/p&gt;

&lt;h2&gt;
  
  
  The flag, and why deleting it is expensive
&lt;/h2&gt;

&lt;p&gt;Our pre-push hook runs the test suite. One line of it looks like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nv"&gt;UV_NO_SYNC&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1 &lt;span class="s2"&gt;"$PY"&lt;/span&gt; -m pytest &lt;span class="s2"&gt;"$PYTEST_SCOPE"&lt;/span&gt; -x -q --tb=short
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;UV_NO_SYNC=1&lt;/code&gt; is not a tuning knob. On Windows, one of our migration tests shells out to &lt;code&gt;uv run alembic&lt;/code&gt;, which tries to replace native extensions — SQLAlchemy's &lt;code&gt;cyextension&lt;/code&gt;, &lt;code&gt;cryptography&lt;/code&gt; — while the pytest process holding those files open is still running. Windows refuses the replacement. The removal half-completes. The virtualenv is left gutted.&lt;/p&gt;

&lt;p&gt;The symptom is that your dependencies appear to have randomly vanished. It presents as environment flakiness, it sends you hunting for a cause that is not there, and the fix is a full reinstall. It cost one engineer four reinstalls before anyone understood it, and then it cost a second engineer four more.&lt;/p&gt;

&lt;p&gt;So the flag matters, and a guard asserting it is still there is a reasonable thing to want.&lt;/p&gt;

&lt;h2&gt;
  
  
  The assertion
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="s2"&gt;"UV_NO_SYNC=1"&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;hook_text&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Here is the region of the hook it was searching:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;echo&lt;/span&gt; &lt;span class="s2"&gt;"pre-push: pytest ($PYTEST_SCOPE) — $GATE_OUT"&lt;/span&gt;
&lt;span class="c"&gt;# UV_NO_SYNC=1 is NOT optional on Windows. test_head_downgrade_upgrade_roundtrip shells&lt;/span&gt;
&lt;span class="c"&gt;# out to `uv run alembic`, which tries to replace native extensions (sqlalchemy's&lt;/span&gt;
&lt;span class="c"&gt;# cyextension, cryptography, ...) while this pytest process still holds the files open.&lt;/span&gt;
&lt;span class="c"&gt;# Windows refuses, the removal half-completes, and the venv is left gutted — presenting&lt;/span&gt;
&lt;span class="c"&gt;# as "my dependencies randomly vanished" and sending people hunting for a cause that&lt;/span&gt;
&lt;span class="c"&gt;# isn't there.&lt;/span&gt;
&lt;span class="c"&gt;#&lt;/span&gt;
&lt;span class="c"&gt;# WORKFLOW_RULES §3 has documented this as mandatory since it cost QA, and then Growth&lt;/span&gt;
&lt;span class="c"&gt;# four reinstalls — and the stated lesson was that a fix living only in prose gets&lt;/span&gt;
&lt;span class="c"&gt;# rediscovered the expensive way. It was still only in prose: the hook never set it.&lt;/span&gt;
&lt;span class="c"&gt;# Setting it here means nobody has to know.&lt;/span&gt;
&lt;span class="nv"&gt;UV_NO_SYNC&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1 &lt;span class="s2"&gt;"$PY"&lt;/span&gt; -m pytest &lt;span class="s2"&gt;"$PYTEST_SCOPE"&lt;/span&gt; -x -q --tb=short
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The string &lt;code&gt;UV_NO_SYNC=1&lt;/code&gt; occurs twice: once on the first line of the comment, once on the command. Delete it from the command and the guard is still satisfied by the comment. The suite goes green, the push succeeds, and the next person on Windows loses an afternoon.&lt;/p&gt;

&lt;p&gt;Read the last four lines of that comment again, because they are the part we cannot stop thinking about. They say that a fix living only in prose gets rediscovered the expensive way, and that this particular fix &lt;em&gt;was&lt;/em&gt; still only in prose, and that writing it into the hook means nobody has to know.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;That paragraph — the one arguing that prose is not a safeguard — was the prose that satisfied the guard meant to prove the safeguard existed.&lt;/strong&gt; We did not plant that. It is just what happens when you assert on text: the most carefully written explanation of a thing is indistinguishable, to a substring search, from the thing.&lt;/p&gt;

&lt;h2&gt;
  
  
  It was not a one-off
&lt;/h2&gt;

&lt;p&gt;While we were fixing that one we went looking for the same shape elsewhere, and found it immediately in a different file, written by a different person, months apart.&lt;/p&gt;

&lt;p&gt;A scheduled workflow runs a test selection with a &lt;code&gt;-k&lt;/code&gt; deselection flag. A guard asserted the flag was present:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="s1"&gt;'-k "not oracle"'&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;step&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Same outcome. The &lt;code&gt;pytest&lt;/code&gt; line had been stripped of &lt;code&gt;-k&lt;/code&gt;, and the assertion stayed green, because the flag was &lt;em&gt;described&lt;/em&gt; in the comment above it. Two of them, in fact — both green against a workflow that no longer did the thing.&lt;/p&gt;

&lt;p&gt;The summary we wrote into our own rulebook is the shortest true statement of the problem: &lt;strong&gt;the guard was checking that we still talk about the fix, not that we do it.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Neither instance was caught by review. Both were caught by mutation — by deliberately breaking the thing the guard watches and checking that the guard noticed. A guard you have never seen go red is not evidence; it is a hypothesis with a green checkmark next to it.&lt;/p&gt;

&lt;h2&gt;
  
  
  The same guard, failing the other way
&lt;/h2&gt;

&lt;p&gt;Here is the half that stops this from being a simple "assert on code, not comments" post.&lt;/p&gt;

&lt;p&gt;The first draft of the replacement guard also banned a shell construct. Our hook assigns some variables conditionally:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="o"&gt;[&lt;/span&gt; &lt;span class="s2"&gt;"$lsha"&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"$HEAD_SHA"&lt;/span&gt; &lt;span class="o"&gt;]&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="nv"&gt;PUSHING_HEAD&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Under &lt;code&gt;set -e&lt;/code&gt;, &lt;code&gt;[ cond ] &amp;amp;&amp;amp; VAR=x&lt;/code&gt; returns non-zero when the condition is false. There is a well-known hazard here: the script can abort at that line, silently skipping everything below it. So the guard banned the construct outright, by pattern match.&lt;/p&gt;

&lt;p&gt;It went red. Against correct code.&lt;/p&gt;

&lt;p&gt;The blanket ban is wrong, and we only learned exactly how wrong by running the cases on the actual shell rather than reasoning about them:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;set&lt;/span&gt; -e&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="o"&gt;[&lt;/span&gt; 1 &lt;span class="o"&gt;=&lt;/span&gt; 2 &lt;span class="o"&gt;]&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="nv"&gt;V&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="nb"&gt;echo&lt;/span&gt; hi                    -&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; survives &lt;span class="o"&gt;(&lt;/span&gt;rc 0&lt;span class="o"&gt;)&lt;/span&gt;
&lt;span class="nb"&gt;set&lt;/span&gt; -e&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;while&lt;/span&gt; &lt;span class="nb"&gt;read&lt;/span&gt; ...&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;do&lt;/span&gt; &lt;span class="o"&gt;[&lt;/span&gt; x &lt;span class="o"&gt;]&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="nv"&gt;V&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;done&lt;/span&gt;&lt;span class="o"&gt;;&lt;/span&gt; more  -&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; survives &lt;span class="o"&gt;(&lt;/span&gt;rc 0&lt;span class="o"&gt;)&lt;/span&gt;
&lt;span class="nb"&gt;set&lt;/span&gt; -e&lt;span class="o"&gt;;&lt;/span&gt; f&lt;span class="o"&gt;(&lt;/span&gt;&lt;span class="o"&gt;)&lt;/span&gt;&lt;span class="o"&gt;{&lt;/span&gt; &lt;span class="o"&gt;[&lt;/span&gt; 1 &lt;span class="o"&gt;=&lt;/span&gt; 2 &lt;span class="o"&gt;]&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="nv"&gt;V&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="o"&gt;}&lt;/span&gt;&lt;span class="o"&gt;;&lt;/span&gt; f                  -&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; ABORTS  &lt;span class="o"&gt;(&lt;/span&gt;rc 1&lt;span class="o"&gt;)&lt;/span&gt;
&lt;span class="nb"&gt;set&lt;/span&gt; -e&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="kc"&gt;true&lt;/span&gt;&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;then&lt;/span&gt; &lt;span class="o"&gt;[&lt;/span&gt; 1 &lt;span class="o"&gt;=&lt;/span&gt; 2 &lt;span class="o"&gt;]&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="nv"&gt;V&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;1&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;fi&lt;/span&gt;           -&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; ABORTS  &lt;span class="o"&gt;(&lt;/span&gt;rc 1&lt;span class="o"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The construct is fatal &lt;strong&gt;only when its non-zero status becomes the exit status of an enclosing scope&lt;/strong&gt; — the last command of the script, of a function body, or of a branch with nothing after it. It is harmless whenever any command follows at the same or an outer level.&lt;/p&gt;

&lt;p&gt;The line the guard flagged sits inside a &lt;code&gt;while read&lt;/code&gt; loop with the entire rest of the hook after it. It is correct. It must not be "fixed."&lt;/p&gt;

&lt;p&gt;So within one file, on one day, a textual guard managed both failure modes: &lt;strong&gt;blind where it should have been red, and red where it should have been silent.&lt;/strong&gt; They look nothing alike when you hit them. They have the same cause. Text is not behaviour, and a pattern match cannot see position, scope, or whether a line is even a line of code.&lt;/p&gt;

&lt;h2&gt;
  
  
  What replaced both
&lt;/h2&gt;

&lt;p&gt;For the presence checks, every needle became the &lt;strong&gt;executable line&lt;/strong&gt;, never the prose that explains it:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="nd"&gt;@pytest&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;mark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;parametrize&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"needle, what"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"skipping rebase and tests"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;              &lt;span class="s2"&gt;"refspec guard"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"git rebase origin/dev --quiet"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;          &lt;span class="s2"&gt;"auto-rebase onto dev"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"pre-push WARNING: branch/commit mismatch"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Closes/branch consistency"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"-m ruff check datanika tests"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;           &lt;span class="s2"&gt;"ruff check"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"-m ruff format --check datanika tests"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="s2"&gt;"ruff format --check"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"pre-push NOTE: uv.lock has changed"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;     &lt;span class="s2"&gt;"stale-venv NOTE"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'UV_NO_SYNC=1 "$PY" -m pytest'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;           &lt;span class="s2"&gt;"Windows venv-gutting guard"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;UV_NO_SYNC=1 "$PY" -m pytest&lt;/code&gt; is a string that cannot appear in an English sentence. That is the whole trick, and it costs nothing.&lt;/p&gt;

&lt;p&gt;For the positional hazard, the pattern match was deleted and replaced by a test that &lt;strong&gt;executes the real block&lt;/strong&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;_run&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;block&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;env_line&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;script&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s1"&gt;'set -e\n{env_line}\n{block}\necho "SCOPE=$PYTEST_SCOPE"\n'&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;subprocess&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;run&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;bash&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"-c"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;script&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;capture_output&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="kc"&gt;True&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;text&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="kc"&gt;True&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;test_block_survives_set_e_when_condition_is_false&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;hook_text&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;_run&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;_block&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;hook_text&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"export DATANIKA_PREPUSH_FULL=0"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;returncode&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"scope block exits non-zero; under set -e that aborts the hook"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;It extracts the actual block out of the actual hook, runs it under &lt;code&gt;set -e&lt;/code&gt;, and reads the exit status. It cannot be fooled by a comment, and it cannot be wrong about position, because it is not modelling the shell — it is asking the shell.&lt;/p&gt;

&lt;p&gt;There is one more trap on the way out, and we wrote it down because we nearly walked into it. If you fix the comment problem by stripping comment lines before asserting, &lt;strong&gt;keep a control that asserts the stripper still lets a real command through.&lt;/strong&gt; Otherwise the next person hits a false positive, narrows the stripper to make it go away, and ends up with a stripper that removes everything and a guard that matches nothing. Same green, new mechanism.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why any of this existed
&lt;/h2&gt;

&lt;p&gt;Context, because it explains the stakes rather than excusing them.&lt;/p&gt;

&lt;p&gt;We had just trimmed the pre-push hook. It used to run the whole suite — 5,261 tests, &lt;strong&gt;1,105.95 seconds&lt;/strong&gt; on Windows. It now runs the deployment tests only — 432 tests, &lt;strong&gt;79.89 seconds&lt;/strong&gt;. Measured on one commit, sequentially, uncontended. That is about seventeen minutes back on every single push.&lt;/p&gt;

&lt;p&gt;Trimming a safety net makes what remains load-bearing. Six things in that hook cannot be replaced by CI — the refspec guard, the auto-rebase, the branch/commit consistency warning, two lint gates, the stale-lockfile note, and &lt;code&gt;UV_NO_SYNC&lt;/code&gt;. The guard exists to make sure a future trim does not quietly take one of them with it. Which is exactly why a guard that could be satisfied by a comment was worse than no guard: it was a load-bearing check that had already stopped bearing load.&lt;/p&gt;

&lt;p&gt;Every one of the thirteen mutations against the replacement has now been seen red, including an anti-vacuity case against a truncated hook — because a guard that passes against a file with nothing in it is measuring the parser, not the file.&lt;/p&gt;

&lt;p&gt;One footnote, since we would rather print the negative results too. We also measured the same suite under WSL2 on ext4: 953.78s against 1,105.95s native. That is 1.16x, which is inside this machine's own run-to-run variance. We had been telling ourselves a filesystem migration was worth doing. On this evidence it is not, and we are not doing it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Four things to check in your own repositories
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Grep your assertions for bare identifiers.&lt;/strong&gt; Any &lt;code&gt;assert "SOME_FLAG" in text&lt;/code&gt; where &lt;code&gt;SOME_FLAG&lt;/code&gt; is also a word people write in comments is already satisfiable by prose. Make the needle a string that can only be a command.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Delete the thing, then run the guard.&lt;/strong&gt; Not the test suite for the guard — the guard, against a mutated copy of the real artifact. If it stays green, you have learned something today. If your guard has never been observed red, it is not yet evidence.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Ask whether your check models behaviour or matches text.&lt;/strong&gt; Anything positional — shell exit status, scope, ordering, control flow — cannot be checked by a regex, and the regex will be confidently wrong in both directions.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Keep a false-positive control beside every filter.&lt;/strong&gt; The moment a guard strips, ignores, or excludes something, add a test that the guard still fires on a real defect. Narrowing a filter until the noise stops is how a working check becomes a decorative one.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The general form, and the reason we keep finding new instances: &lt;strong&gt;a check is only as good as the difference between what it reads and what it claims to know.&lt;/strong&gt; A substring search reads text and claims to know about behaviour. Every gap like that gets closed eventually — by a mutation test, or by an afternoon someone spends reinstalling a virtualenv.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Datanika is an open-source data platform that runs dlt extract-and-load and dbt-core transformations behind one UI, with scheduling, run history and a REST API. Every hook, guard and defect quoted here lives in our public repositories. It is AGPL-3.0 and self-hostable — see &lt;a href="https://datanika.io/docs/architecture/" rel="noopener noreferrer"&gt;the architecture&lt;/a&gt;, or &lt;a href="https://datanika.io/connectors/" rel="noopener noreferrer"&gt;browse the connectors&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>ci</category>
      <category>testing</category>
      <category>bash</category>
      <category>githubactions</category>
    </item>
    <item>
      <title>Why We Built Datanika: One Tool Instead of Five</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Tue, 17 Nov 2026 00:08:48 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/why-we-built-datanika-one-tool-instead-of-five-1hkf</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/why-we-built-datanika-one-tool-instead-of-five-1hkf</guid>
      <description>&lt;p&gt;Ask someone what it takes to get a Postgres table into a warehouse and put a chart on top of it. The honest answer, for most teams in 2026, is five tools.&lt;/p&gt;

&lt;p&gt;One to move the data. One to transform it. One to schedule the transformations. One to hold the credentials and glue. One to tell you when it broke. Five vendors, five bills, five sets of documentation, five places to look when a number is wrong on Monday morning.&lt;/p&gt;

&lt;p&gt;Each of those tools is good. That's the frustrating part. The problem was never any individual piece — it was the seams between them.&lt;/p&gt;

&lt;h2&gt;
  
  
  The seams are the work
&lt;/h2&gt;

&lt;p&gt;Here is what nobody puts on a pricing page: the integration is the job.&lt;/p&gt;

&lt;p&gt;Your ingestion tool lands a table in &lt;code&gt;raw&lt;/code&gt;. Your transformation tool needs to know it arrived, and how fresh it is. Your orchestrator needs to know that the transform depends on the load, which means it needs to reach into both. Your alerting needs to distinguish "the load failed" from "the load succeeded but produced zero rows" from "the transform ran on yesterday's data" — three very different pages at 3am, and none of the tools involved can see the whole picture.&lt;/p&gt;

&lt;p&gt;So you write glue. A little Airflow DAG that pokes the ingestion API. A sensor that waits on a table. A Slack webhook with some &lt;code&gt;if&lt;/code&gt; statements. None of it is hard. All of it is yours to maintain forever, and none of it is the thing you were actually hired to do.&lt;/p&gt;

&lt;p&gt;For a data team of twelve with a platform engineer, that's an acceptable cost — it buys best-of-breed at every layer, and at real scale that matters.&lt;/p&gt;

&lt;p&gt;For a team of two, it's most of the job.&lt;/p&gt;

&lt;h2&gt;
  
  
  What we actually wanted
&lt;/h2&gt;

&lt;p&gt;Datanika started from a narrow, unglamorous question: what would it take for one person to run a real pipeline — sources, transforms, schedules, monitoring — without becoming a full-time integrator?&lt;/p&gt;

&lt;p&gt;Not a toy. Real credentials, real scheduled loads, real dbt models with tests, real retries, real multi-tenancy. Just without the five-way integration tax.&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Corrected 2026-09-17.&lt;/strong&gt; This line originally promised "real incremental loads". An upload's cursor does not carry from one run to the next, so every run starts again from the beginning of each table (&lt;a href="https://github.com/datanika-io/datanika-core/issues/1404" rel="noopener noreferrer"&gt;core#1404&lt;/a&gt;).&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;The answer turned out to be: use the good open-source pieces, but own the seams.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://dlthub.com" rel="noopener noreferrer"&gt;dlt&lt;/a&gt; for extract and load.&lt;/strong&gt; We didn't write our own extractors. dlt already handles schema evolution, incremental state, and normalization better than anything we'd build.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://www.getdbt.com" rel="noopener noreferrer"&gt;dbt-core&lt;/a&gt; for transforms.&lt;/strong&gt; Not a dbt-like DSL. Actual dbt, with &lt;code&gt;ref()&lt;/code&gt;, tests, snapshots, and packages — so your models are portable and your team already knows the tool.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Celery and APScheduler for orchestration.&lt;/strong&gt; Boring, proven, and — crucially — aware of both the loads and the transforms, because they live in the same system.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;One UI over all of it&lt;/strong&gt;, so a run's lineage from source to model is a thing you can look at rather than reconstruct.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The insight isn't that any of these are novel. It's that when one system owns ingestion &lt;em&gt;and&lt;/em&gt; transformation &lt;em&gt;and&lt;/em&gt; scheduling, the dependency between them stops being integration code you write and starts being a property of the platform. A transform that depends on a load is a dropdown, not a sensor.&lt;/p&gt;

&lt;h2&gt;
  
  
  What it looks like in practice
&lt;/h2&gt;

&lt;p&gt;A pipeline that would be four tools and a hundred lines of glue becomes: add a source connection, add a destination, pick tables, write a model, set a schedule. There are &lt;a href="https://datanika.io/connectors/" rel="noopener noreferrer"&gt;36 connectors&lt;/a&gt; — 31 sources and 9 destinations, overlapping on the four databases that do both — and every one of them is available on every plan, including the free one. We gate on volume, not on capability, because a connector you can't use is not a feature.&lt;/p&gt;

&lt;p&gt;When something breaks, the run history shows which step failed, with logs, in the same place you configured it. When a model is wrong, you compile it before you run it, against the warehouse, and see the SQL dbt actually generated.&lt;/p&gt;

&lt;h2&gt;
  
  
  The honest trade-off
&lt;/h2&gt;

&lt;p&gt;An all-in-one platform is not free of cost, and pretending otherwise would be exactly the kind of marketing we find irritating in everyone else.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;You get less depth per layer.&lt;/strong&gt; Fivetran has more connectors than we do — several hundred more. If your business depends on a long-tail SaaS API we haven't built, best-of-breed wins, and we'll say so.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;You're consolidating risk.&lt;/strong&gt; Five tools means five things that can fail independently; one tool means one thing whose failure takes everything. Our answer to that is the open-source core: the platform is AGPL and &lt;a href="https://datanika.io/docs/self-hosting/" rel="noopener noreferrer"&gt;self-hostable&lt;/a&gt;, so "the vendor disappears" is a recoverable event rather than an extinction one. That's also why the &lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;billing plugin is a separate, optional layer&lt;/a&gt; rather than something woven through the core.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Opinionated defaults are only good if you share the opinions.&lt;/strong&gt; We chose dlt and dbt. If your transformation layer is Spark or SQLMesh, we are the wrong tool, and no amount of UI will fix that.&lt;/p&gt;

&lt;p&gt;The bet is that for most teams below a certain size — and that's a &lt;em&gt;lot&lt;/em&gt; of teams — the depth they lose is worth less than the seams they stop maintaining. We wrote out the arithmetic in &lt;a href="https://datanika.io/blog/real-cost-modern-data-stack/" rel="noopener noreferrer"&gt;The Real Cost of Your Modern Data Stack&lt;/a&gt; and put it side by side with the alternatives in &lt;a href="https://datanika.io/blog/datanika-vs-modern-data-stack/" rel="noopener noreferrer"&gt;Datanika vs the Modern Data Stack&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why the pricing works this way
&lt;/h2&gt;

&lt;p&gt;The same instinct shaped how we charge. Per-row and per-connector pricing punishes you for the shape of your schema — a wide table costs more than a narrow one for the same information. We bill for &lt;strong&gt;gigabytes processed&lt;/strong&gt;: 10 GB free, then a published per-GB rate with no per-seat or per-connector multipliers. The reasoning, including where it makes us &lt;em&gt;more&lt;/em&gt; expensive than the alternatives, is in &lt;a href="https://datanika.io/blog/pricing-v2-math-and-why/" rel="noopener noreferrer"&gt;the pricing post&lt;/a&gt; and the &lt;a href="https://datanika.io/why-cheaper/" rel="noopener noreferrer"&gt;cost comparison&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where this goes
&lt;/h2&gt;

&lt;p&gt;The platform is open source, the core is AGPL, and it runs on your infrastructure if you want it to. The hosted version exists because most people don't want to operate Postgres, Redis, and a Celery worker to move a table — not because we're holding features hostage.&lt;/p&gt;

&lt;p&gt;If you've been maintaining glue between five tools and quietly wondering whether that's just what the job is: it isn't.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://app.datanika.io/" rel="noopener noreferrer"&gt;Start free&lt;/a&gt; with 10 GB/month, or read &lt;a href="https://datanika.io/docs/architecture/" rel="noopener noreferrer"&gt;how it's put together&lt;/a&gt; first.&lt;/p&gt;

</description>
      <category>originstory</category>
      <category>moderndatastack</category>
      <category>elt</category>
      <category>dbt</category>
    </item>
    <item>
      <title>SSO for Enterprise: SAML and OIDC in Datanika</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Mon, 16 Nov 2026 23:44:39 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/sso-for-enterprise-saml-and-oidc-in-datanika-3fon</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/sso-for-enterprise-saml-and-oidc-in-datanika-3fon</guid>
      <description>&lt;p&gt;Once a data platform has more than a handful of users, "who has access" stops being a platform question and becomes an IT question. The answer your security team wants is not a list of accounts in our database — it's &lt;em&gt;your&lt;/em&gt; identity provider, with your offboarding process, your MFA policy, and your audit trail.&lt;/p&gt;

&lt;p&gt;Datanika supports &lt;strong&gt;SAML 2.0&lt;/strong&gt; and &lt;strong&gt;OIDC&lt;/strong&gt; single sign-on, configured per organization. Here's how it works and what it actually enforces.&lt;/p&gt;

&lt;h2&gt;
  
  
  Two protocols, one flow
&lt;/h2&gt;

&lt;p&gt;Both protocols land in the same place: a verified email address, a Datanika user, and a membership in your organization.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Aspect&lt;/th&gt;
&lt;th&gt;SAML 2.0&lt;/th&gt;
&lt;th&gt;OIDC&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Typical IdPs&lt;/td&gt;
&lt;td&gt;Okta, Azure AD / Entra, ADFS, OneLogin&lt;/td&gt;
&lt;td&gt;Google Workspace, Auth0, Keycloak, Okta&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;What you configure&lt;/td&gt;
&lt;td&gt;IdP entity ID, SSO URL, signing certificate (or a metadata URL)&lt;/td&gt;
&lt;td&gt;Issuer URL, client ID, client secret&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;How trust is established&lt;/td&gt;
&lt;td&gt;The IdP signs assertions with a private key; we verify against the certificate you gave us&lt;/td&gt;
&lt;td&gt;We exchange an authorization code over TLS using a shared client secret&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Browser flow&lt;/td&gt;
&lt;td&gt;Redirect to IdP, &lt;code&gt;POST&lt;/code&gt; a signed assertion back&lt;/td&gt;
&lt;td&gt;Redirect to IdP, return with a code, exchange server-side&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Pick whichever your identity provider does best. If you have both, OIDC is usually the simpler setup — there are fewer moving parts and no certificate rotation to think about.&lt;/p&gt;

&lt;h2&gt;
  
  
  Configuring it
&lt;/h2&gt;

&lt;p&gt;SSO is set up in &lt;strong&gt;Settings&lt;/strong&gt; by an organization owner or admin. Each organization has one active SSO configuration.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;For SAML&lt;/strong&gt;, you provide your IdP's entity ID, SSO URL, and X.509 signing certificate — or a metadata URL to pull them from. On our side, the service-provider metadata your IdP needs is served at:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="no"&gt;GET&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;api&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;auth&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;sso&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;org_slug&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Hand that URL to whoever administers your IdP and they can configure the application from it directly.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;For OIDC&lt;/strong&gt;, you provide the issuer URL, client ID, and client secret. The client secret is &lt;strong&gt;encrypted at rest&lt;/strong&gt; with Fernet, using the same encryption service that protects your data-source credentials — it is never stored in plaintext and never returned by the API.&lt;/p&gt;

&lt;p&gt;Once saved, your team signs in at:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="no"&gt;GET&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;api&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;auth&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;sso&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;login&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;org_slug&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Just-in-time provisioning
&lt;/h2&gt;

&lt;p&gt;You do not pre-create accounts. When someone from your organization signs in through SSO for the first time:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;We look them up by the email address the IdP asserted.&lt;/li&gt;
&lt;li&gt;If they don't exist, we create the user — no password, no separate registration, no email-verification step (your IdP already confirmed the address).&lt;/li&gt;
&lt;li&gt;We add them to your organization as a &lt;strong&gt;viewer&lt;/strong&gt;, the least-privileged role.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;That last point is deliberate. A new SSO user can look at pipelines and runs but cannot change a connection, edit a model, or trigger anything. An owner or admin promotes them to editor or admin from the members page once they know who the person is. Authentication and authorization stay separate: your IdP decides &lt;em&gt;who gets in&lt;/em&gt;, and your Datanika admins decide &lt;em&gt;what they can do&lt;/em&gt;. See &lt;a href="https://datanika.io/docs/organizations/" rel="noopener noreferrer"&gt;Organizations &amp;amp; Members&lt;/a&gt; for the full role matrix.&lt;/p&gt;

&lt;h2&gt;
  
  
  What we validate on a SAML assertion
&lt;/h2&gt;

&lt;p&gt;A SAML assertion is a signed XML document asserting "this person is who they say they are." The security of the whole flow rests on actually checking that signature — and on checking everything around it.&lt;/p&gt;

&lt;p&gt;Every assertion is validated in strict mode by &lt;a href="https://github.com/SAML-Toolkits/python3-saml" rel="noopener noreferrer"&gt;python3-saml&lt;/a&gt;, backed by &lt;code&gt;xmlsec&lt;/code&gt;, against the certificate configured for your organization. Specifically:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Check&lt;/th&gt;
&lt;th&gt;What it stops&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;XML-DSig signature&lt;/strong&gt; verified against your configured IdP certificate, with assertions required to be signed&lt;/td&gt;
&lt;td&gt;Assertions that are unsigned, signed with the wrong key, or modified after signing&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;Audience&lt;/strong&gt; matches our service-provider entity ID&lt;/td&gt;
&lt;td&gt;An assertion minted for a different application being replayed at ours&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;Destination&lt;/strong&gt; matches our assertion consumer URL&lt;/td&gt;
&lt;td&gt;An assertion intended for a different endpoint&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;Conditions&lt;/strong&gt; (&lt;code&gt;NotBefore&lt;/code&gt; / &lt;code&gt;NotOnOrAfter&lt;/code&gt;)&lt;/td&gt;
&lt;td&gt;Expired assertions&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;InResponseTo&lt;/strong&gt; matches the authentication request we issued&lt;/td&gt;
&lt;td&gt;Unsolicited assertions the user never initiated&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;strong&gt;Single-use request ID&lt;/strong&gt;, held in Redis for 10 minutes and consumed on first use&lt;/td&gt;
&lt;td&gt;Replaying a previously valid assertion a second time&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Two things are worth calling out about &lt;em&gt;how&lt;/em&gt; this is done, not just what is checked.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Signature verification is delegated to &lt;code&gt;xmlsec&lt;/code&gt;, not hand-rolled.&lt;/strong&gt; XML signature verification is notoriously easy to get subtly wrong — signature-wrapping attacks work by splicing an attacker-controlled element next to a legitimately signed one, so that naive code validates one element and then reads a different one. Using a vetted implementation that verifies &lt;em&gt;the signed element itself&lt;/em&gt; removes that entire class of bug, along with XXE exposure from parsing untrusted XML with a general-purpose parser.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;A failed assertion returns &lt;code&gt;401&lt;/code&gt;, not a friendly redirect.&lt;/strong&gt; Most authentication errors are user errors and deserve a helpful message. A SAML assertion that fails validation is not a user error — it is either a misconfiguration or an attack. It gets a flat &lt;code&gt;401&lt;/code&gt; and no session, and the rejection is logged.&lt;/p&gt;

&lt;h2&gt;
  
  
  What SSO does not do
&lt;/h2&gt;

&lt;p&gt;Being direct about the boundaries:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;It is not SCIM.&lt;/strong&gt; We provision users on first login, but we do not yet sync deprovisioning from your IdP. When someone leaves, revoke them in your IdP — that stops new logins immediately — and remove their membership in Datanika to end existing sessions.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It does not replace API keys.&lt;/strong&gt; Programmatic access — the &lt;a href="https://datanika.io/api/reference/" rel="noopener noreferrer"&gt;REST API&lt;/a&gt;, the &lt;a href="https://datanika.io/docs/mcp-server/" rel="noopener noreferrer"&gt;MCP server&lt;/a&gt;, CI jobs — authenticates with API keys, not SSO. Those are issued and revoked separately.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;One configuration per organization.&lt;/strong&gt; If you need multiple IdPs against a single org, that's not supported today.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;We do not sign our own authentication requests.&lt;/strong&gt; Some IdPs can be configured to require this. If yours does, it will need that requirement relaxed.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Availability
&lt;/h2&gt;

&lt;p&gt;SSO is an &lt;strong&gt;Enterprise&lt;/strong&gt; feature. The quota is enforced through the same plan-hook system that governs connection and seat limits — the open-source core emits a &lt;code&gt;sso_config.before_create&lt;/code&gt; event and the cloud billing plugin decides, which is why the open-source edition has no artificial gate here at all. If you &lt;a href="https://datanika.io/docs/self-hosting/" rel="noopener noreferrer"&gt;self-host&lt;/a&gt;, you configure SSO and nothing checks your plan. We wrote about that split in &lt;a href="https://datanika.io/blog/open-core-plugin/" rel="noopener noreferrer"&gt;Open Core Without the Coupling&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;For the hosted platform, the Enterprise tier and its contact-sales route are both on the &lt;a href="https://datanika.io/pricing/" rel="noopener noreferrer"&gt;pricing page&lt;/a&gt;.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Datanika is an open-source data pipeline platform built on dlt and dbt-core. &lt;a href="https://app.datanika.io/" rel="noopener noreferrer"&gt;Start free&lt;/a&gt; with 10 GB/month, or &lt;a href="https://datanika.io/docs/" rel="noopener noreferrer"&gt;read the docs&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sso</category>
      <category>saml</category>
      <category>oidc</category>
      <category>enterprise</category>
    </item>
    <item>
      <title>Your Agent Can Now Change Things — If You Say So Once</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Fri, 13 Nov 2026 11:13:19 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/your-agent-can-now-change-things-if-you-say-so-once-4k0</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/your-agent-can-now-change-things-if-you-say-so-once-4k0</guid>
      <description>&lt;p&gt;Until now, an agent connected to Datanika over &lt;a href="https://datanika.io/docs/mcp-server/" rel="noopener noreferrer"&gt;MCP&lt;/a&gt; could look but not touch. It read your connections, previewed tables, compiled dbt models and pulled run logs — genuinely useful for debugging, and deliberately unable to change anything.&lt;/p&gt;

&lt;p&gt;The eight write tools are now available over the hosted endpoint: create connections, uploads, pipelines and transformations, bulk-import a whole project, and trigger runs. This is the first release where an agent can act on your data rather than only read it.&lt;/p&gt;

&lt;p&gt;The interesting part isn't the tools. It's where the decision lives.&lt;/p&gt;

&lt;h2&gt;
  
  
  The decision is yours, and you make it once
&lt;/h2&gt;

&lt;p&gt;Write access is granted at &lt;strong&gt;OAuth consent time&lt;/strong&gt;. When a client asks for write scopes, you approve them in the browser, and the API key that approval mints carries exactly those scopes. There is no runtime toggle, no per-call prompt, and nothing to restart.&lt;/p&gt;

&lt;p&gt;We considered a per-call confirmation — the agent asks, you approve each write. It doesn't survive contact with a stateless transport, and more importantly it gates the wrong party: a token the agent mints and echoes back to itself is a ritual, not a control. The agent is the thing being gated, so it can't also hold the gate.&lt;/p&gt;

&lt;p&gt;So the human decision moves to the one moment a human is definitely present and definitely paying attention: the consent screen. What you approve is what the credential can do, and enforcement stays on the key's scopes where it already lived.&lt;/p&gt;

&lt;h2&gt;
  
  
  Three properties worth knowing
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Silence is not consent.&lt;/strong&gt; A client that omits &lt;code&gt;scope&lt;/code&gt;, or asks for something we don't recognise, gets read-only. Clients omit it routinely, so this had to be the default rather than an error — an agent should never acquire write access because a request was ambiguous.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;A pasted API key stays read-only on the hosted endpoint&lt;/strong&gt; — even if that key's own scopes would permit writes. Nothing recorded a human decision, so the hosted path won't infer one from a credential you copied into a config file. (Your key's scopes still bound what the REST API does with it; this is specifically about what the MCP layer will expose.)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;There is no "may build but may not trigger" tier&lt;/strong&gt;, and this is the refinement we deliberately didn't ship. It sounds prudent: let the agent draft a pipeline, reserve the run for a human. But the REST API gives &lt;code&gt;create_upload&lt;/code&gt; and &lt;code&gt;trigger_upload&lt;/code&gt; the same &lt;code&gt;uploads:write&lt;/code&gt; scope. A checkbox splitting them could only be enforced inside the MCP layer, while the credential itself still permitted both — a control the token doesn't back, which is exactly the kind of security theatre the per-call confirmation would have been. Spend is bounded by your key's rate limit and byte quota instead, which are real limits rather than a UI promise.&lt;/p&gt;

&lt;h2&gt;
  
  
  What the screen tells you
&lt;/h2&gt;

&lt;p&gt;A read-only grant shows a green &lt;strong&gt;Read-only&lt;/strong&gt; badge and says the app cannot create, modify, delete or trigger anything.&lt;/p&gt;

&lt;p&gt;A write grant shows an amber &lt;strong&gt;Read and write&lt;/strong&gt; badge and spells the capability out: &lt;em&gt;"Create, modify and delete connections, uploads, pipelines, transformations, schedules and notifications — and trigger runs"&lt;/em&gt;, with a caution that the app can change your data and start jobs that use your quota.&lt;/p&gt;

&lt;p&gt;It also names the API key that approving will create — &lt;code&gt;MCP: &amp;lt;app name&amp;gt;&lt;/code&gt; — so you know what to look for later. Revoking that key in &lt;strong&gt;Settings → API Keys&lt;/strong&gt; ends the grant; the authority travels with the credential, so there's no second place to check.&lt;/p&gt;

&lt;h2&gt;
  
  
  Should you grant it?
&lt;/h2&gt;

&lt;p&gt;Often, no — and that's not false modesty. The most common thing people want from an agent here is &lt;em&gt;"help me understand why this pipeline broke,"&lt;/em&gt; which is entirely a read operation. Read-only is the better default for a connection you'll leave in place.&lt;/p&gt;

&lt;p&gt;Write access earns its keep when you want the agent to actually build: scaffold a set of dbt models, wire up a new source, re-run a failed load while you're reading the logs. If that's the job, grant it — knowingly, to a client you trust, with a key you can revoke in one click.&lt;/p&gt;

&lt;p&gt;What we'd suggest either way: use a separate grant for agent work rather than reusing a key from CI. If an agent does something surprising, you want to revoke one thing and know exactly what you broke.&lt;/p&gt;

&lt;h2&gt;
  
  
  What the agent leaves behind
&lt;/h2&gt;

&lt;p&gt;The write tools create dbt models and dlt-backed uploads — the same artifacts you'd have built by hand. &lt;code&gt;create_transformation&lt;/code&gt; writes SQL with &lt;code&gt;ref()&lt;/code&gt; and &lt;code&gt;source()&lt;/code&gt;, testable and runnable by plain &lt;code&gt;dbt run&lt;/code&gt; on a machine that has never heard of Datanika.&lt;/p&gt;

&lt;p&gt;That matters more with write access than without it. An agent can produce a lot of work quickly, and if that work lands in a proprietary format the speed becomes a liability. We wrote about that trade-off on the &lt;a href="https://datanika.io/ai-agents/#portability" rel="noopener noreferrer"&gt;AI agents page&lt;/a&gt;; write tools are the point where it stops being theoretical.&lt;/p&gt;

&lt;h2&gt;
  
  
  Getting started
&lt;/h2&gt;

&lt;p&gt;Full setup, the complete tool list, and the security model are in the &lt;a href="https://datanika.io/docs/mcp-server/" rel="noopener noreferrer"&gt;MCP server guide&lt;/a&gt;. If you're already connected read-only, re-authorize with a client that requests write scopes — the consent screen will show you the difference.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Datanika is an open-source data pipeline platform built on dlt and dbt-core. &lt;a href="https://app.datanika.io/" rel="noopener noreferrer"&gt;Start free&lt;/a&gt; with 10 GB/month, or &lt;a href="https://datanika.io/docs/self-hosting/" rel="noopener noreferrer"&gt;self-host it&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>mcp</category>
      <category>aiagents</category>
      <category>security</category>
      <category>oauth</category>
    </item>
    <item>
      <title>There Is No Maintained dbt Adapter for MySQL. Here Is What That Means for Your Pipeline</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Thu, 12 Nov 2026 21:22:42 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/there-is-no-maintained-dbt-adapter-for-mysql-here-is-what-that-means-for-your-pipeline-40g0</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/there-is-no-maintained-dbt-adapter-for-mysql-here-is-what-that-means-for-your-pipeline-40g0</guid>
      <description>&lt;p&gt;You have an application database in MySQL. You want to model it — staging tables, tests, incremental facts, the usual dbt shape. So you go looking for &lt;code&gt;dbt-mysql&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;You will find one. It was last released in &lt;strong&gt;April 2024&lt;/strong&gt;, and it pins &lt;strong&gt;dbt-core 1.7&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;That pin is the whole story, and it is worth understanding before you decide anything else.&lt;/p&gt;

&lt;h2&gt;
  
  
  What a version pin on your transformation layer costs
&lt;/h2&gt;

&lt;p&gt;An adapter that pins an old &lt;code&gt;dbt-core&lt;/code&gt; does not just deny you new dbt features. It &lt;strong&gt;holds the floor down for everything that shares the environment.&lt;/strong&gt; &lt;code&gt;dbt-core&lt;/code&gt; pulls in a substantial dependency tree, and pinning it to a 2023-era minor version pins large parts of that tree with it. Security updates elsewhere in your stack start colliding with the pin, and the usual resolution is to stop upgrading.&lt;/p&gt;

&lt;p&gt;So the cost is not "we are on an older dbt". The cost is that one adapter, for one database you did not especially need to transform in, becomes the constraint on your whole transformation environment.&lt;/p&gt;

&lt;p&gt;We looked at shipping it anyway. We decided not to, and we say so on the &lt;a href="https://datanika.io/connectors/mysql/" rel="noopener noreferrer"&gt;MySQL connector page&lt;/a&gt; rather than leaving people to find out at the first run.&lt;/p&gt;

&lt;h2&gt;
  
  
  MySQL is a source, and that is not a consolation prize
&lt;/h2&gt;

&lt;p&gt;The instinct when a tool cannot transform in place is to treat it as a gap. It is worth pushing back on that.&lt;/p&gt;

&lt;p&gt;Your MySQL instance is serving an application. It is sized for transactional reads and writes, its indexes are tuned for the queries your product makes, and the last thing it needs is a nightly wave of analytical scans building wide aggregate tables next to production traffic. &lt;strong&gt;Transforming in your operational database is usually the wrong idea even when the tooling supports it.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The shape that works is the one ELT was named for:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Extract&lt;/strong&gt; from MySQL — a whole database, or one table at a time.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Load&lt;/strong&gt; into a warehouse, raw and untransformed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Transform&lt;/strong&gt; there, with dbt, where the compute is separate from the database your customers are hitting.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Step 3 is where your dbt project lives, and the adapter question moves to the warehouse — which is a much better place for it, because that is where the maintained adapters are.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where you can actually run dbt
&lt;/h2&gt;

&lt;p&gt;These are all dbt transformation targets, and all of them are destinations you can land MySQL data into:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;PostgreSQL · SQL Server · ClickHouse · DuckDB · BigQuery · Snowflake · Redshift&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;That list covers most of the reasonable answers. If you already have a warehouse, you almost certainly have one of these. If you do not, DuckDB is a genuinely serious option for small-to-mid volumes and costs nothing to try; &lt;a href="https://datanika.io/connectors/snowflake/" rel="noopener noreferrer"&gt;Snowflake&lt;/a&gt; and BigQuery are the usual answers once concurrency matters.&lt;/p&gt;

&lt;p&gt;The pattern is identical whichever you pick, and we walked through it end to end for a different source in &lt;a href="https://datanika.io/blog/postgresql-to-bigquery/" rel="noopener noreferrer"&gt;PostgreSQL to BigQuery&lt;/a&gt; — the extraction side changes, the modelling side does not.&lt;/p&gt;

&lt;h2&gt;
  
  
  One thing our own tool will not do
&lt;/h2&gt;

&lt;p&gt;Symmetry demands this bit.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;MySQL is not a destination in Datanika either.&lt;/strong&gt; You can extract from it; you cannot load into it. The upload form does not offer MySQL as a destination, and creating an upload that names one anyway — through the API, for example — is refused with a message that says exactly that.&lt;/p&gt;

&lt;p&gt;It is worth being precise about why, because the easy version of that sentence is wrong. The load library underneath us, dlt, &lt;em&gt;can&lt;/em&gt; write to MySQL, through its generic SQLAlchemy destination. Datanika does not use that path, so the limit is ours rather than dlt's. We mention it for the same reason it is on the connector page: if you were planning MySQL → MySQL replication, you should find that out from a blog post rather than from a refused form.&lt;/p&gt;

&lt;h2&gt;
  
  
  The short version
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;There is no maintained dbt adapter for MySQL, and the unmaintained one pins your transformation environment to 2023.&lt;/li&gt;
&lt;li&gt;That is not the blocker it first appears, because &lt;strong&gt;transforming inside your operational database was already the wrong architecture.&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;Extract from MySQL, land in a warehouse, run dbt there. The &lt;a href="https://datanika.io/docs/connectors/mysql/" rel="noopener noreferrer"&gt;MySQL setup guide&lt;/a&gt; covers the extraction half.&lt;/li&gt;
&lt;li&gt;If you specifically need MySQL as a &lt;em&gt;target&lt;/em&gt;, Datanika does not support it. dlt's SQLAlchemy destination can, if you are willing to run dlt yourself.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Datanika is an open-source data platform — extraction, loading, transformation and scheduling in one place. Every connector page lists what the connector cannot do, next to what it can.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>mysql</category>
      <category>dbt</category>
      <category>elt</category>
      <category>dataengineering</category>
    </item>
    <item>
      <title>ClickHouse Speaks Two Protocols. Your Connection Test Speaks One.</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Wed, 11 Nov 2026 20:22:05 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/clickhouse-speaks-two-protocols-your-connection-test-speaks-one-3149</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/clickhouse-speaks-two-protocols-your-connection-test-speaks-one-3149</guid>
      <description>&lt;p&gt;ClickHouse listens on two interfaces. The HTTP one answers on &lt;code&gt;8123&lt;/code&gt;, or &lt;code&gt;8443&lt;/code&gt; with TLS. The native TCP one answers on &lt;code&gt;9000&lt;/code&gt;, or &lt;code&gt;9440&lt;/code&gt; with TLS. They are different protocols on different ports, and a client that can reach one has proved nothing about the other.&lt;/p&gt;

&lt;p&gt;That matters in Datanika because &lt;strong&gt;Test Connection speaks HTTP and a destination load speaks both&lt;/strong&gt;. So the most confusing ClickHouse failure is also the most common one: a green connection test, then a run that creates its tables and dies partway through.&lt;/p&gt;

&lt;p&gt;This post is the map of that failure. The click-by-click setup is in the &lt;a href="https://datanika.io/docs/connectors/clickhouse/" rel="noopener noreferrer"&gt;ClickHouse setup guide&lt;/a&gt;; the field-by-field reference is on the &lt;a href="https://datanika.io/connectors/clickhouse/" rel="noopener noreferrer"&gt;ClickHouse connector page&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  One port field, two ports
&lt;/h2&gt;

&lt;p&gt;The Datanika connection form has a single &lt;strong&gt;Port&lt;/strong&gt; field, and it is the &lt;strong&gt;HTTP&lt;/strong&gt; port. There is no second field for the native port — Datanika derives that one from the &lt;strong&gt;Use HTTPS (TLS)&lt;/strong&gt; checkbox: &lt;code&gt;9000&lt;/code&gt; when it is unticked, &lt;code&gt;9440&lt;/code&gt; when it is ticked.&lt;/p&gt;

&lt;p&gt;A destination load then uses both:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;step&lt;/th&gt;
&lt;th&gt;protocol&lt;/th&gt;
&lt;th&gt;port&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Test Connection (&lt;code&gt;SELECT 1&lt;/code&gt;)&lt;/td&gt;
&lt;td&gt;HTTP&lt;/td&gt;
&lt;td&gt;the one you typed&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;the load's file step&lt;/td&gt;
&lt;td&gt;HTTP&lt;/td&gt;
&lt;td&gt;the one you typed&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;the load's &lt;code&gt;sync&lt;/code&gt; step&lt;/td&gt;
&lt;td&gt;native TCP&lt;/td&gt;
&lt;td&gt;derived from the TLS checkbox&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Open only the HTTP port between Datanika and the server and you get a green test, a set of created tables, and a run that fails at &lt;code&gt;step=sync&lt;/code&gt;. Nothing about the test was wrong. It measured the interface it speaks.&lt;/p&gt;

&lt;p&gt;As a &lt;strong&gt;source&lt;/strong&gt;, ClickHouse speaks HTTP only, so the port you type is the only one that matters in that direction. All of the above is about the destination side.&lt;/p&gt;

&lt;h3&gt;
  
  
  If you once typed the native port to make loads work
&lt;/h3&gt;

&lt;p&gt;Earlier builds passed the stored port straight through as the native one, so putting &lt;code&gt;9000&lt;/code&gt; or &lt;code&gt;9440&lt;/code&gt; in &lt;strong&gt;Port&lt;/strong&gt; was the only way past the &lt;code&gt;sync&lt;/code&gt; step. Datanika now reads that field as the HTTP port and derives the native one, which means a connection still holding &lt;code&gt;9000&lt;/code&gt;/&lt;code&gt;9440&lt;/code&gt; there points the HTTP client at the native interface and stops loading.&lt;/p&gt;

&lt;p&gt;Editing the port on the connection is the entire fix. Nothing needs recreating.&lt;/p&gt;

&lt;h2&gt;
  
  
  What each failure actually says
&lt;/h2&gt;

&lt;p&gt;The useful thing about this class of bug is that ClickHouse is unusually talkative about it.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Wrong port in the form.&lt;/strong&gt; ClickHouse answers over HTTP and names the right port itself. Measured against a stock 24.8 server with &lt;code&gt;9000&lt;/code&gt; in the field:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Connection failed — check your credentials and network settings:
HTTP driver received HTTP status 400, server response:
Port 9000 is for clickhouse-client program You must use port 8123 for HTTP.
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That is a server talking, not a timeout. Set the port to &lt;code&gt;8123&lt;/code&gt;, or &lt;code&gt;8443&lt;/code&gt; with &lt;strong&gt;Use HTTPS (TLS)&lt;/strong&gt; ticked.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Native port blocked or remapped.&lt;/strong&gt; Test Connection is green; the run fails at &lt;code&gt;step=sync&lt;/code&gt;. Open &lt;code&gt;9000&lt;/code&gt;/&lt;code&gt;9440&lt;/code&gt; between Datanika and the server. If your server's native port has been remapped to something else entirely, that cannot be expressed on the form — move the server back to a default native port, or use the connection as a source only.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Host unreachable at all.&lt;/strong&gt; &lt;code&gt;Test connection failed: Connection timed out&lt;/code&gt;. For ClickHouse Cloud this is usually the service's IP access list; for self-hosted it is a firewall rule on the HTTP port.&lt;/p&gt;

&lt;h2&gt;
  
  
  The other green test that hides a red run
&lt;/h2&gt;

&lt;p&gt;There is a second way to pass the test and fail the load, and it has nothing to do with ports.&lt;/p&gt;

&lt;p&gt;The loader reads column types out of &lt;code&gt;INFORMATION_SCHEMA&lt;/code&gt; before it writes. Grant the obvious permissions and nothing else, and Test Connection is still green while the first run fails at &lt;code&gt;step=load&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Code: 497 … datanika_loader: Not enough privileges … ON INFORMATION_SCHEMA.COLUMNS
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;What the destination is left holding is one empty bookkeeping table and none of your data. The grant that prevents it:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;GRANT&lt;/span&gt; &lt;span class="k"&gt;SELECT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;INSERT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;DROP&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt;
  &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;raw_data&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;TO&lt;/span&gt; &lt;span class="n"&gt;datanika_loader&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;GRANT&lt;/span&gt; &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="no"&gt;INFORMATION_SCHEMA&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;TO&lt;/span&gt; &lt;span class="n"&gt;datanika_loader&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Case matters, and it is the part people lose an afternoon to.&lt;/strong&gt; ClickHouse exposes &lt;code&gt;INFORMATION_SCHEMA&lt;/code&gt; and &lt;code&gt;information_schema&lt;/code&gt; as two &lt;em&gt;different&lt;/em&gt; databases, and a grant on one does not cover the other. The uppercase form above is the one the loader needs; granting only the lowercase spelling leaves the run failing exactly as before. Measured on ClickHouse 24.8.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reading the result instead of the badge
&lt;/h2&gt;

&lt;p&gt;All three failures above share a shape: something green certified a narrower thing than the reader assumed it did. We have &lt;a href="https://datanika.io/blog/green-tests-broken-connectors/" rel="noopener noreferrer"&gt;written before about connector tests that pass while the connector is broken&lt;/a&gt;, and ClickHouse is the clearest instance of it we ship.&lt;/p&gt;

&lt;p&gt;So the habit worth building is to check the destination rather than the status badge. After a run, in ClickHouse itself:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;system&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;tables&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="k"&gt;database&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'raw_data'&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;raw_data&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="sb"&gt;`&amp;lt;upload&amp;gt;___&amp;lt;table&amp;gt;`&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Two things about what you will see there. ClickHouse has no schemas, so an upload cannot land in one named after itself — everything goes into the database on the connection, with the upload name folded into each table name as &lt;code&gt;&amp;lt;upload&amp;gt;___&amp;lt;table&amp;gt;&lt;/code&gt;. And dlt writes its own &lt;code&gt;____dlt_loads&lt;/code&gt;, &lt;code&gt;____dlt_pipeline_state&lt;/code&gt; and &lt;code&gt;____dlt_version&lt;/code&gt; bookkeeping tables alongside yours. Seeing only your own tables listed in Datanika is correct, not a partial load.&lt;/p&gt;

&lt;p&gt;A green run means the load finished. Whether it moved what you meant is a question you ask the destination.&lt;/p&gt;

&lt;h2&gt;
  
  
  Related
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/docs/connectors/clickhouse/" rel="noopener noreferrer"&gt;ClickHouse setup guide&lt;/a&gt; — the full walk-through, including the loader user&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/connectors/clickhouse/" rel="noopener noreferrer"&gt;ClickHouse connector reference&lt;/a&gt; — supported engines, ordering keys, load modes&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://datanika.io/docs/scheduling-guide/" rel="noopener noreferrer"&gt;Scheduling guide&lt;/a&gt; — cron syntax, timezones and run-queue behaviour&lt;/li&gt;
&lt;/ul&gt;

</description>
      <category>tutorial</category>
      <category>clickhouse</category>
      <category>destinations</category>
      <category>troubleshooting</category>
    </item>
    <item>
      <title>Your Airtable Sync Will Land Record IDs Where You Expected Names</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Tue, 10 Nov 2026 00:59:40 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/your-airtable-sync-will-land-record-ids-where-you-expected-names-3dgd</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/your-airtable-sync-will-land-record-ids-where-you-expected-names-3dgd</guid>
      <description>&lt;p&gt;Airtable sits in the gap between a spreadsheet and a database, which is exactly why it accumulates things nobody meant to make load-bearing: the CRM tracker, the content calendar, the inventory list, the client roster that finance now reconciles against.&lt;/p&gt;

&lt;p&gt;Getting it into a warehouse is a five-minute job. What is not a five-minute job is the moment afterwards, when you open the landed table and find a column full of this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"recA9dK2mQp1xLzYt"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;where you expected &lt;code&gt;Acme Corp&lt;/code&gt;. Nothing is broken. This post is about why, and about the decision it forces you to make &lt;em&gt;before&lt;/em&gt; you configure the sync rather than after.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why the IDs show up
&lt;/h2&gt;

&lt;p&gt;Airtable's grid shows you a linked record as its display value — the primary field of the row it points at. That display value is a &lt;strong&gt;rendering&lt;/strong&gt;, not the stored data. What the API returns for a linked record field is an array of record IDs, each starting with &lt;code&gt;rec&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;This is an API-level behaviour, not a Datanika quirk, and no connector can paper over it: resolving &lt;code&gt;recA9dK2mQp1xLzYt&lt;/code&gt; to &lt;code&gt;Acme Corp&lt;/code&gt; requires reading the &lt;em&gt;other&lt;/em&gt; table, which the API will not do for you inside a single response.&lt;/p&gt;

&lt;p&gt;The consequence for your pipeline is concrete. &lt;strong&gt;If you sync only the table you care about, its most useful columns arrive as opaque IDs pointing at rows you did not load.&lt;/strong&gt; The fix is to sync both sides and rejoin them downstream — which means you need to know, at configuration time, which tables the one you want actually links to.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 1 — A token, scoped to the bases you mean
&lt;/h2&gt;

&lt;p&gt;Personal access tokens replaced Airtable's legacy API key in 2024. They are scoped per base and per permission, which is a real improvement: you can give Datanika read access to exactly one base.&lt;/p&gt;

&lt;p&gt;At &lt;a href="https://airtable.com/create/tokens" rel="noopener noreferrer"&gt;airtable.com/create/tokens&lt;/a&gt;, create a token named something you will recognise, like &lt;code&gt;datanika-readonly&lt;/code&gt;, and grant exactly two scopes:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;data.records:read&lt;/code&gt; — read records from tables&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;schema.bases:read&lt;/code&gt; — read base schema, so table names and field types are discoverable&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Under &lt;strong&gt;Access&lt;/strong&gt;, add the specific bases. Avoid "All current and future bases" unless you have a reason — a token that automatically gains access to a base created next quarter is a token nobody will remember to review.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Copy the value when it is shown.&lt;/strong&gt; It starts with &lt;code&gt;pat&lt;/code&gt;, and Airtable shows it exactly once. Only read scopes are needed; Datanika never writes back to Airtable.&lt;/p&gt;

&lt;p&gt;You also need the &lt;strong&gt;base ID&lt;/strong&gt;, which is in the base's URL and starts with &lt;code&gt;app&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 2 — The connection
&lt;/h2&gt;

&lt;p&gt;Open &lt;strong&gt;&lt;code&gt;/connections&lt;/code&gt;&lt;/strong&gt; — the form is rendered on the page already — pick the Airtable type, and give it a name, the base ID, and the token. The token is encrypted at rest with Fernet.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 3 — Choosing endpoints, and the checkbox that does the opposite
&lt;/h2&gt;

&lt;p&gt;At &lt;strong&gt;&lt;code&gt;/uploads&lt;/code&gt;&lt;/strong&gt;, create the upload. The name field accepts letters, digits and spaces, and strips the rest as you type, so &lt;code&gt;airtable-daily-sync&lt;/code&gt; becomes &lt;code&gt;airtabledailysync&lt;/code&gt; while &lt;code&gt;Airtable Daily Sync&lt;/code&gt; is kept verbatim — remember that, because the schedule references the upload by name, exactly as saved.&lt;/p&gt;

&lt;p&gt;Because Airtable is a SaaS source, the form shows &lt;strong&gt;Select endpoints to load&lt;/strong&gt; — a checkbox per resource, &lt;strong&gt;all ticked by default&lt;/strong&gt;. Untick anything you do not want; each ticked endpoint becomes its own table in the destination, and unticked ones are not fetched at all.&lt;/p&gt;

&lt;p&gt;Two things about that list are worth knowing before you start unticking.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Unticking every box does not load nothing — it loads the full set.&lt;/strong&gt; An empty selection is treated as "no filter specified", not as "the user chose zero endpoints". If you were planning to untick everything and re-tick one thing, untick carefully; passing through the empty state is fine, but &lt;em&gt;leaving&lt;/em&gt; it empty gives you everything.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The endpoint list is a fixed built-in map, not a live fetch from your account.&lt;/strong&gt; It does not reflect custom objects. If what you need is not on the list, the &lt;a href="https://datanika.io/docs/connectors/rest-api/" rel="noopener noreferrer"&gt;REST API connector&lt;/a&gt; is the way to reach it.&lt;/p&gt;

&lt;p&gt;This is the point where the linked-record problem becomes a configuration decision rather than a surprise. If the table you want has linked record fields, &lt;strong&gt;tick the tables on the other end of those links too.&lt;/strong&gt; You are not loading them for their own sake; you are loading them so the IDs resolve.&lt;/p&gt;

&lt;p&gt;The remaining controls — &lt;strong&gt;Batch size&lt;/strong&gt; (10000 by default) and the &lt;strong&gt;Schema Contract&lt;/strong&gt; dropdowns for &lt;strong&gt;Tables&lt;/strong&gt; / &lt;strong&gt;Columns&lt;/strong&gt; / &lt;strong&gt;Data Type&lt;/strong&gt; — are on every upload regardless of source. There is no write disposition, load mode, source schema or table-name field for a SaaS source; those appear only for SQL databases.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 4 — Run it, then look in the destination
&lt;/h2&gt;

&lt;p&gt;Click &lt;strong&gt;Run&lt;/strong&gt; on the upload's row and watch &lt;code&gt;/runs&lt;/code&gt; for the status badge, timestamps and &lt;strong&gt;Rows&lt;/strong&gt; count.&lt;/p&gt;

&lt;p&gt;The tables land in a schema &lt;strong&gt;derived from the upload's name&lt;/strong&gt; — whitespace runs become single underscores and the whole thing is lower-cased — so &lt;code&gt;airtabledailysync&lt;/code&gt; creates schema &lt;code&gt;airtabledailysync&lt;/code&gt;, and &lt;code&gt;Airtable Daily Sync&lt;/code&gt; creates schema &lt;code&gt;airtable_daily_sync&lt;/code&gt;. dlt's own &lt;code&gt;_dlt_loads&lt;/code&gt;, &lt;code&gt;_dlt_pipeline_state&lt;/code&gt; and &lt;code&gt;_dlt_version&lt;/code&gt; tables are created there too but are not listed in &lt;strong&gt;Models&lt;/strong&gt; — seeing only your own tables is correct.&lt;/p&gt;

&lt;p&gt;Spot-check the row count against the base. A green run means the load finished, not that it moved what you expected.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 5 — Rejoin the linked records
&lt;/h2&gt;

&lt;p&gt;Now the part you configured for. Sync both tables, and resolve the IDs in a dbt model:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;project_name&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;raw_airtable&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;tasks&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;raw_airtable&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;projects&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;
    &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_airtable_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;any&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;project&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;a.project&lt;/code&gt; is the array of record IDs; &lt;code&gt;b._airtable_id&lt;/code&gt; is the linked row's own ID. The &lt;code&gt;any(...)&lt;/code&gt; handles the array, and the &lt;code&gt;left join&lt;/code&gt; is deliberate — a linked field can point at a record that was deleted, and an inner join would silently drop the task rather than showing you a null project.&lt;/p&gt;

&lt;p&gt;If a field can hold multiple links, you have a genuine one-to-many and the join will fan out rows. Decide whether you want one row per task with an aggregated list, or one row per task-project pair. Both are defensible; picking by accident is not.&lt;/p&gt;

&lt;h2&gt;
  
  
  Two failures you will actually hit
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;&lt;code&gt;INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND&lt;/code&gt;.&lt;/strong&gt; The token is missing &lt;code&gt;data.records:read&lt;/code&gt; or &lt;code&gt;schema.bases:read&lt;/code&gt;, or the base is not in its &lt;strong&gt;Access&lt;/strong&gt; list. Edit the token at &lt;a href="https://airtable.com/create/tokens" rel="noopener noreferrer"&gt;airtable.com/create/tokens&lt;/a&gt; and add what is missing — you do not need to regenerate it or update anything in Datanika.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;&lt;code&gt;429 Too Many Requests&lt;/code&gt;.&lt;/strong&gt; Airtable enforces 5 requests per second per base, and a full sync of a large base with many tables will reach it. dlt retries with exponential backoff automatically, so an occasional 429 is not a problem. Persistent ones mean the sync is too wide for one pipeline: split it, with high-priority tables on a fast cadence and the rest nightly.&lt;/p&gt;

&lt;p&gt;A &lt;strong&gt;&lt;code&gt;NOT_FOUND&lt;/code&gt;&lt;/strong&gt; on one specific table almost always means it was renamed or deleted in Airtable after you configured the pipeline. Re-open the upload config and re-select — the base schema is rediscovered each time you open it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 6 — Schedule
&lt;/h2&gt;

&lt;p&gt;At &lt;strong&gt;&lt;code&gt;/schedules&lt;/code&gt;&lt;/strong&gt;: target type &lt;code&gt;upload&lt;/code&gt;, target name exactly as saved (&lt;code&gt;airtabledailysync&lt;/code&gt;), a real five-field cron expression, and a timezone that defaults to &lt;code&gt;UTC&lt;/code&gt;. There is no cadence picker, and leaving an upload unscheduled &lt;em&gt;is&lt;/em&gt; manual-only.&lt;/p&gt;

&lt;h2&gt;
  
  
  The short version
&lt;/h2&gt;

&lt;p&gt;Airtable syncs cleanly. The thing that makes a landed Airtable table useful is not the sync, it is having loaded the tables on the other end of the links — and that is a decision you make at configuration time, before you have any way of noticing you got it wrong.&lt;/p&gt;

&lt;p&gt;Full reference: the &lt;a href="https://datanika.io/docs/connectors/airtable/" rel="noopener noreferrer"&gt;Airtable setup guide&lt;/a&gt; and the &lt;a href="https://datanika.io/connectors/airtable/" rel="noopener noreferrer"&gt;connector page&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>tutorial</category>
      <category>airtable</category>
      <category>connectors</category>
      <category>dbt</category>
    </item>
    <item>
      <title>I Asked Claude to Build a Stripe BigQuery Pipeline. It Did.</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Mon, 09 Nov 2026 12:54:16 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/i-asked-claude-to-build-a-stripe-bigquery-pipeline-it-did-4mal</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/i-asked-claude-to-build-a-stripe-bigquery-pipeline-it-did-4mal</guid>
      <description>&lt;h2&gt;
  
  
  The Prompt
&lt;/h2&gt;

&lt;p&gt;A few days after we shipped the &lt;a href="https://datanika.io/blog/ai-agent-native/" rel="noopener noreferrer"&gt;5-tier agent API&lt;/a&gt;, I ran the cleanest test I could think of. I opened Claude, pasted a Datanika API key, and typed exactly this:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;em&gt;"Read &lt;a href="https://app.datanika.io/llms.txt" rel="noopener noreferrer"&gt;https://app.datanika.io/llms.txt&lt;/a&gt; and build me a Stripe-to-BigQuery pipeline. Load the charges and customers tables daily."&lt;/em&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;No schema. No sample code. No starter template. Just a URL and a sentence.&lt;/p&gt;

&lt;p&gt;What happened next is what an "AI data pipeline" tool is supposed to feel like — and as far as I can tell, it's the only ELT platform where it actually works today.&lt;/p&gt;

&lt;h2&gt;
  
  
  What Claude Did (Transcript, Trimmed)
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Step 1 — Read the discovery doc
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;GET /llms.txt
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;/llms.txt&lt;/code&gt; is a plain-text manifest: base URL, auth header format, rate limits, the 5 agent capability tiers, and pointers to the full OpenAPI spec and the 17-step strategy guide at &lt;code&gt;/api/v1/agent-guide.md&lt;/code&gt;. Claude pulled both. Total: ~8 KB of context. No HTML to parse, no JS to render, no cookie wall.&lt;/p&gt;

&lt;p&gt;This is the piece every other ELT tool is missing. An LLM without a discovery doc has to guess endpoint names from documentation scraped months ago — and that documentation was written for humans, which means it's missing the machine-readable details the agent actually needs (exact parameter names, which fields are required, what errors mean).&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2 — Discover connection types
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;GET /api/v1/meta/connection-types
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Returns JSON for every connector in the catalogue, with full config schemas. Claude picked &lt;code&gt;stripe&lt;/code&gt; and &lt;code&gt;bigquery&lt;/code&gt;, read the schemas, and knew exactly which fields to set. No guessing &lt;code&gt;api_key&lt;/code&gt; vs &lt;code&gt;secret_key&lt;/code&gt; vs &lt;code&gt;token&lt;/code&gt; — the schema said &lt;code&gt;api_key&lt;/code&gt;, so &lt;code&gt;api_key&lt;/code&gt; it was.&lt;/p&gt;

&lt;p&gt;This is the thing I was most skeptical about before we built it. "Why not just document the endpoints?" we thought. Because documentation doesn't compile. A schema does.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 3 — Create source and destination
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;POST /api/v1/connections  {"name": "Stripe Prod", "connection_type": "stripe", ...}
POST /api/v1/connections  {"name": "Warehouse",   "connection_type": "bigquery", ...}
POST /api/v1/connections/{id}/test
POST /api/v1/connections/{id}/test
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Claude tested both connections before doing anything else. I didn't tell it to. The &lt;code&gt;agent-guide.md&lt;/code&gt; golden-path loop says "test before building," and the agent followed it.&lt;/p&gt;

&lt;p&gt;Both passed.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 4 — Introspect the source
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;POST /api/v1/connections/{stripe_id}/introspect
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Returns the list of Stripe tables Datanika's dlt pipeline can extract: &lt;code&gt;charges&lt;/code&gt;, &lt;code&gt;customers&lt;/code&gt;, &lt;code&gt;invoices&lt;/code&gt;, &lt;code&gt;subscriptions&lt;/code&gt;, &lt;code&gt;events&lt;/code&gt;, and a dozen more. Claude matched "charges and customers" from the prompt to the exact table names and moved on.&lt;/p&gt;

&lt;p&gt;No LLM hallucination, no "I think Stripe has a &lt;code&gt;transactions&lt;/code&gt; table" (it doesn't). The source told the agent what tables existed.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 5 — Build the upload and run it
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;POST /api/v1/uploads
  {"source_id": ..., "destination_id": ..., "tables": ["charges", "customers"],
   "write_disposition": "merge"}

POST /api/v1/uploads/{id}/run?wait=true
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;?wait=true&lt;/code&gt; is the part I want to call out. Most ELT APIs let you trigger a run and return immediately — then you have to poll for completion. Agents loop forever on polling. We added blocking mode specifically because &lt;code&gt;wait=true&lt;/code&gt; compresses 30 lines of agent code into one call. The agent fires the request, sits on it for however long the run takes (up to the timeout), and gets back a typed result: success, failure with error code, or timeout.&lt;/p&gt;

&lt;p&gt;Claude's first run succeeded. 4 seconds. 127 charges loaded. 48 customers.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 6 — Write a transformation, compile it, preview it
&lt;/h3&gt;

&lt;p&gt;Claude decided to add a &lt;code&gt;monthly_revenue&lt;/code&gt; model on top of raw charges — I didn't ask it to, but it's a sensible default for a Stripe pipeline, and the &lt;a href="https://app.datanika.io/api/v1/agent-guide.md" rel="noopener noreferrer"&gt;agent guide&lt;/a&gt; explicitly tells agents to offer a starter transformation.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
  &lt;span class="n"&gt;DATE_TRUNC&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MONTH&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="k"&gt;month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;revenue_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;charge_count&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'charges'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'succeeded'&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;month&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then — the step no other ELT platform offers over the API:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;POST /api/v1/transformations/{id}/compile   → 200 OK
POST /api/v1/transformations/{id}/preview   → returns 12 rows of sample output
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Compile-before-run.&lt;/strong&gt; Datanika runs dbt's compile step against the live warehouse schema, catches Jinja errors, catches missing refs, catches type mismatches, catches &lt;code&gt;unsafe_sql&lt;/code&gt; — and returns a typed error code if any of those fail. Claude hit one &lt;code&gt;compilation_error&lt;/code&gt; on its first draft (used &lt;code&gt;total&lt;/code&gt; instead of &lt;code&gt;amount&lt;/code&gt;), the API returned the failed line and column, Claude fixed the SQL and re-compiled. Green on the second try.&lt;/p&gt;

&lt;p&gt;Preview ran a &lt;code&gt;LIMIT 12&lt;/code&gt; against the compiled SQL and returned actual rows. Claude checked they looked sane and moved on.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 7 — Schedule it
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="err"&gt;POST /api/v1/schedules  {"pipeline_id": ..., "cron": "0 2 * * *", "timezone": "UTC"}
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Daily at 2 AM UTC. Done.&lt;/p&gt;

&lt;p&gt;Total time from prompt to running autonomous data pipeline: &lt;strong&gt;~4 minutes, ~23 API calls, zero human intervention after the initial sentence.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Why This Works (And Why Your Other ELT Tool Can't)
&lt;/h2&gt;

&lt;p&gt;I keep going back to the same list. An agent needs five things to build an autonomous data pipeline without supervision:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Machine-readable discovery&lt;/strong&gt; so it knows the platform exists and how to talk to it. (&lt;code&gt;/llms.txt&lt;/code&gt; + &lt;code&gt;/api/v1/agent-guide.md&lt;/code&gt;.)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Typed schemas&lt;/strong&gt; for every config object so it doesn't guess field names. (The &lt;code&gt;/api/v1/meta/*&lt;/code&gt; endpoints.)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Source introspection via API&lt;/strong&gt; so it can match user intent ("charges and customers") to actual source tables. (&lt;code&gt;POST /connections/{id}/introspect&lt;/code&gt;.)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Compile-before-run validation&lt;/strong&gt; so it can fix SQL mistakes before burning warehouse credits or production data. (&lt;code&gt;POST /transformations/{id}/compile&lt;/code&gt;.)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Typed error codes&lt;/strong&gt; so it can branch on failure instead of regex-matching English error messages. (&lt;code&gt;compilation_error&lt;/code&gt;, &lt;code&gt;execution_error&lt;/code&gt;, &lt;code&gt;unsafe_sql&lt;/code&gt;, &lt;code&gt;idempotent_replay&lt;/code&gt;.)&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;&lt;a href="https://datanika.io/compare/fivetran/" rel="noopener noreferrer"&gt;Fivetran has no agent API.&lt;/a&gt; &lt;a href="https://datanika.io/compare/airbyte/" rel="noopener noreferrer"&gt;Airbyte has connectors but no discovery doc and no compile-before-run.&lt;/a&gt; dbt Cloud compiles, but only inside its own UI — not over the API. Stitch's API is read-mostly. Hevo's is a thin CRUD layer with no schemas exposed.&lt;/p&gt;

&lt;p&gt;We're the only &lt;a href="https://datanika.io/ai-agents/" rel="noopener noreferrer"&gt;AI-native ETL&lt;/a&gt; platform on this list where you can hand an LLM a URL and walk away.&lt;/p&gt;

&lt;h2&gt;
  
  
  What This Unlocks
&lt;/h2&gt;

&lt;p&gt;The thing I didn't expect when we shipped the 5-tier API is how quickly the interaction model shifts. When you're the human, you stop writing pipeline code and start writing pipeline &lt;em&gt;intent&lt;/em&gt;.&lt;/p&gt;

&lt;p&gt;"Load charges and customers daily" is not a spec a human could hand to another human and expect a finished pipeline. But it's a complete spec for an agent that has typed schemas, introspection, and compile validation. The missing 98% of the spec is filled in by the platform, not the person.&lt;/p&gt;

&lt;p&gt;That's the actual thesis of LLM data pipelines — not "AI writes your SQL" but "the platform exposes enough structured surface area that an agent can fill in the gaps deterministically."&lt;/p&gt;

&lt;h2&gt;
  
  
  Try It Yourself
&lt;/h2&gt;

&lt;p&gt;If you have a free Datanika account and ten minutes:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Grab an API key from &lt;strong&gt;Settings → API Keys&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Paste this into Claude, ChatGPT, or any LLM with tool-use:&lt;/li&gt;
&lt;/ol&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;em&gt;"Read &lt;a href="https://app.datanika.io/llms.txt" rel="noopener noreferrer"&gt;https://app.datanika.io/llms.txt&lt;/a&gt;, then build a [your-source]-to-[your-destination] pipeline that loads [your-tables] daily. Use API key &lt;code&gt;etf_...&lt;/code&gt;."&lt;/em&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;ol&gt;
&lt;li&gt;Watch it work.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;For the impatient, our &lt;a href="https://datanika.io/templates/stripe-to-postgres/" rel="noopener noreferrer"&gt;Stripe → Postgres template&lt;/a&gt; collapses steps 2-4 into a single pre-validated config. Point the agent at the template URL and it'll skip straight to running.&lt;/p&gt;

&lt;p&gt;Full details on the API surface are in the &lt;a href="https://datanika.io/docs/ai-agents/" rel="noopener noreferrer"&gt;AI Agents docs&lt;/a&gt;, and the behind-the-scenes post on why we built all this is at &lt;a href="https://datanika.io/blog/ai-agent-native/" rel="noopener noreferrer"&gt;Datanika is AI-Agent Native&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;We also &lt;a href="https://datanika.io/compare/fivetran/" rel="noopener noreferrer"&gt;compare to Fivetran&lt;/a&gt; and &lt;a href="https://datanika.io/compare/airbyte/" rel="noopener noreferrer"&gt;Airbyte&lt;/a&gt; if you want to see the capability gap on one page.&lt;/p&gt;

&lt;h2&gt;
  
  
  What's Next
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Update:&lt;/strong&gt; the MCP server shipped. &lt;code&gt;uvx datanika-mcp&lt;/code&gt; wraps the whole 5-tier API as 25 typed tools, so Claude Code, Claude Desktop, and Cursor can build pipelines without any HTTP ceremony — read-only until you pass &lt;code&gt;--allow-write&lt;/code&gt;. Setup is in the &lt;a href="https://datanika.io/docs/mcp-server/" rel="noopener noreferrer"&gt;MCP server guide&lt;/a&gt;. &lt;a href="https://app.datanika.io" rel="noopener noreferrer"&gt;Create a free account&lt;/a&gt; for an API key — no credit card, 10 GB/month of data.&lt;/p&gt;

&lt;p&gt;The point isn't that an AI built a pipeline. The point is that the pipeline platform was built so an AI &lt;em&gt;could&lt;/em&gt;.&lt;/p&gt;

</description>
      <category>aiagents</category>
      <category>llm</category>
      <category>tutorial</category>
      <category>api</category>
    </item>
    <item>
      <title>Google Sheets to Your Warehouse: the Share Step Is the One That Fails</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Sat, 26 Sep 2026 00:00:41 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/google-sheets-to-your-warehouse-the-share-step-is-the-one-that-fails-39m9</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/google-sheets-to-your-warehouse-the-share-step-is-the-one-that-fails-39m9</guid>
      <description>&lt;p&gt;Google Sheets is the most common shadow database in any company. Marketing keeps campaign spend in one, finance maintains the budget model in another, ops runs a lightweight CRM in a third. None of it is in the warehouse, so none of it can be joined to anything, so somebody copy-pastes it into a dashboard once a month and everyone quietly agrees not to ask how fresh it is.&lt;/p&gt;

&lt;p&gt;Landing a sheet in your warehouse is genuinely quick — a service account, four fields, a cron. This post is about the one step in the middle that fails for almost everybody the first time, and about a button that will refuse to tell you whether it worked.&lt;/p&gt;

&lt;h2&gt;
  
  
  The setup, briefly
&lt;/h2&gt;

&lt;p&gt;Google Sheets is &lt;strong&gt;source-only&lt;/strong&gt; in Datanika: you land it somewhere, you do not write back to it. You need a destination warehouse already connected — &lt;a href="https://datanika.io/connectors/postgresql/" rel="noopener noreferrer"&gt;PostgreSQL&lt;/a&gt; is the quickest if you do not have one yet — and a Google Cloud project with the &lt;strong&gt;Google Sheets API&lt;/strong&gt; enabled.&lt;/p&gt;

&lt;p&gt;If you already run &lt;a href="https://datanika.io/connectors/bigquery/" rel="noopener noreferrer"&gt;BigQuery&lt;/a&gt; with Datanika, reuse that same project and its service account; you only need to enable the Sheets API on it and do the sharing step below.&lt;/p&gt;

&lt;p&gt;Create a service account — &lt;strong&gt;IAM &amp;amp; Admin → Service accounts&lt;/strong&gt; — and give it &lt;strong&gt;no IAM roles at all&lt;/strong&gt;. This trips people up, because granting a role feels like the responsible thing to do. It is not relevant here: the service account reaches Sheets through the Sheets API, not through GCP resources, so a role grant would widen its access without enabling anything you need. Create a &lt;strong&gt;JSON key&lt;/strong&gt;, download it, and note the account's email address:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;datanika-sheets-reader@your-project.iam.gserviceaccount.com
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That address is the whole story of this post.&lt;/p&gt;

&lt;h2&gt;
  
  
  The step that fails
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;A Google service account can only read spreadsheets that have been explicitly shared with it.&lt;/strong&gt; Not sheets your account can see. Not sheets in a Drive folder it can reach. The specific spreadsheet, shared with that specific address, the same way you would share it with a colleague.&lt;/p&gt;

&lt;p&gt;So: open the sheet, click &lt;strong&gt;Share&lt;/strong&gt;, paste &lt;code&gt;datanika-sheets-reader@your-project.iam.gserviceaccount.com&lt;/code&gt;, set it to &lt;strong&gt;Viewer&lt;/strong&gt; — Datanika never writes to Google Sheets — and, the part that catches people, &lt;strong&gt;untick "Notify people"&lt;/strong&gt; before you confirm. The service account has no mailbox, so there is nobody to notify.&lt;/p&gt;

&lt;p&gt;Repeat for every spreadsheet you want this connection to read. There is no folder-level shortcut.&lt;/p&gt;

&lt;h2&gt;
  
  
  The button that says "not tested"
&lt;/h2&gt;

&lt;p&gt;Now add the connection. Open &lt;strong&gt;&lt;code&gt;/connections&lt;/code&gt;&lt;/strong&gt; — the form is already on the page, there is no "New Connection" button to hunt for — pick &lt;code&gt;google_sheets&lt;/code&gt; from the type dropdown, and fill in three fields:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Connection Name&lt;/strong&gt; — something you will recognise later, like &lt;code&gt;gsheets-marketing-budget&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Spreadsheet URL&lt;/strong&gt; — the &lt;strong&gt;full&lt;/strong&gt; URL, &lt;code&gt;https://docs.google.com/spreadsheets/d/&amp;lt;ID&amp;gt;/edit&lt;/code&gt;, not just the ID.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Service Account JSON&lt;/strong&gt; — the entire contents of the key file. It is encrypted at rest with Fernet.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Then click &lt;strong&gt;Test Connection&lt;/strong&gt;, and watch it decline to give you a verdict. It returns a neutral &lt;strong&gt;not tested&lt;/strong&gt;, carrying the reason.&lt;/p&gt;

&lt;p&gt;This is deliberate, and it is worth explaining, because a lot of tools would have shown you a green tick here.&lt;/p&gt;

&lt;p&gt;Verifying a service-account credential means minting an OAuth token. That is a real check, and it would pass — the JSON is well-formed, the key is valid, the API is enabled. But it would tell you nothing about the failure you are actually going to hit, because &lt;strong&gt;the sharing permission is checked per upload, not per connection&lt;/strong&gt;. A green tick at this point would be an accurate statement about the credential and a misleading one about the pipeline, and the person reading it cannot tell those apart.&lt;/p&gt;

&lt;p&gt;The alternative — a red cross — is worse in the other direction: a connection that is fine, reported as broken. Reporting an unverified thing as working and reporting it as failed are the same lie told in two directions. So the button says what is true: it has not been tested here, and &lt;strong&gt;the first real verification is the first run&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Click &lt;strong&gt;Create Connection&lt;/strong&gt; and go get one.&lt;/p&gt;

&lt;h2&gt;
  
  
  Configure the upload
&lt;/h2&gt;

&lt;p&gt;Extract-load lives at &lt;strong&gt;&lt;code&gt;/uploads&lt;/code&gt;&lt;/strong&gt;, not on the connection. Connection rows offer Test / Edit / Copy / Delete and nothing else, and &lt;code&gt;/pipelines&lt;/code&gt; is the &lt;strong&gt;dbt&lt;/strong&gt; builder — a different thing entirely.&lt;/p&gt;

&lt;p&gt;Open &lt;code&gt;/uploads&lt;/code&gt;, and note the first quirk while you type: &lt;strong&gt;the upload name accepts letters, digits and spaces&lt;/strong&gt;, and strips everything else as you type. &lt;code&gt;sheets-daily-sync&lt;/code&gt; becomes &lt;code&gt;sheetsdailysync&lt;/code&gt; in front of you, while &lt;code&gt;Sheets Daily Sync&lt;/code&gt; is kept verbatim. This matters more than it looks, because the schedule you create later references the upload &lt;strong&gt;by name&lt;/strong&gt;, exactly as saved.&lt;/p&gt;

&lt;p&gt;Pick your source and destination connections — the pickers list entries as &lt;code&gt;16 — myconnection (postgres)&lt;/code&gt;, so id, name, type — and then set:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Sheet Names&lt;/strong&gt; &lt;em&gt;(optional, comma-separated)&lt;/em&gt; — name the tabs you want. &lt;strong&gt;Leave it empty to load every tab in the spreadsheet.&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Batch size&lt;/strong&gt; — 10000 by default.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Schema Contract&lt;/strong&gt; — three dropdowns, &lt;strong&gt;Tables&lt;/strong&gt; / &lt;strong&gt;Columns&lt;/strong&gt; / &lt;strong&gt;Data Type&lt;/strong&gt;, deciding whether a changed incoming shape evolves the destination or fails the run. For a spreadsheet that humans edit, this is the setting worth thinking about hardest: somebody &lt;em&gt;will&lt;/em&gt; add a column.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;There is no write disposition, load mode, source schema or table-name field, and that is not an omission — those controls are rendered only when the source is a SQL database.&lt;/p&gt;

&lt;h2&gt;
  
  
  The first run is the actual test
&lt;/h2&gt;

&lt;p&gt;Hit &lt;strong&gt;Run&lt;/strong&gt; on the upload's row. There is no "Run now" on a pipeline page; the trigger lives on the upload's own row. Watch &lt;code&gt;/runs&lt;/code&gt;: status badge, start and finish timestamps, a &lt;strong&gt;Rows&lt;/strong&gt; count, and a &lt;strong&gt;Logs&lt;/strong&gt; icon for the detail.&lt;/p&gt;

&lt;p&gt;If the share step went wrong, this is where you find out.&lt;/p&gt;

&lt;p&gt;When it finishes, open &lt;strong&gt;Models&lt;/strong&gt; (&lt;code&gt;/models&lt;/code&gt;). Your tables land in a schema &lt;strong&gt;derived from the upload's name&lt;/strong&gt;: whitespace runs become single underscores and the whole thing is lower-cased. So &lt;code&gt;sheetsdailysync&lt;/code&gt; creates schema &lt;code&gt;sheetsdailysync&lt;/code&gt;, while &lt;code&gt;Sheets Daily Sync&lt;/code&gt; — the name this post told you is kept verbatim — creates schema &lt;code&gt;sheets_daily_sync&lt;/code&gt;. dlt also writes its own &lt;code&gt;_dlt_loads&lt;/code&gt;, &lt;code&gt;_dlt_pipeline_state&lt;/code&gt; and &lt;code&gt;_dlt_version&lt;/code&gt; bookkeeping tables into that schema, and Models does not list them; seeing only your own tables there is correct, not a partial load. There is no target-schema field to choose.&lt;/p&gt;

&lt;p&gt;Then do the thing the badge cannot do for you: &lt;strong&gt;spot-check the row count against the sheet.&lt;/strong&gt; A green run means the load finished. It does not mean it moved what you expected — an empty tab and a tab you forgot to share are very different problems that can produce the same colour.&lt;/p&gt;

&lt;h2&gt;
  
  
  Put it on a cron
&lt;/h2&gt;

&lt;p&gt;Schedules are at &lt;strong&gt;&lt;code&gt;/schedules&lt;/code&gt;&lt;/strong&gt;, and reference the upload by name:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Target type&lt;/strong&gt; — &lt;code&gt;upload&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Target name&lt;/strong&gt; — exactly as saved, so &lt;code&gt;sheetsdailysync&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cron expression&lt;/strong&gt; — a real five-field cron string. There is no cadence picker and no "manual only" option; leaving an upload unscheduled &lt;em&gt;is&lt;/em&gt; manual-only. &lt;code&gt;0 * * * *&lt;/code&gt; hourly, &lt;code&gt;0 */6 * * *&lt;/code&gt; every six hours, &lt;code&gt;0 3 * * *&lt;/code&gt; nightly at 03:00.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Timezone&lt;/strong&gt; — &lt;code&gt;UTC&lt;/code&gt; by default, and the cron is evaluated in it, which matters for daily and weekly cadences.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Then wire failure alerts in &lt;strong&gt;Settings → Notifications&lt;/strong&gt;, because a spreadsheet pipeline breaks for a reason no other pipeline has: somebody renames a tab.&lt;/p&gt;

&lt;h2&gt;
  
  
  The one-line version
&lt;/h2&gt;

&lt;p&gt;Everything in a Sheets sync is easy except remembering that the service account is a stranger to your spreadsheet until you introduce them. If a run fails and the credential is fine, you already know which step to go back to.&lt;/p&gt;

&lt;p&gt;Full field-by-field reference: the &lt;a href="https://datanika.io/docs/connectors/google-sheets/" rel="noopener noreferrer"&gt;Google Sheets setup guide&lt;/a&gt; and the &lt;a href="https://datanika.io/connectors/google-sheets/" rel="noopener noreferrer"&gt;connector page&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>tutorial</category>
      <category>googlesheets</category>
      <category>connectors</category>
      <category>elt</category>
    </item>
    <item>
      <title>A Customer 360 from HubSpot and Stripe: the Join Is the Work</title>
      <dc:creator>Evgenii Timofeev</dc:creator>
      <pubDate>Fri, 25 Sep 2026 08:07:46 +0000</pubDate>
      <link>https://dev.to/eu_ti_f127c5b5d7535b7174f/a-customer-360-from-hubspot-and-stripe-the-join-is-the-work-22f1</link>
      <guid>https://dev.to/eu_ti_f127c5b5d7535b7174f/a-customer-360-from-hubspot-and-stripe-the-join-is-the-work-22f1</guid>
      <description>&lt;p&gt;Every "customer 360" tutorial shows you the easy half. Sync your CRM, sync your billing system, write a &lt;code&gt;sources.yml&lt;/code&gt;, and then — with no ceremony at all — this line:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;hubspot_contacts&lt;/span&gt; &lt;span class="n"&gt;hc&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;hc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;sc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That line is where the tutorial ends and the work starts. HubSpot and Stripe share &lt;strong&gt;no identifier&lt;/strong&gt;. Not one. There is no HubSpot ID in Stripe and no Stripe ID in HubSpot unless somebody deliberately put it there. The only overlapping &lt;em&gt;value&lt;/em&gt; is an email address, typed by a human into two different forms, possibly years apart, possibly not the same address.&lt;/p&gt;

&lt;p&gt;So the question this post answers is not "how do I join these." It is &lt;strong&gt;what fraction of your customers actually match, how do you find out, and what do you do with the ones that don't&lt;/strong&gt; — because in every real dataset there are some, and the difference between a trustworthy customer 360 and a misleading one is entirely in how honestly you handle them.&lt;/p&gt;

&lt;p&gt;If you want the Stripe-only revenue modelling first — MRR, churn, LTV — that is &lt;a href="https://datanika.io/blog/stripe-revenue-dashboard-dbt/" rel="noopener noreferrer"&gt;a separate post&lt;/a&gt;. This one assumes you have Stripe landed and adds HubSpot beside it.&lt;/p&gt;

&lt;h2&gt;
  
  
  What the two systems actually give you
&lt;/h2&gt;

&lt;p&gt;Worth being precise, because half the tutorials out there join against tables that don't exist.&lt;/p&gt;

&lt;p&gt;In Datanika, HubSpot and Stripe are SaaS sources, so the upload form shows &lt;strong&gt;Select endpoints to load&lt;/strong&gt; — a checkbox per resource. The full lists:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Source&lt;/th&gt;
&lt;th&gt;Endpoints&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;HubSpot&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;companies&lt;/code&gt;, &lt;code&gt;contacts&lt;/code&gt;, &lt;code&gt;deals&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Stripe&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;charges&lt;/code&gt;, &lt;code&gt;customers&lt;/code&gt;, &lt;code&gt;invoices&lt;/code&gt;, &lt;code&gt;prices&lt;/code&gt;, &lt;code&gt;products&lt;/code&gt;, &lt;code&gt;subscriptions&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Nine tables. Scan them for a shared key and you will not find one. &lt;code&gt;hubspot.contacts&lt;/code&gt; has an email and an associated company; &lt;code&gt;stripe.customers&lt;/code&gt; has an email and a name. That is the entire overlap, and both sides are free text.&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Where the tables land.&lt;/strong&gt; A SaaS source has no write-disposition, load-mode or target-schema field — those are rendered only for SQL database sources. On &lt;strong&gt;BigQuery / Snowflake / Databricks&lt;/strong&gt; the schema is the &lt;strong&gt;Dataset&lt;/strong&gt;/&lt;strong&gt;Schema&lt;/strong&gt; field on the &lt;em&gt;destination connection&lt;/em&gt;. On &lt;strong&gt;Postgres / MySQL / DuckDB&lt;/strong&gt; it is the &lt;strong&gt;upload's own name, snake-cased&lt;/strong&gt; — and upload names accept letters, digits and spaces only, so you name the upload &lt;strong&gt;&lt;code&gt;Raw Hubspot&lt;/code&gt;&lt;/strong&gt; and get the schema &lt;code&gt;raw_hubspot&lt;/code&gt;. Check the &lt;strong&gt;Schema&lt;/strong&gt; column in &lt;strong&gt;Models&lt;/strong&gt; — the sidebar entry for Datanika's data catalog, at &lt;strong&gt;&lt;code&gt;/models&lt;/code&gt;&lt;/strong&gt; — for the real name before writing &lt;code&gt;sources.yml&lt;/code&gt;; a wrong schema name is the most common reason the models below don't resolve.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;Two uploads, two schemas:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="c1"&gt;# models/staging/sources.yml&lt;/span&gt;
&lt;span class="na"&gt;version&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="m"&gt;2&lt;/span&gt;

&lt;span class="na"&gt;sources&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;raw_stripe&lt;/span&gt;
    &lt;span class="na"&gt;tables&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="pi"&gt;[{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;customers&lt;/span&gt;&lt;span class="pi"&gt;},&lt;/span&gt; &lt;span class="pi"&gt;{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;subscriptions&lt;/span&gt;&lt;span class="pi"&gt;},&lt;/span&gt; &lt;span class="pi"&gt;{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;invoices&lt;/span&gt;&lt;span class="pi"&gt;}]&lt;/span&gt;
  &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;raw_hubspot&lt;/span&gt;
    &lt;span class="na"&gt;tables&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="pi"&gt;[{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;contacts&lt;/span&gt;&lt;span class="pi"&gt;},&lt;/span&gt; &lt;span class="pi"&gt;{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;companies&lt;/span&gt;&lt;span class="pi"&gt;},&lt;/span&gt; &lt;span class="pi"&gt;{&lt;/span&gt;&lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="nv"&gt;deals&lt;/span&gt;&lt;span class="pi"&gt;}]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;HubSpot returns default properties only.&lt;/strong&gt; If &lt;code&gt;email&lt;/code&gt; or &lt;code&gt;domain&lt;/code&gt; is missing from a landed table, it is not a Datanika bug — HubSpot's API returns a default property set unless the pipeline asks for more. Add the custom properties you need to the resource config before you go hunting through dbt.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;h2&gt;
  
  
  Step 1 — Normalize the identifiers before you join anything
&lt;/h2&gt;

&lt;p&gt;Never join on a raw email column. Email is case-insensitive in the domain part, effectively case-insensitive at every provider anyone uses, frequently has trailing whitespace from a paste, and Gmail ignores dots and &lt;code&gt;+tags&lt;/code&gt;. Two staging models, one job each: produce a &lt;code&gt;match_email&lt;/code&gt; you can trust and a &lt;code&gt;email_domain&lt;/code&gt; you can fall back to.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/staging/stg_hubspot__contacts.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'view'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;hs_object_id&lt;/span&gt;                              &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;contact_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;associatedcompanyid&lt;/span&gt;                       &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;company_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;email&lt;/span&gt;                                     &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;email_raw&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;                        &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;split&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;&lt;span class="p"&gt;)),&lt;/span&gt; &lt;span class="s1"&gt;'@'&lt;/span&gt;&lt;span class="p"&gt;)[&lt;/span&gt;&lt;span class="k"&gt;offset&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)]&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;email_domain&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="c1"&gt;-- BigQuery&lt;/span&gt;
    &lt;span class="n"&gt;firstname&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;lastname&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;createdate&lt;/span&gt;                                &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;source&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'raw_hubspot'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'contacts'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;email&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/staging/stg_stripe__customers.sql  (extends the version in the revenue post)&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'view'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt;                                        &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;email&lt;/span&gt;                                     &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;email_raw&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;                        &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;split&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;&lt;span class="p"&gt;)),&lt;/span&gt; &lt;span class="s1"&gt;'@'&lt;/span&gt;&lt;span class="p"&gt;)[&lt;/span&gt;&lt;span class="k"&gt;offset&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)]&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;email_domain&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="c1"&gt;-- the exact join key, when someone was disciplined enough to write it&lt;/span&gt;
    &lt;span class="n"&gt;json_value&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'$.hubspot_contact_id'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;hubspot_contact_id_from_metadata&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;timestamp_seconds&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;source&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'raw_stripe'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'customers'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Dialect.&lt;/strong&gt; &lt;code&gt;split(...)[offset(1)]&lt;/code&gt; and &lt;code&gt;json_value&lt;/code&gt; are BigQuery. Snowflake: &lt;code&gt;split_part(lower(trim(email)), '@', 2)&lt;/code&gt; and &lt;code&gt;metadata:hubspot_contact_id::string&lt;/code&gt;. Postgres/DuckDB: &lt;code&gt;split_part(...)&lt;/code&gt; and &lt;code&gt;metadata -&amp;gt;&amp;gt; 'hubspot_contact_id'&lt;/code&gt;. Everything after staging is plain SQL.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;The two remaining HubSpot models are thin. Note that &lt;code&gt;companies.domain&lt;/code&gt; gets the same &lt;code&gt;lower(trim(...))&lt;/code&gt; treatment — it is the Tier C join key, and a stray capital there costs you matches for no reason:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/staging/stg_hubspot__companies.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'view'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;hs_object_id&lt;/span&gt;        &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;company_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;domain&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="k"&gt;domain&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;source&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'raw_hubspot'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'companies'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="k"&gt;domain&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/staging/stg_hubspot__deals.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'view'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;hs_object_id&lt;/span&gt;       &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;deal_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;associatedcompanyid&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;company_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="c1"&gt;-- see the caveat below&lt;/span&gt;
    &lt;span class="n"&gt;dealname&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;dealstage&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;closedate&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;source&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'raw_hubspot'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'deals'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Check the deal→company column before you trust it.&lt;/strong&gt; In HubSpot, a deal's link to a company is an &lt;em&gt;association&lt;/em&gt;, not a plain property, and which column (if any) lands depends on the properties your pipeline requests. Open &lt;strong&gt;Models&lt;/strong&gt; and look at the landed &lt;code&gt;deals&lt;/code&gt; table. If there is no company column, either add the association to the resource config or drop the two deal columns from the final model — the customer 360 is still worth building without them. Do not join deals on company &lt;em&gt;name&lt;/em&gt;.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;h2&gt;
  
  
  Step 2 — Three joins, in descending order of trust
&lt;/h2&gt;

&lt;p&gt;This is the part worth reading twice. There is not one join. There are three, they have very different reliability, and a customer 360 that treats them as interchangeable is quietly wrong.&lt;/p&gt;

&lt;h3&gt;
  
  
  Tier A — an explicit key in Stripe &lt;code&gt;metadata&lt;/code&gt; (exact)
&lt;/h3&gt;

&lt;p&gt;Stripe lets you attach arbitrary &lt;code&gt;metadata&lt;/code&gt; to a customer. If your signup flow writes the HubSpot contact ID there, you have a real foreign key and none of the rest of this post applies to those rows.&lt;/p&gt;

&lt;p&gt;Almost nobody has this on day one, because it has to be decided &lt;em&gt;before&lt;/em&gt; the customers exist. &lt;strong&gt;If you take one action from this post, make it this one&lt;/strong&gt;: start writing &lt;code&gt;hubspot_contact_id&lt;/code&gt; into Stripe customer metadata today. It does nothing for your backlog and it makes every future row exact.&lt;/p&gt;

&lt;h3&gt;
  
  
  Tier B — normalized email (good, and incomplete)
&lt;/h3&gt;

&lt;p&gt;The workhorse. Matches whenever the human typed the same address into both systems.&lt;/p&gt;

&lt;p&gt;It fails in ways worth knowing, because each one is a real customer in your unmatched pile:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;signed up with &lt;code&gt;dana@acme.com&lt;/code&gt;, billing goes through &lt;code&gt;accounts-payable@acme.com&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;signed up with a personal address, expensed it later&lt;/li&gt;
&lt;li&gt;the company was acquired and the domain changed&lt;/li&gt;
&lt;li&gt;one side has a typo&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Tier C — email domain → company domain (recovers real customers, over-matches on public domains)
&lt;/h3&gt;

&lt;p&gt;When the emails differ but both live at &lt;code&gt;acme.com&lt;/code&gt;, HubSpot's &lt;code&gt;companies.domain&lt;/code&gt; bridges them. This is how you catch the &lt;code&gt;accounts-payable@&lt;/code&gt; case, and it is the tier that needs a guard:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;&lt;code&gt;gmail.com&lt;/code&gt; is not a company.&lt;/strong&gt; Join two consumer addresses on their domain and you will merge unrelated people into one "customer" with a straight face. Exclude free providers explicitly, and treat a domain matching many distinct Stripe customers as a signal to stop rather than a bigger match.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/marts/customer_identity_map.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'table'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_stripe__customers'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}),&lt;/span&gt;
     &lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_hubspot__contacts'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}),&lt;/span&gt;
     &lt;span class="n"&gt;companies&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_hubspot__companies'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}),&lt;/span&gt;

&lt;span class="n"&gt;free_domains&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;domain&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="k"&gt;unnest&lt;/span&gt;&lt;span class="p"&gt;([&lt;/span&gt;
        &lt;span class="s1"&gt;'gmail.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'googlemail.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'yahoo.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'outlook.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'hotmail.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="s1"&gt;'live.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'icloud.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'me.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'proton.me'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'protonmail.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'aol.com'&lt;/span&gt;
    &lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="k"&gt;domain&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;

&lt;span class="c1"&gt;-- Tier C is only trustworthy where the domain identifies ONE company. A domain&lt;/span&gt;
&lt;span class="c1"&gt;-- shared by many Stripe customers is either a free provider we missed or an&lt;/span&gt;
&lt;span class="c1"&gt;-- agency billing for several clients; both should fall through to unmatched.&lt;/span&gt;
&lt;span class="n"&gt;safe_domains&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;email_domain&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;email_domain&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;domain&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;free_domains&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;email_domain&lt;/span&gt;
    &lt;span class="k"&gt;having&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;

&lt;span class="n"&gt;tier_a&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'A_metadata'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_tier&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
    &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;hubspot_contact_id_from_metadata&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;

&lt;span class="n"&gt;tier_b&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'B_email'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_tier&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
    &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;match_email&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;match_email&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_a&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;

&lt;span class="n"&gt;tier_c&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'C_domain'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_tier&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
    &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;safe_domains&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email_domain&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email_domain&lt;/span&gt;
    &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;companies&lt;/span&gt; &lt;span class="n"&gt;co&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;domain&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email_domain&lt;/span&gt;
    &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_a&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;

&lt;span class="n"&gt;unmatched&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;cast&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;string&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;contact_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'UNMATCHED'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;match_tier&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;stripe&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_a&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_c&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_a&lt;/span&gt;
&lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_b&lt;/span&gt;
&lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tier_c&lt;/span&gt;
&lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;unmatched&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Two design choices worth stating, because they are the difference between this and the one-liner:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Every Stripe customer appears exactly once&lt;/strong&gt;, including the ones that matched nothing. A map that silently drops rows turns "we couldn't match 18% of your revenue" into "revenue is 18% lower than Stripe says," and someone will spend a day on that.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;match_tier&lt;/code&gt; is a column, not a comment.&lt;/strong&gt; It travels downstream, so any number built on this map can be recomputed for exact matches only.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Step 3 — Measure the match rate; don't assume it
&lt;/h2&gt;

&lt;p&gt;Here is the model that makes this a system instead of a query. It answers &lt;em&gt;"how much of this do I believe?"&lt;/em&gt; and it answers it every run.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/marts/customer_identity_coverage.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'table'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;match_tier&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                                                    &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;customers&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(),&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;          &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;pct_of_customers&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'customer_identity_map'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;match_tier&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then turn the number into a gate. dbt tests fail builds; that is the point:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="c1"&gt;# models/marts/schema.yml&lt;/span&gt;
&lt;span class="na"&gt;version&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="m"&gt;2&lt;/span&gt;

&lt;span class="na"&gt;models&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;customer_identity_map&lt;/span&gt;
    &lt;span class="na"&gt;columns&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
      &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;customer_id&lt;/span&gt;
        &lt;span class="na"&gt;tests&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="pi"&gt;[&lt;/span&gt;&lt;span class="nv"&gt;not_null&lt;/span&gt;&lt;span class="pi"&gt;,&lt;/span&gt; &lt;span class="nv"&gt;unique&lt;/span&gt;&lt;span class="pi"&gt;]&lt;/span&gt;        &lt;span class="c1"&gt;# a fan-out here means a duplicate contact&lt;/span&gt;
      &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;name&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;match_tier&lt;/span&gt;
        &lt;span class="na"&gt;tests&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
          &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;accepted_values&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
              &lt;span class="na"&gt;values&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="pi"&gt;[&lt;/span&gt;&lt;span class="s1"&gt;'&lt;/span&gt;&lt;span class="s"&gt;A_metadata'&lt;/span&gt;&lt;span class="pi"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'&lt;/span&gt;&lt;span class="s"&gt;B_email'&lt;/span&gt;&lt;span class="pi"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'&lt;/span&gt;&lt;span class="s"&gt;C_domain'&lt;/span&gt;&lt;span class="pi"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'&lt;/span&gt;&lt;span class="s"&gt;UNMATCHED'&lt;/span&gt;&lt;span class="pi"&gt;]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- tests/assert_identity_coverage_above_threshold.sql&lt;/span&gt;
&lt;span class="c1"&gt;-- Fails when unmatched customers exceed 25%. Set the threshold to a little worse&lt;/span&gt;
&lt;span class="c1"&gt;-- than today's real number, then tighten it as you fix the data. A threshold you&lt;/span&gt;
&lt;span class="c1"&gt;-- have never been near is not a test — it is decoration.&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt;
        &lt;span class="n"&gt;countif&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;match_tier&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'UNMATCHED'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;unmatched_rate&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'customer_identity_map'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;unmatched_rate&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;25&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run it the first time and read the number before you set the threshold. Whatever it is, it is &lt;em&gt;your&lt;/em&gt; number, and it is the most useful single fact in this entire pipeline: nobody who joins on email has any idea what theirs is.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 4 — Now the 360 is boring, which is the goal
&lt;/h2&gt;

&lt;p&gt;With a map that every row goes through, the customer view is a straightforward assembly:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/marts/customer_360.sql&lt;/span&gt;
&lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="n"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;materialized&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s1"&gt;'table'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;

&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;match_tier&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;email_raw&lt;/span&gt;                    &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;billing_email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;                         &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;billing_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;hc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;firstname&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;hc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;lastname&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;                         &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;company_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;domain&lt;/span&gt;                       &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;company_domain&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;

    &lt;span class="c1"&gt;-- billing side&lt;/span&gt;
    &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;sub&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;subscription_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                          &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;subscriptions&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;countif&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sub&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'active'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                               &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;active_subscriptions&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;inv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;amount_paid&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                            &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;lifetime_revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                                           &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;billing_since&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;

    &lt;span class="c1"&gt;-- CRM side&lt;/span&gt;
    &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;deal_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                                    &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;deals&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;closedate&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                                             &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;last_deal_closed_at&lt;/span&gt;

&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'customer_identity_map'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt; &lt;span class="n"&gt;m&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_stripe__customers'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;    &lt;span class="n"&gt;sc&lt;/span&gt; &lt;span class="k"&gt;using&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_hubspot__contacts'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;    &lt;span class="n"&gt;hc&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;hc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;contact_id&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_hubspot__companies'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;   &lt;span class="n"&gt;co&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;hc&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_stripe__subscriptions'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt; &lt;span class="n"&gt;sub&lt;/span&gt; &lt;span class="k"&gt;using&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_stripe__invoices'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;     &lt;span class="n"&gt;inv&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;inv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;
                                                   &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;inv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'paid'&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'stg_hubspot__deals'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt;       &lt;span class="n"&gt;d&lt;/span&gt;  &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;co&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;company_id&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;3&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;4&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;6&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;8&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Every join is a &lt;code&gt;left join&lt;/code&gt; &lt;strong&gt;from the map&lt;/strong&gt;, so an unmatched Stripe customer still produces a row with real billing figures and null CRM fields. That is the honest shape: you know what you know.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 5 — Ship the unmatched list to the humans
&lt;/h2&gt;

&lt;p&gt;The unmatched bucket is not an error state. It is a work queue, and it is the highest-value output of the whole build — every row is a paying customer your CRM cannot see.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- models/marts/unmatched_paying_customers.sql&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt;
    &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;billing_email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;billing_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;lifetime_revenue&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;{{&lt;/span&gt; &lt;span class="k"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'customer_360'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;}}&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;match_tier&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'UNMATCHED'&lt;/span&gt;
  &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;lifetime_revenue&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;lifetime_revenue&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Sorted by revenue, because the ten unmatched customers who pay you the most are worth an afternoon of manual reconciliation and the long tail is worth an automation. Every row someone fixes moves permanently into Tier A if they write the metadata back.&lt;/p&gt;

&lt;h2&gt;
  
  
  What this does not do
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;It is not identity resolution as a product.&lt;/strong&gt; No fuzzy name matching, no phonetic keys, no probabilistic scoring. Deterministic tiers with an explicit unmatched bucket beat a similarity threshold you cannot explain to finance.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It does not deduplicate within a system.&lt;/strong&gt; Two HubSpot contacts for the same person will both match the same Stripe customer and trip the &lt;code&gt;unique&lt;/code&gt; test on &lt;code&gt;customer_id&lt;/code&gt; — deliberately. Fix the CRM; don't paper over it in SQL.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It has no opinion on which side is right&lt;/strong&gt; when HubSpot and Stripe disagree about a name or a company. The model keeps both columns and lets you choose.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Tier C is a heuristic.&lt;/strong&gt; The &lt;code&gt;count(distinct customer_id) = 1&lt;/code&gt; guard and the free-domain list make it defensible, not correct. If a number would embarrass you at 5% error, compute it on Tier A and B only — &lt;code&gt;match_tier&lt;/code&gt; is right there.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Wire it up
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Two connections — &lt;a href="https://datanika.io/docs/connectors/hubspot/" rel="noopener noreferrer"&gt;HubSpot&lt;/a&gt; (private app token, &lt;code&gt;crm.objects.{contacts,companies,deals}.read&lt;/code&gt;) and &lt;a href="https://datanika.io/docs/connectors/stripe/" rel="noopener noreferrer"&gt;Stripe&lt;/a&gt; (a &lt;strong&gt;restricted&lt;/strong&gt; read key, never a secret key).&lt;/li&gt;
&lt;li&gt;Two uploads. Name them so the schemas come out right, and confirm in &lt;strong&gt;Models&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;The models above under &lt;strong&gt;Transformations&lt;/strong&gt;, then schedule the transform to depend on &lt;strong&gt;both&lt;/strong&gt; uploads — a 360 built from a fresh Stripe and yesterday's HubSpot is a 360 that disagrees with itself. See the &lt;a href="https://datanika.io/docs/scheduling/" rel="noopener noreferrer"&gt;scheduling guide&lt;/a&gt;.&lt;/li&gt;
&lt;li&gt;Point a dashboard at &lt;code&gt;customer_360&lt;/code&gt; and &lt;code&gt;customer_identity_coverage&lt;/code&gt;. Put the coverage number on the dashboard, not in a runbook. A match rate nobody looks at drifts.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Datanika meters &lt;strong&gt;bytes processed&lt;/strong&gt;; both of these are narrow JSON sources and the &lt;a href="https://datanika.io/pricing/" rel="noopener noreferrer"&gt;Free plan&lt;/a&gt; includes 10 GB/month. Since 2026-09-24 the dashboard's &lt;strong&gt;Plan Usage&lt;/strong&gt; panel carries a &lt;strong&gt;bytes processed&lt;/strong&gt; dimension against your plan's included volume (&lt;a href="https://github.com/datanika-io/datanika-core/issues/1513" rel="noopener noreferrer"&gt;datanika-core#1513&lt;/a&gt;); it appears once a pipeline has written volume data, so there is nothing to read there before your first run. Size the first estimate from your own data — the meter counts what an upload writes after normalization.&lt;/p&gt;

&lt;h2&gt;
  
  
  Next steps
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://datanika.io/blog/stripe-revenue-dashboard-dbt/" rel="noopener noreferrer"&gt;Stripe revenue dashboard with dbt&lt;/a&gt;&lt;/strong&gt; — MRR, churn and LTV on the Stripe half.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://datanika.io/docs/connectors/hubspot/" rel="noopener noreferrer"&gt;HubSpot setup guide&lt;/a&gt;&lt;/strong&gt; · &lt;strong&gt;&lt;a href="https://datanika.io/docs/connectors/stripe/" rel="noopener noreferrer"&gt;Stripe setup guide&lt;/a&gt;&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://datanika.io/docs/transformations/" rel="noopener noreferrer"&gt;Transformations&lt;/a&gt;&lt;/strong&gt; — how models, tests and schedules fit together.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;a href="https://datanika.io/connectors/" rel="noopener noreferrer"&gt;All connectors&lt;/a&gt;&lt;/strong&gt; — the same identity map takes a third source the day you add one.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The join is the work. Measure it, publish the number, and hand the leftovers to a human.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Correction, 2026-09-23.&lt;/em&gt; The cost line told you to check &lt;strong&gt;Usage&lt;/strong&gt; for your own byte figures. That card counted model runs, not bytes, and at the time no screen in the app showed a byte count, so the line was changed to size it from your data instead. The same sentence was corrected in &lt;a href="https://datanika.io/blog/stripe-revenue-dashboard-dbt/" rel="noopener noreferrer"&gt;the Stripe revenue post&lt;/a&gt; a day earlier; tracked in &lt;a href="https://github.com/datanika-io/datanika-core/issues/1513" rel="noopener noreferrer"&gt;datanika-core#1513&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Update, 2026-09-24.&lt;/em&gt; &lt;a href="https://github.com/datanika-io/datanika-core/issues/1513" rel="noopener noreferrer"&gt;datanika-core#1513&lt;/a&gt; shipped, and the dashboard's usage card now carries a bytes-processed dimension — verified on the serving container across that day's deploy. The cost line names the screen again, together with the condition that makes the figure appear.&lt;/p&gt;

</description>
      <category>tutorial</category>
      <category>dbt</category>
      <category>hubspot</category>
      <category>stripe</category>
    </item>
  </channel>
</rss>
