Multi Source Nodes

Multisource nodes represent Coalesce's implementation of SQL UNIONs.

📘

Nodes can have multiple sources, using JOINs or UNIONs (or both) to ultimately create a new table/view.

Multi Source Strategy

There are 3 options for how a multi source node will load source tables into the target table.

INSERT (default option)

This will break down the operation into multiple INSERTs. This allows individual parts to fail before moving on to others, which can improve performance. This operation will retain duplicates.

UNION

This will make a UNION of sources and get rid of duplicates.

UNION ALL

This will make a UNION ALL of sources and keep duplicates.

Example UNION with Three Tables

Let's walk through a simple example using a multi source node to form a UNION of 3 tables. We're looking to consolidate all the crew members of the Nostromo onto one table, but they're currently scattered across three similar (but not identical) tables.

CREATE OR REPLACE TABLE NOSTROMO_SRC1 (
    N_CREWKEY NUMBER(38,0),
    N_FIRSTNAME VARCHAR(50),
    N_LASTNAME VARCHAR(50),
    N_COMMENT VARCHAR(152)
);

INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC1
(N_CREWKEY, N_FIRSTNAME, N_LASTNAME, N_COMMENT)
VALUES(1, 'Ellen', 'Ripley', 'Warrant Officer');
INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC1
(N_CREWKEY, N_FIRSTNAME, N_LASTNAME, N_COMMENT)
VALUES(2, 'Arthur', 'Dallas', 'Captain');
INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC1
(N_CREWKEY, N_FIRSTNAME, N_LASTNAME, N_COMMENT)
VALUES(3, 'Thomas', 'Kane', 'Executive Officer');

CREATE OR REPLACE TABLE NOSTROMO_SRC2 (
    N_CREWKEY NUMBER(38,0),
    N_FIRSTNAME VARCHAR(50),
  N_MIDDLEINITIAL VARCHAR(1),
    N_LASTNAME VARCHAR(50),
    N_COMMENT VARCHAR(152)
);

INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC2
(N_CREWKEY, N_FIRSTNAME, N_MIDDLEINITIAL, N_LASTNAME, N_COMMENT)
VALUES(4, 'Ash', NULL, NULL, 'Science Officer');
INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC2
(N_CREWKEY, N_FIRSTNAME, N_MIDDLEINITIAL, N_LASTNAME, N_COMMENT)
VALUES(5, 'Joan', 'M', 'Lambert', 'Navigation Officer');
INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC2
(N_CREWKEY, N_FIRSTNAME, N_MIDDLEINITIAL, N_LASTNAME, N_COMMENT)
VALUES(6, 'Dennis', 'M', 'Parker', 'Chief Engineer');

CREATE OR REPLACE TABLE NOSTROMO_SRC3 (
    N_CREWKEY NUMBER(38,0),
    N_FIRSTNAME VARCHAR(50),
    N_LASTNAME VARCHAR(50),
    N_COMMENT VARCHAR(152)
);

INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC3
(N_CREWKEY, N_FIRSTNAME, N_LASTNAME, N_COMMENT)
VALUES(7, 'Samuel', 'Brett', 'Engineers Mate');
INSERT INTO <YOUR_DATABASE>.<YOUR_SCHEMA>.NOSTROMO_SRC3
(N_CREWKEY, N_FIRSTNAME, N_LASTNAME, N_COMMENT)
VALUES(8, 'Jones', NULL, 'Ship''s Cat');
  1. Add the 3 tables to Snowflake using the above commands
  2. Add the 3 tables as sources in Coalesce
13661366

Source tables loaded in Coalesce

  1. Add a Stage Node from NOSTROMO_SRC1 and rename it STG_NOSTROMO_COMBINED
  2. From within STG_NOSTROMO_COMBINED select Options Multi Source and enable the toggle
34843484

A multisource node with only one source

  1. With multi source enabled, now inside of STG_NOSTROMO_COMBINED you'll see a column on the left listing all the sources for the multi source node. Inside of STG_NOSTROMO_COMBINED, you can edit their names by clicking on them and and editing the relevant text field. Rename the one source node to just SRC1.

There are two different zones for drag and drop, and they work differently. The top drop zone (blue in the image below) will attempt to map columns from the new source into those of the existing source, like in a UNION. The bottom drop zone (orange in the image below) is available in all nodes, and will add the columns as separate columns, like in a JOIN.

34843484

Two different drop zones in a multisource node

  1. Add a new source to STG_NOSTROMO_COMBINED by clicking on the + symbol beneath SRC1
  2. Select and rename it SRC2
  3. Now we will add NOSTROMO_SRC2 by dragging it from the Nodes list on the left into the blue drop zone.
  4. Notice it didn't copy one of the columns, N_MIDDLEINITIAL, as this doesn't correspond to any from SRC1. Select that column specifically and drag it into the orange drop zone.
  5. Move the new N_MIDDLEINITIAL column to order it after N_FIRSTNAME
  6. Open the Join tab for SRC2 Generate Join Copy to Editor
  7. Return to the Mapping area for STG_NOSTROMO_COMBINED
  8. Add another source, renaming it SRC3
  9. Drag NOSTROMO_SRC3 into the blue dropzone
  10. Open the Join tab for SRC3 Generate Join Copy to Editor
  11. Return to the mapping area for STG_NOSTROMO_COMBINED
28102810

After step 16, your node will look something like this

  1. Create and Run the STG_NOSTROMO_COMBINED node and you'll see all the data combined
21502150

Result of the Multi Source Node


Did this page help you?