<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Alexey ban on Medium]]></title>
        <description><![CDATA[Stories by Alexey ban on Medium]]></description>
        <link>https://medium.com/@alexeyban?source=rss-caa831a99b27------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*k-CzDCnVT5ho3Hqa.jpg</url>
            <title>Stories by Alexey ban on Medium</title>
            <link>https://medium.com/@alexeyban?source=rss-caa831a99b27------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 16:48:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@alexeyban/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[From Legacy ERP Source to a Reconciled Lakehouse]]></title>
            <link>https://medium.com/@alexeyban/from-legacy-erp-source-to-a-reconciled-lakehouse-3de949c8e926?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/3de949c8e926</guid>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[eko]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[delta-lake]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Mon, 26 Oct 2026 12:22:06 GMT</pubDate>
            <atom:updated>2026-10-26T12:22:06.093Z</atom:updated>
            <content:encoded><![CDATA[<p><em>How I pointed EKOS at an open-source Java ERP, taught it Java and Oracle, let it document the system, and had it generate a bronze and silver lakehouse that reconciles against the source on every run.</em></p><p>TL;DR. I took iDempiere (4,542 Java files, ~900 Oracle tables), added Java and Oracle analyzers to EKOS, and compiled the code, migrations, git history, docs and the live database into one evidence-backed ledger. From that ledger EKOS generated 1,060 wiki pages of documentation, and then the ekos-lakehouse extension generated the pipeline: Trino SQL into Iceberg bronze, dbt-trino silver, and a Dagster job. On a 165-table subset every bronze table reconciled with Oracle and 3,664 of 3,667 silver tests passed. No LLM was used for any of it: every run log ends with llm count 0.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iwIW1FUwS5xggN4AmgOrag.png" /></figure><p>The interesting part for a data architect is not that the pipeline was generated. It is <em>where each design decision came from</em>. Whether a table loads incrementally, how deletes are detected, which columns become booleans and which values get tested: each of those is read from the application’s own code and catalog, cited, and signed off by a person before anything is built.</p><p><strong>In this article:</strong></p><ol><li>The source and why it is a hard testbed</li><li>Teaching EKOS Java and Oracle</li><li>Documentation and human sign-off</li><li>The lakehouse stack</li><li>Ingestion: the extraction plan</li><li>Bronze: generated SQL and reconciliation</li><li>Silver: built from the source model</li><li>Orchestration, results, and what broke</li><li>Decisions for data architects, and what is next</li></ol><h3>1. The source: iDempiere on Oracle</h3><p><a href="https://github.com/idempiere/idempiere">iDempiere</a> is an open-source ERP (the ADempiere / Compiere family): orders, invoices, inventory, accounting, CRM. It is exactly the kind of system that ends up in front of a data team with the words “we need this in the lakehouse” and nobody left who remembers how it works.</p><p>Why it is a fair test:</p><ul><li><strong>The schema lives in code.</strong> 763 generated X_* model classes and 764 I_* interfaces restate the Application Dictionary: every column, its business name, its list values, which columns are mandatory and which reference which table.</li><li><strong>The database does not say what you need to know.</strong> Nothing in the catalog tells you whether a raw UPDATE somewhere bypasses the Updated column, or whether users can delete rows from the UI. 705 of 828 active tables are deletable by users, a fact stored only in AD_Table.IsDeleteable.</li><li><strong>It ships twice.</strong> Every migration exists once for Oracle and once for PostgreSQL, so a naive analyzer sees thousands of “conflicts” that are really two databases.</li><li><strong>Oracle quirks.</strong> CHAR(n) padding, NUMBER without scale, Y/N flags in CHAR(1), table names with $ in them, and a 4,000-byte limit on row strings.</li></ul><p>The testbed: iDempiere release-13 source (commit 3355eb5fc9), the iDempiere 12 seed imported into Oracle XE 21c in Docker, and a read-only user ekos_ro with CREATE SESSION, SELECT on the schema and SELECT_CATALOG_ROLE. Every number below comes from that setup.</p><h3>2. Teaching EKOS Java and Oracle</h3><p>EKOS had no Java analyzer and no Oracle dialect, so the first job was to add them. Four RFCs, all deterministic, none calling a model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*qOBlyoxxIRdUyZVzXBKqTQ.png" /></figure><p>RFC 0212 is the one that matters most later. An incremental copy needs two facts the schema never states: whether every change moves a timestamp you can use as a watermark, and whether rows ever vanish. iDempiere’s PO layer sets Updated on every save, but raw UPDATEs in Java and PL/SQL may not. Rows leave through PO.delete, raw DELETEs, ON DELETE CASCADE, age-based purges and the UI. EKOS classifies each table and keeps the evidence:</p><pre>&quot;evidence&quot;: [<br>  &quot;update class po_only: the application sets UPDATED on every write it makes (EKOS write behaviour, RFC 0212)&quot;,<br>  &quot;an update made outside the application does not set UPDATED: the load misses it and the next reconciliation shows it&quot;,<br>  &quot;deletes: every run (deleted_by_users) by bucketed key diff, recorded as delete&quot;<br>]</pre><p>That snippet is copied from the generated lakehouse.lock.json for AD_ALERTPROCESSOR. The pipeline carries its own justification.</p><p>Everything is configured in one ekos.toml. The database password is never in it, only the name of the variable that holds it:</p><pre>[[databases]]<br>name = &quot;idempiere-db&quot;<br>kind = &quot;oracle&quot;<br>connect = &quot;localhost:51521/XEPDB1&quot;<br>user = &quot;ekos_ro&quot;<br>password-env = &quot;EKOS_ORACLE_PASSWORD&quot;   # the variable&#39;s NAME; never the password<br>owners = [&quot;ADEMPIERE&quot;]<br>profile = &quot;p2&quot;                          # one aggregate scan per table<br>dictionary = &quot;idempiere&quot;                # also read AD_Table&#39;s flags</pre><pre>ekos database test idempiere-db      # read-only connection, writes refused, census vs ledger<br>ekos build &amp;&amp; ekos recover &amp;&amp; ekos resolve &amp;&amp; ekos compile &amp;&amp; ekos commit</pre><p><strong>What the ledger held after one compile:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*6zgF3sBMdbVxZ1lOJib2Pg.png" /></figure><p>A 25-question evaluation over the ledger answered 14 to 15 questions correctly with no hallucinated answer. That is the honest baseline, not a marketing number.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*higVcWPMvgdFcSpcHpIVvg.png" /></figure><h3>3. Documentation, then a human sign-off</h3><p>Before building anything, the ledger was turned into documentation for the source, and a person agreed to the model the pipeline would be built from.</p><p><strong>Documentation.</strong> ekos docs generate --layout estate --output wiki/ wrote 1,060 wiki pages for iDempiere in 26 seconds: a page per table with columns, types, keys, business names from the Java model, references, and value-free profiles; plus logic, history, glossary and findings sections. Two gates are built into the renderer: no data value ever reaches a page (a profile gives counts only), and text is used only from sources with a known licence. Here is the start of the C_ORDER page:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*AICWnk8YUgPIldAq70HYwg.png" /></figure><p>The same pages are written in the format the lakehouse-docs publishers read, so they can go to a GitHub wiki, Confluence or Azure DevOps. A re-run keeps manual sections and deletes only the pages it generated.</p><p><strong>Review and sign-off.</strong> The documentation tells people what the system is. The sign-off records what they agree the pipeline should do with it. There are six subjects:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*uGHBGZQ9-i-y0TxlwMyLvw.png" /></figure><pre>ekos review scope AD_ERROR AD_PINSTANCE_LOG --out --reason &quot;runtime logs, not business data&quot;<br>ekos review pii AD_USER.EMAIL --confirm --mask hash --reason &quot;personal email&quot;<br>ekos signoff approve scope --digest &lt;digest&gt; --note &quot;reviewed with the app team&quot;</pre><p>A sign-off is bound to the <strong>digest of what was shown</strong>, and validity is recomputed on every read. Add a column in Oracle and the PII sign-off goes stale and names the column, while the other subjects stay valid. With require-signoff = true, every lakehouse step refuses to write until the subjects it depends on are signed. No MCP tool or API token can decide or sign off: those are human-only commands, and a test enforces it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N78RVAf9GnDF_ufughZXEw.png" /></figure><h3>4. The lakehouse stack</h3><p>The lakehouse is open-source end to end and runs on a laptop. ekos-lakehouse lakehouse install brings up the sandbox; the versions are the ones the live suites ran against.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c0YNtpGSnZsMXHDJcKPGpQ.png" /></figure><p>The extension is a separate binary built on EKOS core. Steps 1 to 3 (analysis, docs, sign-off) belong to EKOS; steps 4 to 7 (requirements, extraction, bronze/silver, marts) belong to the extension. It adds one section to ekos.toml:</p><pre>[extensions.lakehouse]<br>source-engine = &quot;oracle&quot;<br>source-database = &quot;idempiere-db&quot;       # reuses the [[databases]] entry<br>source-catalog = &quot;idempiere&quot;           # the Trino catalog over Oracle<br>namespace-prefix = &quot;bronze_idem_&quot;<br>application-watermark = &quot;UPDATED&quot;      # incremental where the code keeps it<br>trino-url = &quot;http://127.0.0.1:58080&quot;<br>dagster-url = &quot;http://127.0.0.1:53100&quot;<br>require-signoff = true<br>require-plan-approval = true</pre><p>Oracle got in behind a Source trait. PostgreSQL&#39;s catalog code moved behind it unchanged, and the Pagila reference pipeline regenerated byte-identical (84 files). That was the regression test for the refactor.</p><h3>5. Ingestion: an extraction plan with evidence</h3><p>Ingestion starts with a <strong>plan</strong>, not with code. ekos-lakehouse lakehouse plan reads the Oracle catalog, the ledger&#39;s facts and the step-3 decisions, and proposes, per table, how it lands in bronze. A person reviews it in the console&#39;s Extraction page or on the CLI, changes what they disagree with (each change needs a reason), and approves it. With require-plan-approval = true, generate builds only the approved plan.</p><pre>ekos-lakehouse lakehouse plan                     # reads the source, saves the proposal<br>ekos-lakehouse lakehouse extraction show --table ADEMPIERE.C_BPARTNER<br>ekos-lakehouse lakehouse extraction change --table C_BPARTNER --full --reason &quot;order import is being reworked&quot;<br>ekos-lakehouse lakehouse extraction approve --note &quot;first load&quot;</pre><p>The plan answers four questions per table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z2TM9-3X47WSUy0qdUTiCw.png" /></figure><h3>5.1 Full or incremental?</h3><p>A watermark is only safe if every write moves it. Usually that is an assumption; here it is a fact from the code.</p><ul><li>Update class po_only (all writes go through the PO layer) or raw_sql_sets_updated (raw SQL that also sets Updated): <strong>incremental</strong> on UPDATED.</li><li>Update class raw_sql_skips_updated (a raw UPDATE that leaves Updated alone): <strong>full</strong> load, because a watermark would silently miss changes.</li><li>Tables below incremental-min-rows: full, because it is cheaper than being clever.</li></ul><p>The plan also states what it cannot see: an update made outside the application does not set Updated, so the load misses it, and the next reconciliation shows it.</p><h3>5.2 How are deletes detected without a marker?</h3><p>iDempiere has no soft-delete column and no CDC by default. Each incremental table gets a <strong>delete plan</strong> from its delete classes: checked every run where users or code delete, or audited every delete-audit-days (7) where the code never hard-deletes.</p><p>The check is a <strong>bucketed key diff</strong>. Primary keys are hashed into 256 buckets on both sides; only the buckets whose counts or digests differ are read key by key. Missing keys become tombstone rows (_ekos_op = &#39;delete&#39;, or purge for an age-based purge you chose to keep).</p><p>A <strong>mass-delete guard</strong> stops anything over 5 % or 10,000 keys at once. Nothing is applied until a person runs lakehouse deletes --accept. Until then reconciliation stays red, so a pending table never reads as green.</p><h3>5.3 Which types?</h3><p>Every column gets an Oracle-to-Iceberg mapping with its <strong>lossiness</strong> recorded. A column that cannot land exactly is refused, and its table with it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/946/1*APUxqVfXi6nxhAE4TAvQOg.png" /></figure><p>One trap worth knowing: CAST(number AS decimal(38,2)) rounds 1.005 to 1.01 without an error. That is why scale is measured, and why reconciliation compares decimals without rounding.</p><h3>5.4 What stays out, what gets masked?</h3><p>Scope and PII come straight from the step-3 sign-offs. Out-of-scope tables are never planned; confirmed PII columns are masked in silver.</p><p><strong>The approved plan on iDempiere:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/389/1*F6Evklh6eSpww7gQ_EIwbw.png" /></figure><p>One column was masked. The approval is a ledger fact bound to the plan’s digest. The digest leaves evidence text out, so moving row estimates do not invalidate it, but a column added in Oracle does, and generate then names the table that moved.</p><p>The live suite drove four delete scenarios through Oracle and bronze: a single delete, a delete and re-insert, a 148-key mass delete (stopped by the guard, then applied once accepted), and a retained purge. Each reconciled green afterwards.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zhLrIcl348Y7P4vMQoyEdw.png" /></figure><h3>6. Bronze: generated SQL, append-only, reconciled</h3><p>ekos-lakehouse lakehouse generate turns the approved plan into a <strong>pipeline repository</strong>: plain SQL files, a dbt project and a Dagster project, plus lakehouse.lock.json, the plan as it was built. Nothing in it is hand-written, and every file says so in its first line.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4nDaN9tPhjuGzo8B4wpjUg.png" /></figure><pre>pipeline/<br>  lakehouse.lock.json            # the plan, with evidence per table and per column<br>  bronze/ADEMPIERE/&lt;TABLE&gt;/<br>    create.sql  load.sql  watermark.sql  current.sql  schema.sql  maintenance.sql<br>  dbt/models/silver/ADEMPIERE/   # one model per table + _ADEMPIERE__models.yml<br>  dagster/definitions.py<br>  marts/                         # approved mart descriptions (step 4)</pre><p><strong>An incremental load</strong> reads Oracle through Trino&#39;s idempiere catalog and appends to Iceberg. Every row carries four lineage columns:</p><pre>-- Generated by ekos-lakehouse (RFC 0192). Do not edit: regenerate.<br>INSERT INTO &quot;iceberg&quot;.&quot;bronze_idem_ADEMPIERE&quot;.&quot;AD_ALERTPROCESSOR&quot; (..., &quot;_ekos_loaded_at&quot;, &quot;_ekos_run_id&quot;, &quot;_ekos_source_lsn&quot;, &quot;_ekos_op&quot;)<br>SELECT<br>  CAST(&quot;AD_ALERTPROCESSOR_ID&quot; AS bigint) AS &quot;AD_ALERTPROCESSOR_ID&quot;,<br>  CAST(&quot;ISACTIVE&quot; AS varchar)            AS &quot;ISACTIVE&quot;,<br>  CAST(&quot;UPDATED&quot; AS timestamp(6))        AS &quot;UPDATED&quot;,<br>  -- ... every column through its type rule ...<br>  CAST(current_timestamp AS timestamp(6) with time zone) AS &quot;_ekos_loaded_at&quot;,<br>  CAST(&#39;{{run_id}}&#39; AS varchar) AS &quot;_ekos_run_id&quot;,<br>  CAST(&#39;{{lsn}}&#39; AS varchar)    AS &quot;_ekos_source_lsn&quot;,<br>  CAST(&#39;upsert&#39; AS varchar)     AS &quot;_ekos_op&quot;<br>FROM &quot;idempiere&quot;.&quot;ADEMPIERE&quot;.&quot;AD_ALERTPROCESSOR&quot;<br>WHERE {{watermark}} IS NULL OR &quot;UPDATED&quot; &gt;= {{watermark}}</pre><p>The watermark is read from bronze itself, never from a side table:</p><pre>SELECT CAST(max(&quot;UPDATED&quot;) AS varchar)<br>FROM &quot;iceberg&quot;.&quot;bronze_idem_ADEMPIERE&quot;.&quot;AD_ALERTPROCESSOR&quot;<br>WHERE &quot;_ekos_op&quot; &lt;&gt; &#39;delete&#39;</pre><p>Bronze is <strong>append-only</strong>: an update is a new version, a delete is a tombstone. The current state is a generated view of the latest version per key:</p><pre>SELECT &lt;columns&gt; FROM (<br>  SELECT *, row_number() OVER (PARTITION BY &quot;AD_ALERTPROCESSOR_ID&quot;<br>                               ORDER BY &quot;_ekos_loaded_at&quot; DESC) AS &quot;_ekos_rn&quot;<br>  FROM &quot;iceberg&quot;.&quot;bronze_idem_ADEMPIERE&quot;.&quot;AD_ALERTPROCESSOR&quot;)<br>WHERE &quot;_ekos_rn&quot; = 1 AND &quot;_ekos_op&quot; &lt;&gt; &#39;delete&#39;</pre><p><strong>A full load</strong> is a CREATE OR REPLACE TABLE ... AS SELECT with the same type rules, for example rpad(CAST(&quot;DOCSTATUS&quot; AS varchar), 2, &#39; &#39;) on C_ORDER&#39;s CHAR(2) status. Each table also gets maintenance.sql: Iceberg optimize, expire_snapshots and remove_orphan_files with a 7-day retention.</p><h3>Reconciliation: proving bronze equals the source</h3><p>A load that finished is not a load that is correct. After every bronze load the extension compares the current state with Oracle at three levels:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/556/1*89Osjem8LX-Gugxp9Ug1RQ.png" /></figure><p>wo details make this trustworthy. First, a <strong>planted control</strong>: a known defect is planted and must be detected, so a reconciler that always says “green” is caught. Second, Oracle cannot build a row string past 4,000 bytes, so rows are hashed over <strong>column digests</strong> instead of one concatenated string. The extension checks that Oracle’s canonical forms, hashes and bucket numbers equal its own Rust implementation before trusting any result.</p><p>A red reconciliation is a Dagster failure, so downstream silver models do not run on bad bronze. You can see that in an early run log: ADEMPIERE.AD_ROLE is not reconciled with its source, and the silver step was skipped.</p><pre>ekos-lakehouse lakehouse reconcile --table ADEMPIERE.C_ORDER</pre><h3>7. Silver: built from what the source model knows</h3><p>Most silver layers are written by hand: someone decides that ISACTIVE is a boolean, adds an accepted_values test for DOCSTATUS, and guesses which _ID columns are foreign keys. Here those decisions come from the ledger, and each one cites its evidence in the dbt YAML.</p><p>The plan carries, per column, the Oracle check constraints and the application model’s code facts:</p><pre>{ &quot;name&quot;: &quot;ISACTIVE&quot;, &quot;not_null&quot;: true,<br>  &quot;check&quot;: { &quot;constraint&quot;: &quot;SYS_C0022242&quot;, &quot;values&quot;: [&quot;Y&quot;, &quot;N&quot;] },<br>  &quot;code&quot;:  { &quot;boolean&quot;: true } },<br>{ &quot;name&quot;: &quot;SUPERVISOR_ID&quot;, &quot;not_null&quot;: true,<br>  &quot;code&quot;:  { &quot;references&quot;: &quot;AD_User&quot; } }</pre><p>The silver generator applies these rules:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*EDK_URr_g8w5RIEJzKEeNA.png" /></figure><p>Declared facts win. A column with a check constraint is not also tested on the code’s list, and a declared foreign key suppresses the code-reference test.</p><p>The generated model for C_ORDER, trimmed:</p><pre>-- Generated by ekos-lakehouse (RFC 0193). Do not edit: regenerate.<br>{{ config(materialized=&#39;table&#39;, schema=&#39;silver_ADEMPIERE&#39;, alias=&#39;C_ORDER&#39;) }}</pre><pre>with bronze as (<br>    select * from {{ source(&#39;bronze_idem_ADEMPIERE&#39;, &#39;C_ORDER&#39;) }}<br>)<br>select<br>    &quot;C_ORDER_ID&quot;,<br>    case &quot;ISACTIVE&quot; when &#39;Y&#39; then true when &#39;N&#39; then false end as &quot;ISACTIVE&quot;,<br>    case &quot;ISSOTRX&quot;  when &#39;Y&#39; then true when &#39;N&#39; then false end as &quot;ISSOTRX&quot;,<br>    &quot;DOCUMENTNO&quot;,<br>    &quot;DOCSTATUS&quot;,<br>    &quot;GRANDTOTAL&quot;,<br>    -- ...<br>    &quot;_ekos_loaded_at&quot; as &quot;_ekos_bronze_loaded_at&quot;<br>from bronze</pre><p>And its tests, each with the rule and the evidence that produced it:</p><pre>- name: ADEMPIERE__C_ORDER<br>  description: Sales &amp; Purchase Order<br>  columns:<br>  - name: C_ORDER_ID<br>    data_tests:<br>    - unique:   { config: { meta: { ekos_rule: primary-key, evidence: the source&#39;s primary key } } }<br>    - not_null: { config: { meta: { ekos_rule: not-null,    evidence: the source column is NOT NULL } } }<br>  - name: AD_ORG_ID<br>    data_tests:<br>    - relationships:<br>        arguments: { to: ref(&#39;ADEMPIERE__AD_ORG&#39;), field: &#39;&quot;AD_ORG_ID&quot;&#39; }<br>        config: { meta: { ekos_rule: foreign-key, evidence: foreign key ADORG_CORDER } }<br>  - name: DOCSTATUS<br>    description: AD_Reference_ID=131<br>    data_tests:<br>    - accepted_values:<br>        arguments: { values: [&#39;??&#39;, AP, CL, CO, DR, IN, IP, NA, RE, VO] }</pre><p>The description “Sales &amp; Purchase Order” and AD_Reference_ID=131 come from the Java model, not from a person typing them.</p><p><strong>Deletes and history in silver.</strong> Rows the source purged but the plan chose to keep carry _ekos_purged_at_source, and keep their last real version. A history snapshot closes a row deleted at the source at the time the delete was seen. flags-as-text = true keeps flags as text for consumers that need the original codes.</p><p><strong>What the facts amounted to on iDempiere:</strong> of 2,376 code-declared references, 2,367 already have a declared key; all 1,776 check lists are Y/N flags. So the Java model mostly confirmed the database, and the warnings it adds are aimed at the few places it does not.</p><h3>8. Orchestration, results, and what broke</h3><p>The generated Dagster project has one asset per bronze table and one per silver model. A bronze asset runs load.sql, then the delete check if the table has a delete plan, then reconciliation; a failure raises dg.Failure and blocks the silver assets that depend on it. dbt models are attached through dagster-dbt with their asset keys set in the model&#39;s meta, so the graph is the table graph.</p><pre>ekos-lakehouse lakehouse generate                 # bronze SQL, dbt silver, Dagster<br>ekos-lakehouse lakehouse run --in-process --wait  # launch the job over Dagster GraphQL and follow it<br>ekos-lakehouse lakehouse status</pre><p>lakehouse.lock.json is rewritten on every generate, with the SQL beside it. If a column&#39;s load expression changes (a new type rule, say), generate requests a <strong>reload</strong> for that incremental table, because an incremental load never rewrites an unchanged row. The reload path already exists and is reconciled, so nothing is patched in place.</p><h3>Results on iDempiere</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/774/1*ivkjev97qiU82BIY02Ip-A.png" /></figure><p>The full 849-table run was dropped: it needs more memory than the testbed machine has. The subset was chosen by closing over references, so every relationships test had its parent table present.</p><h3>What the run found</h3><p>The first iDempiere run through Dagster surfaced six bugs, all fixed before the green run. The lessons transfer to any Oracle-to-Iceberg project:</p><ul><li><strong>CHAR(n) padding.</strong> Trino&#39;s char(n) to varchar cast drops trailing spaces; Oracle keeps them. Fix: rpad(CAST(c AS varchar), n, &#39; &#39;) for n &gt; 1.</li><li><strong>$ in table names.</strong> Trino reads t$x as an Iceberg metadata table, so CREATE$JAVA$LOB$TABLE broke. Fix: a safe Iceberg name for $ names only, and a stricter safe name for Dagster and dbt.</li><li><strong>Polaris refuses a replace</strong> whose new location is not the table’s own.</li><li><strong>YAML 1.1.</strong> PyYAML reads bare ON, off and yes as booleans, while serde_yaml writes them bare, so a generated code list could change meaning. Fix: quote values in generated YAML.</li><li><strong>Eager dbt tests.</strong> dagster-dbt runs tests eagerly for a model subset; --indirect-selection cautious keeps a subset honest.</li><li><strong>Memory.</strong> The 2 GiB sandbox Trino was OOM-killed by four dbt threads over thousands of tests; two threads are fine. Running Dagster in one process (execution: in_process) used about 1 GB against 3.6 GB for a process per step.</li></ul><p>None of these is exotic. They are the kind of thing a hand-written pipeline discovers in production. Reconciliation on every load is what made them visible on day one.</p><h3>9. Decisions for data architects, and what is next</h3><p>If you take one thing from this project, take the pattern: <strong>every pipeline decision is a fact with evidence, and a person approves the facts, not the code.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wl6kBWGYZZxYQb3oZtI_Fw.png" /></figure><h3>What is next: gold marts</h3><p>Gold is built from <strong>approved requirements</strong>, not from prompts. An operator describes a mart in plain words; a local model (llama3 via Ollama) drafts its metadata; EKOS resolves every term against silver, and anything it cannot resolve becomes a question for the operator, never for the model:</p><pre>---<br>mart: sales_by_month<br>grain: [month, customer]<br>dimensions:<br>  month: month of c_order.dateordered<br>  customer: c_bpartner.name via c_order.c_bpartner_id<br>measures:<br>  total: sum of c_order.grandtotal<br>  orders: count of c_order<br>filters:<br>  - c_order.issotrx is true<br>  - c_order.docstatus = &#39;CO&#39;<br>---<br>Completed sales orders per month and customer.</pre><p>Joins follow declared foreign keys from child to parent only, at most four keys long, so a join never repeats rows. From C_ORDERLINE there are 36 key paths to C_REGION within four hops, so the operator picks one with via. The gold drafter then compiles the approved requirement into a dbt model, checks every atom against the description (nothing invented), plans it on Trino with EXPLAIN, adds grain-uniqueness and per-measure reconciliation tests, and proposes it on a git branch. Merging is the approval.</p><p>On iDempiere a request drafted by llama3 came back with three questions and two mistakes no check could catch: the region dimension named store, and a filter that selected purchase orders instead of sales. Nine edits later it was approved, and its gold mart proposed with 30 atoms checked and nothing invented. Step 4 works; the Marts page and per-mart measure reconciliation are the next article.</p><h3>Links</h3><ul><li>EKOS: <a href="https://github.com/alexeyban/EKOS">github.com/alexeyban/EKOS</a>, RFCs in <a href="https://github.com/alexeyban/EKOS/tree/main/ekos/docs/rfcs">ekos/docs/rfcs</a></li><li>Operator workflow guide: <a href="https://github.com/alexeyban/EKOS/blob/main/docs/operator-workflow.md">docs/operator-workflow.md</a></li><li>iDempiere: <a href="https://github.com/idempiere/idempiere">github.com/idempiere/idempiere</a></li></ul><p><em>If you are sitting on a legacy Oracle or Java estate that has to reach a lakehouse, I would like to hear how you handle watermarks and deletes today.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3de949c8e926" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Three Questions Every Enterprise AI Team Should Ask — Answered from the EKOS Codebase]]></title>
            <link>https://medium.com/@alexeyban/three-questions-every-enterprise-ai-team-should-ask-answered-from-the-ekos-codebase-5424f4d4b590?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/5424f4d4b590</guid>
            <category><![CDATA[eko]]></category>
            <category><![CDATA[rags]]></category>
            <category><![CDATA[knowledge-management]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Mon, 12 Oct 2026 07:27:38 GMT</pubDate>
            <atom:updated>2026-10-12T08:41:33.812Z</atom:updated>
            <content:encoded><![CDATA[<h3><em>An LLM in EKOS never decides anything on its own authority. Here is how the code makes that true — with the parts that still fall short.</em></h3><p>A reader asked me three questions about EKOS:</p><ol><li>How do you make AI decisions traceable and verifiable?</li><li>How do you keep knowledge alive across long-running AI workflows?</li><li>How do you let several agents reuse knowledge safely?</li></ol><p>I build EKOS, an open-source Rust knowledge compiler. It reads code, SQL schemas, ETL pipelines, git history and documents, and compiles them into an append-only fact ledger. Agents query that ledger over MCP.</p><p>I could answer these questions with a diagram and a slogan. Instead I went back through the repository and the devlogs and wrote down what the code actually does. Every mechanism below has an RFC number, a module or a test name behind it. Where something is not solved, I say so.</p><p>The short version: <strong>the answer comes from a compiled ledger, the model only explains evidence that was assembled before it ran, and anything an agent writes is quarantined until a person promotes it.</strong></p><h3>1. How does EKOS make AI decisions traceable and verifiable?</h3><p>By never letting the model be the source of a fact. Every answer is built from evidence the compiler recorded, every citation is checked against that evidence, and every ledger write records which run produced it.</p><h3>Evidence first, model second</h3><p>In most RAG systems the model receives chunks of text and decides what is true. In EKOS a question is first compiled into a typed query plan (RFC 0123, runtime/src/reason.rs). The plan runs fully offline against the ledger and produces an EvidenceSet: a flat list of atomic claims.</p><p>Each claim carries what you need to check it:</p><ul><li>the claim itself, such as orders.schema = &quot;public&quot;</li><li>the id of the source evidence record</li><li>the location as path:line</li><li>a confidence score and the analyzer that extracted it</li><li>a weak flag when it came from a loose retrieval match</li><li>a disputed flag when sources disagree about it (more on that below)</li></ul><p>Only then does the LLM enter. Its job, in the words of the code comment, is to <em>explain</em> the assembled evidence. It cannot add a fact that is not in the set.</p><h3>Citations are checked by code, not trusted</h3><p>The model must end its answer with a machine-readable block listing the evidence ids it used. The parser keeps only ids that exist in the evidence set and drops everything else. Then it labels the result:</p><ul><li>AI001 — the answer has no citation block at all</li><li>AI002 — the block parsed, but none of its citations resolved to real evidence</li></ul><p>AI002 exists because of a real failure. Live testing against gpt-4o-mini showed roughly half of simple questions came back with a confident, correct-looking answer and an empty citation list. Before the fix, that looked exactly like a well-grounded answer. Now a caller can tell the difference.</p><p>The eval harness taught the same lesson. An early groundedness metric left uncited answers out of the denominator instead of counting them as failures, and the score looked far better than it was. Fixing the ruler came before fixing the answers.</p><h3>Every write knows where it came from</h3><p>The ledger is append-only. A retraction is itself a new fact, and a monotone transaction id orders every write. RFC 0135 added a write context to each entry: the pipeline run id, the stage (build, commit, commit:lineage…) and the source artifact behind it.</p><p>An agent can ask for the full write history of any object through the read-only MCP tool ekos_audit. It can also rebuild what the ledger knew at any past moment with ekos_state and a timestamp.</p><h3>Disagreement is shown, not hidden</h3><p>Real enterprise sources contradict each other. In LedgerSMB, the base schema and a later migration define the same table differently. Most systems silently keep one side.</p><p>EKOS keeps both as a ConflictingEvidence record (RFC 0172). Since RFC 0173, any claim about a disputed attribute comes back marked disputed: true with the conflict ids, in ekos_query, ekos_retrieve and ekos ask. An agent can see that it is reading one side of an open argument.</p><p>Only a person can settle a conflict. A test scans the MCP code and fails if it can ever reach the resolve function.</p><h3>Same input, same ledger</h3><p>Object and relationship ids are deterministic, derived from their content (RFC 0135). Re-running the pipeline on unchanged sources appends nothing. That is what makes a result reproducible and therefore checkable.</p><p>Source freshness (RFC 0171) closes the last gap: EKOS records what the ledger was compiled from, and ekos freshness reports which files changed since and which objects cite them.</p><p><strong>What is not done yet.</strong> Entries written before RFC 0135 carry no run provenance, and neither do reads on the partitioned backend. Tracing a write back to a single knowledge artifact through the compile step is still a stated follow-up.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MVKqAOIjk2DsUR4WGuAuUg.png" /></figure><h3>2. How does EKOS preserve knowledge across long-running AI workflows?</h3><p>With two layers: a compiled ledger that does not need rebuilding, and a separate memory for what an agent concluded, which knows when it has gone stale.</p><h3>Layer one: compile once, query many times</h3><p>Agent-swarm approaches rebuild their understanding of a codebase on every task. EKOS compiles it once into a versioned ledger. A session that starts tomorrow reads the same compiled facts as the one that ran today. It can also branch the ledger, merge branches back, and look at any past state.</p><p>That covers what the <em>sources</em> say. It does not cover what an <em>agent</em> figured out.</p><h3>Layer two: agent session memory (RFC 0151)</h3><p>An agent learns things during a session: a dead end, a constraint, a decision and the reason for it. At /compact or at the end of a session, that is gone. RFC 0151 gives those notes somewhere to live.</p><ol><li><strong>Capture.</strong> The agent writes typed notes — finding, decision, dead end, constraint, todo — through ekos_session_note. They go to a capped, redacted, append-only inbox file. This tool never opens the ledger.</li><li><strong>Commit.</strong> A person runs ekos session commit. It redacts the batch again, hashes it, and maps each note to a SessionClaim in the ledger. The mapping is deterministic: the same notes always produce byte-identical records.</li><li><strong>Anchor.</strong> A note can point at a real object, such as the orders table. Matching is exact only. An ambiguous name is recorded as ambiguous, never guessed.</li><li><strong>Recall.</strong> ekos_session_recall and ekos_session_brief return notes within a token budget, ranked by trust tier, relevance to the current scope, staleness and recency.</li></ol><h3>Memory that knows it is out of date</h3><p>This is the part I care about most. When a note is anchored, EKOS stores a fingerprint of the object at that moment. On every read, each note gets a verdict: <strong>fresh</strong>, <strong>changed</strong>, <strong>orphaned</strong> or <strong>unanchored</strong>.</p><p>So the note “orders total is stored in cents” is flagged the moment the orders table changes. The agent is told to use it but say it may be outdated. A summary-based memory has no way to do this — it does not know what the summary was about.</p><h3>What the measurements say</h3><p>I ran this against a real model (Claude Haiku, real ledger) and the results are mixed. With 7 notes, EKOS memory and a model-written summary both scored 0.92 correctness. Under the RFC’s own rule, that was a no-go.</p><p>At 121 notes across 40 tables, with every condition held to a 400-token budget:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/845/1*WNa-fdlrtJ1L5UzlrGHkDA.png" /></figure><p>Two honest conclusions. Scoped retrieval works, and the staleness warning now reaches the answer: in 5 of 6 eligible answers the model answered and warned the note might be outdated. But an <em>unscoped</em> session-start brief scored the same as no memory at all. A 400-token brief holds about 12 of 121 notes, and without a scope it cannot know which 12 matter.</p><p>The fixture also favours exact retrieval: its facts are arbitrary 4-digit numbers that a summary is bound to lose. For those reasons the feature ships off by default and I don’t claim more than the table shows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9P55c_3H-KPQZ6q-PcM2Bw.png" /></figure><h3>3. How does EKOS enable safe reuse of knowledge across AI agents?</h3><p>By making the shared knowledge read-only to agents, and by treating anything an agent writes as untrusted data until a person promotes it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*5xv08Al_FAnst7J3WQmJTQ.png" /></figure><p>Agents only read the compiled ledger. What they write goes to an inbox and needs two human steps before anyone can treat it as trusted.</p><h3>One ledger, read-only to every agent</h3><p>All agents read the same compiled ledger through the MCP server. Every MCP tool is read-only, with three narrow exceptions:</p><ul><li>ekos_identity_review confirms or rejects a proposed match between two objects. It never writes raw data.</li><li>ekos_clickhouse_query is off by default and accepts only SELECT statements.</li><li>ekos_session_note writes only to the local inbox file, never to the ledger.</li></ul><p>No agent can change what another agent reads.</p><h3>Trust tiers, and no way for an agent to promote itself</h3><p>Every note an agent leaves enters the ledger as <strong>T0, unconfirmed</strong>. Promotion to T1 happens through ekos session review, a CLI command with no MCP equivalent.</p><p>The code makes this structural rather than a convention. The lifecycle module’s actor type has exactly one value, Human, with a comment saying there is deliberately no Agent variant. A test fails if any MCP code can reach that module.</p><h3>Agent notes stay out of normal answers</h3><p>One agent’s notes must not quietly become another agent’s facts. So session claims are filtered out of every read path an agent normally uses: query, retrieval, graph traversal, impact analysis, state reconstruction and graph export. They appear only through the session tools, clearly labelled.</p><p>This filter came from a real bug. The first version filtered two read methods. A live probe found six other tools returning agent notes as ordinary compiled objects — and ekos_dependents counted a note as a dependent of a table. Now a test parses the Runtime code and fails if any new read method does not explicitly state how it treats session memory.</p><h3>Treat agent text as data, not instructions</h3><p>A note written by one agent could contain a prompt injection aimed at the next one. Recall results are returned wrapped as untrusted: true, and the session brief uses an untrusted-data envelope. The eval tracks how often a deliberately poisoned note leaks into an answer. In the live runs, none did.</p><h3>No automatic merging</h3><p>When EKOS thinks a table in one system is the same as one in another, it writes a SameAs link marked <strong>unconfirmed</strong>. It never merges them. A review of a conflict carries forward only while the underlying claims stay the same; when the evidence changes, the decision has to be made again.</p><h3>Secrets stay out</h3><p>Redaction runs at every point where raw content enters, including when an agent asks to read source text at query time. Session notes are redacted when written and again when committed. A note whose redaction fails is dropped, not stored.</p><p><strong>The remaining risk.</strong> Redaction is pattern-based. A secret in a shape it does not recognise can still be committed, and an append-only ledger cannot un-commit it.</p><h3>The pattern behind all three answers</h3><p>The three answers share one design rule: <strong>separate where facts come from from where language comes from.</strong> Facts come from a deterministic compiler and an append-only ledger. Language comes from the model. Anything the model or an agent produces is a proposal until code checks it or a person confirms it.</p><p>The second rule is to enforce boundaries with tests, not with documentation. The most useful tests in the repository are the ones that scan the code itself: no MCP path to conflict resolution, no MCP path to claim promotion, no read method without a stated policy on agent notes. Each of them exists because a boundary that was only written down got crossed.</p><p>The third is to publish the measurements that disagree with you. The groundedness metric was wrong before it was right. Session memory lost to a model summary at small scale. Those results are in the RFCs next to the ones that went well.</p><p>The open work is listed above: full provenance through the compile step, pattern-based redaction, and making session memory useful without a scope. If you are solving the same problems, I would like to compare notes.</p><p>EKOS is open source: <a href="https://github.com/alexeyban/EKOS">github.com/alexeyban/EKOS</a>. The RFCs referenced here are in ekos/docs/rfcs/, and the devlogs record how each piece was built and measured.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5424f4d4b590" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Facts, Not Documents: Why I Built a Knowledge Compiler That Refuses to Guess]]></title>
            <link>https://medium.com/@alexeyban/i-stopped-treating-documentation-as-writing-and-started-treating-it-as-compilation-952af45fd932?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/952af45fd932</guid>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Thu, 27 Aug 2026 09:53:55 GMT</pubDate>
            <atom:updated>2026-08-27T13:36:06.027Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*YhlM1dKA1A_LvKrUrLPjyA.jpeg" /></figure><h3>What I learned building a knowledge compiler in Rust, and why “I don’t know” is a feature</h3><p>Every tool that promises to understand your codebase has the same failure mode: it answers. It always answers. Point it at an ETL job it can’t parse, a table it never found, a dependency it only half-resolved, and it will still produce fluent, structured, confident prose. You find out it was wrong when the deploy fails.</p><p>I’ve spent the last months building EKOS, an open-source system in Rust that takes the opposite position. Its most important behavior is refusing to say things it can’t support. This article is about the design decisions that follow from that constraint, the two questions I get asked most often about it, and why the answer to both turned out to be “the ledger already knows.”</p><p><em>(Every diagram and output block below is real, unedited tool output — most of it also appears, with more context, in the </em><a href="https://alexeyban.github.io/EKOS/"><em>presentation deck</em></a><em> in the repo.)</em></p><h3>The storage model: facts, not documents, and why that matters for agents</h3><p>Before the compiler pipeline, one lower-level decision shapes everything downstream: what a single unit of stored knowledge actually <em>is</em>. I didn’t pick a document store, and I didn’t pick a vector index as the source of truth. I picked an EAV fact model — the same family of idea Datomic and XTDB are built on, reimplemented locally in Rust rather than borrowed as a JVM dependency. This section is the “why,” because it’s the part that determines how well an LLM agent can actually trust what it’s querying.</p><h3>What the unit of storage actually is</h3><p>Every one of the four KIR primitives — object, relationship, event, evidence — decomposes into <strong>facts</strong>:</p><pre>Fact {<br>    e:  EntityId      // which thing<br>    a:  AttrId        // which property, interned to a u32<br>    v:  Value         // typed scalar | reference to another entity | blob ref<br>    tx: TxId          // monotone transaction number — the ordering authority<br>    op: Assert | Retract<br>}</pre><p>A KirObject isn&#39;t one row. It&#39;s a set of facts: (e, :object/name, &quot;AuthController&quot;), (e, :object/kind, &quot;Module&quot;), one fact per property path, nested JSON flattened with dotted paths (:prop/metrics.rows). A relationship is (e, :rel/from, ref), (e, :rel/to, ref), (e, :rel/kind, &quot;DependsOn&quot;). Evidence fragments stay whole as compressed blobs — decomposing prose into facts buys nothing; decomposing structure buys everything below.</p><p>The payoff shows up the moment something changes. A new version of an object doesn’t rewrite its payload — it diffs against the entity’s current fact set and emits only the facts that actually changed, as a retract/assert pair. A single field update is one retract plus one assert, on the order of 40 bytes before compression, not a full JSON document rewritten and re-signed. <strong>Retract is itself a fact, appended, never a deletion</strong> — the append-only invariant holds all the way down to the storage primitive, not just at the API surface.</p><h3>Three sort orders, three different questions</h3><p>The facts themselves are the truth. Everything queryable is a derived, rebuildable index over them, sorted three ways:</p><p>Index Physical order Answers <strong>EAVT</strong> entity → attribute → value → tx “Reconstruct this object” / object_at(id, timestamp) <strong>AEVT</strong> attribute → entity → value → tx &quot;Every entity where kind = Table&quot; — the EKL WHERE clause <strong>AVET</strong> attribute → value → entity → tx &quot;Every entity where :rel/from = X&quot; — a graph hop</p><p>The same append-only fact log, sorted three different ways on disk — no fact is ever stored twice as <em>data</em>, only re-sorted as an index:</p><pre>raw facts, in append order (the only truth)<br>  ─────────────────────────────────────────────<br>   e=auth-ctl   a=:object/name    v=&quot;AuthController&quot;        tx=101<br>   e=auth-ctl   a=:object/kind    v=&quot;Module&quot;                tx=101<br>   e=auth-ctl   a=:rel/from       v=ref(auth-ctl)            tx=104<br>   e=auth-ctl   a=:rel/to         v=ref(password-mod)        tx=104<br>   e=auth-ctl   a=:rel/kind       v=&quot;DependsOn&quot;              tx=104<br>   e=pwd-mod    a=:object/kind    v=&quot;Module&quot;                 tx=101<br>   e=pwd-mod    a=:rel/from       v=ref(pwd-mod)              tx=107<br>   e=pwd-mod    a=:rel/to         v=ref(hash-lib)             tx=107<br>        │<br>        │  same facts, re-sorted three ways — nothing recomputed, only reordered<br>        ▼<br>┌───────────────────────┐  ┌───────────────────────┐  ┌───────────────────────┐<br>│ EAVT                  │  │ AEVT                  │  │ AVET                  │<br>│ entity·attr·val·tx    │  │ attr·entity·val·tx    │  │ attr·val·entity·tx    │<br>├───────────────────────┤  ├───────────────────────┤  ├───────────────────────┤<br>│ auth-ctl :object/kind │  │ :object/kind auth-ctl │  │ :rel/from auth-ctl … │<br>│ auth-ctl :object/name │  │ :object/kind pwd-mod  │  │ :rel/from pwd-mod  … │<br>│ auth-ctl :rel/from    │  │ :object/name auth-ctl │  │ :rel/to  password-mod│<br>│ auth-ctl :rel/kind    │  │ :rel/from  auth-ctl   │  │ :rel/to  hash-lib    │<br>│ auth-ctl :rel/to      │  │ :rel/from  pwd-mod    │  │  …                    │<br>│ pwd-mod  :object/kind │  │ :rel/kind  auth-ctl   │  │                       │<br>│ pwd-mod  :rel/from    │  │ :rel/to    auth-ctl   │  │                       │<br>│ pwd-mod  :rel/to      │  │ :rel/to    pwd-mod    │  │                       │<br>└───────────┬───────────┘  └───────────┬───────────┘  └───────────┬───────────┘<br>            ▼                          ▼                          ▼<br>  &quot;give me AuthController      &quot;give me every Module&quot;    &quot;who has :rel/from =<br>   as of tx 104&quot; — one              — one contiguous        auth-ctl?&quot; — one<br>   contiguous range read            range read              contiguous range read,<br>   over EAVT, fold, done.           over AEVT.               i.e. ekos_impact&#39;s<br>                                                              one graph hop.</pre><p>Nothing here is a lookup table you keep in sync by hand — all three are mechanically derived from the same fact log and are declared <strong>rebuildable by design</strong>: if an index is ever lost or corrupted, it’s regenerated from the sealed segments, never treated as a second source of truth. That’s what “derived index” means as an invariant, not just as an implementation detail.</p><p>That third column is the one that matters for agent tool calls specifically. ekos_impact&#39;s multi-hop traversal — the &quot;what breaks if I touch this&quot; query from earlier — is not a similarity search and not a SQL join across tables that might not share a foreign key. It&#39;s a <strong>ranged scan of a sorted index</strong>: find every fact where the attribute is :rel/from and the value is this entity&#39;s id, follow the edges, repeat. Deterministic, exact, and its cost is bounded by the actual fan-out of the graph, not by how a query planner feels about it.</p><h3>Why this, and not the more obvious choices</h3><p>I considered and rejected the alternatives explicitly, in an RFC, before writing the engine:</p><ul><li><strong>SQLite with an EAV table bolted on</strong> — keeps transactions for free, but puts a fact-shaped workload on B-tree row storage. The actual wins (branch-as-manifest, prefix-coded sorted runs, mmap’d reads) all live <em>below</em> the SQL layer, somewhere SQLite cannot cede control.</li><li><strong>RocksDB or a generic KV engine</strong> — a real sorted-run store, but brings its own background compaction and WAL policy. EKOS wants deterministic, build-time-only maintenance, not a database making autonomous decisions about when to rewrite files.</li><li><strong>Datomic or XTDB themselves</strong> — the actual design this borrows from. Rejected only because they’re JVM services and EKOS is a local-first Rust CLI. Their architecture is the asset worth taking; their runtime isn’t.</li></ul><p>Measured against the older SQLite-backed ledger on the live estate (80,211 versions): 98 MB → 65 MB, with three full sorted indexes plus integrated search included in that number, not on top of it.</p><h3>Why this, specifically, for an LLM agent — and not vector search</h3><p>This is the part worth being precise about, because “EAV vs. vector search” isn’t really a storage-format argument. It’s an argument about what kind of question an agent is allowed to ask and how much you can trust the answer.</p><p><strong>Vector search answers “what’s semantically similar to this.”</strong> An embedding of a query gets compared against embeddings of chunks, and the nearest neighbors come back ranked by cosine distance. That’s a genuinely good answer to “find text that’s about the same topic.” It is structurally the wrong tool for “what does this depend on,” because similarity is not the same relation as dependency. Two functions can be textually and semantically nothing alike and still be connected by a real FeedsInto edge; two functions can read as nearly identical and share no relationship at all. An agent asking an impact-analysis question through a vector index isn&#39;t wrong occasionally — it&#39;s answering a different question than the one it was asked, every time, and the ranked-list format makes that look like an answer instead of a category error.</p><p><strong>The failure mode compounds specifically for agents, not just for humans reading search results.</strong> A person skimming five loosely-relevant chunks can usually tell when none of them actually answers the question. An agent chaining tool calls treats a retrieved chunk as ground truth for its next reasoning step by default, and a plausible-but-wrong retrieval doesn’t fail loudly — it produces a fluent, confident, structurally wrong answer three steps later, with no signal anywhere in the chain that says which step introduced the error.</p><p><strong>EAV gives the opposite trade entirely: exact, not approximate.</strong> ekos_impact doesn&#39;t rank candidate dependents by how related they seem — it returns the actual set of entities reachable by real, evidenced FeedsInto/DependsOn edges, each traceable back to the file and line that produced it. When there&#39;s no edge, the honest answer is dependents_count: 0, not &quot;here are the closest five things I could find.&quot; An agent can act on that zero, because it&#39;s a fact about the graph, not a confidence threshold that happened not to clear.</p><p><strong>Versioning is the other half of the gap, and it’s not a minor one.</strong> A vector index has no native concept of “what did this look like before the change” — updating a document typically means re-chunking and re-embedding it, and the old embedding is simply gone unless you’ve built your own version-tracking layer on top, which most RAG stacks haven’t. EAV’s tx-ordered facts make time-travel a property of the storage engine itself: object_at(id, timestamp) and AS OF queries fall out of the same index that answers &quot;what is this right now,&quot; with no separate versioning system bolted on. Drift detection — comparing an entity&#39;s first classification against its latest — is a consequence of this, not a feature built on top of it.</p><p>None of this makes lexical or fuzzy search obsolete — EKOS keeps a real full-text index (tantivy, BM25) alongside the fact store, for exactly the class of query where “find something roughly matching this name” is the actual intent. The point isn’t that ranking has no place. It’s that <strong>structural questions — dependency, lineage, ownership, drift, impact — should never be answered by a similarity metric that was never designed to represent structure in the first place</strong>, and an agent that’s about to recommend touching a table in production deserves the version of “I don’t know” that a zero-result exact query gives, not the version a low-confidence nearest-neighbor silently smooths over.</p><h3>The compiler metaphor, taken literally</h3><p>The core idea is that enterprise knowledge extraction is a compilation problem, not a summarization problem. Not “compiler-like” as a marketing gesture. Actually structured like a compiler:</p><pre>observe  →  recover  →  resolve  →  compile  →  commit<br>  (raw)      (extract)   (dedupe)   (canonicalize) (persist)</pre><p>Each stage is a pass. Each pass consumes typed artifacts and emits typed artifacts. Every artifact is content-addressable — its identity is the SHA-256 of its canonical, key-sorted JSON, with volatile fields like timestamps excluded from the hash.</p><p>That last detail is the one that pays off. Because identity derives from content, caching, reproducibility, and dependency tracking come free. Re-running a pass over unchanged input is a no-op the system detects rather than a job it repeats. The artifact store uses the same layout git uses for objects: &lt;root&gt;/&lt;2-hex&gt;/&lt;64-hex&gt;.json.</p><p>The passes themselves are ordinary. A SQL analyzer turns DDL into tables and foreign keys. A git analyzer turns history into ownership and co-change coupling. Language analyzers turn source into modules, functions, and dependency edges. On a real cold run against an unmodified open-source repo — 2,022 files across Elixir, TypeScript, SQL and ClickHouse — nine passes complete in 2.23 seconds:</p><pre>Recover stage: 2.23s wall clock, 9 passes<br>  Backend  (Elixir)      1,231 files → ~1,260 modules, ~4,800 functions<br>  Frontend (JS/TS)         324 files → real npm dependency graph<br>  Database (SQL)                     → 57 real tables<br>  Database (ClickHouse)              → 15 real tables<br>  Git history             501 commits, 124 contributors compiled<br>  CI/CD                    14 real pipelines from .github/workflows</pre><h3>An intermediate representation that is deliberately stupid</h3><p>Between raw observation and compiled model sits KIR, the Knowledge Intermediate Representation. Four primitives:</p><pre>KirObject        — an identity node: a table, a file, a service, a module<br>KirRelationship   — a directed edge between two objects<br>KirEvent          — an immutable change record: a commit, a deploy, a migration<br>KirEvidence       — a provenance pointer: source file, line, fragment, confidence</pre><p>The rule I enforce hardest is that the KIR crate is forbidden from depending on the identity resolver or any LLM client. It’s checked at review time and visible in cargo tree. No normalization. No synonym resolution. No confidence scoring on objects themselves — only on evidence. KirObject.name is whatever string the source actually contained, however ugly.</p><p>This is unglamorous and it’s the reason the system works. Every temptation to be clever — merging two similarly-named things, cleaning up an inconsistent label, inferring a relationship that “obviously” exists — is a temptation to lose the distinction between what was observed and what was guessed. Pushing all enrichment into later, separately-testable passes keeps that boundary legible.</p><p>The other rule: every node traces to at least one KirEvidence. Evidence is the only node type that references a raw artifact. It is the bridge from a compiled claim back to the line of code that justifies it, and it&#39;s what makes the whole thing auditable rather than merely plausible.</p><h3>Append-only, because knowledge has a history</h3><p>The ledger never updates or deletes. Every write is a new row in one log:</p><pre>entries(id, entry_type, payload, content_sig, written_at)</pre><p>Logical identity (id) and content identity (content_sig, a SHA-256 of the canonical payload) are tracked separately. Re-appending logically identical content is a no-op. Appending genuinely different content under the same id inserts a new version.</p><p>That split is what makes time travel real rather than aspirational. object_history(id) returns every version oldest-to-newest; object_at(id, timestamp) returns the latest version written at or before a moment. The query language exposes this directly:</p><pre>$ ekos ekl &quot;SELECT * FROM technologies AS OF 2026-01-01&quot;</pre><p>Which means the system can answer a question most documentation tooling structurally cannot: <em>what did we understand about this system before the change?</em> Not what the docs said — what the compiled evidence supported.</p><h3>What happens when code and docs disagree</h3><p>This is the question I get asked most, so I’ll answer it with the actual mechanism, not a marketing description of it.</p><p>Documentation drift isn’t detected by diffing a markdown file against source. It’s detected by comparing the <strong>first and last version of the same claim’s history in the ledger</strong> — a capability that turned out to already exist, because everything in the ledger is versioned by construction. Nothing new had to be built for storage; only a comparison function.</p><pre>ekos_architecture_drift → real findings against a real, already-committed ledger</pre><pre>REAL DRIFT FINDINGS: 7<br>  ekos-semantic   : core library      -&gt; shared utility<br>  ekos-runtime    : core library      -&gt; plugin/connector<br>  ekos-kir        : shared utility    -&gt; core library<br>  ekos-marketing  : shared utility    -&gt; test support<br>  ekos-scheduler  : shared utility    -&gt; plugin/connector<br>  ekos-simulation : shared utility    -&gt; plugin/connector<br>  ekos-ledger     : core library      -&gt; shared utility</pre><p>Each row is a real crate whose LLM-classified architectural role changed between two recover runs — surfaced automatically, not asked for. One of these is worth sitting with: ekos-kir legitimately reads as either &quot;shared utility&quot; (a types crate nearly everything depends on) or &quot;core library&quot; (it defines the foundational representation the whole pipeline is built around). A classifier landing on different sides of that real ambiguity across two runs isn&#39;t a bug — it&#39;s exactly the honest signal drift detection exists to surface.</p><p>Two things about the design are deliberate:</p><ul><li><strong>Drift is reported, not scored.</strong> It does not fold into a composite completeness number, because there’s no calibrated weight yet for how much one drift finding should move a score, and inventing one would be false precision.</li><li><strong>A claim that’s new in the later snapshot is never reported as a role change.</strong> There’s no real “from” value to name, so misreporting it as a change would be worse than staying silent.</li></ul><p>The same versioned-history primitive answers a related, harder question with a full audit trail rather than a single flag: ekos architecture diff --from &lt;ts&gt; --to &lt;ts&gt; reports every technology added or removed, every role reclassification, every new risk or open question between two points in time — a plain id-set comparison per kind, because every covered object already has a deterministic id. No fuzzy matching, no new storage.</p><h3>A late correction: determinism first, but not determinism only</h3><p>When I first wrote about this project, I described the design as “deterministic extraction, LLM strictly layered on top, invoked only when asked.” That was accurate then and it undersold a real gap: a system that only describes what it can deterministically parse produces a lot of correctly-structured, completely silent pages. A real function with a real 9-edge dependency graph and zero prose describing what it does isn’t more honest than a described one — it’s just less useful, for exactly the code that needs documentation most: the undocumented kind.</p><p>So the latest revision adds an LLM extraction pass at compile time whose only job is to describe entities that have no other description — grounded in their real compiled structure and real source text, never invented from a name alone. The result is persisted to the ledger as evidence like anything else, which means it can be diffed, reviewed, and — this is the part that matters — flagged as <strong>stale</strong> when a human-written comment already exists but no longer matches what the code does:</p><pre>before:  &quot;Not documented in source.&quot; — nothing else<br>   ↓  RFC 0088, post-commit, evidence-grounded pass<br>after:   104/116 real symbols described, 0 errors<br>   ↓  live run against a local model, zero API cost<br>found:   2 real bugs a passing test suite hadn&#39;t — same session</pre><p>This doesn’t relax the invariant, it completes it. The rule was never “no LLM.” The rule is “no unlabeled inference” — and a generated description sitting in the ledger as a Claim, with its own evidence, subject to the same drift detection as everything else, is exactly as auditable as a deterministically-extracted fact. It&#39;s a claim, not a fact, and the system now says so at every layer that touches it.</p><p>The one place the system crosses a stricter line on purpose — letting an LLM build and run a live SQL query against ClickHouse rather than only describing already-compiled data — stays fenced separately: the generated SQL is parsed and hard-rejected unless it’s exactly one SELECT, results are redacted, and every call is written to the ledger as an audited event even though the row data itself never is.</p><h3>Won’t an infinite append-only log eventually get huge and slow?</h3><p>The second question I get asked constantly, and the honest answer has three real, separate parts — because “won’t it get slow” is actually three different engineering problems wearing one trenchcoat.</p><p><strong>Storage size.</strong> Loose JSON artifacts on disk waste space to filesystem block slack — most artifacts are small (median 707 bytes) on filesystems with 4KB blocks. The fix was packing them into sealed, content-addressed, zstd-compressed segments instead of one file per artifact:</p><pre>Measured on the live estate:<br>  46,539 loose files  →  1 packed segment + manifest,  built in 4.5s<br>  214 MB on disk      →  31 MB   (6.9× smaller)</pre><p>Segments are immutable once sealed (~64MB or on close), fsynced, and self-describing — the frame index isn’t a sidecar file that can go stale, it’s derived by a cheap header-only scan on open (36 bytes per frame, sub-100ms at estate scale). “An index that cannot go stale beats one that must be validated” is a real design principle here, not a slogan — a persisted sidecar index remains a documented option if profiling ever shows the scan matters.</p><p><strong>Point-in-time read speed.</strong> This is the one that actually threatened to get slow. Folding an entity’s full version history from genesis is O(versions × entries) — fine at low version counts, a real problem for an object with a long history. The fix is periodic, purely-additive checkpoints: every 20 versions, a (entity, tx, facts) snapshot is written, and a point-in-time read seeds its fold from the nearest prior checkpoint instead of genesis.</p><p>I want to be specific about a mistake I caught before it shipped, because it’s the kind of thing that matters more than the feature itself: the first draft of this design claimed it “eliminates O(full history) reads entirely.” Reading the actual index key order (entity → attribute → value → transaction, not entity → transaction) showed that claim was false — a checkpoint shrinks the <em>fold</em>, not the underlying storage scan. I corrected the RFC’s claimed win before writing the code, not after a benchmark contradicted it.</p><p><strong>Crash recovery.</strong> Sealed segments are verified by hash; the active segment can have a torn tail after a crash, and recovery truncates to the last valid, checksummed batch boundary — everything before it is intact by construction. This existed for a while as tested-but-unexposed code before I added ekos ledger repair, a command that reports every sealed segment&#39;s integrity individually instead of the previous, blunter &quot;restore from backup&quot; as the only recovery path.</p><p><strong>What I have not done, on purpose.</strong> The append-only invariant — no object-level delete, no tombstone, anywhere — is load-bearing: it’s what makes AS OF queries and drift detection trustworthy, because &quot;the history&quot; can&#39;t have quietly lost a chapter. Real retention/pruning of old delta history is in genuine tension with that invariant, and I&#39;ve deliberately not resolved that tension by building around it quietly. It&#39;s named explicitly as its own future decision requiring a separate conversation before any design work starts — not the &quot;next phase in sequence&quot; I back into because a roadmap says so. An append-only log that never grows is not append-only; the honest position is compression and acceleration now, and an explicit, separately-reasoned decision later about what — if anything — old history is worth discarding.</p><h3>Real repositories find bugs that fixtures never will</h3><p>A pattern that repeated often enough to be a principle: hand-built test fixtures encode the assumptions of whoever wrote them. Real, messy, unmodified repositories don’t.</p><p>Running against real code surfaced, among others:</p><ul><li><strong>JSX inside plain </strong><strong>.js files</strong> — 18 of 291 files failed to parse because the parser doesn&#39;t enable JSX for .js by default. Fixed by forcing JSX on for JS source types while deliberately leaving .ts alone, since &lt;T&gt;expr is a genuine generic-assertion ambiguity there.</li><li><strong>ClickHouse DDL clauses the SQL parser never supported</strong> — CODEC(...), INDEX ... TYPE ... GRANULARITY, PARTITION BY, SAMPLE BY, SETTINGS, whole CREATE DICTIONARY statements. Found while documenting a real analytics repo. Handled by a preprocessing step that strips well-formed occurrences only, before the SQL reaches the parser.</li><li><strong>Identity resolution over-merging</strong> — the resolver collapsed distinct entities that shared a name prefix, in one real case merging six real ClickHouse tables into one. The fix is a kind-exclusion list for object kinds that are already deterministically identified by construction. I published the bug before I published the fix.</li></ul><h3>How this actually gets used: impact analysis</h3><p>The point of all of the above isn’t the ledger — it’s the questions it can answer that a grep can’t, because they cross a format boundary. The one I show engineers first is multi-hop impact tracing through a real ETL pipeline:</p><pre>→ tools/call ekos_impact { &quot;id&quot;: &quot;f7238521-...&quot;, &quot;direction&quot;: &quot;dependents&quot;, &quot;max_hops&quot;: 5 }</pre><pre>← result { &quot;count&quot;: 5, &quot;hops&quot;: [<br>  { &quot;hop&quot;: 1, &quot;name&quot;: &quot;...ktr:4&quot;, &quot;via&quot;: &quot;FeedsInto&quot; },<br>  { &quot;hop&quot;: 2, &quot;name&quot;: &quot;...ktr:6&quot;, &quot;via&quot;: &quot;FeedsInto&quot; },<br>  { &quot;hop&quot;: 3, &quot;name&quot;: &quot;...ktr:8&quot;, &quot;via&quot;: &quot;FeedsInto&quot; },<br>  { &quot;hop&quot;: 4, &quot;name&quot;: &quot;...ktr:7&quot;, &quot;via&quot;: &quot;FeedsInto&quot; },<br>  { &quot;hop&quot;: 5, &quot;name&quot;: &quot;...ktr:3&quot;, &quot;via&quot;: &quot;FeedsInto&quot; }<br>]}</pre><p>That’s the exact question a data engineer asks before editing an early step in a twelve-step pipeline — answered without opening the file. And in the same real session, the honest counterpart:</p><pre>→ tools/call ekos_dependents { &quot;id&quot;: &quot;a77168f6-...&quot; }   // a SQL-defined fact table</pre><pre>← result { &quot;target&quot;: { &quot;kind&quot;: &quot;Table&quot; }, &quot;dependents_count&quot;: 0, &quot;dependencies_count&quot;: 0 }</pre><p>A real Pentaho job in the same workspace writes to exactly this table — the SQL Table object and the Pentaho Sink node referencing it by name simply haven&#39;t been resolved to one identity yet. That&#39;s a documented, open gap, not a hidden one. A tool whose confidence you can&#39;t calibrate is a tool you can&#39;t use for a deploy decision, and the zero above is more useful known than smoothed over.</p><h3>What I’d tell you to take from this</h3><p>The useful generalization isn’t “build a knowledge compiler.” It’s narrower:</p><p><strong>Separate observation from inference at the type level, not by convention.</strong> If your data structure can hold both a fact and a guess without distinguishing them, they will merge, and you will not notice when it happens.</p><p><strong>Make absence expressible.</strong> A schema where “unknown,” “empty,” and “not yet computed” all serialize the same way is a schema that will eventually lie to you. The same discipline that keeps a diagram from drawing an edge with no evidence is what makes drift detection trustworthy instead of noisy.</p><p><strong>Versioned storage isn’t a cost center for a feature like drift detection — it’s the whole feature.</strong> I didn’t build drift detection. I built an append-only ledger for other reasons, and drift detection turned out to be “compare the first and last version of something already being versioned.” Look for that shape before building something new.</p><p><strong>Test against something you didn’t write.</strong> Every parser bug above came from a real repository. None came from the fixture suite, which was passing the whole time.</p><p><em>EKOS is open source and in active development, with the gaps above still open and documented: </em><a href="https://github.com/alexeyban/EKOS"><em>github.com/alexeyban/EKOS</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=952af45fd932" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Asking a Data Warehouse Questions in Plain English: An Honest Look at a Semantic Vault on…]]></title>
            <link>https://medium.com/@alexeyban/asking-a-data-warehouse-questions-in-plain-english-an-honest-look-at-a-semantic-vault-on-95b7370cdfc9?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/95b7370cdfc9</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[semantic-layer]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Fri, 26 Jun 2026 15:06:23 GMT</pubDate>
            <atom:updated>2026-06-26T15:18:19.646Z</atom:updated>
            <content:encoded><![CDATA[<h3>Asking a Data Warehouse Questions in Plain English: An Honest Look at a Semantic Vault on Databricks</h3><p><em>Part 3 of an engineering diary about building a dvdrental data platform on Azure and Databricks. The earlier parts covered the medallion pipeline and the metadata-first plan. This one is about what happened when we pointed natural-language interfaces at the result — across two Databricks Genie sessions, one agent/MCP session over the vector and graph layer, and a final run at a million rows.</em></p><h3>The promise, restated plainly</h3><p>The pitch for a semantic layer is seductive: build it once, and a business user types <em>“which customers rent more than 30 films?”</em> into a box and gets an answer — no SQL, no analyst in the loop. BI engineers get a governed model. Data engineers get a single source of truth. Everyone wins.</p><p>This article is the post-mortem on whether that promise held. The short version: <strong>natural-language querying works remarkably well — but only on the parts of the model shaped the way the LLM already reasons, and the parts we were proudest of were the parts the easy interface couldn’t touch.</strong> A semantic layer turns out to need <em>two</em> front doors, and once you scale it past a toy dataset, the data-quality questions it was papering over become the actual work.</p><p>We’ll build that case from the evidence: a plan, a build, and four real sessions.</p><h3>1. What we planned vs. what we built</h3><p>The plan was strictly metadata-first. A single hand-authored source_metadata.json — 15 entities, 21 relationships, primary and foreign keys — would drive everything physical: table DDL, dbt models, ontology, embeddings, and an MCP tool surface. The spine was &quot;author the metadata once; generate the rest.&quot; LLM enrichment was deliberately kept <em>off the critical path</em> — it decorates the metadata, it never blocks the core tables.</p><p>That discipline mostly survived contact with reality:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*N3n3krHxd1KGsSgw5FURJw.png" /></figure><p>Planned Built Gap 15 entities from JSON 9 sem_*_entity tables materialized The other 6 stayed as metadata candidates only 21 relationships <strong>3</strong> sem_*_rel tables materialized 18 registered in relationship_candidate, not built Vector Search + MCP tools 4 VS indexes, 5 MCP tools Built as planned Genie space (config-only, Phase 14) Manual UI registration <strong>No SDK API exists</strong> — cannot be GitOps&#39;d Graph layer (GraphFrames optional) Pure SQL graph views GraphFrames rejected — doesn&#39;t run on Serverless</p><p>The discrepancies aren’t failures; they’re the residue of shipping the <em>foundation</em> (Phases 1→4→5) fully and letting the enrichment land incrementally. Three relationships covered the questions people actually asked. The other eighteen were speculative.</p><p>The architecture that emerged turns each Silver row into <strong>four complementary shapes</strong>, because no single representation serves every consumer:</p><pre>Silver Delta (dvdrental.silver_*)<br>        │  dbt: 22 sem_* models<br>        ▼<br>  ┌─────────────┬──────────────┬───────────────┬──────────────┐<br>  ▼             ▼              ▼               ▼<br>SCD2 entity   relationship   context JSON    graph views<br>sem_*_entity  sem_*_rel      sem_*_context   graph_vertex_v<br>(versioned)   (links)        (rich blobs)    graph_edge_v<br>  │                             │<br>  │                             ▼<br>  │                       Vector Search indexes (GTE-large-en)<br>  │                             │<br>  └──────────────► Consumers ◄──┘<br>        Genie (SQL)   MCP tools (similarity)   Agents</pre><p>The design tension that runs through the rest of this article is right there at the bottom: <strong>Genie and the MCP tools consume different shapes.</strong> Genie generates SQL; the MCP tools call Vector Search and traverse the graph. Keep that split in mind — it’s the whole story.</p><h3>2. Genie in practice: business questions, answered in plain English</h3><p>Databricks Genie registers a set of Unity Catalog tables and lets an LLM write and run SQL against them on a user’s behalf. We registered the dp Semantic Vault (dev) space and put it in front of real business questions. The results were genuinely useful — within a clear boundary.</p><h3>2.1 What Genie is good at</h3><ul><li><strong>JSON context extraction.</strong> Ask <em>“which customers have more than 30 rentals?”</em> and Genie reaches into sem_customer_context.context_json, generates GET_JSON_OBJECT(...) against the nested activity.rental_count field, and answers. The richer the context blob, the better the answers — the single biggest payoff of the context-JSON design.</li><li><strong>Flat, well-named tables.</strong> business_rule, ontology_entity, and attribute_metadata are queried accurately with zero hints. <em>&quot;Show me all business rules for the film entity&quot;</em> just works, because the column names are self-explanatory.</li><li><strong>The Instructions field is high-leverage.</strong> Pasting the ontology YAML (entity synonyms and definitions) into Genie’s Instructions field measurably improved term resolution. Without it, “renter” and “member” returned nothing; with it, Genie mapped both to customer. The cheapest, highest-impact tuning knob in the system.</li><li><strong>Sometimes Silver beats Semantic.</strong> For <em>“what are the most rented categories?”</em>, Genie chose silver_film_category + silver_rental over the semantic tables — a flatter relational schema is easier for a SQL generator than digging through JSON. A useful humility lesson: the fancy layer isn&#39;t always the right answer.</li></ul><p>Both Genie sessions below followed the same internal loop — worth drawing, because it’s the reasoning skeleton behind every answer:</p><pre>NL question<br>     │<br>     ▼<br> ① Asset discovery   → forYouAssets / tableSearch (find semantic.* + dp-vector-search)<br>     │<br>     ▼<br> ② Schema profiling  → readTable (map join paths, check coverage)<br>     │<br>     ▼<br> ③ SQL aggregation   → executeCode (GROUP BY, LEAD(), REGR_SLOPE…)<br>     │<br>     ▼<br> ④ Validation loop   → inspect render data, fix chart/query coupling<br>     │<br>     ▼<br> ⑤ Honest caveat     → flag sparse data / artifacts before shipping</pre><p>We ran two sessions over the same dataset with different framings. Side by side they show how much the <em>segmentation choice</em> shapes the answer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*1MHFBlouf7thUI8zfpiuAQ.png" /></figure><p>2.2 Session A — country-group trend forecast</p><p><strong>Q1 · “Calculate the rental funnel: most-popular films (Film A), what people watch after Film A, top cities/genres, and the user profile. Use the semantic layer + Vector DB / AI Search.”</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lc2Gg4Lw8D2ZhYHrpNzQ_Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/741/1*cOdpUIPWGpUHUBpNJwFN-w.png" /></figure><p>Top customer groups it surfaced:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/749/1*C9fc7KmlscjAFToPZmOo6A.png" /></figure><p>Customer group Rentals Users Avg/user Active | India 1,572 60 26.2 Active | China 1,426 53 26.9 Active | United States 968 36 26.9 Active | Japan 825 31 26.6 Active | Mexico 796 30 26.5</p><blockquote><em>The analyst explicitly asked Genie to use Vector Search — but the funnel was answered with pure SQL aggregation. </em><strong><em>The AI Search indexes were never invoked.</em></strong><em> That boundary is the subject of Section 3.</em></blockquote><p><strong>Q2 · “Forecast next-quarter rental volumes per item and find anomalies.”</strong> This is the part I most want BI engineers to notice, because it’s the difference between a demo and an engineering artifact. Genie reached for the native ai_forecast() function first, found it <strong>disabled</strong>, and fell back to linear REGR_SLOPE/REGR_INTERCEPT. On the sparse late period the forecast <strong>collapsed toward zero</strong> — and rather than ship those zeros, Genie labeled them an artifact of incomplete history, not a prediction. Anomaly detection via z-score:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/775/1*N9dXvPZ2uZTX5XrcyIzG8g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AACE4a8elIopcmttU8gz_Q.png" /></figure><p><strong>Q3 · “Predict genres and styles per group — trends, not titles.”</strong> This was the pivot that rescued the forecast: reframe the collapsed time series into an <strong>early-vs-late preference-share comparison</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/748/1*ta7j9d9h82mDfp7BbNWTCw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hk8PssONwJLrXNXKFhPvng.png" /></figure><p><em>Style</em> was approximated by concatenating MPAA rating with special_features — a stated proxy, not a real taxonomy. The same restraint showed up earlier in the Film-A→Film-B step: the sequential signal was 1–2 transitions per group, and the report said so plainly rather than dressing it up. <strong>Surfacing the weakness of a result is itself a feature.</strong></p><h3>2.3 Session B — store-based funnel &amp; audience analytics</h3><p>A second analyst ran the same dataset through a <strong>store</strong> lens and got a four-page dashboard. The operational overview page covers the full funnel (catalog → inventory → ever-rented), top titles, genre popularity, a genre-by-city heatmap, and the semantic-layer top-films/top-customers tables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XqAHSPdvzMhNLlKk68xx1A.png" /></figure><p>The reasoning approach Genie logged is itself instructive:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*tIf-xl-qbsY7ymWaqbTxkg.png" /></figure><p>Decision Choice made Why Dataset Reuse existing dashboard dataset Avoid re-deriving the same aggregates Segmentation Store 1 vs Store 2, then top countries Activity-based segments were <strong>uninformative</strong> — every synthetic customer is a heavy renter Forecast Linear REGR_SLOPE/REGR_INTERCEPT over trailing quarters Simple, defensible on short history Anomaly Slope + last-quarter residual vs. catalog-wide z-scores Separates trend outliers from one-off spikes Soft deletes WHERE NOT _is_deleted throughout Respect the Silver-layer delete pattern</p><p>For trend forecasting it projected next-quarter genre demand per store and per country group:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MOxKuDNZOxTpwe9UkWAb8A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hk8PssONwJLrXNXKFhPvng.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*KX4TCcE0nO9d0JYLIFupSg.png" /></figure><p>And it ran item-level anomaly detection over quarterly rental volume, classifying each film as Rapid Growth, Sharp Decline, Surprise Spike, or Surprise Drop:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gTFQDuCMd9szG9at-3c_6g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*cIxZ6z-8L99NnsDe9mPLqA.png" /></figure><p>Two debugging notes made it into the report — the kind of detail that separates a working build from a slide:</p><ul><li>The <strong>Pulp Beverly</strong> follow-on query needed i2.film_id carried <em>inside</em> the sequential CTE, or the downstream next-film join referenced the wrong identifier.</li><li>Early anomaly bar charts showed misleading null/subtotal bars because the editor injected grouping; the fix was a strict <strong>single-dimension + one-measure</strong> chart pattern.</li></ul><p>For a non-technical user, the takeaway from both sessions is the headline: a multi-page dashboard, a forecast, and a written report — all from plain-English prompts, no SQL authored by hand.</p><h3>3. Where plain English breaks down</h3><p>Now the failures, because they define the operating envelope.</p><p><strong>Genie cannot use Vector Search.</strong> It generates SQL, full stop. Ask <em>“find films similar to ACADEMY DINOSAUR”</em> and it degrades to a LIKE match on the title or a same-category join — it never calls film_vs_index. The entire embeddings investment is invisible to Genie. Semantic similarity is the job of the MCP find_related_films tool, not the natural-language SQL surface.</p><p><strong>Genie cannot traverse the graph.</strong> Our entity_key is a JSON string — {&quot;inventory_id&quot;: 42} — transparent to code but opaque to the LLM. Asked for the graph degree of inventory item 42, Genie couldn&#39;t construct the key from the integer the user typed. And it never emits WITH RECURSIVE, so multi-hop traversal is off the table. The clever JSON-PK design that made the graph layer uniform is precisely what locked Genie out of it.</p><p>That produces a clean division of labor:</p><pre>Natural-language question<br>            │<br>      ┌─────┴──────┐<br>      ▼            ▼<br>  &quot;aggregate /   &quot;similar to X&quot; /<br>   filter /       &quot;related&quot; /<br>   rule lookup&quot;   &quot;traverse&quot;<br>      │            │<br>      ▼            ▼<br>    GENIE        MCP / AGENT<br>   (SQL gen)    (Vector Search<br>                 + graph SQL)</pre><p>The example queries our own session reports shipped make the split concrete — and tellingly, two of the five (C and D) are ones Genie <em>cannot</em> run, only document:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*BKOmPuuvyUdZIP9KCld2Og.png" /></figure><p>That table is the whole article in miniature: the natural-language SQL surface nails A and B, while C, D, and E — the semantic-search and graph features we invested the most in — live behind a separate programmatic door. <strong>A semantic layer needs two front doors.</strong> Genie for SQL-shaped questions; an agent/MCP surface for similarity and graph. The next section is that second door, opened.</p><h3>4. The other front door: an agent session over vector and graph</h3><p>To exercise the capabilities Genie declines, we ran an agent session directly against the Vector Search indexes and graph views — on the original small dvdrental slice (<strong>1,000 films · 599 customers · 4,581 inventory items</strong>). These are precisely the C/D/E questions from the table above.</p><p><strong>Q1 · “Find films by semantic description.”</strong> The agent located film_vs_index on dp-vector-search plus the test_semantic_layer notebook pattern, then ran natural-language film retrieval <em>directly against the index</em> — the call Genie never makes.</p><p><strong>Q2 · “Find customers matching ‘highly active customer with many open rentals’.”</strong> The money shot for Vector Search: an ANN query against customer_vs_index returned ranked matches with cosine scores.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/742/1*gIr1OAW29yARYc7O02uNKw.png" /></figure><p>It also exposed a real rough edge: <strong>HYBRID mode with projected return columns was rejected as an invalid request</strong> — only a minimal ANN query succeeded, returning entity_id + score and nothing else. Mapping IDs back to names needs a second SQL hop. The index works; projecting extra columns from it did not.</p><p><strong>Q3 · “Degree distribution by entity type — which type is most interconnected?”</strong> Pure graph territory. Querying sem_graph_metrics:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*l_bYLBsVDtFxpKZhhbPkkg.png" /></figure><p>Category is the hub — 16 nodes carrying ~62.5 edges each — while customer, address, staff, language and store show <strong>zero degree</strong>: an honest tell that those relationships are registered in metadata but not materialized (recall 3 of 21 *_rel tables built).</p><p><strong>Q4 · “Find two customers who live nearby with the same film preferences.”</strong> This combined vector intuition, graph, and geography — and is most instructive for how it <em>failed before it succeeded</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*zm1_ySgEYcEzU96OWsUkGA.png" /></figure><p>The winning answer: <strong>Brenda Wright (#31, India)</strong> and <strong>Pearl Garza (#224, India)</strong>, sharing Classics / Foreign / Documentary and both having rented <em>Army Flintstones</em>. The lesson is in the failures: at 599 customers “same city” is too sparse to ever match, “same country” is the right grain — and Vector Search is reached through the SDK/MCP tool, never a SELECT. This single session is the clearest proof of the thesis: the similarity and graph capabilities are real and valuable, but they live behind the programmatic door, with their own sharp edges.</p><h3>5. What changed at one million rows</h3><p>Everything above ran on the original ~1,000-film slice. We then pointed the <em>same</em> semantic layer at an expanded synthetic dataset — on the order of a million rental rows, <strong>13,599 customers</strong>, with history stretching 1999→2026 — and ran a Genie business-analytics session against it. The architecture didn’t change; what changed was which problems became visible.</p><p><strong>The coverage cliff.</strong> At small scale the payment gap was a footnote. At a million rows it became the central modeling decision:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*u4F1TXfwQWlJ5W_iWio8fw.png" /></figure><p>Only 599 of 13,599 customers carry any payment record, and payments cover three months of 2007 while rentals span 27 years — the same rental-without-payment quarantine class the architecture doc flagged, now dominating the dataset. Genie’s response was to carry <strong>two revenue lenses</strong> rather than pick one and hide the gap:</p><ol><li><strong>Actual payment revenue</strong> — reliable, but only for the 599-customer paying cohort.</li><li><strong>Implied revenue</strong> — rental count × film rental rate, for the full population.</li></ol><p>And the lens changes the answer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/745/1*XfRXXl-xW95lXzapqu8iyA.png" /></figure><p>Both are true; neither alone is honest.</p><p><strong>Schema assumptions break at scale.</strong> An early query assumed customer_id was a column in sem_customer_context. It isn&#39;t — the key is entity_id, with business fields inside context_json. The JSON-key design that helps Genie <em>extract</em> fields also trips up first-draft SQL. Cheap to fix, but it surfaced on the first query.</p><p><strong>Retention becomes legible at volume.</strong> With 13,599 customers the signal is clear and monotonic in engagement depth:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/735/1*-c0iD2RMBSU3-KvnN1L5Zg.png" /></figure><p><strong>Exact pairs get <em>worse</em> at scale; category affinity gets better.</strong> Counterintuitively, more data made title-level co-rental even sparser (top pairs shared by only ~5 customers). Category affinity stayed stable and actionable — Horror→Music (lift 1.21), Foreign↔Sports (1.15), Family→Sci-Fi (1.13) — so the recommendation strategy had to live at category grain, not title.</p><p><strong>Forecasting still needs a hand-rolled heuristic.</strong> As in the small-data sessions, there was no precomputed forecast object and no ai_forecast(), so Genie built an explainable weighted blend — 50% latest complete quarter, 30% same quarter last year, 20% trailing-4-quarter average — and projected the 2026-Q3 leaders:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/733/1*NVotQZcLR5aD2Vh8YMKh-A.png" /></figure><p>The takeaway from the scale-up: <strong>the semantic layer held, but the data-quality questions it had been papering over became the main event.</strong> Coverage gaps, revenue definition, and key conventions barely matter at 1,000 rows; at a million, they <em>are</em> the analysis.</p><h3>6. Lessons learned</h3><p><strong>Shape your tables for the reasoner, not for elegance.</strong> Flat, well-named columns and JSON blobs with obvious paths are what an LLM handles well. The abstractions we were proudest of — entity_v, relationship_v, JSON-encoded keys — were the ones Genie stumbled on. Our own architecture doc ended up recommending <em>avoid registering </em><em>graph_vertex_v / </em><em>entity_v in Genie; they confuse the LLM.</em></p><p><strong>Build two front doors on purpose.</strong> Genie for SQL-shaped aggregation, filtering, and rule lookup; an agent/MCP surface for similarity and graph. Trying to make one interface do both is how you get a demo that disappoints in week two.</p><p><strong>The Instructions field is real engineering, not a setting.</strong> Synonym mapping moved questions from “no results” to “correct.” Budget time for it.</p><p><strong>Manual steps don’t fit GitOps, and pretending otherwise wastes time.</strong> Genie spaces have no SDK API; we stopped trying to automate registration and documented the UI steps instead. Knowing what <em>can’t</em> be code is part of the design.</p><p><strong>Let the data’s weaknesses show.</strong> The forecast that collapsed to zero, the quarantined payment-less customers, the sparse sequential signal — naming these openly is what separated a believable report from a polished lie.</p><p><strong>Don’t over-build the speculative parts.</strong> Three materialized relationships answered the real questions; the other eighteen would have been inventory nobody queried.</p><p><strong>Scale doesn’t break the layer; it exposes the data.</strong> The same models that looked clean on 1,000 films forced an explicit two-lens revenue model and a key-convention fix at a million rows. Validate coverage before you trust any aggregate.</p><p>A semantic layer absolutely can let a business user interrogate a warehouse in plain English. It just won’t be magic, it won’t cover every question through a single door, and the honest version is the one that tells you so.</p><p><em>Stack: Azure Databricks, Unity Catalog, Delta Lake, dbt (SCD2 incremental merge), Vector Search (GTE-large-en), Databricks Genie, MCP tools, Databricks Asset Bundles. Dataset: dvdrental. All figures are from the project’s own session exports and architecture docs.</em></p><blockquote><strong><em>A note on the data.</em></strong><em> Everything in this article is built on a synthetic dataset. The dvdrental database — its film titles, customer names, rental histories, payments, and geographic distribution — is artificially generated test data. Titles like </em>Bucket Brotherhood<em>, </em>Monterey Labyrinth<em>, or </em>Pulp Beverly<em> and customer names like </em>Brenda Wright<em> do not refer to any real film, person, or company, and any resemblance to actual names is purely coincidental. The trends, forecasts, anomalies, and statistics shown here do not reflect any real-world rental market or business; they exist solely to exercise the semantic layer and demonstrate how natural-language querying behaves against a governed data model.</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=95b7370cdfc9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Metadata-Driven Azure Data Platform with Azure Data Factory, Databricks, and dbt]]></title>
            <link>https://medium.com/@alexeyban/metadata-driven-azure-data-platform-with-azure-data-factory-databricks-and-dbt-71b28e3dba3d?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/71b28e3dba3d</guid>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[microsoft-azure]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 13:09:44 GMT</pubDate>
            <atom:updated>2026-06-23T13:09:44.359Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A technical case study: how one JSON config file governs fifteen tables, three processing layers, and a complete medallion lakehouse — built in six days.</em></p><h3>Introduction</h3><p>Most data platforms begin with good intentions and end with spaghetti. Fifteen source tables become fifteen ADF pipelines. Fifteen pipelines become fifteen Databricks notebooks. Each one slightly different, each one requiring its own PR when you need to add an audit column.</p><p>This case study documents a different approach, built on the PostgreSQL <strong>dvdrental</strong> sample database: a single ADF pipeline controlled by a JSON config file, a single Databricks notebook reading that same config, and a dbt project materialising Silver and Gold layers — all without touching pipeline code when a new table is added.</p><p>The platform was designed around three constraints:</p><ol><li><strong>Infrastructure as code from day one.</strong> No clicking in the portal. Every resource provisioned by Terraform, every ADF artifact committed to Git.</li><li><strong>Managed identity everywhere.</strong> No service principal passwords, no storage keys, no SAS tokens stored anywhere in the codebase.</li><li><strong>Documentation-first.</strong> The metadata schema was designed before the first pipeline was written. If you cannot express a table’s ingestion rules in JSON, it does not belong in this platform yet.</li></ol><p>The result is a system where adding a new source table requires editing one JSON file, creating one dbt model, and raising one PR. No new pipeline code. No new notebooks. No infrastructure changes.</p><h3>High-Level Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gvudJIShCkmLrt3SeWRGiA.png" /></figure><h3><strong>Solution Architecture</strong></h3><p>The platform spans three Git repositories with strict separation of concerns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/853/1*aY1nKxw7_0123ICxExMdJg.png" /></figure><p>Infrastructure Layer (Terraform)</p><p>The azure-data-project repo contains 15 Terraform modules and 119 resource blocks across 53 unique resource types. The naming module wraps aztfmod/azurecaf to enforce CAF naming conventions throughout: dev-kv-dp-n001, dev-adf-dp-ajim-n001, devstdpn001.</p><p><strong>Security baseline (non-negotiable defaults across all modules):</strong></p><pre># modules/storage/main.tf<br>resource &quot;azurerm_storage_account&quot; &quot;main&quot; {<br>  name                          = var.name<br>  resource_group_name           = var.resource_group_name<br>  location                      = var.location<br>  account_tier                  = &quot;Standard&quot;<br>  account_replication_type      = &quot;LRS&quot;<br>  min_tls_version               = &quot;TLS1_2&quot;<br>  enable_https_traffic_only     = true<br>  public_network_access_enabled = false   # Always off in staging/prod<br>  is_hns_enabled                = true    # ADLS Gen2<br>}</pre><p><strong>ADF managed identity wiring (no credentials stored):</strong></p><pre># modules/adf/main.tf — MSI gets exactly the permissions it needs, nothing more<br>resource &quot;azurerm_role_assignment&quot; &quot;adf_storage_contributor&quot; {<br>  scope                = var.adls_id<br>  role_definition_name = &quot;Storage Blob Data Contributor&quot;<br>  principal_id         = azurerm_data_factory.main.identity[0].principal_id<br>}<br><br>resource &quot;azurerm_role_assignment&quot; &quot;adf_kv_secrets_user&quot; {<br>  scope                = var.key_vault_id<br>  role_definition_name = &quot;Key Vault Secrets User&quot;<br>  principal_id         = azurerm_data_factory.main.identity[0].principal_id<br>}</pre><p>The Databricks Access Connector similarly receives Storage Blob Data Contributor on ADLS — the Unity Catalog metastore uses the same managed identity path with no access keys anywhere in the system.</p><h3>Medallion Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PMwrC8ynB1v7KGvlErOlqg.png" /></figure><h3>Metadata-Driven Architecture</h3><p>The core design decision: <strong>one pipeline for all tables, controlled by a JSON config file</strong>.</p><p>The alternative — a separate ADF pipeline per table — would mean 15 pipelines with identical Copy Activity logic but different table names. Adding an audit column would require 15 PRs. Renaming an ADLS path would require 15 parameter changes. Disabling a table during a hotfix would require editing 15 pipelines.</p><p>With metadata-driven design, everything is in one place.</p><h3>Entity Configuration Schema</h3><pre>// adf-pipelines/metadata/ingestion/pgsql_to_adls_incremental.json<br>{<br>  &quot;entity&quot;: &quot;rental&quot;,<br>  &quot;unique_name&quot;: &quot;rental_public_rental_incremental&quot;,<br>  &quot;source_schema&quot;: &quot;public&quot;,<br>  &quot;source_table&quot;: &quot;rental&quot;,<br>  &quot;primary_key_column&quot;: &quot;rental_id&quot;,<br>  &quot;watermark_col&quot;: &quot;last_update&quot;,<br>  &quot;target_layer&quot;: &quot;bronze&quot;,<br>  &quot;target_container&quot;: &quot;raw&quot;,<br>  &quot;target_path&quot;: &quot;dvdrental/incremental&quot;,<br>  &quot;file_format&quot;: &quot;parquet&quot;,<br>  &quot;load_type&quot;: &quot;incremental&quot;,<br>  &quot;active&quot;: true<br>}</pre><p>The active flag is critical: the ADF pipeline&#39;s <strong>Filter activity</strong> runs @equals(item().active, true) before the ForEach. Toggling a table off requires editing one JSON field and raising a PR — no pipeline editing, no deploy coordination.</p><h3>Metadata Flow</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OuqqYxewSa5c5a6ri2gHHw.png" /></figure><h3>Why Metadata Is More Valuable Than Code</h3><p>Each entity config in pgsql_to_adls_incremental.json encodes decisions that would otherwise live inside pipeline code:</p><ul><li>watermark_col: which column tracks changes. If a table uses modified_at instead of last_update, the pipeline does not need to change — only the config.</li><li>primary_key_column: drives the MERGE condition in both ADF&#39;s watermark upsert and Databricks&#39; Delta MERGE.</li><li>active: false: immediately disables ingestion with zero pipeline downtime.</li><li>custom_query: overrides the auto-generated SELECT * for tables needing JOIN pre-computation.</li></ul><p>The rental entity demonstrates this: because payment has no last_update column, its related data is pre-joined at ingestion time using a custom query that reads the watermark from the rental table:</p><pre>{<br>  &quot;entity&quot;: &quot;rental&quot;,<br>  &quot;custom_query&quot;: &quot;SELECT r.rental_id, r.rental_date, r.inventory_id, r.customer_id, r.return_date, r.staff_id, r.last_update, p.payment_id, p.amount, p.payment_date FROM public.rental r LEFT JOIN public.payment p ON r.rental_id = p.rental_id WHERE r.last_update &gt; &#39;{watermark}&#39;&quot;<br>}</pre><p>This is ingestion-layer denormalisation encoded as config, not code.</p><h3>Source Analysis Phase</h3><p>Before writing a single pipeline, the source schema was documented. PostgreSQL’s dvdrental database contains 15 tables with several types that ADF&#39;s Parquet writer cannot serialize natively:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ay9n9Lr_4XqYKrGTbqs6jQ.png" /></figure><p>These decisions are captured in pgsql_to_adls.json as custom_query values per entity — documentation-as-config that travels with the data through every layer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CdvghpRxTkASOIFgD4GJQA.png" /></figure><p>Composite primary keys required explicit handling: film_actor uses [actor_id, film_id], film_category uses [film_id, category_id]. These are stored as comma-separated values in primary_key_column and parsed by both the ADF pipeline (for the ForEach&#39;s merge key parameter) and the Databricks notebook (for the Delta MERGE condition).</p><h3>Azure Data Factory</h3><h3>Pipeline Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_0bzfhOozYHEwqhlFIGvGA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OTPEqE0R1oRlWYXmqE5K2A.png" /></figure><h3>Metadata-Driven Copy Activity</h3><p>The power of the pattern is in the Copy Activity parameters. The child pipeline receives an entire entity config object as a parameter, and uses ADF expressions to drive all dynamic behaviour:</p><pre>Source Query (when custom_query absent):<br>@concat(&#39;SELECT * FROM &quot;&#39;, item().source_schema, &#39;&quot;.&quot;&#39;, item().source_table, &#39;&quot;&#39;,<br>        if(equals(item().load_type, &#39;incremental&#39;),<br>           concat(&#39; WHERE &#39;, item().watermark_col, &#39; &gt; &#39;&#39;&#39;, pipeline().parameters.watermark, &#39;&#39;&#39;&#39;),<br>           &#39;&#39;))<br><br>Sink Path:<br>@concat(item().target_path, &#39;/&#39;, variables(&#39;run_timestamp&#39;), &#39;/&#39;, item().entity, &#39;/&#39;)</pre><p>Adding table inventory_log to this platform means adding one JSON object to the config file:</p><pre>{<br>  &quot;entity&quot;: &quot;inventory_log&quot;,<br>  &quot;unique_name&quot;: &quot;inventory_log_public_inventory_log_incremental&quot;,<br>  &quot;source_schema&quot;: &quot;public&quot;,<br>  &quot;source_table&quot;: &quot;inventory_log&quot;,<br>  &quot;primary_key_column&quot;: &quot;log_id&quot;,<br>  &quot;watermark_col&quot;: &quot;created_at&quot;,<br>  &quot;target_layer&quot;: &quot;bronze&quot;,<br>  &quot;target_container&quot;: &quot;raw&quot;,<br>  &quot;target_path&quot;: &quot;dvdrental/incremental&quot;,<br>  &quot;file_format&quot;: &quot;parquet&quot;,<br>  &quot;load_type&quot;: &quot;incremental&quot;,<br>  &quot;active&quot;: true<br>}</pre><p>No pipeline changes. No dataset changes. No new linked services. The next ADF pipeline run picks it up.</p><h3>Watermark Tracking</h3><p>The incremental pipeline maintains watermarks in a PostgreSQL table created idempotently on each run:</p><pre>-- Created by Script Activity on every orchestrator run<br>CREATE TABLE IF NOT EXISTS dp_pipeline_watermarks (<br>    entity_name    VARCHAR(255) PRIMARY KEY,<br>    last_loaded_at TIMESTAMP NOT NULL DEFAULT &#39;1900-01-01&#39;,<br>    updated_at     TIMESTAMP NOT NULL DEFAULT NOW()<br>);</pre><p>The watermark lifecycle across a single ForEach iteration:</p><ol><li><strong>GetWatermark</strong>: SELECT last_loaded_at FROM dp_pipeline_watermarks WHERE entity_name = &#39;rental&#39;</li><li><strong>Child pipeline runs</strong> with watermark = &#39;2026-06-18 14:30:00&#39;</li><li><strong>Only on child success</strong>: INSERT INTO dp_pipeline_watermarks ... ON CONFLICT DO UPDATE SET last_loaded_at = &#39;2026-06-19 09:00:00&#39;</li></ol><p>If the child pipeline fails, the watermark is <strong>not updated</strong> — the next run re-processes from the last successful checkpoint. This is why the ForEach is sequential: a failed watermark upsert for rental must not block the payment watermark from advancing independently.</p><h3>Databricks Architecture</h3><h3>Three-Job Structure (Databricks Asset Bundles)</h3><p>The Databricks layer is defined entirely in YAML using <strong>Databricks Asset Bundles (DAB)</strong>. Three jobs correspond to the three medallion layers:</p><pre># databricks.yml — bundle root<br>bundle:<br>  name: dp-databricks<br><br>variables:<br>  catalog:<br>    description: &quot;Unity Catalog name for bronze/silver/gold output&quot;<br>  adls_raw_base:<br>    description: &quot;ADLS Gen2 abfss base URI for raw Parquet&quot;<br>    default: &quot;&quot;<br>  warehouse_id:<br>    description: &quot;SQL warehouse ID used by dbt_task&quot;<br>    default: &quot;25b25130cbc20010&quot;<br><br>artifacts:<br>  dp_library:<br>    type: whl<br>    path: .<br>    build: &quot;pip install build &amp;&amp; python -m build --wheel --outdir dist/&quot;<br><br>targets:<br>  dev:<br>    mode: development<br>    variables:<br>      catalog: dp_catalog<br>      adls_raw_base: &quot;abfss://raw@devstdpn001.dfs.core.windows.net&quot;<br>    resources:<br>      jobs:<br>        dvdrental_raw_to_bronze:<br>          schedule:<br>            pause_status: PAUSED   # Safe default: no accidental runs in dev</pre><p>The artifacts.dp_library block tells DAB to build and deploy the src/dp/ Python wheel before job execution. Notebooks import from dp.* via sys.path.insert(0, f&quot;{bundle_files_path}/src&quot;) — no cluster-level library configuration required.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4QFee9jeu4NybT-iiiAPbQ.png" /></figure><p>Bronze Layer: Soft-Delete Handling</p><p>The Bronze layer is not a raw dump — it is an append-optimised CDC approximation. Every Bronze table carries four audit columns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AAS5MWI3bpIg2XduYKW-0A.png" /></figure><p>The delete detection algorithm:</p><pre># src/dp/transforms/bronze.py<br>def detect_deleted_rows(<br>    spark: SparkSession,<br>    new_snapshot_df: DataFrame,<br>    bronze_table: str,<br>    primary_keys: list[str],<br>    run_timestamp: datetime,<br>    keys_snapshot_df: DataFrame | None = None,<br>) -&gt; DataFrame:<br>    &quot;&quot;&quot;Find rows in bronze that are absent from the new full snapshot.&quot;&quot;&quot;<br>    try:<br>        active_bronze = spark.table(bronze_table).filter(&quot;_is_deleted = false&quot;)<br>    except Exception:<br>        # Table does not exist yet — first run, no deletes possible<br>        return spark.createDataFrame([], new_snapshot_df.schema)<br><br>    # Use lightweight PK-only file if available (avoids reading full Parquet twice)<br>    pk_df = (<br>        keys_snapshot_df.select(primary_keys)<br>        if keys_snapshot_df is not None<br>        else new_snapshot_df.select(primary_keys)<br>    )<br><br>    deleted = active_bronze.join(pk_df, on=primary_keys, how=&quot;left_anti&quot;)<br>    if deleted.isEmpty():<br>        return spark.createDataFrame([], new_snapshot_df.schema)<br><br>    ts = F.lit(run_timestamp).cast(&quot;timestamp&quot;)<br>    return (<br>        deleted<br>        .withColumn(&quot;_is_deleted&quot;, F.lit(True))<br>        .withColumn(&quot;_deleted_at&quot;, ts)<br>        .withColumn(&quot;_updated_at&quot;, ts)<br>        .select(new_snapshot_df.columns)<br>    )</pre><p>The keys_snapshot_df optimisation is worth noting: ADF writes a keys_snapshots/ subfolder containing only primary key columns alongside the full Parquet export. The Bronze notebook uses this lightweight file for the LEFT ANTI JOIN rather than scanning the full Parquet data twice — important when tables reach millions of rows.</p><h3>Delta MERGE: Immutable Insert Timestamps</h3><p>The write_delta function implements the key invariant that _inserted_at is never overwritten:</p><pre>-- Generated MERGE statement (simplified)<br>MERGE INTO target<br>USING source ON (target.rental_id = source.rental_id)<br><br>-- Soft-deleted row: mark as deleted<br>WHEN MATCHED AND source._is_deleted = true THEN UPDATE SET<br>  target._is_deleted = source._is_deleted,<br>  target._deleted_at = source._deleted_at,<br>  target._updated_at = source._updated_at<br><br>-- Active row update: everything EXCEPT _inserted_at<br>WHEN MATCHED AND source._is_deleted = false THEN UPDATE SET<br>  target.rental_date   = source.rental_date,<br>  target.return_date   = source.return_date,<br>  -- ... all business columns ...<br>  target._updated_at   = source._updated_at<br>  -- _inserted_at deliberately absent<br><br>-- New row: set _inserted_at once<br>WHEN NOT MATCHED THEN INSERT *</pre><h3>Silver Layer: dbt Incremental Models</h3><p>Silver models clean the bronze data without business aggregation. The generate_schema_name macro override is required for Unity Catalog — without it, dbt prefixes schema names with the target name, producing dev_dvdrental instead of dvdrental:</p><pre>-- dbt/macros/generate_schema_name.sql<br>{% macro generate_schema_name(custom_schema_name, node) -%}<br>  {%- if custom_schema_name is none -%}<br>    {{ target.schema }}<br>  {%- else -%}<br>    {{ custom_schema_name }}<br>  {%- endif -%}<br>{%- endmacro %}</pre><p>The rental model demonstrates how business flags are added at the Silver layer — not in Bronze (which preserves raw source fidelity) and not in Gold (which aggregates):</p><pre>-- dbt/models/silver/silver_rental.sql<br>{{ config(unique_key=&#39;rental_id&#39;) }}<br><br>SELECT<br>    rental_id,<br>    rental_date,<br>    inventory_id,<br>    customer_id,<br>    return_date,  -- NULL = disc still rented; intentionally preserved<br>    staff_id,<br>    last_update,<br>    CASE WHEN return_date IS NULL THEN true ELSE false END AS is_open_rental,<br>    _inserted_at,<br>    _updated_at,<br>    _deleted_at,<br>    _is_deleted<br>FROM {{ source(&#39;bronze&#39;, &#39;bronze_rental&#39;) }}<br>WHERE _is_deleted = false<br>{% if is_incremental() %}<br>  AND _updated_at &gt; (SELECT MAX(_updated_at) FROM {{ this }})<br>{% endif %}</pre><p>The is_open_rental flag is computable from a NULL check on return_date. Making it explicit in Silver means Gold models and Power BI reports never independently re-implement this logic.</p><h3>Gold Layer: Business Aggregates</h3><p>Four Gold models answer the core business questions about a video rental business:</p><pre>-- dbt/models/gold/gold_customer_lifetime_value.sql<br>{{ config(materialized=&#39;table&#39;, tags=[&#39;gold&#39;, &#39;customer_analytics&#39;]) }}<br><br>SELECT<br>    c.customer_id,<br>    c.first_name,<br>    c.last_name,<br>    c.email,<br>    c.store_id,<br>    c.is_active,<br>    c.create_date                       AS customer_since,<br>    COUNT(DISTINCT r.rental_id)         AS total_rentals,<br>    COUNT(r.rental_id)<br>        FILTER (WHERE r.is_open_rental = true) AS open_rentals,<br>    SUM(p.amount)                       AS total_spend,<br>    AVG(p.amount)                       AS avg_payment,<br>    MIN(r.rental_date)                  AS first_rental_date,<br>    MAX(r.rental_date)                  AS last_rental_date<br>FROM {{ ref(&#39;silver_customer&#39;) }}   c<br>LEFT JOIN {{ ref(&#39;silver_rental&#39;) }}  r ON c.customer_id = r.customer_id<br>LEFT JOIN {{ ref(&#39;silver_payment&#39;) }} p ON r.rental_id   = p.rental_id<br>WHERE c._is_deleted = false<br>GROUP BY c.customer_id, c.first_name, c.last_name,<br>         c.email, c.store_id, c.is_active, c.create_date</pre><p>Gold models are materialised as <strong>tables</strong> (not incremental views) because they are the reporting layer — Power BI queries them directly and benefits from pre-computed aggregations rather than live joins across three Silver tables.</p><h3>How to Add a New Entity</h3><p>The following sequence covers adding a hypothetical inventory_log table from zero to Power BI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*foJvHDEVzeI3zoeiL2v23g.png" /></figure><h3>Step 1 — Add Entity Metadata</h3><pre>// adf-pipelines/metadata/ingestion/pgsql_to_adls_incremental.json<br>{<br>  &quot;entity&quot;: &quot;inventory_log&quot;,<br>  &quot;unique_name&quot;: &quot;inventory_log_public_inventory_log_incremental&quot;,<br>  &quot;source_schema&quot;: &quot;public&quot;,<br>  &quot;source_table&quot;: &quot;inventory_log&quot;,<br>  &quot;primary_key_column&quot;: &quot;log_id&quot;,<br>  &quot;watermark_col&quot;: &quot;created_at&quot;,<br>  &quot;target_layer&quot;: &quot;bronze&quot;,<br>  &quot;target_container&quot;: &quot;raw&quot;,<br>  &quot;target_path&quot;: &quot;dvdrental/incremental&quot;,<br>  &quot;file_format&quot;: &quot;parquet&quot;,<br>  &quot;load_type&quot;: &quot;incremental&quot;,<br>  &quot;active&quot;: true<br>}</pre><h3>Step 2 — Source Query (if needed)</h3><p>If the table has incompatible types, add a custom_query. Otherwise, the pipeline auto-generates SELECT * FROM public.inventory_log WHERE created_at &gt; &#39;{watermark}&#39;.</p><h3>Step 3 — Run ADF Ingestion</h3><p>Trigger pl_dvdrental_incremental_pgsql_adls manually or wait for the schedule. The ForEach processes the new entity alongside all others. Parquet files land at raw/dvdrental/incremental/202406191230/inventory_log/*.parquet.</p><h3>Step 4 — Bronze Notebook (optional DQ config)</h3><p>If data quality checks are needed, add expectations to metadata/dq/dvdrental_bronze_expectations.json:</p><pre>{<br>  &quot;entity&quot;: &quot;inventory_log&quot;,<br>  &quot;expectations&quot;: {<br>    &quot;expect_table_row_count_to_be_between&quot;: [0, 500000],<br>    &quot;expect_column_values_to_not_be_null&quot;: [&quot;log_id&quot;, &quot;inventory_id&quot;],<br>    &quot;expect_column_values_to_be_unique&quot;: [&quot;log_id&quot;]<br>  }<br>}</pre><p>The generic notebook reads this config and runs native PySpark checks — no Great Expectations dependency (which is incompatible with Databricks Serverless due to DataFrame.persist() calls).</p><h3>Steps 5–7 — dbt Models</h3><pre>-- dbt/models/silver/silver_inventory_log.sql<br>{{ config(unique_key=&#39;log_id&#39;) }}<br><br>SELECT<br>    log_id,<br>    inventory_id,<br>    action,<br>    created_at,<br>    _inserted_at,<br>    _updated_at,<br>    _is_deleted<br>FROM {{ source(&#39;bronze&#39;, &#39;bronze_inventory_log&#39;) }}<br>WHERE _is_deleted = false<br>{% if is_incremental() %}<br>  AND _updated_at &gt; (SELECT MAX(_updated_at) FROM {{ this }})<br>{% endif %}</pre><h3>Step 8 — Deploy</h3><pre># PR merged → CI passes → deploy stage runs:<br>databricks bundle deploy --target dev<br># DAB picks up the new dbt model automatically — no job YAML changes needed</pre><h3>CI/CD</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HkVqg96rsKrbIJ0BFaXTRg.png" /></figure><h3>Testing Without a Cluster</h3><p>The src/dp/ library was designed with a hard rule: <strong>no </strong>dbutils references. Every function takes spark: SparkSession as an explicit parameter rather than relying on the global spark object injected by the Databricks runtime.</p><p>This means the entire library runs locally with pyspark==3.5.1 and delta-spark==3.2.0 installed in a venv. The 69 unit tests run in under 30 seconds on the CI agent with no Databricks connection:</p><pre># tests/dp/transforms/test_bronze.py<br>@pytest.fixture(scope=&quot;module&quot;)<br>def spark():<br>    return (<br>        SparkSession.builder<br>        .master(&quot;local[2]&quot;)<br>        .config(&quot;spark.sql.extensions&quot;, &quot;io.delta.sql.DeltaSparkSessionExtension&quot;)<br>        .config(&quot;spark.sql.catalog.spark_catalog&quot;, &quot;org.apache.spark.sql.delta.catalog.DeltaCatalog&quot;)<br>        .getOrCreate()<br>    )<br><br>def test_detect_deleted_rows_returns_soft_delete_markers(spark, tmp_path):<br>    bronze_data = [Row(id=1, val=&quot;a&quot;), Row(id=2, val=&quot;b&quot;), Row(id=3, val=&quot;c&quot;)]<br>    new_snapshot = [Row(id=1, val=&quot;a&quot;), Row(id=3, val=&quot;c_updated&quot;)]  # id=2 deleted<br><br>    bronze_df = spark.createDataFrame(bronze_data).withColumn(&quot;_is_deleted&quot;, F.lit(False))<br>    snapshot_df = spark.createDataFrame(new_snapshot).withColumn(&quot;_is_deleted&quot;, F.lit(False))<br><br>    # Write bronze to local Delta path<br>    bronze_path = str(tmp_path / &quot;bronze_test&quot;)<br>    bronze_df.write.format(&quot;delta&quot;).save(bronze_path)<br><br>    deleted = detect_deleted_rows(spark, snapshot_df, bronze_path, [&quot;id&quot;], run_ts)<br><br>    assert deleted.count() == 1<br>    assert deleted.filter(&quot;id = 2&quot;).first()[&quot;_is_deleted&quot;] is True</pre><h3>The Metadata Sync Problem</h3><p>One non-obvious CI/CD constraint: the ADF pipeline’s Lookup Activity reads metadata from ADLS at runtime. If triggers start <strong>before</strong> the metadata sync completes, the first pipeline run reads the old config. The deployment order is enforced in the pipeline YAML:</p><pre># adf-pipelines/azure-pipelines.yml (simplified)<br>- stage: Deploy_Dev<br>  jobs:<br>    - deployment: Deploy<br>      steps:<br>        - task: AzureCLI@2<br>          displayName: &quot;Stop ADF Triggers&quot;<br>          # ...<br>        - task: AzureResourceManagerTemplateDeployment@3<br>          displayName: &quot;Deploy ARM Template&quot;<br>          # ...<br>        - task: AzureCLI@2<br>          displayName: &quot;Sync Metadata to ADLS&quot;<br>          inputs:<br>            inlineScript: |<br>              az storage blob upload-batch \<br>                --account-name devstdpn001 \<br>                --source metadata/ \<br>                --destination raw/metadata/ \<br>                --overwrite true<br>        - task: AzureCLI@2<br>          displayName: &quot;Start ADF Triggers&quot;<br>          # ALWAYS last — metadata must be current before any trigger fires</pre><p>A SHA-keyed archive copy is also uploaded (raw/metadata/archive/{git_sha}/) providing an immutable audit trail of which config was active during each ADF run.</p><h3>Lessons Learned</h3><h3>What Worked</h3><p><strong>Metadata-driven design scales linearly, not multiplicatively.</strong> Adding the 15th table took the same effort as adding the 2nd. Without this pattern, the marginal cost of each table would be a new pipeline, a new dataset, a new notebook, and a new set of tests.</p><p><strong>Managed identity everywhere eliminates an entire class of operational problems.</strong> No rotation schedules, no credential leaks, no “who rotated the storage key without telling anyone” incidents. The ADF MSI permissions were configured once in Terraform and have not been touched since.</p><p><strong>Serverless Databricks eliminates cluster management but surfaces hidden API constraints.</strong> The following issues were discovered sequentially during testing and are worth documenting explicitly:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m8HNTVwkMjOTraoTbOyEQA.png" /></figure><p>Replacing Great Expectations with native PySpark DQ checks removed a dependency that was fundamentally incompatible with serverless compute — and the replacement is simpler, faster, and easier to extend.</p><p><strong>The </strong>arm-template-parameters-definition.json file is a footgun. When this file is present in an ADF repo, it completely replaces ADF Studio&#39;s default parametrisation logic. A single JSON syntax error wipes all linked service parameters from the generated ARM template. The symptom is subtle: the ARM template deploys successfully, but linked services appear unconfigured in the factory. The fix is to validate this file against the ARM schema before every ADF Studio publish.</p><p><strong>DAB CLI v1.x diverges from the documentation in three breaking ways:</strong></p><ol><li>bundle.artifact_path → renamed to workspace.artifact_path</li><li>Relative paths in notebook_path must start from the job YAML&#39;s directory</li><li>outcome: FAILED for conditional tasks → renamed to run_if: AT_LEAST_ONE_FAILED</li></ol><p>All three caused silent pipeline failures — tasks appeared to succeed but the intended logic did not execute.</p><h3>What Should Be Improved</h3><p><strong>The metadata sync is a deployment dependency, not a data dependency.</strong> The current design syncs metadata to ADLS as part of the ADF CI/CD pipeline. A cleaner approach would read metadata directly from the Git repository via a configuration API, eliminating the ADLS sync step entirely.</p><p><strong>Delete detection is full-scan by default.</strong> The Bronze notebook uses LEFT ANTI JOIN between active Bronze rows and the incoming snapshot. For tables with millions of rows, this is expensive. The keys_snapshot_df optimisation (PK-only files) partially addresses this, but a proper CDC mechanism (Debezium + Event Hub) would be more efficient. This was the original architecture — it was abandoned due to Azure VM quota limits on an MSDN subscription — and should be revisited on a production subscription.</p><p><strong>dbt tests are not yet blocking.</strong> The dbt test command runs after each dbt run in the Databricks job, but test failures currently only send notifications rather than blocking downstream jobs. The Gold job should depend on Silver test success, not just Silver completion.</p><p><strong>There is no data contract between ADF and Bronze.</strong> If a source table adds a new column, ADF copies it silently, but the Bronze table schema does not evolve until the next write_delta with overwriteSchema=true. Adding JSON schema validation on the Parquet files before Bronze ingest would catch breaking schema changes before they propagate.</p><blockquote><strong><em>Callout: The Architecture That Never Ran</em></strong></blockquote><blockquote><em>The original design used Debezium running in Azure Container Instances, streaming CDC events from PostgreSQL logical replication into Event Hub, then into Databricks via Spark Structured Streaming. This architecture appears in </em><em>devlog_3.md and the subnet layout still reserves </em><em>snet-aci and </em><em>snet-postgres for it.</em></blockquote><blockquote><em>It was abandoned on day 4 when an MSDN free trial subscription hit a hard quota of 0 total VMs. App Service Plans consume VMs even on the Y1 consumption tier. Azure Functions, Logic Apps, and Container Instances all require VM quota. The batch ADF architecture was built instead, and the platform shipped.</em></blockquote><blockquote><em>The lesson: design for infrastructure constraints before committing to streaming architectures. Not every data platform needs sub-second latency.</em></blockquote><p><em>Platform built with Terraform 1.15.6, Azure Data Factory (ARM v2), Databricks Runtime 15.x Serverless, dbt-databricks 1.8, PySpark 3.5.1, Delta Lake 3.2.0.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=71b28e3dba3d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building an Azure Data Platform in One Week with Claude Code]]></title>
            <link>https://medium.com/@alexeyban/building-an-azure-data-platform-in-one-week-with-claude-code-d9ded6d80bdd?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9ded6d80bdd</guid>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[microsoft-azure]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Sat, 20 Jun 2026 19:21:00 GMT</pubDate>
            <atom:updated>2026-06-21T13:37:09.176Z</atom:updated>
            <content:encoded><![CDATA[<p><em>An engineering case study, not a product pitch: what an AI pair programmer actually did, and didn’t do, on a real three-repo data platform build.</em></p><h3>About me</h3><p>I’ve spent the last twenty years building data platforms: SQL Server warehouses, Greenplum and Vertica MPP clusters, and, more recently, Azure and Databricks lakehouses.</p><p>I’ve written the pipelines myself, and I’ve reviewed other people’s pipelines as an architect. After doing that for two decades, I’ve become less interested in typing code and more interested in building systems.</p><p>Experience teaches a few things. Most systems evolve; very few are designed perfectly from the start. Business rewards value, not elegance. A good solution delivered today is usually worth more than a perfect one delivered six months later.</p><p>And while I enjoy architecture and solving problems, I’ve never been particularly attached to writing boilerplate code. Terraform modules, YAML files, configuration files, tests, documentation — necessary, yes, but not the reason I became an engineer.</p><p><em>What I enjoy is seeing systems come alive.</em></p><p><em>Which made AI coding agents interesting to me.</em></p><p><em>Not because I wanted to replace engineers.</em></p><p><em>But because I wanted to spend less time typing and more time thinking.</em></p><p><em>That’s what this experiment was really about.</em></p><h3>Motivation</h3><p>The premise above only matters if the project behind it was real, so let’s start there.</p><p>The AI-replaces-engineers conversation has been loud for two years, and almost every example I’d seen answering it was a toy: a CRUD app, a to-do list, a weekend script. None of it addressed the question specific to my field — can an AI coding agent help bootstrap a <em>real</em> data platform, with Terraform-provisioned infrastructure, a managed orchestration layer, a lakehouse, a transformation layer, CI/CD, and documentation?</p><p>So, I built one. I picked a small but genuinely relational source — the dvdrental PostgreSQL sample database, 15 tables, 18 foreign keys — and built a complete, three-layer Azure data platform around it, with Claude Code as my implementation partner, on a free-tier Azure subscription, working alone.</p><p>The subscription is what made this interesting, and it’s worth stating plainly because it shaped every architectural decision that followed: an MSDN/Visual Studio Azure subscription comes with a hard quota of <strong>zero virtual machines, across every VM family.</strong> Not a soft limit. Not a per-family cap you can request around. Zero. Nobody tells you this in a tutorial. You find out when terraform apply fails midway through a deployment you were certain would work.</p><h3>Project scope</h3><p>That quota constraint is the thread running through the rest of this article, but the platform it constrained is worth describing on its own terms first.</p><p>The build spans three separate Azure DevOps repositories, split by concern rather than by convenience:</p><ol><li><strong>azure-data-project</strong> — Terraform. Provisions everything: Azure Data Factory, ADLS Gen2, Key Vault, the Databricks workspace, PostgreSQL Flexible Server, networking, RBAC, monitoring. Fifteen modules, 119 resource blocks, roughly 51 unique Azure resource types.</li><li><strong>adf-pipelines</strong> — the Azure Data Factory artifacts: pipelines, datasets, linked services, and the metadata JSON files that drive ingestion. Five pipelines, four linked services, and two metadata files — one for full loads, one for incremental — each covering the same 15 source tables.</li><li><strong>azure-databricks-project</strong> — the lakehouse: a small PySpark library, Databricks notebooks, a dbt project (15 Silver models, 4 Gold models), and Databricks Asset Bundle job definitions.</li></ol><p>The data flow is a standard medallion lakehouse, deliberately unoriginal by design:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gt2Yu1TlmhkOaGUmDb6GCQ.png" /></figure><p>What that diagram doesn’t show is just as important to the story: a fully built real-time CDC pipeline — PostgreSQL logical replication into Debezium, streamed through Event Hub, captured to ADLS — exists in the Terraform modules today, tested, and entirely disabled. I’ll explain why two sections from now, because the reason is the same one behind half the decisions in this build.</p><p>The active implementation ran about six days. The commit history splits unevenly across the three repos — about 90% of commits in the infrastructure repo carry a Claude co-authorship line, about 80% in the Databricks repo, and only about 51% in adf-pipelines. That last number isn&#39;t a fluke; it&#39;s a preview of the section called &quot;What went wrong.&quot;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jHIMliUXYdgOJ0Zvo_HKtg.png" /></figure><h3>Architecture</h3><p>With the scope in view, the decisions behind it are easier to defend or criticize on their own merits — and a few of them I’d defend in a design review without hedging.</p><p><strong>Infrastructure-as-code, no portal clicks.</strong> Every resource is Terraform. The handful of places where a manual step was genuinely unavoidable — looking up an auto-provisioned Unity Catalog metastore ID, generating a Databricks personal access token — were followed by terraform import to bring the resource back under management rather than left as drift.</p><p><strong>Managed identity where the platform supports it, password or token where it doesn’t yet — and the gap is documented, not hidden.</strong> Azure Data Factory authenticates to Key Vault and ADLS using its system-assigned identity. PostgreSQL authentication moved from a Key-Vault-stored password to Entra-based managed identity via pgaadauth_create_principal_with_oid — a function with no Terraform equivalent, run by hand after every new environment. Databricks is the honest exception: its linked service in ADF still uses a personal access token, because the workspace had Entra authentication disabled at the time this was built, and switching it was deferred rather than blocked on.</p><p><strong>Secure by default, exceptions documented, not silent.</strong> public_network_access_enabled = false everywhere except dev, where the self-hosted CI agent needs a path in over the public internet. Key Vault runs in RBAC mode. Every checkov security-scanner suppression — fifteen of them — carries a one-line justification in version control, readable by anyone, not buried in a portal exception list.</p><p><strong>Metadata-driven pipelines, not per-table pipelines.</strong> This is the decision I’d point to first if asked what worked. Both the ADF layer and the Databricks layer converged — independently, built weeks apart by the same two-person system of architect and AI — on the identical pattern: one generic pipeline or notebook, driven by a JSON config file with one entry per entity. Adding a sixteenth table to the platform means adding one JSON object and opening a pull request. No pipeline code, no notebook code, changes.</p><p><strong>The quota wall reshaped everything downstream of it.</strong> The original design used PostgreSQL logical replication, Debezium running on Azure Container Instances, and Event Hub as a Kafka-compatible bus, landing CDC events in ADLS via Event Hub Capture. All of it got built. All of it hit the zero-VM wall in sequence: Container Instances need a VM; the Azure Functions DML simulator needs one too, on both the Consumption and the newer Flex Consumption plan; and even Databricks Unity Catalog operations — metastore assignment, external locations — silently provision an internal cluster unless you specifically avoid it. The fix that actually worked was a <strong>serverless SQL warehouse</strong>, which runs on Databricks-managed compute and never touches the Azure VM quota. The CDC stack wasn’t deleted when it was disabled — it’s still in the Terraform modules, ready to be re-enabled the moment the subscription allows it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xE6s37fAHT1-vSkgtyuBLg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pKHOrb3j2M8ocpS-JllBJw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8stYgm-3hlv2asrf2NuH_A.png" /></figure><h3>Documentation first, metadata first: the source analysis phase</h3><p>Before any infrastructure or pipelines existed, I wanted to answer a question that most data projects postpone until it’s already painful:</p><p>Do we actually understand the source system?</p><p>In my experience, this phase consumes more time than writing pipelines. Schemas are incomplete, documentation is outdated, and the columns that look authoritative often aren’t. Most teams discover those problems one failed Copy activity at a time.</p><p>I wanted to invert that process.</p><p>Instead of building pipelines first and learning later, I wanted to understand first and build later.</p><p>Before writing a single ADF pipeline, I had Claude build a small inspection tool for the source PostgreSQL database. Its purpose wasn’t to move data. It was to understand data.</p><p>The tool walked information_schema, identified PostgreSQL types that Azure Data Factory cannot write to Parquet without additional handling, mapped all foreign-key relationships, and generated two complementary artifacts.</p><p>The first was written for humans: markdown documentation describing not only tables and columns but also the kind of knowledge that rarely appears in ER diagrams. Which columns were truly authoritative. Which strings needed trimming. Which tables were reference data. Which nulls carried business meaning rather than missing values.</p><p>The second artifact was written for machines: a JSON schema registry containing the same knowledge in a format that could be consumed by code.</p><p>That registry later became the foundation for both ADF and Databricks. The metadata-driven architecture described earlier — generic pipelines driven by configuration files instead of one pipeline per table — was only possible because the metadata existed first.</p><p>The principle itself wasn’t new. I’ve encountered versions of it throughout my career: documentation first, metadata first, understand the source before writing code.</p><p>What’s changed is the economics.</p><p>Traditionally, creating and maintaining this kind of metadata required enough manual effort that many projects simply skipped it. AI didn’t invent metadata-driven engineering. It made it cheap enough that there was little reason not to do it.</p><p>What previously required days of analysis and documentation could now be produced in hours, reviewed, corrected, and evolved continuously.</p><p>And that’s probably the most reusable idea I’ll take from this project.</p><p><em>Not the scripts.</em></p><p><em>Not the pipelines.</em></p><p><em>The process.</em></p><p><em>Understand first.</em></p><p><em>Generate metadata second.</em></p><p><em>Only then start writing pipelines.</em></p><p>That doesn’t mean static analysis replaces execution. It doesn’t.</p><p>One of the fifteen tables, payment, lacked the watermark column I expected. Nothing in the schema inspection flagged it. The issue only surfaced when the incremental pipeline actually ran and failed with:</p><pre>42703: column &quot;last_update&quot; does not exist</pre><p>Static analysis tells you the shape of the data.</p><p>Running systems reveal its behavior.</p><p>What I believe generalizes beyond this project isn’t the inspection script itself, but treating source-system analysis as a first-class phase with its own deliverables.</p><p>On the next project, against a source I’ve never seen before, I wouldn’t start by saying:</p><blockquote><em>Build me a pipeline.</em></blockquote><p>I’d start with:</p><blockquote><em>Understand this system.</em></blockquote><blockquote><em>Map its relationships.</em></blockquote><blockquote><em>Identify incompatible types.</em></blockquote><blockquote><em>Generate documentation for humans and metadata for machines.</em></blockquote><p>Only then would I start building.</p><p>And perhaps the most interesting part is that this capability doesn’t have to remain a one-time script.</p><p>The same analysis can be rerun later to detect schema drift. Compare the current schema with the stored registry. Identify new columns, changed data types, or broken relationships. Let AI propose updates automatically when the changes are low-risk, and ask for human review when they affect keys, watermarks, or downstream assumptions.</p><p>That’s the same division of labor that emerged throughout this project.</p><p>AI notices.</p><p>Humans decide.</p><p>And I suspect that pattern will outlive most of the code generated during this experiment.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*N08LZkoanIZPoCMmAL8WpQ.png" /></figure><h3>How I Worked with Claude Code</h3><p>The architecture above didn’t get written by hand. It’s worth describing the actual mechanics of how that division of labor worked day to day, because the answer isn’t “I described it and Claude built it” — that’s true but it skips the part that made multi-day work possible at all.</p><p>The loop looked like this:</p><pre>I decide what needs to exist and why<br>    → I describe it to Claude Code, with constraints<br>        → Claude generates the implementation<br>            → I review, run it, and report back what broke<br>                → Claude fixes, and writes a devlog entry<br>                    → Pull request, merge, deploy</pre><p>The devlog step is the one I’d insist on if I were advising anyone trying this. Every repository has a CLAUDE.md file with an explicit instruction: after any session with significant changes, generate a new devlog_N.md at the repo root, without being asked. Each devlog follows a fixed template — summary, what was built, problem and root cause, decisions made, &quot;knowledge captured,&quot; files changed.</p><p>This solved a problem that showed up almost immediately. A coding session has a context window; a multi-day project doesn’t. Three days in, asking Claude to “add a table to the incremental pipeline” without the devlogs in context risked re-introducing bugs already fixed once — the resource-group typo pointing at an empty rg-dp-dev-001 instead of the actual rg-dp-dev-n001, the coalesce() call that crashes on an absent JSON key rather than a null one, the Key Vault role assignment that grants management access but not the data-plane access actually needed to read a secret. With the devlogs present, those fixes were retrievable as project memory instead of buried in commit history nobody re-reads. By the end of the build there were seventeen devlogs across the three repos — more reusable, frankly, than most of the dbt models, because they generalize to the next Azure project and not just this one.</p><p>What I didn’t delegate is just as important as what I did. Architecture decisions — which repo owns which resource, metadata-driven versus per-table pipelines, when to stop fighting the CDC stack and disable it — were made before I opened an editor. Claude wrote the Terraform HCL, the ADF JSON, the Python, the dbt SQL, and most of the CI/CD YAML implementing those decisions. I reviewed pull requests, ran the pipelines, and turned error messages into the next instruction. That last activity — debugging a stack trace into a fix — was the single most repeated thing either of us did.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qWAUXoR2xcjULKWJo01LQQ.png" /></figure><h3>What surprised me</h3><p>Some of what came out of this loop was genuinely good, and a case study that only catalogs failures would be its own kind of dishonest.</p><p><strong>Boilerplate generation was complete, not partial.</strong> Asking for a new Terraform module didn’t produce a half-finished resource block — it produced the resource, variables with descriptions, outputs with descriptions, a provider-version constraint file, diagnostic settings wiring to Log Analytics, and a README, in one pass. Work that used to take a focused day per module compressed to review time.</p><p><strong>It handled genuinely awkward source data correctly, without my hitting the failure first.</strong> The dvdrental schema has columns that don&#39;t survive a naive copy: film.rating is a PostgreSQL enum that Azure Data Factory can&#39;t write to Parquet without a ::text cast; film.special_features is a text array that needs array_to_string() or it lands as a raw {item1,item2} literal; film.fulltext is a tsvector that fails the copy activity outright unless cast to text, with no design-time warning; staff.picture is a bytea column that has to be excluded from the select list entirely. Claude produced correct custom queries for all four on the first or second attempt — the kind of platform-specific trivia you&#39;d otherwise learn from a stack trace at 11pm.</p><p><strong>It reasoned correctly about identity chains, including the unintuitive ones.</strong> Given “ADF needs to read this Key Vault secret,” it consistently produced the right managed-identity role assignment instead of reaching for a connection string — including the PostgreSQL linked service case, where the obvious Terraform resource doesn’t support Key-Vault-backed passwords in the provider version we were pinned to, and the actual fix requires a different, less obvious resource type entirely.</p><p><strong>Where it fell short, consistently, was anything Azure decides for you and documents nowhere.</strong> It didn’t know that Azure auto-provisions one Unity Catalog metastore per region per account before you ask for one, so the Terraform tried to create a second metastore and failed with a limit error that gives no hint about the auto-provisioned one sitting unused. It didn’t know that a Databricks linked service’s “serverless cluster” setting gets silently dropped by ADF Studio the moment you click Publish. It didn’t know that adding a count argument to an already-deployed Terraform resource changes that resource&#39;s state address and breaks a targeted apply referencing the old one. None of these are reasoning failures. They&#39;re facts about specific platform behavior that live in nobody&#39;s documentation, so there was nothing to have learned them from. Finding them was my job every time, and it always started with reading an error message neither of us could have predicted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ii5sRjsBgBKwRKKrgAwAyg.png" /></figure><h3>What went wrong</h3><p>The previous section earns this one. Four failures are worth telling in detail, because each one changed how the project was actually built, not just what got fixed.</p><p><strong>The Databricks transformation layer first ran dbt the wrong way, and it worked.</strong> That’s the uncomfortable part. The first implementation of the Silver and Gold layers ran dbt run from inside a Databricks notebook via Python&#39;s subprocess, fetching a SQL warehouse connection string and a personal access token from Key Vault at runtime. It worked, until a clean environment threw SecretDoesNotExist on a secret-scope binding that should have resolved and didn&#39;t. The fix wasn&#39;t to chase the binding. It was to notice that Databricks has a native dbt_task job type that injects workspace credentials automatically and removes the Key Vault dependency from this code path entirely. Migrating to it took four small pull requests, each one surfacing the next problem in sequence — a project path that resolves relative to the job&#39;s YAML file rather than the bundle root, a missing compute environment spec, the dbt binary itself not being bundled by default. The lesson wasn&#39;t &quot;the bug is fixed.&quot; It was &quot;a working answer can still be the wrong design, and the platform sometimes has a better one already built in.&quot;</p><p><strong>Five separate failures stood between the first commit and the first successful ADF deployment.</strong> In order: the deploy pipeline referenced a resource group, rg-dp-dev-001, that existed but was empty — the actual factory lived in rg-dp-dev-n001, and Azure&#39;s &quot;resource not found&quot; error looks identical either way. Next, the ARM template path assumed it lived at the repository root; ADF Studio actually nests it under a subfolder named after the factory itself. Next, a PowerShell-based pipeline step failed because the self-hosted Linux agent has no PowerShell Core installed by default — rewritten in az cli and bash. Next, a git fetch inside the same pipeline run failed because Azure Pipelines wipes checkout credentials right after the initial clone unless told not to. Last, and strangest: a trigger change in the pipeline YAML was silently ignored for an entire run, because the pipeline had been registered through the Azure DevOps UI with an override flag that makes UI trigger settings win over YAML, with no warning that the override existed. None of these are exotic. Each is the kind of friction that only shows up at actual deploy time, and an AI pair programmer working from documentation has no more advance warning of it than a human does.</p><p><strong>A single malformed configuration file deleted every environment-specific parameter in the ADF deployment, silently.</strong> ADF Studio publishes ARM templates alongside a file, arm-template-parameters-definition.json, that controls which linked-service fields become deployment parameters. We wrote &quot;domain&quot;: { &quot;value&quot;: &quot;=&quot; }, where the correct syntax is the plain string &quot;domain&quot;: &quot;=&quot;. ADF Studio doesn&#39;t validate this file and reject it — it parses the malformed entry, decides the file is authoritative, and replaces its own default parametrization with nothing. The next Publish produced an ARM template with exactly one parameter: the factory name. Every other linked service — Key Vault, ADLS, PostgreSQL — lost its environment-specific value in one click, with no error at publish time and no error until the next deployment failed against three environments at once. Recovering took four pull requests: delete the broken file, strip the parameter files down to match the now-empty template, deploy, re-Publish to let ADF Studio regenerate its defaults, then add everything back. This is the one failure I&#39;d call a real tooling design flaw rather than a configuration mistake — a file with no validation and a blast radius that touches every environment at once.</p><p><strong>Provider version skew produced the same shape of bug three times, in three different services.</strong> Terraform’s azurerm provider renamed Azure Data Factory&#39;s public-network-access attribute between major versions, and using the old name doesn&#39;t raise an error — it silently does nothing, so a security scanner keeps flagging a setting that looks, from the code, already fixed. The same shape of bug showed up with Event Hub&#39;s kafka_enabled attribute, removed entirely in a later provider version, and with a storage container resource expecting an attribute name that had been renamed elsewhere in the docs we were reading. None of these are AI-specific mistakes — anyone writing Terraform against a provider that&#39;s still actively changing makes them. The pattern is worth naming precisely because it isn&#39;t about AI at all: it&#39;s about how silently a renamed attribute fails in this ecosystem, and how little signal you get when it does.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0sRQHkFlF8ORUzyp3cFUtA.png" /></figure><h3>Metrics</h3><p>The story above is more convincing with numbers attached, and it’s worth being upfront that our own documentation doesn’t fully agree on what those numbers are. Two internal tracking snapshots cover slightly different windows — a six-day active-build period and a 37-day calendar span that includes documentation and iteration — and they disagree by single-digit percentages on totals like commit count and pull requests. The table below uses the more granular, session-level snapshot, which logs actual Claude Code session counts and tool-call totals rather than an estimate:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/786/1*XS2pMTqih1lg-sh5-ln4Ng.png" /></figure><p>The per-repo authorship split is the number I’d put in a headline before the overall 83%, because it tells you where the human time actually went: not into the infrastructure that mostly worked on the first or second attempt, but into the ADF repo that absorbed the five-failure deployment sequence and the ARM-parameter incident described above.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1EjMYJW1I_ZWRfxL6R300A.png" /></figure><h3>Lessons learned</h3><p>Putting the failures and the successes side by side changes what I’d actually generalize from this, compared to what I expected going in.</p><p><strong>Working beats elegant, but only once “working” has been verified, not assumed.</strong> The subprocess-based dbt implementation wasn’t elegant, and it ran — right up until a clean environment exposed the assumption it depended on. Replacing it with the native dbt_task wasn&#39;t a pursuit of elegance for its own sake; it was correct because the platform had a strictly better option once we&#39;d seen where the first one broke. Shipping first and improving on contact with reality worked here. Shipping and never coming back to it would not have.</p><p><strong>Architecture decided more outcomes than implementation did.</strong> The single highest-leverage decision in this project was accepting, early, that real-time CDC was incompatible with a zero-VM subscription, rather than spending days trying to force Container Instances into a quota that would never grant them. The second was committing to metadata-driven pipelines on day one, before a single table was ingested — the one decision that survived every later architecture change untouched, including the raw-source format swap and the dbt execution model swap.</p><p><strong>Devlogs functioned as memory for the tool in the same way documentation has always functioned as memory for a team — and that’s a narrower, more mechanical claim than it might sound.</strong> They didn’t prevent every repeated mistake; the Key Vault role-assignment gap, for instance, surfaced more than once across different services before it was named clearly enough to stop recurring. What they did reliably do was make a previously-fixed bug <em>retrievable</em> in the next session instead of buried in commit history nobody re-reads — which is a real, bounded benefit, not a guarantee against context loss.</p><p><strong>An AI pair programmer is a multiplier on judgment you’ve already exercised, not a source of it.</strong> Claude did not decide to abandon CDC, and it did not decide that metadata-driven pipelines were worth the upfront design cost over per-table pipelines that would have shipped marginally faster on day one. What it did well, repeatedly, was take a decision already made and produce a complete, conventionally structured, mostly correct implementation of it fast enough that the next decision — not the current syntax — became the bottleneck.</p><p><strong>Most systems evolve; very few are designed correctly on the first attempt, and this one is no exception.</strong> The raw data source for the Databricks layer changed from a planned Unity Catalog external catalog to direct ADLS Parquet reads, after the first integration attempt revealed the original design didn’t match what ADF actually produced on disk. That’s not a planning failure. That’s what planning against a system you haven’t finished building always looks like, with or without an AI agent involved in writing it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cIIB7DW5svTlCsMJUc0FgA.png" /></figure><h3>Conclusions</h3><p>I started this experiment with a simple question:</p><p>Could one architect, with the help of an AI coding agent, bootstrap a real Azure data platform?</p><p><em>The answer turned out to be yes — with caveats.</em></p><p><em>Claude Code didn’t replace a DevOps engineer.</em></p><p><em>It didn’t replace a Senior Data Engineer.</em></p><p><em>It certainly didn’t replace architecture.</em></p><p>What it did was compress a surprising amount of implementation work into review time.</p><p>Terraform modules, ADF artifacts, Databricks notebooks, dbt models, CI/CD pipelines, documentation — all of it got built faster than I would have thought possible a year ago. Not because the AI made better decisions, but because it let me spend less time producing code and more time deciding what should exist.</p><p>That distinction matters.</p><p>Because after twenty years in data engineering, I’ve realized that typing code was never the part of the job I valued most.</p><p>What I enjoy is understanding systems, making trade-offs, and turning ideas into something useful.</p><p><strong>Business doesn’t reward perfect architectures.</strong></p><p><strong>It rewards working systems.</strong></p><p>And perhaps the biggest lesson from this project is that AI changes the economics of getting to that first working system.</p><p><strong>Not every decision was correct.</strong></p><p><strong>Not every generated solution was elegant.</strong></p><p><strong>Some parts had to be redesigned.</strong></p><p><strong>Some had to be disabled.</strong></p><p><strong>Many required human review.</strong></p><p>But “good enough today” often creates more value than “perfect six months from now.”</p><p>And that’s probably the biggest shift I took away from this experiment.</p><p><strong>AI didn’t make architecture less important.</strong></p><p><strong>If anything, it made architecture more important.</strong></p><p>Because when implementation becomes cheaper, decisions become more valuable.</p><p>More importantly, this experiment gave me something I didn’t have before: confidence.</p><p><em>I now understand how this style of development works in practice.</em></p><p><em>I understand where AI helps, where it struggles, and where human judgment is still essential.</em></p><p><em>The platform itself is only a prototype, but the process behind it is reusable.</em></p><p>I can take the same approach, adapt it to different source systems, different requirements, and real business problems, and start from a much stronger position than a blank repository.</p><p><strong>That’s what makes this more than a one-week experiment.</strong></p><p><strong>For me, it validated a new way of building data platforms.</strong></p><p><strong>I don’t believe this is the end of data engineering.</strong></p><p><strong>Quite the opposite.</strong></p><p>I believe we’re entering a new era of data engineering, where implementation becomes increasingly automated, while architecture, domain knowledge, and engineering judgment become even more valuable.</p><p><em>The code can always be rewritten.</em></p><p><em>The hard part has never been typing.</em></p><p><em>The hard part has always been deciding what to build.</em></p><p>If you’re exploring AI-assisted data engineering or building similar platforms, I’d love to compare notes and learn how others are approaching the same problems.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/913/1*xlFYiSjpy1Lz44ove9IB0w.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9ded6d80bdd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Inside Spark’s Catalyst Optimizer: How Your Lazy SQL Becomes Fast Code]]></title>
            <link>https://medium.com/@alexeyban/inside-sparks-catalyst-optimizer-how-your-lazy-sql-becomes-fast-code-adc4eccd8121?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/adc4eccd8121</guid>
            <category><![CDATA[catalyst-optimizer-spark]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Fri, 12 Jun 2026 19:02:38 GMT</pubDate>
            <atom:updated>2026-06-12T19:02:38.980Z</atom:updated>
            <content:encoded><![CDATA[<p><em>You write a five-line query. Spark turns it into something dramatically faster than what you literally asked for. Here’s the machinery that makes it happen — explained step by step, with no PhD required.</em></p><p>Every time you run a SQL query or a DataFrame operation in Apache Spark, something remarkable happens before a single byte of data is read: your query is parsed, analyzed, rewritten, costed, compiled — and sometimes even re-planned <em>while it’s already running</em>.</p><p>The engine behind all of this is the <strong>Catalyst Optimizer</strong>, and it’s the main reason Spark can execute complex queries efficiently without you hand-tuning every join and filter.</p><p>Think of Catalyst as a GPS for your data. You tell it the destination (“give me total order amounts per customer”). You don’t tell it the route. Catalyst looks at the map (your tables and their metadata), checks the traffic (statistics about your data), and picks the fastest path — sometimes rerouting mid-journey.</p><p>In this article we’ll walk through the whole journey, from raw SQL text to optimized JVM bytecode:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FqzfRhcpOyCD_t8xUGE9WQ.png" /></figure><p>Let’s take this query as our running example:</p><pre>SELECT c.customer_id,<br>       SUM(o.amount)<br>FROM customers c<br>JOIN orders o<br>  ON c.customer_id = o.customer_id<br>WHERE o.amount &gt; 100<br>GROUP BY c.customer_id</pre><h3>Stage 1: Parsing — “What did you just say?”</h3><p>First, Spark needs to understand the <em>grammar</em> of your query. The SQL text is parsed into an <strong>Abstract Syntax Tree (AST)</strong> — a tree of operations that mirrors the structure of your statement:</p><pre>Project (customer_id, SUM(amount))<br> └─ Aggregate (GROUP BY customer_id)<br>     └─ Filter (amount &gt; 100)<br>         └─ Join (customer_id = customer_id)<br>             ├─ customers<br>             └─ orders</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zgQQ7Pyye488sbUFspwRaQ.png" /></figure><p>At this point Spark understands the <em>syntax</em> — and nothing else. It doesn’t yet know:</p><ul><li>whether the tables customers and orders actually exist,</li><li>what data types the columns have,</li><li>how the data is partitioned,</li><li>anything about table sizes or statistics.</li></ul><p>That’s why this output is called the <strong>Unresolved Logical Plan</strong>. It’s a sentence that’s grammatically correct, but whose words haven’t been looked up in a dictionary yet.</p><p>If you write DataFrame code instead of SQL, you skip the text-parsing step — the DataFrame API builds the same logical plan directly. From here on, SQL and DataFrames travel through <em>exactly</em> the same pipeline. (This is also why there is essentially no performance difference between well-written SQL and DataFrame code in Spark.)</p><h3>Stage 2: Analysis — looking everything up</h3><p>Now Catalyst consults the <strong>Catalog</strong> (and the metastore behind it — Hive Metastore, Unity Catalog, Glue, etc.) and resolves every name in the plan:</p><p>In your query After analysis customers HiveTableRelation(customers) — a real, located table customer_id AttributeReference(customer_id, LongType) — a typed column SUM(amount) AggregateExpression(Sum(amount)) — a resolved function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ucWJfPpJ3Pg18l-UOPqyQ.png" /></figure><p>If you’ve ever seen the famous AnalysisException: cannot resolve column... — this is the exact stage where it&#39;s thrown.</p><p>The output is the <strong>Analyzed Logical Plan</strong>: Spark now fully understands the <em>semantics</em> of your query. Every table is located, every column is typed, every function is bound.</p><p>Now the fun begins.</p><h3>Stage 3: Rule-Based Optimization — rewriting your query into a better one</h3><p>This is the heart of Catalyst. Spark applies <strong>hundreds of deterministic rewrite rules</strong> to the logical plan. Each rule is essentially a pattern match:</p><pre>// conceptually:<br>if (plan matches some pattern)<br>  transform(plan)</pre><p>No statistics needed, no guessing — these are rewrites that are <em>always</em> a good idea. Let’s look at the most important ones.</p><h3>Predicate Pushdown — filter early, not late</h3><p>Your plan says: “read orders, <em>then</em> keep rows with amount &gt; 100.&quot;</p><p>Catalyst rewrites it to: “tell the data source to return only rows with amount &gt; 100.&quot;</p><pre>Before:  Filter(amount &gt; 100)          After:  Scan(orders,<br>            └─ Scan(orders)                       pushedFilter = amount &gt; 100)</pre><p>With formats like Parquet and Delta Lake, the filter travels all the way down to the file reader, which can skip entire row groups or files using min/max statistics. Less I/O, fewer rows ever loaded into memory.</p><h3>Column Pruning — read only what you asked for</h3><p>Suppose orders has 40 columns, but your query only touches customer_id and amount. Catalyst trims the scan down to exactly those two columns.</p><p>This is where columnar formats (Parquet, ORC, Delta) shine: skipping a column means literally not reading those bytes from disk.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lN4Pv1EPrvOpWZEYQYyTVQ.png" /></figure><h3>Constant Folding — do the math once</h3><pre>SELECT price * (2 + 3)   -- becomes:   SELECT price * 5</pre><p>Why compute 2 + 3 a billion times — once per row — when you can compute it once at planning time?</p><h3>Boolean Simplification</h3><pre>WHERE x &gt; 5 AND TRUE     -- becomes:   WHERE x &gt; 5</pre><p>Looks trivial, but such expressions appear constantly in <em>generated</em> SQL — from BI tools, ORMs, and templated queries.</p><h3>Predicate Reordering — cheap checks first</h3><pre>WHERE expensive_udf(col) AND country = &#39;US&#39;</pre><p>Spark can evaluate country = &#39;US&#39; first, so the expensive UDF only runs on the rows that survive. (One caveat: Spark treats UDFs as black boxes and is conservative with non-deterministic ones — another reason to prefer built-in functions over UDFs whenever possible.)</p><h3>Null Propagation</h3><pre>NULL + col               -- becomes:   NULL</pre><p>No need to evaluate an expression whose result is known in advance.</p><h3>How rules are applied: batches and fixed points</h3><p>Catalyst doesn’t run rules once. It groups them into <strong>batches</strong> and runs each batch repeatedly until the plan stops changing — until a <strong>fixed point</strong> is reached:</p><pre>Batch &quot;Operator Optimization&quot;:<br>  ConstantFolding, BooleanSimplification,<br>  PredicatePushdown, ColumnPruning, ...</pre><pre>repeat batch until: plan(n+1) == plan(n)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1O65yE0-oj1UyO87LEcDng.png" /></figure><p>Why iterate? Because rules unlock each other. Folding a constant may turn a complex predicate into a simple one, which can then be pushed down, which may make a column prunable. One pass is never enough; the loop guarantees nothing is left on the table.</p><p>The result of this stage is the <strong>Optimized Logical Plan</strong> — your query, rewritten into an equivalent but leaner form.</p><h3>Stage 4: Cost-Based Optimization — choosing the cheapest strategy</h3><p>Rule-based optimization improves the <em>structure</em> of a query. But some decisions can’t be made on structure alone. Consider:</p><ul><li>In what order should three tables be joined?</li><li>Is a table small enough to broadcast to every executor?</li></ul><p>These decisions depend on <strong>how big the data actually is</strong>. Enter <strong>Cost-Based Optimization (CBO)</strong>.</p><h3>The statistics behind it</h3><p>CBO feeds on statistics:</p><pre>row counts, table size in bytes,<br>column min/max, distinct counts (cardinality),<br>null counts, histograms</pre><p>In classic Spark you collect them explicitly:</p><pre>ANALYZE TABLE orders COMPUTE STATISTICS FOR ALL COLUMNS;</pre><p>Modern table formats like Delta Lake maintain file-level statistics automatically as data is written.</p><blockquote><em>⚙️ </em><strong><em>Worth knowing:</em></strong><em> full CBO (</em><em>spark.sql.cbo.enabled) is actually </em><strong><em>off by default</em></strong><em> in open-source Spark, partly because stale statistics can make things worse, and because Adaptive Query Execution (more on it below) now handles many of the same decisions at runtime — with </em>real<em> numbers instead of estimates. Broadcast-join sizing, however, works out of the box based on table size estimates.</em></blockquote><h3>Example: join order matters — a lot</h3><p>Say your query joins three tables:</p><pre>orders     ≈ 1,000,000,000 rows<br>customers  ≈    10,000,000 rows<br>countries  ≈           200 rows</pre><p>Join customers ⋈ orders first, and you produce a gigantic intermediate result that then gets joined with countries. Join the small, selective tables first, and the intermediate data stays tiny all the way through.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K3CBQ9QqWkN-ju_lIhNMgg.png" /></figure><p>The query result is identical either way. The execution cost is not — the difference can be orders of magnitude.</p><h3>Example: broadcast or shuffle?</h3><p>The default strategy for joining two large tables is a <strong>Sort-Merge Join</strong>: both sides are shuffled across the cluster so that matching keys land on the same executor. Shuffling a 500 GB table over the network is expensive.</p><p>But if one side is small —</p><pre>orders    = 500 GB<br>customers =  20 MB</pre><p>— Spark can instead <strong>broadcast</strong> the small table: ship a full copy of customers to every executor and join locally. The 500 GB table never moves.</p><pre>customers &lt; spark.sql.autoBroadcastJoinThreshold (default: 10 MB)<br>        ⇒ BroadcastHashJoin (BuildRight: customers)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CskqisPfHVJcKBGFWrieiA.png" /></figure><p>This single decision is often the difference between a 2-minute job and a 2-hour one.</p><h3>RBO vs CBO in one table</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IhBqyoZAWQoS6ReAVhPnzA.png" /></figure><h3>Stage 5: Physical Planning — from “what” to “how”</h3><p>A logical plan says <em>what</em> to compute (“join these two relations”). A physical plan says <em>how</em> (“use a BroadcastHashJoin with customers as the build side”).</p><p>For a single logical join, Spark has several physical implementations to choose from:</p><pre>BroadcastHashJoin    – small side fits in memory, no shuffle<br>SortMergeJoin        – the robust default for two large sides<br>ShuffledHashJoin     – hash-based, when one side is smaller<br>BroadcastNestedLoop  – the fallback for non-equi joins</pre><p>Catalyst applies its planning strategies — using size estimates, statistics, and your configuration — to map every logical operator to the physical operator with the lowest expected cost. The output is the <strong>Physical Plan</strong> (also called the Spark Plan), which you can inspect yourself at any time:</p><pre>df.explain(true)   // shows all four plans: parsed → analyzed → optimized → physical</pre><p>Reading explain() output is one of the highest-ROI skills a data engineer can build. The plan never lies.</p><h3>Stage 6: Whole-Stage Code Generation — fusing operators into one loop</h3><p>Here’s a subtle performance killer in classic query engines: every operator (filter, project, aggregate…) is a separate object, and every row is passed from one operator to the next through virtual function calls. Per row. Billions of times.</p><p>Spark’s answer is <strong>Whole-Stage Code Generation</strong>: at runtime, Catalyst collapses a chain of operators into a <em>single</em> generated Java function — essentially the tight, hand-written loop a human expert would have produced:</p><pre>// conceptually, what Spark generates:<br>while (rows.hasNext()) {<br>    row = rows.next();<br>    if (row.amount &gt; 100) {           // Filter<br>        sum[row.customer_id] += ...;  // Aggregate<br>    }<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K-e8BgK6DHq8-wEUyGqu6g.png" /></figure><p>The wins:</p><ul><li>no virtual calls between operators,</li><li>far less object allocation,</li><li>data stays hot in CPU registers and cache.</li></ul><p>You can spot it in explain() output: operators wrapped in *(1), *(2) markers belong to the same generated stage.</p><h3>Stage 7: Adaptive Query Execution — re-planning mid-flight</h3><p>Everything so far happens <em>before</em> execution, based on <strong>estimates</strong>. But estimates can be wrong — statistics get stale, filters are more (or less) selective than predicted, data is skewed in ways nobody anticipated.</p><p>Since Spark 3.x (enabled by default from 3.2), <strong>Adaptive Query Execution (AQE)</strong> closes this gap: at every shuffle boundary, Spark pauses, looks at the <em>actual</em> sizes of the data just produced, and re-optimizes the rest of the plan.</p><p>Three classic AQE moves:</p><p><strong>1. Runtime broadcast conversion.</strong> The plan expected customers to be 500 MB after filtering, so it chose a Sort-Merge Join. At runtime the filtered data turns out to be 8 MB → AQE switches to a Broadcast Hash Join on the fly.</p><p><strong>2. Shuffle partition coalescing.</strong> The job was configured for 200 shuffle partitions, but the actual shuffled data is tiny. AQE merges them into, say, 12 — eliminating the overhead of scheduling 188 nearly-empty tasks.</p><p><strong>3. Skew join handling.</strong> One customer accounts for 90% of all orders, so one partition is enormous while 199 are idle. AQE detects the skew and splits the heavy partition into smaller chunks processed in parallel — no more “one straggler task holds the whole job hostage.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A_Vbw7tU5BKK_BJuvGgigw.png" /></figure><p>AQE is, in a sense, the honest admission built into Spark: <em>we will never estimate perfectly, so let’s measure and correct instead.</em></p><h3>Bonus: What Changes in Databricks?</h3><p>On Databricks, Catalyst doesn’t work alone — it sits inside a larger optimization stack:</p><pre>SQL / DataFrame<br>   → Catalyst RBO<br>   → Catalyst CBO (fed by Delta Lake statistics)<br>   → AQE<br>   → Photon (vectorized C++ execution engine)</pre><p>plus Delta-specific accelerators:</p><ul><li><strong>Delta Lake file statistics</strong> — min/max per file, collected automatically on write</li><li><strong>Data skipping &amp; Dynamic File Pruning</strong> — skip whole files at runtime based on join keys</li><li><strong>Z-Ordering / Liquid Clustering</strong> — physically co-locate related data so skipping actually works</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xWJcV4JoDDyKvlC-_DTHbA.png" /></figure><p>This is why a well-designed Delta table can be <em>dramatically</em> faster than raw Parquet — even though the query text is character-for-character identical. The query didn’t change; the amount of information available to the optimizer did.</p><h3>The Interview-Ready Summary</h3><p>If a Senior Data Engineer interview throws “explain Catalyst” at you, here’s the answer in one breath:</p><blockquote><em>Catalyst first parses SQL or DataFrame operations into an unresolved logical plan, then resolves tables, columns, and functions against the catalog during analysis. It applies rule-based optimizations — predicate pushdown, column pruning, constant folding, expression simplification — iteratively, in batches, until a fixed point, producing an optimized logical plan. Cost-based optimization then uses table and column statistics for decisions like join ordering and join strategy selection. Spark maps the logical plan to the cheapest physical plan, fuses operator chains into single JVM functions via Whole-Stage Code Generation, and — through Adaptive Query Execution — keeps re-optimizing at runtime: converting joins to broadcast, coalescing shuffle partitions, and splitting skewed partitions based on actual data sizes.</em></blockquote><p>And the practical takeaway for everyday work:</p><ol><li><strong>Trust Catalyst with structure</strong> — don’t micro-optimize what pushdown and pruning already handle.</li><li><strong>Help it with information</strong> — keep statistics fresh, use Delta/Parquet, avoid opaque UDFs.</li><li><strong>Verify with </strong><strong>explain()</strong> — the plan tells you exactly what Spark decided, and why your job is slow.</li></ol><p>The best optimizer in the world can only be as smart as the metadata you give it.</p><p><em>If you found this useful, I write regularly about data engineering, Spark, and data platform architecture — follow me here on Medium or connect on LinkedIn.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=adc4eccd8121" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What It Actually Takes to Build a Private AI Assistant for Your Business]]></title>
            <link>https://medium.com/@alexeyban/what-it-actually-takes-to-build-a-private-ai-assistant-for-your-business-9f252d088a0d?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/9f252d088a0d</guid>
            <category><![CDATA[productivity]]></category>
            <category><![CDATA[knowledge-management]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[ai-rag-system]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Fri, 17 Apr 2026 16:56:48 GMT</pubDate>
            <atom:updated>2026-04-18T06:29:47.182Z</atom:updated>
            <content:encoded><![CDATA[<p>A client came to me with a brief that went something like this:</p><p><em>“We’re a small firm and we want an advanced chatbot that can answer questions using our internal documents. We have around 1,000+ PDFs and Word docs covering our service offerings, onboarding guides, FAQs, and internal policies. We want it deployed as a web interface our team of 15 people can use. Answers should reference the source document. We need it to stay on topic and not make things up.”</em></p><p>I’ve heard some version of this brief more than once. The documents are different each time — legal filings, insurance policies, technical manuals, HR handbooks. The pain is always the same.</p><p>I’m a data engineer and architect with 12+ years building data platforms — from on-premise warehouses to cloud lakehouses on Azure and Databricks. I’ve worked with large retail and product companies, helped teams migrate infrastructure to the cloud, and built systems that run reliably at scale without constant babysitting. These days I also take on focused consulting engagements where there’s a clear data problem to solve. This was one of them.</p><p>Here’s what I actually built, what it cost, and what I learned.</p><h3>The Real Problem Isn’t AI. It’s Trapped Knowledge.</h3><p>Before writing a single line of code, I spent time reframing the problem with the client.</p><p>Every company I’ve worked with has the same invisible issue: years of accumulated knowledge, carefully written by smart people, sitting completely inaccessible in shared drives, inboxes, and document folders that nobody navigates anymore. New employees spend their first weeks interrupting colleagues with questions the answers to which already exist somewhere. Senior people waste hours searching for a document they know exists but can’t locate. Customers get inconsistent answers because support staff are working from memory rather than source material.</p><p>The question isn’t whether AI is ready for this. It is. The question is whether your knowledge is in a state where any system — AI or otherwise — can reliably find and use it.</p><p>That reframe matters, because it changes how you think about the investment. You’re not buying a chatbot. You’re finally making your documents useful.</p><h3>What I Actually Built, and How Long It Took</h3><p>The system is called a RAG pipeline — Retrieval-Augmented Generation. The name is technical but the concept is simple enough to explain over coffee.</p><p>Imagine two people working together to answer your questions. The first one — call them the researcher — has read every document your company has ever produced. When you ask something, they go find the three or four most relevant paragraphs from across all those files and put them on a desk in front of the second person. The second person — the writer — reads those paragraphs and composes a clear, direct answer for you, with a note about which document it came from.</p><p>That’s the whole system. The AI model is the writer. The retrieval pipeline is the researcher. And here’s the part most people get backwards: the writer is the easy part. Building a reliable researcher — one that finds the <em>right</em> paragraphs 90% of the time rather than 60% — is where the real work is.</p><p>The full system includes an ingestion pipeline that processes all document types, a vector database that stores meaning rather than keywords, a reranking layer that double-checks retrieval quality, an LLM that synthesizes the answer, and a clean web interface the team can use without any training.</p><p>End-to-end, working with modern tooling, the project came together in under two weeks. That pace is possible when you’ve solved the underlying data engineering problems before.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OG-bE2HsHBVRLOIGoJDCg.png" /></figure><h3>The Documents Problem Nobody Warns You About</h3><p>This is where unprepared projects quietly fall apart.</p><p>“A thousand PDFs” sounds like a storage problem. It isn’t. The real challenge is that those thousand documents were created by different people, in different tools, over different years, in different formats — and before any AI system can use them, they all need to become something consistent and clean.</p><p>In practice, documents come in four flavours, each requiring different handling.</p><p><strong>Text PDFs</strong> are the straightforward case — the text is embedded in the file and can be extracted directly.</p><p><strong>Scanned PDFs and images — including JPEG and TIFF files</strong> — are the hidden time bomb. In most enterprise document archives, somewhere between 20 and 40 percent of files are scanned images of printed pages. There’s no text to extract — only pixels. These require OCR (optical character recognition) to convert the image into readable text before anything else can happen. The system I built handles this automatically: it detects whether a PDF contains real text or just an image, routes it accordingly, and processes standalone image files the same way. If your system can’t handle scanned documents, you’re already missing a significant portion of your knowledge base before you’ve asked a single question.</p><p><strong>Internal wikis and Confluence pages</strong> require a different approach entirely — pulling content through an API, stripping out the formatting noise that accumulates in collaborative tools (broken table layouts, internal macros, legacy content that never got cleaned up), and converting everything into plain readable text.</p><p><strong>Word documents</strong> have their own quirks — nested styles, tracked changes, embedded objects — all of which need to be handled gracefully rather than silently dropped.</p><p>Everything ends up normalized into the same clean format before any AI touches it. This uniformity is what makes the rest of the pipeline fast and reliable. It’s also the step where having a data engineering background rather than a pure AI background makes a real difference — handling dirty, inconsistent source data systematically is the job.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Vfom7KglPu9MQMC-ruzmJA.png" /></figure><h3>How the Search Actually Works</h3><p>Most people assume AI search works like Google — you type words, it finds documents containing those words. What’s actually happening is more powerful and more useful.</p><p>Instead of matching keywords, the system works with meaning. When a new employee asks “how do I submit expenses,” the system finds the relevant section of your finance policy even if that document uses the phrase “reimbursement process” and never mentions the word “submit.” It understands that these mean the same thing.</p><p>After the initial search returns a candidate set of results, a second layer — the reranker — reads through them and re-orders them by how genuinely relevant each one is to the actual question, not just how semantically similar the words are. It’s a quality gate before anything reaches the AI model.</p><p>The practical impact of this two-stage approach is significant. The difference between a system that surfaces the right information 60% of the time and one that does it 90% of the time lives entirely in this layer — and it’s where most off-the-shelf chatbot products quietly underperform.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GWRV4NwaXFNeFNUOZhYAHw.png" /></figure><h3>Keeping the AI Honest</h3><p>AI models have a well-known tendency to sound confident while being wrong — generating plausible, well-structured answers that aren’t grounded in any real source. For an internal business tool where people will act on what they read, this is a serious problem.</p><p>The solution isn’t a better model. It’s better constraints.</p><p>The system is built with strict instructions: answer only using the documents provided. If the answer isn’t there, say so. No improvising, no filling gaps with general knowledge, no hedging with something that sounds relevant but isn’t sourced.</p><p>In practice, this means the system will sometimes say “I don’t have enough information in the available documents to answer that.” That’s the right behaviour. A tool your team trusts to say “I don’t know” is far more valuable than one that always produces an answer.</p><h3>What Does This Actually Cost to Run?</h3><p>This is the question most technical write-ups skip. Let me be direct about it, because the numbers drive the decision.</p><p>There are three realistic options for a team of 10–20 people.</p><p><strong>Option A — Private cloud hosting.</strong> You run the system on a cloud server (AWS, Azure, or GCP) that you control. Nothing leaves your environment. All documents, all queries, all answers stay within your infrastructure. Monthly cost runs roughly $800–$1,500 depending on usage and the size of the AI model. This is the recommended starting point for most teams — you get full privacy and control without the upfront hardware commitment.</p><p><strong>Option B — On-premise server.</strong> You own the hardware. Upfront investment of roughly $5,000–$8,000 for a server and GPU. Spread over two to three years, the effective monthly cost drops to around $300–$450. This is the best long-term economics for teams with stable headcount and sensitive data that requires air-gapped infrastructure.</p><p><strong>Option C — Cloud AI API (OpenAI, Anthropic, or similar).</strong> The fastest and cheapest option to deploy — roughly $50–$300 per month for a small team, with no server to manage. For many businesses, this is perfectly reasonable.</p><p>But there’s a caveat worth stating plainly: with this option, every question your team asks — and every document fragment searched to answer it — travels through a third-party provider’s servers. For general business content, that may be acceptable. For firms handling client data, financial records, legal documents, medical information, or anything that falls under GDPR, HIPAA, or similar regulations, this is a meaningful risk. It’s one that’s easy to underestimate until a compliance audit or a data incident makes it impossible to ignore.</p><p>The private options cost more. They exist for good reasons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WySsTmJVocRoBgDzIOaIJg.png" /></figure><p>One architectural note that affects all three scenarios: the single biggest cost driver is the AI model, not the storage or the application. A well-built retrieval pipeline — one that finds the right context reliably — allows you to run a smaller, cheaper model and still get excellent answers. This is where engineering investment directly reduces operating cost. Better retrieval is not just a quality improvement. It’s a financial one.</p><p>Monthly Cost Privacy Setup Time Private cloud $800–$1,500 Full Days On-premise $300–$450 Full Weeks Cloud AI API $50–$300 Partial Hours</p><h3>Who This Is For</h3><p>This kind of system makes sense if your team regularly wastes time searching for information they know exists somewhere but can’t locate. It makes sense if onboarding new people is slow because institutional knowledge lives in senior colleagues’ heads rather than findable documents. It makes sense if you’ve looked at consumer AI tools and concluded — correctly — that they’re not suitable for your sensitive internal content.</p><p>It’s probably not the right investment if your document base is small and stable, or if your team already has a reliable search and knowledge management system that people actually use.</p><h3>What the Team Got</h3><p>Against the original brief: 1,000+ documents ingested across all formats including scanned files. A chat interface accessible to all 15 team members. Answers that cite the specific source document and section. A system that declines to answer when the information isn’t in the knowledge base. Full privacy — no document content sent to external services. Deployed and running in under two weeks.</p><p>The brief was met. The team has a tool they can trust.</p><h3>Where This Goes Next</h3><p>The foundation of a RAG system is the ingestion and retrieval pipeline. Once that’s solid, the natural next layer is making the system easier to use, smarter over time, and safe to scale across a growing organization.</p><p><strong>Making it easier to use.</strong> The next practical step for most teams is meeting people where they already work — Slack and Microsoft Teams integrations so staff can ask questions without switching to a separate interface. Alongside that, a web-based admin panel where non-technical team members can add new document catalogs, manage user roles, and trigger re-indexing without touching code or opening a terminal.</p><p><strong>Making it smarter over time.</strong> When a document is updated, the knowledge base should update automatically — right now that’s a manual step, and auto re-indexing eliminates it entirely. A simple feedback mechanism (thumbs up or down on each answer) creates a signal for continuous quality improvement. Multi-language support is also on the roadmap: the ability to ask questions in one language and retrieve answers from documents written in another, which matters more than people expect for international teams.</p><p><strong>Making it safe to scale.</strong> Document-level access control — so HR documents are only retrievable by HR, and confidential client material stays appropriately scoped — is the feature that turns a team tool into an enterprise-grade one. Usage analytics round this out: visibility into what questions people are asking most, which documents get cited, and where the knowledge base has gaps.</p><p>The foundation is working. These are the layers that turn a useful tool into a business-critical one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5CZ5UOBV5UBLj7G44iiQIA.png" /></figure><h3>Let’s Talk</h3><p>I’m a data engineer and architect. I work with companies that need their data — and their institutional knowledge — to actually work for them. If your team is sitting on a library of documents that nobody can find anything in, that’s a solvable problem. It just needs someone who’s done it before.</p><p>If this resonates, I’d be glad to have a conversation: <a href="https://www.linkedin.com/in/alexey-banaev-16b69325/">linkedin.com/in/alexey-banaev</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9f252d088a0d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I Let an AI Build a Full Data Engineering Platform From Scratch. Here’s What Actually Happened.]]></title>
            <link>https://medium.com/@alexeyban/i-let-an-ai-build-a-full-data-engineering-platform-from-scratch-heres-what-actually-happened-73238d241938?source=rss-caa831a99b27------2</link>
            <guid isPermaLink="false">https://medium.com/p/73238d241938</guid>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[metabase]]></category>
            <category><![CDATA[generative-ai-tools]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Alexey ban]]></dc:creator>
            <pubDate>Sun, 15 Mar 2026 18:27:06 GMT</pubDate>
            <atom:updated>2026-03-15T18:27:06.777Z</atom:updated>
            <content:encoded><![CDATA[<p><em>15 sessions. ~16 hours. 2 free AI models. Zero human-written code. MIT License.</em></p><p>A few days ago, I ran an experiment that most data engineers would call either ambitious or reckless — probably both.</p><p>I started with an <strong>empty GitHub repository</strong>, dropped in a collection of AI agent definitions, and handed the entire job to an AI coding assistant. My only instructions: build a modern, production-grade CDC data platform using only open-source components. No scaffolding. No starter code. No hints about what tables to create.</p><p>I deliberately chose <strong>not to intervene as a developer</strong> — the whole point was to find the real boundary of what these systems can do on their own.</p><p>Here’s what I found.</p><h3>The Scale of the Experiment</h3><p>This wasn’t a quick proof-of-concept. By the end:</p><ul><li><strong>15 AI sessions</strong> across two days (March 14–15, 2026)</li><li><strong>Two different models</strong>: MiniMax M2.5 (free) for the first 11 sessions, then a switch to big-pickle (also free tier) for the final 4</li><li><strong>13 Docker services</strong> running simultaneously</li><li><strong>~40+ agent and skill definition files</strong> in the repository, covering roles from Data Engineer to DevOps Automator to Threat Detection Engineer</li><li>A full <strong>medallion architecture</strong> (Bronze → Silver → Gold) with CDC, orchestration, query engine, and BI layer</li></ul><p>The agent library I had pre-built included roles like engineering-data-engineer, engineering-devops-automator, engineering-rapid-prototyper, engineering-backend-architect, and a newly created engineering-data-platform-architect — all defined as markdown files that the AI used to orient its approach and decision-making style.</p><h3>What I Asked the Model to Build</h3><p>The requirements were intentionally minimal:</p><blockquote>“Build a modern ingestion architecture from PostgreSQL to Delta Lake suitable for analytics workloads. Analyze the source database, decide on the fact and dimension tables yourself, and implement a full Bronze → Silver → Gold pipeline. Add Metabase on top for dashboards.”</blockquote><p>Source: the <strong>dvdrental</strong> sample database — 15 tables, ~600K rows, a classic video rental schema.</p><p>I said nothing about which fact or dimension tables to create. That was the model’s job.</p><h3>What the Model Did First — And This Part Impressed Me</h3><p>Before writing a single line of code, the model explored the empty repository and immediately understood the context from structure alone:</p><blockquote>“I see — this repository is a collection of agent definitions for an AI data platform. It’s not a typical code repository with Python or JavaScript files — it’s essentially documentation/definitions for AI agents.”</blockquote><blockquote>“Now I have a good understanding. This is a repository of AI agent definitions for an AI data platform engineering project. The agents are defined as markdown files with YAML frontmatter containing metadata.”</blockquote><p>Then, without any instruction, it:</p><ol><li><strong>Analyzed the full dvdrental schema</strong> — 15 tables, foreign keys, cardinalities, data types</li><li><strong>Independently designed the data model</strong> — proposed fact_payment, fact_rental, dim_customer, dim_film, dim_staff, dim_date</li><li><strong>Designed the full architecture</strong> — CDC pipeline, medallion layers, orchestration strategy</li><li><strong>Created new agents and skills</strong> — added a database-analysis skill, data-pipeline skill, infra-as-code skill, and a new engineering-data-platform-architect agent to the library</li></ol><p>That autonomous schema analysis was the most impressive moment in the entire experiment. I gave zero hints about what tables to build. The model looked at a rental business’s source data, reasoned about what analytics workloads would need, and made reasonable star-schema decisions independently.</p><h3>What Actually Got Built</h3><p>By the end of all 15 sessions, the repository contained:</p><ul><li><strong>Docker Compose</strong> with 13 services (PostgreSQL, Zookeeper, Kafka, Debezium, Spark master + worker, MinIO, Hive Metastore, Trino, Airflow, Metabase, Kafka UI)</li><li><strong>PySpark Delta Lake pipeline</strong> — Bronze (15 raw tables), Silver (10 cleaned/deduplicated tables), Gold (6 analytics tables)</li><li><strong>Debezium CDC connector</strong> — capturing INSERT/UPDATE/DELETE from PostgreSQL WAL</li><li><strong>Airflow DAG</strong> — 14 tasks, running on a 1-minute continuous schedule</li><li><strong>OLTP Simulator</strong> — Python script generating realistic transaction load</li><li><strong>Trino catalog configurations</strong> — for both Delta Lake and Iceberg query paths</li><li><strong>Metabase dashboards</strong> — connected to Gold layer tables</li><li><strong>Project documentation</strong> — AGENTS.md, OPENCODE_LOG.md, PROMPT_TEMPLATES.md, README with full architecture guide and issue log</li></ul><p>End metrics: <strong>&lt; 1 second CDC latency</strong>, 14 Airflow tasks passing, 6 Gold tables populated, Metabase showing live charts.</p><h3>Where It Struggled — The Honest Part</h3><p>This is the more interesting section, because the failures tell you more about the current state of these systems than the wins do.</p><h3>1. Context loss between sessions</h3><p>The model restarted from scratch three times in early sessions — re-exploring the same directories, re-reading the same files, re-doing already-completed work. It created an OPENCODE_LOG.md mechanism to preserve state, but didn&#39;t reliably read it at session start. <strong>~90 minutes lost</strong> to repeated initialization.</p><p><em>Lesson: Agentic systems need better persistent context. This is a tooling problem as much as a model problem.</em></p><h3>2. Trusting exit codes over actual data</h3><p>Spark returns exit code 1 even on a successful run, due to JVM shutdown hooks. The model took this at face value and spent <strong>7+ consecutive Airflow runs</strong> debugging a pipeline that was working fine. It never thought to verify whether data actually landed in MinIO until explicitly told to check output, not process.</p><p><em>Lesson: AI engineers make the same mistake junior humans do — they trust the wrong signal.</em></p><h3>3. The Hive Metastore wall</h3><p>The last two sessions revealed a deeper problem: getting <strong>Metabase to query Delta Lake directly</strong> (without PostgreSQL as a bridge) required a working Hive Metastore for Trino. This proved genuinely hard. The model tried:</p><ul><li>naushadh/hive-metastore — Docker config syntax errors</li><li>ghcr.io/apeschel/hive-metastore — image pull denied</li><li>starburstdata/hive-metastore — strict env var requirements, Derby/PostgreSQL SQL dialect conflicts</li><li>Manual Derby schema initialization — schemaTool failed with syntax errors</li><li>Appending services directly to docker-compose.yml — validation errors</li></ul><p>After exhausting Hive Metastore options, the model correctly pivoted: <strong>propose Apache Iceberg</strong> as an alternative to Delta Lake, since Trino has native Iceberg support without a metastore dependency. The Iceberg pipeline was written. But the session ended before end-to-end validation could be completed.</p><p><strong>This is the task that was not finished.</strong> The model reached its limits on the Trino ↔ Delta Lake direct query path and saved progress for a future session.</p><h3>4. The Superset dead end (3 sessions lost)</h3><p>Three full sessions were spent trying to make Apache Superset work. The model kept hitting 403 Forbidden errors on every API call, retrying variations of the same approach without stepping back to question the tool choice. It took a direct human prompt — <em>“try reading Superset docs”</em> — to break the loop.</p><p>After session 4, Superset was abandoned. Metabase worked on the first serious attempt.</p><p><em>Lesson: Models don’t know when to pivot. They optimize locally, not globally.</em></p><h3>5. Non-idempotent scripts</h3><p>Scripts failed on re-run with “user already exists”, “bucket already created”, “connector already registered”. Basic defensive patterns — IF NOT EXISTS, exists_ok=True, checking state before acting — had to be prompted explicitly rather than being the default.</p><h3>6. A PySpark type bug that slipped through</h3><p>python</p><pre># What the model wrote (bug):<br>col(&quot;date&quot;).cast(StringType)   # passing the class, not an instance</pre><pre># What it should have been:<br>col(&quot;date&quot;).cast(&quot;string&quot;)</pre><p>Small, but it broke dim_date creation and was committed before being caught. The model eventually fixed it — but only after the code was already in the repository.</p><h3>My Conclusion After 15 Sessions</h3><p>The model did a lot. But it did not finish the task.</p><p><strong>That was intentional on my part.</strong> I deliberately didn’t intervene as a developer, because I wanted to see where the real boundary is — not where a helpful human plus AI can get to, but where the AI alone stalls.</p><p>Here’s what I found that boundary to be:</p><p><strong>What AI handles well today:</strong></p><ul><li>Boilerplate infrastructure (Docker Compose, connector configs, DAG scaffolding)</li><li>Known architectural patterns (medallion architecture, star schema, CDC setup)</li><li>Autonomous exploration and analysis of unfamiliar codebases</li><li>Generating the first 80% of any standard data engineering task, fast</li></ul><p><strong>What still requires a human:</strong></p><ul><li>Knowing when to abandon a failing approach (the Superset loop, the Hive Metastore wall)</li><li>End-to-end verification — checking that data actually landed, not just that commands succeeded</li><li>Writing resilient code that works on the 10th run, not just the first</li><li>Completing the last 20% when things get genuinely novel</li></ul><h3>What This Project Is Now</h3><p>I’m releasing this project under the <strong>MIT License</strong>.</p><p>My honest assessment: <strong>this is an excellent starting point for a real data project</strong>, not a finished production system. The model assembled a solid, coherent boilerplate — Docker infrastructure, pipeline structure, orchestration patterns, BI connectivity — that would take a data engineer several days to write from scratch. With a human developer picking it up from here, most of what remains is configuration and integration work, not architecture.</p><p>I believe that with time — and with paid model tiers that have longer context windows and better session memory — a system like this will be able to spin up a complete data project end-to-end with minimal human intervention.</p><p>We’re not there yet. But after this experiment, I think we’re closer than most people realize.</p><h3>Final Thought</h3><p>The most striking moment in the entire experiment wasn’t a technical achievement. It was session zero — when the model looked at an empty repository containing only markdown files and said:</p><blockquote>“Now I have a good understanding. This is a repository of AI agent definitions for an AI data platform engineering project.”</blockquote><p>It read the room. It understood the intent from structure alone. Then it started building.</p><p>We’re not at “AI replaces data engineers.” We’re somewhere more interesting: <strong>AI that can orient itself, form a plan, and execute most of it</strong> — with a human needed to catch the edge cases, unstick the dead ends, and finish what the model leaves open.</p><p>That changes the economics of data engineering. It doesn’t eliminate the role — it compresses the timeline and shifts where human judgment is actually needed.</p><p>And that’s worth paying attention to.</p><p><em>Full session logs, all generated code, architecture documentation, and prompt templates are available on GitHub under MIT License:</em> <a href="https://github.com/alexeyban/ai-data-platform-engineer"><em>alexeyban/ai-data-platform-engineer</em></a></p><p><em>Stack: MiniMax M2.5 (free) · big-pickle (free) · OpenCode · PostgreSQL · Debezium · Kafka · Apache Spark · Delta Lake · Apache Iceberg · MinIO · Apache Airflow · Trino · Metabase · Docker</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=73238d241938" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>