Jordan Goodman

Data Modernization Playbook

Companies are eager to add AI, but AI is only useful when it can access reliable, well-defined data.

Consider Really Cool Beverages, a fictional company with an on-premises SQL Server ERP, several disconnected SaaS platforms, and analysts who still assemble reports in Excel. The goal is not to deploy a chatbot first. It is to build a governed data foundation that supports reporting, automation, and AI.

Target architecture

The architecture has five layers:

  1. Ingestion: Airbyte replicates databases; Python pipelines extract data from unsupported APIs.
  2. Storage: Snowflake centralizes source data.
  3. Transformation: Raw records become clean, business-ready models.
  4. Semantics: Approved definitions describe metrics, relationships, and terminology.
  5. Consumption: BI and AI tools query only the governed layer.
SQL Server ── Airbyte ──┐
                       ├── Snowflake IMPORT ── Snowflake OUTPUT ── BI / AI
SaaS APIs ── Python ───┘

Snowflake foundation

Start with separate schemas for replicated and curated data:

CREATE WAREHOUSE ERP_PIPELINE_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE ERP;
CREATE SCHEMA ERP.IMPORT;
CREATE SCHEMA ERP.OUTPUT;

ERP.IMPORT contains source-aligned tables such as CUSTOMERS, ORDERS, and INVOICES. ERP.OUTPUT contains governed models such as DIM_CUSTOMER, FACT_ORDER, and V_DAILY_SALES.

This separation matters. Ingestion tools should preserve source data, while reporting models should standardize names, types, joins, and business rules. BI and AI tools should query OUTPUT, not raw ingestion tables.

Use dedicated service roles for each workload:

Use key-pair authentication or a managed secret store for service accounts. Avoid personal administrator credentials and hardcoded passwords.

Database replication with Airbyte

Airbyte replicates the on-premises SQL Server database into Snowflake. A practical first deployment is a Docker-based Airbyte instance on a VM or EC2 host.

The main implementation work is usually networking and access:

Begin with customers, products, orders, invoices, payments, inventory, and locations. Use incremental replication for large transactional tables and full refreshes for small reference tables. Nightly synchronization is often enough for an initial reporting workload; change data capture can follow when lower latency is justified.

Custom API pipelines

When Airbyte does not provide an adequate connector, use a small Python pipeline:

API → Python container → Snowflake IMPORT → Snowflake OUTPUT

Each pipeline should handle:

Package the code as a container and schedule it with cron, ECS and EventBridge, or an equivalent job runner. Store credentials in a secret manager. A scheduled, monitored container is a pipeline; a script running from an engineer's laptop is not.

For unstable API payloads, raw JSON can initially land in a Snowflake VARIANT column. Curated views can extract typed fields while preserving the original response. Once the schema stabilizes, move high-value datasets into typed tables.

Every ingestion process should write an audit record containing the pipeline name, source, target, start time, finish time, status, row count, and error message. This makes freshness and failures observable.

The semantic layer

Centralized data alone does not make a system AI-ready. AI also needs business context.

A semantic layer defines:

For example, Net Sales should map to one governed calculation rather than being regenerated differently for every question. Terms such as active customer, gross margin, and sales region need explicit definitions.

An AI interface should query curated Snowflake views through a restricted role. It should not inspect every raw table or invent business logic from ambiguous source fields.

Before rollout, test common questions against trusted reports:

Incorrect answers usually indicate a modeling, definition, permission, or freshness problem—not a need for a more impressive chatbot.

Cost controls

The core platform can start small:

Snowflake compute should run only when pipelines or users need it. Container jobs should be scheduled rather than left running continuously. Enterprise AI licensing may cost more than the underlying data platform, so begin with a narrow group such as finance, operations, sales leadership, and the data team.

Measure whether the system reduces manual reporting, improves self-service, and returns trustworthy answers before expanding access.

Implementation sequence

  1. Create Snowflake warehouses, schemas, roles, and service accounts.
  2. Replicate the ERP database with Airbyte.
  3. Deploy containerized pipelines for unsupported APIs.
  4. Add monitoring, audit records, and freshness checks.
  5. Build clean models in ERP.OUTPUT.
  6. Connect BI tools to the governed models.
  7. Define metrics, relationships, descriptions, and synonyms.
  8. Connect the AI interface using a restricted role.
  9. Validate answers against trusted reports.
  10. Expand one business domain at a time.

The first milestone is not “we have AI.” It is “the data arrives reliably.” The second is “the reporting layer is trusted.” AI becomes valuable only after both are true.

Conclusion

AI does not repair siloed systems, fragile pipelines, or inconsistent metrics. It exposes those problems faster.

The practical path is straightforward: replicate databases with Airbyte, extract unsupported APIs with scheduled Python containers, centralize the data in Snowflake, model it into governed business objects, and place AI on top of that trusted layer.

Infrastructure first. AI second.

For help with Airbyte, Snowflake, API pipelines, semantic modeling, or AI readiness, contact me at jordan@jordangoodman.xyz.