July 2, 2026

Five schemas, one direction of flow

The database is organized as a pipeline, and each schema names a stage:

The extract layer: typed, defaulted, verified

Each extract function carries three idioms at once. A first UNION branch selects literal defaults — isnull(cast(null as int), 0) — which simultaneously declares the column’s type, makes the whole output non-nullable, and materializes the id-0 default row that unresolved references will point to. STAR’s negative flags are flipped into positive warehouse semantics with XOR — Suspended ^ 1 becomes enabled. And every string is standardized to nvarchar(100) with an empty-string default.

CREATE function [extract].[jobstatuses]() returns table as return (
    select
              id        = isnull(cast(null as int), 0)
            , name      = isnull(cast(null as nvarchar(100)), '')
            , enabled   = isnull(cast(null as bit), 1)
            , opened    = isnull(cast(null as bit), 1)
            , billable  = isnull(cast(null as bit), 1)
    union all
    select
              id        = t.JobStatusID
            , name      = isnull(cast(t.JobStatus as nvarchar(100)), '')
            , enabled   = isnull(cast(t.Suspended ^ 1 as bit), 1)
            , opened    = isnull(cast(t.Closed ^ 1 as bit), 1)
            , billable  = isnull(cast(t.NoBilling ^ 1 as bit), 1)
        from source.tblJobStatus t
        where null is not distinct from
            case
                when t.JobStatusID = 0 then cast('ERROR: Encountered JobStatusID with zero value.' as int)
            end
);

ID verification means the warehouse never repeats a foreign key it has not proven. The job extract does not accept tblJob.ClientID at face value — it left-joins to tblClient and takes the ID from the lookup side, so an ID that fails to resolve becomes 0 and lands on the default row instead of poisoning joins downstream. Role IDs resolve through two hops — partner to staff — for the same reason.

select
          id            = t.JobID
        , client_id     = isnull(c.ClientID, 0)
        , partner_id    = isnull(ppp.StaffID, 0)
        -- …
    from source.tblJob t
        left outer join source.tblClient c on t.ClientID = c.ClientID
        left outer join source.tblPartner pp on t.PartnerID = pp.PartnerID
            left outer join source.tblStaff ppp on pp.StaffID = ppp.StaffID
        -- …

The payoff arrives one layer up: because extract guarantees verified IDs and a default row, every join in the transform layer is a plain inner join. The null negotiation happens once, at the border, instead of in every report.

The tripwire WHERE clause

The deal-breaker guard from the meeting is worth isolating, because the mechanics are subtle. The predicate compares NULL to a CASE expression. While the deal-breaking condition never occurs, the CASE yields NULL, null is not distinct from null is true, and every row passes. The moment the condition appears, the CASE tries to cast an error message to int — and the query fails loudly, with the message as the error text. No rows survive a deal-breaker; silent corruption becomes a visible stop.

where null is not distinct from
    case
        when t.JobStatusID = 0 then cast('ERROR: Encountered JobStatusID with zero value.' as int)
    end

IS [NOT] DISTINCT FROM requires SQL Server 2022 or later. On earlier versions the same tripwire works as where 1 = isnull(case when … then cast(’ERROR: …’ as int) end, 1).

One generated MERGE for every table

The etl procedure refuses to hand-write sync code. It reads the catalog views — the extract functions on one side, the extracted tables on the other — full-outer-joins the two column lists, and uses the same tripwire idiom to fail immediately if the mapping disagrees anywhere. Then, per table, it generates and executes a MERGE:

declare @sqlcommand nvarchar(max) = '
    drop table if exists #source;
    select * into #source from extract.' + quotename(@table_name) + '();
    alter table #source add primary key clustered (' + @key_cols + ');
    merge extracted.' + quotename(@table_name) + ' with (holdlock) t
        using #source s on ' + @join_cols + @matched_clause + '
        when not matched by target then
            insert (' + @insert_cols + ')
                values (' + @insert_vals + ')
        when not matched by source then
            delete;
    set @row_count = @@rowcount;
';

Two details separate this from a toy. The update branch fires only when something actually changed, using IS DISTINCT FROM across the non-key columns — and string comparisons are forced to a binary collation, so a case-only change still counts as a change. And every extracted table must declare a primary key, or the procedure throws before touching data: the generator enforces its own preconditions.

What it taught

Clean at the border, and only at the border. Because extract owns nulls, types, defaults, and ID verification, everything above it gets inner joins, non-nullable columns, and predictable strings for free. The warehouse never negotiates with a null twice.

Make bad data loud. The tripwire pattern turns a deal-breaking condition into a self-describing failure at extraction time, which is exactly where a warehouse wants to find out — instead of in a partner’s report three layers later.

Metadata can write the plumbing. The generated MERGE meant zero per-table sync code, and the mapping check meant the generator could not silently drift from the tables it fed. That idea — describe the shape once, generate the mechanics — grew into the contract experiments of v3 and v4.

And the honest lesson: a week of handcraft is what taught which parts deserved automation. The cost of v1 is the reason v2 changed the question.

Six reports, one method

Each report got its own schema and a single run procedure that stages only the tables and columns the report actually needs, then assembles the final query. The usage assumptions were narrowed deliberately and written down up front — job-level grouping, no per-user restriction logic, specific parameter values — because understanding a proc means being honest about which of its branches you are reproducing.

One comment in the scripts states the intent exactly: these tables are not the report — they are the minimal staged sources that make the final query easy to understand and tie back to the original STAR proc. No indexes, on purpose. This is a learning and reconciliation exercise, not a production load.

Documentation a non-accountant can read

Every staged table carries the same comment structure: what it is for, a plain-language explanation of the accounting concept, the flags and dates that matter, and — most important — a proc-comparison note stating exactly what the original stored procedure does with this data. The drafts came from an AI agent working under direction; correctness came from tying the final output back to the original report, which is the only test that counts.

/*
    dw.wip

    Non-accountant explanation:
        WIP means "work in progress" — value sitting on a job that has not been
        fully cleared, billed, written off, or otherwise resolved.

    Charge type grouping:
        22 = bill        Billing/progress-bill-style row.
        28 = allowance   WIP provision/allowance. Goes to ALLOWANCE, not AGE1-AGE6.
        23-27 = normal   Normal aged WIP rows. These go into AGE1-AGE6.

    Important dates:
        WipDate       = raw tblWIP.Date. Useful for debugging.
        WipAgingDate  = sgv_WIP_PeriodDates.Date — the date the proc actually
                        uses for the aged bucket calculation.

    Important amount:
        BaseRemainingAmount = Amount - Billed
*/
select
    w.WIPID,
    w.JobID,
    WipGroup =
        case
            when w.ChargeTypeID = 22 then 'bill'
            when w.ChargeTypeID = 28 then 'allowance'
            when w.ChargeTypeID in (23, 24, 25, 26, 27) then 'normal'
            else 'other'
        end,
    WipDate = w.[Date],
    WipAgingDate = wpd.[Date],
    BaseRemainingAmount = cast(w.Amount - w.Billed as money)

Details like the two dates matter more than they look: the report ages by the period view’s date, and knowing that is the difference between reproducing the numbers and being able to explain them.

The aging semantics, finally explicit

This is where the meeting’s aging story lives in code. Progress bills are handled differently depending on report and configuration: the Aged WIP report either presents bills as their own PB column or folds them into the aging buckets, and the AR Aging report — when unallocated cash is enabled — redistributes PB against the buckets oldest-first: AGE6, then AGE5, down to AGE1. The decomposed version replaces the proc’s paired CASE expressions with a readable waterfall: at each step the bucket absorbs what it can and the remainder carries to the next.

--  Each waterfall step does the same thing:
--      new bucket = max(bucket + remaining_pb, 0)
--      new pb     = min(bucket + remaining_pb, 0)
--
--  Tie-out rules preserved from the proc:
--      only rows where PB <> 0 go through the waterfall;
--      TOTAL and NET are calculated before redistribution and are not recomputed.

cross apply
(
    select
            age6    = cast(case when u.apply_unalloc = 1 then greatest(r.age6 + u.pb_start, cast(0 as money)) else r.age6 end as money)
        ,   pb      = cast(case when u.apply_unalloc = 1 then least(r.age6 + u.pb_start, cast(0 as money)) else 0 end as money)
) s6

cross apply
(
    select
            age5    = cast(case when u.apply_unalloc = 1 then greatest(r.age5 + s6.pb, cast(0 as money)) else r.age5 end as money)
        ,   pb      = cast(case when u.apply_unalloc = 1 then least(r.age5 + s6.pb, cast(0 as money)) else 0 end as money)
) s5

-- … continues through AGE1

The tie-out discipline is the quiet lesson: rows with PB = 0 are skipped even when a bucket is negative, and totals are deliberately left un-recomputed — because the goal is matching the report exactly, and “almost the same logic” is a different report.

What it taught

Reproducing a number and understanding it are different achievements. The documentation pass turned charge types, period cutoffs, and bucket redistribution from mystery into model — and once the meaning was explicit, the logic became something that can be shaped rather than only copied.

Same label, different meaning: “aging” is a family of methods, not one calculation. Progress bills aged by their own date and progress bills consumed oldest-bucket-first produce different reports from the same data — which is why validating against a firm’s own reports is the only honest standard.

A note from the group: implementations have become more standardized in recent years with the SR2 report pack, though older environments still vary.

GREATEST and LEAST earned their place: the waterfall reads as algebra — absorb what you can, carry the remainder — instead of nested CASE pairs. And the AI workflow found its shape here: the agent drafts, the human directs, and the tie-out decides.

Anatomy of a contract

Every element of the type does double duty. A ## computed column’s definition stores the source object name. The physical columns are the source shape — and their PRIMARY KEY is the source key. The # computed columns are the warehouse columns, and their computed definitions are the conversion expressions — with a UNIQUE constraint marking the warehouse key. CHECK constraints carry the deal-breaker rules. One type, read back through the catalog views, is the whole specification:

CREATE TYPE [contract].[jobs] AS TABLE(
    [##external_object_name]  AS ('tblJob'),
    [JobID] [int] NOT NULL,
    [ClientID] [int] NULL,
    [ContNominalID] [int] NULL,
    [JobTypeID] [int] NULL,
    [#id]  AS ([JobID]),
    [#client_id]  AS (isnull([ClientID],(0))),
    [#cont_nominal_id]  AS ([ContNominalID]),
    [#job_type_id]  AS (isnull([JobTypeID],(0))),
    PRIMARY KEY CLUSTERED ([JobID] ASC),
    UNIQUE NONCLUSTERED ([#id] ASC),
    CHECK (([JobID]<>(0)))
)

A reflection function shreds the catalog metadata, and contract.tables assembles two commands per contract by filling a template — the CREATE TABLE for the extracted target, and the extraction script itself. The external server and database come from a one-row app.config table, which is what retired the synonyms from v1: change the config row and every generated command points at a different STAR.

-- the extraction template, before placeholder replacement:

drop table if exists {temp_table_name};
select {temp_table_select_list}
    into {temp_table_name}
    from {external_server_name}.{external_db_name}.{external_schema_name}.{external_object_name};

alter table {temp_table_name} add primary key clustered ({temp_table_primary_key_cols});

with source as (
    select
            {temp_table_select_expression_list}
        from {temp_table_name}
)
merge [extracted].{contract_name} t
    using source s on {merge_join_cols}
    {merge_match_clause}
    when not matched by target then
        insert ({merge_insert_cols})
            values ({merge_insert_vals})
    when not matched by source then
        delete;

The generator polices itself with the same tripwire idiom from v1: if a contract lacks a warehouse unique key or a source primary key, the command column does not come back empty or wrong — it throws, and the error text tells the developer exactly which constraint to add. With the generator doing the thinking, the etl procedure shrank to a loop: create the extracted table if missing, execute the extraction command, next contract.

Event views: one vocabulary for six reports

Above the extraction, this version reshaped the financial sources into event views — nominal events, bill events, receipt allocations, WIP, transfers, adjustments, provisions — so the six reports draw from one vocabulary instead of six private ones. The WIP view shows the payoff: every charge type becomes a typed measure, and the v2 semantics lesson is carried as code — the same row is classified two ways, because a write-off is its own thing to a WIP report and just another aged row to an aging report:

, wip_group     = cast(case when w.charge_type_id = 22 then 'bill'
                            when w.charge_type_id = 23 then 'writeoff'
                            when w.charge_type_id = 28 then 'allowance'
                            when w.charge_type_id in (24, 25, 26, 27) then 'normal'
                            else 'other' end as varchar(20))
, aging_group   = cast(case when w.charge_type_id = 22 then 'bill'
                            when w.charge_type_id = 28 then 'allowance'
                            when w.charge_type_id in (23, 24, 25, 26, 27) then 'normal'
                            else 'other' end as varchar(20))
, hours             = convert(decimal(19,2), case when w.charge_type_id = 24 then w.units else 0 end)
, time_amount       = convert(decimal(19,2), case when w.charge_type_id = 24 then w.amount else 0 end)
, bill_amount       = convert(decimal(19,2), case when w.charge_type_id = 22 then w.amount else 0 end)
, writeoff_amount   = convert(decimal(19,2), case when w.charge_type_id = 23 then w.amount else 0 end)

The regression harness: tie-out as a test suite

This version also answered “how do you know the rebuilt reports are right?” at scale. All six reports exist as parameterized procedures, and a regression runner executes them against a table of test sets — date ranges, period mode, progress-bill and unallocated-cash switches, aging boundaries — comparing each run against a stored baseline:

Every run and every result is logged with who, when, and what failed. Manual tie-out proved v2 was right once; the harness proves the whole set is still right after every change.

What it taught — and why it was set aside

The contract idea works, and the exercise proved something real: describe the shape once and the mechanics can be generated, validated, and pointed at a different environment by editing one config row. But the costs surfaced with it. A table type was never meant to be a metadata store — the conversion logic hides inside computed-column definitions where no tooling expects it, debugging happens inside generated strings, and every reader must first learn the convention before they can read the system. The cleverness charges rent.

What survived is the substance without the vehicle: the tripwire guards, the changed-rows-only MERGE under binary collation, the config-driven source, the event vocabulary, and — most durably — the regression harness. Reproducing a report once is an achievement; keeping six of them provably correct while the design keeps changing is an operating capability.

The contract, inverted and hardened

Reading a v4 contract, the warehouse shape is the visible layer and the source is the implementation detail. The rules got real: required references, zero-or-positive IDs, non-empty names, a date floor — and a composite business key, unique job name per client, expressed as the type’s UNIQUE constraint:

CREATE TYPE [contract].[jobs] AS TABLE(
    [##external_schema_name]  AS ('dbo'),
    [##external_object_name]  AS ('tblJob'),
    [#JobID] [int] NOT NULL,
    [#ClientID] [int] NULL,
    [#JobTypeID] [int] NULL,
    [#Name] [varchar](100) NULL,
    [#CreateDate] [datetime] NULL,
    -- …
    [id]          AS ([#JobID]),
    [client_id]   AS (isnull([#ClientID],(0))),
    [jobtype_id]  AS (isnull([#JobTypeID],(0))),
    [name]        AS (isnull(CONVERT([nvarchar](100),[#Name]),'')),
    [createddate] AS (CONVERT([date],isnull([#CreateDate],'1900-01-01'))),
    [createdtime] AS (CONVERT([time](3),isnull([#CreateDate],'1900-01-01'))),
    PRIMARY KEY CLUSTERED ([#JobID] ASC, [id] ASC),
    UNIQUE NONCLUSTERED ([client_id] ASC, [name] ASC),
    CHECK (([#ClientID] IS NOT NULL AND [#ClientID]>(0))),
    CHECK (([#JobID]>(0))),
    CHECK (([#JobTypeID] IS NOT NULL AND [#JobTypeID]>(0))),
    CHECK (([#Name] IS NOT NULL AND [#Name]<>'')),
    CHECK (([#CreateDate] IS NULL OR [#CreateDate]>='1900-01-01'))
)

Checks that run before the merge

This is the version’s central move. The generator reads every CHECK constraint off the contract and emits an assertion that runs against the staged extract — after the pull, before the merge. A violation stops the load with an error that names the contract, the source object, and the failed expression:

if exists (select * from {temp_table_name} where case when {check_definition} then 1 else 0 end = 0) begin;
    throw 50000, 'ERROR: Contract "{contract_name}" failed source check against
        "{external_schema_name}.{external_object_name}". Check expression: {check_definition_message}', 0;
end;

One subtlety worth noticing: case when … then 1 else 0 end = 0 counts an UNKNOWN result as a failure. That is deliberately stricter than a native CHECK constraint, where UNKNOWN passes — the assertion refuses the benefit of the doubt a table would grant. The lineage is visible: v1’s tripwire guarded rows, v3’s generator guarded its own configuration, and v4’s checks guard the source data itself, by name.

Virtual, debugger, console

The rename from extracted to virtual is a philosophy: the local copy is disposable. The app.virtualize procedure drops every virtual table and rebuilds the set from the contracts, then reports tables dropped, created, and extracted. Two switches in app.config support the workflow. With virtual_debugger on, each extraction also snapshots the raw, pre-transform pull — so when a check fails, the offending source rows are sitting in a keyed table instead of gone with the temp table:

if exists (select * from app.config t where t.virtual_debugger = 1) begin;
    drop table if exists [virtual_debugger].{contract_name};

    select *
        into [virtual_debugger].{contract_name}
        from {temp_table_name};

    alter table [virtual_debugger].{contract_name} add constraint
        [virtual_debugger.{unquoted_contract_name}!!!{debug_table_primary_key_pluses}]
        primary key clustered ({debug_table_primary_key_cols});
end;

With console on, every generated command is echoed line by line before it runs — a verbosity switch stored in data rather than sprinkled through code.

The database records its own evolution

A database-level DDL trigger shreds EVENTDATA() into a typed app.eventdata log — event type, login, object, target, the full command text — alongside the raw XML. Every CREATE, ALTER, and DROP in the exploration is on the record, with who and when. The trigger runs with execute as ’dbo’ so the logging insert succeeds no matter which principal issued the DDL — a small clause carrying a real security-context decision.

What it taught

Rules belong at the border, and they should name themselves. A load that fails with “contract jobs failed its ClientID check against dbo.tblJob” is a diagnosis; a constraint violation three layers later is a mystery.

A warehouse you can drop and regenerate changes how you debug. Nothing in the virtual schema is precious, so experiments are cheap — and the debugger snapshot means a failed check leaves evidence instead of a vanished temp table.

Observability is a feature of the pipeline, wanted from the start: the console switch, the run summary, and the DDL event log all exist so the system can explain what it did.

And the cost curve kept climbing. The contracts now carry columns, conversions, keys, and a rulebook — each one a small specification language to learn. The trajectory bends visibly toward v5’s question: how much of this ceremony survives contact with the desire for something simpler?

The pendulum lands on local

The source-pointer question had swung across the versions — v1 used a source schema of synonyms, v3 replaced them with a config table and four-part names — and it settles here on the simplest reading experience: synonyms in dbo, so every query looks like the STAR tables live in the warehouse database.

create synonym dbo.tblJob   for [STARSERVER].[StarDB].dbo.tblJob;
create synonym dbo.tblWIP   for [STARSERVER].[StarDB].dbo.tblWIP;
-- … one per source object; repoint the set to move environments

A semantic extract layer

This is where the layer stops being a cleaner copy of STAR and starts speaking the business. Rows are typed — extract.nominal and extract.wip carry a type column, so report logic reads like English: where n.type = ’invoice’, where w.type = ’writeoff’. The isnull defaults, nvarchar(100) standardization, and 1900-01-01 date floor survive from v1; the zero-rows and tripwire WHERE clauses do not. The charge/non-charge question gets four explicit lenses on the jobs view instead of one folded flag:

, jobtype_chargeable              = isnull(convert(bit, case when isnull(jt.NonChargeable, 0) = 0
                                        and isnull(jt.NonChReporting, 0) = 0 then 1 else 0 end), 0)
, jobtype_chargeable_nonreporting = isnull(convert(bit, case when isnull(jt.NonChargeable, 0) = 0 then 1 else 0 end), 0)
, jobtype_chargeable_reporting    = isnull(convert(bit, case when isnull(jt.NonChReporting, 0) = 0 then 1 else 0 end), 0)
, is_reportable_job               = isnull(convert(bit, case when isnull(jt.NonChargeable, 0) = 0
                                        and isnull(jt.NonChReporting, 0) = 0 then 1 else 0 end), 0)

A new extract.wip_movements view unifies WIP-to-WIP activity — allocations and transfer roles — under one documented column contract:

/*  extract.wip_movements — only WIP-to-WIP movements:
    - type tells you which semantic movement row this is.
    - wip_id is the primary WIP row this movement is about.
    - targetwip_id is the paired/counterpart WIP row when one exists.
    - date/time/period_id describe the movement date, not the WIP row date.
    - agingdate describes the primary WIP row's aging date.            */

GREATEST earns real work inside the balance-allocation logic — deciding which of three periods wins, and then fetching the winner’s own date by matching each candidate back against the decision:

greatest(
      case when n.PeriodID      = greatest(n.PeriodID, target.PeriodID, s.period_id) then npd.date end
    , case when target.PeriodID = greatest(n.PeriodID, target.PeriodID, s.period_id) then targetnpd.date end
    , case when s.period_id     = greatest(n.PeriodID, target.PeriodID, s.period_id) then cast(rahpd.periodstartdate as datetime) end
)

One more small idea worth stealing: extract_base.configuration is a one-row view that derives its values — latest period, latest period date, the first un-perioded date — from the data itself. Configuration as a computed fact instead of a maintained table.

Six reports, rebuilt on meaning

All six reports exist as procedures over the semantic layer, and each hardens its inputs before any logic runs: dates are normalized to midnight, @period is trimmed, uppercased, and validated, and Period mode throws by name when the requested dates cannot resolve to reporting periods. The tripwire spirit from v1 returns as plain, early THROWs:

declare @period_normalized varchar(10) = upper(trim(isnull(@period, '')));

if @period_normalized not in ('DATE', 'PERIOD') begin;
    throw 50000, 'report.billings @period must be Date or Period.', 0;
end;

if @period_normalized = 'PERIOD'
and (@minperiod_id is null or @maxperiod_id is null) begin;
    throw 50000, 'report.billings could not resolve @firstdate/@lastdate to reporting periods.', 0;
end;

The report bodies read the way the v2 documentation promised they could. The billings proc keeps the STAR report’s useful grain — job, bill reference, bill date — and its CTEs name their meaning: bills selects type = ’invoice’ rows, profits sums the type = ’writeoff’ WIP matched back to each bill. Semantics discovered in v2 became columns in v5, and the report logic collapsed into readable set operations over them.

The honest cost stands: everything runs live, through synonyms, across the linked server — every report call pays the full extraction price. Correct first, fast next.

What it taught

Deletion is a design act. This version kept the guarantees that earn their keep daily — early, named failures and per-firm semantics expressed as columns — and shed the ceremony whose maintenance cost exceeded its protection: contracts, generators, zero-rows, the tripwire WHERE. Every simplification is a trade; this one was made looking at the price tags.

When the view is the contract, there is nothing to keep in sync with it. v3 and v4 described shapes in metadata and generated code from the description; here the view is the description, and the reports read it directly.

Several unnumbered offshoots of this version came from letting an AI agent explore autonomously. One sibling went a different direction entirely — a persisted warehouse schema with a generic, signature-checking sync engine. It was reviewed and set aside: ideas worth reading, code that was not kept, which is a fair summary of what unsupervised generation currently delivers.

The detail is the report

Most shops write the summary first and bolt on a detail view later — and the two drift, because they are two implementations of one idea. Here drift is structurally impossible: there is one implementation, and the summary consumes it.

create procedure [report].[billings]
        @firstdate  datetime
    ,   @lastdate   datetime
    ,   @period     varchar(10) = 'Period'
as begin;
    set nocount on;

    create table #detail
    (
          source_type   varchar(20)
        , nominal_id    int
        , wip_id        int
        , JobID         int
        , BillRef       varchar(200)
        , [Date]        datetime
        , Net           decimal(19,4)
        -- …
    );

    insert into #detail
        exec report.billings_detail
              @firstdate = @firstdate
            , @lastdate  = @lastdate
            , @period    = @period;

    select JobID, BillRef, [Date]
         , Net = convert(decimal(19,4), sum(Net))
         -- …
        from #detail
        group by JobID, BillRef, [Date], TranDate;
end;

Notice the first three columns of every detail row: source_type, nominal_id, wip_id. That is provenance — each row names the STAR rows it came from. A partner’s question about a number becomes a filter on the detail, and the detail answers with IDs you can look up. The drill path is a column set, not a feature.

What it taught

One implementation per report. When the summary is a GROUP BY over its own detail, the two can never disagree — and the expensive audit question, “why doesn’t the detail tie to the summary?”, is retired by construction.

Provenance is what makes a number trustworthy. A figure that carries the IDs of the rows behind it can be interrogated; a figure that only carries its own value has to be believed.

And the honest cost that sets up v7: this version runs live, through synonyms, across a linked server — every report call pays the full extraction price. Correct first, fast next. Proving the semantics before persisting anything was the sequence on purpose.

Extraction that ships its own source

The generic etl.extract_object procedure needs no metadata at all. It reads the extract function’s own definition out of the catalog, peels the query body with a regular expression, and ships that text to the linked server inside OPENQUERY — so the remote STAR server executes the whole query and only finished rows cross the wire:

CREATE procedure [etl].[extract_object] @object_name sysname, @primary_keys int = 1 as begin; set nocount on;
    declare @object_id int = object_id('extract.' + quotename(@object_name), 'IF');
    if @object_id is null throw 50000, 'ERROR:  Not found.', 0;

    declare @definition nvarchar(max) = char(9) + regexp_substr(object_definition(@object_id),
        N'\breturns\s+table\s+as\s+return\s*\(\s*(.*)\s*\)\s*;?\s*$', 1, 1, 'is', 1);

    declare @sqlcommand nvarchar(max) = ''
        + 'select * into extracted.' + quotename(@object_name) + ' from openquery(STARDBSERVER, ''' + char(13) + char(10)
        + replace(@definition, '''', '''''')
        + ''');';
    execute sys.sp_executesql @sqlcommand;

    declare @keys nvarchar(max) = (
        select string_agg(cast(quotename(t.name) as nvarchar(max)), ', ')
            from sys.columns t
            where t.object_id = object_id('extracted.' + quotename(@object_name)) and t.column_id <= @primary_keys
    );
    execute sys.sp_executesql N'alter table extracted.' + quotename(@object_name) + ' add primary key clustered (' + @keys + ');';
end;

The whole v3 apparatus — contract types, reflection views, a template engine — collapses into two conventions: the extract function is the specification, and the key columns come first in its select list, so @primary_keys is just a count. One honest wrinkle: OPENQUERY requires a literal linked-server name, so the proc pins it — the single place the environment leaks into code. And note regexp_substr: this is SQL Server 2025; earlier versions would do the same peel with CHARINDEX arithmetic.

Corrections: reversing the future to see the past

A WIP balance today is easy; a WIP balance as of a past date means undoing everything that happened since. Two tiny functions do exactly that. Transfers after the cutoff are unwound by emitting a ± pair against both sides of the movement; allocations handle two asymmetric windows — a future bill gives the amount back to the allocated row, and an already-billed row whose allocation landed after the cutoff gives it back to the bill:

CREATE function [transform].[wip_transfer_corrections](@lastDate date) returns table as return (
    select x.wip_id, t.date, x.amount
        from transform.wip_transfers t
            cross apply (values (t.original_id, -t.contra_amount), (t.contra_id, t.contra_amount)) x(wip_id, amount)
        where t.date > @lastDate and t.bothExist = 1
);

CREATE function [transform].[wip_allocation_corrections](@lastDate date) returns table as return (
    select t.allo_id as wip_id, t.bill_date as date, t.amount
        from transform.wip_allocations t
        where t.bill_date > @lastDate and t.bothExist = 1
    union all
    select t.bill_id as wip_id, t.allo_date as date, -t.amount as amount
        from transform.wip_allocations t
        where t.bill_date <= @lastDate and t.allo_date > @lastDate and t.bothExist = 1
);

Both filter on bothExist = 1 — and that flag is the other idea of this layer. Every joined entity in the transform views carries visible diagnostics: _exists, _orphan, jobsDiffer, hasOrphans. v1 verified IDs by silently zeroing them; v7 states the data-quality facts as columns and lets the corrections trust only verified pairs. The transfers view also settles the “when did this movement really happen?” question with GREATEST across all three touched dates — composing each date with its time to make the comparison honest:

, date = isnull(greatest(t.date, o.date, c.date), '1900-01-01')
, time = isnull(cast(greatest(
              dateadd(day, datediff(day, '19000101', t.date), cast(t.time as datetime2))
            , dateadd(day, datediff(day, '19000101', o.date), cast(o.time as datetime2))
            , dateadd(day, datediff(day, '19000101', c.date), cast(c.time as datetime2))
        ) as time(3)), '00:00:00.000')

The report is a function

v6’s pairing idea returns, upgraded: summary and detail are inline table functions, so the summary composes over the detail with no temp table and nothing the optimizer cannot inline — and the report itself can be joined, filtered, and reused like any other table:

CREATE function [report].[wip_reconciliation](@firstDate date, @lastDate date) returns table as return (
    select t.job_id
            , hours   = isnull(cast(sum(t.hours) as decimal(19,4)), 0)
            , opening = isnull(cast(sum(t.opening) as decimal(19,4)), 0)
            -- … time, expenses, adjustments, bills, writeoffs, allowances …
            , closing = isnull(cast(sum(t.closing) as decimal(19,4)), 0)
        from report.wip_reconciliation_detail(@firstDate, @lastDate) t
        group by t.job_id
);

Inside the detail, the as-of arithmetic completes the thought: the corrections net into an adjusted balance, closing is that balance as at @lastDate, and opening is not stored anywhere — it is derived, closing − (writeoffs + bills + adjustments + expenses + time). A zero-delete filter then drops rows where every bucket and the closing are zero. Nothing in the reconciliation exists twice, so nothing can disagree with itself.

What it taught

Convention beats metadata. Two agreements — the function is the spec, keys come first — replaced the entire contract apparatus of v3 and v4, and object_definition did the rest. The exploration’s central tension resolves here: describe things once, but describe them in the code itself.

As-of correctness is arithmetic, not storage. Reconstructing a past balance by reversing subsequent activity — and deriving opening from closing by identity — means the point-in-time numbers cannot drift from the current ones, because they are computed from them.

And finishing one report end to end taught more than sketching six. The narrowed scope is what made the corrections, the diagnostics, and the composable report shape possible — and it sets up v9’s question: now that one pipeline is right, what does it take to generalize it?

Parameterize the question, not the computation

v7’s correction functions took @lastDate as a parameter — every as-of question re-ran the computation. v8 removes the parameter. Each correction is emitted once as an event carrying its own identity, its signed amount, and its gates: the datetime and period that decide whether it applies to any given cutoff. The asymmetric allocation case gets a second gate:

CREATE function [transform].[report_wip_recon_corrections]() returns table as return (
    -- transfer original: -contra; gate = transfer datetime / contra period
    select
              event_key         = concat_ws('|', 'TO', t.batch_id, t.original_id, t.contra_id)
            , wip_id            = t.original_id
            , amount            = -c.amount
            , source            = cast('transfer_orig' as varchar(20))
            , gate_datetime     = t.[datetime]
            , gate_period_id    = t.contra_period_id
            , gate2_datetime    = cast(null as datetime2(3))
            , gate2_period_id   = cast(null as int)
        from extracted.wip_transfer t
            inner join extracted.wip c on t.contra_id = c.id
    union all
    -- … transfer contra, allocation allo-side …
    union all
    -- allocation bill-reversal: -amount; gate = allo (after), gate2 = bill (on/before)
    select
              concat_ws('|', 'AB', t.bill_id, t.allo_id, convert(varchar(23), t.datetime, 121))
            , t.bill_id
            , -t.amount
            , cast('alloc_bill' as varchar(20))
            , t.allo_datetime
            , t.allo_period_id
            , t.bill_datetime
            , t.bill_period_id
        from extracted.wip_allocation t
);

The date test moves to read time. One persisted stream — transformed.report_wip_recon_corrections, indexed on its gates — now answers every as-of question ever asked of it:

with adjustments_by_wip as (
    select e.wip_id, sum(e.amount) as allocation
        from transformed.report_wip_recon_corrections e
        where (
                @mode = 'date'
                and e.gate_datetime > @lastDate
                and (e.gate2_datetime is null or e.gate2_datetime <= @lastDate)
            )
            or (
                @mode = 'period'
                and e.gate_period_id > @lastPeriod_id
                and (e.gate2_period_id is null or e.gate2_period_id <= @lastPeriod_id)
            )
        group by e.wip_id
        having sum(e.amount) <> 0
)

Note the null-pass: gate2 is populated only for the bill-reversal branch, so the on-or-before test passes trivially for every other event. And the reports themselves grew up to match — one signature serves both Date and Period mode, and the aging boundaries became parameters with defaults, @age1 int = 30 through @age5 int = 150.

Touch STAR once

Extraction split into two stages. Stage one mirrors just the needed columns of each STAR table into local dbo twins — server and database names read from app.config, column lists passed as JSON and shredded with OPENJSON in declared order. Stage two runs the extract functions against that local mirror, at local speed. STAR is queried exactly once per load; everything else never leaves the building:

-- the load script is the manifest: table, columns, key — visible, per object
execute etl.extract_external_star_object 'dbo', 'tblJobType',
    '["JobTypeID", "Description", "Hide", "NonChargeable", "NonChReporting"]', '["JobTypeID"]';

execute etl.extract_external_star_object 'dbo', 'tblWIP',
    '["WIPID", "JobID", "PeriodID", "ChargeTypeID", "ChargeAcID", "StaffID", "Approved", "Date", "TSDay", "Units", "Amount", "Billed"]', '["WIPID"]';

execute etl.extract_external_star_object 'dbo', 'tblWAH',
    '["BillWIPID", "AlloWIPID", "WADate", "Allocation"]', '["BillWIPID", "AlloWIPID", "WADate"]';

This also retires v7’s one wrinkle: OPENQUERY needed a literal server name baked into code, while the mirror stage reads both server and database from a one-row config table. The proc’s own Todo comment is honest about maturity — today it drops and re-dumps; the staged-twin naming (tblJob_) and a merge are the stated intent.

The physical pass

This is also the version where the persisted layer gets deliberately indexed, and the naming taxonomy completes: !!! for primary keys, !! for unique indexes, ! for nonclustered, with + joining composite parts:

CREATE UNIQUE NONCLUSTERED INDEX [extracted.job!!client_id+name] ON [extracted].[job] …
CREATE NONCLUSTERED INDEX [extracted.wip!effectiveDateTime] ON [extracted].[wip] …
CREATE NONCLUSTERED INDEX [transformed.report_wip_recon_corrections!gate_datetime] ON …

v4’s unique-job-name-per-client rule is now a real index; v7’s effective date is a persisted, indexed column; the correction gates are indexed because the gates are what every query filters on. And one workbench quirk worth smiling at: tables named entirely with underscores exist in each schema — visual dividers for Object Explorer, nothing more.

What it taught

Parameterize the question, not the computation. Moving the cutoff from the function’s parameter to the reader’s WHERE clause turned a calculation into an asset — computed once, indexed on its gates, able to answer any as-of question without recomputation. That single move is what makes point-in-time reporting cheap.

Mirror, then transform. Reaching through a linked server on every query was v6’s honest cost; pulling a column-selective mirror once and running everything locally is the answer, and it shrinks the environment’s footprint in code to two config values.

And the reason this version matters despite being skipped on the call: v9’s incremental synchronization only makes sense against exactly this shape. A gate-indexed, mirror-fed, persisted layer is the thing rowversion will keep current.

The registry is the metadata, the log, and the watermark

v3 described sources in table types; v8 described them in a script of JSON parameters; v9 finally puts them where descriptions belong — in rows. One row per target: where it comes from (a 1-to-4-part name, with the server and database falling back to app.config when omitted), what it carries (keys and columns as JSON arrays), how it loads (enabled, incremental, watermark column), and what happened last time (counts, dates, and the high-water mark itself):

CREATE TABLE [app].[datasources](
    [target_object_name] [sysname] NOT NULL,
    [source] [nvarchar](800) NOT NULL,
    [keys] [nvarchar](max) NOT NULL,        -- json array
    [columns] [nvarchar](max) NOT NULL,     -- json array
    [enabled] [bit] NOT NULL,
    [incremental] [bit] NOT NULL,
    [watermark_name] [sysname] NULL,        -- the rowversion column
    [watermark_value] [binary](8) NULL,     -- the high-water mark
    [loadDate] [datetime2](7) NULL,
    [loadedDate] [datetime2](7) NULL,
    [insert_count] [int] NULL,
    [update_count] [int] NULL,
    [delete_count] [int] NULL,
    CONSTRAINT [app.datasources!!!target_object_name] PRIMARY KEY CLUSTERED ([target_object_name] ASC)
)

The registry doubles as the run log: the executor stamps loadDate going in, and writes back the new watermark, the finish time, and insert/update/delete counts coming out. Asking “when did jobs last load and what did it do?” is a one-row SELECT.

A view that refuses bad configuration

app.datasources_view turns each row into finished commands through a chain of CTEs — and its first layer is the tripwire idiom’s final form. Every validation failure poisons the offending column with a cast error whose message names the target and states the fix. A misconfigured row cannot be read quietly:

, keys = case
            when isjson(isnull(t.keys, ''), array) = 0
                then cast(cast(concat('[', t.target_object_name, '] keys is not a json array') as int) as nvarchar(max))
            when json_value(t.keys, '$[0]') is null
                then cast(cast(concat('[', t.target_object_name, '] keys is empty') as int) as nvarchar(max))
            when exists (select * from openjson(t.keys) x where nullif(trim(x.value), '') is null or x.type <> 1)
                then cast(cast(concat('[', t.target_object_name, '] keys has a blank or non-string element') as int) as nvarchar(max))
            else t.keys
        end

The same treatment covers blank config values, non-parseable source names, empty column lists — and one rule that shows the design thinking: the columns list must not contain the watermark column, because the view appends it everywhere it belongs. PARSENAME decomposes the source, later layers aggregate the JSON into quoted lists, join predicates, and update sets, and the final layer assembles complete commands — including the primary-key name built in the !!! taxonomy with + joining composite parts.

Rowversion does the heavy lifting

The watermark is a binary(8) rowversion, rendered as a hex literal and pushed into the remote query, so the source server returns only rows that changed since the last sync. Three generated fragments carry the whole incremental design:

, is_incremental  = case when t.incremental = 1 and t.watermark_value > 0x0000000000000000 then 1 else 0 end
, openquery_where = case when t.is_incremental = 1 then ' where ' + t.watermark_qname + ' > ' + t.watermark_svalue else '' end

, matched_clause  = case when t.watermark_name is not null
                        then 'when matched and (t.' + t.watermark_qname + ' is distinct from s.' + t.watermark_qname + ') then update set ' + t.update_set
                        else 'when matched then update set ' + t.update_set
                    end
, delete_clause   = case when t.is_incremental = 0 then 'when not matched by source then delete' end

Each line encodes a decision. A watermark of zero means the first load runs full — incremental only engages once a high-water mark exists. The matched test compares only the watermark column: rowversion changes on any update, so one comparison replaces v5-era per-column change detection. And the delete clause exists only for full loads, because an incremental pull cannot see rows that vanished — the limitation is encoded, not ignored; this is exactly the trade the CDC discussion below completes. The target table itself is typed by a select top 0 shipped through OPENQUERY, and after each merge the executor reads max(watermark) back into the registry:

declare @sqlcommand nvarchar(max) = 'select @watermark_value = max(' + quotename(@watermark_name) + ') from ' + @target_path + ';';
exec sys.sp_executesql @sqlcommand, N'@watermark_value binary(8) output', @watermark_value = @watermark_value output;

update t set t.watermark_value = @watermark_value, t.loadedDate = sysdatetime()
           , t.insert_count = @insert_count, t.update_count = @update_count, t.delete_count = @delete_count
    from app.datasources t where t.target_object_name = @target_object_name;

What it taught

This version is a synthesis, and it is visible line by line. v1’s tripwire became per-cell configuration validation that ships its own fix instructions. v3’s contract registry returned as ordinary rows in an ordinary table. v7’s remote execution survived, with the literal-server problem solved by generating the literal from config. v8’s JSON manifests became data. The naming taxonomy holds throughout. The exploration’s survivors all report for duty in two hundred and fifty lines.

Rowversion is the cheapest honest answer to “what changed?” — one monotonic column, one stored high-water mark, one WHERE clause the source server can satisfy with an index. Its price is equally honest: it cannot see deletes, which is why full loads keep the delete branch and why the CDC question from the chat is the right companion discussion.

And the operational lesson that closes the arc: when the registry is also the log, the system explains itself. Metadata, execution, and history live in one row per source — which is the property every earlier version was reaching for.

Templates you can read, a vocabulary that fills them

Every generator so far hid the SQL inside itself — v3 in a view, v9 in a CTE chain. v10 inverts that. The merge templates sit in plain sight at the top of each procedure, and app.bind(@value, @object_id) fills their tokens from config and the catalog. The function precomputes column lists in every flavor a template could want — plain, aliased, and as comparison predicates joined by commas, ANDs, or ORs — for all columns, key columns, and non-key columns:

, t_qname_eq_s_qname    = concat('t.', quotename(t.name), ' = ', 's.', quotename(t.name))
, t_qname_idf_s_qname   = concat('t.', quotename(t.name), ' is distinct from ', 's.', quotename(t.name))
, t_qname_indf_s_qname  = concat('t.', quotename(t.name), ' is not distinct from ', 's.', quotename(t.name))
-- aggregated per flavor: {columns_…}, {pkcolumns_…}, {dcolumns_…} × (', ' | ' and ' | ' or ')

The payoff is that stage two of the pipeline reads exactly like the SQL it becomes — the local merge from a datasource function into its data table:

declare @sqlcommand nvarchar(max) = '
merge data.{object_qname} with (holdlock) t
    using datasource.{object_qname}() s on {pkcolumns_t_qname_eq_s_qname_and}
    when matched and ({dcolumns_t_qname_idf_s_qname_or})
        then update set {dcolumns_t_qname_eq_s_qname}
    when not matched by target
        then insert ({columns_qname})
            values ({columns_s_qname})
    when not matched by source
        then delete;
';
set @sqlcommand = app.bind(@sqlcommand, @object_id);

The rowversion fence

v9 read its high-water mark from the target after loading. v10 asks the source a better question before loading — STAR’s own min_active_rowversion(), fetched remotely and stored in config as the fence for the whole run:

declare @sqlcommand nvarchar(max) = app.bind(
    'execute {star_server_qname}.{star_db_qname}.sys.sp_executesql
        N''set @rowversion = min_active_rowversion();'',
        N''@rowversion binary(8) output'', @rowversion output;', default);
execute sys.sp_executesql @sqlcommand, N'@rowversion binary(8) output', @rowversion output;
update t set t.star_rowversion = cast(@rowversion as bigint) from app.config t where t.id = 0;

The distinction is correctness, not style. The largest loaded value can sit above rows whose transactions were still in flight when you read — sync past them once and they are skipped forever. min_active_rowversion() is the safe ceiling: everything below it is settled. Each table then pulls a half-open window and moves its own mark up to the fence:

-- per table: [last fence, current fence)
' where [TimeStampField] >= ' + convert(varchar(18), cast(@min_rowversion as binary(8)), 1)
+ ' and [TimeStampField] < '  + convert(varchar(18), cast(@max_rowversion as binary(8)), 1)

-- incremental runs comment the delete branch out of the template:
set @sqlcommand = replace(@sqlcommand, '<delete>', iif(@incremental = 0, '', '; --'));

-- and a table whose mark already equals the fence is skipped entirely:
case when t.incremental = 1 and cfg.star_rowversion is not distinct from t.min_rowversion then 1 else 0 end

The small print carries the same care: a first load (mark of zero) truncates and pulls full with the delete branch alive; the STAR timestamp column is cast to binary(8) inside the remote select so OPENQUERY lands it cleanly; and the incremental delete limitation from v9 is still honest — now visible as a branch the template literally comments out.

Convention finishes the registry

The registry slims to behavior and history — structure moved into the tables themselves. Create a mirror table in source and step 01 auto-registers it; create a data table with a matching datasource function and stage two is wired. The row that describes a source is also its run log:

CREATE TABLE [app].[sources](
    [name] [sysname] NOT NULL,
    [incremental] [bit] NOT NULL,
    [min_rowversion] [bigint] NOT NULL,
    [executions] [int] NULL,
    [executingDate] [datetime2](7) NULL,
    [executedDate] [datetime2](7) NULL,
    [affectedRows] [int] NULL,
    CONSTRAINT [app.sources!!!name] PRIMARY KEY CLUSTERED ([name] ASC)
)

app.ping runs the numbered steps in order — register, fence, sync — and the double-underscore names make the runbook read like its own documentation: step_01__sources__insert_missing, step_02__config__set_star_rowversion, step_03__sourcetables__sync.

What it taught

The generator became a language. Ten versions of machinery for producing SQL end with the SQL back in plain sight and one function supplying the lists — the v3 idea, kept, with its cost finally paid down.

Correctness lives in fences. min_active_rowversion() is the difference between a sync that is usually right and one that is right — and finding details of that size is what an exploration like this is for.

And one table end to end, on purpose. The foundation is proven when the pattern survives a full trip, not when every table is wired — which is exactly where this work stands, and where the next chapter picks up.

Change Data Capture

Change Data Capture is SQL Server’s log harvester. Enabled per database and per table on the source, a SQL Agent job reads the transaction log and lands every insert, update, and delete in a generated change table — operation codes, the before and after images of updates, LSN ordering — which consumers read between two LSNs. Mapped onto v9 and v10: the LSN range is the fence-to-fence window, the net-changes function is the incremental pull, and the change table is a richer merge input — richer because it carries the two things rowversion cannot: deletes, and the full history of intermediate states. The price is machinery on the source: Agent jobs, log-retention pressure, cleanup, and a schema-change story.

And that price names the reason it does not fit at the source. STAR is a vendor database this warehouse touches with SELECT only — no writes, no DDL, no enabled features — while CDC (and its lighter sibling, Change Tracking, which supplies just changed-and-deleted keys since a version) must be switched on at the source. Rowversion won because STAR already ships it: the TimeStampField columns were there to exploit, with zero source footprint. Choose CDC when you own the source and need change history itself — auditing, intermediate states, replayable streams.

The one gap rowversion leaves — deletes under incremental loads — is handled operationally in the versions that implement it: each registry row’s stored watermark can be reset to zero, which forces a truncate and full re-pull of that table, reconciling deletes by construction. (The mark is kept as bigint in the current versions for easier debugging; in production form it is binary(8).) Pulling just the primary keys and anti-joining would be the more efficient incremental answer — but since these extracts carry only a handful of columns per table, the versions to date kept it simple.

A standing caveat belongs here: these warehouse versions are experiments in progress — by no means complete or ready for use — and future versions will continue until one design, drawn from all of these experiments, is solid enough to stand on.

The suggestion may also have pointed the other direction — CDC on the warehouse itself, not the source — and that reading has real merit. It is worth exploring toward the end, once the table and column storage solidifies; for the current experimental versions that overhead is deferred. But it stays on the list, either as the built-in feature or as a home-grown equivalent that sidesteps its constraints: surviving schema changes without capture-instance churn, keeping change data as a first-class citizen of the warehouse that can be indexed and optimized on its own, and comparing values so an update that changes nothing produces no change record.

Query Store

Query Store is a per-database flight recorder: query texts, every plan each query has carried, runtime statistics and waits, stored inside the database itself under a size budget and queryable through the sys.query_store views. It is a different category from CDC — instrumentation rather than pipeline — and it runs on the side of this architecture we own: the warehouse databases, never STAR.

It fits this work in two places. During the exploration, it turns “this version felt slower” into measured plan-and-duration differences per version. In a production install, it is the answer when a STAR upgrade or a statistics change flips a critical extraction query onto a worse plan — the history shows before and after, and a plan can be pinned while the root cause gets fixed. Since SQL Server 2022 it is on by default for new databases; the warehouse databases here keep it on.

From the group: caching statistics back into STAR

One member described computing monthly statistics and storing the results back into STAR custom fields so historical values stay exactly as they were reported. That is the same instinct the warehouse formalizes — a frozen period is a set of numbers that never changes underneath you — and it was good validation that the precompute theme matches how firms already protect their history. It also points at the roadmap: once these warehouse versions reach a mature, ready milestone, the natural next step is implementing history in a reliable way, so the warehouse gains time-travel capabilities of its own.