Data Integration and ETL Pipeline Development

Where your reporting data really comes from

Operational integrations move records between systems so work can happen. Data integration does something different: it gathers history from every system into one place so you can measure the work. Tepia often meets companies that have the first and lack the second, which shows up as a monthly ritual of exports, VLOOKUPs and arguments about whose number is right.

Picture a multi branch service company. Jobs and technician hours live in the field service system, revenue and receivables in QuickBooks or NetSuite, leads and campaigns in HubSpot, call volume in the phone system, and payroll hours in ADP. The owner wants revenue per technician hour by branch, close rate by lead source, and repeat customers by service type. No single system can answer those questions, because each holds one piece and each names the customer slightly differently.

Tepia builds pipelines that pull each source on a schedule, match the customer, job and employee across systems, and publish clean tables that a dashboard tool such as Power BI, Looker or Tableau reads directly. The spreadsheet ritual goes away, and so do most of the arguments.

ETL or ELT, batch or near real time

Classic ETL transforms data before loading it. Modern warehouses are fast enough that Tepia usually prefers ELT: land raw data first, then transform it inside the warehouse with SQL models managed in dbt. Raw history stays available, so when a business rule changes you can rebuild past months instead of living with the old logic.

Tepia still transforms before loading in specific cases. Health data that includes PHI may need masking or tokenizing before it reaches an analytics environment, in line with HIPAA aware architecture. Some sources also need heavy cleanup that is easier in Python than in SQL.

On timing, most reporting is well served by loads every hour or every few hours. Near real time pipelines using change data capture from PostgreSQL, MySQL or SQL Server make sense for operational dashboards such as same day dispatch boards or inventory alerts. Tepia matches freshness to the decision being made, because a pipeline that refreshes every minute is harder to run than one that refreshes every hour.

For standard sources such as Salesforce, HubSpot or Shopify, a managed connector like Fivetran or Airbyte is often the sensible extract step. Tepia writes custom extractors for in house apps, legacy databases and APIs that no connector covers, then runs both through the same models and tests.

Anatomy of a pipeline Tepia builds

Every pipeline follows the same stages, and each stage has a typical way of failing that Tepia designs against from the start.

Tepia documents each model with its owner and definition, so a new analyst can see exactly how active customer or gross margin is calculated.

Data quality, history and access

Getting rows into a warehouse is the easy part. The value comes from handling the awkward cases correctly. Tepia plans for these in every project:

• Changing attributes. When a customer moves from one sales territory to another, should last year’s sales stay with the old territory? Tepia keeps history so both views are possible.
• Deletes and corrections. Sources delete or void records, and pipelines must reflect that instead of counting ghosts.
• Late arriving data. A job closed today may be billed next week, so models reprocess a rolling window.
• Personal data. Tepia applies GDPR and CCPA aware handling, limits who sees personal fields, and uses row level security in the BI tool so a branch manager sees only their branch.

Clean, documented data also prepares you for AI work, since retrieval and forecasting depend on the same foundations. See data preparation for AI projects, and for the reporting layer itself, our data analytics and dashboards page.

How Tepia approaches data integration and ETL

Tepia’s six phase process keeps data projects tied to real decisions rather than an ever growing list of tables. A US based project manager leads each engagement.

1. Discovery: interviews with the people who read reports, an inventory of every source and its API or database access, and a sample extract from each. Deliverables are an Investigation Summary, an Interview Summary and User Stories such as an operations director comparing branch utilization each Monday.
2. Design: a metric dictionary signed by finance and operations, the warehouse model, freshness targets per source, and wireframes of the first dashboards.
3. Development and Testing: an Alpha with two or three sources and the most requested metrics, then a Beta adding the remaining sources, with numbers reconciled against the systems of record.
4. Training: sessions for analysts on the models and for managers on the dashboards.
5. Launch: backfill of history, retirement of the old spreadsheets, and alerting switched on.
6. Support: monitoring, fixes when sources change and new metrics on request.

Typically Discovery takes 1 to 3 months, development and QA 2 to 6 months, and launch about a month. Read about our process or talk to Tepia about the reports you cannot get today.

Frequently asked questions