How to populate for all fields with union all

I have two CTE's with different data that needs to be union together. The issue that I am having is try to get all the fields to populate with all the data from both CTE's. One CTE has one row of data.

with software_production as (
    SELECT DISTINCT
        f.client_id,
        m.firmware,
        f.created_at,
        --f.packing_date same as first configuration_change_timestamp
    FROM `production-us`.`dev_warehouse`.`dev_dataset` AS f , UNNEST(motion_trackers) AS m
    WHERE f.client_id IS NOT NULL
        and f.client_id = 'US-211111'
        AND f.client_id != '-2'
),
next_created_at AS (
    SELECT
        *,
        LEAD(created_at)
            OVER (PARTITION BY client_id  ORDER BY created_at ASC) AS created_at_lead
    FROM software_production
),
next_created_at_final AS (
    SELECT
        * EXCEPT(created_at_lead),
        CASE
            WHEN created_at_lead IS NULL
            THEN TIMESTAMP('9999-12-31 00:00:00')
            ELSE created_at_lead
        END AS created_at_lead
    FROM next_created_at
    WHERE created_at_lead IS NULL
        OR (created_at != created_at_lead)
)
select * from next_created_at_final

The data looks like this:

client_id       |firmware|created_at             |created_at_lead        |
----------------+--------+-----------------------+-----------------------+
US-211111       |4.3.1   |2023-11-22 11:21:10.000|9999-12-30 19:00:00.000|

The second part of the code for what I'm doing a union on looks like:

with first_dt_versions as (
    select
        client_id,
        system_created_at       as created_at,
        device.dt_semantic_version      as device_dt_version,
        configuration_change_timestamp --first date matched the packing_date
    from `production-us`.`dev_warehouse`.`dev_client_system`
    where device.dt_semantic_version is not null
    and client_id = 'US-211111' 
),
dt_conversion as (
    select distinct
        client_id,
        created_at,
        device_dt_version,
        configuration_change_timestamp                   as dt_version_configuration_start_date,
        lead(configuration_change_timestamp)
            over(PARTITION BY client_id  
            ORDER BY configuration_change_timestamp ASC) as dt_version_configuration_end_date,
    from first_dt_versions 
    group by
        client_id,  
        created_at,
        device_dt_version,
        configuration_change_timestamp
)
select * from dt_conversion

The returned data looks like such:

client_id|created_at             |device_dt_version|dt_version_configuration_start_date|dt_version_configuration_end_date|
---------+-----------------------+-----------------+-----------------------------------+---------------------------------+
US-211111|2023-11-22 11:21:10.000|2.16.4           |            2024-02-06 05:09:58.000|          2024-02-07 05:42:31.000|
US-211111|2023-11-22 11:21:10.000|2.17.1           |            2024-03-13 04:49:18.000|          2024-03-15 18:01:00.000|
US-211111|2023-11-22 11:21:10.000|2.18.1           |            2024-03-29 17:34:48.000|          2024-03-30 13:03:31.000|
US-211111|2023-11-22 11:21:10.000|2.17.1           |            2024-02-26 05:23:34.000|          2024-02-28 17:36:46.000|
US-211111|2023-11-22 11:21:10.000|2.16.4           |            2024-02-09 05:57:00.000|          2024-02-13 18:46:01.000|
US-211111|2023-11-22 11:21:10.000|2.17.0           |            2024-02-17 09:01:29.000|          2024-02-17 09:01:29.000|

When I union both sets of data I get the following results:

client_id|device_dt_version|firmware|dt_version_configuration_start_date|dt_version_configuration_end_date|
----------------+-----------------+--------+-----------------------------------+---------------------------------+
US-211111       |                 |4.3.1   |                                   |                                 |
US-211111       |2.9.1            |        |            2023-11-22 11:21:10.000|          2024-01-17 17:46:13.000|
US-211111       |2.13.2           |        |            2024-01-17 17:46:13.000|          2024-01-18 04:57:31.000|
US-211111       |2.16.2           |        |            2024-01-18 04:57:31.000|          2024-01-18 04:57:31.000|
US-211111       |2.13.2           |        |            2024-01-18 04:57:31.000|          2024-01-19 04:39:40.000|

What I'm expecting is:

client_id|device_dt_version|firmware|dt_version_configuration_start_date|dt_version_configuration_end_date|
---------+-----------------+--------+-----------------------------------+---------------------------------+
US-211111|2.9.1            |4.3.1   |            2023-11-22 11:21:10.000|          2024-01-17 17:46:13.000|
US-211111|2.9.1            |4.3.1   |            2023-11-22 11:21:10.000|          2024-01-17 17:46:13.000|
US-211111|2.13.2           |4.3.1   |            2024-01-17 17:46:13.000|          2024-01-18 04:57:31.000|
US-211111|2.16.2           |4.3.1   |            2024-01-18 04:57:31.000|          2024-01-18 04:57:31.000|
US-211111|2.13.2           |4.3.1   |            2024-01-18 04:57:31.000|          2024-01-19 04:39:40.000|

I tried doing a join with the packing_date and configuration_change_timestamp because the packing_date and the first configuration_change_timestamp match along with the client_id and then doing a union but that still gave me the same results. Any help would be appreciated!

You should do a join on Client_id instead of a Union. Your expected results contains 5 rows with the first row duplicated. I suspect you actually want 4 rows returned with no duplicated rows.

SELECT c.client_id, 
       c.device_dt_version,
       f.firmware,
       c.dt_version_configuration_start_date,
       c.dt_version_configuration_end_date
FROM dt_conversion c
INNER JOIN next_created_at_final  f ON c.Client_id =fClient_id