Training Data

Playbook Part 1: Clean Context, Sharp Answers (Why We Retire Old ETL Tables)

Đăng vào bởi Gong Elijah
Playbook Part 1: Clean Context, Sharp Answers: Why We Retire Old ETL Tables — Vatico Training

If you’ve started poking around the warehouse, you’ve probably noticed three generations of different tables—original ETL tables, ELT first-gen tables, and our current modern ELT tables (ODS → DIM/DWD → DWS → ADS)—layered on top of each other. While a human analyst can navigate this clutter with the right supervision, leftover legacy tables present a silent, structural risk to our automated agents: they make Vatico Bot confidently hallucinate wrong answers.

The Three Generations of Vatico’s Warehouse

We didn’t build our database layout in a single afternoon. Over the years, Vatico has transitioned through three distinct stages of data layering. To understand what tables you should query, you need to recognize which era they belong to:

Warehouse Evolution Stack — The Three Eras of Tables
⏳
1. ETL Era
Original legacy tables. Data was transformed before load.
→
🏗️
2. ELT First Gen
Early extract-then-load attempt. Flexible, but lacked the ability to easily make changes in one table without affecting other tables.
→
🛡️
3. Modern ELT
Current standard: data modeling + data layering (Raw (ODS) → Cleaned Facts & Dims (DWD/DIM) → Shared Metrics (DWS) → Applications (ADS)).

Note on our evolution: What makes the modern ELT standard work is a combination of data modeling and layering. The first-gen ELT became messy because we did not adapt data modeling; our thinking back then was adapted from Snowflake’s schema design, believing that as long as raw data was in the warehouse, business teams could transform it to whatever they needed on the fly. We later learned that traditional data modeling makes more sense, which we have now modernized through Alibaba’s data layering pattern.

The Core Problem: A Bot Lacks Human Context

As Elijah’s post on your first T job covers, Vatico Bot is exceptionally helpful for discovery — pointing you towards which tables might contain the data you need — but it has a fundamental limit: as of 29 Jul 2026, it does not actually understand how the business process works. It doesn’t know yet that we plan to retire some tables. It doesn’t know what tables are operationally active and which ones have been abandoned.

Here is how the bot’s view compares to your own view as an intern:

Vatico Bot’s View

Limited: The bot reads database schemas directly from dbt models and their corresponding .yml documentation, plus the CRM schema and whatever text was in the onboarding slides. It’s like a db engineer who can tell you the values of a table and column and it’s type. It can infer what a table does based on yml documentation.

The Blind Spot: If a table exists in dbt/yml, the bot assumes it is active. As it reads through 458+ tables in our warehouse, like an engineer it can also make mistakes due to the amount of context it ingests – these mistake in the output are known as hallucinations.
vs
Your View (As an Intern)

Comprehensive: You can check Datahub lineage graphs, query update timings in pgAdmin, log into WooCommerce or the courier portals, or simply ask your supervisor if a number looks off.

The Human Advantage: You have the critical context—the understanding of the business process and flows—needed to realize when a table is stale or deprecated. For e.g. For us we know which woo_id is the latest order because of data discovery. So from there we can infer that a table is still in use if the latest woo_ids are present inside the table.

Since the bot cannot run external verification checks (E.g. like checking courier websites or checking with supervisor), if a legacy table is sitting in the warehouse — even if it has been dead for years — the bot treats it as a completely valid option. Worse, it will write a query against it, run it, and return a wrong answer with absolute confidence. There is no warning. Just an output that happens to be completely false.

This risk extends beyond simple query mistakes. At Vatico, we adopt an AI agent to automate the design of our database structures and pipelines. If our data context is unclear or cluttered, the AI agent is forced to construct new layers on top of legacy, obsolete models. This inevitably generates badly designed code that may follow past conventions instead of our current one.

Therefore it extremely important for us to retire all legacy tables from our warehouse so as to clean context for Vatico bot so that it can provide us with sharper answers that are less hallucinated.

Two Stale Tables Scheduled for Retirement

To help you recognize deprecated models, here are two real examples of stale tables currently in our database that are up for retirement. We have masked portions of their names with xx:

Table 1: Stale Monthly Summary

Table Name: kpis_xx_summary_monthly

Last Entry: 2025-02-01 — potentially not updated in the last year.

The Risk: If the bot attempts to calculate monthly trends using this summary, it will retrieve data that cuts off in early 2025, completely missing more recent operations.

Table 2: Stale Migration Backup

Table Name: kpis_xx_and_xx_by_sku_xx_backup

Last updated_at: 2023-06-03 — confirmed stale for over 2 years.

The Risk: Because this is an old backup table from a database migration, querying it will return outdated SKU volumes and revenues, leading to reports that are years out of date without raising any query errors.

How to know if a Table is Legacy?

In order to recognize legacy tables, I have devised a workflow you can adopt to do some simple fact checking:

01
Bot Suggestion
Ask Vatico Bot about tables (e.g., “Is this table connected up stream or downstream?”, “do you see any table with ref(va_shipment) or source(‘staging’,’va_shipment’) in its code?”). Treat the bot’s suggestion as a starting point, not the source of truth.
02
Check Datahub Lineage
Search for the suggested table in Datahub. Look at its upstream sources and downstream dashboard dependencies. If a table has no recent dbt runs or has no upstream models, it may indicate depreciation. The bot might not see this, but you can.
03
Check pgAdmin timestamp columns.
Look at the maximum timestamp in the date columns (e.g., SELECT MAX(created_at) FROM table). If the most recent row is years old, the table is dead.
04
Validate Keys (woo_ids & courier tracking Codes)
For order and fulfillment tables, check the actual IDs (woo_id) or courier tracking codes. If they align with our most recently placed production orders, the table is active and not legacy.

References

Gong Elijah