Quick Start

​

Before you begin

• PostgreSQL source host, port, database, username, password, and SSL mode
• PostgreSQL destination host, port, database, username, password, and SSL mode
• A source user with read access to public.orders
• A destination user with write access to analytics.orders_live
• A pre-created destination table named analytics.orders_live

CREATE SCHEMA IF NOT EXISTS analytics;

CREATE TABLE IF NOT EXISTS analytics.orders_live (
  id bigint PRIMARY KEY,
  customer_id bigint,
  order_status text,
  total_amount numeric,
  created_at timestamptz,
  updated_at timestamptz
);

​

Build the pipeline

1. Open Connections and create a Source connection with PostgreSQL.
2. Fill the connection form and click Test Connection.
3. Save the source after the test succeeds.
4. Create a second PostgreSQL connection with the role set to Destination.
5. Click Test Connection and save the destination after the test succeeds.
6. Open Data Pipelines and create a new pipeline with a name and the source connection.
7. In the builder, click the settings icon on the Source node.
8. Click Discover schema or Refresh tables.
9. Include public.orders and preview the rows.
10. Click the settings icon on the Destination node.
11. Choose the destination connection.
12. Keep Sync mode as FULL_TABLE for the first validation run.
13. Keep Write mode as Upsert.
14. In the SQL Layer tab, set the final delivery table to analytics.orders_live.
15. Use a simple SQL model:

SELECT
  id,
  customer_id,
  LOWER(order_status) AS order_status,
  total_amount,
  created_at,
  updated_at
FROM {{ source('raw', 'public__orders') }}
WHERE order_status IS NOT NULL

16. Click Validate SQL.
17. Click Preview output.
18. Click Save destination.
19. Run the destination manually from the destination node.
20. Open run status or run history and confirm the run succeeded.

​

Add a schedule later