> For the complete documentation index, see [llms.txt](https://cleyrop.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cleyrop.gitbook.io/docs/documentation-fr-en/data-and-ai-project/datasets/gerer-les-datasets.md).

# Manage Datasets

A dataset corresponds to a table of structured data that you can use for your analyses, transformations, and visualizations.

Cleyrop lets you create datasets in several ways: by importing local files, connecting to external data sources, or using the results of your Dataflows.

Once created, your datasets can be **refreshed** to keep your data up to date, **published in the catalog** to share them with other users, or **exposed via API** to feed your business applications.

***

## Create a Dataset <a href="#creer-un-dataset-depuis-un-import-local" id="creer-un-dataset-depuis-un-import-local"></a>

### Create a Dataset from a Datasource <a href="#creer-un-dataset-depuis-un-import-local" id="creer-un-dataset-depuis-un-import-local"></a>

**Add a Datasource to the project**

{% hint style="info" %}
This step is not necessary to create a dataset from a **local import** : this source does not require a Datasource or a prior access request.
{% endhint %}

To use a Datasource as the source of a dataset within a project, you must first obtain permission to use it in the project.

* From the library, go to the `Datasources`
* Click the `Add a Datasource`. You can search for a Datasource in the list by entering one or more keywords in the search bar or by filtering by sensitivity or connector type
* Click `Request access` then explain your request in the field `Comment` and click `Send`.

An access request is sent to Platform Managers.

You will receive a notification after the request has been processed to confirm or deny access.

**Create the Dataset**

{% stepper %}
{% step %}
**Choose the Datasource from the project's list of authorized Datasources**
{% endstep %}

{% step %}
**Define collection settings according to the source type**

{% tabs %}
{% tab title="Local Import" %}
Choose your files:

* either drag and drop your files into the designated area
* or click Browse to select your files

Define collection settings:

* **Dataset schema** : it will be based on the inferred schema of the first file. If you have several files, you can choose the file to use as the basis for the reference schema. You can choose to ignore types; all columns will be strings.

{% hint style="warning" %}
**If you import several files at the same time, the applied schema policy is&#x20;*****schema merging*****. Concretely:**

* **Matching existing columns:**\
  Columns with the *same name* that those in the reference schema must also have the *same type* ➝ *If the type does not match, the file is rejected.*

* **Additional or missing columns:**\
  Extra or missing columns are allowed ➝ *Missing columns will simply be filled with empty values.*
  {% endhint %}

* **Delimiter** : the character that separates the columns in your file. By default, the delimiter is a semicolon. *\[Case: text files with delimiter]*&#x20;

* **Header** : if your file contains a header row, check this box so that column names are automatically detected *\[Case: text files with delimiter and Excel]*&#x20;

* **Element path - JSON files case**
  * Start with “$” and use the “.” separator. Example: *$.item\[\*]*
  * You can use the navigation helper to move between levels and get the correct path&#x20;

    <figure><img src="/files/4f927091757d63ab37cd23ec865686da43321712" alt=""><figcaption></figcaption></figure>

* **Element path - XML files case***.* Use the “/” separator. Example: *dataset/record*

* **Sheet** : the sheet of your Excel file to import - By default, the first sheet is selected *\[Case: Excel files]*

Define an advanced configuration

* **Encoding.** By default, the encoding is UTF-8
* **Compression** : if your file is compressed, choose the compression type - By default, the file is not compressed *\[Case: non-Excel files]*
* Number of rows or columns to skip *\[Case: Excel files]*
* Transpose the dataset *\[Case: Excel files]*
* **Add a column** : with the file name or the import date
* Ignore the **empty rows**
* Ignore schema types: all columns will be **string type** (useful configuration when consistency between the data and the recognized type is not ensured)

{% hint style="info" %}
Supported text formats are: `.csv`, `.txt`, `.tsv`, `.tab`, `.dsv` and `.psv`. Define the delimiter corresponding to your file.
{% endhint %}

You can at any time preview on your reference file the impact of your parameter choices

<figure><img src="/files/431dd7110e97832d8a23f97f4326fc2d46f6ab5e" alt=""><figcaption></figcaption></figure>
{% endtab %}

{% tab title="SQL/Snowflake" %}

* Select the tables or views
* You can preview the data (a select \* ... limit 50 query will be sent to the datasource)
* **Custom SQL filtering** : in the Processing step, you can enter an SQL query to import only a subset of the data. If the field is empty, the entire table is imported.

{% hint style="warning" %}
**Accepted syntax rules** : Only simple queries are supported: `SELECT [columns] FROM [table] WHERE [condition] GROUP BY [col] ORDER BY [col] LIMIT [n]`. Joins and subqueries are not allowed. The table indicated in the `FROM` must match the table selected in the form.
{% endhint %}

<figure><img src="/files/a2a79b6f9766235a64787f7de54c2f8d81d4d21e" alt=""><figcaption></figcaption></figure>
{% endtab %}

{% tab title="SFTP / Repository" %}

* You will need to define file access rules: path and selection rules for file names.
* Follow the same configuration steps as [Create a dataset from a local import](#creer-un-dataset-depuis-un-import-local-1)
* When a Dataset is refreshed, these settings will be reused to access the files
  {% endtab %}

{% tab title="API" %}
At the **Ingestion**, you can override certain parameters defined in the datasource:

* **URL suffix (endpoint)** : specify or override the endpoint to call (e.g. `/users`, `/posts`)
* **Parameters** : add or override query parameters (e.g. `limit: 100`)
* **Headers** : add or override HTTP headers

You can also configure pagination if your API is paginated, as well as the JSON response returned by your API.

<details>

<summary>Configure pagination</summary>

If your API is paginated, enable **Pagination** and choose its **type** : Page based, Offset based, Cursor based, Link based, Header link based, or OData.

Page-based example:

* **Parameter name** : name of the query parameter indicating the page number (e.g. `page`)
* **First page** : number of the first page (often `1` or `0`)
* **Format** : defines how the API signals the end of pagination:

  * **Infinite scroll** : a boolean indicating whether there are more items to fetch is returned in the response
  * **Number of pages** : the total number of pages is returned in the response (e.g. `$.total_pages`)

  In both cases, fill in the **Element path** corresponding: the JSON Path to this value
* **Limit** (optional): name and value of the page size parameter (e.g. `limit` = `50`)

{% hint style="info" %}
The **Element path** (Output section) must point to the data array, and not to the pagination information (e.g. `$.results`).
{% endhint %}

<figure><img src="/files/a1c71a7414c3c19a0f2b74ff29ca953050226a29" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/a5640c6bfcceef58546abca4b3dcb63bcf15c3a8" alt=""><figcaption></figcaption></figure>

</details>

<details>

<summary>Configure the JSON response</summary>

Configure the JSON response: you can define how the JSON data returned by the API will be interpreted:

* **Element path (JSON Path):** defines the entry point in the JSON structure. It uses JSON Path syntax:
  * `$` : root of the response (default)
  * `$[*]` : all elements of an array at the root
  * `$[*].sub_object` : the sub-object of each element
* **Retrieve the schema automatically:** click **Retrieve the schema** to query the API and automatically detect the response structure. The JSON Path Help panel then displays the available columns with their type:
  * `#` : numeric
  * `T` : text
  * `{}` : nested JSON object
* **Navigate through nested objects:** when your JSON response contains nested objects (type `{}`), click the icon to the right of the object to navigate through its subfields. The Element Path is updated automatically.

<div><figure><img src="/files/a40c8dc7f813af74880e8593d3f69310684b3622" alt=""><figcaption></figcaption></figure> <figure><img src="/files/1df92f816721555cd037a63cda67b457d0075664" alt=""><figcaption></figcaption></figure></div>

<figure><img src="/files/1df92f816721555cd037a63cda67b457d0075664" alt=""><figcaption></figcaption></figure>

</details>
{% endtab %}

{% tab title="Business Central" %}
**Ingestion step**

* **Company** : select the Business Central company to query
* **Entity** : select the entity (table) to import from the proposed list

**Processing step**

* **Columns to import** : select the columns to keep from those detected automatically
* **Advanced configuration** :
  * **Filter** : filter in OData format (e.g. `type eq 'Inventory' and blocked eq false`)
  * **Limit** : maximum number of rows imported at each refresh (0 = no limit)
  * **Result sorting** : useful only if a limit is defined, to determine which rows are kept

You can **preview** preview your data at any time to check the impact of your settings.

<figure><img src="/files/610c5159301430bb6e844306220051405178ec88" alt=""><figcaption></figcaption></figure>
{% endtab %}

{% tab title="SalesForce" %}
**Entity** : select the entity (Salesforce object) to import from the proposed list

**Processing step**

* **Columns to import** : select the columns to keep from those detected automatically
* **Advanced configuration** :
  * **Filter** : filter in SOQL format (e.g. `Status = 'Open' AND Priority = 'High'`)
  * **Limit** : maximum number of rows imported at each refresh (0 = no limit)
  * **Result sorting** : useful only if a limit is defined, to determine which rows are kept

You can **preview** preview your data at any time to check the impact of your settings.

<figure><img src="/files/53d1fcb78bb3de4e4d52cbc58a7ac889ec6b8b79" alt=""><figcaption></figcaption></figure>
{% endtab %}
{% endtabs %}
{% endstep %}

{% step %}
**Define refresh settings**&#x20;

* **Refresh mode** : Appending new data to existing data or replacing existing data with new data
* **Refresh frequency** : to automate according to a defined recurrence
* If custom SQL filtering was configured at creation, it is reapplied at each refresh. The SQL query used is visible from the dataset's Refresh details page.

{% hint style="warning" %}
**During a refresh, the applied schema policy is&#x20;*****schema merging*****&#x20;:**

* **Matching existing columns:** Columns with the *same name* as those in the Dataset schema must also have the *same type*. ➝ *If the type does not match, the file is rejected.*
* **Additional or missing columns:** Extra or missing columns are allowed ➝ *Missing columns will simply be filled with empty values.*
  {% endhint %}
  {% endstep %}

{% step %}
**Qualify your Dataset**

* **Name** : display name

{% hint style="info" %}
The dataset's unique ID is generated from the Name and cannot be modified afterwards
{% endhint %}

* **Description** : provide more information so that other users can understand the dataset's content
* **Owner** : Data Owner
* **Sensitivity** :&#x20;
  * For a local import: you can choose its sensitivity. Restricted and sensitive datasets are visible in the catalog but require authorization to be used in projects.
  * Otherwise: the sensitivity will be inherited from the Datasource's sensitivity.
* **Classification** : classification allows identifying the level of data processing and their preferred use: bronze - raw, silver - processed, or gold - reference / ready for business use
* **Labels** : you can choose a label from the list of existing ones or add a new one by typing it. You can manage labels by clicking on the ... next to a label name

<figure><img src="/files/d4f915b47b80bb0dc581dae055ae1e70f8f1c4cb" alt=""><figcaption></figcaption></figure>
{% endstep %}
{% endstepper %}

### **Create a Dataset from a Dataflow**

A dataset can be created directly from a Dataflow configured to produce transformed data. You can choose which output datasets you want to make available in your project's library.

**To create a dataset from a Dataflow** :

1. [Create a Dataflow](/docs/documentation-fr-en/data-and-ai-project/dataflow/creer-un-dataflow.md) and add a transformation with an Output Dataset with an output dataset
2. Configure your [Output Dataset](/docs/documentation-fr-en/data-and-ai-project/dataflow/creer-un-dataflow/configurer-un-dataset-de-sortie.md)
3. Run the Dataflow to generate the first data
4. Click **Reference** (above the output dataset or from the Details page) to make the output dataset available in the project's library.
5. After your Dataflow is deployed, the dataset marked for referencing appears in your library to be used in the project

<figure><img src="/files/a29e2cf2f97a50063ade9836d14e899b32fcafbd" alt="" width="563"><figcaption></figcaption></figure>

The dataset will be populated with each Dataflow run. To learn more about the[execution and automation](/docs/documentation-fr-en/data-and-ai-project/dataflow/executer-deployer-et-automatiser.md) of your Dataflow.

### Supported column types

Each column in a dataset has a type, automatically detected on import or configurable depending on the source. The following types are supported:

<table data-search="false"><thead><tr><th>Type</th><th>Description</th><th>Example</th><th>Pandas</th><th>Spark SQL</th><th>Polars</th></tr></thead><tbody><tr><td><strong>Text (String)</strong></td><td>String</td><td><code>Hello</code></td><td><code>object</code> / <code>string</code></td><td><code>string</code></td><td><code>Utf8</code></td></tr><tr><td><strong>Integer (Integer)</strong></td><td>Whole number (32 bits)</td><td><code>42</code></td><td><code>int32</code> / <code>Int32</code></td><td><code>int</code></td><td><code>Int32</code></td></tr><tr><td><strong>Long integer (Long)</strong></td><td>Whole number (64 bits)</td><td><code>9999999999</code></td><td><code>int64</code> / <code>Int64</code></td><td><code>bigint</code></td><td><code>Int64</code></td></tr><tr><td><strong>Decimal (Float)</strong></td><td>Single-precision floating point</td><td><code>3.14</code></td><td><code>float32</code></td><td><code>float</code></td><td><code>Float32</code></td></tr><tr><td><strong>Decimal (Double)</strong></td><td>Double-precision floating point</td><td><code>1.5e200</code></td><td><code>float64</code></td><td><code>double</code></td><td><code>Float64</code></td></tr><tr><td><strong>Exact decimal (Decimal)</strong></td><td>Exact precision (19 digits total, 2 maximum after the decimal point)</td><td><code>123456789012345.67</code></td><td><code>object</code> (Decimal)</td><td><code>decimal(19,2)</code></td><td><code>Decimal(38,s)</code></td></tr><tr><td><strong>Boolean</strong></td><td>True or false</td><td><code>true</code> / <code>false</code></td><td><code>bool</code></td><td><code>boolean</code></td><td><code>Boolean</code></td></tr><tr><td><strong>Date</strong></td><td>Date only, no time</td><td><code>2024-01-15</code></td><td><code>datetime64[ns]</code></td><td><code>date</code></td><td><code>Date</code></td></tr><tr><td><strong>Timestamp</strong></td><td>Date and time</td><td><code>2024-01-15 10:30:00</code></td><td><code>datetime64[ns]</code></td><td><code>timestamp_ntz</code></td><td><code>Datetime("us", None)</code></td></tr><tr><td><strong>Timestamp with time zone</strong></td><td>Date and time with time zone</td><td><code>2024-01-15T10:30:00Z</code></td><td><code>datetime64[ns, tz]</code></td><td><code>timestamp</code></td><td><code>Datetime("us", "UTC")</code></td></tr><tr><td><strong>Binary (Binary)</strong></td><td>Binary data (inline files, hashes, blobs)</td><td><code>SGVsbG8=</code></td><td><code>bytes</code> / <code>object</code></td><td><code>binary</code></td><td><code>Binary</code></td></tr><tr><td><strong>Array (Array)</strong></td><td>List of typed values</td><td><code>[1, 2, 3]</code></td><td><code>object</code> (list)</td><td><code>array&#x3C;...></code></td><td><code>List(inner)</code></td></tr><tr><td><strong>Struct (Struct)</strong></td><td>Nested data with fixed keys</td><td><code>{"name": "Ada", "age": 36}</code></td><td><code>object</code> (dict)</td><td><code>struct&#x3C;...></code></td><td><code>Struct({...})</code></td></tr><tr><td><strong>Key/Value (Map)</strong></td><td>Nested data with dynamic keys</td><td><code>{"a": 1, "b": 2}</code></td><td><code>object</code> (dict)</td><td><code>map&#x3C;k,v></code></td><td><code>List(Struct)</code> (no native Map type)</td></tr></tbody></table>

{% hint style="info" %}
The **Binary** type is not detected automatically on import: select it manually on the relevant column if necessary.
{% endhint %}

## Add descriptions to columns

Each column of a dataset can be documented with a text description to explain its business meaning, transformation logic, or specific constraints.

{% hint style="info" %}
For Dataset administrators
{% endhint %}

To add or modify a description:

1. Go to the **Schema** tab of a dataset's details page
2. Click the <i class="fa-pen">:pen:</i> Edit
3. Click the relevant column and enter your text in the field **Description**.

Descriptions are persistent and accessible via the interface and API. Editing a description does not affect either the data or the pipelines.

💡 **Good to know** : For datasets originating from Dataflow, descriptions can be edited in Draft mode. Once the Dataflow is in production, they can only be modified from the dataset details page after merging into production, and not in development branches.

## **Refresh a Dataset**

Refreshing allows you to update the data in a dataset.

### **Refresh a dataset from data sources**

{% hint style="success" %}
Refreshing automatically reapplies the **import configuration** (transformations, delimiters...).
{% endhint %}

#### **Manual refresh**

1. Open the dataset details page
2. Click the icon <i class="fa-arrows-rotate-reverse">:arrows-rotate-reverse:</i> Refresh on the home page or at the top right (visible only if you have the necessary rights)
3. The refresh starts immediately

<figure><img src="/files/a40b54ea26bfff494fb48aa525a20d3e2ae979ee" alt=""><figcaption></figcaption></figure>

#### **Automatic refresh**

You can define a data refresh frequency when creating or modifying a dataset by clicking Edit from the Dataset details page (visible only if you have permissions)

1. Go to the dataset settings
2. Edit the dataset
3. Configure the desired frequency in the "Recurrence" section

<figure><img src="/files/a9f9a1af0849a2a9ff1c7b563090df70b069b364" alt=""><figcaption></figcaption></figure>

### Refresh a dataset from a local import

You can add new files to an existing dataset by following a guided process.

**Multi-file import** :

1. Open the dataset details page
2. Click <i class="fa-arrows-rotate-reverse">:arrows-rotate-reverse:</i> "Refresh" (visible only if you have the necessary rights)
3. Choose the refresh mode for this import:
   1. **Append** : adds the new rows to the existing data
   2. **Replace** : completely replaces the dataset's data
4. Select 1 to n files to import

{% hint style="info" %}
The added files must be of the same type as the initial import.
{% endhint %}

**Validation and discrepancy management** :

The system automatically performs the following checks for each file:

* **Schema compatibility** : data type validation. In case of incompatibility, a clear message indicates the column and, if possible, the affected rows.
* **Column discrepancies** : clear display of additional columns (present in the file but missing from the dataset) and missing columns (expected by the dataset but missing from the file), with a reminder of the configured schema policy.

**Configuration preview** :

Before validating, you can review:

* The transformations that will be applied
* The delimiter used
* The selected sheet (for Excel files)

<figure><img src="/files/5089787b1f80ccf26eedfcaac608be406407f4f4" alt=""><figcaption></figcaption></figure>

Once validated, the refresh appears in the refresh list with:

* The user who triggered the operation
* The list of imported files
* Detailed information (applied configuration, status, logs)

### Refresh a dataset from Dataflow

To refresh a dataset created from a Dataflow, you can, if you have the permissions:

* Run the source Dataflow manually
* Define a run recurrence
* Configure an event trigger when a file is added in Work Data

For more information, see the page [Run, deploy and automate](/docs/documentation-fr-en/data-and-ai-project/dataflow/executer-deployer-et-automatiser.md).

## Monitor refreshes

Each refresh generates a detailed report accessible from the dataset card > Refresh tab > Click on the Refresh row.

You will find the following information there:

* The **Refresh mode** : Append or Replace
* Number of **lines** and **columns** processed by step (ingestion / transformation)
* **Applied configuration** : reminder of the parameters used
* **Status** :
  * ✅ **Success** : all data has been processed
  * ⚠️ **Warning** :
    * at least one file has an issue, but some data was imported
    * all data has already been processed
  * ❌ **Failure** : the import could not be completed
* **Logs** : technical details to investigate in case of a problem
* List of files processed in the case of a file source. In case of a problem with a file, you will see <i class="fa-triangle-exclamation">:triangle-exclamation:</i> with more information on hover over the icon:
  * Incompatible schema: the file was not imported
  * File already processed

In case of status **Failure** or **Warning**, you can display the ingestion step logs to identify the cause of the problem.

{% hint style="info" %}
Refresh dates now include the time as well as the date, and are standardized across all connector types (API, File, SQL).
{% endhint %}

{% hint style="info" %}
**Good to know** : For performance reasons, only the first detected error is identified and logged. The full file is not scanned.
{% endhint %}

<figure><img src="/files/4fa4dcd37d87dd0145064109f7506559125f30af" alt=""><figcaption></figcaption></figure>

### Subscribe to alerts

1. From the list of Datasets in your project or the Catalog
2. Click <i class="fa-bell">:bell:</i> `Subscribe` and choose Quality and/or Refresh

![](/files/7638dee22751e59779acf3185ab90c9be5f304ee)

You will then receive an in-app and email notification when an alert is raised or a refresh fails. You can find all your subscriptions in the tab <i class="fa-gear">:gear:</i> `Notification Management`.

{% hint style="info" %}
Notification Management

* The subscription option for refresh failures is automatically offered when a Dataset is created
* Subscription to Quality rules is only offered if at least one quality rule is defined on the dataset
* When a Quality rule is created, a subscription prompt is displayed if you are not already subscribed
* The owner of a Dataset can disable its automatic subscription to Quality rules from Notification Management
  {% endhint %}

## Add a Dataset to the library from the catalog <a href="#ajouter-un-dataset-depuis-le-catalogue" id="ajouter-un-dataset-depuis-le-catalogue"></a>

To add a Dataset from the catalog, go to the menu `Datasets`. Click on the button `Add a Dataset` then on `From the catalog`. You can search for a Dataset in the catalog by entering one or more keywords in the search bar or by using the filters.

* For sensitive Datasets **Internal** click `Add` to add it to the project.
* For sensitive Dataset **Sensitive** or **Restricted** click `Request access` to request access to the Dataset then explain your request in the field `Comment` and click `Send`.

When your request is processed by a platform manager, you will receive a notification.

## Export a Dataset

When exporting a dataset in CSV format, a configuration window appears before download. There you can choose the column separator and enable enclosing text values in quotation marks.

**Export options available**

* **Delimiter** : choose the character that will separate the columns in the exported file (comma by default, semicolon, tab, pipe, etc.).
* **Quotation marks** : if enabled, all text values are enclosed in double quotation marks. Quotation marks present in values are automatically doubled (in accordance with RFC 4180).