Official Statistics produces figures and studies based on surveys, which it manages in a fully autonomous manner, from their design to their dissemination following the collection and non-response processing phases. However, with the abundance of data produced and made available by other bodies, the mass use of external sources and particularly administrative data to produce statistics is increasing rapidly (Lamarche and Lollivier, 2021, Ouvrir dans un nouvel ongletHand 2018). This practice is also growing internationally (Ouvrir dans un nouvel ongletUNECE Integration, statswiki, Cros 2014). As a result, there is now a strong desire to further industrialise the integration of administrative sources in order to improve the security, traceability and reproducibility of this data integration.

It is often the case that administrative data cannot be used directly (Courmont, 2021). Their integration therefore consists of uncoupling data and metadata from their original management universe and linking them to the statistical world (statistical unit, concepts, nomenclature, etc.). In order to do so, future users must make the content of this new source their own.

Where the quality of the administrative data is deemed to be sufficient for the production of reliable statistics (“source qualification”), the first stage in the process is to integrate external information into the statistical information system. As is the case for survey-based data collection, integration is the first step in statistical processing (check, verification, adjustment) enabling the transition from raw data to data that can be disseminated.

The objectives, the precise scope and the various stages of this data integration process are described in this article. The qualification phase is only briefly touched upon here, even though it is essential. Administrative sources may have gaps in coverage (e.g. missing data for Alsace-Lorraine as they were defined after the Concordat) or use concepts far removed from those used by Official Statistics (for example: data collected from electricity meters for the purposes of billing household consumption): these problems must be identified upstream so that they can be addressed as soon as possible.

Before integrating a source, it is important to ensure that it is of adequate quality for use for statistical purposes. It is therefore necessary to contact the producer of the data to verify that the source is:

It should be noted that the UK Office for National Statistics sends statisticians directly to the administrative bodies in order to perform this qualification phase (Fermor-Dunman and Parsons, 2022).

Administrative data are gathered and structured by administrative bodies to suit their own needs. For example, income tax returns are collected at the tax household level (unit managed by the DGFiP). This concept refers to all of the individuals recorded on a single tax return and does not directly correspond to the concepts of the individual and the household used in statistics. A tax household may comprise one or several individuals and there may be multiple tax households within a single “statistical” household: for example, an unmarried couple counts as two tax households if each partner completes their own tax return. This basic administrative information must therefore be restructured in order to be used for statistical purposes.

During the integration phase, the individual administrative data are transformed into raw individual data suitable for statistical use. Only essential information is selected from external sources in compliance with the principles of minimisation and necessity that statisticians are bound by. Switching from the administrative world to the statistical world involves a change of governance. Statisticians are permitted to modify the data without any need to inform the original producer of this. These data then become statistical data and are therefore subject to statistical confidentiality.

This phase makes it possible to build a coherent set from various different files, or even from several different sources. A very interesting example of this is FIDELI. This file incorporates several tax sources: a source describing the members of the taxable households, a source containing information regarding the income tax of those households, a source containing information on housing tax and another containing information on property tax, each of which may comprise multiple files (more than one hundred per source) for the purposes of obtaining information covering the whole of France.

The integration produces the raw version of the data, which will feed into the remainder of the process. At this stage, it is simply a case of reorganising the information without checking them in any way and certainly not correcting them. However, some traceability is required to guarantee the reproducibility of the creation of this initial database.

Integration is the starting point of the statistical process. It provides a point of reference to which we can return in the event of problems in subsequent statistical processing. By comparing the raw data with the data modified by managers during the “manual and automatic checking and correction” phase, it is possible to judge the effectiveness of the checks put in place. Likewise, by comparing the raw data with the data obtained following automatic adjustments, it is possible to measure the variance introduced by such processing and the increase in quality for various indicators, particularly with regard to the reduction of bias.

If we compare the data life cycles of statistical production based on a survey and statistical production based on an external source, we notice that they differ in the upstream phase, but that they are identical following processing aimed at transforming raw statistical data into data that can be disseminated.

As per the GSBPM (Ouvrir dans un nouvel ongletUNECE, GSPBM), data integration precedes the downstream phases of data processing and analysis, which result in data that can be disseminated (check, manual and automatic correction, validation of individual data, aggregation and validation of aggregated data, dissemination of data and archiving of data). We do not discuss these phases in this article.

This means that if the administrative file contains values that are obviously incorrect (30 February, for example), such values will not be modified during the data integration phase, but corrected with a possible value during the subsequent “check, manual and automatic correction and validation of individual data” phase.

Integration is the first stage of the process. This is also the point at which statistical metadata that will be of use throughout the life cycle of the data are initialised (concepts, description of variables, lists of codes and nomenclatures). This makes it possible to set up metadata-driven processes (see below).

The statistical survey is handled by statisticians from the start and ensures that the questionnaire is designed in such a way that the data collected are in line with statistical concepts and units. As a result, there is no need to transform them following their collection and they can be directly used as raw statistical data.

Conversely, external data must be transformed (creation of statistical units, renaming, etc.) before they can be considered raw statistical data. Some metadata are initialised during this integration phase, such as the definition of variables, the rules for their calculation, their links with statistical concepts, etc.

At INSEE there are statistical processes that link survey data to administrative data in order to produce robust statistics while limiting the statistical burden, namely the time required for respondents to complete the survey. An example of this is the Tax and Social Incomes Survey (enquête Revenus Fiscaux et Sociaux), which merges data from the Labour Force survey (enquête Emploi) with tax and social data, or the structural business statistics process (système d’élaboration des statistiques annuelles d’entreprises – ESANE), which is based on the results of annual statistical sector-based surveys, tax data, and employment data (Déclaration sociale nominative – DSN).

Administrative data are often linked to public policy, the legal contours of which can change rapidly over time (introduction or withdrawal of a tax or a social benefit, etc.). It is therefore essential that the tools and methods developed during this data reception phase be as transparent and adaptable as possible.

Integration involves a succession of elementary operations written in a declaratory manner, i.e. by specifying the what and not the how, which can easily be “replayed” to obtain a permanent and reusable processing chain. In order to implement this reproducibility and ensure its adaptability, it is essential to make use of standards and provide statisticians with tools that will allow them to work as independently as possible. The VTL language is a solution that allows both of these key requirements to be met. The transformation of administrative data into raw statistical data involves a sequence of basic tasks (similar to function composition in mathematics), which can be broken down into the following six categories:

This order is for information only and may differ from one source to another. In particular, the pseudonymisation and filtering phases may occur at different times within the sequence.

This categorisation is based on the “source hosting service” established by the Statistical Directory of Individuals and Housing (répertoire statistique des individus et des logements – RESIL) program (Ouvrir dans un nouvel ongletDurr et al., 2022), which will take on this role of integrating administrative data into the demographic and social information system using the ARC (accueil réception contrôle – receipt, acceptance, control) tool (Box 1).

The remainder of this article will be illustrated using a fictitious administrative file used by INSEE by way of example. This example is simplistic, but allows us to take account of the different transformations that are possible and to illustrate the use of a “pipeline” for the purposes of integrating a source.

Let’s imagine that the content of the data sent to INSEE by means of a file in CSV format (with “;” as a separator) is as follows:

In our example, an individual’s wage is only allocated to one tax household, but an individual can be allocated to several tax households, for example if they own a second home in a different region to that in which they declare their wage: this is the case for Jonathan Durant in this example.

In order to use variables from an administrative source, it is easier when they have a name that can be understood (for example: decl_wage) rather than retaining the name in the original file that corresponds to the name of the box in the tax form (1AJ) in this example.

The names of variables in external files are often linked to the context in which they were collected and they will ideally be renamed at this stage to make them easy for statisticians to use (they will not necessarily have been directly involved in the data integration phase). However, it is not sufficient to simply rename the data to facilitate their use by statisticians. Descriptive metadata must also be captured, to provide a more precise description of the concept, the format of the variables, etc. The new names for the variables will therefore more accurately correspond to the statistical concepts that they cover. Therefore, in our example:

Administrative management units (electricity meters, tax households) are generally different from the statistical units that are disseminated (dwellings, households, individuals, companies, etc.). These statistical units must therefore be derived if they are not directly present within the administrative source. This step results in the aggregation of several records or the deletion of records to create a statistical unit. For example, data can be grouped at the “household” level based on individual data, or grouped at the “individual” level based on data regarding employment contracts, etc. This is possible if the data within the external source allow this thanks to the presence of the household identifier in the records of individuals, for example. If such processing requires data external to the source, this operation will take place at a later stage of the process.

Conversely, it may be desirable to break a record within the input file down into several records in the statistical data model (this is the case for individuals within the tax files, which are structured as tax households). It then becomes necessary to explain the links between different statistical units. In the context of tax data, establishing this link between individuals and tax households on the one hand, and tax households and addresses on the other hand, ultimately allows us to create households.

In our example, the file sent contains a “mix” of two types of statistical unit within a single record.

The first step is to create two different tables, one for individuals and the other for tax households, within the “statistical” data model.

The data are integrated into the following two tables:

The original data can be reconstituted by joining these two tables using the household_id variable, which is present in both tables. It is a case of restructuring the information rather than changing it.

Administrative sources might contain nominative information (social security number, last name, first names, etc.) that are not essential for statistical production; these must be deleted at this stage to comply with data privacy. So we pseudonymise them. In order to separate civil status data (last name, first name, date and place of birth and address) from data useful for compiling statistics (information regarding employment, income, etc.) as early as possible, the source civil status data are matched with an ID of the register in order to replace the civil status data in the integrated data with this ID.

The implementation of such pseudonymisation in our example results in the table of individuals being bisected, with one table called “individual_civil_status”, which contains civil status data, and the second called “individual_wage”, which contains the other variables. The two tables are linked using the variable ind_id, which uniquely identifies an individual based on their last name, first name and date of birth. As can be seen, the “individual_civil_status” table only contains six rows, as Jonathan Durant is duplicated within the file.

Administrative sources may use different classifications from those used for statistics. This step makes it possible to conform to the latter classifications, provided that the transformation is fully automated (replacement of “M” with “1” for gender, for example). For records requiring additional human input (codifying the PCS for example), a recovery indicator can be set during integration phase and resulting in the “manual” recoding being pushed back to a later stage in the production process.

Such processing directly concerns the values of one or more of the variables in the input file. It may involve:

Statisticians often use classifications (age group, company category, etc.) for the purposes of their studies. By calculating derived variables, they are able to convert from a year of birth contained within the administrative source to an age group.

This involves the creation of new variables that will be useful in subsequent phases. Examples include the creation of a statistical variable by means of aggregation or by splitting the input variables. Income can be defined as the sum of different items in the tax form, the address can be broken down into several fields, etc.

In the example, recoding has taken place and automatic derived variables have been created. They do not require any human input:

The tables obtained following this integration phase are as follows:

The coverage of the input source may be significantly broader than the coverage that is of statistical interest. In this case, if the source contains variables that allow the population of interest to be filtered, it is possible to do this at the integration stage to reduce the size of the statistical database. This also allows for compliance with the principles of minimisation and necessity under which all statisticians are compelled to restrict themselves, to the maximum extent possible, to using only the data they actually require. An example of this would be removing commercial premises from an administrative file when we are only interested in residential premises.

In the example, the rows of duplicate individuals with a missing wage value are not of interest. The unique variable is recalculated based on this new table.

The final individual_wage table is therefore as follows:

The source integration phase must be fully automated and reproducible. It involves the establishment of a general framework to ensure a systematic approach to data structured by an external producer. It must result in the establishment of a pipeline complete with checkpoints to ensure that the sequence of tasks is progressing correctly and to halt the process as soon as a potential issue is encountered.

The aim of this industrialisation is to establish a sequence of generic, customizable processing activities that are as independent as possible from the input source. Integrating a new source (or updating an existing source) is therefore a case of describing the input source, the desired output data model and the transformations required in order to turn one into the other. The data integration step sequence then takes place automatically based on these metadata, which become active metadata. As a result, by specifying the expected representation of a variable (M, F for the gender variable, for example), it is possible to automatically generate value-checking processing activities.

Under these conditions, some of the more precise metadata are of particular interest when it comes to transforming data. Upstream, the documentation of processing activities may extend to their specification in a more or less formal language, such as BPMN or directed acyclic graph (DAG) for the process as a whole and SQL or VTL (Box 2) for the transformations themselves. This offers the advantage of allowing for the full or partial automation of the process where active metadata are involved. The specification in VTL is a form of metadata (it describes the transformation in a language that can be understood by statisticians) that is then implemented automatically using IT tools, which then makes it directly active. Downstream, accurate tracing of operations (which variables were used to calculate another variable, for example) allows for improved process control and promotes reproducibility and transparency. This is known as metadata provenance or lineage.

These active metadata allow the designer to work as independently as possible and to more easily adapt their pipeline to changes in the external sources. For example, the Directorate-General for Public Finance (Direction générale des finances publiques – DGFiP) recently changed the way that it registers residential outbuildings in its information system: in the past they were linked to a main premises, whereas they are now considered to be dwellings in their own right. Updates such as these therefore require a revision of the filter used to define coverage or even the addition of new variables. Using active metadata, statisticians can create a new variable within the host data model in RMéS (Bonnans, 2019), which makes it possible to identify outbuildings, establish a method for calculating this new variable within ARC using the variables within the DGFiP file, and modify the VTL script used to define its coverage to take account of this new variable. A statistician is then completely free to modify their pipeline, to take into account the changes to the structure of the input file and to trace these.

In the pipeline relating to the different stages of data integration (figure), the statistician enters the metadata upstream (definition of variables, link to a statistical concept, transformation rule, etc.). The step sequence then progresses automatically based on these metadata, resulting in standardisation and a clear separation between logical specification and technical implementation, and enabling greater autonomy for the statistician.

Figure - The data and metadata pipeline

As a result of this process, the administrative data are integrated into the information system in the form of raw data. In addition, a quality assessment is produced in parallel to provide initial quality indicators (number of records, partial non-response rate, etc.), which give initial insights into the quality of the raw data.

As is the case at any stage of the statistical process, the existence of complete and accurate metadata when transforming data is key to ensuring that operations run smoothly. Moreover, as transformation-related processing often takes place at the very beginning of the process, it is necessary to capture or create the metadata that will be reused in subsequent phases.

As a general rule, administrative data are described with their own metadata. Some may be used, but not necessarily all of them. In addition, the statistical metadata resulting from the integration process differ from the original data (the classification may be different, the associated statistical concepts are specific to statistical data, etc.).

Different types of metadata are of particular importance when it comes to transforming data.

Metadata can themselves be subject to transformation alongside the transformation of data. For example, the renaming of variables can be seen as a transformation of metadata.

The structural metadata, i.e. the definition of the variables used, the statistical concepts to which they relate, the type and the value domain, which are occasionally limited by code lists, must be defined at the earliest possible opportunity. It is important that they are available in formats that allow their use to be easily automated for the purposes of validating the processing performed. Descriptive metadata are another important category of metadata. They store all of the information necessary to facilitate the discovery and understanding of the data and to evaluate their quality or suitability for a specific use, etc. The documentation detailing the processing carried out and the methods used, as well as the conditions under which data are provided, can also be considered descriptive metadata.

Although the source has been qualified upstream and its quality deemed sufficient for the production of statistics, it is important to have an initial measure of the quality of the data as soon as they are integrated (Ouvrir dans un nouvel onglet Six and Kowarik, 2022, UNECE Statswiki Quality). This provides an initial idea of the intrinsic quality of the source and allows an ongoing quality improvement process to be initiated at the earliest opportunity. This phase also allows users to familiarise themselves with new sources and better understand their structure and content.

In terms of integration, this quality measure (commonly referred to as data profiling in the international literature) consists of defining indicators that will provide an initial measure of the quality of the source and make it possible to monitor its development over time, by comparing successive deliveries from the source (monitoring). The following are some examples of these indicators:

The analysis of these indicators will often require a request for clarification from the producer, at least the first few times that files are received. This may also result in the producer introducing checks into their own process in order to improve the quality of data collection. For example, the work carried out at INSEE during the receipt of the Nominative Social Declaration (Déclaration sociale nominative – DSN) resulted in the GIP-MDS adding an NIR check into its DSN data collection interface, which significantly improved the quality of the NIR within the DSN. Such indicators can be used to identify at the earliest opportunity whether the data provided are incomplete when the number of records or the totals for certain variables are lower than in previous deliveries. These indicators are also used to identify problems that can be corrected by subsequent processes (e.g. the quality indicator for the household wage, which flags the suspect wage of one billion euro). These quality indicators will therefore be useful to the user of the raw data when they come to manage their own processing activities.

Given the critical nature of this phase, specific tools have been developed.

The integration of administrative data into a statistical process is a key phase, the importance of which is recognised at international level (Box 3). It must be carefully identified and specified and the right tools must be used. It represents a change to the nature and “owner” of the data, which then becomes statistical and subject to the associated legal, methodological and organisational framework.

It must result in the implementation of a documented and reproducible pipeline of basic transformations; it is desirable to formally define it in a manner independent of any particular technology. When specifying this pipeline, it is important to take account of metadata, whether source metadata that may themselves be subject to transformation or those resulting from the integration process in the case of metadata provenance. As with any process, quality indicators must also be produced in order to check and improve processing.

Beyond administrative data, the use of external data for statistical purposes is expected to increase, and the establishment of a methodological framework and shared tools for data acquisition will make it possible to industrialise this function, as is already the case for surveys within INSEE (Cotton and Dubois, 2019) and (Koumarianos and Sigaud, 2019). As for the latter, the management of processes through the activation of metadata, which leads to standardisation and a clear separation of logical specification from technical implementation, affords the statistician greater autonomy and improves the responsiveness of systems, which is key to adapting to external changes.

The logical specification of integration-related processing, which is still largely organised in silos, will also allow it to be made more modular and easier to share. In this regard, consideration can be given to transferring certain steps upstream to the data producer (such as the performance of pseudonymisation or recoding via smartphones prior to the transfer of information). This issue is most commonly encountered in connection with smart statistics. Lastly, this automated data integration phase is becoming increasingly useful today due to the mass availability of proteiform data from sensors, digital tools and even electronic/social networks that reflect the social, environmental and economic reality. Whether they can be used to produce quality statistics remains to be seen.