Skip to content
Dataspilot

Recovering your data, even from legacy software: our ETL

Is your data spread across a modern SQL database, an old line-of-business application and various online tools? Dataspilot's ETL extracts it, cleans it and brings it together in one place, ready for your reports, your dashboards or a migration.

Article By Administration

In most SMEs, the useful data already exists: it is simply scattered. Some of it sits in the accounting software, some in an online CRM, and some in an old line-of-business program that nobody dares touch any more. To make use of it all together, Dataspilot uses its own ETL.

What is an ETL?

ETL stands for Extract, Transform, Load: extracting data from its sources, transforming it (cleaning, converting, matching) and then loading it into a target database. It is the invisible step that makes a dashboard reliable or a migration lossless.

Sources in very different formats

Our ETL is designed to read data wherever it lives, however old it is:

  • Modern databases: the SQL databases used by most business management software.
  • Legacy databases: data files from old line-of-business software, sometimes with no vendor or documentation. We analyse their structure to recover customers, products and history.
  • API: online software (CRM, invoicing, e-commerce, banking…) queried through its programming interfaces.
  • Files: CSV or Excel exports produced regularly by a tool.

Each source is checked on a case-by-case basis during the analysis: we confirm what can be extracted, and how, before committing.

Transform: where quality happens

Extracting is not enough. The same information is rarely encoded the same way from one piece of software to another. The transformation step makes it possible to:

  • convert character encodings, date formats and amount formats from legacy systems;
  • match the same customer or product appearing in several sources (company number, customer code, reference);
  • remove duplicates and flag incomplete data rather than carrying it over as it is;
  • apply your business rules: how turnover, margin or an active customer is defined.

Load: a single, up-to-date database

The transformed data is loaded into a target database. Loads can be scheduled (every night, every hour…) and each run is logged: you know what was loaded, when, and what was rejected.

What it is used for in practice

  • Reports and dashboards that combine several software packages, with the same figures for everyone. See Data & reporting.
  • Migration from legacy software to a new tool, with no re-keying and no loss of history.
  • Regular synchronisation between tools that do not talk to each other. See Integrations & API.
  • Searchable archiving of data from software that is about to be retired.

How a project runs

  1. Inventory of the sources and useful data.
  2. Analysis of the structures, particularly for legacy or undocumented databases.
  3. Transformation rules defined with you, in writing.
  4. Initial load and review of the results with your teams.
  5. Automation of subsequent loads and monitoring.

Do you have data locked away in legacy software or spread across several tools? Let's talk.

Key points

  • An ETL extracts, transforms and loads data from several sources into a single database.
  • Dataspilot's ETL reads modern databases, legacy software databases, APIs and files.
  • The transformation (formats, duplicates, matching) is what makes the result reliable.
  • Uses: reporting, migration, synchronisation between tools, archiving.
  • ETL
  • data
  • databases
  • API
  • migration
All articles