Migrating from Google BigQuery Legacy to Google BigQuery V2

Updated 5 minutes ago by James Dietrich

Google BigQuery Legacy will be switched off on 1 January 2027, so move to Google BigQuery V2 before that date.

This guide is for anyone who queries Dreamdata data in BigQuery Legacy, or who maintains dashboards, scheduled queries or pipelines that read from it.

The move has two parts and you can do them separately:

1. Access. You subscribe to a V2 listing in your own Google Cloud project, instead of reading a dataset shared from Dreamdata's project.
2. Queries. The V2 schema is different from Legacy. There are six tables instead of ten, and many flat columns are now nested records.

Legacy and V2 can run side by side while you rewrite your queries, so you don't need to switch over in one go.

Why move to V2?

• You control access. The dataset lives in your own project, and you manage who can see it with your own IAM (Identity and Access Management) settings.
• No billing project setup. On Legacy you have to set your own billing project, or queries fail with a permissions error. On V2 this step isn't needed.
• Lower query costs. V2 tables are partitioned by month on timestamp, and events, spend and stages are also clustered, so a query over a date range reads less data.
• New data. Signals, stage transitions, journey settings, branded-search labels, ad accounts and the Google advertising channel are only available in V2. Legacy will not get new features.
• Field descriptions. Every V2 column has a description you can read in the BigQuery console.

A note on names

The integration is called Google BigQuery V2 in the Dreamdata Platform, but the dataset in the listing is called data_platform_v3. You choose your own dataset name when you subscribe, so the name in your project may be different.

This guide uses your_project.your_dataset for your V2 dataset and dreamdata.your_legacy_dataset for your Legacy dataset. Replace them with your own names.

Before you start

You need a Google Cloud project and these three permissions in it:

• serviceusage.services.enable
• analyticshub.listings.subscribe
• bigquery.datasets.create

To run queries on the V2 dataset, you also need the BigQuery Job User role.

If your Google Cloud project is in the US, you need one extra step. The V2 listing is in the EU, and Dreamdata provides a US copy of it. To use the US copy, run this command in the project where you subscribed. Replace dreamdata with your dataset name. This step is free.

ALTER SCHEMA `dreamdata`
          ADD REPLICA IF NOT EXISTS `us`
          OPTIONS(location='us');

Make a list of everything that reads your Legacy dataset, so you know what to rewrite. This could include:

• Scheduled queries
• dbt models
• Looker Studio or Power BI dashboards
• Reverse ETL jobs or other pipelines

Migration steps

Set up V2 first, then rewrite and check your queries while Legacy keeps running.

1. In the Dreamdata Platform, go to Data Platform → Data Access → Google BigQuery V2.
2. Add at least one Google user or group email. A group email means access stays in place when someone leaves your team.
3. Wait for the next full data modelling run to finish. The See your Listing button stays greyed out until then.
4. Click See your Listing to open the Analytics Hub listing, choose your project, and give the dataset a name.
5. Rewrite your queries against the V2 dataset. See Rewriting your queries below.
6. Run the checks in Checking your numbers below on both datasets and compare the results.
7. Point your dashboards and pipelines at V2.
8. Contact Dreamdata support to switch off your Legacy dataset.

For more detail on steps 1 to 4, see Google BigQuery V2.

Which table replaces which

The ten Legacy tables become six V2 tables, and one Legacy table has no V2 equivalent.

New in V2

Removed in V2

These Legacy fields have no V2 replacement.

Rewriting your queries

Some changes make a query fail with an error, which is easy to spot. Others let the query run but return different numbers, so check these carefully.

Changes that affect your numbers

1. Attribution models are in an array. In Legacy, revenue_attribution has one row per attribution model, and you pick a model with WHERE attributionModel = 'Last Touch'. In V2, attribution has one row per stage and session, with all models in a repeated attribution field. Use UNNEST and filter on model. If you don't filter on a model, your totals are multiplied by the number of attribution models on your account.

2. Events are stored once per company. When a contact belongs to more than one company, V2 stores the same event once for each company. dd_event_id and dd_session_id are unique per row, not per real event. To count events, use COUNT(DISTINCT dd_event_activity_id). To count sessions, use COUNT(DISTINCT dd_session_activity_id). Don't use dd_is_primary_event for counting, because it is deprecated.

3. Company and contact details are not on events or attribution. Legacy had about 25 company and contact columns on every event and attribution row, such as companyName, industry, number_of_employees, jobTitle and seniority. In V2, join companies on dd_company_id for company details, and join contacts on dd_contact_id for contact details.

The attribution table is session-level only and has no event columns, such as event, url or utm_*. Use events for those.

4. Revenue is now called stages.

journey.start_date works differently from lookback_start_date. V2 has a journey record with type, start_date and end_date, and start_date is empty (NULL) when the journey covers all tracked data.

5. Several IDs are now one.

6. Ad impressions are counted differently. Rows with dd_tracking_type = 'exposure' are impressions, such as linkedin_ad_impression. These rows have an empty dd_session_activity_id and a quantity above 1.

COUNT(DISTINCT dd_session_activity_id) leaves these rows out, so session counts can be much lower than in Legacy if you run LinkedIn ads. To count real sessions, filter on dd_tracking_type = 'activity'. To count impressions, use SUM(quantity) on the exposure rows.

Changes that cause errors

• Column names change from camelCase to snake_case, and IDs start with dd_.
• The event column is now event_name.
• conversion is now event.is_conversion, and conversionType is now event.conversion_name.
• custom_properties is a JSON column instead of a string, so use JSON_VALUE() instead of JSON_EXTRACT_SCALAR().
• Ad hierarchy fields are nested. For example, adHierarchy_level_1_name is now session.ad_hierarchy.level_1.name.

Filter on dates to keep costs down

V2 tables are partitioned by month on timestamp, so always filter on timestamp. A query without a date filter still runs, but it reads the whole table.

On attribution, timestamp is the session start time. Filtering only on stage.timestamp does not reduce the data read.

Example: attributed revenue by channel

Legacy

SELECT
            channel,
            ROUND(SUM(attributableRevenue), 0) AS attributed_revenue,
            ROUND(SUM(attributableDeal), 2)    AS attributed_deals
          FROM `dreamdata.your_legacy_dataset.revenue_attribution`
          WHERE attributionModel = 'Last Touch'
            AND revenueModel     = 'Closed Won'
            AND revenueTimestamp >= '2026-01-01'
          GROUP BY channel
          ORDER BY attributed_revenue DESC

V2

SELECT
            a.session.channel       AS channel,
            ROUND(SUM(m.value), 0)  AS attributed_revenue,
            ROUND(SUM(m.weight), 2) AS attributed_deals
          FROM `your_project.your_dataset.attribution` AS a,
               UNNEST(a.attribution) AS m
          WHERE m.model            = 'Last Touch'
            AND a.stage.name       = 'Closed Won'
            AND a.stage.timestamp >= '2026-01-01'
          GROUP BY channel
          ORDER BY attributed_revenue DESC

Example: events by company industry and size

Legacy

SELECT
            industry,
            number_of_employees,
            COUNT(*) AS events
          FROM `dreamdata.your_legacy_dataset.events`
          WHERE timestamp >= '2026-06-01' AND timestamp < '2026-07-01'
            AND industry IS NOT NULL
          GROUP BY industry, number_of_employees
          ORDER BY events DESC

V2

SELECT
            c.properties.industry            AS industry,
            c.properties.number_of_employees AS number_of_employees,
            COUNT(*)                         AS events
          FROM `your_project.your_dataset.events` AS e
          JOIN `your_project.your_dataset.companies` AS c
            ON c.dd_company_id = e.dd_company_id
          WHERE e.timestamp >= '2026-06-01' AND e.timestamp < '2026-07-01'
            AND c.properties.industry IS NOT NULL
          GROUP BY industry, number_of_employees
          ORDER BY events DESC

This example uses COUNT(*) so the result matches Legacy. To count unique events, use COUNT(DISTINCT e.dd_event_activity_id).

Example: sessions per month by channel

Legacy

SELECT
            DATE_TRUNC(DATE(timestamp), MONTH) AS month,
            channel,
            COUNT(*) AS sessions
          FROM `dreamdata.your_legacy_dataset.sessions`
          WHERE timestamp >= '2026-04-01' AND timestamp < '2026-07-01'
          GROUP BY month, channel
          ORDER BY month, sessions DESC

V2

SELECT
            DATE_TRUNC(DATE(timestamp), MONTH)     AS month,
            session.channel                        AS channel,
            COUNT(DISTINCT dd_session_activity_id) AS sessions
          FROM `your_project.your_dataset.events`
          WHERE timestamp >= '2026-04-01' AND timestamp < '2026-07-01'
            AND dd_event_session_order = 1
            AND dd_tracking_type = 'activity'
          GROUP BY month, channel
          ORDER BY month, sessions DESC

This V2 query counts real sessions only, so it will be lower than Legacy if you have ad impressions. To match the Legacy COUNT(*), remove the dd_tracking_type filter and use COUNT(*).

Checking your numbers

Before you switch over, run these four checks on both datasets and compare the results. Run the Legacy and V2 queries separately, because the two datasets can be in different regions and BigQuery can't combine them in one query.

1. Attributed revenue by channel. The results should match exactly. If V2 is a multiple of Legacy, the query is missing a filter on the attribution model.
2. Spend by channel by month. The results should match exactly.
3. Stage count and total value by stage name. The results should match exactly.
4. Sessions per month. The V2 rows for activity and exposure should add up to the Legacy total.

Check 1: attributed revenue by channel

-- Legacy
          SELECT channel, ROUND(SUM(attributableRevenue), 0) AS attributed_revenue
          FROM `dreamdata.your_legacy_dataset.revenue_attribution`
          WHERE attributionModel  = 'Last Touch'
            AND revenueModel      = 'Closed Won'
            AND revenueTimestamp >= '2026-01-01' AND revenueTimestamp < '2027-01-01'
          GROUP BY channel ORDER BY channel;
          
          -- V2
          SELECT a.session.channel AS channel, ROUND(SUM(m.value), 0) AS attributed_revenue
          FROM `your_project.your_dataset.attribution` AS a, UNNEST(a.attribution) AS m
          WHERE m.model            = 'Last Touch'
            AND a.stage.name       = 'Closed Won'
            AND a.stage.timestamp >= '2026-01-01' AND a.stage.timestamp < '2027-01-01'
          GROUP BY channel ORDER BY channel;

Check 2: spend by channel by month

-- Legacy
          SELECT DATE_TRUNC(DATE(timestamp), MONTH) AS month, channel,
                 ROUND(SUM(cost), 2) AS cost, ROUND(SUM(clicks), 0) AS clicks
          FROM `dreamdata.your_legacy_dataset.paid_ads`
          WHERE timestamp >= '2026-01-01' AND timestamp < '2027-01-01'
          GROUP BY month, channel ORDER BY month, channel;
          
          -- V2
          SELECT DATE_TRUNC(DATE(timestamp), MONTH) AS month, channel,
                 ROUND(SUM(cost), 2) AS cost, ROUND(SUM(clicks), 0) AS clicks
          FROM `your_project.your_dataset.spend`
          WHERE timestamp >= '2026-01-01' AND timestamp < '2027-01-01'
          GROUP BY month, channel ORDER BY month, channel;

Check 3: stage count and value by stage name

-- Legacy
          SELECT revenueModel AS stage_name, COUNT(*) AS stages, ROUND(SUM(revenue), 2) AS total_value
          FROM `dreamdata.your_legacy_dataset.revenue`
          WHERE revenueTimestamp >= '2026-01-01' AND revenueTimestamp < '2027-01-01'
          GROUP BY stage_name ORDER BY stage_name;
          
          -- V2
          SELECT stage_name, COUNT(*) AS stages, ROUND(SUM(value), 2) AS total_value
          FROM `your_project.your_dataset.stages`
          WHERE timestamp >= '2026-01-01' AND timestamp < '2027-01-01'
          GROUP BY stage_name ORDER BY stage_name;

Check 4: sessions per month

-- Legacy
          SELECT DATE_TRUNC(DATE(timestamp), MONTH) AS month,
                 COUNT(*) AS session_rows, SUM(quantity) AS quantity
          FROM `dreamdata.your_legacy_dataset.sessions`
          WHERE timestamp >= '2026-01-01' AND timestamp < '2027-01-01'
          GROUP BY month ORDER BY month;
          
          -- V2: the activity and exposure rows for each month add up to the Legacy total
          SELECT DATE_TRUNC(DATE(timestamp), MONTH) AS month, dd_tracking_type,
                 COUNT(*)                               AS session_rows,
                 COUNT(DISTINCT dd_session_activity_id) AS sessions,
                 SUM(quantity)                          AS quantity
          FROM `your_project.your_dataset.events`
          WHERE timestamp >= '2026-01-01' AND timestamp < '2027-01-01'
            AND dd_event_session_order = 1
          GROUP BY month, dd_tracking_type ORDER BY month, dd_tracking_type;

The sessions column is 0 on exposure rows, because impressions have no session activity ID.

If your numbers don't match

• If a total is a whole multiple of the Legacy total, check that you filter on one attribution model after UNNEST.
• If a session count is much lower than Legacy, check for exposure rows.
• If a count is slightly higher than Legacy, check that you count distinct activity IDs, because events are stored once per company.
• If rows are missing, check for an inner join to companies, which leaves out events with no dd_company_id.

Full field mapping

These tables list every Legacy field and where to find it in V2. An empty V2 cell means the field was removed.

companies → companies

contacts → contacts

events → events

The sessions table maps to events in the same way, using WHERE dd_event_session_order = 1. Its page_content field was removed.

session_performance, paid_session_performance and revenue_attribution → attribution

All three Legacy tables become one. The attribution table is session-level only and has no event columns.

revenue → stages

dd_stage_id is new and is the unique ID for each row in stages.

paid_ads → spend

google_search

The google_search table has no V2 equivalent.

FAQ

Can I keep using Legacy while I move?

Yes. Legacy and V2 run side by side until you ask for Legacy to be switched off, or until 1 January 2027.

Why does the contacts table have fewer rows?

V2 has one row per contact. Legacy had one row per contact and company pair.

Who pays for queries on V2?

Dreamdata pays to store the data. You pay for the queries you run, in the same way as for any other table in your project.

Where can I get help?

Contact Dreamdata support through your account manager.

How did we do?