Duplicate SQL files in a warehouse repo

For SQL projects without dbt, this rule flags files that write a relation and contain the same SQL as another such file. We compare a fingerprint of each whole file, ignoring comments, case and whitespace. Any table name the file writes to is part of it.

A fix applied to one copy is missed in the others, and the tables drift apart. Nobody can tell from the tables alone which copy holds the current logic.

Remove duplicated SQL files. Keep one definition and have the others read from it, or generate them from one template.

Before:

-- shop_eu/orders_daily/query.sql
select ordered_at, count(*) as orders
from raw.shop_orders group by 1

-- shop_uk/orders_daily/query.sql
select ordered_at, count(*) as orders
from raw.shop_orders group by 1

After:

-- shop_eu/orders_daily/query.sql
select ordered_at, count(*) as orders
from raw.shop_orders group by 1

-- shop_uk/orders_daily/view.sql
create view shop_uk.orders_daily as
select ordered_at, orders
from shop_eu.orders_daily

A copy per dataset that a script regenerates from one template is maintained in one place already. So is a copy kept only until a consumer moves. Tell us which applies, and the finding comes out of your report.

The .sql files, read from your repository with read-only access or uploaded. The fingerprint is a hash, and we keep none of the SQL. Identical text is a high-confidence match.