---
title: "Collect Vélib' availability every hour with SQL only"
description: "Find out how to collect the availability of every Vélib' station in Paris every hour, build its history and model it with SQL views for Superset"
url: https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/tutorials-velib-availability
lang: en
lastUpdated: 2026-11-03
---
> For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/de/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/de/llms-full.txt.

# Collect Vélib' availability every hour with SQL only

## Objective

This guide walks you through collecting the availability of every Vélib' station in Paris from a public API, every hour, building a clean history of it and modelling it with SQL views ready for Apache Superset. You connect the API with an [HTTP REST source](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-sources-rest.md), create the history table in [Lakehouse Manager](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-tables.md), date and deduplicate each collection with [Data Processing Engine](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-sql.md), then model the history with [views](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-views.md). Everything happens in the Data Platform interface and in SQL: not a single line of Python.

The Vélib' API only gives the current state of each station. By polling it regularly, you build the history it does not keep, and that history is what you analyse.

By the end you will have the table `velib_raw`, which grows on its own every hour, and five SQL views on top of it, ready to query from Apache Superset. The second part of the guide, [Build a Vélib' availability dashboard in Superset](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/tutorials-velib-availability-superset-dashboard.md), turns them into a dashboard.

## Requirements

To follow this guide you need:

- a Data Platform project. This guide uses a project named `velib-availability`.
- nothing else to collect the data: the API is public and needs no account or key. Step 8 deploys an Apache Superset application, billed for the time it stays deployed: see [Data Platform pricing](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/manage-pricing.md).

**Note:** **About the data:** [Vélib - Vélos et bornes - Disponibilité temps réel](https://opendata.paris.fr/explore/dataset/velib-disponibilite-en-temps-reel/), © Smovengo / Syndicat Autolib' Vélib' Métropole, published by Ville de Paris under the [Open Database License (ODbL)](https://opendatacommons.org/licenses/odbl/).

When you reuse these data, mention their producers and the ODbL licence. transport.data.gouv.fr applies [special conditions of use](https://doc.transport.data.gouv.fr/le-point-d-acces-national/cadre-juridique/conditions-dutilisation-des-donnees/licence-odbl) to the share-alike clause of the licence: it only covers a derived database of the same nature, granularity, temporal conditions and geographic extent as the original data. Check these conditions before you publish data built from this dataset.

We recommend completing the [Getting Started guides](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/landing-page-getting-started.md) first. This guide assumes you are comfortable with the main components of Data Platform.

## What you will build

| Stage                                 | Component                                                                                                                                                                                                                                 | Result                                                     |
| ------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------- |
| 1. Connect the API                    | [HTTP REST source](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-sources-rest.md)                                                                                                                             | A source that returns every station                        |
| 2. Check the attributes               | [Analyzer](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/landing-page-connectors-analyzer.md)                                                                                                                            | Types you can rely on                                      |
| 3. Create the history table           | [Lakehouse Manager tables](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-tables.md)                                                                                                                    | `velib_raw`, with two collection columns                   |
| 4. Date each collection               | [SQL action](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-sql.md)                                                                                                                                           | Every row knows when it was collected                      |
| 5. Keep one row per station and hour  | [Deduplicate action](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-deduplicate.md)                                                                                                                           | No duplicate, even when both runs of an hour succeed       |
| 6. Schedule the collection every hour | [Workflow](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-workflows.md) and a [trigger](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-jobs-preferences.md)                                       | One collection per hour, with a second run as a safety net |
| 7. Model the data                     | [Lakehouse Manager views](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-views.md)                                                                                                                      | Five views ready for analysis                              |
| 8. Connect Superset                   | [Trino consumer](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-consumers-trino.md) and [Apache Superset](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/tutorials-install-apache-superset.md) | One Superset dataset per view                              |

## Instructions

### Step 1: Connecting the API

The dataset has an **export** endpoint that returns every station in one call:

```text
https://opendata.paris.fr/api/explore/v2.1/catalog/datasets/velib-disponibilite-en-temps-reel/exports/json
```

The `records` endpoint shown in the _API_ tab of Paris Data returns a limited number of results per call. The export endpoint returns every station at once: about 1,500 (1,519 on 22 October 2026).

1. Open **Connectors** > **Sources** and create a new source of type _HTTP REST_.

2. Name it `velib`.

3. In _Base URL_, enter `https://opendata.paris.fr/api/explore/v2.1/`. Keep _SSL Verify_ on and _Authentication mode_ set to **None**, then click **Connect**.

4. In _Endpoints to connect_, click **Add an endpoint** and fill in the endpoint:

   | Setting                                            | Value                                                              |
   | -------------------------------------------------- | ------------------------------------------------------------------ |
   | Method                                             | `GET`                                                              |
   | Endpoint                                           | `/catalog/datasets/velib-disponibilite-en-temps-reel/exports/json` |
   | Source object technical name, in the _Options_ tab | `stations`                                                         |
   | Pagination, in the _Options_ tab                   | None                                                               |

5. Click **Confirm**.

6. Click **Create**.

   The source lists one endpoint, `stations.json`.

Each station comes with its availability and its description:

```json
{
  "stationcode": "40001",
  "name": "Hôpital Mondor",
  "is_installed": "OUI",
  "capacity": 28,
  "numdocksavailable": 20,
  "numbikesavailable": 7,
  "mechanical": 3,
  "ebike": 4,
  "is_renting": "OUI",
  "is_returning": "OUI",
  "duedate": "2026-10-07T06:27:24+00:00",
  "coordonnees_geo": { "lon": 2.4537451531298, "lat": 48.798922410229 },
  "nom_arrondissement_communes": "Créteil",
  "code_insee_commune": "94028",
  "station_opening_hours": null
}
```

### Step 2: Checking the attributes

1. Open **Connectors** > **Analyzer**.
2. Select the source `velib`, then `stations.json`.
3. Click **Extract metadata**.

The [Analyzer](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-analyzer-extract-metadata.md) detects the types on a sample of 1,000 rows. Check them now: once the table is created, you cannot change the type of its attributes.

| Attribute                                    | Detected type | What to know                                                                                                                                                                                                            |
| -------------------------------------------- | ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `stationcode`                                | Double        | Station codes never start with a zero, so the number loses nothing. The views turn it back into text.                                                                                                                   |
| `code_insee_commune`                         | Double        | Same as above, but the views do not use it.                                                                                                                                                                             |
| `coordonnees_geo.lon`, `coordonnees_geo.lat` | String        | The nested object is split into two attributes. The views convert them to numbers.                                                                                                                                      |
| `duedate`                                    | DateTime      | The Analyzer adds two [blueprint rules](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-analyzer-blueprint-rules.md) on its own: they turn the text into a date and time, and keep it in UTC. |
| `station_opening_hours`                      | String        | Always empty. Clear its checkbox.                                                                                                                                                                                       |
| `is_installed`, `is_renting`, `is_returning` | String        | `OUI` or `NON`. The views turn them into booleans.                                                                                                                                                                      |

### Step 3: Creating the history table

#### Creating the table

1. Open **Lakehouse Manager** > **Tables** and click **New table** > **Create from a Connectors source**.

2. Select `stations.json` from the source `velib`, then click **Next**.

3. Name the table `velib_raw`, keep the dataset `default_dataset`, and keep the three options turned on: _Automatically build the table_, _Automatically load the table once_ and _Automatically generate Load action for later_.

4. Click **Create**.

   The table holds one row per station, about 1,500 rows. The nested coordinates become `coordonnees_geo_lat` and `coordonnees_geo_lon`.

#### Adding the collection columns

The API does not say when you collected a station. Two columns record it:

| Column           | Type     | Role                                |
| ---------------- | -------- | ----------------------------------- |
| `collected_at`   | Datetime | Exact time of the collection        |
| `collected_hour` | Datetime | Same time, rounded down to the hour |

1. Open the table `velib_raw`, then its _Schema_ tab.

2. Click **New attribute**, name it `collected_at`, select the data type _Datetime_ and click **Save**. Do the same for `collected_hour`.

3. Click **Build**: adding attributes changes the structure of the table.

#### Emptying the table

The first load has no collection time. Empty the table with a [Delete action](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-delete.md) before the first real collection:

1. Open **Data Processing Engine** > **Actions**, click **New action** and select the _Delete_ action.
2. In _Table list_, move `velib_raw` to _Table to empty_.
3. Click **Create**, then run the action.

In the [Explorer](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-explorer.md), `SELECT count(*) FROM velib_raw` returns 0.

### Step 4: Dating each collection

Create an [SQL action](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-sql.md) that dates the rows just loaded.

1. In **Data Processing Engine** > **Actions**, click **New action** and select the _SQL_ action.

2. Name it `Stamp collection time`.

3. In the _Tables_ panel, click the copy icon of `velib_raw`: an SQL action needs the full name `catalog.schema.table`, and the catalog name is specific to your project.

4. Paste the query below, with that full name in place of `<catalog>.default_dataset.velib_raw`:

   ```sql
   UPDATE <catalog>.default_dataset.velib_raw
   SET collected_at   = CAST(current_timestamp AT TIME ZONE 'UTC' AS timestamp(6)),
       collected_hour = date_trunc('hour', CAST(current_timestamp AT TIME ZONE 'UTC' AS timestamp(6)))
   WHERE collected_at IS NULL
   ```

5. Click **Validate**. Validation is a dry run: it checks the query without changing the table.

6. Click **Create**.

`WHERE collected_at IS NULL` only dates the rows of the current collection: earlier collections keep their time. Times are stored in UTC, like `duedate`.

#### Checking a first collection

1. Run the _Load_ action of `velib_raw`, generated when you created the table.
2. Run `Stamp collection time`.
3. In the Explorer, run:

   ```sql
   SELECT count(*) AS rows, count(collected_at) AS stamped, min(collected_at) AS first_collection, max(collected_at) AS last_collection
   FROM velib_raw
   ```

   You get one row per station, all stamped, with a single collection time in UTC.

### Step 5: Keeping one row per station and hour

When both runs of an hour succeed, each station has two rows for the same hour, possibly with different figures. The [Deduplicate action](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-actions-deduplicate.md) keeps one row per station and hour: the one with the most recent `collected_at`.

If a run fails between the load and the stamp, the next run loads the stations again, and `Stamp collection time` dates both loads at once, with the same `collected_at`. The action still keeps one row per station and hour, but there is no guarantee which of the two it keeps.

1. In **Data Processing Engine** > **Actions**, click **New action** and select the _Deduplicate_ action.

2. Fill in the action:

   | Setting                     | Value                                                                                                                                                |
   | --------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
   | Table                       | `velib_raw` from `default_dataset`                                                                                                                   |
   | Key column(s)               | `stationcode`, `collected_hour`                                                                                                                      |
   | Row to keep                 | _Most recent / oldest entry_, then **Most recent**, ordered by the column `collected_at`                                                             |
   | Keep a backup of this table | Off: [Time Travel](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-time-travel.md) already keeps the previous state |

3. Click **Scan**: it estimates, without changing anything, how many rows the key would remove. On a single collection, it finds no duplicate.

4. Click **Create**. The action is named `Deduplicate - velib_raw`.

The Deduplicate action reads the whole table at each run, and the table grows by about 36,000 rows a day. Its run time, billed in [DPU hours](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/manage-pricing.md#billing-units), grows with the history.

### Step 6: Scheduling the collection every hour

#### Creating the workflow

1. Open **Data Processing Engine** > **Workflows** and click **New workflow**.

2. Name it `Vélib collection`.

3. Add one action per stage, in this order:

   | Stage | Action                                      |
   | ----- | ------------------------------------------- |
   | 1     | `velib_raw`, the _Load_ action of the table |
   | 2     | `Stamp collection time`                     |
   | 3     | `Deduplicate - velib_raw`                   |

4. Click **Create**.

Each action must be in its own stage: actions of the same stage run at the same time, and each step here needs the previous one to be finished.

#### Scheduling it

In the _Preferences_ tab of the workflow, in the _Triggers_ section, click **Add** and fill in the time-based [trigger](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/dpe-jobs-preferences.md#time-based-trigger):

| Setting    | Value           |
| ---------- | --------------- |
| Name       | `Twice an hour` |
| Type       | Cron            |
| Mode       | Advanced        |
| Expression | `7,37 * * * *`  |

Click **Confirm**.

The goal is one collection per hour. The workflow runs twice, at minutes 7 and 37 of every hour, UTC: the second run is a safety net, in case the first one fails. Both runs of an hour get the same `collected_hour`, and the Deduplicate action keeps the most recent one, so each hour holds one row per station either way. The expression avoids minute 0 on purpose, because many scheduled jobs start at that moment.

#### Checking the collection

After a few hours, each hour holds exactly one row per station:

```sql
SELECT collected_hour, count(*) AS stations
FROM velib_raw
GROUP BY collected_hour
ORDER BY collected_hour DESC
```

Every line shows the same number of stations, about 1,500. A missing hour points to runs that failed; a higher number to a deduplication that did not run. Step 7 gives a query that lists the missing hours.

### Step 7: Modelling the data with views

Five views turn the raw history into data ready for analysis. Create each one in **Lakehouse Manager** > **Views** > **New view**, in the dataset `default_dataset`, in the order below: each view reads the ones before it.

| View              | One row is                         | Used for                        |
| ----------------- | ---------------------------------- | ------------------------------- |
| `station_hourly`  | a station at a given hour          | the base of every other view    |
| `network_hourly`  | the whole network at a given hour  | the daily rhythm                |
| `station_profile` | a station over the whole period    | rankings of stations            |
| `velib_daily`     | the whole network on a given day   | trends, day after day           |
| `station_current` | a station at the latest collection | the map and the current figures |

**Note:** **Note:** inside a view, a table needs only its name, `velib_raw`: the catalog and schema are resolved for you. A view read by another view needs its full name, `catalog.schema.view`. Replace `<catalog>` with the catalog name of your project, the one you copied in Step 4.

#### station\_hourly

Converts the types, renames the columns and adds the collection hour in Paris time.

```sql
SELECT
    CAST(CAST(stationcode AS BIGINT) AS VARCHAR)                         AS station_code,
    name                                                                 AS station_name,
    nom_arrondissement_communes                                          AS commune,
    CAST(coordonnees_geo_lat AS DOUBLE)                                  AS lat,
    CAST(coordonnees_geo_lon AS DOUBLE)                                  AS lon,
    capacity,
    numbikesavailable                                                    AS bikes,
    mechanical,
    ebike,
    numdocksavailable                                                    AS free_docks,
    capacity - numbikesavailable - numdocksavailable                     AS out_of_service_docks,
    is_installed = 'OUI' AND is_renting = 'OUI'                          AS is_open,
    collected_hour,
    CAST(with_timezone(collected_hour, 'UTC') AT TIME ZONE 'Europe/Paris' AS timestamp(6)) AS collected_hour_paris
FROM velib_raw
```

`capacity - numbikesavailable - numdocksavailable` gives the docks that are neither free nor holding a bike: docks out of service.

`collected_hour` stays in UTC, and every grouping by hour uses it: a UTC hour is never repeated or skipped. `collected_hour_paris` gives the same hour in Paris time, for display, for the hour of the day and for the day in `velib_daily`. Do not group hours on it: when the clocks go back in October, two UTC hours share the same Paris time, and when they go forward in March, one Paris hour does not exist. The `CAST` keeps the Paris time without a time zone, so that Superset handles it as an ordinary date.

#### network\_hourly

```sql
SELECT
    collected_hour,
    collected_hour_paris,
    hour(collected_hour_paris)                                            AS hour_of_day,
    day_of_week(collected_hour_paris)                                     AS day_of_week,
    CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
    count(*)                                                              AS stations,
    sum(bikes)                                                            AS bikes,
    sum(mechanical)                                                       AS mechanical,
    sum(ebike)                                                            AS ebike,
    sum(free_docks)                                                       AS free_docks,
    sum(out_of_service_docks)                                             AS out_of_service_docks,
    count_if(bikes = 0)                                                   AS empty_stations,
    count_if(free_docks = 0)                                              AS full_stations,
    round(100.0 * sum(bikes) / sum(capacity), 1)                          AS fill_rate_pct
FROM <catalog>.default_dataset.station_hourly
WHERE is_open
GROUP BY collected_hour, collected_hour_paris
```

Only open stations count: a closed station is neither empty nor full for a user. The hour of the day and the day of the week come from the Paris time, so that 8 a.m. means 8 a.m. in Paris all year round.

#### station\_profile

```sql
SELECT
    station_code,
    max(station_name)                                                  AS station_name,
    max(commune)                                                       AS commune,
    max(lat)                                                           AS lat,
    max(lon)                                                           AS lon,
    max(capacity)                                                      AS capacity,
    count(*)                                                           AS observed_hours,
    round(100e0 * count_if(bikes = 0) / count(*), 1)                   AS empty_hours_pct,
    round(100e0 * count_if(free_docks = 0) / count(*), 1)              AS full_hours_pct,
    round(100e0 * sum(bikes) / nullif(sum(capacity), 0), 1)            AS avg_fill_rate_pct,
    round(avg(out_of_service_docks), 1)                                AS avg_out_of_service_docks
FROM <catalog>.default_dataset.station_hourly
WHERE is_open
GROUP BY station_code
```

#### velib\_daily

```sql
SELECT
    CAST(collected_hour_paris AS date)                                    AS day,
    CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
    count(*)                                                              AS observed_hours,
    min(bikes)                                                            AS min_bikes,
    max(bikes)                                                            AS max_bikes,
    max(bikes) - min(bikes)                                               AS daily_swing,
    round(avg(bikes))                                                     AS avg_bikes,
    round(avg(fill_rate_pct), 1)                                          AS avg_fill_rate_pct,
    sum(empty_stations)                                                   AS empty_station_hours,
    max(empty_stations)                                                   AS peak_empty_stations
FROM <catalog>.default_dataset.network_hourly
GROUP BY CAST(collected_hour_paris AS date), CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END
```

| Column                | Meaning                                                                                                                                    |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `daily_swing`         | Difference between the most and the fewest bikes parked during the day: an estimate of the bikes in use at the peak, not a number of trips |
| `empty_station_hours` | Total of hours spent empty by all stations: a station empty for 3 hours counts for 3                                                       |
| `observed_hours`      | Number of collections of the day: 24 on a complete day, 23 or 25 on the days the clocks change                                             |

#### station\_current

```sql
SELECT
    station_code,
    station_name,
    commune,
    lat,
    lon,
    capacity,
    bikes,
    mechanical,
    ebike,
    free_docks,
    out_of_service_docks,
    is_open,
    round(100e0 * bikes / nullif(capacity, 0), 1)                     AS fill_rate_pct,
    CASE
        WHEN NOT is_open      THEN 'Closed'
        WHEN bikes = 0        THEN 'Empty'
        WHEN free_docks = 0   THEN 'Full'
        ELSE 'Available'
    END                                                                AS status,
    collected_hour,
    collected_hour_paris
FROM <catalog>.default_dataset.station_hourly
WHERE collected_hour = (
    SELECT max(collected_hour)
    FROM <catalog>.default_dataset.station_hourly
)
```

#### Checking the views

In the Explorer, prefix each view with its dataset:

```sql
SELECT count(*) AS stations, max(collected_hour_paris) AS last_collection, sum(bikes) AS bikes
FROM default_dataset.station_current
```

You get the number of stations, about 1,500, and the latest collection hour, in Paris time.

To list the hours with no collection, generate every hour of the period and keep those missing from `station_hourly`. The query works on the UTC hour, so clock changes create no false gap. Inside a `WITH`, the view needs its full name:

```sql
WITH bounds AS (
    SELECT min(collected_hour) AS first_hour, max(collected_hour) AS last_hour
    FROM <catalog>.default_dataset.station_hourly
),
hours AS (
    SELECT h AS collected_hour
    FROM bounds
    CROSS JOIN UNNEST(sequence(first_hour, last_hour, INTERVAL '1' HOUR)) AS t(h)
)
SELECT h.collected_hour
FROM hours h
LEFT JOIN (
    SELECT DISTINCT collected_hour
    FROM <catalog>.default_dataset.station_hourly
) s
  ON s.collected_hour = h.collected_hour
WHERE s.collected_hour IS NULL
ORDER BY h.collected_hour
```

A run can fail for a transient reason: the second run of each hour fills the gap.

Trino's `sequence` function returns at most 10,000 entries, that is about 416 days of hourly values: if the history is longer, restrict the period in `bounds` with a `WHERE` on `collected_hour`.

### Step 8: Connecting Superset

All the data is now ready in the Lakehouse. To analyse it, create an Apache Superset application:

1. Create a Trino connection by following the [Generate a Trino connection string for external tools](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/connectors-consumers-trino.md) guide. Copy the connection string before you close the window: it is not shown again.

2. Create the application and connect it to Trino by following Steps 1 to 3 of the [Deploying Apache Superset on OVHcloud Data Platform](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/tutorials-install-apache-superset.md) guide.

#### Creating the datasets in Superset

1. In Superset, open **Datasets** and click **+ Dataset**.
2. Select the Trino database, then the schema `default_dataset` and the view `station_current`.
3. Click **Create and explore dataset**.
4. Do the same for `network_hourly`, `station_profile` and `velib_daily`.

`station_hourly` only feeds the other views: it does not need a dataset.

## Good to know

- **The API dates the station, not the change.** `duedate` can stay the same while bikes come and go, so it cannot tell two states apart: see the "[Why duedate is not the deduplication key](#why-duedate-is-not-the-deduplication-key)" section.
- **Schedules are in UTC.** Paris is one or two hours ahead, depending on the season.
- **Load adds rows.** Each run of the _Load_ action appends the current state of every station: the history grows without any setting.

### Why duedate is not the deduplication key

The obvious key for a history of stations is the station and its update time, `stationcode` and `duedate`. It looks right: two collections of a station that did not update return the same `duedate`.

[Time Travel](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/lakehouse-manager-time-travel.md) shows it is wrong. On the table as it was before a first deduplication, 676 of the 1,463 duplicated keys had different figures. For example, the station **Turbigo - Française** had the same `duedate` in two collections, 06:28:26 UTC, with different figures:

| Bikes | Mechanical | Electric | Free docks |
| ----- | ---------- | -------- | ---------- |
| 9     | 7          | 2        | 7          |
| 10    | 7          | 3        | 6          |

Someone returned an electric bike between the two collections, and `duedate` did not change. Deduplicating on `duedate` would have kept one of the two rows at random, and lost a real change. The collection time, which you control, is a key you can trust.

The check that revealed it:

```sql
SELECT count(*) AS keys_with_different_values
FROM (
    SELECT stationcode, duedate
    FROM velib_raw FOR VERSION AS OF <snapshot ID before the deduplication>
    GROUP BY stationcode, duedate
    HAVING count(*) > 1
       AND (count(DISTINCT numbikesavailable) > 1 OR count(DISTINCT numdocksavailable) > 1)
)
```

Check that a deduplication key identifies truly identical rows before you trust it: the Scan of the Deduplicate action counts duplicates, but does not say whether they are identical.

## What you have built

You have a history of every Vélib' station, collected every hour from a live public API, with a second run as a safety net, cleaned of duplicates and modelled by five SQL views, all without a line of Python. Four Superset datasets sit on top of it.

The next part, [Build a Vélib' availability dashboard in Superset](https://docs.ovhcloud.com/de/guides/public-cloud/data-platform/tutorials-velib-availability-superset-dashboard.md), turns these datasets into a dashboard: bikes available now, daily rhythm, problem stations and daily trends.

## Go further

For training or technical assistance implementing our solutions, contact your sales representative or visit our [Professional Services](https://www.ovhcloud.com/de/professional-services/) page to request a quote and have your project analysed by our experts.

Ask questions, give your feedback and interact directly with the team building the Data Platform on the dedicated [Discord channel](https://discord.gg/ovhcloud).

If you need support with your OVHcloud services, create a request in our [Help Centre](https://help.ovhcloud.com/csm?id=csm_get_help).

Join our [community of users](https://community.ovhcloud.com/).
