Extracting paid orders from PostgreSQL

Context — orders live in a production PostgreSQL database

Finance needs a daily extract of paid orders. Credentials must stay outside YAML and the database should do the filtering.

Where things stand

• The current result is fragile. Its assumptions are split between tools, clicks and memory.
• Reruns are uncertain. The write mode or orchestration rule is not visible beside the data.
• Evidence is missing. A colleague cannot compare a declared rule with a concrete before and after state.

The question

How do you connect safely and extract only the useful rows from PostgreSQL?

• Keep credentials in environment variables
• Push the predicate to PostgreSQL
• Stream rows in batches
• Write a traceable snapshot

The solution in one line

A Hydra postgresql source, a local inspection destination and one pipeline command.

Steps

1. inspect the input

1. Identify the records and the contract you need.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

PG_HOST=localhost
PG_PORT=5432
PG_USER=hydra_reader
PG_PASSWORD=replace-me
PG_DATABASE=shop

2. declare the postgresql source

1. Describe the connection and extraction.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

version: "1.0"
sources:
  paid_orders:
    type: postgresql
    connection:
      host: ${ENV:PG_HOST}
      port: ${ENV:PG_PORT}
      user: ${ENV:PG_USER}
      password: ${ENV:PG_PASSWORD}
      database: ${ENV:PG_DATABASE}
    extract:
      query: "SELECT order_id, amount, paid_at FROM orders WHERE status = 'paid'"
      batch_size: 5000

3. declare a reviewable output

1. Write the extracted rows to a local CSV.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

version: "1.0"
destinations:
  result:
    type: csv
    connection:
      base_path: "out"
    load:
      table: result.csv
      mode: replace

4. wire source to destination

1. Connect the two identifiers.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

version: "1.0"
pipeline:
  from: paid_orders
  to: result

5. validate without reading data

1. Check the four manifest files.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

$ hdrctl test postgresql-source

ok  sources.yaml      — postgresql
ok  destinations.yaml — csv, replace
ok  pipeline.yaml     — paid_orders → result

✅ All tests pass — ready to execute.

6. run and read the counters

1. Execute the extraction once.
2. Compare the file with the explanation in the workbench.
3. Record the shown check before moving to the next step.

$ hdrctl run postgresql-source

✅ Pipeline completed successfully
Rows read   : 842
Rows written: 842
Query        : executed by PostgreSQL

Expected result

• 842 paid orders exported
• The source remains unchanged.
• The connection and extraction contract are versioned.

Reading the results

The source counter proves what the connector emitted. Compare it with the expected table, file or API count before adding business transforms.

What the counters do—and do not—prove

They prove how many records entered and left this run. They do not replace checking the target schema, the business meaning of values or the reason a workflow step was skipped.

The rule to carry forward

Use a read-only database account. Hydra resolves `${ENV:…}` before opening the connection.

Did we answer the question?

Before and after

• Before — orders live in a production PostgreSQL database requires a person to remember the order, options and checks.
• After — one reviewed manifest and one command produce the same observable result.
• What is really gained — The durable gain is the contract: a colleague can read the configuration, reproduce the run and challenge the assumptions.

Close by

• sources.yaml
• transformations.yaml
• destinations.yaml
• workflow.yaml
• Tutorial — your first job
• All Guide workshops