Multi Source Nodes

Multi Source nodes represent Coalesce's implementation of SQL UNIONs.

📘

Node Sources

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.

3. Add a Stage Node from NOSTROMO_SRC1 and rename it STG_NOSTROMO_COMBINED
4. From within STG_NOSTROMO_COMBINED select Options and toggle Multi Source to on.

5. 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.

6. Add a new source to STG_NOSTROMO_COMBINED by clicking on the + symbol beneath SRC1.
7. Select and rename it SRC2.
8. Now we will add NOSTROMO_SRC2 by dragging it from the Nodes list on the left into the blue drop zone.
9. 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.
10. Move the new N_MIDDLEINITIAL column to order it after N_FIRSTNAME.
11. Open the Join tab for SRC2 >Generate Join>Copy to Editor.
12. Return to the Mapping area for STG_NOSTROMO_COMBINED.
13. Add another source, renaming it SRC3.
14. Drag NOSTROMO_SRC3 into the blue dropzone.
15. Open the Join tab for SRC3 > Generate Join> Copy to Editor.
16. Return to the mapping area for STG_NOSTROMO_COMBINED.

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