Expertise

Databases & Datalakes

Before any dashboard, you need reliable data. It is the invisible part of the job, and often the most important one.

Role: Creation, updates and maintenance
Sources: ERP, CRM, finance, HR
Tools: SQL Server, SSMS
Output: Power BI reports

Three software tools, three ways of writing the same customer. The real work starts right there.

Reconciling, in practice

Each tool has its own naming. Until these three rows are recognised as one and the same customer, no report can be right.

ERP: NameLAITERIE DU VAL SAS; IDCLI-00482; CountryFR

CRM: NameLaiterie du Val; ID482; CountryFrance

Finance: NameLaiterie Val (SAS); ID00482; CountryFRA

Datalake, business zone: Name?; ID?; Country?; Sources?

Fictional example.

Datalake or database?

Datalake Database

Data Raw, any format: tables, files, logs, documents Structured in tables, following a defined model

Structure Defined on read, at analysis time Defined on write, before saving

Use Exploration, full history, data science Applications, transactions, reliable reporting

Strength Flexibility: keep everything, decide later Data consistency, integrity and security

In practice, the two complement each other: the Datalake keeps everything, and the business tables built from it feed the reports.

The path of a piece of data

At Synerlink, the Datalake already existed when I arrived: my role was to keep it up to date and improve it, so that it reliably fed every Power BI report.

SourcesERP, CRM, finance, HR
IngestionScheduled extracts
Raw zoneExact copy, with history
Clean zoneFormats and duplicates fixed
Business zoneAnalysis-ready tables
ReportingPower BI reports

In SQL, it looks like this

Once the IDs are aligned, a single query combines what three tools each knew on their own.

rapprochement_clients.sql

-- Revenue per customer, as seen by the three sources
SELECT  e.client_id,
        TRIM(e.raison_sociale)      AS client,
        c.segment,
        SUM(f.montant_ht)           AS ca_ht
FROM    erp.clients       AS e
JOIN    crm.comptes       AS c ON c.code_erp  = e.client_id
LEFT JOIN finance.factures AS f ON f.client_id = e.client_id
GROUP BY e.client_id, e.raison_sociale, c.segment
ORDER BY ca_ht DESC;