Audit Transactions Before Power BI Import

How do you connect to different data sources in Power BI, which connection type do you select, and what things must you check before importing via an SQL script?

Asked in the Technical Round stage. Initial technical questions focused on data ingestion and SQL preparation.

Write a PostgreSQL query that audits source transactions before they are imported into Power BI. Identify missing customer relationships, null required fields, non-positive amounts, and duplicate source references.

Output

1. One row per source transaction, with transaction_id, customer_id, transaction_date, amount, import_status, and validation_issue.
2. Mark rows as Ready only when all checks pass; otherwise mark them Review.
3. Order by transaction_id ascending.

Output details

• Treat source_reference as required, and report issues in this order: Missing customer, Missing transaction date, Missing amount or Non-positive amount, Missing source reference, Duplicate source reference.
• Use NULL for validation_issue when no issues exist; otherwise separate multiple issue messages with ; exactly.
• Flag Duplicate source reference only when a non-NULL source_reference occurs more than once, while retaining every source transaction.
• Require an existing matching customer, non-NULL transaction_date, amount greater than 0, non-NULL source_reference, and no duplicate reference for Ready.