sqlmesh.core.audit.builtin

  1from __future__ import annotations
  2
  3import inspect
  4import sys
  5
  6from sqlglot import exp
  7
  8from sqlmesh.core.audit.definition import Audit, ModelAudit
  9
 10
 11def create_non_blocking_copy(audit: Audit) -> Audit:
 12    return audit.copy(update={"name": f"{audit.name}_non_blocking", "blocking": False})
 13
 14
 15# not_null(columns=(column_1, column_2))
 16not_null_audit = ModelAudit(
 17    name="not_null",
 18    defaults={"condition": exp.true()},
 19    query="""
 20SELECT *
 21FROM @this_model
 22WHERE @AND(
 23  @REDUCE(
 24    @EACH(
 25      @columns,
 26      c -> c IS NULL
 27    ),
 28    (l, r) -> l OR r
 29  ),
 30  @condition,
 31)
 32    """,
 33)
 34
 35# unique_values(columns=(column_1, column_2))
 36unique_values_audit = ModelAudit(
 37    name="unique_values",
 38    defaults={"condition": exp.true()},
 39    query="""
 40SELECT *
 41FROM (
 42  SELECT
 43    @EACH(
 44      @columns,
 45      c -> row_number() OVER (PARTITION BY c ORDER BY c) AS rank_@c
 46    )
 47  FROM @this_model
 48  WHERE @condition
 49)
 50WHERE @REDUCE(
 51  @EACH(
 52    @columns,
 53    c -> rank_@c > 1
 54  ),
 55  (l, r) -> l OR r
 56)
 57    """,
 58)
 59
 60# accepted_values(column=column_name, is_in=(1, 2, 3))
 61accepted_values_audit = ModelAudit(
 62    name="accepted_values",
 63    defaults={"condition": exp.true()},
 64    query="""
 65SELECT *
 66FROM @this_model
 67WHERE @AND(@column NOT IN @is_in, @condition)
 68""",
 69)
 70
 71# number_of_rows(threshold=100)
 72number_of_rows_audit = ModelAudit(
 73    name="number_of_rows",
 74    defaults={"condition": exp.true()},
 75    query="""
 76SELECT COUNT(*)
 77FROM (
 78  SELECT 1
 79  FROM @this_model
 80  WHERE @condition
 81  LIMIT @threshold + 1
 82)
 83HAVING COUNT(*) <= @threshold
 84    """,
 85)
 86
 87# forall(criteria=(
 88#   column_1 > 0,
 89#   column_2 in (1, 2, 3),
 90#   column_3 is not null
 91# ))
 92forall_audit = ModelAudit(
 93    name="forall",
 94    defaults={"condition": exp.true()},
 95    query="""
 96SELECT *
 97FROM @this_model
 98WHERE
 99  @AND(
100    @REDUCE(
101      @EACH(
102        @criteria,
103        c -> NOT (c)
104      ),
105      (l, r) -> l OR r
106    ),
107    @condition,
108  )
109    """,
110)
111
112# accepted_range(column=age, min_v=0, max_v=100)
113# accepted_range(column=age, min_v=10)
114# accepted_range(column=age, max_v=50)
115accepted_range_audit = ModelAudit(
116    name="accepted_range",
117    defaults={
118        "min_v": exp.null(),
119        "max_v": exp.null(),
120        "inclusive": exp.true(),
121        "condition": exp.true(),
122    },
123    query="""
124SELECT *
125FROM @this_model
126WHERE
127  @AND(
128    @OR(
129      @IF(@min_v IS NOT NULL AND @inclusive, @column < @min_v, NULL),
130      @IF(@min_v IS NOT NULL AND NOT @inclusive, @column <= @min_v, NULL),
131      @IF(@max_v IS NOT NULL AND @inclusive, @column > @max_v, NULL),
132      @IF(@max_v IS NOT NULL AND NOT @inclusive, @column >= @max_v, NULL),
133    ),
134    @condition,
135  )
136    """,
137)
138
139# at_least_one(column=column_name)
140at_least_one_audit = ModelAudit(
141    name="at_least_one",
142    defaults={"condition": exp.true()},
143    query="""
144SELECT 1
145FROM @this_model
146WHERE @condition
147GROUP BY 1
148HAVING COUNT(@column) = 0
149    """,
150)
151
152# not_constant(column=column_name)
153not_constant_audit = ModelAudit(
154    name="not_constant",
155    defaults={"condition": exp.true()},
156    query="""
157SELECT 1
158FROM (
159  SELECT COUNT(DISTINCT @column) AS t_cardinality
160  FROM @this_model
161  WHERE @condition
162) AS r
163WHERE r.t_cardinality <= 1
164    """,
165)
166
167# not_empty_string(column=column_name)
168not_empty_string_audit = ModelAudit(
169    name="not_empty_string",
170    defaults={"condition": exp.true()},
171    query="""
172SELECT *
173FROM @this_model
174WHERE @AND(@column = '', @condition)
175    """,
176)
177
178# not_null_proportion(column=column_name, threshold=0.9)
179not_null_proportion_audit = ModelAudit(
180    name="not_null_proportion",
181    defaults={"condition": exp.true()},
182    query="""
183SELECT *
184FROM (
185  SELECT
186    count(*) as cnt_tot,
187    count(@column) as cnt_not_null,
188    count(*) - count(@column) as cnt_null
189  FROM @this_model
190  WHERE @condition
191) AS s
192WHERE s.cnt_not_null <= s.cnt_tot * @threshold
193    """,
194)
195
196# not_accepted_values(column=column_name, is_in=(1, 2, 3))
197not_accepted_values_audit = ModelAudit(
198    name="not_accepted_values",
199    defaults={"condition": exp.true()},
200    query="""
201SELECT *
202FROM @this_model
203WHERE @AND(@column IN @is_in, @condition)
204""",
205)
206
207# sequential_values(column=column_name, interval=1)
208# TODO: support grouping
209sequential_values_audit = ModelAudit(
210    name="sequential_values",
211    defaults={"interval": exp.Literal.number(1), "condition": exp.true()},
212    query="""
213WITH windowed AS (
214  SELECT
215    @column,
216    LAG(@column) OVER (
217      ORDER BY @column
218    ) AS prv
219  FROM @this_model
220  WHERE @condition
221), validation_errors AS (
222    SELECT *
223    FROM windowed
224    WHERE NOT (@column = prv + @interval)
225)
226
227SELECT *
228FROM validation_errors
229    """,
230)
231
232# unique_combination_of_columns(columns=(column_1, column_2))
233unique_combination_of_columns_audit = ModelAudit(
234    name="unique_combination_of_columns",
235    defaults={"condition": exp.true()},
236    query="""
237SELECT @EACH(@columns, c -> c)
238FROM @this_model
239WHERE @condition
240GROUP BY @EACH(@columns, c -> c)
241HAVING COUNT(*) > 1
242    """,
243)
244
245# mutually_exclusive_ranges(lower_bound_column=date_from, upper_bound_column=date_to)
246# TODO: make inclusivity configurable
247mutually_exclusive_ranges_audit = ModelAudit(
248    name="mutually_exclusive_ranges",
249    defaults={"partition_clause": exp.false(), "condition": exp.true()},
250    query="""
251WITH window_functions AS (
252  SELECT
253    @lower_bound_column AS lower_bound,
254    @upper_bound_column AS upper_bound,
255    LEAD(@lower_bound_column) OVER (
256      @if(@partition_clause, @partition_clause)
257      ORDER BY @lower_bound_column, @upper_bound_column
258    ) AS next_lower_bound,
259    row_number() OVER (
260      @if(@partition_clause, @partition_clause)
261      ORDER BY @lower_bound_column desc, @upper_bound_column desc
262    ) = 1 AS is_last_record
263  FROM @this_model
264  WHERE @condition
265), calc AS (
266  SELECT
267    *,
268    COALESCE(
269      lower_bound <= upper_bound,
270      False
271    ) AS lower_bound_lte_upper_bound,
272    COALESCE(
273      upper_bound <= next_lower_bound,
274      is_last_record,
275      False
276    ) AS upper_bound_lte_next_lower_bound
277  FROM window_functions
278), validation_errors AS (
279  SELECT *
280  FROM calc
281  WHERE NOT (
282    lower_bound_lte_upper_bound
283    AND upper_bound_lte_next_lower_bound
284  )
285)
286
287SELECT *
288FROM validation_errors
289    """,
290)
291
292# valid_uuid(column=column_name)
293valid_uuid_audit = ModelAudit(
294    name="valid_uuid",
295    defaults={"condition": exp.true()},
296    query="""
297SELECT *
298FROM @this_model
299WHERE @AND(
300  NOT REGEXP_LIKE(LOWER(@column), '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'),
301  @condition,
302)
303
304    """,
305)
306
307# valid_url(column=column_name)
308valid_url_audit = ModelAudit(
309    name="valid_url",
310    defaults={"condition": exp.true()},
311    query=r"""
312SELECT *
313FROM @this_model
314WHERE @AND(NOT REGEXP_LIKE(@column, '^(https?|ftp)://[^\s/$.?#].[^\s]*$'), @condition)
315    """,
316)
317
318# valid_http_method(column=column_name)
319valid_http_method_audit = ModelAudit(
320    name="valid_http_method",
321    query="""
322SELECT *
323FROM @this_model
324WHERE NOT @column IN ('GET', 'POST', 'PUT', 'DELETE', 'PATCH', 'HEAD', 'OPTIONS', 'TRACE', 'CONNECT')
325    """,
326)
327
328# valid_email(column=column_name)
329valid_email_audit = ModelAudit(
330    name="valid_email",
331    defaults={"condition": exp.true()},
332    query=r"""
333SELECT *
334FROM @this_model
335WHERE @AND(NOT REGEXP_LIKE(@column, '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'), @condition)
336    """,
337)
338
339# match_regex_pattern_list(column=column_name, patterns=('^pattern_1', 'pattern_2$'))
340match_regex_pattern_list_audit = ModelAudit(
341    name="match_regex_pattern_list",
342    defaults={"condition": exp.true()},
343    query="""
344SELECT *
345FROM @this_model
346WHERE @AND(
347  @REDUCE(
348    @EACH(
349      @patterns,
350      c -> NOT REGEXP_LIKE(@column, c)
351    ),
352    (l, r) -> l AND r
353  ),
354  @condition,
355)
356    """,
357)
358
359# not_match_regex_pattern_list(column=column_name, patterns=('^pattern_1', 'pattern_2$'))
360not_match_regex_pattern_list_audit = ModelAudit(
361    name="not_match_regex_pattern_list",
362    defaults={"condition": exp.true()},
363    query="""
364SELECT *
365FROM @this_model
366WHERE @AND(
367  @REDUCE(
368    @EACH(
369      @patterns,
370      c -> REGEXP_LIKE(@column, c)
371    ),
372    (l, r) -> l OR r
373  ),
374  @condition,
375)
376    """,
377)
378
379# match_like_pattern_list(column=column_name, patterns=('%pattern_1%', 'pattern_2%'))
380match_like_pattern_list = ModelAudit(
381    name="match_like_pattern_list",
382    defaults={"condition": exp.true()},
383    query="""
384SELECT *
385FROM @this_model
386WHERE @AND(
387  @REDUCE(
388    @EACH(
389      @patterns,
390      c -> NOT @column LIKE c
391    ),
392    (l, r) -> l AND r
393  ),
394  @condition,
395)
396    """,
397)
398
399# not_match_like_pattern_list(column=column_name, patterns=('%pattern_1%', 'pattern_2%'))
400not_match_like_pattern_list_audit = ModelAudit(
401    name="not_match_like_pattern_list",
402    defaults={"condition": exp.true()},
403    query="""
404SELECT *
405FROM @this_model
406WHERE @AND(
407  @REDUCE(
408    @EACH(
409      @patterns,
410      c -> @column LIKE c
411    ),
412    (l, r) -> l OR r
413  ),
414  @condition,
415)
416    """,
417)
418
419# z_score(column=column_name, threshold=3)
420z_score_audit = ModelAudit(
421    name="z_score",
422    defaults={"condition": exp.true()},
423    query="""
424WITH stats AS (
425  SELECT
426    AVG(@column) AS mean_@column,
427    STDDEV(@column) AS stddev_@column
428  FROM @this_model
429  WHERE @condition
430)
431SELECT
432  @column,
433  (@column - mean_@column) / NULLIF(stddev_@column, 0) AS z_score
434FROM @this_model, stats
435WHERE ABS((@column - mean_@column) / NULLIF(stddev_@column, 0)) > @threshold
436    """,
437)
438
439# string_length_between(column=column_name, max_v=22)
440string_length_between_audit = ModelAudit(
441    name="string_length_between",
442    defaults={
443        "min_v": exp.null(),
444        "max_v": exp.null(),
445        "inclusive": exp.true(),
446        "condition": exp.true(),
447    },
448    query="""
449SELECT *
450FROM @this_model
451WHERE
452  @AND(
453    @OR(
454      @IF(@min_v IS NOT NULL AND @inclusive, LENGTH(@column) < @min_v, NULL),
455      @IF(@min_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) <= @min_v, NULL),
456      @IF(@max_v IS NOT NULL AND @inclusive, LENGTH(@column) > @max_v, NULL),
457      @IF(@max_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) >= @max_v, NULL),
458    ),
459    @condition,
460  )
461    """,
462)
463
464# string_length_equal(column=column_name, v=22)
465string_length_equal_audit = ModelAudit(
466    name="string_length_equal",
467    defaults={"condition": exp.true()},
468    query="""
469SELECT *
470FROM @this_model
471WHERE @AND(LENGTH(@column) != @v, @condition)
472    """,
473)
474
475# stddev_in_range(column=age, min_v=2.5, max_v=25)
476stddev_in_range_audit = ModelAudit(
477    name="stddev_in_range",
478    defaults={
479        "min_v": exp.null(),
480        "max_v": exp.null(),
481        "inclusive": exp.true(),
482        "condition": exp.true(),
483    },
484    query="""
485SELECT *
486FROM (
487  SELECT STDDEV(@column) AS stddev_@column
488  FROM @this_model
489  WHERE @condition
490)
491WHERE
492  @OR(
493    @IF(@min_v IS NOT NULL AND @inclusive, stddev_@column < @min_v, NULL),
494    @IF(@min_v IS NOT NULL AND NOT @inclusive, stddev_@column <= @min_v, NULL),
495    @IF(@max_v IS NOT NULL AND @inclusive, stddev_@column > @max_v, NULL),
496    @IF(@max_v IS NOT NULL AND NOT @inclusive, stddev_@column >= @max_v, NULL),
497  )
498    """,
499)
500
501# mean_in_range(column=age, min_v=2.5, max_v=25)
502mean_in_range_audit = ModelAudit(
503    name="mean_in_range",
504    defaults={
505        "min_v": exp.null(),
506        "max_v": exp.null(),
507        "inclusive": exp.true(),
508        "condition": exp.true(),
509    },
510    query="""
511SELECT *
512FROM (
513  SELECT AVG(@column) AS mean_@column
514  FROM @this_model
515  WHERE @condition
516)
517WHERE
518  @OR(
519    @IF(@min_v IS NOT NULL AND @inclusive, mean_@column < @min_v, NULL),
520    @IF(@min_v IS NOT NULL AND NOT @inclusive, mean_@column <= @min_v, NULL),
521    @IF(@max_v IS NOT NULL AND @inclusive, mean_@column > @max_v, NULL),
522    @IF(@max_v IS NOT NULL AND NOT @inclusive, mean_@column >= @max_v, NULL),
523  )
524    """,
525)
526
527# kl_divergence(column=age, target_column=normalized_age, threshold=0.1)
528kl_divergence_audit = ModelAudit(
529    name="kl_divergence",
530    defaults={"condition": exp.true()},
531    query="""
532WITH
533  table_a AS (
534    SELECT
535      @source_column,
536      COUNT(*) AS num_rows
537    FROM @this_model
538    WHERE @condition
539    GROUP BY @source_column
540  ),
541  table_b AS (
542    SELECT
543      @target_column,
544      COUNT(*) AS num_rows
545    FROM @this_model
546    WHERE @condition
547    GROUP BY @target_column
548  ),
549  table_a_with_p AS (
550    SELECT
551      @source_column,
552      num_rows,
553      num_rows / SUM(num_rows) OVER () AS p
554    FROM table_a
555  ),
556  table_b_with_q AS (
557    SELECT
558      @target_column,
559      num_rows,
560      num_rows / SUM(num_rows) OVER () AS q
561    FROM table_b
562  ),
563  table_a_with_q AS (
564    SELECT
565      @source_column,
566      num_rows,
567      p,
568      COALESCE(q, 0) AS q
569    FROM table_a_with_p
570    LEFT JOIN table_b_with_q USING (@source_column)
571  ),
572  table_b_with_p AS (
573    SELECT
574      @target_column,
575      num_rows,
576      q,
577      COALESCE(p, 0) AS p
578    FROM table_b_with_q
579    LEFT JOIN table_a_with_p USING (@target_column)
580  ),
581  table_a_with_kl AS (
582    SELECT
583      @source_column,
584      num_rows,
585      p,
586      q,
587      p * LOG(p / NULLIF(q, 0)) AS kl
588    FROM table_a_with_q
589  ),
590  table_b_with_kl AS (
591    SELECT
592      @target_column,
593      num_rows,
594      p,
595      q,
596      q * LOG(q / NULLIF(p, 0)) AS kl
597    FROM table_b_with_p
598  ),
599  unioned AS (
600    SELECT *
601    FROM table_a_with_kl
602    UNION ALL
603    SELECT *
604    FROM table_b_with_kl
605  )
606SELECT
607  @source_column,
608  @target_column,
609  SUM(kl) AS kl_divergence
610FROM unioned
611GROUP BY @source_column, @target_column
612HAVING kl_divergence > @threshold
613    """,
614)
615
616# chi_square(column_a=account_tier, column_b=account_user_segment, dependent=true, critical_value=9.48773)
617# Use the following to get the critical value for a given p-value:
618# from scipy.stats import chi2
619# chi2.ppf(0.95, 1) where 0.95 is the p-value and 1 is the degrees of freedom
620# https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.chi2.html
621# or use a table like https://www.medcalc.org/manual/chi-square-table.php
622chi_square_audit = ModelAudit(
623    name="chi_square",
624    expressions=[
625        "@def(c, (SELECT COUNT(DISTINCT x_a) FROM contingency_table))",
626        "@def(r, (SELECT COUNT(DISTINCT x_b) FROM contingency_table))",
627        "@def(E, (tot_a * tot_b / g_t))",
628    ],
629    defaults={"dependent": exp.true(), "condition": exp.true()},
630    query="""
631WITH
632  samples AS (
633    SELECT
634      @column_a AS x_a,
635      @column_b AS x_b,
636    FROM @this_model
637    WHERE @AND(@column_a IS NOT NULL AND @column_b IS NOT NULL, @condition)
638  ),
639  contingency_table AS (
640    SELECT
641      x_a,
642      x_b,
643      COUNT(*) as observed,
644      (SELECT COUNT(*) FROM samples AS t WHERE r.x_a = t.x_a) as tot_a,
645      (SELECT COUNT(*) FROM samples AS t WHERE r.x_b = t.x_b) as tot_b,
646      (SELECT COUNT(*) FROM samples) as g_t -- g_t is the grand total
647    FROM samples AS r
648    GROUP BY x_a, x_b
649  )
650
651SELECT
652  (@c - 1) * (@r - 1) as degrees_of_freedom,
653  SUM((observed - @E) * (observed - @E) / @E) as chi_square
654FROM contingency_table
655-- H0: the two variables are independent
656-- H1: the two variables are dependent
657-- if chi_square > critical_value, reject H0
658-- if chi_square <= critical_value, fail to reject H0
659HAVING NOT @IF(@dependent, chi_square > @critical_value, chi_square <= @critical_value)
660    """,
661)
662
663# The following audits are not yet implemented
664# we are awaiting a first class way to express cross-model audits
665
666# cardinality_equality(source_column=column_1, target_column=column_2, to=some.model)
667# cardinality_equality_audit = ModelAudit(
668#     name="cardinality_equality",
669#     query="""
670# WITH table_a AS (
671#   SELECT
672#     @source_column,
673#     count(*) AS num_rows
674#   FROM @this_model
675#   GROUP BY @source_column
676# ),
677# table_b AS (
678#   SELECT
679#     @target_column,
680#     count(*) AS num_rows
681#   FROM @to
682#   GROUP BY @target_column
683# ),
684# except_a AS (
685#   SELECT *
686#   FROM table_a
687#   EXCEPT
688#   SELECT *
689#   FROM table_b
690# ),
691# except_b AS (
692#   SELECT *
693#   FROM table_b
694#   EXCEPT
695#   SELECT *
696#   FROM table_a
697# ),
698# unioned AS (
699#   SELECT *
700#   FROM except_a
701#   UNION ALL
702#   SELECT *
703#   FROM except_b
704# )
705# SELECT * FROM unioned
706#     """,
707# )
708
709
710# relationship(to=some.model, source_column=id, target_column=id)
711# relationship_audit = ModelAudit(
712#     name="relationship",
713#     defaults={"from_condition": exp.true(), "to_condition": exp.true()},
714#     query="""
715# SELECT
716#   child.source_column
717# FROM (
718#   SELECT @source_column as source_column
719#   FROM @this_model
720#   WHERE NOT @source_column IS NULL
721#   AND @from_condition
722# ) AS child
723# LEFT JOIN (
724#   SELECT @target_column AS target_column
725#   FROM @to
726#   WHERE @to_condition
727# ) AS parent
728# ON child.source_column = parent.target_column
729# WHERE parent.target_column IS NULL
730#     """,
731# )
732
733# equality(columns=(column_1, column_2), to=some.model)
734# equality_audit = ModelAudit(
735#     name="equality",
736#     query="""
737# SELECT @EACH(@columns, c -> c)
738# FROM @this_model
739# EXCEPT
740# SELECT @EACH(@columns, c -> c)
741# FROM @to
742#     """,
743# )
744
745# equal_row_count(to=some.model)
746# equal_row_count_audit = ModelAudit(
747#     name="equal_row_count",
748#     query="""
749# SELECT 1
750# FROM (
751#   SELECT count(*) as cnt
752#   FROM @this_model
753# ) AS src
754# INNER JOIN (
755#   SELECT count(*) as cnt
756#   FROM @to
757# ) AS tgt ON src.cnt != tgt.cnt
758#     """,
759# )
760
761# fewer_rows_than(to=some.model)
762# fewer_rows_than_audit = ModelAudit(
763#     name="fewer_rows_than",
764#     query="""
765# SELECT 1
766# FROM (
767#   SELECT count(*) as cnt
768#   FROM @this_model
769# ) AS src
770# INNER JOIN (
771#   SELECT count(*) as cnt
772#   FROM @to
773# ) AS tgt ON src.cnt >= tgt.cnt
774#     """,
775# )
776
777
778BUILT_IN_AUDITS = {
779    audit.name: audit
780    for _, model_audit in inspect.getmembers(
781        sys.modules[__name__], lambda v: isinstance(v, ModelAudit)
782    )
783    for audit in (model_audit, create_non_blocking_copy(model_audit))
784}