DEV Community

jacob baruch
jacob baruch

Posted on Originally published at goagentdata.com AI-assisted

Do you need bronze, silver and gold layers? Keep three things instead

TL;DR. Probably not, if you are a startup with no data person. The three layers, called the medallion architecture, are a recommended practice, not a requirement, and the real work of the middle one, making two systems agree, can start as one mapping table. What you should not skip are three things that are often impossible to bring back later: an untouched copy of data as it arrived, one model that keeps history instead of overwriting it, and written definitions your coding agent can read. Add a layer when mappings multiply or a second consumer with different needs shows up, not before.

Layers are optional, three things are not: what arrived, what was true when, what it means
Illustration, not measured data.

What is the layer cake, and what is it for?

Data platforms often sort data into three layers. Bronze holds data as it arrived. Silver holds it cleaned and made to agree across sources. Gold holds tables shaped for reports. Databricks, whose platform made the pattern popular, calls it a recommended best practice but not a requirement.

The silver layer does real work. Picture a startup that moved billing from PayPal to Stripe in March. Two systems now hold its revenue, with two customer IDs and two spellings of each plan. A yearly revenue number needs both, matched to one customer. The same happens when the app and the CRM each keep their own customer list. That work does not go away when someone calls layers unnecessary.

The question for a startup is narrower: does that work need a whole layer of copied tables, or one mapping table and a view? Before your first data person, I think it is usually the second.

What can you not skip?

I think the layers are optional and three things are not. Each one protects something you cannot rebuild later.

1. A copy of what arrived, never edited. Your billing tool sends an event. Your code reads it, converts it and saves the result. Picture a startup whose code read amounts in cents as dollars for two months. If only the converted numbers were kept, the history of revenue is wrong and stays wrong. If the original events were kept as they arrived, you fix the code and rebuild.
Guardrail: store every incoming event or file in one table, as it arrived, with the time it arrived and where it came from. Engineers call this an append-only landing table. Create it as the admin user, so your app cannot update or delete rows there.
Check: try to change one row with the login your app uses. It must fail.

2. One model that keeps history. Your app stores a customer's plan in one field and overwrites it when the plan changes. A month later someone asks how many customers paid on the first of October. The app cannot answer, because the old value is gone. We ran this on a small test database on 20 November 2026: today's table showed no paying customers, while the history table showed two on the date asked. No agent can recover a value that was never kept.
Guardrail: keep a history table for the few things that change and matter, such as plan, price and status, with the date each value started and ended. Data engineers call this a type 2 slowly changing dimension.
Check: ask for last month's number for one customer. If nobody can answer, history is not kept.

3. Written definitions. An agent that reads your tables sees column names, not meaning. It does not know that a row is one paying account, not one person, or that test accounts should be left out.
Guardrail: one short file per important table, in your repository, that says what one row is and how the main numbers are counted.
Check: ask your agent "how many active customers do we have", then ask how it counted. Compare the answer with the file.

When does a layer earn its place?

A layer is worth its cost when it removes work that repeats. I think four situations justify one. Two systems describe the same thing in many places, so mapping tables multiply and every report repeats the same joins. A second consumer appears with different needs, such as finance wanting closed months that never change while product wants today's numbers. A heavy reshaping runs every hour and is cheaper to store than to repeat. Or someone outside the team, such as an auditor, must follow each step from source to report. A WhereScape recap of a practitioner panel, in August 2026, calls medallion a shared vocabulary, not a specification. WhereScape sells data warehouse automation. Its line is still a fair way to use the names: labels for stages, added when a stage is needed.
Check: count customers in billing with no match in the app. If the count is above zero and growing, start a mapping table. If no second team reads a table and nothing rebuilds it on a schedule, skip the layer.

What changes the answer is who consumes the data and what it costs to rebuild, more than company size.

Before launch. Keep the copy of what arrived, history for plan and status, and one line saying what a row in each table is. Skip everything else.

First paying customers. Add the definitions file for the tables your numbers come from. When a second system holds the same customers, after a billing migration or when sales starts using a CRM, add a mapping table. An agent can build the report tables on request from the history model.

Growing, with a first data person. That person decides which reshaped tables to store, because by now some questions repeat every week. They inherit raw data and history they could never have recreated.

For your data team

This part gets technical. The SQL below ran on PostgreSQL 16.

-- 1. What arrived: one table, rows only added.
-- Run this block as the admin user, not the app login.
create table raw_arrivals (
  id          bigserial primary key,
  received_at timestamptz not null default now(),
  source      text not null,          -- 'billing', 'crm', 'webhook:signup'
  payload     jsonb not null
);
-- The table belongs to the admin user, so the app login can add and read, nothing else.
grant insert, select on raw_arrivals to app_writer;
grant usage on sequence raw_arrivals_id_seq to app_writer;

-- 2. What was true when: history kept by the database, not by app code.
create table customers_history (
  id int, email text, plan text,
  valid_from timestamptz not null,
  valid_to   timestamptz,               -- null means current
  deleted    boolean not null default false
);
-- security definer: the trigger writes history with the owner's rights, so the app
-- does not need, and does not get, permission to edit the history table.
create function keep_customer_history() returns trigger language plpgsql
security definer set search_path = public as $$
begin
  if tg_op in ('UPDATE','DELETE') then
    update customers_history set valid_to = now()
     where id = old.id and valid_to is null;
  end if;
  if tg_op in ('INSERT','UPDATE') then
    insert into customers_history (id, email, plan, valid_from)
    values (new.id, new.email, new.plan, now());
  else
    insert into customers_history (id, email, plan, valid_from, deleted)
    values (old.id, old.email, old.plan, now(), true);
  end if;
  return null;
end $$;
create trigger customers_history_trg
after insert or update or delete on customers
for each row execute function keep_customer_history();

-- Run once, so today's values are the starting point.
insert into customers_history (id, email, plan, valid_from)
select id, email, plan, now() from customers;

-- Paying customers on a past date.
select count(*) from customers_history
where valid_from <= '2026-10-01' and (valid_to is null or valid_to > '2026-10-01')
  and not deleted and plan <> 'cancelled';
Enter fullscreen mode Exit fullscreen mode

A trigger keeps history even when a script or a teammate updates the table outside the app. It adds one insert per change, which is small for tables like customers and plans. Do not put it on a table that changes thousands of times a second. A deleted customer leaves a row marked deleted, so last month's counts stay right.

When two systems hold the same customers, one mapping table and one check keep them in line:

create table customer_map (
  source      text not null,          -- 'paypal', 'stripe', 'crm'
  source_id   text not null,
  customer_id int  not null,          -- your app's id
  primary key (source, source_id)
);

-- Billing customers no one has matched yet. Should be zero, or shrinking.
select payload->>'customer' as stripe_customer, count(*)
from raw_arrivals r
where source = 'billing'
  and not exists (select 1 from customer_map m
                  where m.source = 'stripe' and m.source_id = r.payload->>'customer')
group by 1;
Enter fullscreen mode Exit fullscreen mode

For the third item, a file such as data/definitions/customers.md with three lines is enough to start: what one row is, which accounts are excluded, and how "active" and "paying" are counted. Point your agent's instructions file at that folder.

In our test, a revenue table built with the cents bug summed to 6,800 dollars. Rebuilt from the raw copy with the fix, it summed to 68. An update to the raw table from the app login failed with "permission denied". One run on made-up data, to show the mechanism.

Check it in 10 minutes

  1. Ask where incoming billing and signup events are stored before your code changes them. If the answer is nowhere, add the arrivals table.
  2. Pick one field that changes and matters, usually plan or status, and ask whether its old values are kept. If not, add the history table and trigger above.
  3. Ask your agent how it counts active customers, and write its answer into a definitions file once a person has corrected it.
  4. No coding agent and no repository? This step is for you. Open your database host's SQL editor as the admin user and paste the second block, changing the column names to match your customers table, and run the last insert once. From then on, every plan change is kept, and the app can still save changes without being able to edit the history.
  5. The engineer who owns the app database owns these three items.

See where your setup stands with the two minute self-check, then read the data contracts post for checks on what each field means.

Originally published at goagentdata.com.

Top comments (0)